Mysql Connection String using latest driver

[ravicoder]

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=" + m_pwd + ";database=" + m_dbase + ";option=3"
the connection to mysql is made

(I have stored the above parameters in a text file for simplicity)

My problem is when I am upgrading to MySql ODBC connector 9.0 (64 bit) and I change the above connection string to
connection_string="DRIVER={MySQL ODBC 9.0 Driver};SERVER=" + m_server + ";port=3306;UID=" + m_uid + ";PWD=" + m_pwd + ";database=" + m_dbase + ";option=3"
It does not connect. I have created a system DSN using this driver and there it is showing connection successful

Any help or guidance on this is welcome

Also is there any other better database backend that can be used similar to mysql? if so what are the requirements for connection?

Thanks in advance
Ravi

 

[Chris Miller]

In the last thread184-1830399 on the topic of MySQL connectors AzNative tested MySQL 8.0.33 to be the latest driver that works with VFP.

As you have the choice of database, I'd recommend going the path of MariaDB, I didn't hear of problems connecting to that variation of the MySQL DB.

And whatever you choose, you need a 32bit driver for VFP to be able to use it at all. And an ANSI driver, not a Unicode driver. You can still store Unicode in the data, with the limitations of what a VFP frontend can handle, the driver is not limiting the character sets usable in the backend, but since VFP is a 32bit ANSI process, it can't handle neither a 64bit driver nor a Unicode driver, just like DECLARE- DLL can also only use the ANSI variations of the Windows API funtions and not the Unicode ones. For example when two variations of a function exist, like SetWindowTextA and SetWindowTextW, if you declare SetWindowText, you actually declare SetWindowTextA, not SetWindowTextW.

Those are the base limitations of VFP about what makes up a 32bit and an Ansi process.

MariaDB still is a drop in replacement for MySQL, i.e. mostly compatible with all the syntax. There is an extensive list of lists of differences (yes, a list of lists) at

https://mariadb.com/kb/en/mariadb-vs-mysql-features/

It Looks like overwhelmingly too much differences , but the list extends mainyl because it compares ever MariaDBV version with MySQL 8.0. It's understnadable they don't also list all higher MySQL versions like 8.1, 8.2, but I'd expect there to be a new section for comparisons vs the 9.0 version. The lists are from MariaDB, so expect a bias towards their Database, but you shouldn't be concerned about not finding the same level of documentation reference, support in forums, etc. Plus, when you start new, there shouldn't be compatibility concerns, anyway.

Chriss

 

[ravicoder]

Thanks Chris for the Advice and suggestions

I upgraded to mysql connector 8.0.33 and it is working with existing code and data. Was using 3.51 earlier and been wanting to Upgrade.

Regards
Ravi

 

[Chris Miller]

Okay, glad that helps.

I just wonder why you asked for other database backends. Okay, you asked about "similar to use" databases, but if you'd interpret that as usable by SQLExec function (SQL Passthrough, more generally) then that's all other databases. MariaDB would still be a logical migration path to go, as you're stuck at the 8.0.33 driver, even though that is a lot versions ahead from what you've been using so far.

What version is your MySQL backend?

Chriss