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:

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:

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:

When using RWDBOSql, please note that the statement you provide must be valid syntax for the database against which you are executing.

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.

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.

Although this type setting is not used by all DB Access Modules, it is recommended for application code portability.

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:

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.

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:

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:

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.