Chapter 15 Using the Open SQL Classes
Class RWDBOSql
As we discussed in
Chapter 14, “Defining Open SQL,” Open SQL is a database-portable set of classes for directly executing SQL statements. In this chapter, we explore the Open SQL classes in more detail, and demonstrate how to use them within your applications. Of the three classes that form the Open SQL API, class
RWDBOSql is the most important.
Class
RWDBOSql is the core of the Open SQL API. Fundamentally, using Open SQL involves four activities:
• providing an SQL statement to that instance
• executing the statement
• obtaining the results
Each of these activities depends on
RWDBOSql, so we begin this chapter by creating an instance of this class.
Creating an RWDBOSql Instance
Class
RWDBOSql is an encapsulation of an SQL statement. Creating an instance of
RWDBOSql is simple enough. You just instantiate one as you would any other C++ object. If you know the SQL statement you want to use, you can provide it in the constructor. For example, if you want to use the statement
"SELECT * FROM MYTABLE", you write this code:
RWDBOSql anOSql("SELECT * FROM MYTABLE", RWDBOSql::Query);
The second parameter
RWDBOSql::Query is from enum
RWDBOSql::StatementType. It helps in optimizing binding and performance while executing the statement.
Providing an SQL Statement
An
RWDBOSql can encapsulate the SQL statement provided in the constructor. However, if you don’t know what statement you want to use when your
RWDBOSql is constructed, you can set it later using the
statement() method as shown here:
// default constructor, no statement provided
RWDBOSql anOSql;
…
// provide a statement
anOSql.statement("SELECT * FROM MYTABLE", RWDBOSql::Query);
When using
RWDBOSql, please note that the statement you provide must be valid syntax for the database against which you are executing.
NOTE >> Your SQL statement must use the correct syntax for your database.
The goal of
RWDBOSql is to provide a database-independent way of executing statements and processing results. No attempt is made, however, to normalize SQL grammars between databases. If you are writing an application that must work with relational data in a database-independent fashion, you should use the DML classes—
RWDBSelector,
RWDBInserter,
RWDBDeleter, and
RWDBUpdater—instead of directly executing SQL statements.
Statement Type Hint
An Open SQL statement type,
RWDBOSql::StatementType, can be
NonQuery (the default),
Query, or
Procedure. This type can be set along with the SQL statement on the constructor or on the
statement() method.
RWDBOSql::statement(const RWCString& sql, StatementType type=NonQuery)
Although this type setting is not used by all DB Access Modules, it is recommended for application code portability.
Executing the SQL Statement
Once a statement is provided, executing the statement encapsulated by the
RWDBOSql is as simple as calling its
execute() method. This call takes as a parameter the
RWDBConnection on which to execute the statement:
RWDBConnection aConn = aDb.connection(); // 1
RWDBOSql anOSql(“INSERT INTO MYTABLE VALUES(10)”); // 2
anOSql.execute(aConn); // 3
Let’s look at this example more closely. On
//1, we create an
RWDBConnection,
aConn, from an
RWDBDatabase object created earlier in the program. (For more information on this step, see the section “
Getting a Connection” in Chapter 4.) This
RWDBConnection encapsulates a single connection to our database. On
//2, we create an
RWDBOSql instance to encapsulate our SQL statement, in this case,
“INSERT INTO MYTABLE VALUES(10)”. On
//3, we use the
execute function to execute this statement on
aConn. When this function is invoked, the statement is prepared and immediately executed on our database through the connection
aConn.
One significant difference between
RWDBOSql and many other classes in the DB Interface Module is that its instances are
not produced by any other object. You can create an instance of
RWDBOSql any time, even before you connect to a database. You can pass instances of
RWDBOSql within your application, even if you are
not connected. Of course, when you want to invoke
execute() on your
RWDBOSql, you must have a valid
RWDBConnection on which to execute.
NOTE >> You can create an
RWDBOSql any time, but you must establish a valid database connection to execute the encapsulated SQL statement.
Error Handling
As we know, even valid SQL statements don’t always succeed when executed. Problems such as loss of connectivity, invalid data, and myriad other errors can cause an otherwise good SQL statement to fail. Fortunately, checking the success or failure of an Open SQL execution is straightforward: you follow the same error model used in the standard API of the DB Interface Module. (See
Chapter 6, “The Error Model.”)
With Open SQL, any error that occurs during execution causes the
RWDBOSql instance to become invalid, and its status is changed to reflect the error. However, the
RWDBOSql instance may still be reused; it remains invalid only until the next successful execution.
Let’s see how error handling works with Open SQL. Using the error model of the DB Interface Module, we can easily add in-line error checking to the previous code sample:
RWDBConnection aConn = aDb.connection(); // 1
RWDBOSql anOSql(“INSERT INTO MYTABLE VALUES(10)”); // 2
anOSql.execute(aConn); // 3
// Did the execution fail?
if ( !anOSql.isValid() ) { // 4
// It failed. Print out the error message.
// someOstream is an ostream or std::ostream, such as
// cout or cerr (or std::cout, std::cerr)
someOstream << “Error: “
<< anOSql.status().message() // 5
<< endl;
}
In
//1 -
//3, we execute the statement as in the previous example. On
//4, we use the
isValid() method on the
RWDBOSql; here
isValid() returns
false if the execution of the SQL statement fails. On
//5, we use the
status() method to return an
RWDBStatus that contains information about the error, such as a printable error message, and error codes. Although the
RWDBOSql is invalid at this point, we can choose to reinvoke
execute(). If the re-execution succeeds, the
RWDBOSql becomes valid again, so that calling
isValid() on it returns
true.
Obtaining the Results
For the sake of simplicity, our SQL statements thus far have neither required input data nor produced output data. Of course, inputting and outputting data are important functions of Open SQL, and class
RWDBOSql provides mechanisms for both:
• Input binding is the mechanism for passing data to your statements.
• Output binding and fetching are the mechanisms for retrieving results.
To use
RWDBOSql for exchanging data with your database in either direction, you bind your own program variables to the statement (for input binding), or to a result set (for output binding).
To provide Open SQL with information about your program variables, you use the template class
RWDBTBuffer. When you use
RWDBTBuffer with
RWDBOSql, your data is
not converted unless absolutely necessary, and
no internal copy of your data is made unless absolutely necessary. Avoiding these data conversions and internal copies can result in greater performance for Open SQL in comparison with the classes of the standard API, such as
RWDBInserter and
RWDBReader.
The next section explains
RWDBTBuffer in more detail.