How to proces SQL light3 tables from VFP

[Nifrabar]

Hi!
Long time since I was here. But I don't do too much with VFP since I'm retired.
For home automation I m using Domoticz. Domoticz keeps track of all data using a SQL light3 database.
I want to be able to proces the database-tables and preferably using VFP.
I managed to create a connection thanks to

http://www and

Wil Hentzens publications:
in short
STORE SQLCONNECT('Connect1') TO gnConnHandle
IF gnConnHandle <= 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
= MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
STORE SQLTABLES(gnConnHandle, 'TABLE', 'mycursor') TO nTables
? nTables
IF nTables = 1

SELECT mycursor

brow

remaining code kept away

Now I get a browse window which has a memo field Table_name.
So I can see all tabe names.
But how do I connect to a table ? I like to update columns and delete some of them.
Can I treat those tables as VFP tables in a way ?

Thanks for your help

-Bart

 

[Mike Lewis]

Good to see you again, Bart. It's been a while since you were here.

First point: the fact that you are connecting to an intelligent lighting system is not really relevant. You are simply using an ODBC driver to talk to a "server", so anything that you have read about ODBC will probably be relevant.

Once you have a connection handle (gnConnHandle in your case), you can use it to send any SQL command to the server. You do that by using SQLEXEC(). Yuu pass it the connection handle, and the SQL command that you want to execute. If the command returns a result set, you can optionally pass it the name of a cursor to receive the result.

So, for example, you could do this:

lnReply = SQLEXEC("gnConnHandle", "SELECT * FROM SomeTable", "MyCursor")

or

lnReply = SQLEXEC("gnConnHandle", "UPDATE SomeTable SET Field1 = '123' ")

In each case, lnReply will contain a reply code.

This is a simplified explanation, but it should give you a start. Look up SQLEXEC() in the Help for more details.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

To add to Mikes answer nd directly answer your question:

No, you can't act on server tables like DBFs and USE them, you're depending on SQL. The best adaption of all dbf principles you can achieve is when you make a result (VFP cursor) updatable, which needs several CURSORSETPROP() settings about updatenames, updatablefieldlist,keyfieldlist (usually just one key field), sendupdates and wheretype and perhaps some more.

Then you still only can act on data and use TABLEUPDATE() to store buffered changes (new, changed and deleted rows), But when you alter the cursor and drop a field you don't drop the column in the server table, that still needs SQL, aLTER TABLE.

Chriss

 

[Nifrabar]

Both Mike and Chris, thanks for heading me in the right direction.
Good base to further investigate.
Bart