×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

mySQL ODBC ANSI connector 5.3.12

mySQL ODBC ANSI connector 5.3.12

mySQL ODBC ANSI connector 5.3.12

(OP)
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

RE: mySQL ODBC ANSI connector 5.3.12

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

RE: mySQL ODBC ANSI connector 5.3.12

(OP)
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)

RE: mySQL ODBC ANSI connector 5.3.12

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
WHERE bitfield 
intead of the more verbose
WHERE bitfield=1 
. 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

RE: mySQL ODBC ANSI connector 5.3.12

Hi Nigel,

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

Regards, Gerrit

RE: mySQL ODBC ANSI connector 5.3.12

(OP)

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




RE: mySQL ODBC ANSI connector 5.3.12

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close