Updating a date field in an external tabler via ODBC

[AndrewMozley]

I have an external database (in fact a NySQL database) to which I have established a connection with handle zhandle.

I would like to call SQLEXEC() from the VFP program to update a field Lastdate in table Mytable, record with ID 123. ID is the primary index.

The date which I wish to write to this field in Mytable is held in variable lcLastDate. I realise that this variable is not visible once the engine which interprets the SQLEXEC() command is doing its thing.

Would I need to have instructions something like this :

LOCAL lCommand
lCommand = "UPDATE MyTable SET Lastdate = " + ;
< some instruction to convert lcLastdate into a suitable character string > ;
WHERE Id = 123"
lnResult = SQLEXEC(zHandle, lCommand)

. . . . test to see if this update was successful.

I would be grateful to know what command I should use.

 

[bborissov]

Use parameters:

LOCAL lCommand
TEXT TO lCommand NOSHOW PRETEXT 15
     UPDATE MyTable SET Lastdate = ?m.ldLastDate
     WHERE Id = 123"
ENDTEXT
ldLastDate = DATE()
lnResult = SQLEXEC(zHandle, lCommand)

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

Sorry, I have copy and paste problem:

LOCAL lCommand
TEXT TO lCommand NOSHOW PRETEXT 15
     UPDATE MyTable SET Lastdate = ?m.ldLastDate
     WHERE Id = 123
ENDTEXT
ldLastDate = DATE()
lnResult = SQLEXEC(zHandle, lCommand)

Borislav Borissov
VFP9 SP2, SQL Server

 

[Mike Lewis]

Andrew,

The code you posted will work OK, but there is an easier way:

LOCAL lCommand, lcLastDate
lcLastDate = <a date in the format expected by the database>
lCommand = "UPDATE MyTable SET Lastdate = [b]?lcLastDate[/b] WHERE Id = 123"
lnResult = SQLEXEC(zHandle, lCommand)

Note the user of the question mark before lcLastDate. This tells SQLEXEC() that lcLastDate is a variable, and should be substituted for its contents.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The solutions given by Borislav and Mike are both equal and fine, if the destination database conforms to some ODBC standards.

Just to state the obviuos: Three tiers are involved here: 1. VFP, 2. the ODBC driver of the remote database and 3. the remote database server itself. First of all VFP tells ODBC to transport a date variable additional to the query string, the VFP date type is converted to ODBC date. This is forwarded to the remote database and it will understand and convert to it's internal date or datetime type from the ODBC date type of this transportation layer.

OK.

If it doesn't work, and there are surely some cases or configurations this may go wrong, knowing the literal format the remote database understands is essential. Eg in T-SQL you can use a format 'YYYYMMDD hh:mm:ss' independant on the MS SQL Server configuration. Your lCommand would then be something along the lines of "UPDATE MyTable SET Lastdate = '20130923';" Not you really use a string delimited with single quotes, this is autoconverted to date, as it's the correct format.

Borislav's and Mike's solution is to be preferred, if it works out, because in the same way you can transport all other types and it saves you from the harm of SQL injection, if you don't concatenate anything in the query string which could be broken by user input.

Bye, Olaf.

 

[Mike Lewis]

If it doesn't work, and there are surely some cases or configurations this may go wrong, knowing the literal format the remote database understands is essential. Eg in T-SQL you can use a format 'YYYYMMDD hh:mm:ss' independant on the MS SQL Server configuration. Your lCommand would then be something along the lines of "UPDATE MyTable SET Lastdate = '20130923';" Not you really use a string delimited with single quotes, this is autoconverted to date, as it's the correct format.

I believe that in MySQL (which is what Andrew is using), dates are expressed as strings in YYYY-MM-DD format (at least, that's how they are returned from a SELECT statement). So if lcLastDate contains YYYYMMDD, and you want to insert the literal string, you would need some code to insert the dashes. But presumably that wouldn't be a problem if you were using the question-mark syntax that Borislav and I suggested.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Fair enough, Mike

presumably that wouldn't be a problem

Yes, presumably. The more so, if it's MySQL. I got MySQL to work with dates this way.

But I also had problems and read threads about problems with SQL Servers, at least with the accuracy of milliseconds, there even should be a thread here. It may also depend on german vs english/us config, if using a syntax with dashes (if we talk of T-SQL again).

It's nice to know how several DBs expect certain types as literals, anyway.

In regard of literals in MySQL, see

http://dev.mysql.com/doc/refman/5.0/en/literals.html

In regard to dates it also accepts 'YYYYMMDD' strings and even 'YYYYMMDDHHMMSS' for datetimes, this differs with T-SQL, for example.

Also in regard of T-SQL both you and me are right,
see

http://technet.microsoft.com/en-US/library/ms180878(v=SQL.105).aspx#StringLiteralDateandTimeFormats

I prefer the non locale specific 'YYYYMMDD HH:MM:SS' format and would even more like the total machine readable format without any spacing or colons, as MySQL accepts it. It's a matter of taste, of course.

And I admitted right from the beginning, the parameterized syntax with ? is to be preferred, if it works. So simply try first, Andrew. I don't criticize this solution, I just offer a fallback and evangelize the knowledge about some DB specifics like literals for each SQL dialect and DB you work with. In worst cases you should also be able to work with SQL Server Management Studio or MySQL Workbench or phpMySQLAdmin or whatever tool for further DBs with native syntax without using VFP via SQLExec, and there it's quite essential knowledge to be able to write a statement with literal values.

Bye, Olaf.