SQL_COMMAND
See alsoSends Sql commands using a pre-configured Sql connection.
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.
Some of the modes are asynchronous. The application developer must ensure that the value of the status variable is monitored and take adequate actions when it changes.
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 | INITCONNECTION | 1 |
| 2 | CLOSECONNECTION | 2 |
| 3 | GETCONNECTIONLIST | 3 |
| 4 | GETCONNECTIONINFO | 4 |
| 5 | EXECREADER | 5 |
| 6 | EXECSCALAR | 6 |
| 7 | EXECNONQUERY | 7 |
| 8 | GETCOMMANDLIST | 8 |
| 9 | GETCOMMANDINFO | 9 |
| 10 | READBUFFER | 14 |
| 11 | SEEKBUFFER | 10 |
| 12 | READBUFFERLINE | 11 |
| 13 | READBUFFERCELL | 12 |
| 14 | BUFFERLINECOUNT | 13 |
| 15 | BUFFERFIELDCOUNT | 13 |
| 16 | BUFFERFIELDNAME | 14 |
| 17 | BUFFERFIELDTYPE | 14 |
| 18 | BUFFERSIZE | 13 |
| 19 | READSCALARVALUE | 14 |
| 20 | READAFFECTEDROW | 13 |
| 21 | CANCEL | 13 |
| 22 | DISPOSE | 13 |
Arguments common to more than one mode
|
Argument |
Meaning |
| SqlConnectionName | The name of the Sql Connection as configured in General.Data connections in the Application Explorer. Type STR. |
| BufferHandle | The handle of a buffer as returned by ALLOC_BUFFER. Type LONG. All modes require the buffer to be allocated prior to calling the instruction. |
| SqlCmd | The Sql command to send. Type STR. |
| StatusVariable |
The name of the register variable used to monitor the status of an asynchronous operation. Type STR. Succeeded = 0 |
| ErrortextVariable | The name of a text variable used to return additional information when the status variable value is set to failed (2). Type STR. |
| SubstituteNullValueWithEmptyString |
Substitute a null value with an empty string in returned data. Type INTEGER. 0 - a null value is represented by <NULL> |
| LineNumber |
A line position in the result buffer (first line is LineNumber 0). Type LONG. |
| CmdId | The command Id as returned by mode EXECREADER, EXECSCALAR or EXECNONQUERY. Type INTEGER. |
Syntax 1
IntVal = SQL_COMMAND(Mode, SqlConnectionName [, MaxSimultaneousCommand]);
Return type: INTEGER
|
Argument |
Meaning |
| MaxSimultaneousCommand |
The maximum number of simultaneous asynchronous commands. Type STR. The primary use is to limit the memory used by PcVue for the reply buffer. The default value is 1. The maximum value is 100. If the value is incorrect, the default value is used instead. |
Execution
|
Mode |
Mnemonic |
Action |
| 1 | INITCONNECTION |
Initialize the runtime object necessary to handle the specified Sql connection. It does not actually establish any end-to-end connection with the RDBMS. The Sql connection must be started prior to using this mode. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
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.
Syntax 2
IntVal = SQL_COMMAND(Mode[, SqlConnectionName]);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 2 | CLOSECONNECTION |
Free-up resources associated to the runtime object corresponding to the specified Sql connection. If the SqlConnectionName argument is omitted, all open connections will be closed. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Syntax 3
IntVal = SQL_COMMAND(Mode, BufferHandle);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 3 | GETCONNECTIONLIST |
Get the name of all open Sql connections and return them in a buffer. Connection names are delimited with the comma character. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Syntax 4
IntVal = SQL_COMMAND(Mode, ConnectionParam, SqlConnectionName, BufferHandle);
Return type: INTEGER
|
Argument |
Meaning |
| ConnectionParam |
The sub mode. Type INTEGER or STR. 1 or "NB_COMMAND" Get the number of active commands, e.g. ones that are not yet disposed of using the DISPOSE mode. |
Execution
|
Mode |
Mnemonic |
Action |
| 4 | GETCONNECTIONINFO |
Return information about the number of commands. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Syntax 5
IntVal = SQL_COMMAND(Mode, SqlConnectionName, SqlCmd, StatusVariable, ErrorTextVariable, LineSeparator, FieldSeparator [, SubstituteNullValueWithEmptyString]);
Return type: INTEGER
|
Argument |
Meaning |
| LineSeparator | The line separator character to be used in the buffer. Type STR. |
| FieldSeparator | The field separator character to be used in the buffer. Type STR. |
Execution
|
Mode |
Mnemonic |
Action |
| 5 | EXECREADER |
Execute a Sql query designed to return a set of records and store the result in a buffer. The contents are separated by the line and field separator arguments. Return: Either a positive value representing the command Id, or a negative value indicating one of the following errors. See the table below for return values. |
This mode is asynchronous. The program must monitor the value of the status variable and use either modes READBUFFER, READBUFFERLINE or READBUFFERCELL to get the buffer result.
Syntax 6
IntVal = SQL_COMMAND(Mode, SqlConnectionName, SqlCmd, StatusVariable, ErrorTextVariable [, SubstituteNullValueWithEmptyString]);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 6 | EXECSCALAR |
Execute a Sql query designed to return a scalar value and store the scalar result in a buffer. The result is a single value. Return: Either a positive value representing the command Id, or a negative value indicating one of the following errors. See the table below for return values. |
This mode is asynchronous. The program must monitor the value of the status variable and use mode READSCALAR to get the value.
Syntax 7
IntVal = SQL_COMMAND(Mode, SqlConnectionName, SqlCmd, StatusVariable, ErrorTextVariable);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 7 | EXECNONQUERY |
Execute a Sql request that does not query data from the database, but causes a certain operation to be executed on the data source side (as defined by the SqlCmd argument). Such requests usually return the result of the operation execution. Return: Either a positive value representing the command Id, or a negative value indicating one of the following errors. See the table below for return values. |
This mode is asynchronous. The program must monitor the value of the status variable and use the mode READAFFECTEDROW to get the number of rows affected by the command.
Syntax 8
IntVal = SQL_COMMAND(Mode, SqlConnectionName, BufferHandle);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 8 | GETCOMMANDLIST |
Retrieve the command list associated with a connection. The command Ids are returned in the buffer separated by commas. Return: Either a positive value representing the number of active commands, or a negative value indicating one of the following errors. SQLCONNAME_PARAM_NOT_STR_OR_NOT_READ See the table below for return values. |
Syntax 9
IntVal = SQL_COMMAND(Mode, RequestParam, CmdId, BufferHandle);
Return type: INTEGER
|
Argument |
Meaning |
| RequestParam |
The sub mode. Type INTEGER or STR. 1 or "TYPE". |
Execution
|
Mode |
Mnemonic |
Action |
| 9 | GETCOMMANDINFO |
Retrieve information, in a buffer, about a command specified by its CmdId. The information returned depends on the sub mode. TYPE - Retrieves the type of command that has been sent. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Syntax 10
IntVal = SQL_COMMAND(Mode, CmdId, LineNumber);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 11 | SEEKBUFFER |
Used to seek a line position in the buffer result, once the status variable is set to 0. First line is LineNumber 0. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Syntax 11
IntVal = SQL_COMMAND(Mode, CmdId, BufferHandle, LineNumber);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 12 | READBUFFERLINE |
Read a specific line in the buffer, produced as a result of the use of an EXECREADER function, once the status variable is set to 0. First line is LineNumber 0. Return: Either a positive value representing the number of lines read, or a negative value indicating one of the following errors. See the table below for return values. |
Syntax 12
IntVal = SQL_COMMAND(Mode, CmdId, BufferHandle, LineNumber, FieldNumber);
Return type: INTEGER
|
Argument |
Meaning |
| FieldNumber |
A field position in a line of the result buffer (first field is FieldNumber 0). Type LONG. |
| LineNumber | A line position in the result buffer . (first line is LineNumber 0). Type LONG. |
Execution
|
Mode |
Mnemonic |
Action |
| 13 | READBUFFERCELL |
Read a specific field in a specific line of the buffer, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: Either a positive value representing the number of lines read, or a negative value indicating one of the following errors. See the table below for return values. |
Syntax 13
IntVal = SQL_COMMAND(Mode, CmdId);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 14 | BUFFERLINECOUNT |
Get the number of lines in the buffer, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: Either zero or a positive value representing the number of lines, or a negative value indicating one of the following errors. See the table below for return values. |
| 15 | BUFFERFIELDCOUNT |
Get the number of fields in the buffer, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: Either zero or a positive value representing the number of fields, or a negative value indicating one of the following errors. See the table below for return values. |
| 18 | BUFFERSIZE |
Get the size of the buffer, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: A positive value representing the number of bytes in the buffer, or a negative value indicating one of the following errors. See the table below for return values. |
| 20 | READAFFECTEDROW |
Read the result of a EXECNONQUERY function, once the status variable is set to 0. Return: A positive value representing the affected row count, or a negative value indicating one of the following errors. See the table below for return values. |
| 21 | CANCEL |
Cancel the request corresponding to the Cmdld. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
| 22 | DISPOSE |
Release the context of a query including the internal buffer, corresponding to the Cmdld. Must be called after request result is processed to free up resources. The buffer result is no longer usable after the execution of the DISPOSE mode. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
Failing to call the mode DISPOSE once you have completed a command and the processing of its result prevents from freeing up memory and may lead to an uncontrolled usage of computer resources.
Syntax 14
IntVal = SQL_COMMAND(Mode, CmdId, BufferHandle);
Return type: INTEGER
Execution
|
Mode |
Mnemonic |
Action |
| 10 | READBUFFER |
Used to read the result of EXEC_READER query once the status variable value is set to 0. It may be necessary to run this function one or more times depending on the size of the buffer and the quantity of data returned. Return: Either a positive value representing the number of lines read, or a negative value indicating one of the following errors. See the table below for return values. |
| 16 | BUFFERFIELDNAME |
Get the header names, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
| 17 | BUFFERFIELDTYPE |
Get the datatypes corresponding to the fields, produced as a result of the use of the EXECREADER function, once the status variable is set to 0. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
| 19 | READSCALARVALUE |
Read the scalar value, produced by an EXECSCALAR function, once the status variable is set to 0. Return: 0 if successful, else a negative number indicating one of the following errors. See the table below for return values. |
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 | SQLCMD_PARAM_NOT_STR_OR_NOT_READ | The parameter SqlCmd is not of type string or could not be read |
| -3 | STATUSVARIABLE_PARAM_NOT_STR_OR_NOT_READ | The parameter StatusVariable is not of type string or could not be read |
| -4 | LINESEPARATOR_PARAM_NOT_STR_OR_NOT_READ_OR_TOOLONG | The parameter LineSeparator is not of type string, could not be read or is too long |
| -5 | FIELDSEPARATOR_PARAM_NOT_STR_OR_NOT_READ_OR_TOOLONG | The parameter FieldSeparator is not of type string, could not be read or is too long |
| -6 | BUFFERHANDLE_PARAM_INVALID_OR_NULL_OR_NULLSIZE | The parameter BufferHandle is invalid, Null or of Null size. The buffer allocation using ALLOC_BUFFER is probably missing or failed to execute properly |
| -7 | LINENUMBER_PARAM_INVALID | The parameter LineNumber is invalid |
| -8 | FIELDNUMBER_PARAM_INVALID | The parameter FieldNumber is invalid |
| -9 | ERRORTEXTVARIABLE_PARAM_NOT_STR_OR_NOT_READ | The parameter ErrorTextVariable is not of type string or could not be read |
| -10 | CONNECTION_PARAM_INVALID | The parameter ConnectionParam is invalid |
| -11 | COMMAND_PARAM_INVALID | The parameter CommandParam is invalid |
| -12 |
SQLCON_DOES_NOT_EXIST |
The specified Sql connection does not exist in the configuration |
| -13 |
STATUSVARIABLE_NOT_REGISTER_OR_DOES_NOT_EXIST |
The status variable passed as parameter (StatusVariable) is not a register variable or it does not exist |
| -14 |
ERRORTEXTVARIABLE_NOT_TEXT_OR_DOES_NOT_EXIST |
The additional information variable passed as parameter (ErrorTextVariable) is not a text variable or it does not exist |
| -15 | CONNECTION_ALREADY_INIT | The connection is already initialized |
| -16 | CONNECTION_INIT_FAILED | The connection could not be initialized |
| -17 | CONNECTION_ALREADY_CLOSE_OR_NOT_INIT | The connection is already closed or not initialized |
| -18 | CONNECTION_CLOSE_FAILED | Failed to close the connection |
| -19 | 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 |
| -20 | MAXIMUM_COMMAND_REACH | The maximum number of outstanding command is reached |
| -21 | MAXIMUM_CONNECTION_REACH | The maximum number of simultaneous connections is reached |
| -22 | CMDID_DOES_NOT_EXIST | The command CmdId passed as parameter does not exist |
| -23 | CMD_TYPE_NOT_VALID | The command type is invalid. For example, this error is raised when using the READ_BUFFER with a Command Id corresponding to a request of type non-query or scalar |
| -24 | CMD_NOT_FINISHED | The command is not yet completed |
| -25 | CMD_NOT_REMOVED | The command could not be removed |
| -26 | RESULT_BUFFER_EMPTY | The result buffer is empty |
| -27 | RESULT_BUFFER_TOOSMALL | The result buffer is too small |
| -28 | RESULT_BUFFER_REACH_NBLINE_MAX | Failure to return information about the number of lines in the bufffer because it exceeds the return value range |
| -29 | RESULT_BUFFER_REACH_SIZE_MAX | Failure to return information about the buffer size because it exceeds the return value range |