2.18 Open SQL and Microsoft SQL Server ODBC
The alternative Open SQL interface can be used with Microsoft SQL Server ODBC driver. This section provides some specific details and examples.
2.18.1 Placeholder Syntax
The placeholder syntax for Microsoft SQL Server is simply:
?
Here is an example:
INSERT INTO PICNICTABLE VALUES( ? )
Placeholders are not a portable feature. Different Access Modules use different placeholder syntax.
2.18.2 RWDBDataCallback and Open SQL
The data callback classes can be used with Microsoft SQL Server to insert and fetch data. These classes 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.
When fetching data, Microsoft SQL Server 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 Microsoft SQL Server will fetch only one row on each call to RWDBOSql::fetch().
Multiple rows can be inserted in one call to
RWDBOSql::execute(). The number of rows is determined by the
entries parameter passed to the constructor of a data callback class or an
RWDBTBuffer instance. Please see
Chapter 15, “Using the Open SQL Classes,” in the
DB Interface Module User’s Guide for information about array input.
2.18.2.1 Piece Size Setting
The DB Access Module for Microsoft SQL Server sets the piece size for inserting or fetching data to 32768. This value represents bytes in the case of binary data, and code units in the case of character data.
2.18.2.2 RWDBDataCallback::getLength()
When inserting data using data callbacks, some databases need to be provided the total length of the inserted value, which requires overriding the RWDBDataCallback::getLength() method.
When using the SQL Server Native Client or ODBC Driver for SQL ServerThe Microsoft SQL Server Native Client and ODBC Driver for SQL Server do not need the total length of the value, although there are potential performance benefits to using it. Therefore, overriding the RWDBDataCallback::getLength() method may be desirable but is not required.
When using the Microsoft SQL Server MDAC Client The MDAC Client requires the total length of the insert value to insert data successfully. Therefore, the method RWDBDataCallback::getLength() must be overridden when inserting data.
2.18.2.3 Stored Procedures and RWDBDataCallback
Stored procedure execution is not supported with the data callback classes.
2.18.3 Stored Procedures and Open SQL
Standard ODBC CALL syntax can be used when executing stored procedures with
RWDBOSql.
RWDBOSql myOpenSqlObject("{ CALL myStoredProc(?, ?) }", RWDBOSql::Procedure);
In this example, myStoredProc is the name of the stored procedure, and ? characters are the placeholders for the parameters to be passed to the stored procedure. Note the provided hint RWDBOSql::Procedure.
RWDBOSql myOpenSqlObject("{ ? = CALL myStoredProc(?, ?)}", RWDBOSql::Procedure);
This example uses an additional placeholder to receive the return value of the stored procedure as an OUT parameter. A SQL Server stored procedure is allowed to have return value only of type integer.
Please see
Section 2.18.5.3 for an example that uses
RWDBOSql with stored procedures.
Stored procedure execution is not supported when using the data callback classes.
2.18.3.1 Sequence of Fetching Result Sets and Out Parameters
For Microsoft SQL Server stored procedures that return a result set, the SQL Server driver returns the OUT parameters only after the result sets are returned. Hence, fetchReturnParams() should be called only after completely processing all the result sets, otherwise the buffers bound to the OUT parameter will not have the correct output value.
2.18.4 Executing Cancel in Open SQL
Method
RWDBOSql::cancel() can be used either to cancel the current result set or to cancel all pending result sets of the current execution. This method should be called only after the
RWDBOSql object is executed.
Method
RWDBOSql::cancel(RWDBOSql::Current) cancels the current result set and moves the
RWDBOSql to the next available result set, if any.
Method RWDBOSql::cancel(RWDBOSql::All) cancels all the pending result sets. No subsequent results will be available from the current execution after this call has been made.
2.18.5 Open SQL Examples
These examples illustrate how an
RWDBOSql object can be used to:
All statements are based on an employee table
emp. The examples assume an error handler is associated with the connection in use, and don’t check for 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
2.18.5.1 An Open SQL Insert Example
This example shows how to execute an
INSERT statement using
RWDBOSql to insert data into the
emp table.
const size_t NUM_OF_ROWS = 14; //1
RWDBTBuffer<int> empnoBuff(NUM_OF_ROWS), mgrBuff(NUM_OF_ROWS),
deptBuff(NUM_OF_ROWS);
RWDBTBuffer<RWCString> enameBuff(NUM_OF_ROWS), posnBuff(NUM_OF_ROWS);
RWDBTBuffer<RWDecimalPortable> salBuff(NUM_OF_ROWS); //2
empnoBuff[0] = 1;
enameBuff[0] = "ABC XYZ";
posnBuff[0] = "ADMIN";
mgrBuff[0] = 6;
salBuff[0] = "58000.00";
deptBuff[0] = 4; //3
// Populate rest of the rows
RWDBOSql openSql("INSERT INTO emp VALUES(?, ?, ?, ?, ?, ?)",
RWDBOSql::NonQuery); //4
openSql << empnoBuff << enameBuff << posnBuff
<< mgrBuff << salBuff << deptBuff; //5
openSql.execute(cn); //6
long rowsInserted = openSql.rowsAffected(); //7
std::cout << (openSql.isValid() ? "Data insertion successful." //8
: "Data insertion failed.") << std::endl;
if (rowsInserted >= 0) {
std::cout << "Inserted " << rowsInserted << " rows." << std::endl;
}
2.18.5.2 Open SQL Query Examples
These examples illustrate how an
RWDBOSql object can be used to execute
SELECT queries and retrieve their results. The
SELECT query is executed on table
emp and retrieves all columns of the table for employees in a particular department.
The first example assumes awareness of the structure of table
emp (
Section 2.18.5) and hence the schema of the result set returned by the query.
RWDBOSql openSql("SELECT * FROM emp WHERE 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 = ?", 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 through
RWDBOSql execution are the same as in the previous example.
2.18.5.3 An Open SQL Stored Procedure Example
This example shows how to invoke a stored procedure,
EmpInDept, using an
RWDBOSql object, fetch result sets and fetch output parameters.
CREATE PROCEDURE EmpInDept(@deptno INT, @empcount INT OUT)
AS
SELECT @empcount = COUNT(*) FROM emp WHERE emp.dept = @deptno
SELECT empno, ename FROM emp WHERE emp.dept = @deptno
SELECT MIN(sal), AVG(sal), MAX(sal) FROM emp WHERE emp.dept = @deptno
The stored procedure takes a department number as an input parameter and provides the count of employees in that department as an output parameter. It produces two result sets: the first returns a list of employee number and name for each employee in that department; the second result set returns a single row with three columns containing the minimum, average and maximum salary of employees in that department.
RWDBOSql openSql ("{CALL EmpInDept(?, ?) }", RWDBOSql::Procedure); //1
RWDBTBuffer<int> dept, empCount; //2
empCount.paramType(RWDBColumn::outParameter); //3
openSql << dept << empCount; //4
dept[0] = 2; //5
openSql.execute(cn); //6
const size_t NUMBER_OF_ROWS = 10;
RWDBTBuffer<int> empNums (NUMBER_OF_ROWS); //7
RWDBTBuffer<RWCString> empNames (NUMBER_OF_ROWS); //8
openSql[0] >> empNums >> empNames; //9
long count = 0;
while ((count = openSql.fetch().rowsFetched()) > 0 ) { //10
for (int row = 0; row < count; row++) {
std::cout << "Employee: " << empNums[row] << ", "
<< empNames[row] << std::endl; //11
}
}
RWDBTBuffer<RWDecimalPortable> minBuff, avgBuff, maxBuff; //12
openSql[1] >> minBuff >> avgBuff >> maxBuff; //13
openSql.fetch(); //14
std::cout << "\nMinimum Salary: " << minBuff[0]
<< "\nAverage Salary: " << avgBuff[0]
<< "\nMaximum Salary: " << maxBuff[0]
<< std::endl; //15
openSql.fetchReturnParams(); //16
std::cout << "Number of Employees in dept " << dept[0] << ": "
<< empCount[0] << std::endl; //17