I am going to write a small application to access a mySql database from our website. The database has been exported to an SQL file and imported into a local MariaDB running on a Raspberry Pi4 on our network.

Now I want to access it from my Windows 7 environment via Lazarus (not inside the editor but from a program written using Lazarus).

I am confused about the SQLDB component tab in the IDE since it lists many connection components for MySql (7 in fact). Which one do I use?

I have used Lazarus and SQLDB for MsSQLServer with TMSSQLConnection, but that is a single component...
What version of the TMySqlxxConnection shall I use towards a MariaDB?

The displayed version is:

$ mysql --version
mysql Ver 15.1 Distrib 10.3.15-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Does this by any chance indicate one should use a TMySql52Connection? But then again that component does not exist on the palette...

A working fpc example towards a MariaDB database server would be valuable!

I run Lazarus 2.0.4 with Fpc 3.0.4 on Windows 7 and on Raspberry Pi4,

On Mon, 26 Aug 2019 21:53:38 +0200, Bo Berglund via lazarus wrote:
Do you need to install mySql on the client machine in order to connect with the MariaDB database on the server machine?

I have not installed any database engine except for MSSQLServer on my Windows7 computer.

What I google up does not end in a working way. Bad googling... Or people only post problems and not success.

TMySQL57Connection.

I have used Lazarus and SQLDB for MsSQLServer with TMSSQLConnection, but that is a single component... What version of the TMySqlxxConnection shall I use towards a MariaDB?

See above.

No, see above.

If I may suggest:

If you have a choice of database, choose something else. MariaDB/MySQLDB are the probably the worst choices you can make. Postgres, Firebird, SQLIte: all better choices, depending on your use case.

Michael.

Michael Van Canneyt wrote

If you have a choice of database, choose something else. MariaDB/MySQLDB are the probably the worst choices you can make. Postgres, Firebird, SQLIte: all better choices, depending on your use case.

Well it so happens that the database I need to access is a MySql one on our website. So I have created an export of it using phpmyadmin on the webserver and loaded this into a MariaDB server I have inbstalled on a RaspberryPi4 box.
Gonna use this while I am making the program to finally be deployed against the website database.
So the database server is not really a choice I can make.

In my past I worked with Delphi against MSSQLServer for many years using the ADODB connection in Delphi. Now no longer using Delphi and no longer at that company either.
So Fpc/Lazarus it is....

That's why I started with 'if you have a choice'.

MySQL by itself is not a very good database, but connecting to it from FPC/Lazarus is even worse.

Unfortunately, the ease of use of the LAMP stack made it very popular, and many are in your situation. I would not recommend it for new projects.

Michael.

Thanks,
I should maybe drop the idea of keeping a mirrored database in MySql format and instead use an MSSQLServer db as the mirror. I know for a fact that accessing that from Lazarus is pretty simple.

I will look into the possibility of importing the MySql dump into MSSQLServer, after all it is in SQL format and our tables are not really very complex either.

If that works I could add to the website php some kind handling of an operations log file where I save the SQL statements that are adding the data to the website db. Then I could retrieve these regularly and run against the backup database using a Lazarus/Fpc program on Windows (where the main database is hosted).

You can use the open source zeoslib ( http://zeoslib.sourceforge.net/ ).

It can access many databases, including mysql and mariadb since many many years.

There is nothing wrong with using mysql or mariadb for websites, it is not an accident that they are popular.

AB

AB wrote

You can use the open source zeoslib ( http://zeoslib.sourceforge.net/ ). It can access many databases, including mysql and mariadb since many many years.

So can SQLDB. The point is that it's a bad idea to use it.

There is nothing wrong with using mysql or mariadb for websites, it is not an accident that they are popular.

Depends on what you expect from a database.

MySQL is not really ACID.
It is also not really typesafe (you can store 0 in a date field), has no referential integrity by default and does not use transactions by default. You can add these but at the cost of performance.

So by default you can store any junk in it that you want: It just manages more or less structured files with an SQL syntax.

But if that is enough for you (and it is for most PHP programmers) then go right ahead, but my demands are a little higher...

Michael.

Bo Berglund wrote

I will look into the possibility of importing the MySql dump into MSSQLServer, after all it is in SQL format and our tables are not really very complex either.

I went over the SQL produced by a PhpMyAdmin export and changed quite a lot of formal things in it.

But in the end I managed to execute the script in the 2104 SQL Management Studio and put the data into a mirror of the website database on MSSQLServer.

So now I can work with that concerning the website backup of the data. And the SQLDb connection will be so much easier to implement.

Bo Berglund wrote

I am confused about the SQLDB component tab in the IDE since it lists many connection components for MySql (7 in fact). Which one do I use?

If you want to hide all complexity of database components, maybe take a abstracts away all database components and allows you to freely switch between backend databases and database components simply with a compiler define change and recompile. You can even switch from a client/server application to a n-tier application - again just with a compiler define changes and recompile. Not a single line of code needs to change. As a bonus, your application works 100% with objects instead of database tables and fields - thus making unit testing so much easier.

tiOPF homepage: http://tiopf.sourceforge.net
tiOPF help on FPC wiki: https://wiki.lazarus.freepascal.org/tiOPF

Regards,
Graeme

Michael Van Canneyt wrote

MariaDB/MySQLDB are the probably the worst choices you can make. Postgres, Firebird, SQLIte: all better choices, depending on your use case.

+1000

Regards,
Graeme

AB wrote

There is nothing wrong with using mysql or mariadb for websites, it is not an accident that they are popular.

I beg to differ - it is terrible!

https://forum.lazarus.freepascal.org/index.php/topic,20826.0.html

Regards,
Graeme