SQLite to VFP Cursor 1

[David Higgs]

I would like to be able to Open an SQLite DBF and to copy the contents to a VFP Cursor. I have the following Connection String (but realise that there is much more to it than just that).

		dbName = "My_DB.SQLite"

	SqliteConn = "DRIVER=SQLite3 ODBC Driver;Database=My_DB.SQLite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
				 
	  SQLConn = Sqlstringconnect(SqliteConn)

The connection string refers to SQLite3 ODBC Driver and a Google Search takes me to:

http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

I've Run sqliteodbc.exe which has created Files in C:\Program Files (x86)\SQLIteODBC Driver.

SQLConn = 1 So it looks like the connection is made?

Any help would be much appreciated.


Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

Since there is MySQL as database I wonder why SQLite would install itself into C:\MySQL, but maybe that was you just installing it. What did you install? Wikipedia mentions the "most official" ODBC driver is from Christian Werner.

I have used MSSQL Compact Edition, which goes into the same direction, but I wonder why you want to use SQLite. Do you have a need to connect to SQLite database of another software, or do you think it could replace DBFs? From the SQLite homepage

https://www.sqlite.org/whentouse.html

you can learn, that its use mainly is that, what we already have with local DBFs, and local DBFs are much easier to master in VFP, of course. Besides local DBFs for single user applications don't suffer from the SMB2 or later protocol problems. And to share data SQLite is no good choice, it's not for client/server usage.

You have a simple mistake in your code, as you define a variable dbname, then use Database=dbName in the connection string, if you want that data base name to be mytable.SQLite you better write mytable.SQLite directly or write &dbname., or simply "+dbname+" or best option in VFP9, perhaps, use Textmerge():

SqliteConn = Textmerge("DRIVER=SQLite3 ODBC Driver;Database=<<dbName>>;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")

Be aware, this creates a database, not a table, so a file name mytable.SQLite is misleading, an SQLite file is a database file, more like SQL Server or Access, all tables of a database are stored in that one file. The reserved file extension is .db, not .SQLite. Besides, you specify a file name, not just a database name:

https://www.connectionstrings.com/sqlite3-odbc-driver/

The way you did connect you'll now have a file just named dbname without file extension somewhere.

The next few steps can just be like this:

Local lcDatabaseFilename, lcConnectionString, lnStatementHandle

lcDatabaseFilename = "d:\temp\sqlite.db"
lcConnectionString = Textmerge("DRIVER=SQLite3 ODBC Driver;Database=<<lcDatabaseFilename>>;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")
lnStatementHandle = Sqlstringconnect(lcConnectionString)
? lnStatementHandle
? SQLExec(lnStatementHandle,"create table test (id integer, cdata char(10))")
? SQLExec(lnStatementHandle,"insert into test values (1,'hello')")
? SQLExec(lnStatementHandle,"insert into test values (2,'world')")
? SQLExec(lnStatementHandle,"select * from test","crsTest")
? SQLDisconnect(lnStatementHandle)
Select crsTest
Browse

I recommend you write wrappers around these function if you don't step up and write cursor adapter classes.

Everytime SQLStringConnect or SQLExec returns a negative result that means an error occurred, that doesn't trigger VFP system error handling or ON ERROR routine or the Catch of TRY..CATCH, you have to react and get error info from AERROR() in such cases, and that's best done once in such wrapper functions that keep the statement handle SQLStringConnect returns in their possession (as private property of a SQLite object, ideally, so the wrapper at best is a class with methods, or at least in some property of _sreen or your application object goApp or whatever you use to store such permanently necessary information as file handles or in this case a statement handle is.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Since there is MySQL as database I wonder why SQLite would install itself into C:\MySQL, but maybe that was you just installing it. What did you install? Wikipedia mentions the "most official" ODBC driver is from Christian Werner.

I don't know how the SQLite3.dll File ended up in the C:\MySQL Directory; looking at the dates of files in that Directory it has probably been there for a couple of years possibly when MySQL was installed. I downloaded the SQLIteODBC.EXE from Christian Werner.

I have used MSSQL Compact Edition, which goes into the same direction, but I wonder why you want to use SQLite. Do you have a need to connect to SQLite database of another software, or do you think it could replace DBFs? .

I would like to connect to a SQLite database of another software with a view of writing a Front End module to enable additional info to be saved in an external database.

Be aware, this creates a database, not a table, so a file name mytable.SQLite is misleading, an SQLite file is a database file, more like SQL Server or Access, all tables of a database are stored in that one file. The reserved file extension is .db, not .SQLite.

The external database FileName is of the "MyTable.SQLite" format and is located on the Local PC Drive.

Thank you for your code, I will take a look and report back.

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

I downloaded the SQLIteODBC.EXE from Christian Werner.

That installs itself into C:\Program Files (x86)\SQLite ODBC Driver amd also displays that target folder during installation.
Well, you don't need to know the installation path of the odbc driver, it'll be availabel anyway, C:\MySQL\ is not part of SQLite, anyway.

OK, you have a good reason to use the SQLite ODBC driver, now you just need to point it to the full file name. Indeed .db is just the normal file extension, the ODBC driver works with any file name, just specifying "dbname" I found a "dbname" file in HOME() of VFP. (If your Home() is in program files, you'll rather find it in C:\Users\%USERNAME%\AppData\Local\VirtualStore\Program Files (x86)\Microsoft Visual Foxpro 9) It's not very important, as you can just leave it there, it's maybe just 8kb or less. Just notice you better specify a fully qualified file name here, with drive and path, to which you want to connect to. If it doesn't exist, it will be created.

You can use [tt]SQLTables(lnStatementHandle,"","crsTables")[/tt] to get a list of tables in the database.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

You can use SQLTables(lnStatementHandle,"","crsTables") to get a list of tables in the database.

This revealed a Table with 5 Fields and 3 Records.

Table_qualifier = .NULL. (all 3 Records)
Table_Owner = .NULL. (all 3 Records)
Table_Name = Memo (which contained:- Informations, hrdlog,log)
Table_Type = TABLE (all 3 Records)
Remarks = .NULL. (all 3 Records)






Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

Yes, not all databases have all types of informations, but you can see this database has 3 tables named.
1. Informations
2. hrdlog
3. log

To see what's in there, why not query these tables?

...
? SQLExec(lnStatementHandle,"select * from Informations","crsInformatins")
? SQLExec(lnStatementHandle,"select * from hrdlog","crsHrdlog")
? SQLExec(lnStatementHandle,"select * from log","crsLog")
Select crsInformations
BROWSE NOWAIT
Select crsHrdlog
BROWSE NOWAIT
Select crsLog
BROWSE NOWAIT

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Hello Olaf,

Once again many thanks for your assistance it was very much appreciated. Having gained access to the Data
should keep me occupied for quite a while!




Regards,

David.

Recreational user of VFP.