Compile for multiple versions of MySQL

[zakiwi]

Hi there,

My software needs to be able to interface with multiple versions of MySQL and MariaDB (depending on the target machine's configuration) ... is it possible to single compile my application such that it could instantiate the appropriate connection object (based on the version of MySQL or MariaDB that is present on the machine) ...

A quick look at the code indicates that this is not possible, but I thought I'd ask before doing something drastic.

Cheers

zakiwi

[BigChimp]

Yes, but you do know that you can compile against a certain version of the client which can then communicate with various mysql versions?

Newer versions of Lazarus (don't know if it is in stable versions yet) have a db library chooser component that allows you to load the required library (before setting up the connection).

[zakiwi]

OK ... so how would you suggest that I should try to achieve my goal?

Should I try to make the software capable of using the correct connection object based on the client library version, or should I ship a specific version of the client library with my program or are there other ways??

[BigChimp]

I would ship it with a certain library version which you have tested against the relevant servers/know are compatible with those server versions.

As for the other option - thinking out loud: you could look into using a http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnector.html
and casting that to the relevant tmysqlbla object when required.

[zakiwi]

This is quite a good idea and I did look into it, but it seems that it can only connect to one version of mysql .... which defeats the point.

How safe would it be to use the 5.5 Client Library against a 5.0 or 5.1 database ... I'm also concerned about the legality issues with respect to the distribution of the client library given Oracle's direction with MySQL.

[BigChimp]

1. Can't really comment. "it" is a bit vague, especially without any indication of what code you are using.

2. I have no idea about compatibility. Ask Oracle.

3. Regarding the legality: don't really understand what you're saying. Have client distribution conditions changed?

As for Oracle's direction with mysql: I wouldn't use mysql myself if I could help it. Nothing wrong with Firebird, Postgresql or sqlite...

[mirce.vladimirov]

I've never been faced to this issue but anyway I was often thinking about it, so i have something in my mind that could work. You could do something like this :
When application starts for a first time since installed do a "try loop" with different connection types, on succesefull connect create a text file or a sqlite DB which stores the connection type.
Next time when the application is startedfirst look at this information and take the proper connection type.
Now it's not over yet, because each TSQLQuery that you use must have the TSQLQuery.connection:=XYZ  thats the part which might need more coding depending on complexity of your application.

[BigChimp]

@mirce.vladimirov:

Now it's not over yet, because each TSQLQuery that you use must have the TSQLQuery.connection:=XYZ  thats the part which might need more coding depending on complexity of your application.

That's why using tsqlconnector instead of a specific mysql connector may be a better idea.

A simple example of this approach (although it doesn't have loading of various dlls, and it uses TSQLConnection instead of TSQLConnector) can be found in this test program:
https://bitbucket.org/reiniero/smalltools/src
directory pasql

[Leledumbo]

How safe would it be to use the 5.5 Client Library against a 5.0 or 5.1 database

TOTALLY UNSAFE (well, if it's at all possible)

[zakiwi]

Sorry ... I seem to have been a bit unclear in my comments. 

1. Can't really comment. "it" is a bit vague, especially without any indication of what code you are using

Sorry ... "it" referred to your suggestion of considering TSQLConnector.

TOTALLY UNSAFE (well, if it's at all possible)

This is what scares me.

I am only interested in connecting to MySQL or MariaDB in the context of this discussion ...

It seems to me that you can only compile one of:

   TMySQL55Connection = Class(TConnectionName);
   TMySQL51Connection = Class(TConnectionName);
   TMySQL50Connection = Class(TConnectionName);

into your application, so there is no chance of instantiating the "correct" object for the machine in question as the compiler directives around these objects (in mysqlconn.inc) makes them mutually exclusive ...

I don't really understand why these were made mutually exclusive when these units were designed.

If there is no way to do this (connect to any one of MySQL 5.0, MySQL 5.1 or MySQL 5.5), then I'm inclined to duplicate the functionality of these units into my own units in a way that is NOT mutually exclusive ... but I would prefer not to as I loose the benefit of any updates.

[jack616]

can the connection class be compiled into a dll ?

[LacaK]

It seems to me that you can only compile one of:

   TMySQL55Connection = Class(TConnectionName);
   TMySQL51Connection = Class(TConnectionName);
   TMySQL50Connection = Class(TConnectionName);

into your application, so there is no chance of instantiating the "correct" object for the machine in question as the compiler directives around these objects (in mysqlconn.inc) makes them mutually exclusive ...

Why do you think so ?

When you put into your uses clause:
mysql50conn, mysql51conn, mysql55conn
you can instantiate then:
TMySQL50Connection, TMySQL51Connection, TMySQL55Connection
as you want.

[Leledumbo]

It seems to me that you can only compile one of:

   TMySQL55Connection = Class(TConnectionName);
   TMySQL51Connection = Class(TConnectionName);
   TMySQL50Connection = Class(TConnectionName);

into your application, so there is no chance of instantiating the "correct" object for the machine in question as the compiler directives around these objects (in mysqlconn.inc) makes them mutually exclusive ...

Did you even see a {$define} to match those {$ifdef}? And did you realize that you can drop all those 3 components on the same form? Having the same include file doesn't mean you can't have all of them. The respective unit for each connection class includes the include file and define the respective symbol to reduce code duplication.

[zakiwi]

Sorry lads, I seem to have been too focused on mysqlconn.inc and have totally ignored it's interface units (mysql50conn.pas, mysql51conn.pas & mysql55conn.pas).

This is the section in mysqlconn.inc that confused me ... also, I don't have a GUI, so didn't notice the components on the component pallet ...

{$IFDEF mysql55}
  mysql55dyn;
 {$ELSE}
{$IFDEF mysql51}
   mysql51dyn;
{$ELSE}
  {$IfDef mysql50}
    mysql50dyn;
  {$ELSE}
    {$IfDef mysql41}
      mysql41dyn;
    {$ELSE}
      {$IFDEF mysql4} // temporary backwards compatibility for Lazarus
        mysql40dyn;
      {$ELSE}
        mysql40dyn;
      {$EndIf}
    {$EndIf}
  {$EndIf}
{$endif}
{$endif}       

and ...

{$ifdef mysql55}
    TMySQL55Connection = Class(TConnectionName);
    TMySQL55ConnectionDef = Class(TMySQLConnectionDef);
    TMySQL55Transaction = Class(TTransactionName);
    TMySQL55Cursor = Class(TCursorName);
  {$else}
    {$IfDef mysql51}
      TMySQL51Connection = Class(TConnectionName);
      TMySQL51ConnectionDef = Class(TMySQLConnectionDef);
      TMySQL51Transaction = Class(TTransactionName);
      TMySQL51Cursor = Class(TCursorName);
    {$ELSE}
      {$IfDef mysql50}
        TMySQL50Connection = Class(TConnectionName);
        TMySQL50ConnectionDef = Class(TMySQLConnectionDef);
        TMySQL50Transaction = Class(TTransactionName);
        TMySQL50Cursor = Class(TCursorName);
      {$ELSE}
        {$IfDef mysql41}
          TMySQL41Connection = Class(TConnectionName);
          TMySQL41ConnectionDef = Class(TMySQLConnectionDef);
          TMySQL41Transaction = Class(TTransactionName);
          TMySQL41Cursor = Class(TCursorName);
        {$ELSE}
          {$IFDEF mysql4} // temporary backwards compatibility for Lazarus
            TMySQLConnection = Class(TConnectionName);
            TMySQL40ConnectionDef = Class(TMySQLConnectionDef);
            TMySQLTransaction = Class(TTransactionName);
            TMySQLCursor = Class(TCursorName);
          {$ELSE}
            TMySQL40Connection = Class(TConnectionName);
            TMySQL40ConnectionDef = Class(TMySQLConnectionDef);
            TMySQL40Transaction = Class(TTransactionName);
            TMySQL40Cursor = Class(TCursorName);
          {$EndIf}
        {$EndIf}
      {$endif}
    {$EndIf}
  {$ENDIF}                               

Anyway ... everyone get a chance to be a dumb ass from time to time ... this was just my chance ...

I'll have another look at this next week and see if I can get the application talking to all 3 versions of MySQL and MariaDB 5.5 ...

I'll report back.

Out of interest ... is there any intention of creating a MariaDB component set in the future now that they intend to go their own way and not keep in sync with the Mysql release cycle after 5.5?

[taazz]

you are missing the fact that the .inc file can be included in more than one .pas files and be compiled with different directives in different units, after all a unit is a pas file not the .inc which is "copied" (in lack of a better word) in each unit that uses it as is.