SQL_QUERY

Concept Link IconSee also Concept Link IconExample

Used to execute a specific Sql Query (read or write). It is essential to manage synchronization problems between substitution parameters and trigger.

WebVue support - Yes.

The use of pre-configured Sql connections along with the instructions SQL_CONNECTION, SQL_COMMAND and SQL_QUERY is preferred to using the verb SVSQL (based on ODBC).

Pre-configured Sql connections are configured in the Application Explorer. See the Data Connections book for information.

This verb can be executed either locally on the producer station of the Sql connection or from any client station of the producer. In the latter case, commands and returned data are routed transparently through the multi-station messaging system. Multiple connections to the same data source are only allowed if using different Sql Connections. Multiple connections using the same Sql Connection will be refused, however, multiple simultaneous requests using the same Sql Connection is possible.

A connection, and the data associated to it, are managed in the context of user sessions. A connection should only be handled in the context of the session that had originally initiated it; and all related objects must be properly released/closed before the session is ended. For example, it is not possible to initiate a connection at start up and have requests executed in any user context.

 

Mode Mnemonic Syntax
1 EXECUTE 1

 

Syntax 1

IntVal = SQL_QUERY(Mode, SqlConnectionName, SqlQueryName [,Param01…[,Param10]]);

Return type: INTEGER

Argument

Meaning

SqlConnectionName

The name of a Sql connection as configured in General.Data connections in the Application Explorer. Type STR.

SqlQueryName

The name of a Sql query subordinate to the Sql connection. Type STR.
Parm01....

A list of values to overwrite query parameters. Parameters are substituted in order: ?Param1, ?Param2, ?Param3, ?Param4, etc. Type STR.

If a value is missing, the configured substitution is used.

A parameter value can be an expression as supported for pre-defined query configuration.

Execution

Mode

Mnemonic

Action

1 EXECUTE

Execute the name Sql query. It is equivalent to the Trigger query with parameters task available in the Application Explorer.

Return: 0 if successful, else a negative number indicating an error. See the table below for return values.

The mode EXECUTE is asynchronous. The application developer must ensure that status variables are properly configured for the query and used to follow the execution progress, success or failure.

List of possible return values and meanings

Return value Enum Description
0 OPERATION_SUCCEEDED Operation completed successfully.
-1 SQLCONNAME_PARAM_NOT_STR_OR_NOT_READ The parameter SqlConnectionName is not of type string or could not be read.
-2 SQLQUERYNAME_PARAM_NOT_STR_OR_NOT_READ The parameter SqlQueryName is not of type string or could not be read.
-3 SQLCON_DONT_EXISTS The specified Sql connection does not exist in the configuration.
-4 SQLQUERY_DONT_EXISTS The specified Sql query does not exist in the configuration.
-5 SQLQUERY_TOO_MANY_PARAM The maximum number of parameters (21) has been exceeded.
-6 SQLQUERY_PARAM_UNSUPPORTED_TYPE The type of a parameter is not supported.
-7 SEND_COMMAND_FAILED Failed to send the Sql command. This error occurs if the underlying Windows service is stopped or if the user does not have enough privileges to execute the command.