Database - SQL query

Declaration

<AMDATABASE ACTIVITY="sql_query" SESSION="text" CONNECTIONTYPE="text (options)" CONNECTIONSTRING="text (encrypted)" PREDEFINEDCONNECTION="text" CONNECTION="text (encrypted)" LOGIN="YES/NO" RESULTDATASET="text" INCLUDEMILLSECONDS="YES/NO" SQLSTATEMENT="text" SQLQUERY="text" CACHEDAFTER="DateSerial%" MAXROWS="text" TIMEOUT="10" MEASURE="text (options)" /> />

Related Topics    

Description

Passes SQL (Structured Query Language) statement(s) (including queries) to the data source by way of OLEDB. Specifying a query creates and populates a Dataset with the query results.

Practical usage

Useful for automated retrieval, update and management of data. Could also be used to test database response times and size quotas. Given that this action provides the full power of SQL, which is a powerful database language itself - the possibilities are limitless. A working knowledge of SQL will help get the most out of this action.

Parameters

Connection

Cache

Advanced

Description

Error Causes

On Error

Additional notes

Datasets

A dataset is a multiple column, multiple row container object. This action creates and populates a dataset. The fields contained within the dataset are determined by the query that was executed. For example if the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';

Then the following dataset would be generated (where 'datasetname' specifies the name of the dataset):

datasetname.firstname

datasetname.lastname

datasetname.company

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

%datasetname.firstname%

or you could combine two fields together like this:

%datasetname.firstname + " " + datasetname.lastname%

Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:

<AMMESSAGEBOX MESSAGETEXT="%datasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

At runtime the text %datasetname.firstname% is replaced by the contents of the subject of the current record.

The amount of datasets as well as the name assigned for each dataset fundamentally depends on the activity that initially created them. In addition, there are standard fields included in every dataset. You can easily view the list  The table below describes these fields (assuming the name of the dataset is theDataset):

Example

Description

This sample task demonstrates using SQL query with a Loop Dataset activity. It retrieves all customers where the 'City' field is 'San Diego' and populates a dataset with the results. A Loop Dataset activity is used to loop through the rows of the dataset. During each iteration, a message box appears showing the current result. To make this task work, change the SQL Query connection string and query to match your database.

1
2
<AMDATABASE ACTIVITY="sql_query" CONNECTIONTYPE="connection_string" CONNECTION="AM2y6SWh0mxeUsrZJdHT9FyayHUkPdLQWKrLISRd0XxeqsoFJ7XTZF+2yImnfdJ4X17LWSfx08jfpsltISHRFF3yyREndda10/BfctA==aME" RESULTDATASET="theDataset" SQLQUERY="SELECT firstname, lastname from customer where city='San Diego" TIMEOUT="2" MEASURE="minutes" />
<AMLOOP ACTIVITY="dataset" DATASET="theDataset" /><AMSHOWDIALOG WINDOWTITLE="CURRENT NAME">First Name - %theDataset.firstname%Last Name - %theDataset.lastname%</AMSHOWDIALOG><AMLOOP ACTIVITY="end" />