Updating records in a table accessed via ODBC

[AndrewMozley]

I have a source of data (created by MySQL). I can connect to this data source using ODBC and I can import this data into a cursor, from where I can display it. In essence my code so far consists of :

.zConnect = "Driver = {MySQL ODBC 5.2a Driver}; Server=localhost; Database=anm_schema;  . . .
   SQLSTRINGCONNECT(.zConnect)
   lResult = SQLEXEC(.zHandle,"SELECT * FROM table1")

This all works, and I am able to browse the table. I would later like to update a record in Table1 (not its real name) in the schema. Rather optimistically, I have this code :

UPDATE table1 SET PRODUCT = "XYZ" WHERE RECNO() = 1

However this command does not work, because VFP looks for a table table1.dbf, and prompts for its folder. Is it possible that I have to use SQLEXEC commands - is that the standard way to communicate with an SQL database from VFP? Or is there a 'native' SQL command within VFP?

What do I need to use please. I would mention that I have done a lot of work with traditional VFP databases, but I am not familiar with the SQL approach.

Thanks as always

 

[Mike Lewis]

Andrew,

You are right that the SQLEXEC() places the data into a cursor. What you don't know is that the cursor is named, by default, SQLResult. So, the following code would work:

UPDATE [b]SQLResult[/b] SET PRODUCT = "XYZ" WHERE RECNO() = 1

If you don't like the name SQLResult, you can specify a different cursor name in your SQLEXEC(), for example:

lResult = SQLEXEC(zHandle,"SELECT * FROM table1", [b]"MyCursor"[/b])

But, of course, all that the above line does is to update the local cursor within VFP. It doesn't touch the data in MySQL. If that's what you want to do, then you have to use SQLEXEC() to send the UPDATE command:

lResult = SQLEXEC(zHandle, "UPDATE table1 SET PRODUCT = 'XYZ' WHERE ID = 1")

Here, you keep Table1, because that's the name of the table on the server. I've used [tt]ID = 1[/tt] in the WHERE clause, because MySQL won't know about RECNO() - of course, you must substitute whatever condition is appropriate here. You'll also see I've used single quotes around XYZ, because you are already using double-quotes for the command itself.

Finally, I'm not sure why you have a dot in front of zHandle. I've removed it in the above examples.

I hope this answers your question.

Mike







__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

In short: Yes.

In Detail:
You don't find your data, as you don't use the next parameter of SQLExec allowing you to give the result a VFP name. If you don't do so data arrives in a cursor named "query1", IIRC.
Take a look at the datasession window, and you'l see the ALIAS() generated.

You can now do SQLEXEC with UPDATE-SQL, DELETE-SQL, etc, but that will effect the remote table, not your query result. And vice versa, you can UPDATE ALIAS() Set field="X", but the MySQL table won't change, there is no magic band between them, the cursor is not the mysql table and vice versa. The same applies, if you do SELECT * FROM Some.DBF into cursor curResult and then UPDATE Some.DBF Set somefield=somevalue WHERE ID = x. This will also not update curResult, but only Some.DBF (unless the cursor is just a filter to the dbf, but that's a special case with fully optimizable queries on single DBFs only, just a side note).

You work with the shortened syntax .zConenct, .zHandle, which tells me this code comes from a WITH..ENDWITH block. What are you using there? Is it perhaps a framework dataaccess object, or did you do that yourself? If you use some framework, you should perhaps refer to it's documentation and look what it offers, before I reinvent the wheel here, with you.



If not, there are further possibilities. "Updatable" is the keyword. Remote View Cursors, SQL Passthrough Cursors (you generate with SQLEXEC, as in your code) and/or Cursoradapter Cursors can be made updatable, and in all cases TABLEUPDATE() then is the central function to update remote data.

You may look at thread184-1716941 in which I explain how to make use of that. It works with MySQL, too.

Quoted from there, here's a modified version to update data, instead of appending it:

* your connection string varies of course.
nHandle = SQLStringConnect("Driver={MySQL....};Server=....;Database=...;...;")

SQLEXEC(nHandle,"SELECT * FROM test","curData") && in the test case the table called test has fields id (an integer colimn) and data (a char(10) column or whatever you like nd transfers nicely to VFP data types).

SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curData')
CURSORSETPROP('SendUpdates',.T.,'curData')
CURSORSETPROP('Tables','test','curData')
CURSORSETPROP('KeyFieldList','id','curData') 
CursorSetProp('UpdatableFieldList','data','curData')
CursorSetProp('UpdateNameList','id test.id,data test.data','curData')

* Work on curData, as you like and know from working with DBFs here:
* eg UPDATE curData SET data ="XYZ" WHERE id=1
* or BROWSE and make changes

If NOT TABLEUPDATE(2,.T.,"curData")
   AError(laError)
   Set Step on
EndIf 
Close Tables All
SQLDisconnect(nHandle)

And this is just the hardest sample, Remote views come with the limited view editor, Cursoradapters let you use a builder and interactively choose database, table(s), fields, specify keyfieldsm updatable fields etc.

Some more tips (an any case):

1. Don't work with RECNO()s here, this is varying, depending how and what you query. Records don't have a constant invariant record number, like in a DBF. The cursor you fetch of course will have a recno, but if you introduce some WHERE clause, RENCNOs wil differ, this is not a unique identifier of a record anymore. It even wasn't in DBFs, when you used PACK.
Use Primary Keys, id fields, guid fields, whater is unique and can be autogenerated. I'm a big evangelist for uniqueidentifiers, GUIDs, for the ease of creating them, also within VFP, not depending on a trip to the database and back. In MySQL like in T-SQL the function to create a GUID is NEWID().

2. Work with WHERE. For sake of scalability, only fetch very small data tables as a whole, limit data via WHERE clause, only fetch what you really need for display, that also means, only fetch pagewise portions of data instead of what you're used to with USE or DE tables and the grid. It may be okay to fetch 1000 records, but if your data grows you don't want to fetch all data just because a user wants to edit a single customer name. This needs other thinking than USEing DBFs.

Bye, Olaf.

 

[AndrewMozley]

Thanks, Olaf and Mike, for your kind replies

In the matter of the use of .zhandle, I am indeed working inside a WITH . . . .ENDWITH section. This is because the code is a method within a form, and I chose to set up several variables as properties of the form. Apart from that, I don't think that I am using any other special features of the form in this code.

Olaf. Thanks for the code sample. However in your example, is not Curdata just a cursor, so that any changes applied to it do not affect the underlying table ("test", I believe)? I did not understand why it might be necessary to set up properties like buffering, since Curdata is only being used by this instance of this program. Likewise, it was not clear to me why I should need to issue a TableUpdate() command on a cursor? Or have I missed something?

Mike. Thanks for that. I have indeed been able to update a field with my ODBC table, using a variant of your SQLEXEC(.zhandle, "UPDATE . . . ."). Not really wishing to justify myself, but I only included the "WHERE RECNO() = 1" clause as an example of a filter ! I suppose I could have written "WHERE <somefilter>" and that would have got the idea across.

On the more general point, it seems that all my update instructions will have to be enclosed within an SQLEXEC() function call. I don't mind that, but I though that it was a feature of VFP that its statements allow you to apply SQL to a table directly. Indeed many of the entries in the Help files of VFP9 have titles like "UPDATE - SQL command" or "DELETE -SQL command". So I thought it might be possible to use these commands directly, rather than having to use SQLEXEC() as an interpreter every time. When is one able to use the SQL versions of commands such as UPDATE and DELETE directly, rather than via this extra level of interpretation?

 

[Olaf Doschke]

Well, you have understood half of my explanation very well, the code I gave indeed overcomecomes the separation of the cursor and the table, all the properties set make the cursor updatable. That was referring to the second part of my explanation about updatable cursors. They are not only updatable in the sense of readwrite cursors, enablin you to change the cursor, a TABLEUPDATE() really takes all buffered changes and "replays" the same modifications to the remote database table.

Bye, Olaf.

 

[Olaf Doschke]

In regard to your answer to Mike, The VFP language integrated SQL only works on DBFs and cursors, not on any remote data, how should it be possible? Not any database can understand any VFP code, and you can include usage of VFP commands in SQL.

But as you can make cursors updatable you can work on them with VFPs SQL commands, the only additional thing to do is to then update the remote database from the buffered changes via TABLEUPDATE().

Bye, Olaf.

 

[Mike Lewis]

I thought that it was a feature of VFP that its statements allow you to apply SQL to a table directly. Indeed many of the entries in the Help files of VFP9 have titles like "UPDATE - SQL command" or "DELETE -SQL command". So I thought it might be possible to use these commands directly, rather than having to use SQLEXEC() as an interpreter every time.

Andrew, you're not the only one who gets confused about that. The point is that there is a difference between SQL the language, and SQL the database. Many developers mistakenly talk about SQL databases, meaning back ends like MySQL and SQL Server. But SQL is nothing more or less than a language ("language" is what the L stands for in SQL). Databases like MySQL use SQL as their programming interface, but they are no more "SQL databases" than Visual FoxPro.

When you see references to "UPDATE - SQL command" or "DELETE -SQL command" in the VFP Help, they refer to commands in the SQL language that operate on Foxpro tables. This is to distinguish them from the old UPDATE and DELETE commands that were part of the original xBase language. It does not mean that they operate on back ends like MySQL.

it seems that all my update instructions will have to be enclosed within an SQLEXEC() function call. I don't mind that, but I though that it was a feature of VFP that its statements allow you to apply SQL to a table directly

There is such a feature. In fact, there are several possible ways of doing it. In summary, they are:

(i) You can set certain properties of your cursor (the one that is returned when you SQLEXEC a SELECT statement) such that any updates to the cursor (within VFP) automatically get sent to the back end.

(ii) You can use remote views. This essentially provides a wrapper for the functionality of (i) above.

(iii) You can use cursor adapters, which are conceptually similar to remote views, but which provide a lot more functionality (and complexity).

Personally, I don't bother with any of these. I find it easier to send my UPDATE (and DELETE and INSERT) commands directly via SQLEXEC(). But if you wanted to use one of the alternatives, I would personally go with remote views (but I'll be that other people here disagree with that). Whatever you decide, use it consistently. It's usually unsatisfactory to mix the above methods in the same program.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

> It's usually unsatisfactory to mix the above methods in the same program.

@Andrew:
I think Mike explained very well, why the VFP help refers to the SQL commands as SQL commands. If that was your misunderstanding, that should settle it.

One set of SQL functions working on remote data are called SQL-Passthrough, the term was already mentioned, it's SQL(String)Connect, SQLExec, SQLDisconnect, but also SQLPrepare, SQLSetProp and some more I forgot, but not SELECT-SQL, DELETE-SQL, UPDATE-SQL. They are merely titled that way to distinguish them from the xbase commands.

The SQL..() Passtrough functions all go to a SQL Server, and pass through sql commands to them 1:1. Your samples so far don't use special SQL, so you don't see the difference, but be warned: SQLExec() also does not translate VFP SQL to the SQL dialect of the remote database, merely the results are cursors, which are also DBF, so you can work on them in VFP the way you are used to. By the way, if you specify the VFPODBC driver in a SQLStringConnect() you may also work the same way on DBFs as remote data, but then you'd do without all the advantages of native DBF file access.

@Mike:
I tend to mix cursoradapters and sqlexec, just because you can do lot more via sqlexec, even though you also can use the CA class to send complex scripts, I use SQLExec mostly for commands executing at the server itself, you don't want to fetch mass data to a cursor and update on the VFP side.

CA offers events CA cursors lack, even though essentially you can use both cursors for remote updates, both techniques have their advantages and combined are not a compromise but add to each other. You can mix them very nice, as you can use CAs and SQL-Passthrough to use the same ODBC connection and also share Transactions.

Name a thing you can do with remote views, that you can't with a cursor adapter, there is none I can think of, a reason I see for remote views is, you're used to them and already have all your data access routines based on views.

Bye, Olaf.

 

[dbMark]

At my work we wrote up a class to handle all the common variations of opening, updating and closing an OBDC cursor. Using the class as a wrapper kept it simpler for our coding.

We used to use ADO until we realized that ADO was just a layer on top of ODBC. The Windows driver apparently only allows 64 connections at a time (we generally only make the calls from the server Windows account), at least that was the case for ADO with "too many client tasks" error, so if we're only reading data we immediately afterward close the connection.