DB Access Module for MySQL User’s Guide : Chapter 2 Technical Information : Open SQL and MySQL
Open SQL and MySQL
The alternative Open SQL interface can be used with MySQL. This section provides some specific details and examples.
Placeholder Syntax
The placeholder syntax for MySQL is simply:
?
Here is an example:
INSERT INTO PICNICTABLE VALUES( ? )
Placeholders are not a portable feature. Different Access Modules use different placeholder syntax. All values are sent as literals upon execution of the SQL statement. Please see “Input Binding of Data.”
RWDBDataCallback and Open SQL
The data callback classes are not supported in the DB Access Module for MySQL. Attempts to use the data callbacks will result in an RWDBStatus::notSupported error.
Executing Cancel in Open SQL
The DB Access Module for MySQL does not currently implement RWDBOSql::cancel() functionality.
Stored Procedures and Open SQL
The DB Access Module for MySQL does not support executing stored procedures using Open SQL.
Open SQL Examples
These examples illustrate how an RWDBOSql object can be used to
• Execute an INSERT statement, “An Open SQL Insert Example.”
• Execute a SELECT query and retrieve its results, “Open SQL Query Examples.”
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 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
An Open SQL Insert Example
This example shows how to execute an INSERT statement using RWDBOSql to insert data in 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;
}
//1 Defines a constant for the number of rows to insert into the table in one insert operation. This is the array size of the bound variables.
//2 Defines buffers for holding the data to be inserted. A buffer is defined for every column in the table. The datatype on which each RWDBTBuffer object is templatized depends on the server type of each column in the table. Mappings between the C++ types and server datatypes are provided in “Datatypes.” The number of entries in the buffer equals the number of rows to be inserted.
//3 Fills the buffers with data for the first row using the index operator on the RWDBTBuffer objects. Similarly, fills other rows with the data. Alternatively, you could write a separate subroutine to fill the buffer with data.
//4 Creates an RWDBOSql object with the INSERT SQL statement using the statement type hint of RWDBOSql::NonQuery. The INSERT statement uses placeholders for each column in the table. Buffers need to be bound for the placeholders before the RWDBOSql is ready for execution.
//5 Binds the input buffers to the RWDBOSql in the same sequence as columns in the table.
//6 Executes the RWDBOSql on the supplied RWDBConnection.
//7 Fetches the number of rows inserted using the method rowsAffected(). Note that some rows may be inserted even if the insert operation fails.
//8 Determines the success of the insert operation using the isValid() method on RWDBOSql.
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;
}
}
//1 Creates an RWDBOSql object with the supplied SQL query and the hint RWDBOSql::Query indicating that the SQL is a SELECT statement. The “?” in the SQL statement is a placeholder for the department number whose employees are to be selected. The placeholder needs to be bound before executing the RWDBOSql.
//2 Creates an RWDBTBuffer object that will be bound to the placeholder. It is templatized on type int, as the dept column is of server type INT. The buffer will have a single entry of type int.
//3 Uses the index operator to access the single entry from the buffer created in //2, assigning it the department number for which the records will be queried.
//4 Binds the buffer to the RWDBOSql object. The SQL query has only one placeholder, hence only one buffer needs to be bound. The RWDBOSql object is now ready for execution.
//5 Executes the RWDBOSql object on a supplied RWDBConnection object.
//6 Defines a constant for the number of rows to fetch at a time from the query result set.
//7 Creates RWDBTBuffer objects to be used in fetching the query result set, with one object per column of the result set. The objects are templatized on the expected server types of the result columns as mapped to SourcePro types. (See the type mapping in “Datatypes.”) Each buffer is created with the number of entries equal to the number of rows to be fetched at a time.
//8 Binds the output buffers to the first RWDBMultiRow object in the RWDBOSql. As this is the first (and only) result set from the execution of the RWDBOSql, the buffers are bound to the first RWDBMultiRow object accessed using the index operator on RWDBOSql.
//9 Fetches the result into the bound buffers with each call to fetch() . The call rowsFetched() returns the number of rows fetched. The call to fetch() is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 0.
//10 Uses the index operator on the bound output buffers to access the fetched data.
//11 Uses the isNull() method on the bound output buffer mgrBuff to determine if a particular entry in the buffer is NULL.
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 until RWDBOSql execution are the same as in the previous example.
//1 Fetches the schema of the available result set using the schema() method on the RWDBOSql object. (You can use the RWDBSchema API to browse through the contents of the result set schema.) Here, we use the returned schema directly to create an RWDBMultiRow that creates buffers matching the datatypes of the result set columns. Each buffer will be created with ROWS_TO_FETCH entries.
//2 Assigns the created RWDBMultiRow to the RWDBOSql for use in fetching the first result set, by assigning to the 0th position in the RWDBOSql's result sets.
//3 Fetches rows into the buffers in the RWDBMultiRow with the fetch() call. The number of rows fetched is returned by rowsFetched(). This is repeated until all rows in the result set are fetched, indicated when rowsFetched() returns 0.
//4 Finds the number of buffers in the RWDBMultiRow using method numberOfColumns().
//5 Accesses each row’s data inside the RWDBMultiRow buffers as an RWDBRow object by calling the index operator on RWDBMultiRow and passing in the row number needed. Each value in the RWDBRow instance can then be accessed as an RWDBValue instance using the index operator on RWDBRow and passing in the column number needed.
//6 Uses the RWDBValue API to check if it is NULL. RWDBValue contains several other methods to process the value, for example, to determine the value’s type, access the value, or convert the value from one type to another.