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.

 

[dylim]

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.

Yeah, Oracle/MySQL has a way way better downloads section. It was such a maze like experience for me to find the lower versions too.

So far, what is working for me, is MariaDb Server 11.4, and MySQL ODBC 8.

Have no luck using MariaDb ODBC 3.2.5 and 3.1.17.

I also use MySQL Workbench to access MariaDb, very annoying though as it tags the connection as "MariaDb - Warning, not supported". The "Client Connections" also do not work in my case. It merely creates a tab named Client Connections and then show a blank body.

Also, what GUI tool do you use to access MariaDb sir?

 

[Chriss Miller]

I can use MariaDB Server 10.4.21 with MariaDB ODBC Driver 3.1.21 and
I can use MariaDB Server 11.4.5 with MariaDB ODBC Driver 3.2.5.
In short: Since this works so well with these two stable current versions I assume the pairing of MariaDB with the appropriate ODBC driver work in general.

Don't know from where you got the advice to try ODBC version 3.1.17. With your 11.4.5 Server you should look for drivers in the 3.2 range, not lower, if 3.2.5 doesn't work for you try 3.2.1 to 3.2.4.

Are you sure you're providing the correct server, port, etc.? What's the error or problems you encounter?

I use VFP only, no Workbench or Alternative, but I used HeidiSQL in the past. There's not only MySQL Workbench, there are a lot of alternatives. MariaDB lists them here: https://mariadb.com/kb/en/graphical-and-enhanced-clients/

 

[dylim]

Chriss,

The reason why I used 3.1.1.7 was because of its date of issue. Maybe the older versions may work. haha

I have no uninstalled 3.1.17 and put back 3.2.5.

I put my connection parameters in an INI file, which I have been using for decades, like so:

driver={MariaDB ODBC 3.2 Driver}
server=localhost
port=1696
option=67108992
uid=root
pwd=12345
database=richline

My MySQL custom port is 1686, while I used MariaDb custom port is 1696.

Still doesn't work.

I created a System DSN and named it "richline", with the same parameter values. So my INI file contains:

dsn=richline

Still doesn't work. I also tried:

dsn={richline}

 

[Chriss Miller]

But on MySQL ODBC 8.0, curly braces will cause it not to work!

It's off topic by now, but I can't confirm that. It's {MySQL ODBC 8.0 ANSI Driver} or {MySQL ODBC 8.0 Unicode Driver}

Using MySQL ODBC 8.0 only the error is
Connectivity error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
By teh way, the curly brackets are optional, just help with any driver name that contains a semicolon and thus are used by default, usually as a norm. But a shortened driver name doesn't work.

Regarding MariaDB drivers: The installation of the 3.2.5 driver has replaced the 3.1 Driver. That's certainly just because all MariaDB ODBC drivers use the same DLL file name.

I now understand why they give the insaller option to auto-redirect all connection to use the latest driver, as there always is only one on the system at any time.
Well, as I tested 3.1.21 prior to upgrading both server and driver it still worked, respectively.

When you use a DSN, connect using SQLCONNECT("dsnname") instead of SQLSTRINGCONNECT("DSN=dsnname"), though both work and if one doesn't work, the other won't, too.
How are you reading in the ini file and combining it to a connection string? If you read in the ini with filetostr and use that as a connection string it won't work, of course not, the options need to be separated by semicolons and there are no linefeeds in a connection srting. For reading INIs you usually will use Windows API function GetPrivateProfileString. Using anything else, I don't see. MySQL itself could also get its configuration from a my.cnf file, but not ini. That a) needs to be server side and b) would require option bit 16, value 65536 (see above in the OPTION list and explanation), I doubt you mean that but actually have a Windows .INI file, that in itself might be something the ODBC Administrator - one of its DSNs - can be configured by, which I know nothing about, but if there is such a mechanism it surely is bound to a DSN name, which you would pick. So, well, if you could explain further how you use that INI, I might help get this going correctly. If I were you I'd double check if the INI mechanism you have built into your application is actually used and not overrriden by any other code directly using SQLSRTINGCONNECT somewhere else. If you change an ini that's actually not used, you can change it any way and it still won't be used. It helps to look at the connection string actually finally put together, make a breakpoint at the SQLSTRINGCONNECT line and see, if you even get there as you expect, or some other part of your application code runs.

Also, if you don't get a connection,i.e. SQLSTRINGCONNECT() returns -1, use AERROR(laError) and look into the laError array for the connection error message.