Description

Passes an SQL statement (including, but not limited to queries) to the datasource specified via OLEDB. If a query is specified, an Automated Workflow dataset with the name you specify is created and populated with the query results. Useful for Automated Workflow retrieval, updating or transfer 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 most people get the most out of this action. Many books on SQL are available.

IMPORTANT: Because the results retrieved may contain multiple records (think of records as rows) and multiple fields (think columns), to access data retrieved from SQL Query you must use the Loop Dataset Action <AMLOOPDATASET> to iterate the records and embedded expressions to extract the data from the individual fields (e.g. %datasetname.fieldname% ). It sounds more difficult than it actually is - see Notes below for more information.

Declaration

<AMSQLQUERY CONNECTIONSTRING="text" RESULTDATASET="text" SQLSTATEMENT="text" MAXROWS="number" LOGIN="yes/no" TIMEOUT="number" CACHEDAFTER="datetime">

Example

<!--- Demonstrates using SQL query with Loop Dataset. Gets all customer where city field is San Diego. Then writes names to a file, to make this Workflow work, change the SQL Query connection string and query to match your database--->
<AMSQLQUERY CONNECTIONSTRING="19y9I4LjuL6U1z5kWV9qNz7f2GrLdS16HzZ2IABihh5oydIvZvKxirNVsXfr02s5+YSmraH478OnG3fyNDrnr4k2lmJKCFqoQ/ poQgCblLO4gyfnpETTF3SS5ARqugxsajhx7hYL0OypO7YoBsI0EMMvOE56AM64O" RESULTDATASET="myresults">SELECT firstname, lastname from customer where city='San Diego';</AMSQLQUERY>
<AMMESSAGEBOX>%myresults.TotalRows% customers found</AMMESSAGEBOX>
<AMLOOPDATASET DATASET="myresults">
<AMFILEWRITE FILE="c:\customerlist.txt">%myresults.firstname% %myresults.lastname%</AMFILEWRITE>
</AMLOOP>

General Tab Parameters

Connection string: Specifies an OLEDB connection string. The connection string provides driver, server, username, password and other important information to the OLEDB subsystem so that the connection can be made. Generate a connection string is by clicking the New icon . The connection string can be optionally encrypted to protect your password information by using the "Encrypt Connection String" [ENCRYPT=] option.

Text, Required
MARKUP:
a) CONNECTIONSTRING="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Common Files\Borland Shared\Data\BCDEMOS.mdb"

Prompt User for Name and Password: Specifies that the user should be prompted with a username and password for the database server each time the Workflow is run.

Yes/No, Optional - Default - NO
MARKUP: LOGIN="YES"

Create and Populate Dataset: Name of the dataset that should be created and populated with the results (if any) of the SQL Statement upon execution. To access the data in subsequent steps simply specify %DATASETNAME.FIELDNAME% inside a Loop Dataset <AMLOOPDATASET> action (to recurse the rows).

Text, Required
MARKUP: DATASETRESULT="DATESETNAME"

SQL Statement: Specifies the SQL statement that should be executed.

Text, Required
MARKUP: SQLSTATEMENT="SELECT * FROM CUSTOMER WHERE CITY='Los Angeles'"

Cache Tab Parameters

Never use cached data or Use cached query data is execution date is after: Specifies a date/time after which the query should use cached data instead of contacting the database. Usually this would be a calculated value using an expression containing DateAdd.

Date, Optional Default - None
MARKUP: CACHEDAFTER="%DateSerial(2001,10,12) + TimeSerial(11,14,16)%"

Advanced Tab Parameters

Maximum Rows: Specifies the maximum number of rows that should be returned by the server.

Number, Optional
MARKUP: MAXROWS="500"

The query times Out After: Specifies the amount of milliseconds, seconds, minutes, or hours that the query should be allowed before timing out at the server level.

Number, Optional Default - 0
MARKUP: TIMEOUT="500"

Notes

A variable field Dataset is generated. A dataset is a multiple column, multiple row container object. This action creates and populates a dataset, the fields contained within that 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 data set would be generated:

datasetname
|--firstname
|--lastname
|--company

along with the standard fields included in every dataset

|--CurrentRow
|--TotalRows
|--TotalColumns
|--ExecutionDate
|--RowsAffected
|--SQLQuery
|--Datasource

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action <AMLOOPDATASET> 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:

%mydatasetname.firstname%

or you could combine two fields together like this:

%mydatasetname.firstname + " " + mydatasetname.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="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

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

See Also

Loop Dataset, Stored Procedure, Open SQL Connection Action, Close SQL Connection Action