ODBC specifications

[Chris Miller]

I wondered about the latest posts about MySQL driver problems, and I'm not sure if I'm even on the right track.

It's awkjward the MySQL driver version 8.0.33 works and 8.0.35 does not. One hint is that there's a gap, no version 8.0.34 wsa released, see

https://dev.mysql.com/doc/relnotes/connector-odbc/en/news-8-x.html

and specifically

https://dev.mysql.com/doc/relnotes/connector-odbc/en/news-8-0-34.html:

There was not a MySQL Connector/ODBC 8.0.34 release.

The release notes of the first non workingdriver 8.0.35 are only describing the bug fixes, not a drastic change that would render the driver incompatible, the missing 8.0.34 driver could point out a core problem with it due to such a milestone change, despite that usueally being worth to make it the first 8.1 driver.

There is one change that could exclude VFP from being able to use an ODBC driver, and now I'm getting to my question: There could have been a change from compliance with the ODBC 3.5 specification to the 4.0 specification, that is the version of the specification of ODBC itself. See Wikipedia, for example: [URL unfurl="true"]https://en.wikipedia.org/wiki/Open_Database_Connectivity#ODBC_specifications[22][/url]

ODBC 4.0 specifications of MS are at

https://github.com/microsoft/ODBC-Specification/blob/master/ODBC 4.0.md

As said, I'm not even sure if I'm on the right track, the track being this ODBC specification. If it changed from MySQL ODBC driver version 8.0.33 to 8.0.35 Oracle did a major change without any announcement of it.

The questions that poses to me are:
1. (obviously) Is that the change that makes later MySQL ODBC drivers imposssible to use with VFP?
2. Which ODBC specification is VFP capable to support?
3. How can you find out which specification an ODBC driver is compliant with?

The repository about the latest 4.0 specification is not helpful, I think.

There is a starting point with reference on ODBC driver

https://learn.microsoft.com/en-us/s...-programmer-s-reference?view=sql-server-ver15

Another one is at

https://learn.microsoft.com/en-us/s...dbc-driver-architecture?view=sql-server-ver15

and then from there the intersting point is Apüplication/Driver compatibility:

https://learn.microsoft.com/en-us/s...nd-driver-compatibility?view=sql-server-ver15

All these topics are located within the SQL Server documentation, I wonder whether they refelct general ODBC reference/documentation/specification and not only regarding SQL Server.

I don't only hope to find out why VFP can't use newer MySQL drivers, the issue could extend to any ODBC drivers, once a specification becomes the norm, which VFP does not support. A target and I think a bigger task would be to replace the ODBC layer of the VFP language and reimplement communication with ODBC drivers by usage of the API reference and replacing all SQL Passtruough functions with a 4.0 specification compliant variation of them, ie. starting at SQLConnect/SQLStringConnect making a connection in concordance with the 4,0 specifications. Maybe that would already be sufficient to make use of all further SQL Passtrough functios like SQLExec without needing to reimplement all of them.

All that said, it's not even clear whehter that's the problem and I still think it's very unlikely the MySQL driver series made such a leap from ODBC 3.5/3.8/whatever to 4.0 or any other leap of version that causes VFP to become incapable of using the driver. There's also lots of downward compatibility options that could allow usage of 4.0 drivers for an application (or runtime like the VFP9r.dll) only capable of a 3.x specification. One thing is clear cut from the age of VFPs runtime: ODBC 4.0 was intrduced 2017, VFP9 is from 2005/6, there's no chance VFP is compliant with the ODBC 4.0 specification, so that's a clear hurdle of VFP using most recent drivers that comply with a standard introduced 7 years ago and it makes it more and more probable of VFP being excluded from the ODBC interface to other RDBMS in need of some middleware that could bridge that gap.

Chriss

 

[mJindrova]

It's very strange because SQLTABLES() and SQLSCOLUMNS() (VFP) works prefectly,
but SQLEXEC() (VFP) return randomize data (at calls SQLGetData() (API))
Column PSCHEDNO is VARCHAR(30), db is MariaDB.


IMHO, it's a bug in MySQL ODBC 8.0 ANSI Driver (8.0.35/8.0.36) 32bit because MySQL ODBC 8.4 ANSI Driver 64bit works perfectly.



mJindrova

 

[Chris Miller]

I'm not usure testing MySQL ODBC Drivers with a MariaDB server is giving results about driver compatibility or server compatibility. The most recent thread also failed with the newest 9.0 driver.

How could you use a 64bit driver? Are you testing with VFPA? Or is it just a 64bit setup that includes 32bit drivers?

Chriss

 

[mJindrova]

I did test with VFPA 10.1 64 bit - "MySQL ODBC 8.4 ANSI Driver" is 64 bit only.

mJindrova

 

[Chris Miller]

Okay, that explains that.

Here's what happens in VFP9 with the 9.00.00.00 driver, which is only available in 64bit:

The DSN is visible from the 32bit ODBC Data-Source Administrator and also seen by SQLCONNECT(), but results in the architecture mismatch, which refers to the 32bit/64bit architecture differerence. And the last 32bit driver is version 8.00.37.

What you can find in release notes of the first 8.1 driver is that

MySQL Connector/ODBC 8.0.x exists to continue 32-bit support.

and

32-bit binaries are no longer built as of MySQL 8.1.

That doesn't say anything about whether the 8.0.x series will get new releases also for 9.0 server or higher. Since the ODBC driver is only a messenger between client and server, it can also connect to newer server versions, that would only become impossible when the driver actually contains the SQL engine, like it's for VFP ODBC drivers but that's just because VFP isn't a server and not the case for MySQL, MSSQL or other database servers.

I haven't tried, but you may even still use MySQL 9.0 Server with the 8.0.33 driver from VFP9. The server doesn't need to be the same bitness as the ODBC driver, that's not a problem. You can just be sure even when this works today, there will come a day you need a newer driver and then only third party drivers could help, or migrating to MariaDB, which offer their latest releases of ODBC drivers still for both 32bit and 64bit Windows.

You may also migrate to VFPA, as it seems. I'd still like to know about the exact specifications VFP9 can support, but thanks for the effort because that does not only concern MySQL but use of any ODBC drivers of a specification VFP can't support anymore.

Chriss

 

[Piotr Czekalski]

Sorry for small Off Topic.

There are more problems with VFP. For some of my clients working on Win10, the command "Locate For FieldName = Variable" sometimes works incorrectly. It suddenly stopped working properly after a Windows update. It should be replaced with the command "Locate For AliasTables.FieldName = Variable". I use the Locate for command with extensive search conditions. It can be replaced with the command "Copy For" or "Select * from Table Where...". In Windows 11, the "$" operators work incorrectly, for example X$"ABCD". Sometimes they work, sometimes they don't. I had to change the code to InList(X,"A","B","C","D"). Not only SQL drivers but also other things in VFP system start working incorrectly. If something is permanently wrong, sometimes it can be replaced with another command. During testing, you can catch it. The worst is when something works seemingly correctly but throws errors from time to time. In my case, a mistake in the "Locate" search statement caused a serious crash in my client's database.

Have a nice time, Piotr

 

[Chris Miller]

Piotr, for more visibility such a post should be in a new thread. I don't see why the behavior of a FOR clause only breaks for LOCATE and not for COPY TO, it's causing Rushmore optimiazation of the for clause and that's part of the SQL Engine, nothing of that should depend on Windows API. Well, you never know if part f the implementation of clauses or also operators like $ deep down call Windows API functions, but I can't believe this for $, really.

I've had bad behavior of VARTYPE() vs TYPE(), but it only was on one customers PC and it vanished pretty much, so I can contribute to such campfire stories.

I think the reason for PACK not to work stable since Vista could be tracked down to changes in behavior of the filesystem. But I don't see how FOR or the $ operator depend on OS level changes. Nevertheless, it's okay to be warned that such things could fail for whatever reason.

I just remember someone could tell me about a decade ago, if not even longer, which version of ODBC specifications VFP and the VFP runtime actually implement. It's surely not the 4.0 standard. If you think that upgrades in the ODBC specifications only concern the ODBC dll itself you underestimate how a part of the programming language you use, i.e. the SQL Passtrhough functions in the VFP language, make communication with the ODBC driver in a specific way that's not set in stone forever, but changes with ODBC specification changes. Some glitches are alreaday well known, for example VarChar(Max) fields being made C(0) fields in VFP result cursors with certain SQL Server ODBC drivers.

The ODBC specification on which an ODBC driver is based on is not communicated, everyne is just talking about the version of the ODBC driver itself, typically kept in synch with the database version, but that's not talking about the ODBC specification version.

What that means for ODBC is that in the long run you will rely on third parties filling in the need for "legacy ODBC spec" drivers. or fail to adress some newer versions of databases, which will then be something you may not be able to work around, or you will need a full replacement of the set of SQL functions with FLLs that implement the newer specifications for ODBC itself, i.e. adressing/communicating with an ODBC driver or turning the result ODBC returns in ODBC data types into VFP cursors. That's always given for people who thinks that's part of what the ODBC drivers do, it's done by part of the VFP runtime ODBC layer included in the implementation of SQLEXEC, for example, which in turn must also be used by remote views and Cursoradapter going through ODBC, not to talk of ADO and OLEDB Providers. At least the latter are less of a problem as the database communication focusses very much on ODBC.

Chriss