Running SQLEXEC on MySQL Database -- Continued...

[AzNative]

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 Server: 8.0.37-0ubuntu0.22.04.3
MySQL ODBC (Windows - 32 Bit): 8.0.37

MySQL Table (test): A simple example, but this issue seems to impact all tables regardless of data type.
Create SQL: CREATE TABLE test SELECT UUID() as id, CAST(1 AS DECIMAL(1,0)) as _row; INSERT INTO test SELECT UUID() as id, CAST(2 AS DECIMAL(1,0)) as _row;...
Select SQL: SELECT * FROM test;

VFP Commands:
aa=SQLStringConnect("Driver={MySQL ODBC 8.0 ANSI Driver};Server={host};Database=mysql;Uid={user};Password={password}")
SQLEXEC(aa,"SELECT * FROM test;")
browse

Additional Testing:
[ul]
[li]mariadb-connector-odbc-3.2.2-win32.msi works appropriately[/li]
[li]MySQL 8.0.33 works[/li]
[li]MySQL 8.0.35 does not work[/li]
[li]MySQL 8.0.36 does not work[/li]
[li]MySQL 8.0.37 does not work[/li]
[li]Excel works appropriately when connecting via MySQL ODBC 8.0.37. This leads me to believe it's how VFP is interpreting the data from MySQL.[/li]
[/ul]

Any guidance would be greatly appreciated.

 

[Chris Miller]

How did you install a MySQL database 8.0.37-0ubuntu0.22.04.3 on Windows?

Your code says it makes a connection to localhost, that by definition of how VFP works must be a computer running Windows OS, so the connection you want to make to an installation of MySQL on a Linux server, even if that's a virtual machine hosted on your PC, won't be localhost.

Any way you look at it, the setup seems wrong or odd and so lets take it from something that works - Excel. What connection do you use in Excel that makes it see data correctly?

Chriss

 

[AzNative]

Hi Chris,

MySQL Server is installed on Ubuntu, but the connection is being made from a Windows device using an ODBC connection. I hope that helps clarify.

 

[Chris Miller]

You edited your original post from Server=localhost to Server={host}, that makes more sense, but still doesn't make this better in itself.

So can you post the specific connection string Excel uses and/or compare for yourself that you do connect to the same database server at all?

Chriss

 

[AzNative]

You're correct, Chris. I edited the original post to state "{host}" to prevent any future misinterpretation by other users.

Yes, I'm able to connect via Excel using a DSN. However, using that same DNS in VFP I'm unable to connect. For example:

Excel Power Query connection string: Odbc.DataSource("dsn=TEST", [HierarchicalNavigation=true])
Excel Power Query Output:

VFP Commands:
aa=SQLCONNECT("TEST")
SQLEXEC(aa,"SELECT * FROM test;")
browse (Provides the same output as above)

I hope this helps.

 

[Chris Miller]

Can you show the definition of the DSN (minus anything secret, of course) within the ODBC Data Source Administrator (32 bit)?
Excel is also 32bit?


Chriss

 

[AzNative]

You bet.

Verifying DSN "TEST" is 32-bit:

Various default configuration settings within the MySQL ODBC connection:

 

[Chris Miller]

Interesting, the essential information is above all the details you screenshot.

Anyway, you connect to the mysql database within the database server instance? That's the main system database, create a new one

I'd give it a slight chance you get a desired result running all this:

lnH = SQLConnect("TEST")

=SQLExec(lnH,"Create Database `testfoxdb` ChHARACTER SET = latin1 COLLATE = latin1_general_ci")
=SQLExec(lnH,"Use `testfoxdb`")
=SQLExec(lnH,"Create Table `testfox` (`rownum` integer, `uuid` varchar(36))") 
=SQLExec(lnH,"Insert Into `testfox` Values (1,UUID()),(2,UUID()),(3,UUID())") 
=SQLExec(lnH,"Select * From `testfox`",'mysqldata') 
SQLDisconnect(lnH)
Browse

Chriss

 

[AzNative]

Quick Update:

I've further verified that I'm able to connect via VBA through Excel using both DSN and full connection string. As an example:

 

[AzNative]

Thank you, Chris.

I've ran the code provided and here is the output:

 

[Chris Miller]

The result should be 3 records with rownums 1,2,3 and 3 UUIDs, of course.
The hope was creating a database with latin1 character set would work best for VFP.

Looking into my details I use MySQL ODBC 8.0 Ansi Driver in version 8.00.32.00 from 10/12/2022

So it seems to confirm your observation about Ansi drivers not working from version 8.00.35 and higher. I doubt Oracle will care for that and the best thing you can do is use the latest driver working with VFP.

Chriss

 

[Chris Miller]

You could also try alternatives. Devart is doing ODBC drivers not only for deprecated products.

Here's their page for their MySQL ODBC driver and instructions for using it with VFP:

https://www.devart.com/odbc/mysql/integrations/mysql-foxpro-connection.html

Chriss

 

[AzNative]

Thank you for your help Chris!