[Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

[Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
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,


--
Bo Berglund
Developer in Sweden

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
On Mon, 26 Aug 2019 21:53:38 +0200, Bo Berglund via lazarus
<[hidden email]> 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.

>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,
>

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


--
Bo Berglund
Developer in Sweden

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list


On Mon, 26 Aug 2019, Bo Berglund via lazarus wrote:

> 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?

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.

>
> 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...

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.
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
On Mon, 26 Aug 2019 23:39:47 +0200 (CEST), Michael Van Canneyt via
lazarus <[hidden email]> 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....


--
Bo Berglund
Developer in Sweden

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list


On Tue, 27 Aug 2019, Bo Berglund via lazarus wrote:

> On Mon, 26 Aug 2019 23:39:47 +0200 (CEST), Michael Van Canneyt via
> lazarus <[hidden email]> 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.

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.
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
On Tue, 27 Aug 2019 07:51:16 +0200 (CEST), Michael Van Canneyt via
lazarus <[hidden email]> wrote:

>> So the database server is not really a choice I can make.
>
>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.
>

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).


--
Bo Berglund
Developer in Sweden

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
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

On 8/27/19 12:36 AM, Bo Berglund via lazarus wrote:

> On Tue, 27 Aug 2019 07:51:16 +0200 (CEST), Michael Van Canneyt via
> lazarus <[hidden email]> wrote:
>
>>> So the database server is not really a choice I can make.
>>
>> 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.
>>
>
> 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).
>
>
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list


On Tue, 27 Aug 2019, AB via lazarus 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.
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list
On Tue, 27 Aug 2019 09:36:02 +0200, Bo Berglund via lazarus
<[hidden email]> 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
Developer in Sweden

--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list
On 26/08/2019 8:53 pm, Bo Berglund via lazarus 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
look at tiOPF. It 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

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list
On 26/08/2019 10:39 pm, Michael Van Canneyt via lazarus 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
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] How to connect to MariaDB/mySql database from fpc/Lazarus?

Free Pascal - Lazarus mailing list
In reply to this post by Free Pascal - Lazarus mailing list
On 27/08/2019 3:22 pm, AB via lazarus 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

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp
--
_______________________________________________
lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus