MySql to VFP cursor

[Claudio Costa]

Hi again.

Im trying to get the rows from an MySql Select to a VFP cursor but all I get is an result.

Im using VFP 9 and MySql ODBC 8.0 Ansi driver

My code:

SQLEXEC(gnConnHandle,'SELECT referencia FROM artigos','abc')

SELECT abc

browse

but what i get is this :

 

[Chriss Miller]

Well, you ignored what I said and just listened to Tore plus assume getting a connection handle already proves a driver works.

I'm serious here, the driver you use must be changed. You can of course create a DSN, but the final truth about a driver working is with actively working on data in all ways.

We invested quite some time in the threads I found and you disregard that all as irrelevant.

 

[Chriss Miller]

Here for reference:

Running SQLEXEC on MySQL Database -- Continued... - Microsoft: FoxPro

Hello All, In reviewing a prior thread (https://www.tek-tips.com/viewthread.cfm?qid=1829136), it seems as though I have a very similar dilemma. The primary difference is that I'd prefer not to utilize outdated ODBC connectors due to vulnerability concerns. I'll quickly recap below: MySQL...

www.tek-tips.com

Mysql Connection String using latest driver - Microsoft: FoxPro

Hi all I am using Mysql as backend database for my applications the Mysql ODBC Connector version 3.51 (32 bit) is installed on my system and I give the connection string as below connection_string="DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + m_server + ";port=3306;UID=" + m_uid + ";PWD=" +...

www.tek-tips.com

 

[Pieter Koemans]

Hi again.

Im trying to get the rows from an MySql Select to a VFP cursor but all I get is an result.

Im using VFP 9 and MySql ODBC 8.0 Ansi driver

My code:

SQLEXEC(gnConnHandle,'SELECT referencia FROM artigos','abc')

SELECT abc

browse

but what i get is this :

In addition to Chris, you can also try the Mariadb 3.1 driver, works fine with VFP and i think released in 2023

 

[Chriss Miller]

Yes, I pointed out MariaDB Drivers may work, too in the other thread.

Im using VFP 9 and MySql ODBC 8.0 Ansi driver

Since you say that the question now is which one. Assuming you indeed installed 8.0.33 the question that's most relevant is what data type the referencia column of the artigos table is and what you get from SELECT Count(*) from artigos.

 

[Chriss Miller]

By the way: It doesn't suffice to install the correct driver. If you still use SQLCONNECT('WebBrowser', 'root', 'mysql') and didn't define a new DSN or changed the configuration of the WebBrowser DSN you already have to use the 8.0.33 driver, then you still get the same erroneous behavior. How is your DSN configured?

 

[Claudio Costa]

The connection is ok , I can insert rows in the mysql table.

Its just the Select that is not given de results.

 

[Tore Bleken]

I don't use MySql myself, so I am not quite sure about the syntax. Could it be that you need to add a Where clause? Something like

SELECT referencia FROM artigos Where 1=1

 

[Chriss Miller]

he connection is ok , I can insert rows in the mysql table.

Well, when you get the results you show it's a sign VFP can't cope with the driver. I already told you that getting a connection isn't proof of the driver working for VFP. In the threads I pointed out the testing was including queries, not just inserts. Besides that, query results like you show that have data types of fields in them were typical results of driver versions that don't work. You seem to think in black/white, a driver eiuther works or not. It's not the first time drivers are known for only working partly, there's a long "tradition" of varchar(max) fields some MS SQL Swerver drivers output as C(0) fields, which is a field type you can't even create in VFP itself and other problems like date type rpoblems.

So it's a strong sign you're still using a wrong driver version. With the correct driver a simple query like SELECT * FROM table could only fail on very exotic data types. That's why you were asked about that. I add to that, a problematic data type would actually error and not give such strange results.

Besides that, you didn't answer anything, so I think I'll just leave the thread, as I can only help you with further information you don't seem to be willing to provide. Good luck with getting your code working.

 

[Claudio Costa]

Thanks for your help Miller , I appreciate it.

 

[tomk3]

If it does not work with another driver :
there may be a problem if "references" is a varchar(max) (in MS-SQL, not sure if its called different in Mysql) type .
AFAIK VFP decides which type to use on first record.

The following is for MS SQL you may adapt it and try in mysql :
If its big enough varchar(4095) instead of varchar(max) would work , a cast to text may help, too.
And I remember a function in vfp2c32 sqlexecex() which corrects ist, too.

 

[Chriss Miller]

The result Claudio Costa showed (click on the image, don't just look at the empty thumbnail) is similar to what AzNative showed in https://www.tek-tips.com/threads/running-sqlexec-on-mysql-database-continued.1830399/:

Running SQLEXEC on MySQL Database -- Continued... - Microsoft: FoxPro

Hello All, In reviewing a prior thread (https://www.tek-tips.com/viewthread.cfm?qid=1829136), it seems as though I have a very similar dilemma. The primary difference is that I'd prefer not to utilize outdated ODBC connectors due to vulnerability concerns. I'll quickly recap below: MySQL...

www.tek-tips.com

And that was solved by another driver version. The nature of this bad result is clearly not just one mangled datatype. It's utterly completely different from what was stored into the table, for example it's 6 records instead of just 3. The word "length" seems to be from a table structure desciption, se SQL Select gets mangled with DESCRIBE. Whatever.

I gave it one more try installing XAMPP and even got a newer MySQL driver to work, but only with a MariaDB Server. Why MariaDB? Because XAMPP swapped out MySQL with MariaDB in 2015 with XAMPP versions 5.5.

The combination of server/driver also plays a role. As long as I only have the minimal information from Claudio Costa, I can't help him find out, because I won't just check out any combination of server and drivers available. One thing is true, tough, you can a) access MariaDB with a MySQL ODBC driver, and b) access MySQL Server with MariaDB drivers. I can't provide a list of working combinations, though. Also don't know what you're stuck with or what you can choose. If you can choose freely I'd actually go for MariaDB.