SQLite to FOXPRO

[andy0923]

Would anyone know how to read a SQLite table data into Foxpro? Any help would be greatly appreciated.

 

[white605]

Not sure about the hows, Wouldnt you have to connect to the data first

http://www.connectionstrings.com/default.aspx?carrier=sqlite

There is an odbc string mentioned at the bottom of this page
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[Mike Lewis]

Andy,

1. Download the SQLite ODBC driver from

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

2. Go into the ODBC applet in Control Panel, and create a new DSN based on the driver.

3. Use that DSN either to create a remote view into your data, or to access the data via SQLEXEC().

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Andy,

do like Mike Lewis said. These steps are all the same for any remote database. Just need an ODBC driver. There may be some database specific settings you need to take care of, that concern eg mapping of field types. Those things should be advanced/extended options of an ODBC DSN.

Bye, Olaf.

 

[andy0923]

Thank you all for your responses. There was no easy way to get the data other than through a query in excel and hours of googling but without your help I wouldnt have even gotten that far. Thanks again to this site and to you.

Reminds me of the old days when DOS and DBase were disappearing - now it seems to be happening to Foxpro and Windows.

 

[JanBucek]

Reminds me of the old days when DOS and DBase were disappearing - now it seems to be happening to Foxpro and Windows."

MS Windows disappearing? It is too nice to be true.

 

[Olaf Doschke]

wonder what excel could do, what vfp can't. I have not installed SQLite but the following code is quite straigth forward connect query, disconnect. You can use it for any database you can connect to with a connectionstring. The site white showed you gives a lot of these strings. They are general connection strings, you might need to add user,password or other settings, that are db specific.

Local lcDatabase, lcUser, lcPassword, lnHandle
lcDatabase = "my.db"
lcUser = "user"
lcPassword = "password"

lnHandle = db_connect(lcDatabase, lcUser, lcPassword)

If lnHandle>=0
   Local lcAlias
   lcAlias = "mycursor"
   SQLExec(lnHandle,"select * from mytable",lcAlias)
   Select (lcAlias)
   Browse
   db_disconnect(lnHandle)
Else
   ? "connect failed"
Endif

Procedure db_connect()
   Lparameters tcDatabase, tcUser, tcPassword
   Local lnHandle
   lnHandle = Sqlstringconnect("DRIVER=SQLite3 ODBC "+;
    " Driver;LongNames=0;Timeout=1000;NoTXN=0;"+;
    "SyncPragma=NORMAL;StepAPI=0;"+;
    "Database="+tcDatabase+";Uid="+tcUser+";Pwd="+tcPassword)
   Return lnHandle
Endproc

Procedure db_disconnect()
   Lparameters tnHandle

   SQLDisconnect(tnHandle)
Endproc

Bye, Olaf.

 

[Mike Lewis]

Reminds me of the old days when DOS and DBase were disappearing - now it seems to be happening to Foxpro and Windows

Both DOS and dBase were replaced by something better. I don't see that happening with FoxPro or Windows in the foreseeable future.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[andy0923]

*I so hope you are right Mike.

Thanks so much Olaf, Cant wait to try it out and let you know.

 

[andy0923]

Olaf,

I was just about to give up when i decided to cut and paste the connection string exactly from

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

and it worked like a charm!!!!!!!

A Thousand thanks to you and as usual all the people on this site.

 

[Olaf Doschke]

Hi andy,

makes sense to use that connection string, if you downloaded the driver from there. It would be nice if you post the string you used (without telling the real username and password of course, if that is part of that string).

Bye, Olaf.

 

[andy0923]

Sure thing Olaf.

What DID NOT WORK:
lnHandle = Sqlstringconnect("DRIVER=SQLite3 ODBC "+;
" Driver;LongNames=0;Timeout=1000;NoTXN=0;"+;
"SyncPragma=NORMAL;StepAPI=0;"+;
"Database="+tcDatabase+";Uid="+tcUser+";Pwd="+tcPassword)

What DID WORK:

strng="DRIVER=SQLite3 ODBC Driver;Database=d:\mydb.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"

lnHandle = Sqlstringconnect(strng)

Again thank you and all for your help.

 

[Olaf Doschke]

Okay, thanks

so it seems the Database must be the first thing within the connectionstring. Good to know.

There is another nice way to get at a working connection string interactively: Create an empty text file and change it's extension to UDL. double click on it and you get into the dialog to configure a DSN to a database, by choosing the driver, browsing for the database etc. Can even test that connection. Afterwards you change the udl extension to txt again and find in that text file the connection string.

Bye, Olaf.

 

[Olaf Doschke]

I see another thing, the database seems not to need any login (User/Password). So the code could work if you simply cut that part off.

Bye, Olaf.