The alternative Open SQL interface can be used with Sybase Client-Library. This section provides some specific details and examples.
The DB Access Module for Sybase CT 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, 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.
All data types of the DB Interface Module are supported for input binding. In addition to these data types, Sybase Client-Library data types are also supported, with the following restrictions.
The Sybase Client-Library doesn't allow binding of large text and image data types (i.e. longer than 16384 bytes) as input parameter types for single row inserts. Other than large text and image data types, all other Client-Library types are supported. Please see the Sybase documentation on the Client-Library function ct_setparam() for restrictions on data types for input parameters. The DB Access Module for Sybase CT uses ct_setparam() for binding input parameters for all single row inserts, and for all single and bulk updates.
Since large text and image data types can't be directly bound, the DB Access Module for Sybase CT always maps text and image bound input data types to the Sybase 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 only be sent as literal strings within the SQL statement. The length limit for Sybase Adaptive Server 12.5 and above is 16384 bytes.
In addition to restricting direct binding of text and image data types, 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 data types 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 data type conversion support.
All DB Interface Module data types are supported for output binding. In addition to these data types, the Sybase Client-Library data types are also supported.
Applications must follow the type mapping rules for output binding as defined in Table 1, and accordingly bind compatible data types. 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.
The DB Access Module for Sybase CT 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.
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 Reference Guide.) For RPC invocation of stored procedures, the second parameter of the statement() method, statementType, must be passed in as RWDBOSql::Procedure, as shown in the following statement:
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.
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.
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 CT 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.
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.17.6.1.
This section provides some Open SQL examples.
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. |
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. |
In Open SQL, you can use cancel() either to cancel the current result set or to cancel the execution of the current statement.
RWDBOSql::cancel(RWDBOSql::Current) cancels the current result set. For aysnchronous 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.
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.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.