SQL Execution Procedures

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:

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:

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:

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.

Parameters

This table describes the parameter:

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:

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:

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:

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:

Syntax

RETCODE SQLNumParams(hstmt,pccol)

Description

SQLNumParams returns the number of parameters in a SQL statement.

Parameters

This table describes the parameters:

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:

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:

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:

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.