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;
//1 These two lines create an int buffer and a float buffer, each of size 10.
//2 Fills the buffers with values using a predefined function.
//3 Declares a Sybase-specific SQL syntax for insertion into a foo table.
//4 Associates the SQL with the RWDBOSql object.
//5 These two lines associate the buffer with the RWDBOSql object.
//6 Executes the SQL statement.
//7 Checks the validity of the execution. If an error handler is associated with the connection or with the RWDBOSql object, the error handler is invoked if the operation fails. When error handlers are associated, explicit error checking is unnecessary.
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;
}
}
//1 Creates an RWDBOSql object with the supplied SQL query and the hint RWDBOSql::Query indicating that the SQL is a SELECT statement. @dept in the SQL statement is a placeholder for the department number from which employees are to be selected. The placeholder needs to be bound before executing the RWDBOSql.
//2 Creates an RWDBTBuffer object to bind to the placeholder. This object is templatized on type int, as the dept column is of server type INT. The buffer will have a single entry of type int.
//3 The single entry from the buffer created in //2 is accessed using in the index operator and assigned the department number, the records of which will be queried.
//4 Binds the buffer to the RWDBOSql object. The SQL query has only one placeholder, hence only one buffer needs to be bound. The RWDBOSql object is now ready for execution.
//5 Executes the RWDBOSql object on a supplied RWDBConnection object.
//6 Defines a constant for the number of rows to fetch at a time from the query result set.
//7 Creates RWDBTBuffer objects to be used in fetching the query result set, with one object per column of the result set. The objects are templatized on the expected server types of the result columns as mapped to SourcePro types. (See Section 2.3 for type mapping detail.) Each buffer is created with the number of entries equal to the number of rows to be fetched for each call.
//8 Binds the output buffers to the first RWDBMultiRow object in the RWDBOSql. As this is the first (and only) result set from the execution of the RWDBOSql, the buffers are bound to the first RWDBMultiRow object accessed using the index operator on RWDBOSql.
//9 Fetches the result into the bound buffers with each call to fetch() . The call rowsFetched() returns the number of rows fetched. The call to fetch() is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 0.
//10 Uses the index operator on the bound output buffers to access the fetched data.
//11 Uses the isNull() method on the bound output buffer mgrBuff to determine if a particular entry in the buffer is NULL.
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.
//1 Fetches the schema of the available result set using the schema() method on the RWDBOSql object. (You can use the RWDBSchema API to browse through the contents of the result set schema.) Here, we use the returned schema directly to create an RWDBMultiRow that creates buffers matching the datatypes of the result set columns. Each buffer will be created with ROWS_TO_FETCH entries.
//2 Assigns the created RWDBMultiRow to the RWDBOSql for use in fetching the first result set, by assigning to the 0th position in the RWDBOSql's result sets.
//3 Fetches rows into the buffers in the RWDBMultiRow with the fetch() call. The number of rows fetched is returned by the rowsFetched() call. This is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 0.
//4 Finds the number of buffers in the RWDBMultiRow using method numberOfColumns().
//5 Accesses each row’s data inside the RWDBMultiRow buffers as an RWDBRow object by calling the index operator on RWDBMultiRow and passing in the row number needed. Each value in the RWDBRow instance can then be accessed as an RWDBValue instance using the index operator on RWDBRow and passing in the column number needed.
//6 Uses the RWDBValue API to check if it is NULL. RWDBValue contains several other methods to process the value, for example, to determine the value’s type, access the value, or convert the value from one type to another.
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;
 
//1 Creates a buffer for input parameters. Note the difference between this buffer creation and the buffer creation in Section 2.19.9.1. In this case, an application address space is bound to the buffer.
//2 Indicates that the input buffer is intended to be an INPUT-OUTPUT parameter buffer. OUTPUT or INPUT-OUTPUT should be specified only if the parameter is expecting any return parameter values from the stored procedure execution.
//3 Creates a buffer for output values. The first result set this stored procedure returns consists of string columns less than 255 characters long. Like the input buffer example, this example uses application address space for the buffer.
//4 Creates an output buffer of type int for the second result set the stored procedure returns. Note that the buffers created here and on line //5 are not supplied the application address space. The RWDBTBuffer objects allocate and manage memory for storing the data. Each buffer has NUM_OF_ROWS entries.
//5 Creates an output buffer of type RWDecimalPortable for the second result set returned by the stored procedure.
//6 Creates a Sybase-specific SQL syntax for stored procedure invocation. In this case, the name of the stored procedure invokes the stored procedure. See //7.
//7 Associates the SQL statement with the RWDBOSql object. Note the hint provided as RWDBOSql::Procedure. Sybase in general treats all SQL statements as queries unless specified otherwise. Specifying the SQL statement as a stored procedure allows the stored procedure to be passed as an RPC command. RPC commands for stored procedure invocation result in better performance, per Sybase documentation.
//8 Associates an input buffer for the input parameter.
//9 Associates the output buffer for the first result set with the openSql object. This output buffer is bound to the first result set, indicated by the index 0.
//10 Associates the output buffers for the second result set with the openSql object. These output buffers are bound to the second result set, indicated by index 1. The buffers are bound in the order of the result set columns.
//11 Fetches rows from the first result set. Rows are fetched inside a while loop until all rows are fetched, indicated when fetch() returns 0 rows. A successful fetch places the rows in the buffer bound to the openSql object. In this case, the buffer is outCharBuffer. Note that the number of rows fetched is guaranteed to be less than or equal to the buffer's row size.
//12 Once a fetch() call returns 0 rows, the next fetch() call fetches rows from the next result set. The while loop fetches rows from the second result set into the buffers outIntBuff and outDecBuff.
//13 Makes an explicit call to fetch the return parameters. After this call, the return parameters can be expected to be available. Note that this call is made after completely processing the returned result sets.
//14 Gets the return value.