Running SQLEXEC on MySQL Database 1

[Abe D]

Hello All,

I need your help with:

*----------
cQuery = 'SELECT * FROM database.lfx_wc_orders'

*---------- Connect to MySQL Server and import all records
gnConnHandle_CR = Sqlstringconnect("Driver={MySQL ODBC 8.0 Unicode Driver};Server=123.123.123.123;Database=database;Uid=abe;Pwd=d")

If gnConnHandle_CR <= 0
= Messagebox('Cannot make connection', 16, 'MySQL Connect Error')
Else
Wait Window "MySQL Server Connection made" Nowait

lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, cQuery , 'tempcursor')
endif

My problem is when I browse tempcursor all I see in the first record field1 varbinary, field2 -3, field3 255 and the rest of the records .Null. etc...

I have uploaded an image showing the cursor content.

Please help, can not figure out what the problem might be.

Thanks,

Abe

 

[Chris Miller]

Try the MySQL ODBC 8.0 ANSI Driver instead.

Don't fear the ANSI driver isn't capable to handle Unicode or UTF-8 data, this difference is not about the characterset of data, this is about whether the ODBC driver and the hosting process - your VFP executable or VFP9.exe - works with the Windows API functions for Unicode or Ansi, and VFP works with ANSI functions.



Chriss

 

[Abe D]

Hi Chris,

Thank you for your response.

I have tried

Driver={MySQL ODBC 8.0 ANSI Driver}​

The cursor had same .Null. data. exactly like the above image.

I'm not sure what to do.

Abe

 

[Abe D]

Using Driver={MySQL ODBC 8.0 ANSI Driver}

I have uploaded three images, one for MySQL table records and the other is its structure and last one VFP cursor

This what shows in VFP cursor

 

[Mike Lewis]

Abe D,

Welcome to the forum.

At first glance, I can't see anything wrong with the query you are sending to MySQL. In cases like this, a good first step is to figure out if the problem lies in the ODBC connection or in the back-end database. Have you tried running the same query directly in the MySQL console? If so, what result do you get?

Another point: I notice that, if you detect an error from SQLSTRINGCONNECT(), you correctly display a message to the user. But you then continue with the SQLEXEC(), which is clearly wrong. I'm not saying that would explain this particular problem, but it is something you need to deal with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

But you then continue with the SQLEXEC(), which is clearly wrong

No, look closer, again. The SQLExec is in the else branch, which means it's only executed when the connection handle is >0.

Abe,
I do agree with the other thing Mike says, though: The simplest reason you get this data is, it simply is this data. I also didn't believe it at first, so you're not alone thinking it points out a problem on the level of VFP or ODBC. But when you also have the ANSI driver available and it returns the same result, that's pointing out it actually is that data.

So, clearly, loooking at the data within the MySQL server would show whether this is the real data or a transfer problem.

Aside from switching to ANSI there are a lot of options you can configure on/off within the MySQL connection string, which are described here:

https://dev.mysql.com/doc/connector...ection-parameters.html#codbc-dsn-option-flags

Since you get .NULL. past some field of record 1, except for the id field which is empty for all records, that's still looking like a problem of how the result is encoded and then interpreted and converted to a VFP cursor on the level of VFP communicating with the ODBC driver. And that is influenced by these options. An example option that can make a difference is NO_BIGINT, as VFP integer fields are only 32bit, this option would enable to automatically turn MySQL BIGINT values to 32bit integer values, which of course helps to get the values that usually still start at 0 or 1 and stay within the 32bit range. And there are more general options like COMPRESSED_PROTO about which protocol is used between client/server and may turn the result into something different or wrong.

One option value I find people using from VFP is OPTION=3, which includes a flag about the detail difference of MKYSQL returning the number of matched rows vs the number of affected rows. It could be, the actual real result should only have one record and the empty further rows come from VFP misinterpreting the result number of records.

Don't just rely on OPTION=3, though. Build your own option value by going through the MySQL documentation and adding up the constant values of the table 5.3 where you think it will be applicable. Most of them are really just a choice, some of them might make a difference in working vs not working, though.

Chriss

 

[Mike Lewis]

The SQLExec is in the else branch, which means it's only executed when the connection handle is >0.

Quite right. My mistake.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Sorry, I was overlooking you already have a screenshot of the MySQL data as it is.

I see the table has some bigint fields, so the option NO_BIGINT should be used, that has a flag value of 16384. Combined (bitor) with 3 that would be 16387.
So see what changes, if you add "Option=16387" to the connection string:

gnConnHandle_CR = Sqlstringconnect("Driver={MySQL ODBC 8.0 ANSI Driver}[highlight #FCE94F];Option=16387[/highlight];Server=123.123.123.123;Database=database;Uid=abe;Pwd=d")

Chriss

 

[Abe D]

Hi Chris and Mike,

Thank you so much for your feedback!

I have tried Option=16387

gnConnHandle_CR = Sqlstringconnect("Driver={MySQL ODBC 8.0 ANSI Driver};Option=16387;Server=123.123.123.123;Database=database;Uid=abe;Pwd=d")

This is what I got

The id column has all ZEROs instead first field varbinary and the rest of the column empty

I'm not sure what to try next.

Abe

 

[atlopes]

Abe,

You may try two or three things to solve the issue or to gather more information:
[ul]
[li]Use a MariaDB ODBC connector instead (select Windows 32-bit as OS).[/li]
[li]Try to connect with the database through ODBC but using another application, like Excel.[/li]
[li]Use a database frontend administrator, such as HeidiSQL.[/li]
[/ul]

 

[mJindrova]

Hi Abe D,

use "MySQL ODBC 3.51 Driver".

mJindrova

 

[Chris Miller]

What's the version of the databse?

lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, 'SELECT VERSION()', 'tempcursor')
BROWSE

Also execute [tt]SELECT VARIABLES LIKE "%version%";[/tt]

And I agree with others to try other drivers, if you expect the latest driver to be able to address all MySQL Server versions you don't realize that MySQL was developed with cuts to downward compatibility, i.e. for sake of better performance/security the newer ODBC drivers use newer standards an older server version might not understand. While you get a connection you obviously see all kinds of side effects. I'm not surprised you still get .null.s, but when you don't get integers using the option NO_BIGINT, there's a systematic problem like using a too new diver could be. It would of course be a surprise and puzzling, if in the end you find out the database version is 8, but from what you report I strongly doubt that.

Chriss

 

[Abe D]

Dear ALL,

Hip Hip Hooray!!! I have tried what mJindrova suggested "MySQL ODBC 3.51 Driver" and IT WORKS .

Thank you so much for all your support and time to help me!

Blessings to all!

Abe

 

[Chris Miller]

Congrats, it's not a solution for all cases, though.

I'd be interested to see what's the MySQL version, so what do you get from

lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, 'SELECT VERSION()', 'mysqlversion')
lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, 'SELECT VARIABLES LIKE "%version%";', 'mysqlvariables')

Chriss

 

[Mike Lewis]

Hip Hip Hooray!!! I have tried what mJindrova suggested "MySQL ODBC 3.51 Driver" and IT WORKS .

Good to see that you have a solution.

We have a system here in the forum for flagging particularly helpful posts, like the one by mJindrova that you mentioned. You simply click on "Great post" in the bottom right corner of the post in question. This results in a red star being placed against the post within the thread, and also in the thread header on the main forum page.

There are two reasons to do this:

- So that anyone browsing the thread can see at a glance which replies were considered useful.

- Similarly, anyone with a similar problem will see at a glance which threads contain a possible solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Sorry, the command to list variables is SHOW, not SELECT. So it would be

lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, "SHOW VARIABLES LIKE '%vers%';", 'mysqlvariables')

Notice the likename pattern must be in single quotes, so I put the SQL command into double quotes.



Chriss

 

[Abe D]

Hi Chris,

I ran

lnsuccess_fetch_new = SQLExec( gnConnHandle_CR, "SHOW VARIABLES LIKE '%vers%';", 'mysqlvariables')

I got version 5.7.44-48

Best regards,

Abe

 

[Chris Miller]

Thanks for the feedback.

In the archive sectiopn of ODBC drivers at

https://downloads.mysql.com/archives/c-odbc/

I see the ODBC driver versions jump from 5.3.13 to 8.0.11, so there is no ODBC driver specific to 5.7.44-48 but the best match is the latest 5.x version, to which you may upgrade.

It's not that surprising the version 8 driver doesn't work with a version 5 database, though. Version 8 was released after MySQL was acquired from Oracle. There are major changes, also Wikipedia said version 6 was in the making and never released, the version 7 numbers were always used by the MySQL Cluster edition - well, a parallel branch of the product anyway. So version 8 was the successor of 5.7, you're addressig the last MySQL AB original and that might have personal preferences/political decisions.

It's not that surprising you can address a 5.7 database with an older but not a newer driver, as database ODBC drivers like those for MySQL just communicate between client and server. But with the major version change from 5 to 8 and the change from MySQL AB to Oracle there were obviously incompatible changes even in that aspect, so too new drivers fail to communicate correctly with older servers. Not differently enough that it breaks down completely, but differently enough to get weird results.

I can tell you that the MYSQL 8.0 driver also works fine with a MariaDB 10 version, so I'd say MariaDB could be used to modernize the backend at some point.

Chriss