Query Database

The Query Database widget executes a single SELECT, INSERT, UPDATE, or DELETE SQL statement against a database. The results of SELECT statements can be stored in a workflow variable. The widget supports SQL Server, Oracle, ODBC, and OLEDB connections.

NOTE: In the case of SELECT statements, the Query Database widget returns either a single value (field) or a single record (row). SQL statements should be constructed with this in mind.

If the query returns multiple fields or rows, the widget will only store the first row or the first field from the first row of results, depending on the variable type selected for the results. See the Save To Variable property description below for more information.

Widget Properties:

Column	Description
Connection Type (Required)	Select the type of database connection to use. Available choices are SQL Server, ODBC, OLEDB, and Oracle.
Connection String (Required)	For ODBC, OLEDB, and Oracle connection types, manually enter the connection string. For SQL Server connections, click the button to define the following additional properties: Server name: Enter the Server name for your SQL Server database. Authentication: Select Windows Authentication or SQL Server Authentication. Windows Authentication will use the login credentials of the workflow initiator. SQL Server Authentication will use the Username and Password defined in the widget. Username/Password: To connect using SQL Server Authentication, enter a Username and Password. Database: Click the Get Database button to retrieve a list of databases on the specified server, and select the database you want to query. Query: Enter a valid SQL statement. Any SQL statement you enter here will propagate to the Query String property in the main Widget Properties form. Results: To test your database connection, enter a SQL statement in the Query box and click the Test button. For INSERT, UPDATE, or DELETE statements, only the database connection string is tested. For SELECT statements, the query string is also tested, and a result indicating success or failure is shown in the Results field. Statements containing lookups cannot be tested, since lookup values are not determined until the workflow runs. NOTE: You can also test ODBC, OLEDB, and Oracle connection types from this property form.
Query String	Enter a single SQL statement to execute against the database. SELECT statements should be constructed to return a single field from a single row or a single record (row) of results. Click the button to include lookups in your statement.
Save to Variable	Select a workflow variable with a Text or Record data type to store the query results (applicable only for the results of a SELECT statement). Create variables in the Workflow Settings tab in the settings pane, or click the Create new variable link next to the variable list.
Run As	This widget can run with the permissions of the workflow initiator, the workflow designer, or another user. Select the option in the Run As property that best matches your requirement. For more information, see the Common Widget Properties topic.
Click Apply to save the widget settings.

Example Using Query Database Widget

Return to External Data Widgets