Now that it's becoming a popular tool to connect to 3000 databases,
ODBC experience can help save you time and trouble

By Joe Geiser
CSI Business Solutions

Now that HP has released ODBCLink/SE to everyone with support for MPE/iX 5.5 Express 3, the questions are coming fast and furious about this ODBC driver, as well as the commercial drivers.

Since we’ve been working with all of the driver products out there, we’ve been compiling lists and statistics on these products. Part of the collection are what we call “the gotchas” — those items which tend to make you tear your hair out.

There are a number of issues surrounding the collection of drivers available today: all are not immune to problems. Most problems fall into two categories: perception and education.

Because TurboIMAGE is not a relational database management system, SQL is not its native language. The DB calls we’ve grown to use and the intrinsic library which support them are the native language. HP has placed an SQL layer on TurboIMAGE, via Allbase, so that access to the data contained in these databases can be accessed via SQL.

With the advent of client-server applications and Web development, there came a need to open the interface up even more. HP first developed PCAPI, then replaced it with ODBCLink/SE. In addition to this product, there are the commercial products, some of which have been out since 1994.

Linkway from CSL and ODBCLink from M. B. Foster are two of the most notable drivers which have been available for some time. ODBC32 from MiniSoft has been available for some time now as well. All of these drivers have their good and bad points. Most notable, however, is that these drivers are not interchangable.

A bold statement to make, and this leads to our first “gotcha.”

Gotcha # 1: Any driver can do the job

Just slap it in and go. Well, nothing can be further from the truth.

Yes, they may be “interchangable” in terms of SQL support; however, performance is a completely different story. Drivers such as Linkway and ODBCLink primarily utilize the IMAGE/SQL interface to the TurboIMAGE database. ODBC32 and ODBCLink/with DataExpress Option have the ability to access TurboIMAGE databases directly, without using the Allbase interface.

The perception is that direct access is faster and better than access via IMAGE/SQL. Direct access may be advantageous, and again, it may not. A lot depends on the access itself, the datasets being accessed, how they are being accessed, how they are being joined, and which operations are being performed on them.

For example, a simple hashread (DBGET, Mode 7) against a Master dataset: All drivers will return this row, if the driver is configured correctly, within milliseconds of each other. The same holds true for short chain reads (DBGET, Mode 5) on a Detail dataset.

Where they start to differ are in areas of either unconventional accesses or when “wildcard” access are being utilized.

For example, using the LIKE predicate in a SELECT clause to locate a match is performed consistently faster under IMAGE/SQL because of the Allbase optimizer and B-Trees. Direct access is dependent on B-trees only, and the optimizers incorporated into these drivers do not necessarily produce the most efficient access paths.

On the other hand, processor utilization is lower in quite a few cases where a direct interface is employed. This is because the layer of IMAGE/SQL and Allbase/Turbo-Connect are not present.

So which driver should you employ in your project? Pilot projects and small production projects may get away nicely with using ODBCLink/SE. It’s free, but also requires the use of IMAGE/SQL and the obligatory attachment of databases. Larger projects, however, should be using a commercial driver. The reasons are simple — ODBCLink/SE is the slowest of all of the available drivers in most cases and consumes more connections to the HP 3000 than its commercial counterparts, all of which contribute to response time issues and performance problems.

Gotcha #2: Locking Strategies

With DBLOCK, we have a variety of ways of locking data for write, update or delete. We can lock at the database, dataset or data item levels, we can lock conditionally or unconditionally. Fred White, Jon Bale and their IMAGE team were thinking of how to keep this database available concurrently while other users needed data. We were spoiled rotten, thanks to these two gentlemen. And we do appreciate their efforts.

Now you’re using ODBC, and guess what? You’re stuck with two types of locks, and guess at what level they lock? The dataset. Note that this does not apply to direct-to-TurboIMAGE drivers, only those utilizing IMAGE/SQL.

Out of the four “Isolation Levels” mentioned in the Allbase and IMAGE/SQL manuals for locking, IMAGE/SQL recognizes two of them. These are “RR” (Repeatable Read) and “RU” (Read Uncommitted).

These isolation levels correspond well to a relational database, but to TurboIMAGE, improper use can be catastrophic for application and system performance.

The rule of thumb to follow is this: If you need the dataset exclusively, and don’t mind locking everyone else out, then use Repeatable Read. If concurrency is what you need (and who doesn’t), then use Read Uncommitted.

For those who are not familiar with SQL Isolation Levels, Repeatable Read is basically a “pessimistic” locking mechanism. It will place a lock on the data after it has been read and queue all other processes accessing the database behind the locking process until the transaction is committed. Read Uncommitted is “optimistic” locking, which basically places a lock around the update, insert or delete. It exists only for the time of the update.

Gotcha #3: Unsigned Numerics

Do you have fields in your datasets defined as Zoned or Packed, and defined in COBOL programs as Unsigned?

In other words, is there something like this lurking in one of your programs:

01 FIELD-A PIC 9(4)

If so, you need to know how to get around this little gem.

These fields are commonly used for counters and other such data which does not require a field. A recent project of ours used this type of field for a “serial number.” Well, with SQL, every numeric has a sign, whether you want one there or not.

The problem is simple: Update FIELD-A, or insert a row with FIELD-A filled in, and it will be signed. When the COBOL program reads the value, depending on how the system’s COBRUNTIME variable is set, the program will abort or will show an ILLEGAL ASCII DIGIT warning.

How can this be? Well, when IMAGE was written, the idea of “datatypes” was foreign. IMAGE, and TurboIMAGE, do not really enforce datatypes. Your program enforces data types, QUERY enforces datatypes, but TurboIMAGE does not. It is entirely possible to place “ABCDEF” into a field that is defined in the database’s schema as Z6! Just define the item in a COBOL program as PIC X(6), and DBPUT away.

When a field is defined as Z6 in the schema and root file, and a COBOL program has a definition of PIC 9(6), a value of “123456” is shown in the database, in Hexadecimal, as “31 32 33 34 35 36”. This is the expected behavior, right? Change the PICture clause in the COBOL program to PIC S9(6), and this same value is now “12345F”. The “F” is the “sign overpunch” that we’ve all grown to know and love.

Going back to our PIC 9(6) field, if we use an INSERT or UPDATE statement from any SQL source (not just ODBC) to update this field, the numeric will be signed. IMAGE/SQL makes no distinction between signed and unsigned for two reasons. First, Zoned or Packed can be signed or unsigned and SQL cannot tell the difference, except by possibly reading the data in advance. Secondly, the ANSI standard for SQL for numerics is signed, and Allbase/SQL, which lies behind IMAGE/SQL, follows these standards rather closely in this regard.

So how do we get around this when dealing with an ODBC driver? The solution is called “casting” — a way of changing the datatype of the data being written and overriding what SQL thinks the datatype might actually be.

In our case here, we need to “cast” the value as a “string” (or in COBOL-speak, a PIC X(6)). Let’s take our serial number as an example. This field is a Zoned decimal field of 10 positions. It’s defined in COBOL programs as PIC 9(10). It contains no sign and it’s a counter, which is read from the dataset. The number read is incremented by one, then updated.

This field is treated in a Visual Basic program as a long integer. Arithmetic is performed on this variable, but the Update is treated as a string (see Figure 1 below).

Figure 1: Visual Basic Code Fragment to update the Serial Number

LwSerialNo& = rsCOUNTER!serial_number
LwSerialNo& = LwSerialNo& + 1
With rsCOUNTER
.Edit
!serial_number = CStr(Format(LwSerialNo&, “0000000000”)) .Update
If Err > 0 then
WsTrans.Rollback
Exit Function
Endif
End With
Set rsCounter = Nothing


Note that we read the value from the database (a recordset in Visual Basic, which can also be referred to as a “cursor”). We then add one to this value and attempt an update.

During the update sequence, note the use of “CStr” — this is a Visual Basic version of a Cast. We are converting the value to a String, from Long Integer. This strips off the sign, as the value is positive. The use of Format in the same statement will ensure that the value is zero filled prior to conversion.

The final result is an unsigned zoned decimal number instead of a signed zoned decimal number.

The rest of the code (If Err > 0 Then ...) just shows that if the update is not successful, we roll everything back as if nothing had happened (which is the beauty of SQL, by the way).

Gotcha #4: Dates

Now that you know about unsigned numerics, you’re ready for the next “gotcha” covering dates.

There are two issues here. The first is that there is no “date” datatype in TurboIMAGE or IMAGE/SQL. The second is simple — if dates are stored in a Zoned decimal field, then re-read “Gotcha #3” before continuing, because more than likely, they are stored as unsigned numerics, right?

How do we use the date functions within our PC-based languages, such as Visual Studio and other Interactive Development Environments (IDEs)? These IDEs require dates to be in a “Date Format” or datatype.

If data is read from an SQL table and the value has a datatype of DATE or DATETIME, then you’re covered. This datatype exists in Allbase and other SQL databases, but alas, does not exist in IMAGE/SQL.

These IDEs have functions: permitting the programmer to find the difference between two dates, to add a number of days, months, and years to come up with a new date, to handle leap years automatically. These functions are real time- and code-savers.

So how do you get these CCYYMMDD and YYMMDD dates into a format so they can be used in these functions? Again, the use of a cast (changing the datatype) is used, but the function will probably require the format of the date be in a different format from which it is stored (See Figure 2 below).

Figure 2: Casting a CCYYMMDD date field in Visual Basic

SdateField$ = rsDATEREC!ship_date
YY$ = Mid(SdateField$, 1, 4)
MM$ = Mid(SdateField$, 5, 2)
DD$ = Mid(SdateField$, 7, 2)
SnewDate = Cdate(MM$ & “/” & DD$ & “/” & YY$)


In this example, we read a date field from an IMAGE/SQL recordset in Visual Basic. This value is a CCYYMMDD date. We then use the string manipulation function “Mid” to extract the components into individual variables. After this is complete, concatenate the date into a standard date format of Month, Day and Year, separated with slashed or hyphens.

Once this concatenated string is passed through the “Cdate” cast, the value is converted to a “date” format, and may be used in all date functions within Visual Basic (in this case).

To get a date back into string format (for update or insert, for example), use the Format and Now functions. For example, to assemble the current date:

YYYY$ = Format(Year(Now()), “0000”)
MM$ = Format(Month(Now()), “00”)
DD$ = Format(Day(Now()), “00”)
SimageDate$ = YYYY$ & MM$ & DD$

The value of SimageDate$ (as of today, that is), is “19980910”. The Year function will return the year of the date field. The Month and Day functions return the month and day respectively. Use of the Now() function returns the current date, but any date-format field can be used. For example, the date field SnewDate which we used in our previous figure can be substituted for Now() as follows:

YYYY$ = Format(Year(SnewDate), “0000”)
MM$ = Format(Month(SnewDate), “00”)
DD$ = Format(Day(SnewDate), “00”)
SimageDate$ = YYYY$ & MM$ & DD$

This will work if the datatype in the dataset is type “X”. Again, if the datatype is Zoned decimal, you have a little more work to do — add the CStr cast to the above, to convert the number to a string.

A question that commonly comes up is: “Can TurboIMAGE store an SQL Date?” Yes, it can. The problem with doing this is that the value cannot be used with any other code except SQL. Even QUERY will have a problem with the field.

If you really want to do this, here’s how:

1. Define a field in a dataset as a K8.

2. Using IMAGESQL.PUB.SYS, override the type with an UPDATE TYPE command, and change the datatype to “DATETIME”

3. Use INSERT or UPDATE to place a Date-Format value in the field.

We demonstrated this on an HP-TV broadcast on Cure2000 when showing how Adager handles Y2K issues. We also showed how QUERY absolutely could not handle the field, but it will yield a DATETIME datatype each and every time from SQL.

Gotcha #5: Unique Keys

This gets off the bits and bytes and onto a topic which infuriates quite a few people —the requirement of unique keys in a table. Why should this be a necessity anyway?

Well, ODBC in most cases is handled by Microsoft Jet, which is the engine which drives Microsoft Access. If you’ve ever tried to use Microsoft Access Linked Tables and you tried to link in Detail Datasets, then you have been prompted for a unique key.

Master datasets already have a unique key and will be reported as such. Detail datasets are never guaranteed to have a unique key, and are reported as such.

Microsoft Access will prompt users to specify one or more fields which comprise a unique key for all detail datasets. The reason is simple, really — so that there is a unique identifier for a row in the event an Update or Delete query is performed on the dataset. Without a unique identifier, it is possible to delete or update rows that were never intended to be deleted or updated.

As stated earlier, Microsoft Jet requires this, which means that Microsoft Access also requires this. There is however, another mode of ODBC called “ODBCDirect” which was introduced with ODBC 3.0. The passthrough mode of ODBCDirect, which most of the drivers for the HP3000 support, does not require the unique key. This means that linked tables cannot be used with Microsoft Access, but can be directly accessed from within the application.

ODBCDirect should be used whenever possible, especially if Remote Data Objects (RDO) and Active Data Objects (ADO) are being contemplated for use. (See the sidebar on how to find out more about these objects.)

In Visual Basic (Visual C++ and Visual Interdev are similar), when using the OpenDatabase method, specify “vbUseODBC” constant instead of “vbUseJet” constant, to utilize ODBCDirect. Again, note that not all drivers can use this mode of access — but that will change shortly. If the driver is fully, ODBC 3.0 compliant, then it should work with ODBCDirect.

If you’re using Visual Basic 4.0 or earlier, ODBCDirect is not supported. It is supported only on Visual Studio 5.0, and now on Visual Studio 6.0. (The suite of languages of Visual Basic, C++, InterDev, FoxPro and J++ are now known as Visual Studio).

Joe Geiser is principal partner in CSI Business Solutions, an integrator and supplier of HP 3000-related client-server and Internet software and services.