Database - SQL Query

Declaration

Description:

Passes a SQL (Structured Query Language) statement including queries to the data source specified via OLEDB. If a query is specified, a Dataset is created and populated with the query results.

NOTE: Because the results retrieved from a query may contain multiple records (rows) and multiple fields (columns), to access the data, you must use the Loop Dataset activity to iterate the records and embedded expressions in order to extract the data from the individual fields. See Datasets below for more information.

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

Property	Type	Required	Default	Markup	Description
Connection					Indicates where the connection string should originate from. This is a design time parameter used only during task construction and configuration, thus, comprises no markup. The available options are: Host (default) - A new or existing connection string will be set individually for this activity. Select this option if performing a single database operation. Session - The connection string should derive from a session created by a previous Open SQL Connection step. This allows several queries to be linked to a specific session. System - The connection string will derive from one configured and saved for the system.
Type	Text (options)	Yes if Connection set to Host	Database	CONNECTIONTYPE="database" CONNECTIONTYPE="connection_string" CONNECTIONTYPE="file"	The type of connection string to use. This parameter is active only of the Connection parameter is set to Host. The available options are: Database (default) - Database connection will derive from a new OLEDB connection string created via the Data Link Properties Wizard. Press the Build Connection button to open the Data Link Properties Wizard. Press the Clear button to clear an existing connection string. Connection string - Database connection will be established using a specific connection string. Enables you to copy an existing string onto the properties of this activity. Data link file - Connection will be established via the data link file specified.
Database connection	Text	Yes if Type set to Database	(Empty)	CONNECTIONSTRING= "AM1P2qCkbYiFHdqWN7qDphOEw==aME"	Specifies a custom 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. To generate a connection string, press the Build connection button. This opens the Data Link Properties Wizard dialog which will help you with construct of your connection string. To clear an existing connection string, press the Clear button. This parameter is available only if the Type parameter is set to Database.
Connection string	Text	Yes if Type set to Connection string	(Empty)	PREDEFINEDCONNECTION= "%MyPreDefinedConnection%"	Specifies the OLEDB connection string. Use this option to copy an existing connection string and paste it in the provided text box. This parameter is available only if the Type parameter is set to Connection string.
Data link file	Text	Yes if Type set to Data link file	(Empty)	CONNECTION="c:\temp\fileName.udl"	The path and filename of the data link (.udl,.dsn) file to connect with. Click the folder icon to open a standard Explorer dialog allowing for easy navigation to the desired data link file. This parameter is active only if the Type parameter is set to Data link file.
Prompt user for a name and password	Yes/No	No	No	LOGIN="YES"	If set to YES, specifies that the user should be prompted to enter a username and password for the database server each time the task is run.Set to NO by default.
Protect connection string					If enabled, the value entered in the Connection string parameter will be encrypted in both visual and AML mode as a means of protecting data embedded in the connection string. If disabled (default), the connection string will appear as normal readable text. Enable this option if your connection string contains sensitive data that you may not want others to view. This is a visual mode parameter used only during design time, therefore, contains no properties or markup. It is available only if the Type parameter is set to Connection string.
Session name	Text	Yes if Connection set to Session	SQLSession1	SESSION="Session10"	Indicates the name of an existing session to attach this activity to. The connection string will derive from that session. This parameter is active only if the Connection parameter is set to Session.
Pre-defined connection string	Text	Yes if Connection set to System	(Empty)	PREDEFINEDCONNECTION="%varName%" PREDEFINEDCONNECTION="AM1NGBAj8FWs8A0A"	Indicates the pre-defined connection string that should be used to connect to the database. This parameter is active only if the Connection parameter is set to System.
SQL statement	Text	Yes	(Empty)	SQLSTATEMENT="SELECT * FROM CUSTOMER WHERE CITY='Los Angeles'"	Specifies the SQL statement that should be executed.
Create and populate dataset	Text	Yes	(Empty)	RESULTDATASET="theDataset"	The 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% (where datasetname specifies the name of the dataset and fieldname specifies the name of the dataset field) inside a Loop Dataset action (to recurse the rows). More details regarding datasets can be found below under Datasets.

Cache

Property	Type	Required	Default	Markup	Description
Never use cached data					If enabled, specifies that cached data will not be used for this connection. This is a visual parameter used only during design time, therefore, contains no markup. When this parameter is enabled, the Use cached query data if execution date is after parameter is ignored.
Use cached query data if execution date is after	Date	No	(Empty)	CACHEDAFTER="%DateSerial(2010,06,14) + TimeSerial(11,41,44)%"	If enabled, 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. When this parameter is enabled, the Never use cached data parameter is ignored.

Advanced

Property	Type	Required	Default	Markup	Description
Maximum number of rows returned (optional)	Number	No	(Empty)	MAXROWS="500"	The maximum number of rows that should be returned by the database server.
The query times out after (optional)	Number	No	(Empty)	TIMEOUT="2"	The amount of time that the query should be allowed before timing out at the server level.
Measure	Text (options)	No	Seconds	MEASURE="minutes"	The time measurement that should correspond to the query timeout value entered. The available options are: Milliseconds - The timeout value is measured in milliseconds. Seconds (default) - The timeout value is measured in seconds. Minutes - The timeout value is measured in minutes. Hours - The timeout value is measured in hours.

Description

Error Causes

On Error

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:

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:

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):

Name	Data Type	Return Value
theDataset.CurrentRow	Number	The current row that will be accessed in the dataset by an expression that does not contain a specific row index.
theDataset.TotalRows	Number	The total number of rows in the dataset
theDataset.TotalColumns	Number	The total number of columns (not including the static columns) in the dataset.
theDataset.ExecutionDate	Date	The date and time the dataset was created and populated
theDataset.RowsAffected	Number	The number of rows affected by an update.
theDataset.SQLQuery	Text	The SQL Query that was used to generate this dataset (If a SQL Query was not used, this value is empty).
theDataset.Datasource	Text	The datasource used for the SQL Query, if applicable.
theDataset.ColumnNames	Text	A comma-delimited list of the column names in the dataset

Example

NOTE: The sample AML code below can be copied and pasted directly into the Steps panel of the Task Builder.

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.

<AMSQLQUERY CONNECTIONSTRING="AM2y6SWh0mxeUsrZJdHT9FyayHUkPdLQWKrLISRd0XxeqsoFJ7XTZF+2yImnfdJ4X17LWSfx08jfpsltISHRFF3yyREndda10/BfctA==aME" RESULTDATASET="theDataset" TIMEOUT="2" MEASURE="minutes">SELECT firstname, lastname from customer where city='San Diego'</AMSQLQUERY>

<AMSHOWDIALOG WINDOWTITLE="CURRENT NAME">First Name - %theDataset.firstname%

Last Name - %theDataset.lastname%</AMSHOWDIALOG>

</AMLOOP>

This help file is for the Advanced Workflow Engine v10. For other versions, please refer to http://help.globalscape.com/help/index.html

(If the Index and Contents are hidden, click the icon in the top left corner.)

If this help topic did not help you, try searching the Knowledgebase.
For the most up-to-date information, to view version history, updates, and activation instructions, visit the Support Center at http://www.globalscape.com/support-home.
For information about Globalscape, visit www.globalscape.com.

Last modified: 28-Jun-19 at 14:01:12