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:
creating an RWDBOSql instance
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.
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.
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.
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.
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 section 4.2, "Getting Connected.") 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.
You can create an RWDBOSql any time, but you must establish a valid database connection to execute the encapsulated SQL statement.
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.
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.
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.