Database - Examples                              How to use?

Example 1: Connect to database and update the records based on the user input

1. Open the Task Editor from Tools -> Task Editor.
2. Double click on the 'Database' command.
3. Select the 'Connect' option
4. Specify the database you want to connect to by clicking on '...' button.
5. Test the connection before you save the command.
6. Similarly insert a Database->Insert/Update/Delete command with the appropriate SQL statement.
7. Insert a Database->Disconnect command.
8. Tip: The Insert/Update/Delete statement can have variables and the values of those variables can be taken from the user using the 'Prompt' command.

Example 2: Query all the accounts whose revenue is over 1 million dollar and print the account names to an Excel file.

1. Open the Task Editor from Tools -> Task Editor.
2. Double click on the 'Database' command.
3. Select the 'Connect' option
4. Specify the database you want to connect to by clicking on '...' button.
5. Test the connection before you save the command.
6. Similarly insert a Database->Select Query command with the appropriate SQL statement to get all required accounts.
7. Insert a Database->Disconnect command.
8. To access the result returned by the SQL Query, use the $Dataset Column$ System variable provided by Automation Anywhere. Use Loop for Each row in a SQL query dataset option to loop through all the rows of the SQL Query result. To access each column use $Dataset Column(index)$ for e.g. to get the first column use $Dataset Column(1)$.

Example 3: Connect to database and create tables, drop tables, alter tables.

1. Open the Task Editor from Tools -> Task Editor.
2. Double click on the 'Database' command.
3. Select the 'Connect' option
4. Specify the database you want to connect to by clicking on '...' button.
5. Test the connection before you save the command.
6. Similarly insert a Database->Insert/Update/Delete command with the appropriate SQL statement to create, drop, alter tables etc.
7. Insert a Database->Disconnect command.

Example 4: Connect to database and call a procedure stored in the database to update records based on the user input

1. Open the Task Editor from Tools -> Task Editor.
2. Double click on the 'Database' command.
3. Select the 'Connect' option
4. Specify the database you want to connect to by clicking on '...' button.
5. Test the connection before you save the command.
6. Similarly insert a Database->Run Stored Procedure command with the procedure name and parameters, if any. The procedure specified should have already been created in the database.
7. Insert a Database->Disconnect command.
8. Tip: The procedure name as well as the parameters can have variables and the values of those variables can be taken from the user using the 'Prompt' command.

Examples of SQL Queries

1. To query all the columns from a table called Table1, use the query 'Select * From Table1'
2. To query FirstName and LastName from a table called Names use the query 'Select FirstName, LastName from Names'
3. To query FirstName and LastName from a table called Names where FirstName is 'John' use the query Select FirstName, LastName from Names WHERE FirstName='John'