Open SQL Examples
This section provides some Open SQL examples.
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;
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.
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;