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
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():
Code:
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:
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:
Code:
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
