SQLite: The Fly in the Dummy Table Technique Ointment

Owned by StevenP
Last updated: Oct 21, 2013 by David HarmsVersion comment

by Unknown user

Last time I figured out how to make The Dummy Table Technique work. Using this technique is a bit different than when using it with full blown SQL (i.e., the kind with a dedicated engine). Not only do I need to declare the dummy table in my dictionary but I have to ensure that the SQLite table physically exists. 

So, then, what happens if my initial dictionary specification for my Dummy file:

Figure 1: Original DCT spec for DUMMY

proves to be inadequate. What if I find I need a second LONG?

Obviously, I add a second LONG to the dictionary:

Figure 2: New DCT spec for DUMMY

What if I later discover I need a few DECIMALs or two more STRINGs?

What happens when I change the dictionary and remake the app and open it? ErrorCode() 47 of course. Except that SQLite is not quite friendly enough to tell me that in the way I'm used to:

Figure 3: ErrorCode() 47 as interpreted by SQLite

Further, suppose I can't wait for the expected SQLite support from Capesoft's FM3?

“Bother” (in the delightfully appropriate words of Pooh Bear).

The Hard Way

Of course, the easiest way to avoid 47'ing is to declare enough fields of each data type in DUMMY to handle any query I might format. But to do so without making the declaration cumbersome.

Truth? That's hard. It means checking every filter, every totaling op, everything to determine the number of variables and data types and number of variables per data type that I need. Then I have to accurately guess what I may be asked for in the future.

I could “just” declare a bunch of CStrings and let Clarion convert data for me. Of course, any numeric is quite likely to need to be FORMATted so that leading/trailing zeros are where they're expected to be.

Or I could guess at how many variables, and what types, I might need. And then pray for the best. But, here in the trenches, few believe in the efficacy of prayer.

The Easy Solution

Figure 3: Check DUMMY with standard ABC code

Try to open the file. If there is a LEVEL other than BENIGN, remove the file. Close it (probably not really necessary as it didn't really get OPENed) and let the FileManager or the RelationManager re-create it on the standard template OPEN.

The only issue I had with this is that it didn't work. Both

Access:DUMMY.Open

and

Relate:DUMMY.Open

returned the error window shown in Figure 1.

Why? Because the OPEN methods have already failed on the changed file spec.

Next?

For those of us who've been around a while, that is who have greater tenure than the ABC classes, there is a well know way to get the ErrorCode and, not just get the ErrorCode, but get control of it.

That is to use a direct Clarion statements:

Figure 4: Check DUMMY with standard Clarion code

This works fine. The final CLOSE is not actually necessary, if there was an error, the file wasn't OPENed, that's why there was an error. But, if there wasn't an error, the CLOSE is necessary. It probably should be wrapped in some conditional code.

The standard template-generated opening of the file will create it. So, everything is copacetic.

But, wait!

SQLite expects DUMMY to exist, physically. That's what got me into this whole mess in the first place.

But I never actually use the file. There is never any i/o. All The Dummy Table Technique needs is the file BUFFER. Since the physical file is never actually used, why not just:

Figure 5: Why not just …?

always REMOVE it before use and never worry about the specification?

I must admit that I don't see a reason not to always create DUMMY before using it. It works (though I haven't tested in a multi-user environment but I can't see that being an issue).

Summary

“Proper Prior Planning,” they say “Prevents Piss Poor Performance.” I was so delighted to get SQL queries working with SQLite that I simply failed to consider the consequences of needing to change DUMMY's layout.

In full blown SQL engines, DUMMY is never CREATEd and, thus, never exists. ErrorCode() 47 is impossible. SQLite is not full blown SQL, as previously discussed, so a slight accommodation is not out of order.