×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

Running SQLEXEC on MySQL Database

Running SQLEXEC on MySQL Database

Running SQLEXEC on MySQL Database

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


RE: Running SQLEXEC on MySQL Database

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

RE: Running SQLEXEC on MySQL Database

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

RE: Running SQLEXEC on MySQL Database

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

RE: Running SQLEXEC on MySQL Database

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

RE: Running SQLEXEC on MySQL Database

Mike,

Quote (Mike Lewis)

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-odbc/en/connec...

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

RE: Running SQLEXEC on MySQL Database

Quote:

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

RE: Running SQLEXEC on MySQL Database

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:

CODE

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

Chriss

RE: Running SQLEXEC on MySQL Database

(OP)
Hi Chris and Mike,

Thank you so much for your feedback!

I have tried Option=16387

CODE -->

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

RE: Running SQLEXEC on MySQL Database

Abe,

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

RE: Running SQLEXEC on MySQL Database

Hi Abe D,

use "MySQL ODBC 3.51 Driver".

mJindrova

RE: Running SQLEXEC on MySQL Database

What's the version of the databse?

CODE -->

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

Also execute SELECT VARIABLES LIKE "%version%";

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

RE: Running SQLEXEC on MySQL Database

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

RE: Running SQLEXEC on MySQL Database

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

CODE

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

Chriss

RE: Running SQLEXEC on MySQL Database

Quote:

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

RE: Running SQLEXEC on MySQL Database

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

CODE

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

RE: Running SQLEXEC on MySQL Database

(OP)
Hi Chris,

I ran

CODE -->

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

I got version 5.7.44-48

Best regards,

Abe

RE: Running SQLEXEC on MySQL Database

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

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! Already a Member? Login


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