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
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;
}
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 (
“Open SQL Examples”) 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.
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