Oracle Objects for OLE Bound Widget Library and C++ Class Library Workbook

This document contains information regarding the Oracle Objects for OLE Bound Widget libraries (MFC) and a library workbook explaining how to use the libraries.

There are two parts in this document

A. OMFC Bound Widget Libaray

B. Oracle Objects for OLE C++ Class Library Workbook

A. OMFC BOUND WIDGET LIBRARY

OMFC 2.5 BOUND WIDGET LIBRARY

This file documents the OMFC library provided with the Oracle Objects for OLE C++ Class Library. The OMFC library contains classes you can use to build GUI programs using Microsoft's MFC framework. The classes have been built using Visual C++ 1.52 and are for MFC version 2.5.

How to build the sample program.

The sample program can be found in the [ORACLE_HOME]\oo4o\cpp\mfc\samples\empedit

directory (where [ORACLE_HOME] is the directory your Oracle products for Windows 3.1 are installed - usually c:\orawin). The project file is vsdept.mak and can be loaded by Microsoft Visual C++ version 1.5x. There is also another sample in [ORACLE_HOME]\oo4o\cpp\workbook\empedt called empedt.mak. Microsoft store some the path information to locate header files and libraries for all your projects in the msvc.ini file and some are stored in the project make file. To change the path information for all your projects, select Options from the VC++ main menu. Select Directories from the popup menu and a dalog appears containing five edit fields. The second of these contains the path for include files. You will need to add or modify references for the class library include files and the omfc include files. These are installed into [ORACLE_HOME]\oo4o\cpp\include and [ORACLE_HOME]\oo4o\cpp\mfc\include respectively. Unfortunatly, there is an upper size to the path of 128 bytes and it is quite easy to execed this limit if you have many include directories. You may wish to copy all the Oracle Objects include files to one place and just add that directory to the path. The third edit field contains the library path and should be modified to contain the paths to oraclm.lib and omfc.lib. These are installed into [ORACLE_HOME]\oo4o\cpp\lib and [ORACLE_HOME]\oo4o\cpp\mfc\lib respectively. The other option is to add or change the project include file path. Select Options from the VC++ main menu, then select Project from the popup menu. Click on the Compiler button and then select Preprocessor from the list. You can then enter the search path in the Include Path edit window.

In order to run the sample (or any application you build), it will need to access the Class Library runtime DLL - oraclm.dll. The easiest way to do this is to copy it to a directory on your path (such as \orawin\bin) or your windows system directory. The latter is recommended. You can find the DLL in the [ORACLE_HOME]\oo4o\cpp\bin directory, or if you want to debug in the class library, you should use the version in [ORACLE_HOME]\oo4o\cpp\bin\dbg. You will need to ensure that oraclm.lib is included in your project. A debuggable version of omfc.lib is also supplied in ORACLE_HOME]\oo4o\cpp\mfc\lib\dbg.

What problem do these classes solve?

The basic classes of the Oracle Objects for OLE C++ Class Library enable you to access the data in an Oracle database. You can fetch records, add records, edit records, execute arbitrary SQL statements, and so forth. However, if you want to write a GUI program that displays the database data, you are on your own. You must fetch the data, push the data into your GUI widgets, and repeat whenever the dynaset moves to another record. If the widget is used to edit the data, you must execute a StartEdit, SetValue, Update cycle.

The OBinder and OBound classes make this work much easier. An OBinder instance manages a dynaset. OBound instances are attached to fields in the OBinder's dynaset and "bound" to the OBinder instance. From then on, the OBinder and OBound code do most of the tedious bookkeeping for you: the OBound instance values are changed when needed, and when edits are made through the OBound instances they are saved to the Oracle database.

The C++ Class Library provides an implementation of OBinder. However, it provides only a pure virtual OBound class. To make use of the convenience of OBinder, you need subclasses of OBound that implement the OBound functionality.

The classes in this OMFC library are subclasses of OBound. They provide GUI widget implementations of the OBound functionality. As a result you can create a form using Visual C++'s App Studio resource editor and, with very few lines of code, you can hook those widgets to database fields. You then have a working application.

Please see the Oracle Objects for OLE C++ online help system for more discussion of OBinder and OBound.

Note to Visual Basic users: An OBinder object works like a data control (it has no user interface but it performs all the bookkeeping). OBound objects work like bound controls.

What kind of objects are available?

OMFC classes are provided for the following kinds of user interface widgets:

edit controls: display (and edit) values as strings

static text controls: display values as strings

check box: display (and edit) values as "on" or "off"

radio button group: display (and edit) values as a setting from a set of choices

slider: a combination slider and text display that graphically displays

and edits a numeric value

How are these objects used?

This OMFC library (and in fact the entire C++ Class Library) is used to build large model programs.

To make and set up instances of any of these classes, you must go through several steps:

1. First, you must create the user interface widget. In Visual C++ this is easiest to do using Microsoft's App Studio resource editor. You can also create the widget programmatically.

2. Next, you must declare an OBinder instance in your application, typically in the view class for the window where the database form resides.

3. You must declare an OBound subclass instance for each widget (or one for a group of radio buttons). These instances are usually members of the view class for the window where the database form resides.

4. You must call the "BindToControl" method on each of the OBound subclass instances. You should do this just once (for example, in the "OnInitialUpdate" method of your view). The BindToControl method tells the instance variable exactly which widget it is managing. The widget is identified by window pointer (CWnd *) and an ID (for instance IDC_WIDGET1).

5. You must call the "BindToBinder" method on each of the OBound subclass instances. You can do this multiple times, but normally you do it just once (for example, in the "OnInitialUpdate" method of your view). The BindToBinder method tells the instance variable what OBinder to get its data from and what field it should access in the OBinder's dynaset.

6. The final step in setting up the instance is to open the OBinder. This creates a dynaset and fetches records from the database.

Your application can now run. You need to implement some way for the user to navigate through the records of the dynaset. For example, you can create a button with the label "Next" that calls OBinder::MoveNext. The user can make changes in the widgets. Just before the dynaset is navigated to another record (for example, in response to a MoveNext call), all changes on the current record are saved.

7. Finally, when the program is exiting, it is a good idea to call OBinder::Close explicitly. This is not strictly necessary, but is good form. See the OBinder::Close in the Oracle Objects for OLE C++ online help system.

The OBound subclass instances manage the user interface widgets and keep them in synch with data in the database. The instance hooks together the user's view of the data and the database's view of the data. (Step 4 sets up the user interface association; Step 5 sets up the database data association.)

An example

An example is provided that uses the OMFC classes. When you installed Oracle Objects for OLE the sample was placed in the Samples subdirectory of the OMFC directory (if you asked to install sample code). The example allows the user to edit the emp2 table, which is an extended version of the sample emp table provided with Oracle databases. The interesting files are VSDEPVW.H (which declares the bound control variables for the formview) and VSDEPVW.CPP (which uses the bound controls).

The OBinder that controls the dynaset on the emp2 table is a member of a special subclass. It is subclassed so that the instance can override the PostMove and PostAdd trigger methods. The PostMove trigger is overridden so that when a new employee is selected, the department OBinder can be updated to the appropriate department. The PostAdd trigger is overridden so that when a new employee record is created, a department number and employee ID number are filled in automatically.

Methods of all the classes

These methods described below are available in all the classes. The methods that are inherited from OBound are not documented here (BindToBinder in particular). Please see the Oracle Objects for OLE C++ online help system for more information on OBound and its methods.

operator= and copy constructor

All these classes define the assignment operator and copy constructor in the class definition but do not implement them. This prevents the use of the compiler's default assignment operator (or copy constructor), which would be wrong. Neither the assignment operation nor construction by copy is defined for any of these classes. If you inadvertently use assignment or copy construction on one of these objects, you get a link error.

BindToControl

This method associates the object with a particular user interface widget.

Usage: oresult BindToControl(CWnd *wnd, int itemid)

wnd points to the window containing the control

itemid is the control ID (IDC_WHATEVER) identifying the control.

BindToControl can only be called once per object. It dynamically subclasses the control window (calls CWnd::SubclassDlgItem) so that the OBound subclass code receives user interface messages.

The individual classes

OBoundEdit

This is the class you will most often use. It displays the database value in a text Edit control. It has the additional method:

SetProperty

This method sets whether the control is read-only or read-write.

Usage: oresult SetProperty(bool mode=OBOUND_READWRITE);

mode a flag indicating whether the control is read-only or read-write. It can be either:

OBOUND_READWRITE

OBOUND_READONLY

OBoundStatic

This class allows you to bind a static text item. Objects of this class are always read-only. It has no additional methods.

OBoundCheckBox

This class allows you to display and edit database values as a checkbox. It is of greatest value for a database field that only has two possible values, such as TRUE and FALSE.

The SetProperty method enables you to specify what value will be considered "on" and what value will be considered "off". When the user checks the checkbox, the field data is set to the "on" value. When the user unchecks the checkbox, the field data is set to the "off" value.

If the field value is neither "off" nor "on", then the checkbox behaves as follows:

If the checkbox is tristate, it is placed into the "grayed-out" state.

If the checkbox is not tristate, it is off.

SetProperty

This method specifies whether the control is read-only or read-write, and what the "on" and "off" values are for the checkbox.

Usage: oresult SetProperty(const OValue onvalue, const OValue offvalue, bool mode=OBOUND_READWRITE)

onvalue the value corresponding to the checkbox being checked

offvalue the value corresponding to the checkbox being unchecked

mode a flag indicating whether the control is read-only or read-write. It can be either:

OBOUND_READWRITE

OBOUND_READONLY

OBoundGroupButton

A single radio button cannot represent a database field value. A group of radio buttons can represent a value. Each radio button corresponds to a single possible value, and the single radio button that is on indicates the actual database field value. Choosing a different radio button changes the field value.

To use an OBoundGroupButton you create regular user interface widgets. You then call BindToControl on the OBoundGroupButton instance for each radio button in the group. You should call SetProperty for each radio button to set the value of the radio button.

If the current field value does not correspond to the value of any of the radio buttons in the group, none of the radio buttons are selected.

SetProperty

This method specifies whether the control is read-only or read-write, and what value the radio button represents.

Usage: oresult SetProperty(int itemid, const OValue value, bool mode=OBOUND_READWRITE)

itemid identifies which radio button you are setting the value for

value the value that this radio button represents

mode a flag indicating whether the control is read-only or read-write. It can be either:

OBOUND_READWRITE

OBOUND_READONLY

OBoundSlider

A slider is a combination of a scroll bar and a static text display. It is suitable for displaying and editing numeric data that has a known range. The data is displayed and edited using the scroll bar. It is also displayed using the static text display.

The OBoundSlider has a slightly different BindToControl method. It needs to bind two controls simultaneously: the scroll bar and the static text.

BindToControl

This method associates the object with a scroll bar and static text item.

Usage: oresult BindToControl(CWnd *wnd, int scrollid, int staticid)

wnd points to the window containing the control

scrollid is the control ID (IDC_WHATEVER) identifying the scroll bar

staticid is the control ID (IDC_STATIC3) identifying the static text item

Except for the extra id argument, which allows the OBoundSlider to connect to a second user-interface widget, this method works the same as the BindToControl described above under "Methods of all the classes".

SetProperty

This method specifies whether the control is read-only or read-write and sets the range of the scroll bar.

Usage: oresult SetProperty(const OValue minvalue, const OValue maxvalue, bool mode=OBOUND_READWRITE)

minvalue the minimum value of the scroll bar

maxvalue the maximum value of the scroll bar

mode a flag indicating whether the control is read-only or read-write. It can be either:

OBOUND_READWRITE

OBOUND_READONLY

B. Oracle Objects for OLE C++ Class Library Workbook

INTRODUCTION

This Workbook provides examples of C++ code that use the Oracle Objects for OLE C++ Class Library. The Oracle Objects for OLE C++ online help system provides general information on Oracle Objects for OLE as well as detailed descriptions of all the C++ classes and their methods.

Some of the examples discussed in this Workbook are so simple that they are represented only with code fragments. Others are sufficiently complex that the complete code has been provided in separate files. The notes for those examples indicate the names of the files containing the code. The sample files are found in subdirectories of the samples directory, which is a subdirectory of the workbook directory. The samples are installed by default or if you choose to install sample code in a custom install. A few of the examples provide complete projects.

The examples presented here begin with simple, fairly common uses of the library and progress to more complex, less common uses. Clever and dedicated developers (with time on their hands) may learn how to use the Class Library simply by working through the examples. However, using the library will be easier if you read and understand relevant sections of the Oracle Objects for OLE C++ online help system.

This Class Library is intended for use against an Oracle database-a relational database that uses the SQL language as its primary interface. The SQL used in these examples is as simple as possible, and brief explanations are provided of what the SQL statement does in each case. But to exploit the full power of this Library-and of any relational database-you need a good grasp of SQL.

Many of the examples use the standard demonstration tables that are shipped with Oracle databases. Most of the examples entail a database named "ExampleDB", a user named "scott", and a password for that user of "tiger". To use the example code for your own projects, you need to change the database name to refer to your database and the user information to refer to some valid user on your database. (Alternatively, you can create a database alias called ExampleDB that refers to a database with the scott/tiger account.)

To use the Class Library properly, your application must call the OStartup and OShutdown methods. These routines perform necessary initialization and cleanup for the Class Library (for example, they initialize and uninitialize OLE). In the examples that contain only fragments of code, we assume that these routines are called outside the fragments.

Example 1: Just get some data

This very minimal example demonstrates the simplest and most common use of the library: to fetch some simple data from the database. In this example look at a table named "emp", which has several columns. The only column we care about for this example is one called "sal", which represents the salary paid to an employee. The database contains a record for each current employee.

Our task is to find determine the total salary paid-the sum of the salaries of all the employees. To do this we have to

1. connect to the database,

2. query the database and retrieve records, and

3. process the records.

We can do this all in a single subroutine, as follows:

double SumSalary(void)

{

ODatabase datab; // the database object

ODynaset dyn; // dynaset object

double sum=0.0; // sum of all salaries

double cursal; // salary of the current employee

// connect to the database

datab.Open("ExampleDB", "scott", "tiger");

// query the database

dyn.Open(datab, "select sal from emp");

// process all the records

while (!dyn.IsEOF()) // until we've gone past all the records

{

dyn.GetFieldValue("sal", &cursal); // get the current salary

sum += cursal;

dyn.MoveNext(); // move to the next record

}

return(sum);

}

For the sake of simplicity, we have ignored all error handling in this example. In later examples we consider how to handle various errors.

Both the database and the dynaset objects are "opened". This is necessary because an unopened ODatabase has no connection to a database, and an unopened ODynaset has no records. Until these objects are opened they are not very useful. The database connection is established by opening the ODatabase object by passing in the name of the database (ExampleDB), the username (scott), and the user's password (tiger). The ODynaset is opened on a particular ODatabase. ODynasets are always the set of records that is the result of an SQL select on the database. The database is represented by the ODatabase argument to the ODynaset Open method. The ODynaset Open method is also given a SQL statement that indicates which records from the database are to be fetched.

In this case the SQL statement is "select sal from emp" ("emp" is the name of the table being queried). A relational database contains many tables. A query can access one or more of the tables at once. A query against an Oracle database can, in fact, access tables across many databases distributed on a network. The emp table has a number of columns in it; "sal", "ename", and "hiredate" are three of them. Since we are only interested in salary, we ask only for the "sal" column. We could have asked for more columns (the special symbol "*" in the column list gets all the columns). This would not have changed our code, but it would have fetched more data from the database-unnecessarily.

Opening the ODynaset gives us a set of records that match the data in the database. The ODynaset contains the notion of which record is "current". We can use the navigational methods on the ODynaset to move from record to record. By default, when the ODynaset is opened, the first record becomes current. We can then use the ODynaset's MoveNext method to traverse all the records that were fetched.

We use the IsEOF method to tell when we've gone through all the records. It returns TRUE when we've "Move[d]Next" past the last record.

Now that we can navigate through the records, it's time to fetch the data. The simplest way to do this is to use the GetFieldValue method of the ODynaset. This method is overloaded to support different types. The method converts the data from the database to the type you implicitly ask for, if possible. Here we ask for a double, because the salary is stored as a value with fractional dollars. We call GetFieldValue with the name of the column and pass the address of a double variable we want set to the value of the salary in the current record.

Then we add up all the salaries. Sounds simple? Actually, it is, because the server can do most of the work for us. There's no need for us to download all the records (in a big company there may be thousands, and that's a lot of network traffic). Instead, we can let the server do the sum for us.

We replace the line

dyn.Open(datab, "select sal from emp");

with

dyn.Open(datab, "select sum(sal) from emp");

Now the database hands us back a single record that contains the sum of the salaries. Often (as in this case), the server is a more powerful computer than our client workstation. In such cases, it is more efficient to let the server do the simple calculations, and it decreases the total number of bytes that have to be transferred over the network.

If we use this statement as it stands, subsequent references to the returned column need to use the name "sum(sal)", which is clumsy because of the parentheses. One more modification to the SQL statement can make it more elegant. We say:

dyn.Open(datab, "select sum(sal) sumsal from emp");

and now we can refer to the returned column as "sumsal". This renaming can become significant in the case of more complex calculations or when we later change queries but want to keep the same column names.

Our completed routine becomes:

double SumSalary(void)

{

ODatabase datab; // the database object

ODynaset dyn; // dynaset object

double sum; // sum of all salaries

// connect to the database

datab.Open("ExampleDB", "scott", "tiger");

// query the database

dyn.Open(datab, "select sum(sal) ""sumsal"" from emp");

// get the sum of the salaries

dyn.GetFieldValue("sumsal", &sum); // get the salary total

return(sum);

}

Notice one more thing. We connected to the database but we never disconnected. This is correct. When the routine exits, it destroys the ODynaset and ODatabase objects. The destruction of the ODatabase object properly drops the database connection-you don't have to think about it.

Example 2: Execute a SQL statement

Another typical operation with a database is to execute some SQL statement that is not a query. For example, you execute SQL statements to create tables, add users, administer the database, delete a set of records, and so forth. In this example we create a simple table. This example also demonstrates some simple error handling.

// routine to create the states table.

// returns 0 on success, -1 on failure

// There is a bug in this code! (See below) Don't use this!

int CreateStatesTable(void)

{

ODatabase datab; // the database object

oresult ores; // indicates whether operation succeeded

// connect to the database

ores = datab.Open("ExampleDB", "scott", "tigers");

if (ores != O_SUCCESS)

{ // couldn't open the database connection

ErrorMessage(datab.GetSession().GetServerErrorText());

return(-1);

}

// create the table

const char *sqls = "create table states (name char(15), area number, population number)";

ores = datab.ExecuteSQL(sqls);

if (ores != O_SUCCESS)

{ // couldn't create the table

ErrorMessage(datab.GetSession().GetServerErrorText());

return(-1);

}

// everything went just fine

return(0);

}

We connect to the database by opening the ODatabase object. Most methods in the library return a result of the type oresult, which indicates whether the method succeeded or failed. If you determine that the method failed, you need to call other routines to get the actual error. In this case we want the routine GetServerErrorText, which is a method in the OSession class, to get the error text back from the Oracle database software.

We check the return from the Open method. If the routine did not execute successfully, we get an OSession object from the ODatabase object and use it to get the error text. Notice that since the GetSession method returns an OSession, it can be used inline. Since the C++ objects in the Class Library are handles to underlying implementation objects, they are lightweight. The use of temporary objects, such as the OSession that is returned by GetSession, is perfectly reasonable. GetServerErrorText returns a (const char *), which we hand to some generic error processing routine, which presumably alerts the user.

To create the table, we first construct a SQL statement. In this example we are creating a trivial table, so we can use a static string. In general, code puts together some complex SQL statement. Our SQL statement creates a table with the name "states" that has three columns in it: "name", which is a text column with a width of 15 characters, "area", which is a number, and "population", which is another number.

Next we use the ExecuteSQL method to pass the SQL statement to the database to be executed. Once again we check the return and get the error text if necessary.

Because the cleanup work is done for you in the object destructors, you don't have to worry about the database connection-whether or not the open succeeded. Cleanup happens automatically.

Now, when we run this example it fails (we've given the wrong password for the user scott). And GetServerErrorText returns a 0 rather than an error string indicating a bad password. Why? Because GetSession is returning an unopened OSession object. And that's because our database object can't return an OSession because it isn't open. But we're getting the error on the Open! How do we get the correct error?

The answer is first to create an OSession object explicitly, and then open the database using that session. Then, if the database open fails, we can refer to our OSession object. Rewritten, the code looks like this:

// routine to create the states table.

// returns 0 on success, -1 on failure

// Corrected version

int CreateStatesTable(void)

{

OSession sess; // database session object

ODatabase datab; // the database object

oresult ores; // indicates whether operation succeeded

// open the default (unnamed) session

ores = sess.Open();

if (ores != O_SUCCESS)

{

ErrorMessage(sess.GetErrorText());

return(-1);

}

// connect to the database

ores = datab.Open(sess, "ExampleDB", "scott", "tigers");

if (ores != O_SUCCESS)

{ // couldn't open the database connection

ErrorMessage(sess.GetServerErrorText());

return(-1);

}

// create the table

const char *sqls = "create table states (name char(15), area number, population number) ";

ores = datab.ExecuteSQL(sqls);

if (ores != O_SUCCESS)

{ // couldn't create the table

ErrorMessage(sess.GetServerErrorText());

return(-1);

}

// everything went just fine

return(0);

}

This version is almost the same as the earlier one, except that we use our explicit OSession object to get the database errors, and there's code to open the OSession object. Opening the OSession is very straightforward. Notice that if the OSession Open fails we get an error message by calling GetErrorText rather than GetServerErrorText. GetServerErrorText is appropriate when the error is a problem with the database. This can't be the case until we're actually connected to, or trying to connect to, the database. If the OSession open fails, it is due to some internal problem such as low memory. So we use GetErrorText instead.

Example 3: A joined query

One of the most important and powerful features of a relational database is the possibility of querying data from several tables at once. Instead of formulating and coordinating several related queries on different tables, the queries can be combined. Most of this example is about writing the SQL needed for a joined query. If you are already familiar with SQL, you can skim through this example, but be sure to read the very last paragraph.

Consider the emp and dept sample tables that are installed in the scott account of most Oracle7 sample databases. The emp table contains the employee name (ename field), the employee's job (job field), the employee's salary (sal field), and the department number where the employee works (deptno field). A related table is the dept table. It also contains the department number (deptno field), as well as the department name (dname field) and the department location (loc field).

Suppose we want to write an application that looks at where the different employees work. We want to query employee names and the location of the departments where they work. One possible solution (though not a good one) is to create two separate queries:

// bad solution to employee & department join

ODatabase datab;

datab.Open("ExampleDB", "scott", "tiger"); // open the database

ODynaset empdyn; // employee query

ODynaset deptdyn; // department query

empdyn.Open(datab, "select ename from emp");

deptdyn.Open(datab, "select loc from dept");

Now we have one query for employee names and another for department locations. Not only do we need to manage the two queries, but we do not know which record in the department list corresponds to which employee. The two queries are not sorted: the first department is not necessarily the department of the first employee.

However, the two tables do share a field: deptno. This field is the "key" that relates the two tables. Now we could write:

// still not a good solution to employee & department join

ODatabase datab;

datab.Open("ExampleDB", "scott", "tiger"); // open the database

ODynaset empdyn; // employee query

ODynaset deptdyn; // department query

empdyn.Open(datab, "select ename, deptno from emp");

deptdyn.Open(datab, "select loc, deptno from dept");

Now whenever we look at an employee record, we can read its department number. Then we can scan through the department records to find the department with that department number. Then we'll have the location. The trouble with this solution is that it requires us to scan those department records pretty often. When we're writing a client-server application, this is the kind of processing that the server does best. The server is very good at coordinating data; there's no reason to download the data to the client workstation for coordination.

What we can do here is tell the server to match the deptno fields of the two tables. Then we have:

// the joined query solution

ODatabase datab;

datab.Open("ExampleDB", "scott", "tiger"); // open the database

ODynaset jdyn; // joined query

jdyn.Open(datab, "select emp.ename, dept.loc from emp, dept \

where emp.deptno = dept.deptno");

Now we have a dynaset with two fields in it: ename and loc. These are just the fields we wanted. And the loc field corresponds to the location of the department where the employee works. In effect, when the server was preparing the records for return to our client, it did the coordination, more efficiently than we could have done it ourselves (because of all the machinery of a relational database).

A couple of points are noteworthy:

· The first is the syntax of the SQL statement. We specified which fields we wanted in our query. Since the fields are coming from several tables, we fully specified the fields by calling them by names like "table.field". When we specified the tables we were querying, we gave a comma-separated list of all the tables we wanted. Finally, we gave a "join condition", which is just a "where" clause that specifies how we want to coordinate the data from the various tables.

· Another point is that the deptno field is not one of the fields in our dynaset. If we wanted deptno we would have to include it in the list of select fields as: "select emp.ename, emp.deptno...".

· By the way, the backslash ("\") is not part of the SQL statement. It is just the standard C syntax for continuing a literal across multiple lines.

· Finally a most important point: Dynasets formed from joined queries are essentially read-only. The "records" in such a dynaset do not correspond to actual records in any table. Therefore it is not possible to delete, update, or add new records to such a dynaset: there's no table to be changed so it doesn't make sense. Other kinds of queries, such as queries that have computed columns, are also read-only.

Example 4: A connection dialog

You need to specify three things to open a database object: the database name, the user name, and the user's password. You can hard code these values into your code, but that is a bad idea because then you won't be able to use the same program for another user or against a different database. It is much better to generalize the connection information.

The obvious way to do this is to create a dialog that obtains the information. We want a routine that hands back an open ODatabase object if the connection was possible and a closed ODatabase to indicate failure. We want the ability to create the ODatabase with different options.

The sample subdirectory logdlg contains the files LOGDLG.CPP and LOGDLG.H. These files contain the code for an implementation of such a login dialog using Visual C++'s MFC framework. The dependency on the framework is slight, so you don't have to be very experienced with MFC to understand what the code is doing.

The important portion of the logdlg class follows:

class logdlg : public CDialog // subclass of a dialog

{

public:

// Construction

logdlg(CWnd* pParent = NULL); // standard constructor

// get a database login

ODatabase GetLogin(long options = ODATABASE_DEFAULT);

private:

OSession m_session; // our handle to the default session

ODatabase m_database; // our handle to the database object

// more implementation details...

};

An instance of the class is a dialog, which is what is created here. Then, when the client of the class wants a valid and open ODatabase object, the method GetLogin is called. This method runs the dialog, tries to make the database connection, notifies the user of errors, and finally returns.

You can see from the source of GetLogin that it does very little. It performs some setup and then calls the "DoModal" routine to run the dialog. It then returns an ODatabase object, which may or may not be open. The real work is done when "OK" is pressed. Here's the method that gets called then:

void logdlg::OnOK()

{

CString dbname; // database name string

CString user; // user name string

CString password; // password string

// get the strings the user has entered

GetDlgItem(IDC_USERNAME)->GetWindowText(user);

GetDlgItem(IDC_PASSWORD)->GetWindowText(password);

GetDlgItem(IDC_DATABASE)->GetWindowText(dbname);

// try to open the database

if (m_database.Open(m_session, dbname, user, password, m_options) != OSUCCESS)

{ // some error

// get the oracle error number

long oraerr = m_session.ServerErrorNumber();

// get the oracle error message, to display to the user

const char *dberrs = m_session.GetServerErrorText();

ErrorMessage(dberrs); // tell user what went wrong

}

else

{

// we're done - get out of the dialog

CDialog::OnOK();

}

return;

}

This method gets the text from the login dialog and uses it to try to open the ODatabase object. When it is opening the ODatabase object it uses the options that were passed in to GetLogin. If there is an error, the generic "ErrorMessage" routine is called (which may just put up a simple message box). Only if the open was successful is the parent dialog class's routine "OnOK" called (which closes the dialog).

There are two ways for the dialog to end: Either the OK is successful, in which case the returned ODatabase object will be open, or the user chooses cancel, in which case the returned ODatabase object will be closed. If the user chooses "OK" to try to connect and the connection fails, the above routine alerts the user but does not close the dialog.

Note again that it is reasonable to pass ODatabase instances around. Here we are using one as the return value from a subroutine. A caller might have some code like this:

// connect to the database

ODatabase database;

logdlg dblogin;

database = dblogin.GetLogin();

if (database.IsOpen())

; // success

else

; // user canceled for some reason

Useful improvements to the dialog would be to allow the fields to be preset, or to allow database options to be set on the ODatabase.Open.

Example 5: Updating a dynaset

Sometimes you may need to do more than just read the contents of a dynaset. You may also want to modify the records in the dynaset, and therefore the database itself.

You can update database records using the Class Library in two ways. The first is to execute an update SQL statement. The second is to use the dynaset to edit the records.

Consider the emp table. It contains employee salary information. Suppose the company has had a good year and everyone is getting a $1000 salary increase.

// give all employees a raise of amount "raise"

oboolean GiveRaise(int raise)

{

ODatabase db; // our database

logdlg db_login; // used to connect to database (see example 4)

// open a connection to the database

db = db_login.GetLogin();

if (!db.IsOpen())

return(FALSE); // indicates that we couldn't give out the raises

// give everybody a raise

char sql[80]; // for constructing the sql statement

sprintf(sql, "update emp sal = sal + %d", raise);

if (db.ExecuteSQL(sql) != OSUCCESS)

return(FALSE); // we couldn't do it

// would be a good idea to look at errors to see why

return(TRUE); // we've given the raises

}

Notice that we run a login dialog in this routine and then, by exiting the routine and destroying the ODatabase instance, we drop the database connection. Generally a routine like this should probably pass in the ODatabase object as a parameter so that many such operations can share a login.

Updating records in this way is very efficient because the server does all the work. But in more complex situations it is not possible to use such a simple update SQL statement. A more realistic example would be a case in which the raise for each employee is calculated in some fashion and then applied to each record. This requires us to go through every record in the database and update some of them as needed. Here's a routine to do that:

// Calculate and apply raises to all employees

oboolean GiveRaises(const ODatabase &db)

{

if (!db.IsOpen())

return(FALSE); // we can't work without an open database

// create a dynaset referring to the employees

ODynaset dyn;

dyn.Open(db, "select ename, sal, hiredate from emp");

if (!dyn.IsOpen())

return(FALSE); // can't open dynaset

double salary, raise;

// go through all the records

while (!dyn.IsEOF())

{ // for every record

raise = CalculateRaise(dyn); // calculate raise somehow

if (raise > 0)

{ // edit this record

dyn.StartEdit(); // begin editing this record

dyn.GetFieldValue("sal", &salary); // get old salary

dyn.SetFieldValue("sal", salary + raise); // set new salary

dyn.Update(); // finish editing this record

}

dyn.MoveNext(); // go to the next record

}

return(TRUE);

}

There are three steps to editing a record:

1. First, call the StartEdit method to tell the dynaset that you're about to edit the record.

2. Second, make calls to change values in the current record. These could be Dynaset.SetFieldValue calls or they may be OField.SetValue calls.

3. Finally, call Update to push the changes into the database.

This method is much more flexible than a direct update, but it is also slower. StartEdit must go to the database and attempt to lock the record. It also checks to see if the data in the database is different from the data in the dynaset (which would indicate that someone else has edited the record, invalidating the dynaset). If another dynaset or program has a lock on the record, StartEdit can either wait indefinitely or can return a "can't obtain lock" error. (See the documentation on ODatabase options for the ODATABASE_NOWAIT option.)

Finally there is a subtle difficulty when updating (or making any other kind of change) to dynasets or to the database: Changes made to records in a dynaset are immediately reflected in that dynaset. Any changes made by other users, or other dynasets in your program, are not reflected immediately. If the dynaset has not yet fetched the row that was changed, it gets the new value when and if it does fetch the row. But it is difficult to predict which rows have really been fetched (because of caching). You can guarantee that the dynaset has the most up-to-date version of the data by performing a Refresh on the dynaset.

Example 6: Adding and deleting records with a dynaset

Example 5 covers the editing of existing records. In many cases you need not only to edit records but to add new records and to delete existing ones. The ODynaset method DeleteRecord deletes the current record. To delete a record, navigate to it and then execute DeleteRecord.

Here is a routine that deletes all employees with a salary above a certain amount.

oboolean DeleteOverpaid(const ODatabase &db, double maxsal)

{

if (!db.IsOpen())

return(FALSE); // error because database object isn't open

ODynaset dyn;

OField salary;

dyn.Open(db, "select sal from emp");

salary = dyn.GetField("sal");

while (!dyn.IsEOF())

{

if ((double) salary > maxsal)

{ // this employee is overpaid. Delete.

dyn.DeleteRecord(); // deletes the current record

}

dyn.MoveNext(); // go through all the records

}

return(TRUE);

}

Once we delete a record with DeleteRecord, the current record becomes invalid (it has been deleted). Most operations on such a record fail. Navigating to another record gets us to a valid record. We won't be able to navigate back to the deleted (thus invalid) record.

In this example we get the field value by using an OField object rather than directly through the ODynaset object. OField objects are closely linked to the underlying dynaset: as navigation occurs in the dynaset, the field value changes to contain the current record value. An OField instance is more convenient to use than going through the dynaset, because it can be treated as a variable, as illustrated by the "(double) salary > maxsal" statement above.

Adding a new record is a little more complex. You can use two methods to add records to a dynaset. The first is AddNewRecord, which adds a blank record. The second is DuplicateRecord, which adds a blank record and then fills it with the field values found in the current record (current at the time DuplicateRecord is called). When a blank record is created, it is either filled with NULLs or sent to the database server to have defaults filled in, depending on whether the ODATABASE_PARTIAL_INSERT option has been turned on. (See the ODatabase documentation for details.)

After adding the record you may change some of its values. The AddNewRecord or DuplicateRecord call allows editing similarly to the way that StartEdit does. When you have made all the changes you want, call Update to put your changes into the database.

When you are adding new records, it is important to be aware of the structure of the table that you are editing. Some fields may require unique values. Some fields may not allow NULL values. These requirements must be fulfilled or the Update call fails.

Here is a routine that adds a new record to the employee table.

oboolean AddEmployee(ODynaset *empdyn, const char *ename,

double salary, int deptno)

{

if (!empdyn->IsOpen())

return(FALSE); // can't work with unopened dynaset

// Add the new record

if (empdyn->AddNewRecord() != OSUCCESS)

return(FALSE);

// Set the values of the record

empdyn->SetFieldValue("ename", ename);

empdyn->SetFieldValue("sal", salary);

empdyn->SetFieldValue("deptno", deptno);

// the empno field is required and must be unique.

// We will find the current maximum empno and add 1 to it.

ODynaset empnodyn;

empnodyn.Open(empdyn->GetDatabase(),

"select max(empno)+1 newempno from emp");

int empno;

empnodyn.GetFieldValue("newempno", &empno);

// finish setting the employee record

empdyn->SetFieldValue("empno", empno);

// put this record in the database

if (empdyn->Update() != OSUCCESS)

return(FALSE);

return(TRUE);

}

The values of most of the fields were left NULL. We set the values of ename, sal, and deptno based on the arguments to the routine.

The empno field is used as a unique key in the employee table, so it must be unique and non-NULL. An easy way to specify this is to ask the server for the highest current employee number, and then have the server add 1 to that number so that we get a new unique value.

In practice this is not a good solution, for two reasons. First, we are making an extra query to the database for every record added. It would be faster to figure out a good employee number at the beginning of the program and then use that number (incrementing it or whatever). Second, this method guarantees only that the empno field is unique among current records. If an employee record was deleted, its employee number field might get reused. This is generally not desirable. The database has objects called "sequences" that help with this kind of problem.

As with editing and updating a dynaset, there is a difficulty with cross-dynaset consistency. If a dynaset contains a record, and it has been fetched from the server already, then when another dynaset deletes that same record there will be a "ghost" record in the first dynaset. Similarly, records added to the first dynaset won't show up in other existing dynasets. (Added records can be seen by refreshing the dynaset.) Attempting to edit a record that has been deleted from the database will fail on the StartEdit method.

Example 7: Transaction control

Normally, when you change the database data with dynaset operations, the database immediately reflects the changes you have made. When you add records or change field values, the database is changed when you execute the Update method. When you delete records, the database is changed when the DeleteRecord method is executed. This is sufficient when changing a record is an independent operation.

But sometimes you have an entire set of changes that must be made together. Either they are all made or none of them are made. The classic example is an application that maintains bank account balances. When a transfer is made from one account to the other, two separate records are edited. The record that records the balance of one account is debited and the record of the other account is credited. These two operations must succeed or fail together. If one takes place without the other, the bank's books will not balance properly.

The solution is to enclose the set of changes in a transaction. Before making the first change you call the BeginTransaction method. Then you make your changes. If you want to cancel the changes, you call the Rollback method. If you want to make the changes permanent, you call the Commit method. Either Rollback or Commit completes the current transaction. Then, by default, we return to the normal behavior of changing the database directly on each Update or DeleteRecord.

Note to experienced Oracle developers: The normal transactional model of the Class Library is similar to the autocommit mode in SQL*Plus or Oracle Forms. Executing BeginTransaction is like turning autocommit off. Executing Rollback or Commit is a rollback or commit. And the default behavior of Rollback or Commit is then to reenter autocommit mode.

The example we consider here uses DynasetMarks. These are objects that remember a position in a dynaset. You can get a mark on the current position and then later use the MoveToMark method to reposition on that record again.

Here's a bank account example:

// transfer money from credit to debit account

// we have a DynasetMark on the two accounts

void TransferMoney(ODynaset accounts, // dynaset of bank accounts

ODynasetMark credit, // mark on record to be credited

ODynasetMark debit, // mark on record to be debited

double amount // amount to transfer

)

{

// get the dynaset's session

OSession banksess = accounts.GetSession();

// start the transaction

banksess.BeginTransaction();

// make the transfer

double balance; // an account's balance

// credit one account

accounts.MoveToMark(credit);

accounts.StartEdit();

accounts.GetFieldValue("balance", &balance);

accounts.SetFieldValue("balance", balance + amount);

if (accounts.Update() != OSUCCESS)

{ // couldn't change the first record

banksess.Rollback();

return;

}

// debit the other account

accounts.MoveToMark(debit);

accounts.StartEdit();

accounts.GetFieldValue("balance", &balance);

accounts.SetFieldValue("balance", balance - amount);

if (accounts.Update() != OSUCCESS)

{ // couldn't change this record

banksess.Rollback();

return;

}

// everything is fine - commit the transaction

banksess.Commit();

return;

}

(Here we're checking for errors only on the Update statement. A real application would be more careful and would check for errors on all the methods.)

How could the second update fail and the first succeed? The database connection might have been lost (maybe because of a hardware failure in a network). Or perhaps the new account balance violated a database constraint (for example, there might be a trigger stored in the database that caused the update to fail because the account balance dropped below zero).

Example 8 shows another way to accomplish the same task. Rather than using a dynaset, it calls ExecuteSQL directly to make the database changes. Changes made to the database using ExecuteSQL (by way of update, insert, and delete statements) are part of the transaction that is managed by BeginTransaction and Commit or Rollback.

The Commit and Rollback methods take an oboolean argument named startnew, which is FALSE by default. If you set it to TRUE, a new transaction is started immediately after the Commit or Rollback (just as if you had called BeginTransaction again).

Oracle developers note: If you call BeginTransaction immediately after opening the session and always set startnew to TRUE, you will have a transactional environment similar to the Oracle environment to which you are accustomed.) Consider the following code:

// fragment illustrating Oracle7 transaction details

OSession sess;

sess.Open(); // open the default session

sess.BeginTransaction(); // start a transaction

ODatabase db;

db.Open(sess, "ExampleDB", "scott", "tiger");

ODynaset dyn1;

dyn1.Open(db, "select * from emp order by empno");

// change the first record

dyn1.MoveFirst();

dyn1.StartEdit();

dyn1.SetFieldValue("sal", 7500);

dyn1.Update();

// open another dynaset

ODynaset dyn2;

dyn2.Open(db, "select * from emp order by empno");

dyn2.MoveFirst();

long salary2;

dyn2.GetFieldValue("sal", &salary2);

// open yet another dynaset

// open a named session

OSession nameds;

nameds.Open(sess.GetClient(), "session2");

ODatabase db2;

db2.Open(nameds, "ExampleDB", "scott", "tiger");

ODynaset dyn3;

dyn3.Open(db2, "select * from emp order by empno");

dyn3.MoveFirst();

long salary3;

dyn3.GetFieldValue("sal", &salary3);

The second ODynaset was opened within the same session as the first. The first ODynaset made a change that has not yet been committed to the database. The third ODynaset was opened on a different session. What are the values of salary2 and salary3?

Because dyn2 is in the same session as dyn1, it sees the same database state. It will see a salary of 7500. But dyn3 is in a different session, so it will not see a salary of 7500; rather, it will see the salary that was in the field before it was changed. This result is independent of whether dyn3 is a part of the same process or not. Dyn3 could be on another computer altogether.

Example 8: Parameters

All of the SQL statements we have used in the previous examples have been entirely literal. So, when we wanted the employee records of Department 20, we use the SQL statement: "select * from emp where deptno = 20". And if we then wanted the records from Department 10, we used "select * from emp where deptno = 10". This is inefficient in a number of ways. It would be more efficient if we could say: "select * from emp where deptno = :deptno", where :deptno is something that can be set to a value at a later time. This is precisely what a parameter is: A parameter allows you to introduce variables into the processing of an SQL statement.

Parameters are attached to an ODatabase object and are accessed by way of an object called OParameterCollection. The OParameterCollection object exposes a variable number of parameters, and you can add or remove parameters from the collection. (The other collection types-OFieldCollection, OConnectionCollection, and OSessionCollection-are read-only.) You can create as many parameters as you want. At any given time, any parameters that are auto-enabled will be bound to your SQL statement. (For more information, consult the Oracle Objects for OLE C++ online help system.

The following code uses a parameter to select different sets of employee records.

// main routine (processes departments)

ProcessCompany(ODatabase *db)

{

// get a list of the department numbers

ODynaset deptnumbers;

deptnumbers.Open(*db, "select deptno from dept order by deptno");

OField deptnof = deptnumbers.GetField("deptno"); // the deptno column

// set up a "dnumber" parameter on the database

OParameterCollection params = db->GetParameters();

// create parameter with initial value of 0

params.Add("dnumber", 0, OPARAMETER_INVAR, OTYPE_NUMBER);

OParameter dnumber = params.GetParameter("dnumber");

// process every department

deptnumbers.MoveFirst();

while (!deptnumbers.IsEOF())

{

// set the value of the parameter

dnumber.SetValue((int) deptnof);

// process that department

ProcessDepartment(db);

// go to next department

deptnumbers.MoveNext();

}

// all done. We don't need the dnumber parameter to be part of

// the collection anymore so get rid of it

params.Remove("dnumber");

return;

}

void ProcessDepartment(ODatabase *db)

{

// open a dynaset of employees for the current department

ODynaset emps;

emps.Open(*db, "select * from emp where deptno = :dnumber");

// process them all

emps.MoveFirst();

while (!emps.IsEOF())

{

// do something interesting here

emps.MoveNext();

}

return;

}

In ProcessCompany we set up the parameter that is then used by the subroutine ProcessDepartment. We can then write the ProcessDepartment routine more generally. The department number it is processing is not hard-coded into it anywhere; rather, it depends on the parameter "dnumber" existing for the database, which is its argument.

The parameter is created by using the Add method on the ODatabase's parameter collection. The parameter's value can be changed at any later time. However, changing the parameter's value does not instantly change the contents of any dynaset that was created using the parameter. The parameter's value is used only at the time that the SQL statement is executed, which is either at the ExecuteSQL call, the ODynaset Open, or the ODynaset Refresh. Finally, when we are finished with the parameter we can remove it, as in the example, with the OParameterCollection::Remove method.

Some details of this example are noteworthy. The ODatabase is passed into the ProcessCompany routine by address. It is legal to dereference an ODatabase address, and that is what we do here. It is also possible (and inexpensive) to pass the ODatabase by value. An OField object is used to get the department number out of the deptnumbers dynaset. An OField always gives you, from the current record, the value of the field to which it is attached. Therefore, as the dynaset is navigated, the OField objects returns different values. Because various cast operators are overloaded for the OField object, getting the value of the column is just a matter of casting the OField object.

Here is a more realistic version of the application from Example 7:

// transfer money from one account to another

// SQL statement to change a balance in the accounts table

static const char *setbalance =

"update accounts set balance = balance + :amount where accno = :anum";

void Transfer(ODatabase *bankdb, // the bank database

int debitaccount, // account to debit

int creditaccount, // account to credit

double amount) // amount to transfer

{

// we assume the existence of the parameters "amount" and "anum"

// begin the transaction

if (bankdb->GetSession().BeginTransaction() != OSUCCESS)

return; // couldn't start transaction

// get the parameters

OParameter amount = bankdb->GetParameters().GetParameter("amount");

OParameter anum = bankdb->GetParameters().GetParameter("anum");

// credit the first account

anum.SetValue(creditaccount);

amount.SetValue(amount);

if (bankdb->ExecuteSQL(setbalance) != OSUCCESS)

{

bankdb->GetSession().Rollback();

return;

}

// debit the second account

anum.SetValue(debitaccount);

amount.SetValue(-amount);

if (bankdb->ExecuteSQL(setbalance) != OSUCCESS)

{

bankdb->GetSession().Rollback();

return;

}

// it all worked

bankdb->GetSession().Commit();

return;

}

To use this routine, you open the bank database and set up the two parameters "amount" and "anum". Then for each transfer you call the Transfer routine. Notice that the actual SQL statement that is used is external to the routine. The SQL statement could actually be moved into some other code, making this Transfer routine even more general and maintainable.

Example 9: An advisory

Occasionally you need some piece of your code to be notified when something happens to a dynaset-either navigation or some editing. Typically the dynaset is controlled by one portion of your code and you monitor it with some other portion of your code (for example, some kind of general access management package).

You accomplish this monitoring with an advisory. Specifically, you attach an OAdvise object to an ODynaset. From then on, whenever something happens to the dynaset, messages are passed to the OAdvise object. The advisory can exert some control over the dynaset actions (for example, it can cancel them), and it can monitor what is happening.

The OAdvise class that is part of the Class Library does nothing. To get a useful advisory object, you must create a subclass of OAdvise, and then create an instance of that subclass. By overriding a few virtual functions, you can obtain the functionality you desire.

The samples directory contains a subdirectory called posadv, which contains the files POSADV.CPP and POSADV.H. These files implement a subclass of OAdvise called PosAdvise. The PosAdvise class keeps track of which record the dynaset is currently on- the first record being 0, the next 1, and so forth. It cancels the actions that it sees that would disturb its bookkeeping, such as a DeleteRecord.

Here's how you use PosAdvise:

// use of PosAdvise class

// open a dynaset

ODynaset dyn1;

dyn1.Open(db, "select * from emp");

// attach a PosAdvise advisory to the dynaset

PosAdvise dynposition;

dynposition.Open(dyn1);

// move to beginning of dynaset to get dynposition started

dyn1.MoveFirst();

// position the dynaset before we do some processing

PositionDynaset(dyn1);

// get the position

long startpos = dynposition.GetPosition();

// now do some processing

ProcessDynaset(dyn1);

// how many records did we process?

long nrecords = dynposition.GetPosition() - startpos;

The advisory is attached to the dynaset by using the OAdvise Open method. It is detached automatically when it is destroyed, or when Close is called.

Actions taken with the dynaset cause several things to happen:

· Before the action takes place, the ActionRequest methods of all attached advisories are called. The advisories can cancel the action at that point. (The PosAdvise class cancels all actions except MoveFirst, MoveLast, MoveNext, and MovePrev.)

· Next, if the action was allowed by all the advisories, it takes place.

· After the action, the ActionNotify methods of all the attached advisories are called.

· The PosAdvise class uses this notification to recalculate the position within the dynaset.

· Finally, the PosAdvise class can implement additional methods beyond the bounds of OAdvise. It implements the GetPosition method.

Example 10: OBound of a variable

All of our examples thus far have accessed the data in a dynaset more or less directly, either through ODynaset methods or using OFields. These are not always the most convenient access methods. Whenever we navigate to a new record and are interested in the values of various fields, we need to fetch the values explicitly. Further, when we want to edit a record or add records, there is a moderate amount of bookkeeping to do. It would be convenient to have a "managed dynaset"-an object that does the more routine work for us. This is the job of the OBound and OBinder classes.

Users of Visual Basic will note that the OBinder class plays the role of the data control (though without any user interface), and subclasses of OBound play the same role as bound controls.

You use an OBinder object much as you would use an ODynaset object. It is opened with database and SQL information, for example. It has methods that enable callers to add and delete records. In addition to the work that a dynaset can do, an OBinder object can keep track of changes that have been made to fields and can update the database automatically.

OBound objects hold the value of a single field of a record. When the dynaset changes, the value of the OBound instance is changed. And when the value of the OBound instance changes, the value of the corresponding field in the dynaset changes-with all the appropriate bookkeeping handled.

In the OMFC and OOWL libraries that are provided with the Class Library, you can see the use of OBinders and OBounds. Those libraries contain machinery that creates normal widgets that are also subclasses of OBound. As a result, in a GUI program that uses these specially subclassed widgets, when the user edits the text of a widget and then navigates to another record, the database editing is done automatically (see Example 11).

In this example we subclass OBound so that we have an OValue variable that works with the OBinder-managed dynaset. The value of the bound OValue object is automatically set to the current value of the field with which it is associated. When the value of the OValue object is changed, the database is updated to reflect the change. We call this class OBoundVal. The implementation of this class is stored in the samples directory, in the subdirectory boundval. Our goal is to be able to write a program like this:

// silly example of use of OBoundVal

void GiveRaises(int minsalary, int saladd)

{ // give everybody with salary below minsalary a raise of saladd

// open connection to database

ODatabase odb("ExampleDB", "scott", "tiger");

// construct the OBinder (the managed dynaset)

OBinder block;

// set up an OValue bound to the salary field

OBoundVal salary;

salary.BindToBinder(&block, "sal");

// Note that we are binding the "sal" column before opening the

// query. If the select list for the query does not contain the

// "sal" column, the OBinder.Open() call will fail.

// get the database data for the managed dynaset

block.Open(odb,"select ename, sal, empno from emp order by empno");

// If we do the binding here and the column we are attempting to

// bind does not exist, the following call will fail:

// salary.BindToBinder(&block, "sal");

// Note that IsLast() will return TRUE after MoveNext() attempts to

// move past the last record. Therefore the last record does get

// processed in the loop

while (!block.IsLast())

{

// check the salary of the current employee

if ((int) salary < minsalary)

{

// change the salary

salary = (int) salary + saladd;

}

block.MoveNext();

}

return;

}

The most interesting part of this sample is the line where the salary is changed. It appears that we're setting the value of a variable. However, we're actually changing the value of a field in the database. The database gets updated when we navigate to another record.

The OBound class has three very important methods:

1. Refresh is called (by OBinder) to give the OBound a new value whenever the field's value changes.

2. SaveChange is called (by OBinder) whenever the OBound should save its value to the database.

3. Changed is called by the OBound subclass implementation to notify OBound and OBinder that the value has changed.

Refresh and SaveChange must be implemented by any subclass of OBound. If the OBound subclass overrides any of the base class triggers steps, the overloaded triggers should call the default triggers in the base class to work correctly. Please see the online documentation for the OBound class for more details. The implementation for OBoundVal is shown below.

Refresh transfers the value from the database to the OBound object. SaveChange transfers the value from the OBound object to the database. Changed notifies the OBinder bookkeeping that a change needs to be saved to the database.

OBound is generally used as a "mix-in" class, adding functionality to some other class hierarchy. In this example we add functionality to the OValue class. OBoundVal multiply inherits from OBound and OValue.

Here is the Refresh method for OBoundVal:

oresult OBoundVal::Refresh(const OValue &val)

{

OValue::operator=(val); // use OValue assignment to get new value

return(OSUCCESS);

}

When we are handed a new value, we use OValue's assignment operator to save that value. This routine is called by the OBinder class. Here is the SaveChange method:

oresult OBoundVal::SaveChange(void)

{

return(OBound::SetValue(*this));

}

We use the OBound helper routine OBound::SetValue to set the value. SaveChange is called by the OBinder class.

But how do we set the value of an OBoundVal? If we just allow the use of OValue::SetValue, our value will get changed but the OBoundVal class won't know about it. Further, we won't tell the machinery that there has been a change, so no change will get saved to the database. Here is the proper implementation for setting the value with an integer argument:

oresult OBoundVal::SetValue(int val)

{

if (!Changed())

return(OFAILURE); // couldn't start change

OValue::SetValue(val);

return(OSUCCESS);

}

The first thing we do is to call the Changed() method. (The default argument is TRUE, which indicates that we are making a change.) This sets a flag indicating that this OBound needs to have SaveChange called on it later, and telling the dynaset to attempt a StartEdit. This may fail, for a variety of reasons: the user may not have permission to edit this database, the record may be locked, and so forth. Only if the StartEdit is successful do we set the value of the OBoundVal, using the parent method OValue::SetValue.

We need to ensure that we override every OValue method that changes the object's value, because we need to call Changed() to make the OBinder bookkeeping aware of any change in the object's value. So we must also override the Clear and operator= methods. And, for extra utility, we also provide a number of extra operator= methods so that the OBoundVal can act like a regular variable as much as possible.

Note that when we use the operator=, we are copying the OBoundVal's value, not the object itself. There are subtle differences. In general, subclasses of OBound want to copy values of objects rather than the objects themselves. For this reason the copy constructor and assignment operator are not implemented for OBound.

There isn't much difference in the way an OBoundVal is used and the way an OField object is used. Both return the field's value for the current record. Both enable you to set the field's value. However, the OBoundVal copies a value every time the dynaset moves to another record, but the OField does not. OField gets the value out of the dynaset only when it is requested. At the same time, If you use OField for editing, you must manage the StartEdits and Updates of the dynaset yourself.

OBound becomes much more useful when the database fields are represented in a user interface. Then, the code for handling changes can be quite distributed, so keeping track of whether StartEdit has been called or not (for example) would be troublesome.

Example 11: A full emp table editor

This example demonstrates the construction of a complete application. We built the application using Microsoft's Visual C++ development environment, including the MFC framework classes. (Users of other development environments will be able to understand what was built from this example.) The sample source is in the EMPEDT subdirectory (emp table editor).

The example uses the connection dialog from Example 4, which provides a convenient interface for the user to log into the Oracle database. The main screen is a single window containing a form that displays and allows editing of all the fields of the employee table. We built it as an MFC formview. For editing the records of the table, we use the OMFC bound control classes (built using the OBinder and OBound machinery) provided with the Class Library. The example also includes a feature that generates a unique employee number automatically when new employee records are added. The amount of code needed to write this application is surprisingly small.

This example is broken up into several sections:

Application Layout discusses the basic creation of the application

Form Creation discusses the building of the main screen

Binding discusses the OBinder-OBound hooks to the form

Buttons discusses the implementation of navigation buttons

Using a Trigger discusses the implementation of the new employee number feature

Error Handling discusses the handling of errors

Application Layout

We generated the original application with AppWizard. We changed the view class to a form view so that a user interface could be constructed quickly with the App Studio resource editor. The application's memory model needs to be set to large.

We set the project to link against the oraclm, omfc, ole2, and ole2disp libraries. Although you don't need to interact with OLE to use the class library, the class library uses OLE internally, so it needs to be linked.

We use the connection dialog from Example 4. We added the files LOGDLG.CPP and LOGDLG.H to the project. Then we copied the dialog resource IDD_LOGIND from the LOGDLG.RC resource file to the EMPEDT.RC resource file.

We made one simple change to the application class: we placed the class library initialization call (OStartup) in CEmpedtApp::InitInstance. We added a destructor method for the application class (~CEmpedt) and placed in it a call to OShutdown.

Form Creation

The purpose of this application is to edit the emp table-one of the standard Oracle demonstration tables. The emp table has eight fields: ename (employee name), empno (employee number), job (a keyword describing the employee's job), mgr (the employee ID of the employee's manager), sal (the employee's salary), comm (a commission to be paid to the employee, which is often NULL), hiredate (date that the employee was hired) and deptno (identification number of the employee's department-used to reference the dept table).

We built the most straightforward kind of form to edit this table: one text edit per database field. We edited the form in App Studio. We gave each edit control a unique ID. We also placed a static text item next to each edit control as a label. The IDs of the edit controls, their order, and what the label says or what relation it has to the control is completely up to you. Each of the edit controls will be attached to a database field by code that you write, instead of based on assumptions that the Class Wizard might make.

Neither variables nor message maps were created for the edit controls. The edit controls will be completely managed by the OBoundEdit class.

We added a button called Connect to the form. The user presses the Connect button to connect to the Oracle database. We used Class Wizard to create a button-clicked method for the connect button. That method is named OnConnect.

Binding

The form view class CEmpedtView controls the operation of the form view. We added a single OBinder instance as a member variable to the view class. Its name is m_empblock. (It is actually declared as OBinderEmp. OBinderEmp is a subclass of OBinder. See Error Handling below.) That OBinder instance manages the database connection, the dynaset, and all the bookkeeping needed for editing the database data. Next we need an OBound subclass instance for each of the user-interface widgets that we want bound to a database field. Since edit controls were used for the user interface, we used OBoundEdit instances. We added one member variable of type OBoundEdit to CEmpedtView for each edit control. (The member variable for empno is an instance of a subclass of OBoundEdit; see Using a Trigger below).

The edit controls are bound to database fields by calls to methods in the OBound class and subclasses. This binding is done after a database connection is established. For simplicity, we placed all the binding code in the OnConnect method.

The first thing that the OnConnect method does is to connect to the database. It does this by calling the login dialog class:

// get an ODatabase object via the connection dialog

logdlg connd;

// get a database object

ODatabase odb = connd.GetLogin(ODATABASE_PARTIAL_INSERT | ODATABASE_EDIT_NOWAIT);

if (!odb.IsOpen())

{ // didn't get a connection - user must have canceled

return;

}

The login dialog prompts the user (via a dialog) for database name, user name, and password and attempts to connect to the Oracle database. When it is successful it returns an open ODatabase object that refers to the Oracle database. Unsuccessful connection attempts put up an error dialog. If the user cancels the login dialog, an unopened ODatabase object is returned. So OnConnect checks the returned ODatabase to see whether it is opened. This amounts to a check whether the user canceled from the login dialog or connected to the Oracle database successfully.

The database is opened with partial insert and nowait options. The partial insert option ensures that any default field values set by the database are properly reflected in the dynaset when a record is added or edited. The nowait option ensures that if another user has a lock on a record that we wish to edit, we don't wait for that lock to be freed. Instead we get an error.

The OBoundEdit controls need to be attached two ways. First, they need to be attached to particular fields that will be available in a particular OBinder. Second, they need to be attached to a particular user interface widget. We accomplish the first attachment with a call to OBound::BindToBinder:

m_ename.BindToBinder(&m_empblock, "ename");

This call binds the m_ename member variable to the "ename" field available in OBinder. The ename field becomes available once the binder object (m_empblock) is opened. It is legal to bind a control to a binder before the binder's SQL statement has been executed.

We accomplish the second attachment with a call to BindToControl, which is a method available for the classes in the OMFC and OOWL libraries:

m_ename.BindToControl(this, IDC_ENAME);

This call binds the m_ename member variable to the user-interface widget identified by IDC_ENAME in the window identified by "this". Since OConnect is a method of the formview class, "this" points to the form window.

Once all the OBoundEdit controls are hooked up the OnConnect method, we can open the OBinder by executing an SQL statement:

m_empblock.Open(odb, "select * from emp order by empno");

This statement creates a dynaset, fetches records from the database, and puts the correct database field values in all of the edit controls.

Buttons

With the login dialog and the edit controls implemented, we can build and run the application. It works; it shows the values of the first record. We added more buttons to the form to implement additional functionality.

We implemented navigation by adding buttons named First, Prev, Next, and Last. We used Class Wizard to create a button-clicked method for each. The implementation for each is merely one line of code. For instance, to do Next:

m_empblock.MoveNext();

Calling OBinder::MoveNext moves the dynaset to the next record, fetches it from the database (if necessary), and updates the values of all the edit controls to display the new record. It actually does more than that. If any of the edit controls had been used to change the data in a record, OBinder::MoveNext saves that change to the database before moving to the next record. All this is accomplished without writing anything more than "MoveNext".

We added a few more buttons for record-level edits: Add New, Duplicate, and Delete. These were also very simple to implement, a single line of code for each. Again, the OBinder class takes care of necessary bookkeeping.

Using a Trigger

The empno field of the emp table is required to contain a unique number in each record. Rather than forcing the user to create the unique number manually every time a new record is added (with either Add New or Duplicate), we added code to set the unique number for the user.

Calculating a unique number is not difficult. One way is to ask the Oracle database the current maximum value of empno across all records. Adding one to that result guarantees a unique value, relative to the records currently in the database. (A better way is to use a sequence. Consult your Oracle documentation for more information about sequences.).

When should this calculation be made and what should you do with the result? The calculation needs to be made every time a new record is added. We could do the calculation every time the Add New or Duplicate button is pressed, and then change the value of the empno edit control manually. Then we would have to call OBound::Changed to make sure that the OBoundEdit instance knew that the value had changed, so that the OBinder would update the database properly. This approach would work, but it is prone to error. For example, what happens if we add some other method that also adds a record?

Instead, we changed the behavior of the empno edit control. Whenever a new record is added to an OBinder dynaset, every OBound instance bound to that OBinder calls its PreAdd method before the addition and the PostAdd method after the addition. The PostAdd method of OBoundEdit does nothing. We subclassed OBoundEdit, , creating a new class called OBoundEmpnoEdit, and defined a PostAdd method for it that overrides the default PostAdd. We declared the empno member variable as an instance of OBoundEmpnoEdit. OBoundEmpnoEdit is a subclass of OBoundEdit and is implemented in EMPEDVW.H and EMPEDVW.CPP.

At runtime, whenever a new record is added to the dynaset, the OBoundEmpnoEdit::PostAdd method is called after the record is added. That routine sets the value of itself, which sets the value of the edit control to the unique number that we have calculated.

Because the trigger methods are all virtual, the OBinder manages the OBoundEmpnoEdit as if it were a normal OBound. But we have overridden the default OBound behavior to suit the needs of the application.

Although in this particular sample OBoundEmpnoEdit doesn't need any information from the rest of the program, in a real working program the operation of the bound control would depend on whatever else is going on. The bound object needs a context. Just to show one way you can accomplish that, the sample code also implements a SetContext method that hands some useful context to the bound object. That context would then be available in its methods for use.

Error Handling

The sample application performs some minimum error handling. When an error occurs, it gives the user an error message.

The first kind of error that can occur is that the class library or some component upon which it depends cannot be loaded or initialized. This error is caught just before we try to log onto the database. We use these lines of code:

// get the default session

OSession defsess(0);

if (!defsess.IsOpen())

{ // couldn't get default session? Class library isn't working

AfxMessageBox("Can't start Oracle class library");

return;

}

If the class library can't be initialized, then the default OSession object won't be able to Open.

The next kind of error that can occur is that the OBinder object can't be opened. We use this code:

// check for error

if (ores != OSUCCESS)

{ // we couldn't open the dynaset

const char *msg;

if (defsess.ServerErrorNumber() != 0)

{ // we have a server error - tell the user that

msg = defsess.GetServerErrorText();

}

else

{ // no server error - class library isn't working correctly

msg = "Class library error when opening dynaset.";

}

// give the user a message

AfxMessageBox(msg);

}

where ores is the result of the OBinder::Open call. The most likely errors here are that the SQL statement has an error (the table doesn't exist or one of the field names is bad) or that the user doesn't have permission to read the table. These are both Oracle errors.

Either of these two cases would prevent the application from working at all. These errors are generally bugs in the client program that can be avoided with correct coding (getting the SQL statement right, for instance). Once we've opened the OBinder , we will look at records in the table. Then we have to start considering other types of errors-interactions with other users or lack of system resources.

The most common error occurs when we attempt to edit the value of a field. When we try to change the value of the field, the OBinder instance attempts a StartEdit on the dynaset. This can fail for a variety of reasons. Most common are: connection to the server is lost, another user has a lock on the row, or the data in the row has been changed by another user. The first two are reported as Oracle errors. The last is an error specifically generated by the class library to ensure that database data is not accidentally overwritten.

The difficulty here is that, with OBoundEdit controls, the data is changed by typing a key into the edit control. Our program isn't making any method calls whatsoever. How do we get control to report the error?

The OBinder class has a method called OnChangedError that can be overridden. It is called when an error occurs while processing the StartEdit on the dynaset. The example uses this to inform the user of an error when a record is starting to be edited. The OBinder class is subclassed to OBinderEmp, and the member variable is declared to be of that type. Then when an error occurs as a user types a value into a record, the OBinderEmp::OnChangedError routine is called.

Finally, errors can occur during the various operations represented by the buttons on the form: navigating to various records or adding and deleting records. Errors during these operations are all sent to the view HandleError method. HandleError takes care of errors that occur when navigating, when adding or deleting records, or when saving a changed record to the database. Any of the navigation methods, or any method that adds records, saves any changes to the current record before doing its work (this is actually done through PreOperation trigger functions).

HandleError reports the error to the user by fetching (or calculating) the correct error message.