sqlite database for administration
lazarus / 16-Databases

----------------------------------------------------------------------
JLommen · 2018-03-27 15:26

Hi all,

The last couple of weeks I have been struggling with the creation of
an administration program for a friends private company. The purpose
of this program is to connect to an SQLite database, show the data of
the people using a dbgrid and be able to add or delete files. However,
I struggle to find a good guide and started using many different
guides to get a result. I don't seem to find a solution on how to
insert files into the database using Lazarus. The methods I tried are
shown below. ( excuse me for my variables I am Dutch and for the
enormous lines.)

SQLquery1.SQL.Add(Format('Insert into
IZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode
,Plaats,gsm,telnr,emailadress,aantal_behandelingen)
values("%s","%s","%s","%s","%s","%s","%s","%d","%d","%s","%d")',
[Edit1.text, Edit2.text, [/font]Edit3.text,Edit10.text,Edit4.text,Edit
5.text,Edit6.text,Edit7.text,Edit8.text,Edit9.text,Edit11.text]));[/i]

SQLquery1.SQL.Add('Insert into IZHdata(Relatie_code,Achternaam,Voornaa
m,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_be
handelingen) values('+QuotedStr(Edit1.Text)+','+QuotedStr(Edit2.Text)+
','+QuotedStr(Edit2.Text)+','+QuotedStr(Edit10.Text)+','+QuotedStr(Edi
t4.Text)+','+QuotedStr(Edit5.Text)+','+QuotedStr(Edit6.Text)+','+Quote
dStr(Edit7.Text)+','+QuotedStr(Edit8.Text)+','+QuotedStr(Edit9.Text)+'
)');

This is my first time on the forum so excuse me if I have created my
post a bit messy.
Do you know a solution to this problem or could help me with a decent
guide on SQLite and Lazarus?
Thank you for your help.

sincere,
JLommen

----------------------------------------------------------------------
taazz · 2018-03-27 16:31

I do not understand the problem exactly. To make things a bit more
clear you do not append commands to an existing query each query can
handle only one command as a generic rule there are occasions that
mutiple commands can be executed but its a bit down the road for you,
so use sqlQuery1.SQL.Text := .........
after a command has been set to the query you need to other call open
if the command returns data (select) or execute if the command does
not return any data (delete, update, insert etc) and at the end of
your processing you need to call commit to persist the changes to the
database.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sqlquery1.Append;sqlquery1.FieldByName('Relatie_code').asstring :=
Edit1.Text;....sqlquiery1.applyupdates;sqlquery1.commit; 
Be warned the code above was keyed in directly on the browser and not
tested if you can't make it work post an error message so we can see
what went wrong.

----------------------------------------------------------------------
JLommen · 2018-03-27 18:40

Hi Taazz and thank you for your fast reply.

I have tried to understand what you said and I have come up with the
following code.

 SQLite3Connection1.DatabaseName:='Database1.sqlite';
 SQLite3Connection1.Transaction:=SQLTransaction1;
 SQLTransaction1.Database:=SQLite3Connection1;
 SQLQuery2.Database:=SQLite3connection1;
 SQLQuery2.Transaction:=SQLTransaction1;


 SQLite3Connection1.Connected := True;
 SQLQuery2.SQL.Clear;
 SQLQuery2.open;


 SQLQuery2.SQL.Add('Insert into IZHdata(Relatie_code,Achternaam,Voorna
am,Geboortedatum,Straat,Postcode,Plaats,gsm,telnr,emailadress,aantal_b
ehandelingen) values(:Relatie_code,:Achternaam,:Voornaam,:Geboortedatu
m,:Straat,:Postcode,:Plaats,:gsm,:telnr,:emailadress,:aantal_behandeli
ngen');
 SQLQuery2.Params.paramByName('Relatie_code').AsString := Edit1.Text;
 SQLQuery2.Params.paramByName('Voornaam').AsString := Edit2.Text;
 SQLQuery2.Params.paramByName('Achternaam').AsString := Edit3.Text;
 SQLQuery2.Params.paramByName('Geboortedatum').AsString :=
Edit10.Text;
 SQLQuery2.Params.paramByName('Straat').AsString := Edit4.Text;
 SQLQuery2.Params.paramByName('Postcode').AsString := Edit5.Text;
 SQLQuery2.Params.paramByName('Plaats').AsString := Edit6.Text;
 SQLQuery2.Params.paramByName('gsm').AsInteger := Edit7.Text;
 SQLQuery2.Params.paramByName('telnr').AsInteger := Edit8.Text;
 SQLQuery2.Params.paramByName('emailadress').AsString := Edit9.Text;
 SQLQuery2.Params.paramByName('Aantal_behandelingen').AsInteger :=
Edit11.Text;
 SQLQuery2.ExecSQL;
 SQLTransAction1.Commit;
 SQLQuery2.Close;

It doesn't seem to recognize the integer as an integer. Could you also
check if I have done the rest of the code correctly?

Again thanks for your help.

JLommen

----------------------------------------------------------------------
taazz · 2018-03-27 23:12

well not what I was going for but close enough. I assume that
sqlQuery2 is not linked with any DB controls and its there to only
execute the commands, in that case you do not need call open at all.
as for the last parameter asinteger you need to keep in mind that
Tedit only handles strings so you to convert it to an integer eg
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQLite3Conn
ection1.DatabaseName:='Database1.sqlite'; SQLite3Connection1.Transacti
on:=SQLTransaction1; SQLTransaction1.Database:=SQLite3Connection1; SQL
Query2.Database:=SQLite3connection1; SQLQuery2.Transaction:=SQLTransac
tion1;   SQLite3Connection1.Connected :=
True; SQLQuery2.SQL.Clear;SQLQuery2.SQL.clear; //<------ make sure
that only one command is in the sql.  SQLQuery2.SQL.Add('Insert into I
ZHdata(Relatie_code,Achternaam,Voornaam,Geboortedatum,Straat,Postcode,
Plaats,gsm,telnr,emailadress,aantal_behandelingen) values(:Relatie_cod
e,:Achternaam,:Voornaam,:Geboortedatum,:Straat,:Postcode,:Plaats,:gsm,
:telnr,:emailadress,:aantal_behandelingen'); SQLQuery2.Params.paramByN
ame('Relatie_code').AsString :=
Edit1.Text; SQLQuery2.Params.paramByName('Voornaam').AsString :=
Edit2.Text; SQLQuery2.Params.paramByName('Achternaam').AsString :=
Edit3.Text; SQLQuery2.Params.paramByName('Geboortedatum').AsString :=
Edit10.Text; SQLQuery2.Params.paramByName('Straat').AsString :=
Edit4.Text; SQLQuery2.Params.paramByName('Postcode').AsString :=
Edit5.Text; SQLQuery2.Params.paramByName('Plaats').AsString :=
Edit6.Text; SQLQuery2.Params.paramByName('gsm').AsInteger :=
Edit7.Text; SQLQuery2.Params.paramByName('telnr').AsInteger :=
Edit8.Text; SQLQuery2.Params.paramByName('emailadress').AsString := Ed
it9.Text; SQLQuery2.Params.paramByName('Aantal_behandelingen').AsInteg
er := strtoint(Edit11.Text); SQLQuery2.ExecSQL; SQLTransAction1.Commit
; SQLQuery2.Close; that should get you going.

----------------------------------------------------------------------
JLommen · 2018-03-28 15:06

Thanks again for your help,

With debugging no mistakes are found but when I fill in the textboxes
and press the button I get an error message saying
'sqlite3connection1: input incomplete'. When searching this on google
I found no answers to this problem.

I'm still in search of a good guide in order to learn this properly. A
functioning example program would be very nice.

Sincere,
JLommen

----------------------------------------------------------------------
Hansvb · 2018-03-28 20:11

I think that line 12 is wrong. You are missing a parenthesis. Insert
into (...) values (.....). The last parenthesis is missing

----------------------------------------------------------------------
JLommen · 2018-03-29 07:22

You are a legend!

The code finally seems to work

Thanks, Taazz for the great help and of course Hansvb for the final
touch.

JLommen