Skip to main content

SQLQuery

Suggest edit Updated on November 10, 2021

Use the SQLQuery when the data source resides in a MS SQL Server database.

The Query components return a dataset or execute a command (such as insert or update). If the Query returns data, the dataset is created as part of the Query component. You can display the returned data by either connecting the Query GetTable method or by specifying the Query as the provider for a TableView.

The Connection String property provides access to the Microsoft Data Link Properties window. Use this series of dialogs to identify the data source and create a connection string.

When you add this component to an automation, the system places it on the Global tab of the component tray. For more information, see Adding Global Components to an Automation. This component must be global to be referenced by other automations in the project.

For more information, see General Component Properties, Methods, and Events.

SQLQuery properties

PropertyDescription
CommandText

The SQL statement. Use the colon (:) for parameters in the statement. Here is an example:

Select * from Orders Where CustomerID = @CustomerID 

Note: Do not dynamically change the CommandText if there are parameters in the command.

CommandTimeoutThe amount of time you want the solution to wait to execute the query. The default is 30 seconds.
CommandTypeSpecifies how the system interprets CommandText.
  • Use Text when the Command Text is a SQL Select statement.
  • Use StoredProcedure when the Command Text is the name of a stored procedure.
  • Use TableDirect when the CommandText is the name of a table.
ConnectionString

The string used to open the data source. To create the Connection String, browse this property to open the Microsoft Data Link Properties window. Click Help for more information. Here is an example of a database connection:

Data link properties

The settings shown above create the following ConnectionString:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL11.SQLEXPRESS\MSSQL\DATA\CHINOOK.MDF;Data Source=WIN7PRO64\SQLexpress

Parameters

Browse this property to open the SqlParameter Collection Editor dialog and set default values for parameters used in the CommandText. The size of the parameter that you enter when you test the query sets the allowable runtime size limit.

Note that you must test the query for the query parameters to be fully defined in the project. If you run the project without testing the query in Studio, an error is thrown. Click the Test Query link to test the query with the parameters.

ReturnTypeIndicates if the query will return rows of data, a scalar value or no data at all.

Methods

MethodDescriptionParametersReturn Type
ExecuteRuns the SQL statement against the designated datasource As specified in the SQL statement Void
GetTableUse this method to return a data table containing the query data. NoneDataTable
Did you find this content helpful? YesNo

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us