 |
|
|
 | Client Application Variables in the SQL Statements |  | Client Application Variables in the SQL Statements |
If the client application is created in an external programming language (C/C++, Pascal), the SQL statement containing client variables must be sent to the server using the SQL_host_execute or SQL_host_prepare API function. The variables must be made explicitly accessible using the hostvars parameter.
The hostvars parameter is an array of elements (t_clivar type) that describe the individual client variables used in the SQL statement. The number of array elements is specified in the hostvars_count parameter. The t_clivar is a structure defined as follows:
struct t_clivar // description of a client variable accessible for the 602SQL Server
{ tname name; // variable name (in uppercase)
t_parmode mode; // mode of variable transfer
int wbtype; // variable type
t_specif specif; // supplemental information about the variable type
void * buf; // pointer to the buffer with the variable value
int buflen; // length of the buf buffer
int actlen; // real value length - valid only for variable-length types
};type t_clivar = record
name : tname; { variable name (in uppercase)
mode : t_parmode; { mode of variable transfer }
wbtype : Integer; { variable type }
specif : t_specif; { supplemental information about the variable type (string length, number of decimal digits,...)}
buf : Pointer; { pointer to the buffer with the variable value }
buflen : Integer; { length of the buf buffer }
actlen : Integer; { real value length - valid only for variable-length types }
end;
Types and values of the items used in this structure are defined in the definition files for the individual programming language. You must specify the real parameter value actlen along with the buffer length buflen for input variables of variable-length types (BLOB, CLOB). The actlen value does not have to be specified for output variables of variable-length types (BLOB, CLOB). The actual parameter value appears in the structure after the statement is executed.
The specif item contains supplemental information about the variable type. It specifies the t_specif structure contents written as a 32-bit integer number. It is equal to the maximum string length, charset and UNICODE flag for character strings with a fixed length, string length for binary strings with a fixed length, or number of decimal digits for precise numbers (NUMERIC). Otherwise it is zero.
The < and > flags specify the value transfer direction in a SQL statement and do not have to be specified, since the same information is set in the mode item. However, if these flags are set, they will be ignored.
Delphi developers who use the CDK components have this functionality embedded in the TSQL602DataSet, TSQL602Statements and TSQL602StoredProc classes. The XML API can be found in the TSQL602XML class.
PHP developers are advised to take a look here for the description of how to pass client variables into 'prepared' SQL statements, and here for a description of the XML API.
The value length does not need to be known for the output value of a variable-length type (BLOB, CLOB) before a statement is executed.
It is recommended to set the buffer pointer buf to NULL. In this case, the buflen value will be ignored. When the statement is executed, the buffer of the required length is automatically allocated and is returned in the buf value. The buffer must be freed later manually using the corefree function.
Example:
Calling the Maxim_salary function with one input parameter and the return value in the Max_salary variable:
t_clivar vardescr[2];
int personal_id, max_salary;
strcpy(vardescr[0].name, "PERSONAL_ID");
vardescr[0].mode=MODE_IN;
vardescr[0].wbtype=ATT_INT32;
vardescr[0].specif=0;
vardescr[0].buf=&personal_id;
vardescr[0].buflen=sizeof(personal_id);
strcpy(vardescr[1].name, "MAX_SALARY");
vardescr[1].mode=MODE_OUT;
vardescr[1].wbtype=ATT_INT32;
vardescr[1].specif=0;
vardescr[1].buf=&max_salary;
vardescr[1].buflen=sizeof(max_salary);
personal_id = 1294;
cd_SQL_host_execute(cdp, "SET :>MAX_SALARY = MAXIM_SALARY(:<PERSONAL_ID)", NULL, vardescr, 2);
// the result is in the max_salary variable. | Client Application Variables in the SQL Statements | | Client Application Variables in the SQL Statements |