A class for preparing and executing SQL statements.
A prepared SQL statement can be parsed and contain input and output parameters. Parameters are marked with a '?' or ':<name>' tag. All DML commands can be parsed. DDL commands can be parsed, too. However, it is not recommended to do so. Prepared SQL statements increase the performance since they are parsed only once and executed several times. Applications only need to change the content of the bound parameters and execute the command again.
All prepared SQL statements are stored in an internally managed ParseInfo Cache . The ParseInfo Cache shares this information with different prepared SQL statements within the same connection.
Use the prepare() member function to prepare the SQL statement.
Use the execute() member function to execute the previously prepared SQL statement.
The SQL statement may contain ASCII or UCS2 characters and must not zero-terminated. The execute() member function converts it to the adequate code set considering the code of the database. Therefore it is possible to write portable code for UNICODE and non-UNICODE databases.
Note:
To increase the performance, applications must use UCS2 statements for UNICODE databases only.
Parameters can be bound to the statement column-wise or row-wise. The default binding type is column-wise. To use row-wise binding, applications must use the method setBindingType() with the size in bytes of the date structure to be bound.
Example:
Binding parameters column-wise and insert some data into a table using mass insert.
int i;
for (i = 0; i < 15; i++) {
rows[i].key_ind = (SQLDBC_Length) sizeof(SQLDBC_Int4);
rows[i].name_ind = (SQLDBC_Length) strlen((const char *) rows[i].name);
rows[i].zip_ind = (SQLDBC_Length) strlen((const char *) rows[i].zip);
rows[i].address_ind = (SQLDBC_Length) strlen((const char *) rows[i].address);
}
prepstmt->execute ();
Hints:
The application must call createPreparedStatement() to retrieve a prepared SQL statement
For parsing the SQL statement, call the prepare() member function,
For a single-row execution, use the execute() member function,
For mass execution, bind arrays of parameter values, use setBatchSize() to set the row array size to the number of array elements bound, and use the execute() method.
Use the isQuery() method to check whether the parsed statement creates a result set upon execution.
Use SQLDBC_Statement for a direct execution, since the prepared statement inherits all direct-execution functions from SQLDBC_Statement.
Binds a user supplied memory buffer to an input and/or output parameter of an SQL statement.
A parameter is a '?' or ':<name>' placeholder in an SQL statement. Applications should use SQLDBC_ParameterMetadata to retrieve information about the type and length of the parameters in an SQL statement.
Parameters:
Index
[in] Index of the parameter, starting with 1
Type
[in] Host type of the parameter
paramAddr
[in] A pointer to a buffer for the parameter's data
LengthIndicator
[in|out] Pointer to parameter length or indicator.
Input parameter:
For non-integral (character and binary data) input parameters, The LengthIndicator argument must specify the length of the parameter data in bytes, or the indicator value SQLDBC_NULL_DATA to assign a NULL value to a column. The parameter length may also be given by the special length SQLDBC_NTS or by passing a NULL pointer argument when the buffer points to a zero-terminated character string. If the Size argument is not zero SQLDBC computes the number bytes by searching the terminator character in first Size bytes. If the value of Size is zero SQLDBC searches an unlimited number of bytes for the occurrences of a termination character to determine the length.
Output parameter:
For output parameters, LengthIndicator stores the column length or the indicator value SQLDBC_NULL_DATA if the column contains the NULL value. For character data it contains on success the number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator, if the Terminate flag was set. If the source string exceeds the Size value SQLDBC_DATA_TRUNC will be returned and LengthIndicator is set to the number of bytes (except the terminator bytes) needed to copy without truncation.
Binds an input and/or output parameter by address.
Binding by adress is useful when the application uses buffers that are allocated as an array of pointers (C function calloc)
A parameter is a '?' or ':<name>' placeholder in an SQL statement. Applications should use SQLDBC_ParameterMetadata to retrieve information about the type and length of the parameters in a SQL statement.
Parameters:
Index
[in] Index of the parameter, starting with 1.
Type
[in] Host type of the parameter
paramAddr
[in] Pointer to a pointer for the parameter's data.
LengthIndicator
[in|out] Pointer to parameter length or indicator.
Input parameter:
For non-integral (character and binary data) input parameters, The LengthIndicator argument must specify the length of the parameter data in bytes, or the indicator value SQLDBC_NULL_DATA to assign a NULL value to a column. The parameter length may also be given by the special length SQLDBC_NTS or by passing a NULL pointer argument when the buffer points to a zero-terminated character string. If the Size argument is not zero SQLDBC computes the number bytes by searching the terminator character in first Size bytes. If the value of Size is zero SQLDBC searches an unlimited number of bytes for the occurrences of a termination character to determine the length.
Output parameter:
For output parameters, LengthIndicator stores the column length or the indicator value SQLDBC_NULL_DATA if the column contains the NULL value. For character data it contains on success the number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator, if the Terminate flag was set. If the source string exceeds the Size value SQLDBC_DATA_TRUNC will be returned and LengthIndicator is set to the number of bytes (except the terminator bytes) needed to copy without truncation.
Executes an ASCII coded zero-terminated SQL statement.
The SQL statement is executed immediately on sending a DBS request to the database. In case of an error, the SQLDBC_ErrorHndl object (to be queried using error()) of this object is set. If the executed SQL statement is a query, a result set is created. The application can check this using isQuery(), and obtain retrieve the resultset using getResultSet().
Parameters:
sql
The zero-terminated ASCII coded SQL statement to be execute.
Executes an UNICODE/ASCII coded zero-terminated SQL statement.
The SQL statement is executed immediately on sending a DBS request to the database. In case of an error, the SQLDBC_ErrorHndl object (to be queried using error()) of this object is set. If the executed SQL statement is a query, a result set is created. The application can check this using isQuery(), and obtain retrieve the resultset using getResultSet().
Parameters:
sql
The zero-terminated SQL statement to be executed. The coding of this string argument depends from the encoding argument.
The SQL statement is executed immediately on sending a DBS request to the database. In case of an error, the SQLDBC_ErrorHndl object (to be queried using error()) of this object is set. If the executed SQL statement is a query, a result set is created. The application can check this using isQuery(), and obtain retrieve the ResultSet using getResultSet().
Parameters:
sql
The SQL statement to be executed. The coding of this string argument depends from the encoding argument.
The buffer into which the zero-terminated cursor name is copied.
encoding
Encoding of the buffer where the cursor name is stored.
bufferSize
Maximum size of the buffer in bytes.
bufferLength
[out] Number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator. If the source string exceeds the bufferSize. SQLDBC_DATA_TRUNC will be returned and the bufferLength set to the number of bytes (except terminator bytes) needed to copy without truncation.
Retrieves the key that was inserted by the last insert operation.
Parameters:
tag
The tag that describes whether to get the last or the first serial key. One of SQLDBC_FIRST_INSERTED_SERIAL, SQLDBC_LAST_INSERTED_SERIAL.
type
The output host type.
paramAddr
The parameter address.
lengthIndicator
The length or indicator value.
size
The size of the output parameter, in bytes.
terminate
Whether output strings are terminated. Default is SQLDBC_TRUE.
Returns:
SQLDBC_OK, if the value is returned, SQLDBC_NOT_OK if an error is set, SQLDBC_NO_DATA_FOUND if the statement didn't inserted any key, or didn't report them to the interface.
Retrieves and converts the value with an start offset in of the specified output column from a of the current row to a buffer.
The specified column value in the current row of this SQLDBC_PreparedStatement object is converted to the given length and SQLDBC_HostType and written to the output parameter buffer pointed to paramAddr.
It can be called multiple times to retrieve character or binary data in parts. For fixed-length datatypes getObject retrieves the same data multiple times. Mixing variable-length datatypes and fixed-length datatypes may produce unexpected results.
Parameters:
Index
Index of the column. The first column is column number 1, the second is column number 2, ...
Type
Parameter type of the output buffer.
paramAddr
A pointer to the parameters output buffer.
LengthIndicator
[out] Pointer to a variable that stores the column length or the indicator value SQLDBC_NULL_DATA if the column contains the NULL value. For character data it contains on success the number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator, if the Terminate flag was set. If the source string exceeds the Size value SQLDBC_DATA_TRUNC will be returned and LengthIndicator is set to the number of bytes (except the terminator bytes) needed to copy without truncation.
Size
[in] Length of the parameter buffer in bytes. The Size argument is only necessary for non-integral data types. For character data the Size argument must be large enough to store the terminator byte(s) if the Terminate flag is set.
StartPos
[in] Start position in the column from which on the data should be retrieved. Start position is counted in bytes from 1. Negative StartPos counts from the end of the column.
Terminate
[in] Specifies that the output buffer must be finished with a C-style zero-terminator. The Terminate flag works only for the host var type character (ASCII, UCS2 or UTF8). As a default, all character data is zero-terminated.
Retrieves and converts the value of the specified output column of the current row to a buffer.
The specified column value in the current row of this SQLDBC_PreparedStatement object is converted to the given length and SQLDBC_HostType and written to the output parameter buffer pointed to paramAddr.
It can be called multiple times to retrieve character or binary data in parts. For fixed-length datatypes getObject retrieves the same data multiple times. Mixing variable-length datatypes and fixed-length datatypes may produce unexpected results.
Parameters:
Index
Index of the column. The first column is column number 1, the second is column number 2, ...
Type
Parameter type of the output buffer.
paramAddr
A pointer to the parameters output buffer.
LengthIndicator
[out] Pointer to a variable that stores the column length or the indicator value SQLDBC_NULL_DATA if the column contains the NULL value. For character data it contains on success the number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator, if the Terminate flag was set. If the source string exceeds the Size value SQLDBC_DATA_TRUNC will be returned and LengthIndicator is set to the number of bytes (except the terminator bytes) needed to copy without truncation.
Size
[in] Length of the parameter buffer in bytes. The Size argument is only necessary for non-integral data types. For character data the Size argument must be large enough to store the terminator byte(s) if the Terminate flag is set.
Terminate
[in] Specifies that the output buffer must be finished with a C-style zero-terminator. The Terminate flag works only for the host var type character (ASCII, UCS2 or UTF8). As a default, all character data is zero-terminated.
Retrieves an SQLDBC_ParameterMetaData object that contains information about the parameters used.
Returns:
An SQLDBC_ParameterMetaData object that contains information about the number, types and properties of the SQLDBC_PreparedStatement object's parameters. If the meta data cannot be retrieved, a NULL pointer is returned
Applications that can adjust the size of records delivered at batch execution
Returns:
The batch size that is preferred by the database for optimum packet usage. This is 4294967295U if the application should deliver as many rows as it can itself handle.
If the requested result set type isn't allowed for this SQL statement, the SQLDBC changes it to an allowed one. Check this with getResultSetType() if the execute() member function returns with a warning.
Returns the number of rows affected by the executed SQL statement.
This method returns a non-zero value if more than one row was addressed by the SQL statement. If the return value is lower than zero, more than one rows was addressed but the exact number of addressed rows cannot be determined.
Retrieves the table name (for SELECT FOR UPDATE commands).
Parameters:
buffer
The buffer in which into the table name is copied.
encoding
Encoding of the buffer where the table name is stored.
bufferSize
Size (in bytes) of the buffer
bufferLength
[out] Number of bytes copied to the buffer, except the number of bytes necessary for the zero-terminator. If the source string exceeds the bufferSize value SQLDBC_DATA_TRUNC will be returned and the bufferLength set to the number of bytes (except the terminator bytes) needed to copy the table name without truncation.
The data pointer that was supplied when binding the parameter, plus any offset that originates from a batch execution sequence (including row-wise binding).
Returns:
SQLDBC_NEED_DATA if paramIndex and paramAddr contain the index or address of the next parameter that will be delivered using putData().
SQLDBC_OK if the last parameter has been processed, and the statement was executed successfully.
Switches to the input parameter if the application uses data at execute parameters,.
After calling this member function, the paramIndex and the paramAddr parameters are set. The paramIndex parameter is used to select the next parameter that is processed. You cannot process parameters that are bound to LOB columns before all parameters that are bound to non-LOB columns have been processed for a row.
Parameters:
paramIndex
Index of the next parameter that shall be processed, or 0 if the application has no preferred next parameter.
paramAddr
The data pointer that was supplied when binding the parameter, plus any offset that originates from a batch execution sequence (including row-wise binding).
Returns:
SQLDBC_NEED_DATA if paramIndex and paramAddr contain the index or address of the next parameter that will be delivered using putData().
SQLDBC_OK if the last parameter has been processed, and the statement was executed successfully.
Pointer to the data which is to be assigned to the SQL parameter. The type of the data must match the type assigned by the bindParameter() member function.
The value of the parameter offset is normally 0, which implies a column-wise binding. If it is set to a value other than 0, a row-wise binding is applied. So, the address offset of the respective next parameter value is computed differently for column-wise and row-wise binding:
Column-wise Binding
byte length of the parameter
Row-wise Binding
size
Parameters:
size
The parameter offset for row-wise binding, if set to 0, column-wise binding is applied.
Sets the command info and a line number (for example name of programm and current line number). This will be send to the database kernel as part of a parse order. If the kernel runs in a special diagnose mode these information will be stored in a system table and can be used to profile your application. The column info and line number will be unset after the next execute of a SQL statement at this statement handle.
Parameters:
buffer
The command info.
bufferLength
Length in bytes of the buffer. In case a zero-terminated string passed on in the buffer argument, you can set the bufferLength to SQLDBC_NTS. Set bufferLength to SQLDBC_NULL_DATA if you want to unset the command info and line number.
Setting the cursorname affects only query and database procedure commands. For DDL-, INSERT-, UPDATE- and DELETE- commands setting the cursorname has no effect.
Parameters:
buffer
The new cursor name.
bufferLength
Length in bytes of the buffer. In case a zero-terminated string passed on in the buffer argument, you can set the bufferLength to SQLDBC_NTS.
The number of rows of the result set is truncated if the result of a query statement is larger than this limit. The default setting is 'unlimited' (0). Setting this limit does not affect an already executed SQL statement.
Sets the maximum number of be rows that can be fetched at once.
Sets the maximum number of rows to be fetched from a result set. Use this to manipulate the number of rows in the order interface. Use a value > 0 to set the maximum number of rows. Use a value <= 0 to reset this limit to the default value. The default value is 'unlimited' (32767). Setting this value does not affect an already executed SQL statement.
