SQLite from VFP9?

[davisonpro]

I've been trying to research using SQLite from a VFP9 front end. I've only found threads here discussing how to move data from SQLite into FoxPro.

I don't think MySQL (or any of the direct competitors) is a good fit for my applications since it seems to be too complicated to deploy and run for my 1,500+ users who struggle with any level of technology. I think I want to stay with an embedded solution.

Does anyone here have experience, successes or failures or war-stories, with regard to deploying apps with VFP9 and SQLite?

I know some will ask about my motivation for this so I'll explain now: I want to be able to deploy the same front end against other installed, managed SQL RDBMS systems that some of my users already have installed on their servers (for other apps they run) and have SQLite available for those who do not - so I can run the same front end and just configure alternate connection strings. I know I'll be limited in my SQL but my apps don't require high levels of sophistication nor are they particularly "sensitive" to RDBMS performance. The data is generally quite small and not used on a daily basis. Concurrency is not a huge issue for me except in my very large clients and those are the ones who already run SQL-Server, Sybase, MySQL, or Oracle.

I have experience with deploying apps that run on almost any managed SQL-RDBMS but not using VFP as the front end.

 

[Mike Lewis]

The biggest negative for SQLLite (from a VFP point of view) is the lack of a professional ODBC driver. The only drivers available seem to be works in progress by enthusiastic amateurs who aren't necessarily able to offer an ongoing support service.

This is not a criticism of the developers in question - rather it's a comment on the fact that you are getting a free product, and the quality is in part governed by the price.

Without a reliable ODBC driver, you won't be able to do SQL pass-through or create remote views, thus drastically reducing the usability of the product within VFP (but not necessarily within other development platforms).

At least, that was the situation when I last researched it. If things have changed recently, I'm willing to be corrected.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[davisonpro]

Thanks Mike!

That answers one of my questions that I forgot to ask: is an ODBC driver going to be required? I was hoping to avoid that.

 

[Olaf Doschke]

davisonpro,

yes, either an ODBC or OLEDB provider is required. Remote Views and SPT require ODBC, OLEDB providers can be used by cursoradapters.

Why not use native DBFs? Even if you only have programmed for SPT usage, you can use the VFP ODBC driver or OLEDB driver to bind to DBFs as "remote" data. It's a bit odd to do that, but it'll work.

Bye, Olaf.

 

[Olaf Doschke]

Just looked at SQLite and found this:

Situations Where Another RDBMS May Work Better

Client/Server Applications

If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

This speaks against your planned usage of SQLite. MySQL is more appropriate besides DBFs of course.

Bye, Olaf.

 

[Mike Lewis]

Another possibility would be Microsoft SQL Server Express - provided the 4 - 10 GB database limit isn't a problem (which, I gather from your post, it won't be).

Regarding MySQL, be aware that this isn't necessarily free, but rather is open source. You'd have to seriously consider whether an open source licence would be a good choice for your application.

Also be aware that developing a front end against a range of back ends isn't necessarily the easy option that some people assume. You will need to take account of differences in language syntax and data types, amongst other things. This probably won't be a problem if your SQL is very simple (as you said it is), but is worth keeping in mind.

Miie

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Olaf's question remains unanswered - "Why not use native DBFs"

I know you said - " I want to be able to deploy the same front end against other installed, managed SQL RDBMS systems that some of my users already have installed on their servers"

But why?

If you have your application use its own VFP data tables and databases in all situations, then you will not be dependent on what the users may (or may not) already have installed.

Or, at the very least, use VFP data tables where the users do NOT have something already installed.

Good Luck,
JRB-Bldr

 

[davisonpro]

Thanks folks!

I've been researching these solutions for about 6 months now so I'm aware of what most of you are telling me. My question was more about the "how" than the "why". I don't know everything and the VFP front-end is where my experience is somewhat limited.

WRT the ODBC thing - I guess I've been spoiled in working so long with systems that have native drivers for most of what is out there in the "full size" database engines. I haven't had to use ODBC in quite a while. I do remember developing a deep dislike for the installations of ODBC drivers on disparate systems that are hundreds of miles away with users who don't even know how to use Windows Explorer so, that is probably something I'll steer clear of for a while.

I've never been too crazy about DBFs. The performance is fine and I suppose they aren't so "fragile" as they were 20 years ago. So, if I'm stuck with the VFP tables for a while then I can live with that.

So, back to my real question: is anybody out there using SQLite with VFP?

Thanks!
Al

 

[Mike Lewis]

Al, don't let your "deep dislike" for installing ODBC influence your decisions. In VFP front-end applications, ODBC is the only practical option (well, it's posssible to use OLE DB with a cursor adapter, but ODBC is simpler and gives more options).

If you have had problems in the past with installing the drivers, it's better to face up to the problems and seek a solution. It's not as if you are trying to anything unusual. I expect most of us here have done remote installs of ODBC drivers many times.

With SQLite, the bigger problem is whether any available drivers are of good enough quality and will enjoy long-term support.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

You're unlikely to find many people using SQLite with VFP because it doesn't make much sense to do so. It was invented to provide a local data engine to applications that don't have one. VFP already has one that is arguably better.

(I'm partly convinced SQLLite was invented for one specific demo during an MVP summit that kept repeating "see VFP guys? local data in VB!")

There's a lot of misunderstanding around ODBC, such as "drivers are difficult". OCBC drivers are dll files that plop into the system directory. End. Of. Story.

What people have difficulty with is DSN setups, a different kettle of fish that can be obviated with DSN-less connections.

If you're shy of using ODBC and shy of using DBF storage, then using VFP for development makes little sense at all. It would be better to move to a newer, currently supported, language that actually *needs* something like SQLLite and therefore probably supports it. Using VFP but discarding its best features makes no sense at all.

 

[davisonpro]

Dan - your points are well taken. Thanks for that!

My current situation with VFP is that I bought a company that built its products in VFP and there are about 750k lines of code and a couple of hundred DBF files involved in the systems.

I *used* to develop a lot in xBase (dBASE, Fox, Clipper) but haven't had to work in it for about 10 years. I plan to own this company for another 10 years so, I've been trying to find some "paths forward". VFP is my immediate future - at least the next 2 years - after that, I can't say yet. I'm looking for a way to leverage all the re-design and re-development in ways that won't necessarily lock me into VFP but that is not to say that I won't continue with it forever. I'm just not prepared to make that call right now.

Meanwhile, I have no choice but to use VFP for a while. Maybe I will grow to love it (again) and never switch.

I bought this company knowing that the products were the weakest and most vulnerable part but the company is strong and has a great future if I can keep the products competitive.

I appreciate all the help and advice and opinions I'm getting here! It gives me some confidence that I can take my time on deciding the long-term paths without feeling too glum about my current situation. The code I've inherited is old and clunky and is just "spaghetti code" of the worst sort! But, it runs and my customers like it. I can't afford to throw out the baby with the bath water so...here I am. Trying to get a handle on what is possible and what is best.

For now, I think I'll just hang with the native database stuff and keep my eye on the horizon.

I sincerely appreciate everyone's input!

Thanks,
Al

 

[Olaf Doschke]

I can only second dan on his thoughts.

I made one assumption which is most probably wrong as I read along all your posts here, in that you already have a VFP frontend using some remote database access and therefore just want to add to the portfolio of supported backends. If that is not the case, then actually the only question is, why not use DBFs?

One (small) advantage for supporting other backends companies already run is, that this will probably fit better in their software politics, for example if they only do use SQL Server and especially if other RDBMS are not allowed.

If you just begin using vfp to program simple frontends with a small data base backend and want variety there, then I strongly recommend using cursor adapters, first to native DBFs, then towards SQL Express perhaps, then add support for other backends.

The main work, if using cursoradapters, is to adapt the database itself and perhaps make some changes in the CA schemas and of course it's connection string.

Bye, Olaf.

 

[Dan Freeman]

Al,

Legacy apps is an arena I know well. Looking for an alternate data storage technology will actually work against you. Your code base, if it used traditional Xbase methodologies, is fundamentally incompatible with working with a remote data source.

In Xbase we tend to rely on the fact that we have a persistent connection to open files, each of which has a permanent "record pointer" that we rely on and construct our logic around.

None of that exists when dealing with non-Xbase data. It's a paradigm shift that will mean most of your code needs to be rewritten (unless it was written with current trends in mind, and that's a pretty rare bird for sure).

Build for the future of your business, for sure. Just be aware that there isn't a quick band-aid you can apply to what you've got.

Dan

 

[Olaf Doschke]

ust out of curiosity I downloaded the DLL version of SQLite and declared the minimum set of functions to open, close a SQLite DB and execute SQL.

In short, this 4 lines of code in pure foxpro can also be run in an sqlite3_exec() call, but the auxiliary code to read the result of the SQL-Select alone is needing about 40 lines.

create cursor test (iid integer, cText C(5))
insert into test values (1,"hello")
insert into test values (2,"world")
select * from test

Ported to SQLite usage:

* adapt this to your location of the sqlite3.dll
Cd C:\...\sqlite\ 

Set Library To vfp2c32.fll Additive
&& CreateCallbackFunc init
#Define VFP2C_INIT_CALLBACK	0x00000100
INITVFP2C32(VFP2C_INIT_CALLBACK)

#Define SQLITE_OK           0   && Successful result

Declare Integer sqlite3_open    In sqlite3.Dll String cFilename, Integer @iDBhandle
Declare Integer sqlite3_close   In sqlite3.Dll Integer iDBHandle
Declare Integer sqlite3_exec    In sqlite3.Dll Integer iDBHandle, String cSql,;
   Integer iCallbackAdress, Integer iCallbackHandle, String @cError
Declare Integer sqlite3_close   In sqlite3.Dll Integer iDBHandle

Declare Integer sqlite3_errcode In sqlite3.Dll Integer iDBHandle
Declare String  sqlite3_errmsg  In sqlite3.Dll Integer iDBHandle

Local lnDbHandle
lnDbHandle = 0
Clear

If sqlite3_open(Strconv(':memory:',9),@lnDbHandle) <> SQLITE_OK
   ? 'error:',sqlite3_errcode(lnDbHandle),' - ',;
      Strconv(sqlite3_errmsg(lnDbHandle),11)
Endif

loCallBack = Createobject('sqlitecallback')

If sqlite3_exec(lnDbHandle,;
      Strconv('create table test (iid integer, cText C(5));'+;
      'insert into test values (1,"hello");'+; 
      'insert into test values (2,"world");'+;
      'select * from test;',9),;
      loCallBack.Address,123,Null) <> SQLITE_OK
   ? 'sql error:',sqlite3_errcode(lnDbHandle),' - ',;
      Strconv(sqlite3_errmsg(lnDbHandle),11)
Endif

If sqlite3_close(lnDbHandle) <> SQLITE_OK
   ? 'error:',sqlite3_errcode(lnDbHandle),' - ',;
      Strconv(sqlite3_errmsg(lnDbHandle),11)
Endif


Define Class sqlitecallback As Exception
   Address = 0

   Function Init
      This.Address = CreateCallbackFunc('sqliterow','BOOL',;
         'LONG, LONG, LONG, LONG',This) && in a galaxy FAR FAR away
   Endfunc

   Function Destroy
      If This.Address <> 0
         DestroyCallbackFunc(This.Address)
      Endif
   Endfunc

   Function sqliterow()
      Lparameters tnCallbackHandle, tnColumns,;
        tnAdressesOfColumnValues, tnAdressesOfColumnNames
        
      ? 'callback from sqlite with callbackhandle ',tnCallbackHandle

      Local lnColumn, lcAdress, lnAdress

      Local Array laColumnNames[tnColumns]
      For lnColumn = 1 To tnColumns
         laColumnNames[lnColumn]=''
         lcAdress = Sys(2600, tnAdressesOfColumnNames,4)
         lnAdress = CToBin(lcAdress,"4RS")
         Do While .T.
            lcChar = Sys(2600, lnAdress, 1)
            If lcChar = Chr(0)
               Exit
            Endif
            laColumnNames[lnColumn] = laColumnNames[lnColumn] + lcChar
            lnAdress = lnAdress + 1
         Enddo
         tnAdressesOfColumnNames = tnAdressesOfColumnNames + 4
      Endfor

      Local Array laColumnValues[tnColumns]
      For lnColumn = 1 To tnColumns
         laColumnValues[lnColumn]=''
         lcAdress = Sys(2600, tnAdressesOfColumnValues,4)
         lnAdress = CToBin(lcAdress,"4RS")
         Do While .T.
            lcChar = Sys(2600, lnAdress, 1)
            If lcChar = Chr(0)
               Exit
            Endif
            laColumnValues[lnColumn] = laColumnValues[lnColumn] + lcChar
            lnAdress = lnAdress + 1
         Enddo
         ? laColumnNames[lnColumn], ':', laColumnValues[lnColumn]
         tnAdressesOfColumnValues = tnAdressesOfColumnValues + 4
      Endfor

      Return .F. && Return .T. aborts the SQL Select
   Endfunc

Enddefine

Do you really want to use SQLite this way? I think not.

Also considering that using DBFs natively in a VFP frontend does mean to have no installation besides the vfp runtime you need to install anyway, this also does not make SQLite an easier backend installation.

Let me expand on this code using the slite3.dll a bit more anyway, to further explain, why SQLite does not at all target a VFP programmer.

The way to retrieve results from an sqlite3_exec() is to have a callback function. This is the first hurdle, I used Christian Ehlscheids VFP2C32.FLL, which enables to use Win32 API needing callback funcionality, or general any C code needing a callback adress.

You'll find this FLL here:

http://vfpx.codeplex.com/wikipage?title=VFP2C32

The callback function is called from sqlite any time, one row of the result set of an SQL-Select is ready to be retrieved. So in each call you get back one record. But not in a very simple format for foxpro. What you get back is essentially the number of columns, an array of memory adresses of column values, and an array of memory adresses of column names.

To be able to make use of these adresses you need to call sys(2600) for each single char or byte of a column value or column name.

In C/C++ pointers to memory are a very normal thing and processing them is easy enough, but in vfp this is very unnatural. VFP is a higher level language keeping you from fiddling with pointers and this is a very good thing about the vfp language, but in this case it hinders you to more easily get back the rows of an sql select.

Even if I would expand on this example code and create a more comfortable callback function creating a foxpro cursor to contain the sql resultset, this would be very, very, very slow in comparison of handling this within C/C++, this is not practical at all.

There is no need for SQLite, as long as it does not support anything special DBFs don't offer I see no use at all from VFP. If there would be a decent ODBC driver and the perspective this is updated along with SQLite itself, then it would be more worth considering as a backend option, but if you insist on not using DBFs, then an SQLite DB is worse and there are many other RDBMS you can use as a backend.

Bye, Olaf.

 

[Olaf Doschke]

Forgot to mention - If you want to test this code you need these files in one Directory you initially CD into:

1. sqlite3.dll
2. vfp2c32.fll
3. msvcr71.dll (coming from the vfp2c32 download)

Bye, Olaf.