2.17 Open SQL and Sybase Client-Library

The alternative Open SQL interface introduced in DBTools.h++ 4.0 can be used with Sybase Client-Library. This section provides some specific details and examples.

2.17.1 Placeholder Syntax

Beginning with DBTools.h++ 4.0, the Sybase CT access library uses host-variable format for binding program variables to the Sybase 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 different columns within an SQL statement. For example, to insert data through binding into a 2-column table named MyTable, the SQL statement would look like:

Insert into myTable values (@col1, @col2)

Placeholder syntax is not a portable feature. Different access libraries use different placeholder syntax.

2.17.2 Supported Datatypes for Input Binding

All DBTools.h++ datatypes are supported for input binding. In addition to DBTools.h++ datatypes, Sybase Client-Library datatypes are also supported, with the following restrictions.

The Sybase Client-Library doesn't allow binding of text and image datatypes as input parameter types for single row inserts. Other than text and image datatypes, all other Client-Library types are supported. Please see the Sybase documentation on the Client-Library function ct_setparam() for restrictions on datatypes for input parameters. The DBTools.h++ Sybase CT access library uses ct_setparam() for binding input parameters for all single row inserts, and for all single and bulk updates.

Since text and image datatypes can't be directly bound, the DBTools.h++ Sybase CT access library always maps text and image bound input datatypes to the Sybase Client-Library types CS_CHAR and CS_BINARY, respectively. This type mapping allows binding for text and image data up to 255 characters long. Text and image types over 255 characters long should not be bound directly; they can only be sent 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.17.3 Supported Datatypes for Output Binding

All DBTools.h++ datatypes are supported for output binding. In addition to the DBTools.h++ datatypes, the Sybase Client-Library datatypes are also supported.

Applications must follow the type mapping rules for output binding as defined in Table 1, and accordingly bind compatible datatypes. For example, a character type of width 1 is stored as a Sybase-CT tinyint and retrieved as a C++ short. To retrieve the type properly, you must bind a buffer of type RWDBTBuffer<short> or a compatible type, such as RWDBTBuffer<int> or RWDBTBuffer<long>. Trying to retrieve this as an RWDBTBuffer<char> will result in undefined errors.

2.17.4 Bulk Inserts and Bulk Input Binding

The DBTools.h++ Sybase CT access library uses Sybase Bulk-Library to send data. Sybase Bulk-Library currently supports only insert statements 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.17.5 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 Class Reference.) For RPC invocation of stored procedures, the second parameter of the statement() method, statementType, must be passed in as RWDBOSql::Procedure, as shown below:

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.17.7.2 demonstrates how to use RWDBOSql with stored procedures.

2.17.6 RWDBSystemHandle and Open SQL

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.17.6.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 DBTools.h++ Sybase CT access library 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.17.6.2 Simultaneous Results Processing of High-Level DBTools.h++ Objects Using the Same Connection

High-level DBTools.h++ objects, 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.17.6.1.

2.17.7 Open SQL Examples

2.17.7.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);                 //2

FillupValues(intBuffer, floatBuffer, NUMBER_OF_ROWS);           //3

RWCString sybaseSyntaxSql("INSERT INTO foo VALUES(@c1, @c2)");  //4
openSql.statement(sybaseSyntaxSql);                             //5

openSql << intBuffer;                                           //6
openSql << floatBuffer;                                         //7

openSql.execute(conn);                                          //8

if( !openSql.isValid() )                                        //9
     cout << "Insert Into Foo Has Failed" << endl;

//1-//2	Create an `int` buffer and a `float` buffer, each of size 10.
//3	Fills the buffers with values using a predefined function.
//4	Declares a Sybase-specific SQL syntax for insertion into a `foo` table.
//5	Associates the SQL with the *RWDBOSql* object.
//6-//7	Associate the buffer with the *RWDBOSql* object.
//8	Executes the SQL statement.
//9	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.17.7.2 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 one result set consisting of character strings. 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

RWCString sybaseSyntaxSql("mySelectStoredProc");                //4
openSql.statement(sybaseSyntaxSql, RWDBOSql::Procedure);        //5

openSql << paramBuffer;                                         //6

openSql[0] >> outCharBuffer;                                    //7


openSql.execute(conn);              // Execute the stored procedure

/*
   Rows are fetched inside a while loop until all rows are fetched. 
   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.
*/
long count = 0;
while ( (count = openSql.fetch().rowsFetched() ) > 0 ) {

    for( int row = 0; row < count; row++ ){
          cout << "STRING VALUE :" << &outCharBuffer [row] << endl;
    }
}

openSql.fetchReturnParams();                                    //8
cout << "RETURN PARAM VALUE : "<< inputParam << endl;

RWDBValue returnValue = openSql.returnValue();                  //9

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.17.7.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 if and only if the parameter is expecting any return parameter values from the stored procedure execution.
//3	Creates a buffer for output values. This stored procedure returns a result set consisting of string columns less than 255 characters long. Like the input buffer example, this example uses application address space for the buffer.
//4	Creates a Sybase-specific SQL syntax for stored procedure invocation. In this case, the name of the stored procedure invokes it. See `//5`.
//5	Associates the SQL statement with the *RWDBOSql* object. Note the hint being given here as stored procedure. Sybase CT 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, as documented by Sybase documentation.
//6	Associates a input buffer for the input parameter.
//7	Associates the output buffer with the `openSql` object. We expect only one result set for this stored procedure execution, so this output buffer is bound to the first result set, which is indicated by the index 0.
//8	Makes an explicit call to fetch the return parameters. After this call, the return parameters can be expected to be available.
//9	Gets the return value.