Correct Connection String VFP to MariaDB

[dylim]

Hi Guys,

I am trying to use MariaDB as an alternative to MySQL. So, I got MariaDB Server 11.4 installed on a test PC, also MariaDB ODBC 3.2.5.

I simply cannot seem to make it work!

This is the string I used:

lnHandle=SQLSTRINGCONNECT("Driver={MariaDB ODBC 3.2 Driver};Server=localhost;Port=3306;Database=MyDB;Uid=root;Pwd=12345;")

I also tried the following variations:

a) Driver={MariaDB ODBC 3.2 Driver}
b) Driver=MariaDB ODBC 3.2 Driver
c) Driver={MariaDB ODBC 3.0 Driver}
d) Driver=MariaDB ODBC 3.0 Driver
e) Driver={MariaDB ODBC 3.2.5 Driver}
f) Driver=MariaDB ODBC 3.2.5 Driver
g) Driver={MariaDB ODBC Driver}
h) Driver=MariaDB ODBC Driver

What is crazy is that, I used MySQL ODBC 8 (driver=MySQL ODBC 8.0 ANSI Driver), and it works like a charm!

Should I just use MySQL ODBC instead of MariaDB ODBC? (this seems very counter-intuitive though)

Thanks in advance!

 

[Chriss Miller]

I used the same counterintuitive combination of MySQL driver with MariaDB server and as it works, let it be counter-intuitive, it works. Anyway, I'll look into MariaDB drivers and see what works best there, too. Should be possible to find a pair that works, shouldn't it? The major issue could be bitness. Windows allows using drivers for both 64bit/32bit and shows some DSNs in the ODBC Adminstrator in both bitnesses, I think VFP is not happy about such CPU unspecific drivers, as one reason I can think of.

One more general thing: The Driver name specified in curly brackets in the connection string has to be is 1:1 as displayed in the ODBC Data Source Administrator (32bit), in the Drivers tab, name column. It doesn't mater, whether you use it to create/configure a system DSN, user DSN or no DSN, this lists the names by which drivers are known to Windows and so also, how they need to be specified in an ODBC connection string.

 

[zazzi]

If this can help here is my connection working perfectly on my Win11 local development MariaDB:

cConnectionString = "DRIVER={MariaDB ODBC 3.2 Driver};" ;
+ "SERVER=127.0.0.1;" ;
+ "PORT=3306;" ;
+ "OPTION=67108864;" ;
+ "UID=[put your userid here];" ;
+ "PWD=[put your password here];" ;
+ "database=[put your database here];" ;
+ "ENABLE_LOCAL_INFILE=1;"

 

[dylim]

I used the same counterintuitive combination of MySQL driver with MariaDB server and as it works, let it be counter-intuitive, it works. Anyway, I'll look into MariaDB drivers and see what works best there, too. Should be possible to find a pair that works, shouldn't it? The major issue could be bitness. Windows allows using drivers for both 64bit/32bit and shows some DSNs in the ODBC Adminstrator in both bitnesses, I think VFP is not happy about such CPU unspecific drivers, as one reason I can think of.

One more general thing: The Driver name specified in curly brackets in the connection string has to be is 1:1 as displayed in the ODBC Data Source Administrator (32bit), in the Drivers tab, name column. It doesn't mater, whether you use it to create/configure a system DSN, user DSN or no DSN, this lists the names by which drivers are known to Windows and so also, how they need to be specified in an ODBC connection string.

Kinda crazy don't you think?! During the days of MySQL ODBC 3.51, curly braces are required. But on MySQL ODBC 8.0, curly braces will cause it not to work!

Will try to use a DSN instead of the usual connection string I use.

 

[dylim]

If this can help here is my connection working perfectly on my Win11 local development MariaDB:

cConnectionString = "DRIVER={MariaDB ODBC 3.2 Driver};" ;
+ "SERVER=127.0.0.1;" ;
+ "PORT=3306;" ;
+ "OPTION=67108864;" ;
+ "UID=[put your userid here];" ;
+ "PWD=[put your password here];" ;
+ "database=[put your database here];" ;
+ "ENABLE_LOCAL_INFILE=1;"

zazzi,

The only difference with what I used are the following:

a) OPTION=67108864
b) ENABLE_LOCAL_INFILE=1

What are they for? Is this the reason my connection string failed coz I don't have these?

Thanks!

 

[zazzi]

zazzi,

The only difference with what I used are the following:

a) OPTION=67108864
b) ENABLE_LOCAL_INFILE=1

What are they for? Is this the reason my connection string failed coz I don't have these?

Thanks!

They deal with the possibility to send to MariaDB multiple lines with TEXT....ENDTEXT and use the LOAD DATA LOCAL INFILE command to upload a csv file into a MariaDB database table.

See https://www.tek-tips.com/threads/se...end-through-text-endtext.1832146/post-7580826

 

[Chriss Miller]

The OPTION=x is a sum of bit flags with the ppower of two values 1,2,4,8,16..., every bit in that number means one option and they are all documented. The options MariaDB suports for MySQL compatibility are listed at https://mariadb.com/kb/en/about-mariadb-connector-odbc/

• OPTION: For MySQL Connector/ODBC compatibility. Aliases: OPTIONS. Here are used bits meaning:
  • 0(1) - Currently is not used
  • 1(2) - Tells connector to return the number of matched rows instead of number of changed rows
  • 4(16) - See NO_PROMPT
  • 5(32) - Forces use of dynamic cursor
  • 6(64) - Forbids the use of database.tablename.column syntax
  • 7(128) Allows [load-data-infile|LOAD DATA INFILE LOCAL]
  • 11(2048) - Tells connector to use compression protocol
  • 13(8192) - See NAMEDPIPE
  • 16(65536) - See USE_MYCNF
  • 21(2097152) - See FORWARDONLY
  • 22(4194304) - See AUTO_RECONNECT
  • 26(67108864) - Allows to send multiple statements in one query

So that's bit 26 only. Some of the options have their own names, like in your example ENABLE_LOCAL_INFILE, which compares to the bit 7 (2^7=128) of the options. So you might find a more speeaking equivaelent for OPTION=67108864 or could summarize this with OPTION=67108992 (which is 67108864+128).

 

[dylim]

They deal with the possibility to send to MariaDB multiple lines with TEXT....ENDTEXT and use the LOAD DATA LOCAL INFILE command to upload a csv file into a MariaDB database table.

See https://www.tek-tips.com/threads/se...end-through-text-endtext.1832146/post-7580826

Zazzu]i,

Do you mean to say, that it is a MUST that these parameters added to my connection string?

Thanks

 

[dylim]

The OPTION=x is a sum of bit flags with the ppower of two values 1,2,4,8,16..., every bit in that number means one option and they are all documented. The options MariaDB suports for MySQL compatibility are listed at https://mariadb.com/kb/en/about-mariadb-connector-odbc/

• OPTION: For MySQL Connector/ODBC compatibility. Aliases: OPTIONS. Here are used bits meaning:
  • 0(1) - Currently is not used
  • 1(2) - Tells connector to return the number of matched rows instead of number of changed rows
  • 4(16) - See NO_PROMPT
  • 5(32) - Forces use of dynamic cursor
  • 6(64) - Forbids the use of database.tablename.column syntax
  • 7(128) Allows [load-data-infile|LOAD DATA INFILE LOCAL]
  • 11(2048) - Tells connector to use compression protocol
  • 13(8192) - See NAMEDPIPE
  • 16(65536) - See USE_MYCNF
  • 21(2097152) - See FORWARDONLY
  • 22(4194304) - See AUTO_RECONNECT
  • 26(67108864) - Allows to send multiple statements in one query

So that's bit 26 only. Some of the options have their own names, like in your example ENABLE_LOCAL_INFILE, which compares to the bit 7 (2^7=128) of the options. So you might find a more speeaking equivaelent for OPTION=67108864 or could summarize this with OPTION=67108992 (which is 67108864+128).

Chriss,

Placing this in my connection string is a MUST for it to work?

Will you still continue on using MySQL ODBC 8 to connect to your MariaDB server sir if we get to have MariaDB ODBC to work?

Thanks

 

[dylim]

Chriss, Zazzi,

It still does not work, ever after placing OPTION=67108992. Rats.

 

[Chriss Miller]

I was only explainig what OPTION means and how it can be computed.

I don't want to be disrespectful, but when Driver={MariaDB ODBC 3.2 Driver} works for zazzi and not for you - are you sure you downloaded and installed this driver?
Could you simply post a screenshot of the Drivers tab of your ODBC Data Source Administrator (32bit)?

 

[Chriss Miller]

Will you still continue on using MySQL ODBC 8 to connect to your MariaDB server sir if we get to have MariaDB ODBC to work?

Yes, that's why I'll start now to try several drivers appropriate for my MariaDB Server and report back. I expect this will improve compatibility and performance with the MariaDB and it's simply the more intuitive way to use it. As said previously I only used the MySQL driver as I didn't know and examine the DB is actuall MariaDB. While Xampp has switched to MariaDB their admin Tool, the XAMPP control panel still talks of "MySQL" in its interface.

 

[Chriss Miller]

I found out my MariaDB Server version is 10.4.21, Google told me the recommended Maria DB ODBC Driver version for that server is 3.1 and this connection string worked for me:

DRIVER={MariaDB ODBC 3.1 Driver};SERVER=127.0.0.1;PORT=3306;OPTION=67108992;database=test;UID=root;PWD=...

One thing to mention is that the ODBC driver installation showed an option to force conncetions using older driver versions to use the new driver. I didn't check that, because you want to be in control what actual driver version to use and not force everything into the newest driver. From the perspective of security using the latest software and drivers is a good choice, but when you want to test which driver version works for you, it's a deadly decision, if the latest driver doesn't work for you.

For your info about driver downloads. Starting at the download page https://mariadb.com/downloads/connectors/connectors-data-access/odbc-connector/ after choosing Windows 32bit the driver versions to choose from are only 3.2.5-GA and 3.1.21-GA. I clicked "Show all files" instead. Got to https://dlm.mariadb.com/browse/odbc_connector/3.2.5/, clicked on "ODBC Connector" to get to the root directory in https://dlm.mariadb.com/browse/odbc_connector/ and drilled down to 3.1 drivers and finally 3.1.21 in https://dlm.mariadb.com/browse/odbc_connector/3.1.21/ of which I downloaded and installed mariadb-connector-odbc-3.1.21-win32

Oracle/MySQL has a better download section for archived drivers, but you also don't just download the latest driver and bang your head against the wall when it doesn't work.

As you stated you installed MariaDB Server 11.4 that may not help you, I'll see into upgrading to check whether 11.4 works with 3.2 drivers.

I haven't yet measured performance differences, but SQLEXEC() querying works fine. I tried a few special things like "SHOW VARIABLES" that actually do cause quirky results with the Oracle MySQL ANSI 8.0 driver but work fine with the MariaDB driver, so that's the first point where the MariaDB driver actually proves to be better compatible with its database, as can be expected.

 

[Chriss Miller]

I now installed MariaDB 11.4.5 and the MariaDB ODBC driver 3.2.5.

At installation of the server I set the root passsword, changed port from 3306 to 3307 and was able to use all that with this connection string:

DRIVER={MariaDB ODBC 3.2 Driver};SERVER=127.0.0.1;PORT=3307;OPTION=67108992;UID=root;PWD=...;

Notice the database=... part is missing, as the new server only has system databases yet. I could still do the SHOW VARIABLES query and confirm that connects me to the 11.4.5 server running in parallel to the old one.

What you need to adapt to your situation can be more than just the UID/PWD on top of server, database, port.

Otherwise, from my perspective everything works as it works for zazzi.

So overall, you must be doing something fundamentally wrong when things work for others, but not you.

 

[dylim]

Yes, that's why I'll start now to try several drivers appropriate for my MariaDB Server and report back. I expect this will improve compatibility and performance with the MariaDB and it's simply the more intuitive way to use it. As said previously I only used the MySQL driver as I didn't know and examine the DB is actuall MariaDB. While Xampp has switched to MariaDB their admin Tool, the XAMPP control panel still talks of "MySQL" in its interface.

MariaDB really tried to make it seamless to former MySQL users. If you log in using the command line, the keywork is still:

mysql -u root -p -h localhost

I was expecting it to be "mariadb" instead of "mysql".

Also, do you still use MySQL Workbench to access the MariaDb server? If not, what are you using? DbForge?

 

[dylim]

I was only explainig what OPTION means and how it can be computed.

I don't want to be disrespectful, but when Driver={MariaDB ODBC 3.2 Driver} works for zazzi and not for you - are you sure you downloaded and installed this driver?
Could you simply post a screenshot of the Drivers tab of your ODBC Data Source Administrator (32bit)?

Here you go sir. Notice that the screenshot shows Maria ODBC 3.1. I removed 3.2 and install 3.1, in the hope that it might work on a lower version.

I tried all possible combinations of the driver string, with curly braces, without, etc.