Using SQLite in Visual Objects

We discovered that we could successfully migrate from DBF to SQLite by employing the SQL classes in Visual Object 2.8. 

Our objective was to incorporate the the C/C++ dll offered by the SQLite Project but we weren't able to make it work.  So we turned instead to a widely-used SQLite ODBC Driver

Many of the VO SQL methods worked beautifully, requiring few code changes.  Others didn't work at all.  Others seemed to work sporadically, so we ignored them and either used SQL statements or fashioned our own work-arounds. 

In our opinion, SQLite is the DBMS of choice if you're converting a legacy DBF system.  It's lightweight, free but mainstream, serverless and well-supported.

(And it would be just about perfect if we could ever incorporate a SQLite wrapper or DLL in our Visual Objects applications.  But that's a discussion for another time in another forum. )

What follows is a set of instructions specifically for Visual Objects users but since VO is related to (and some would say born from ) C/C++, users of these and other languages can follow along and learn something new.   These instructions assume you've already converted your DBFs and have installed SQLite with the necessary drivers and support apps.

Download the Visual Objects AEF.

Indexing

Create an indexing scheme identical to that of the DBF file system. Here's something you'll like: in SQL there's no need to SetOrder(); the system figures it out based on the indexes.

Null Fields

In the DBF world, empty character fields are stored as just that: Empty(). In SQL we've they're always stored as NULL (uninitialized), even when you try to save an Empty() variable. Which of course crashes when an empty field is accessed by FIELDGET(). So we added a NoNilGet() method to SQLSelect{}.

Date Fields

Say goodbye. Don't even try to use the SQLite DATE variable type. Simply convert all your date fields to YYYYMMDD strings and store/retrieve them that way. (Unless of course you plan to fully embrace one of the SQL date types.)  We added a GetDateField() method to SQLSelect{} that takes the YYYYMMDD field and makes it a Date.

bBrowser

Switching from ...

oBrowser:ServerType := #DBase

to

oBrowser:ServerType := #SQL

... doesn't work.   Create a bArrayServer instead.

Some Functions you'll need to forget

GoTo(), RecCount(), RecNo(), LastRec(), OrderKeyNo() and the like

Record Numbers

At some point the database gods decreed 'verboten' the whole idea of record and order numbers. There's a _rowid_ column in SQLite that seems to store this variable but its use is, as with other quirks mentioned here, unreliable. And therefore unusable. (Besides, you should maintain code that works in all flavors of SQL. So stick to the common standards.) Unfortunately we need something more specific than, for example, an Account and Date combination when there could be multiple instances of the same Account and Date.

So we got around this by using something we already had instituted: a unique ID (for example, a guid) for each record appended to the table. Then store it and use the Seek() method to retrieve it. Works well but it might mean hours of coding changes. We've included in the examples a function for appending a table and applying the unique ID.

Counts

If you're trying to set up a progress bar, you can't use OrderKeyNo() arithmetic to determine the range. So try something like this.

cStatement := "SELECT count(*) AS XCOUNT FROM (tablename) WHERE ITEMDATE>='" + DToS( dDate ) + "'"

oSelect := SQLSelect( cStatement, GLOoCONNECTION }

nRange := oSelect:FIELDGET(#XCOUNT)

This is the basic method for substituting, RecCount(), LastRec(), OrderKeyCount() and the like.

SetOrder()

You don't need to set an order to Seek(). Nonetheless, you should acquire the habit of using the SQL "ORDER BY" clause everywhere else.

SEEKing a record and then looping from that point on the basis of SetOrder() is (as we say elsewhere in this missive) unreliable and therefore unusable. Part of the problem is that SQLite seems to have a mind of it's own when using indexes. (i.e. it decides which to use; not you.)

Instead of . . .

oTable:SetOrder( "DATE" ) // SetOrder() and Where() do not seem to coexist
oTable:Where("account='100'")
oTable:Seek( #itemdate, DToS( dDate ), FALSE )

. . . Use

oSelect := SQLSelect{"SELECT * FROM items WHERE account='100' ORDER BY itemdate", GLOoCONNECTION}

When in doubt, Use the standard SQL syntax. It'll save you time later on when you're switching to another SQL platform.

Some Methods you'll need to fix

Eof

The following line:

WHILE oSelect:FIELDGET(#ITEMDATE) = dDate .AND. !oSelect:Eof

... will crash when it gets to the bottom of the file.

So you'll need to change it ...

WHILE ( !oSelect:Eof ) .AND. oSelect:FIELDGET(#ITEMDATE) = dDate

Seek()

This works beautifully on an SQLTable{} instance but the order of parameters must be changed. In every instance (which is understandably a pain if you're facing hundreds of these, including those you've long forgotten).

Seek( Str( nAccount, 7) + DToS( dDate ), FALSE, FALSE )

... must be changed to ...

Seek( { #ACCOUNT, #ITEMDATE}, { nAccount, DToS( dDate ) }, FALSE )

(Note that we're storing the date as a string.)

Seeking and Looping

Don't try to loop a SQLTable{} instance. The index order is unreliable and therefore unusable. Instead do this:

IF oTable:Seek( { #ACCOUNT, #ITEMDATE}, { nAccount, DToS( dDate ) }, FALSE )
  cStatement := "SELECT * FROM (tablename) WHERE ACCOUNT=" + LTrim(Str2( nAccount, 7) ) + "' AND ITEMDATE>='" + DToS( dDate ) + "' ORDER BY ITEMDATE"

  oSelect := SQLSelect{ cStatement, GLOoCONNECTION }
  oSelect:GoTop()
  WHILE !oSelect:Eof
    // ... do whatever
    oSelect:Skip(1)
  ENDDO
  oSelect:Close()
ENDIF

In other words: the only actions that, in our experience, can reliably be performed on a SQLTable{} instance are seeking a single record, retrieving the data and saving it. Everything else is done is a SQLSelect{} instance.

Appending and Updating

The basic method is to begin a transaction, seek, save and then close the transaction.

(We keep global variables for things like SQL statement instances and the constant SQL Connection, which remains open throughout the application.)

Here's how we do it:

// oTable is an instance of SQLTable{} in this case the table name is 'MASTER'
GLOoSTATEMENT:SQLString := "BEGIN TRANSACTION" // this is the same as locking a DBF record
GLOoSTATEMENT:Execute()
aSave := {} // a multi-dimensional array of variables to be saved.
            // { { cFieldName, uVar } } 
            // Example: { { "ACCOUNT", nClient }, {"ADDRESS", cAddr}, {"CITY", cCity} }

IF nEditMode = MODE_APPEND // it's appending a new record
  oTable:AppendNew() // this uses the SQLTable{}:Append() and inserts the unique ID
  oTable:FIELDPUT(#ACCOUNT, nAccount)
  oTable:AppendRow()
ENDIF

// fill aSave with all the variables

// create an SQL statement to save everything; XUpdateStmt() is included in the examples
GLOoSTATEMENT:SQLString := XUpdateStmt( 'MASTER', aSave, "ACCOUNT=" + LTrim( Str2( nAccount, 7 ) ) )
GLOoSTATEMENT:Execute()

GLOoSTATEMENT:SQLString := "END TRANSACTION" // same as unlocking the record
GLOoSTATEMENT:Execute()

GLOoSTATEMENT:FreeStmt( SQL_DROP )

(Honestly we don't know exactly how FreeStmt() works but since we're in the habit of freeing memory this seems like the thing to do. )

Your comments are welcome.

This is just a start. We don't pretend to have the all the answers here; or even the right answers. We're merely trying to help others avoid the time-consuming trial-and-error of using traditional tools to resove present-day problems. Quickly and inexpensively. If you have anything to add, we'll certainly be happy to hear from you.