SQLite3

[rdonnay]

I am working on a project for Michael Greene that involves SQLite3.

I like the potential of this project because SQLite3 is the easiest-to-deploy SQL engine in the world.
No networks, security, ODBC, etc.

I picked up some sample code from Pablo Botella's website but it was incomplete.
There are quite a few C wrappers required to handle callbacks and structures.

I am able to do some rudimentary SQL stuff but nothing as robust as I can do with Advantage or ODBC languages.

My goal is to create a complete set of SQLite3_*() wrappers for their entire API like I did for ADS.

Does anyone have any interest in this or any experience with SQLite3.
I understand it is the most widely used SQL system in the world.

[RDalzell]

Roger,

We use SQL (Micro$oft) currently for software other than my own. I would love to have a SQL solution to offer, but have been busy/afraid/without skills, knowledege and ability to do so.

I was looking forward to the Alaska SQL solution as Steffen promised that it only required the addition of a single statement to make current software SQLable.

I have no knowledge to share, but a desire to have.

Rick

[rdonnay]

Rick -

I totally believe that postGreSQL is the best solution for legacy applications, but unfortunately it is not yet available and there have been no announcements about it.

I am anxious to use it in future projects, but Michael Greene can't wait any longer.

Roger

[sfsltd]

Hi All

The reason we are looking for some way to connect our XBase application to SQLite is because we are (still) writing rich client applications and distributing/installing them using downloaded MSI packages. We have 2500 instances running in UK schools today and could supply 10 times that number IF we can get our support costs down.

Our current application, is still based on DBF tables and CDX indexes. We don't have a problem with DBF's, it's simple technology and (generally) works well. However we don't own nor manage the school networks, and our application is too easily corrupted when services like AntiVirus Scanning / File Indexing are installed or changed. Services that apply OpLocks to our files as they open and close.

Thus our application sits in a 'lottery' of machine Oplocks where it inevitably gets 'trumped' from time to time. XBase++ RDD technology does not interact with the Windows o/s to detect and handle OPLOCK states in the same way other technologies do. Thus there is little we can do beyond try to make our code double check it can read/write ok before it does anything.

Even with this type of checking we get hit far too frequently. With 2500 schools using the system, rarely does a day goes by where we don't have to fix DBF tables!... and this is really hurting >:(

OK... so why have we not adopted Advantage Server or PostgreSQL ???

Here's the key reasons:

* it installs as a service, great if you want ONE reliable database... not at all good if you want 25000!
* we have to get a third party network manager to do the service install/upgrades
* it's not Microsoft SQL so network managers don't want to manage it
* we have to ask the network manager before we can do even the simplest task

In short SQL Servers double our cost to install and run the application on a school network!

That's where SQLite comes in:
* it's a simple embedded database
* it's robust, ACID compliant
* it has a small footprint so we can use email/ftp if we want to
* it can be encrypted
* it's available under LGPL licensing
* it's cross platform, cross device and widely used.
In addition, being a single file containing many tables ... it only gets scanned ONCE on open and ONCE on close so it's fast !

i.e. it installs and behaves like an 'bomb proof' version of Microsoft Access DB.

The Harbour project have a connector as do many other languages... why not XBase++ ??

Michael

[HECTOR]

Hi Michael

Attach a test of sqllite3, wrapper of Pablo Botella and your library otx4 free
Soon it will send a new example

Best Regard
Hector

[HECTOR]

Hi Michael


I found out that was post an older version of the wrapper

Here you can found the new with a small sample showing how to add records binding int, text and blob as named params
I think Pablo was send a copy of this to Roger

Please eliminate the basetable created with clean.bat

Best Regard
Hector Pezoa
Chile

[sfsltd]

Hector

Thanks for the wrapper. Roger and I have been looking at the work Pablos has already done, however there seems to be quite a lot of functions that still need developing (*) ... which raises the question how one or two developers can write and support the amount of code needed. (*) Please correct me if this view is wrong

IDEA:

It occurs to me that there is a lot of wrapper code already written for SQLite.
Everything from SQLite utilites to drivers for all sorts of languages.

Does anyone have views on the feasibility of developing a wrapper by adapting existing LGPL code and releasing the XBase wrapper under an LGPL license?

* Can anyone identify code in other LGPL/BSD projects that can be adapted for XBase++
* Who is interested in connecting to SQLite??
* Who is able to provide skills, code or money to get the project moving?
* What's the best way to get a LGPL project started?
* are there any licensing conflicts/issues such as with Alaska?

Would appreciate feedback on this.

Michael

[Pablo Botella]

Hi,

In C++ I've some classes that I was made to handle sqlite in C++ projects, when I was need to write an audit system in a prg I was think automatilly in sqlite, and was write this small class to handle that I was need.

I was skip the sqlite3_column_... functions and blob-stream IO , these functios can be used directly in xbase with
ot4xb dll functions and commands (DllCall() will not able to handle some of them and also is slower), probably will complete the class with this methods when will have some free time.

As I only was need to retrieve very small recordsets, I was used a Xbase++ array to hold the results, you must take in account that sqlite does not provide a recordset like another sql interfaces, so you must usa yoour own mechanism to hold the records or play dinamically with Count()/LIMIT/OFFSET for your SELECT statements

However there are another subset of sqlite functions to make new SQL agregates and SQL functions, provide custom collation, or custom I/O (VFS) for automatic crypt/decrypt where I will not recomend to use Xbase++, is posible to make them using my free callback compiler or even I have also a mini runtime ASM in a single CH that can do the trick, but this callbacks will be called by the sqlite core and is preferible to make them in C or C++ to avoid loose performance.

Regards,

Pablo Botella

[sfsltd]

Hi Pablo

Clearly you have done a lot more than most on the SQLite connector.

I would be interested in your view as to how much work is needed to get a dll that is capable of reading/writing core transactions to SQLite. Nothing too clever, just the basics.

Also, I can see the ot4xb.dll read data from SQLite.. but you post suggests you can also write records, is this the case?

Michael

[rdonnay]

Pablo -

I am interested in why your aliased functions ( example: @sqlite3:sqlite3_column_name(stmt,n) ) seem to work properly whereas DLLFUNCTION can produce IDSC's and wrong returned values.

This causes an error because it returns a numeric instead of a string:
sqlite3_column_name( stmt, 2 )
DLLFUNCTION sqlite3_column_name( stmt, n ) USING CDECL FROM sqlite3.dll

I used DLLFUNCTION wrappers for every function in the ACE32.DLL (Ads) library with no problems like this.
Never a problem.

Yet SQLite3.dll seems to not work with DLLFUNCTION calls.

I assume that you are creating some kind of dynamic call from inside OT4XB.dll.

I could probably create a complete set of SQLite3_*() function calls by using your technique, but then would I have the right to distribute your library?

SQLite3 works very similar to Ads when working with a statement cursor. Both return a numeric handle to a cursor and both have a similar set of functions to step through the cursor and extract the data. I am familiar with this technique in Ads and have written a complete set of higher-level functions that return data in a format more friendly to Xbase++ programmers, ie in an array or in a browse.

It would be nice to build this kind of library for SQLite3 and I don't mind writing the complete set of wrappers provided that I know they will work with your aliased concept.

Roger