mySQL ODBC ANSI connector 5.3.12

[Nigel Gomm]

hi all,

there's been a change in the mysql driver at version 5.3.12 such that

m.variable = .f.
sqlexec(nnnnn,"SELECT * from xyz where abc = ?m.variable")

returns no results (and it should).

if i change the query to "... where abc = 0" i get the results i expect.

it worked up until this last release (Jan 2019).

is there a parameter to the connection string i'm not seeing?

(this is connecting to a MariaDB server ).


nigel

 

[Mike Lewis]

I can't see why a change in the ODBC driver should affect the syntax of your SELECT command.

Are you sure that the preceding SQLCONNECT() or SQLSTRINGCONNECT() worked? Put another way, what is the value of nnnn in your example? If it is negative, then you have failed to connect to the server. That in turn would cause the SQLEXEC() to fail.

You can also use AERROR() to catch the error code returned from the server.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Nigel Gomm]

mike,

there's no error. i just get an empty result cursor.

it looks like the new driver is not parsing the ?parameter correctly.

Definitely connected.... i can fire off various selects through the same connection and only those that use a ? with a boolean were returning empty resultsets.

scope is not the problem.

for now i simply uninstalled the 3.12 driver and reinstalled the 3.10 to get the customer working again..... but obviously would like to get to the bottom of this.

i didn't do exhaustive testing yet.. so not yet sure it's only boolean ?parameters going wrong.

n

(p.s. by empty resultset i mean i'm seeing the correct columns for the table i'm querying... just no rows. so the connection and the 'select' is happening.... just the 'where' clause is compromised. Same code works fine with 3.10 of the ODBC connector)

 

[Olaf Doschke]

Might be a difference in booleans on the MySQL Maria DB side. Even if it worked that way, AFIAK in the MySQL worlds best fit for boolean is tinyint, the synonym BOOLEAN is translated as tinyint and like in so many languages .f. is 0 and .t. is 1.

Even MSSQLs bit lacks the capability to query just [pre]WHERE bitfield[/pre] intead of the more verbose [pre]WHERE bitfield=1[/pre]. Earlier driver behaviour likely was better and yes, there might be an option, but there are so many and I don't see something specific to VFPs bools.

You'll likely see what arrives in the Maria DB server when you follow Mikes advice to look into AERROR, because I don't assume you really have no error, you are just not checking for them, SQLEXEC by defauilt doesn't throw errors, even if the SQL doesn't work.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Gerrit Broekhuis]

Hi Nigel,

Just curious, has there been a recent update of MariaDB?

Regards, Gerrit

 

[Nigel Gomm]

Olaf,

didn't occur to me to check AERROR because SQLEXEC didn't return < 0 and i was seeing the cursor i expected (albeit with no rows...)

serverside the column in question is created as BIT(1). I did a quick test in another table querying a boolean column with the same non result.

But same version of my .exe connecting to same database on same server for several months stopped working this morning. Looks like this customer's network admin decided to be helpful and updated the mysql driver overnight. By the time it had gone through my first line support people and reached me i just had to get them backup and running asap.

i'll have to download that connector version onto a test PC and do some more testing.

n

 

[Nigel Gomm]

Gerrit,

i host my customers' data on my own (hosted) servers so control the MariaDB version. At present they are all 10.3.11 (which is not the latest available if that's your question).

n