Errors & Omissions: In last week's article, I left out some major methods for WQuery and the example code was subsequently wrong. The following are the major navigational methods for WQuery:

• MoveFirst - moves to the first row in the returned data. This should be the first method called after Open. This will return FALSE if there are no rows in the result set.
• MoveNext - moves to the next row in the result set. Again, this returns FALSE if there are no more rows.
• MovePrior - same as MoveNext, but goes backwards.

With these new additions, last week's sample code should read:

// CUSTOMER_TABLE structure:
// NAME CHAR(20),
// AGE INTEGER
query_1->SetSQL( "SELECT * FROM CUSTOMER_TABLE" );
WBool ok = query_1->Open();
if (!ok) {
    Message( "Error opening query " + query_1->GetSQL() );
}
else {
    ok = query_1->MoveFirst();
    if (!ok) Message( "Error: no records returned" );
    else {
        WDataValue wdv = query_1->GetValue( 1 );
        WString value1 = wdv.GetCHAR();
        WDataValue wdv = query_1->GetValue( 2 );
        WString value1 = wdv.GetUSHORT();
        // do some work on value1 and value2
        wdv.SetCHAR( value1 );
        query_1->SetValue( 1, wdv );
        wdv.SetUSHORT( value2 );
        query_1->SetValue( 2, wdv );
        query_1->Close();
    }
}

Now, on to this week's stuff. When I write database applications using Power++, I always write wrapper classes for the database. This allows for much easier access to the tables. Basically, I create a class for each table in the database. For this example, I will use the following simple table:

TABLE: client
    KeyIndex    int
    Name        char(30)
    Age         int

Each field will become a property for its respective class. I usually just use WString as the type for all fields regardless of the actual database type for ease of handling and just convert it when I need to. Each table class has the following methods:

• Read - read in a record. This method takes a key value to look for (or some kind of search criteria for non-keyed tables) and loads the data into the properties.
• Write - inserts a record using the properties as data.
• Update - updates a record. You can choose not to implement this method to make the table 'read only'.

Let's look at my implementation of a class for the client table described above:

class client_table {
   public:
      WString keyindex, name, age;  // you can make these private and
                                    // use GetName, etc.
      WBool Read( WQuery *query, WString keyvalue );
      WBool Write( WQuery *query );
      WBool Update( WQuery *query );
     client_table();
   private:
      WBool data_loaded;
}

client_table::client_table() {
   data_loaded = FALSE;
}

WBool client_table::Read( WQuery *query, WString keyvalue ) {
   // reads in a record
   query->SetSQL("SELECT * FROM CLIENT WHERE KEYINDEX = "+keyvalue);
   WBool ok = query->Open();
   if (!ok) {
      data_loaded = FALSE;
      return FALSE;
   }
   ok = query->MoveFirst();
   if (!ok) {
      data_loaded = FALSE
      return FALSE;
   }
   WDataValue wdv = query->GetValue( 1 );  // remember, start at 1 not 0
   name = wdv.GetCHAR();
   wdv = query->GetValue( 2 );
   age = wdv.GetCHAR();
   data_loaded = TRUE;
   query->Close();
   return TRUE;
}

WBool client_table::Write( WQuery *query ) {
   // inserts a records
   WString sql = "INSERT INTO CLIENT VALUES ('";
   sql += name + "', ";    // pay special attention to the sql single quoting
   sql += age + ");        // non-numeric fields are quotes
   sql->SetSQL( sql );
   WBool ok = sql->Execute();
   if (!ok) return FALSE;
   else return TRUE;
}

WBool client_table::Update( WQuery *query ) {
   // update the data for a record
   if (!data_loaded) return FALSE;
   query->SetSQL("SELECT * FROM CLIENT WHERE KEYINDEX = "+keyindex);
   WBool ok = query->Open();
   if (!ok) return FALSE;
   WDataValue wdv;
   wdv.SetCHAR( name );
   query->SetValue( 1, wdv );
   wdv.SetUSHORT( atoi(age) );
   query->SetValue( 2, wdv );
   query->Update();
   query->Close();
}

Note: the WQuery passed as a parameter to the methods should be closed and have its bind policy set to 'all' (see last week's article).

Now, all you (or other users of these classes) need to do to access the database is use the class methods and properties (this example updates a record or inserts one if it doesn't exist):

client_table *client;
WString recordnum = GetSomeUserInput("Record #");
WString newname = GetSomeUserInput("Name");
WBool ok = client->Read( query_1, recordnum );
if (ok) {
   client->name = newname;
   ok = client->Update( query_1 );
}
else {
   client->keyindex = recordnum;
   client->name = newname;
   ok = client->Write( query_1 );
}

That's about it for this week. Next week I will discuss some tips and other stuff dealing with database programming.

If you have any questions or suggestions for topics, feel free to e-mail them directly to me at dosten(at)earthlink.net.

Last week I covered some database programming basics and the WTransaction object. This week I will cover the WQuery object.

The WQuery object is the workhorse for accessing databases in Power++. WQueries contain the SQL statements you will use to access the database and also hold the data for reading and writing. Basically, WQueries work by setting the SQL property and calling the Open method. If the query was a SELECT statement, then you can retrieve the data using the GetValue method and modify the data using SetValue. Note that you can set the SQL property at design-time and also can set the query to open automatically is you wish. The following are the main properties for WQuery:

• BindPolicy - This describe how columns are bound to usable objects (e.g. arrays). I always use 'WQBPAll' and never found a use for the other settings.
• RowSetSize - A RowSet is how many columns are actually fetched from the database. For example, if you have a RowSetSize of 10 and then access 11'th record, the query will go back to the database a fetch records 11 through 20. This property may effect performance, so you might try different settings.
• CursorType - I have never found a use for anything but WQCTDynamic. In fact, some DBMS may not support all types.
• SQL - This is the actual SQL statement that will be utilised on a Open or Execute.
• Transaction - The WTransaction object that provides the link to the database (see last week's article).

Most of the properties on the 'Options' tab of the WQuery property sheet are kind of esoteric and I just leave them alone (except for AutoOpen). You might, however, gain some performance or have some specific DBMS reasons to change these settings.

The main methods for WQuery are:

• Open - Open and run the query.
• Execute - Run the query. The query must not return any rows, so this only works for non-SELECT statements.

Now, how to put all this to use... If you need to make a simple user interface to a database (where the user can view, edit, insert, and delete), Power++ has a pretty decent wizard that will set up an entire form for you. Just add a new form, and select the Database Dialog. But for transaction type applications, you need to do the work yourself.

For a bound control (such as an EditBox) it is easy to get the data, just set the DataSource and DataColumns properties on the Database tab for your controls (the wizard can do this automatically for you). But to get the data without external controls, you use GetValue and SetValue. GetValue and SetValue work with WDataValue objects which are kind of like a void data type in that they hold an database data type. WDataValue has several properties that will return the data in different formats (e.g. GetCHAR, GetUSHORT, etc.). Note that GetValue and SetValue take an column index as a parameter and this index starts at 1, not 0.

The following example gives a quick example of using a WQuery and WDataValue at run-time (this code would follow the example from last week that opened the transaction object):

// CUSTOMER_TABLE structure:
// NAME CHAR(20),
// AGE INTEGER
query_1->SetSQL( "SELECT * FROM CUSTOMER_TABLE" );
WBool ok = query_1->Open();
if (!ok) {
    Message( "Error opening query " + query_1->GetSQL() );
}
else {
    ok = query_1->MoveFirst();
    if (!ok) Message( "Error: no records returned" );
    else {
        WDataValue wdv = query_1->GetValue( 1 );
        WString value1 = wdv.GetCHAR();
        WDataValue wdv = query_1->GetValue( 2 );
        WString value1 = wdv.GetUSHORT();
        // do some work on value1 and value2
        wdv.SetCHAR( value1 );
        query_1->SetValue( 1, wdv );
        wdv.SetUSHORT( value2 );
        query_1->SetValue( 2, wdv );
        query_1->Close();
    }
}

Note that the above is modified from the original code posted for this article... See article number 3 (the next one) in the series for more info.

The WQuery object is very complex and there are many ways to perform most tasks. If anyone has some good tips of their own, email me and I will put them on a future article. Next week I will discuss the way I like to handle database access by designing a wrapper class for each table in the database.

That's about it for this week. If you have any questions or suggestions for topics, feel free to e-mail them directly to me at dosten(at)earthlink.net.

I write a lot of database applications accessing everything from simple text files to Paradox tables to MS SQL Server databases. I have spent a fair amount of time playing with the various techniques for database programming in Power++. This is the first in a series of articles on this topic. Keep in mind that I will assume that readers understand basic database design and terminology.

To start, we will discuss some basics on databases and how they appear to Power++. Databases can be in any form from comma-delimited text files to industrial mainframe databases. There are many ways to gain access to databases, such as ODBC, embedded SQL, or Borland Database Engine. Power++ provides the following database tools: DataWindows, ODBC, and native drivers for various DBMS packages. I find that ODBC is the easiest and most straightforward so I will concentrate on ODBC access for this series.

ODBC is basically a 'middleware' that provides a common interface for accessing databases. To use them, an 'ODBC Datasource' must be set up on the machine that runs your applications. This datasource defines the types of data being accessed (such as MS SQL Server) and, depending on the type, some additional information (for MS SQL Server datasources, you must define the server and the database name). One huge advantage of ODBC is the ability to write your program to be compatible with any type of datasource (which I will detail later). One big drawback I find with ODBC is that you must install the correct DBMS client drivers and setup the ODBC datasource on every machine that runs the application (most people don't find this a problem, but I don't like having to modify users' machines too much). Once you have the ODBC datasource setup you're ready to program.

Power++ provides two objects that are key to database programming: WTransaction and WQuery. The Transaction object provides a link to the datasource (and therefore the database itself). It is through this link that your queries are executed through the Query object. Keep in mind that all database operations are done via queries. Your database programming will actually be a bunch of SQL statements. For instance, you will retrieve data using a SELECT statement, and modify using UPDATE, INSERT, and DELETE.

To repeat the WTransaction object provides a link to the datasources you want to access. Its main properties are:

• DataSource - the name of the ODBC driver (or database name for native drivers)
• LoginID - the user id used to login to the database
• Password - the user password to login to the database
• AccessMode - specifies read only or read-write access
• LoginPrompt - specifies if the user should be prompted if the login information is incomplete or incorrect (should be set to WTLPIfRequired)
• Automatically Connect (design time only) - if set, then the transaction will attempt to connect to the database when the program is launched; this can be bad if you want the user to enter an id and password so you should uncheck this.

The main methods are:

• Connect - connect to the datasource. Returns TRUE is successful
• Disconnect - disconnect from the database.

There are some other properties and methods, but the above are the major ones.

The datasource property can be changed during run time. Why is this helpful? Say you do not know what the name of the database is that your application is going to access or want to access a number of databases. As long as they have the same structure, then you can just specify the datasource name using command-line arguments or user input and connect at run-time. Since the structures are the same, the subsequent SQL used by the WQuery's will work fine.

The following example gives an overview of WTransaction (assume GetSomeUserInput(WString) is a function that prompts the user and returns some specified data):

transaction_1->SetDataSource( GetSomeUserInput("Database") );
transaction_1->SetUserID( GetSomeUserInput("User ID") );
transaction_1->SetPassword( GetSomeUserInput("Password") );
WBool ok = transaction_1->Connect();
if (!ok) {
   Message( "Error connecting to database " + transaction_1->GetDataSource() );
}
else {
   // do some work
   transaction_1->Disconnect();
}

Remember, you can set all these properties at design-time and have the Transaction object automatically connect to the database if you need to.

That's about it for this week. Next week I will discuss the WQuery object. If you have any questions or suggestions for topics, feel free to e-mail them directly to me, Darren Osten, at dosten(at)earthlink.net.

See you next week...

Just a quick note before we get to the article. Darren Osten has been kind enough to fill in for Jon this week. Thanks Darren! A brief bio: I have been raised with computers as my father worked for IBM since I was 2. My first computer was a PCjr which my dad heavily modified. I graduated from UCLA with a BS in Computer Science (which at the time is what I thought of it - when I was there there were no C++ courses). My day job is designing and programming insurance marketing software for a startup called ICA Technologies (www.ica-newco.com). This is mostly done in C++ using Power++. I also do some consulting and programming on side with REXX and Delphi.

If you are like me and learned to program for DOS and UNIX, then you frequently used command line arguments.  I still use command line arguments heavily since many of the programs I write are invoked automatically (via chron for instance); they are also good for setting program defaults without hassling with ini files or the registry.  This week I will show you how to used command line arguments in Power++ (without using argc and argv) and the amazingly useful method WString::Parse.

Recently, Jon discussed the WAppObject, which is a macro to the application's own WApplication object.  One of the member functions is GetCommandLineLine() which returns the string that was used to start the application (e.g. "notepad readme.txt").  To use it:

WString cline;
cline = WAppObject.GetCommandLine();

Now, what to do with this new string.  I can't remember the number of times I have written a simple parser function to break up strings.  However, Power++ provides a built-in WString::Parse.  Parse breaks up a string into a WStringArray.  Its behaviour is completely customisable.  I usually specify all the arguments to Parse, but the defaults are usually sufficient.  So to parse the string:

WChar delimiters[2];
delimiters[0] = ' ';
delimiters[1] = '\n';
WStringArray clineargs;
clineargs.SetCount( MAX_NUMBER_OF_ARGS );
clineargs = cline.Parse( delimiters,         // specifies that the string will be broken
                                             // on spaces and newlines (the default is any
                                             // whitespace char
                         TRUE,               // treats multiple delimiters as one
                                             // not as a null string
                         WSTRING_DEFAULT_QUOTELIST  // this defines the characters to define
                                             // strings so "myapp.exe -A 'Hello World'" has
                                             // 2 parts not 3; this macro defines single
                                             // and double quotes
                         TRUE,               // strip out quote delimiters, so 'Hello
                                             // World' is stored w/o the quotes
                         0,                  // start index w/in source string
                         USE_STR_LEN,        // end index (this macro is the string length)
                         FALSE )             // disallow escape characters.

Now that you have the command line separated, it is a simple matter to check the results and set the program options:

// start looping at 1 because 0 contains the application itself
for (WInt i=1; i < clineargs.GetCount(); i++ ) {
   if (clineargs[i][0] == '-' || clineargs[i][0] == '/') {
      // handle switches
   }
   else {
      // handle arguments
   }
}

Like I mentioned, I use command line arguments a lot, but I use Parse even more.  It is great for handling user input and text files.

That's about it for this week. If you have any questions or suggestions for topics, feel free to e-mail them directly to me at dosten(at)earthlink.net.

See you next week...