SQL Execution Procedures
This topic discusses the SQL execution procedures in alphabetical order.
Syntax
RETCODE SQLAllocStmt(hdbc,phstmt)
Description
SQLAllocStmt allocates memory for a statement handle and associates the statement handle with the connection that is specified by the connection handle.
If the application calls SQLAllocStmt with a pointer to a valid statement handle, the driver overwrites the statement handle without regard to its previous contents.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hdbc |
HDBC |
Input |
Connection handle. |
phstmt |
HSTMT FAR* |
Output |
Pointer to storage for the statement handle. |
Syntax
RETCODE SQLExecDirect(hstmt,szSqlStr,cbSqlStr)
Description
SQLExecDirect executes a preparable statement, using the current values of the parameter marker variables if any parameters exist in the statement. The application calls SQLExecDirect to send a SQL statement to the data source. The driver modifies the statement to use the form of SQL used by the data source, then submits it to the data source. In particular, the driver modifies the escape clauses that are used to define ODBC-specific SQL grammar extensions.
The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the application embeds a question mark (?) into the SQL statement at the appropriate position. It is unnecessary to use any parameter markers, as PeopleSoft Query objects know the exact number of prompt values. The PeopleSoft driver prompts the user for input values if no values were supplied through the input or the SQLBindParameter function.
Only stored procedures (predefined queries) are supported.
In addition to the ODBC error conditions, the PeopleSoft driver returns an error condition if the following conditions are true:
A valid PeopleSoft query name cannot be found or loaded.
Prompt values cannot be satisfied using a prompting page.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
szSqlStr |
UCHAR FAR* |
Input |
SQL statement to be executed. |
cbSqlStr |
SDWORD |
Input |
Length of szSqlStr. |
Syntax
RETCODE SQLPrepare(hstmt,szSqlStr,cbSqlStr)
Description
SQLPrepare prepares a SQL string for execution. The application calls SQLPrepare to send a SQL statement to the data source for preparation. The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the application embeds a question mark (?) into the SQL string at the appropriate position. After a statement is prepared, the application uses hstmt to refer to the statement in later function calls. The prepared statement that is associated with the hstmt may be executed again by calling SQLExecute until the application frees the hstmt with a call to SQLFreeStmt with the SQL_DROP option or until the hstmt is used in a call to SQLPrepare, SQLExecDirect, or one of the catalog functions (SQLColumns, SQLTables, and so on). After the application prepares a statement, it can request information about the format of the result set.
Only stored procedures (predefined queries) are supported.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
szSqlStr |
UCHAR FAR* |
Input |
SQL statement to be executed. |
cbSqlStr |
SDWORD |
Input |
Length of szSqlStr. |
Syntax
RETCODE SQLExecute(hstmt)
Description
SQLExecute executes a prepared statement, using the current values of the parameter marker variables if any parameter markers exist in the statement. SQLExecute executes a statement prepared by SQLPrepare. After the application processes or discards the results from a call to SQLExecute, the application can call SQLExecute again with new parameter values.
To execute a Select statement more than once, the application must call SQLFreeStmt with the SQL_CLOSE parameter before reissuing the Select statement.
As in the SQLExecDirect function, the PeopleSoft ODBC driver prompts the user for input values if they have not been supplied.
Note: For applications to be flexible enough for using SQL for a particular purpose, you must provide the application a means to query the ODBC driver for information pertaining to required storage and data types. This is done using descriptive functions defined by the ODBC specification. ODBC-enabled applications use these functions to dynamically query the driver for information about the result set and the input and output values.
Parameters
This table describes the parameter:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
Syntax
RETCODE SQLColAttributes(hstmt,icol, fDescType,rbgDesc,cbValueMax,pcbValue)
Description
SQLColAttributes returns descriptor information for a column in a result set; it cannot be used to return information about the bookmark column (column 0). Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
icol |
UWORD |
Input |
Column number of result data. |
fDescType |
UWORD |
Input |
Valid descriptor type. |
rbgDesc |
PTR |
Output |
Pointer to storage for descriptor information. |
cbValueMax |
SWORD |
Input |
Maximum buffer size. |
pcbValue |
SWORD FAR* |
Output |
Output length of data in buffer. |
Syntax
RETCODE SQLDescribeCol(hstmt,icol,szColName,cbColNameMax,pcbColName,pfSqlType,pcbColDef,pibScale,pfNullable)
Description
SQLDescribeCol returns the result descriptor, column name, type, precision, scale, and null-ability for one column in the result set. An application typically calls SQLDescribeCol after a call to SQLPrepare and before or after the associated call to SQLExecute. An application can also call SQLDescribeCol after a call to SQLExecDirect.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
icol |
UWORD |
Input |
Column number of result data. |
szColName |
UCHAR FAR* |
Output |
Pointer to storage for the column name. |
cbColNameMax |
SWORD |
Input |
Maximum length of the szColName buffer. |
pcbColName |
SWORD FAR* |
Output |
Total number of bytes available to return in szColName. |
pfSqlType |
SWORD FAR* |
Output |
The SQL data type of the column. |
pcbColDef |
UDWORD FAR* |
Output |
The precision of the column on the data source. |
pibScale |
SWORD FAR* |
Output |
The scale of the column on the data source. |
pfNullable |
SWORD FAR* |
Output |
Indicates whether the column allows NULL values. |
Syntax
RETCODE SQLDescribeParam(hstmt,ipar, pfSqlType,pcbColDef,pibScale,pfNullable)
Description
SQLDescribeParam returns the description of a parameter marker that is associated with a prepared SQL statement. In terms of PeopleSoft Query objects, this is the description of the prompt values required to fulfill the query keys.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
ipar |
UWORD |
Input |
Marker number. |
pfSqlType |
SWORD FAR* |
Output |
Pointer to storage for the SQL type. |
pcbColDef |
SWORD FAR* |
Output |
Pointer to storage for precision of value. |
pibScale |
SWORD FAR* |
Output |
Pointer to storage for scale of value. |
pfNullable |
UDWORD FAR* |
Output |
Pointer to storage for nullable flag. |
Syntax
RETCODE SQLRowCount(hstmt,pcrow)
Description
SQLRowCount returns the number of rows affected by an Update, Insert, or Delete statement or by a SQL_UPDATE, SQL_ADD, or SQL_DELETE operation in SQLSetPos. If SQLRowCount is called during a fetch cycle, the value returned is the number of rows returned to the application at the current position.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
pcrow |
SDWORD FAR * |
Input |
Pointer to storage of the row counter. |
Syntax
RETCODE SQLNumParams(hstmt,pccol)
Description
SQLNumParams returns the number of parameters in a SQL statement.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
pccol |
SWORD FAR* |
Output |
Number of parameters in the statement. |
Syntax
RETCODE SQLNumResultCols(hstmt,pccol)
Description
SQLNumResultCols returns the number of columns in the result set. SQLNumResultCols can be called successfully only when the statement handle is in the prepared or executed state. An application typically would use the value returned in pccol in a loop and call SQLDescribeCol for each column in the result set.
An application retrieves an entire row of values using a technique called binding. Binding associates the data from the data source with variables in the application program. There are two directions of binding: input and output. Input data must always be bound. On output, when a result column is bound, the variable receives the value for that column each time a new row is fetched. The following example shows how this technique differs from SQLGetData:
/* for all columns in the current result set */
for (i = 0; i < columns; i++)
SQLBindCol(hstmt, ...,&value[i], ...)
while (SQL_SUCCESS == (rc = SQLFetch(hstmt)))
/* value[ i .. n] contains data for current row */
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
pccol |
SWORD FAR* |
Output |
Number of columns in the result set. |
Syntax
RETCODE SQLBindCol(hstmt,icol,fCType,rbgValue,cbValueMax,pcbValue)
Description
SQLBindCol assigns the storage and data type for a column in a result set.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
icol |
UWORD |
Input |
Column number of result data. |
fCType |
SWORD |
Input |
The C data type of the result data. |
rbgValue |
PTR |
Both |
A pointer to storage for the result column. |
cbValueMax |
SDWORD |
Input |
Maximum length of the rgbValue buffer. |
pcbValue |
SDWORD FAR* |
Both |
A pointer to a buffer for the SQL_NULL_DATA or the number of bytes available to return in rgbValue prior to calling SQLFetch. |
Syntax
RETCODE SQLBindParameter(hstmt,ipar, fParamType,fCType,fSqlType,cbColDef, ibScale,rbgValue,cbValueMax,pcbValue)
Description
An application calls SQLBindParameter to bind each parameter marker in a SQL statement. Bindings remain in effect until the application calls SQLBindParameter again or until the application calls SQLFreeStmt with the SQL_DROP or SQL_RESET_PARAMS option.
An application can use SQLBindParameter to supply the prompt values for a PeopleSoft query. SQLBindParameter calls the new function, ODBCBindParm. ODBCBindParm converts the ODBC C data type, fCType, to the ODBC SQL data type, fSqlType. It then maps the ODBC SQL data type to a supported PeopleSoft RDM data type and calls the appropriate internal bind function.
An ODBC driver is required to support conversions from all ODBC C data types to the ODBC SQL data types that they support.
Parameters
This table describes the parameters:
Argument |
Type |
Use |
Description |
---|---|---|---|
hstmt |
HSTMT |
Input |
Statement handle. |
ipar |
UWORD |
Input |
Parameter number, ordered sequentially left to right, starting at 1. |
fParamType |
SWORD |
Input |
The type of the parameter. |
fCType |
SWORD |
Input |
The C data type of the parameter. |
fSqlType |
SWORD |
Input |
The SQL data type of the parameter. |
cbColDef |
UDWORD |
Input |
The precision of the column or expression of the corresponding parameter marker. |
ibScale |
SWORD |
Input |
The scale of the column or expression of the corresponding parameter marker |
rbgValue |
PTR |
Both |
A pointer to a buffer for the parameter’s data. |
cbValueMax |
SDWORD |
Input |
Maximum length of the rgbValue buffer. |
pcbValue |
SDWORD FAR* |
Both |
A pointer to a buffer for the parameter’s length. |
An application may also supply prompt values as literal strings embedded in the SQL statement string. For example:
SQLExecDirect(hstmt, "{call query.myquery(8001, NEWGN)}", SQL_NTS);
If prompt values are not provided, PeopleSoft Query prompts the user for each required value at the time of statement execution.