Starting MySQL server from Lazarus program
lazarus / 16-Databases

----------------------------------------------------------------------
Kyjan · 2009-09-25 19:58

Okay, guys, here is the next problem: I use MySQL database in a
program, but I don't want the MySQL server to run always. So, how can
I start the MySQL server when the program starts, and stop it, when
its quit. Thank you for every answer.

Kyjan

----------------------------------------------------------------------
Martin_fr · 2009-09-26 01:08

Depends on the OS, and on how clean you want do to this.

On windows mysql runs as a service, I have no idea, how to control
that, but if you go to msdn ....

On Linux you can start it as a child process. You do not need to start
it as a public mysql server, run a new instance just for your app,
using a different port, or using a local socket for communication.

Alternatively you can embed the whole server in your app
(http://dev.mysql.com/doc/refman/5.1/en/libmysqld.html).
However I don't think the headers for that will be available, so you
need to translate them yourself.

Also check what licensing applies to the different models.

----------------------------------------------------------------------
Kyjan · 2009-09-27 17:08

Thx for the help, Martin, it looks like I should use Firebird. My only
problem is that I can't post and read records to/from firebird
database. But I'm keep trying.

Kyjan

----------------------------------------------------------------------
alter · 2009-09-27 19:31

If only your application uses that database and database is used only
locally then you might want to try Sqlite

----------------------------------------------------------------------
Kyjan · 2009-09-28 12:11

alter, thanks for your reply, i will look after sqlite. Is it
complicated?

----------------------------------------------------------------------
alter · 2009-09-28 13:50

Nope, It's really easy to use. And you have TSqlite3Connection class
already implemented so you can use also things like TSQLQuery (no need
to know details about Sqlite itself). Also you will have to remember
to put proper dll file from http://www.sqlite.org/ in your application
folder (Windows) or install proper package (Linux). It's also possible
to use Sqlite with Mac but I have no experience with that.

----------------------------------------------------------------------
Kyjan · 2009-09-29 15:51

alter, i have been trying to use sqlite, but something is wrong.

i can create database and table, but i can't write datas in table. The
Query.Post gives me no error, it seems everything went fine. But when
I try to recieve datas from tables, it says it is empty.

I did it in the following way:
Code: [Select]Connection.DatabaseName:='try.dat';
Connection.Connected:=True;
Connection.Open;
Query.SQL.Text:='create table sampletable (' +
                        'id integer PRIMARY KEY NOT NULL,'+
                        'client_name text NOT NULL)';
Query.ExecSQL;

After then, I tried the following:
Code: [Select]Query.Fieldvalues['client_name']:='Mr. No Body';
Query.Post;Transaction.Commit;
As far as I know, the datas have to be in the table, but if i check it
with Firefox SQLite Manager, or try to use a
Code: [Select]Query.SQL.Text:='select * from sampletable';
i realize that nothing in the table. So, please, help!!!:(

----------------------------------------------------------------------
alter · 2009-09-29 16:58

Try using INSERT and UPDATE sql statements instead. Eventually you can
create TTable object and connect it to existing table in database

----------------------------------------------------------------------
Kyjan · 2009-09-30 08:25

Alter, I'm newbie in programming, especially in Lazarus and SQL. Can
you write me an example, which work correctly, please? I was trying,
but couldn't figure it out.
Thanks,
Kyjan

----------------------------------------------------------------------
alter · 2009-09-30 12:03

Code: [Select]  LCLIntf.EnterCriticalSection(DBCritSection);
  try
    if not Database.SQLQuery.Active then begin

    Database.SQLQuery.SQL.Text:=Sql;
    Database.SQLQuery.ExecSQL;
  end;
  finally
    LCLIntf.LeaveCriticalSection(DBCritSection);
  end;
end; If you don't have mutlithreaded application then you can skip
critical section. As sql values I use constants for example:
INSERT INTO lyrics (lyric, item_id) VALUES (''%s'', %s) and I use
Format on it. More examples of sql are pretty easy to find so it leave
it to you:-)
Also if you are using SELECT to retrieve data from table then you can
access it with TSQLQuery.Fields or better for example with:
SQLQuery.FieldByName('my_field').AsString. Note that after reading
whole record you must go to the next one manually in code with
SQLQuery.Next (there are also methods for previous, last and first
records).

This is how it is done with TSQLQuery. With that you can write easily
your own methods to quickly populate tables and present their contents
with actually any possible component since you can retrieve data in
actually any shape you like.

----------------------------------------------------------------------
Vincent Snijders · 2009-09-30 12:17

What happens if the lyric string contains a double quote. Better use
parameters. See also
http://wiki.lazarus.freepascal.org/Secure_programming#Injections

----------------------------------------------------------------------
LacaK · 2009-09-30 12:35

Quote from: Kyjan on September 29, 2009, 05:51:52 pm
I did it in the following way:
Code: [Select]Connection.DatabaseName:='try.dat';
Connection.Connected:=True;
Connection.Open;
Query.SQL.Text:='create table sampletable (' +
                        'id integer PRIMARY KEY NOT NULL,'+
                        'client_name text NOT NULL)';
Query.ExecSQL;

After then, I tried the following:
Code: [Select]Query.Fieldvalues['client_name']:='Mr. No Body';
Query.Post;Transaction.Commit;
You must first "open table", then insert row and also supply value for
primary key ...

Code: [Select][b]Query.SQL.Text:='select * from sampletable';
Query.Open;

Query.Append;
Query.Fieldvalues['id']:=1;[/b]
Query.Fieldvalues['client_name']:='Mr. No Body';
Query.Post;Transaction.Commit;

----------------------------------------------------------------------
Kyjan · 2009-09-30 18:01

LacaK, i copied what you wrote, but i'm still unable to insert row
into the table.

----------------------------------------------------------------------
LacaK · 2009-10-01 08:56

Yes it seems, that there is some kind of problem.

When I try open table i get error messages like:
"No such table: sampletable" (but GetTableNames on TSQLLite3Connection
object return "sampletable" and of course table of this name exists in
db)
or
"library routine called out of sequence"

(I am trying FPC 2.2.4 and 2.5.1 with latest sqlite3.dll)

Can anybody confirm that TSQLite3Connection (sqlDB) really works ?
Please some link to working example ?

TIA
-Laco.

----------------------------------------------------------------------
LacaK · 2009-10-01 13:01

Quote from: Kyjan on September 30, 2009, 08:01:14 pmbut i'm still
unable to insert row into the table.
It seems, that you must explicitly Commit at end of work ...

Connection.ExecuteDirect('insert into sampletable values(2,
''text2'')'); 
Connection.Transaction.Commit; //TSQLTransaction object is attached to
SQLite3Connection object

It seems, that Action property of TSQLTransaction object has absolute
no effect and is not handled in code ? Can somebody comment why ?

Also it seems, that TSQLQuery object does not parse SQL, so we get
read-only dataset without providing explicitly Insert,Update,DeleteSQL
...

Hm ...

----------------------------------------------------------------------
LacaK · 2009-10-02 11:59

Quote from: LacaK on October 01, 2009, 03:01:54 pmIt seems, that you
must explicitly Commit at end of work ...

Also it seems, that TSQLQuery object does not parse SQL, so we get
read-only dataset without providing explicitly Insert,Update,DeleteSQL
I was wrong here.

You must :
1. ApplyUpdates ... Query.ApplyUpdates
2. Commit Transaction ... Connection.Transaction.Commit;
Then your changes go into database.
HTH
-Laco.
P.S. it would be good document this (that you MUST ApplyUpdates and
Commit), for example here :
http://wiki.freepascal.org/SQLdb_Package#Using_the_SQLdb_Package

----------------------------------------------------------------------
Kyjan · 2009-10-04 10:58

Finally, I found the solution:

You can write in SQLite table in a following way:

Code: [Select]TSQLQuery.SQL.Text:='INSERT INTO tablename(client_name,
phonenumber) VALUES ("Mr. No Body", "123456789");
TSQLQuery.ExecSQL;
TSQLTransaction.Commit;

It work perfectly, and I haven't used any other SQL commands, like
SQL.Open, .Append, or something like that.

Thanks for the help for everyone!

Regards,
Kyjan