2.19 Open SQL and Sybase Open Client Client-Library
The alternative Open SQL interface can be used with Sybase Open Client Client-Library. This section provides some specific details and examples.
2.19.1 Placeholder Syntax
The DB Access Module for Sybase uses host-variable format for binding program variables to the Client-Library. When input parameter values are provided through binding for
RWDBOSql objects, applications must use proper placeholder syntax in their SQL statements. The proper placeholder syntax for host-variable format is
@something, where
something must be a unique string among the placeholders within an SQL statement. For example, here is the SQL statement to insert data through binding into a 2-column table named
mytable:
insert into mytable values (@col1, @col2)
Placeholder syntax is not a portable feature. Different Access Modules use different placeholder syntax.
2.19.2 Supported Datatypes for Input Binding
In addition to the datatype support listed in
Section 2.3, Sybase Open Client Client-Library datatypes are also supported, with the following restrictions.
Since large text and image datatypes can’t be directly bound, the DB Access Module for Sybase always maps text and image bound input datatypes to the Client-Library types CS_CHAR_TYPE and CS_BINARY_TYPE, respectively. This type mapping allows binding for text and image data up to the length limit of 16384 bytes. Text and image types over the length limit should not be bound directly; they can be sent only as literal strings within the SQL statement.
In addition to restricting direct binding of text and image datatypes, the Client-Library function ct_setparam() refrains from implicit type conversions. Hence, it is the responsibility of applications to bind compatible types for target database table columns.
For bulk inserts, applications can directly bind text and image datatypes of any size, since the Sybase Bulk-Library has no type limitation in bulk binding routines. Furthermore, the bulk routine call blk_bind() performs implicit type conversions, and hence it may be possible to bind convertible types when doing bulk inserts. Please see the Sybase documentation for details on datatype conversion support.
2.19.3 Supported Datatypes for Output Binding
In addition to the datatype support listed in
Section 2.3, the Sybase Client-Library datatypes are supported; see
Section 2.17.
2.19.4 Bulk Inserts and Bulk Input Binding
The DB Access Module for Sybase uses Sybase Bulk-Library to send data. Sybase Bulk-Library currently supports only data insertion operations when used for sending data to the server. An error of RWDBStatus::notSupported is returned when bulk input binding is tried for any statements other than inserts.
2.19.5 RWDBDataCallback and Open SQL
The data callback classes can be used with Sybase to fetch data only; inserting data is not supported. Data callbacks are useful for very large data that may not fit in memory for a single database call and are described in
Section 15.3, “The Data Callback Classes,” in the
DB Interface Module User’s Guide.
The DB Access Module for Sybase does not support inserting data with the data callback classes.
When fetching data, Sybase requires that the columns bound to a data callback must be at the end of the select list. For example, if two columns of type integer and LOB are selected, the integer must come before the LOB in the select list in order to use a data callback for the LOB column.
The DB Access Module for Sybase will fetch only one row on each call to RWDBOSql::fetch().
2.19.5.1 Piece Size Setting
The DB Access Module for Sybase sets the piece size for fetching data to 32768. This value represents bytes in the case of binary data, and code units in the case of character data.
2.19.5.2 Changing the Maximum Fetch Size
Sybase limits the length of text or image data that the server can return to 32768 bytes. This value can be changed by calling the Sybase function
ct_options. Please see the class reference description for
RWDBSybCtLibSystemHandle for more information about setting this option.
2.19.5.3 RWDBDataCallback::getLength()
The DB Access Module for Sybase does not support inserting data using the data callbacks, so the method RWDBDataCallback::getLength() does not need to be overridden in the derived class.
2.19.5.4 Stored Procedures and RWDBDataCallback
Stored procedure execution is not supported with the data callback classes.
2.19.6 Stored Procedures and Open SQL
A stored procedure, like any other SQL statement, can be directly executed on an
RWDBOSql object. However, there are some minor variations. The SQL syntax for the stored procedure invocation depends on how the application wants the stored procedure to be invoked. For RPC invocation, the kind of invocation recommended by Sybase for improved performance, the SQL command is simply the name of the stored procedure.
By default, an
RWDBOSql object treats all SQL statements as queries and sends them to the server as language commands until indicated otherwise. (Please see the
RWDBOSql entry in the
SourcePro C++ API Reference Guide) For RPC invocation of stored procedures, the
statementType parameter of either the
RWDBOSql's constructor or the
statement() method must be passed in as
RWDBOSql::Procedure, as shown in the following statements:
RWDBOSql myOpenSqlObject("myStoredProc", RWDBOSql::Procedure);
or
myOpenSqlObject.statement(“myStoredProc”, RWDBOSql::Procedure);
Here,
myStoredProc is the stored procedure name, and
myOpenSqlObject is an
RWDBOSql instance.
If statementType is not given as RWDBOSql::Procedure, a command is sent as a language command. If a stored procedure is sent as a language command, the SQL syntax for the stored procedure invocation may require placeholders for all parameters. Please see the Sybase documentation for more details.
Section 2.19.9.3 demonstrates how to use
RWDBOSql with stored procedures.
Stored procedure execution is not supported with the data callback classes.
2.19.6.1 Sequence of Fetching Result Sets and Out Parameters
For Sybase stored procedures that return a result set, the Open-Client Client Library returns the OUT parameters only after the result sets are returned. Hence, you should call fetchReturnParams() only after completely processing all the result sets. Calling this method before processing the result sets cancels the result sets.
2.19.7 Simultaneous Result Processing
Each
RWDBOSql object corresponds to a
CS_COMMAND structure. A
CS_COMMAND structure is created and associated with an
RWDBOSql object only when the
execute() method is invoked on the
RWDBOSql object. The
CS_COMMAND structure remains associated with the
RWDBOSql object until it goes out of scope, or until
execute() is invoked on the object using a different connection. Invoking
execute() on the same connection reuses the same
CS_COMMAND structure.
2.19.7.1 Simultaneous Results Processing of RWDBOSql Objects on the Same Connection
When executed on an
RWDBConnection for the first time, every
RWDBOSql object creates and associates itself with a new Sybase Client-Library specific
CS_COMMAND structure. Though Sybase Client-Library allows many
CS_COMMAND structures on the same connection, it doesn’t allow execution of any SQL statement on a
CS_COMMAND structure while another
CS_COMMAND structure on the same connection is processing a result set. The net effect is that, although many
CS_COMMAND structures from the same connection are
possible, simultaneous processing of results on the
CS_COMMAND structures from the same connection is
not possible.
To relieve the burden of ensuring that only one
RWDBOSql object at a time processes results on a connection, the DB Access Module for Sybase cancels pending results on the last used
RWDBOSql object before processing a new command on a new
RWDBOSql object. This feature ensures that applications do not have to process the results of one
RWDBOSql object completely before executing new commands on another.
In summary, applications are prohibited from simultaneously processing results on multiple
RWDBOSql objects on the same connection. This restriction does
not apply to
RWDBOSql objects on different connections.
2.19.7.2 Simultaneous Results Processing of High-Level Objects of the DB Interface Module Using the Same Connection
High-level objects of the DB Interface Module, like
RWDBSelector and
RWDBInserter, for example, are internally associated with unique
RWDBOSql objects. For this reason, these high-level objects are also restricted from simultaneously processing results on
RWDBOSql objects on the same connection. Also see
Section 2.19.7.1.
2.19.8 Executing Cancel in Open SQL
In Open SQL, you can use cancel() either to cancel the current result set or to cancel the execution of the current statement.
Method RWDBOSql::cancel(RWDBOSql::Current) cancels the current result set. For asynchronous connections, this will cancel the currently available result set (a result set is available when RWDBOSql::isReady() returns true). If no result is pending on an asynchronous connection the behavior is undefined. This call will block until the cancel operation is complete.
Method RWDBOSql::cancel(RWDBOSql::All) cancels the current statement execution. No subsequent results will be available from the command after this call has been made. This method can be used to cancel asynchronous executions. This call will block until the cancel operation is complete.
2.19.9 Open SQL Examples
This section provides some Open SQL examples.
2.19.9.1 An Open SQL Insert Example
This example shows how to use the Open SQL class
RWDBOSql in a Sybase Client-Library insert operation. The insert is done on a
foo table with an
int column and a
float column.
#define NUMBER_OF_ROWS 10
RWDBOSql openSql;
RWDBTBuffer<int> intBuffer(NUMBER_OF_ROWS); //1
RWDBTBuffer<float> floatBuffer(NUMBER_OF_ROWS);
FillupValues(intBuffer, floatBuffer, NUMBER_OF_ROWS); //2
RWCString sybaseSyntaxSql("INSERT INTO foo VALUES(@c1, @c2)"); //3
openSql.statement(sybaseSyntaxSql); //4
openSql << intBuffer; //5
openSql << floatBuffer;
openSql.execute(conn); //6
if( !openSql.isValid() ) //7
cout << “Insert Into Foo Has Failed” << endl;
2.19.9.2 Open SQL Query Examples
These examples illustrate how an
RWDBOSql object can be used to execute a
SELECT query and retrieve its results. The
SELECT query is executed on an employee table
emp and retrieves all columns of the table for employees in a particular department. The example assumes an error handler is associated with the connection in use, and doesn't check for any errors after calls to the
RWDBOSql object.
The structure of the table emp is:
empno INT NOT NULL
ename VARCHAR(10) NOT NULL
posn VARCHAR(9) NOT NULL
mgr INT NULL
sal NUMERIC(7,2) NOT NULL
dept INT NOT NULL
The first example assumes awareness of the structure of table emp, and hence the schema of the result set returned by the query.
RWDBOSql openSql("SELECT * FROM emp WHERE dept = @dept", RWDBOSql::Query); //1
RWDBTBuffer<int> queryDeptBuff; //2
queryDeptBuff[0] = 3; //3
openSql << queryDeptBuff; //4
openSql.execute(cn); //5
const size_t ROWS_TO_FETCH = 5; //6
RWDBTBuffer<int> empnoBuff(ROWS_TO_FETCH), mgrBuff(ROWS_TO_FETCH),
deptBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWCString> enameBuff(ROWS_TO_FETCH), posnBuff(ROWS_TO_FETCH);
RWDBTBuffer<RWDecimalPortable> salBuff(ROWS_TO_FETCH); //7
openSql[0] >> empnoBuff >> enameBuff >> posnBuff >>
mgrBuff >> salBuff >> deptBuff; //8
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //9
for (size_t i = 0; i < rowsFetched; ++i) {
cout << empnoBuff[i] << "\t" << enameBuff[i] << "\t"
<< posnBuff[i] << "\t"; //10
if (mgrBuff.isNull(i)) { //11
cout << "NULL";
}
else {
cout << mgrBuff[i];
}
cout << "\t" << salBuff[i] << "\t" << deptBuff[i] << endl;
}
}
The second example assumes execution of an ad hoc query for which the schema of the result set is not known beforehand.
RWDBOSql openSql("SELECT * FROM emp WHERE dept = @dept", RWDBOSql::Query);
RWDBTBuffer<int> queryDeptBuff;
queryDeptBuff[0] = 3;
openSql << queryDeptBuff;
openSql.execute(cn);
const size_t ROWS_TO_FETCH = 5;
RWDBMultiRow mRow(openSql.schema(), ROWS_TO_FETCH); //1
openSql[0] = mRow; //2
long rowsFetched = 0;
while ((rowsFetched = openSql.fetch().rowsFetched()) > 0) { //3
for (size_t row = 0; row < rowsFetched; ++row) {
for (size_t col = 0; col < mRow.numberOfColumns(); ++col) { //4
RWDBValue val = mRow[row][col]; //5
if ( val.isNull() ) { //6
cout << "NULL" << "\t";
}
else {
cout << val.asString() << "\t";
}
}
cout << endl;
}
}
The steps until
RWDBOSql execution are the same as in the previous example.
2.19.9.3 An Open SQL Stored Procedure Example
This example illustrates how a stored procedure can be executed using an
RWDBOSql object. The stored procedure used here is assumed to take one input-output parameter of type
int, and to return two result sets, the first consisting of character strings and the second of two columns of types
int and
RWDecimalPortable. This stored procedure has a return value of type
int.
This example assumes that an error handler is associated with the connection, and therefore doesn’t check for any errors after calls to the
RWDBOSql object.
#define NUMBER_OF_ROWS 10 // Indicates the number of rows
// for the buffer
#define MAXSTRING_SIZE 255 // Maximum length of a string
// in the buffer
RWDBOSql openSql;
int inputParam = 100;
RWDBNullIndicator indA = 0;
RWDBTBuffer<int> paramBuffer(&inputParam, &indA, 1); //1
paramBuffer.paramType(RWDBColumn::inOutParameter); //2
char aString[NUMBER_OF_ROWS][MAXSTRING_SIZE];
memset(aString, 0, NUMBER_OF_ROWS * MAXSTRING_SIZE );
RWDBNullIndicator nullInd[NUMBER_OF_ROWS];
RWDBTBuffer<char> outCharBuffer(aString[0], nullInd,
NUMBER_OF_ROWS, MAXSTRING_SIZE); //3
RWDBTBuffer<int> outIntBuff(NUMBER_OF_ROWS); //4
RWDBTBuffer<RWDecimalPortable> outDecBuff(NUMBER_OF_ROWS); //5
RWCString sybaseSyntaxSql("mySelectStoredProc"); //6
openSql.statement(sybaseSyntaxSql, RWDBOSql::Procedure); //7
openSql << paramBuffer; //8
openSql[0] >> outCharBuffer; //9
openSql[1] >> outIntBuff >> outDecBuff; //10
openSql.execute(conn); // Execute the stored procedure
long count = 0;
while ( (count = openSql.fetch().rowsFetched() ) > 0 ) { //11
for( int row = 0; row < count; row++ ){
cout << "STRING VALUE :" << &outCharBuffer[row] << endl;
}
}
while ( (count = openSql.fetch().rowsFetched() ) > 0 ) { //12
for( int row = 0; row < count; row++ ){
cout << "INT VALUE: " << outIntBuffer[row] << endl;
cout << "DECIMAL VALUE: " << outDecBuff[row] << endl;
}
}
openSql.fetchReturnParams(); //13
cout << "RETURN PARAM VALUE : "<< inputParam << endl;
RWDBValue returnValue = openSql.returnValue(); //14
cout << "RETURN VALUE :" << returnValue.asInt() << endl;