Writing to Mysql database
lazarus / 16-Databases

----------------------------------------------------------------------
Noobie · 2020-03-20 12:03

Maybe this is a stupid question but if somebody could help.

How do I write a variable into mysql database?

If I try to...
INSERT INTO table (column1, column2, column3, ...)
VALUES (1, 2, 3);
... everything works

but when I use variables like...
INSERT INTO table (column1, column2, column3, ...)
VALUES (a, b, c);
... I will get an error.

----------------------------------------------------------------------
lucamar · 2020-03-20 12:20

I'm not entirely sure (we use this format only for simple DB apps) but
I think you have to use a parameterized "query", like:
Code: [Select]INSERT INTO table (column1, column2, column3, ...)
VALUES (:a, :b, :c); and then add the values with something like:
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.Params.ParamByName('a').AsInteger := a;

----------------------------------------------------------------------
MacWomble · 2020-03-20 13:52

As
INSERT INTO table (column1, column2, column3, ...)
VALUES (a, b, c);
has to be a string it only works this way:
'INSERT INTO table (column1, column2, column3, ...)
VALUES ('+IntToStr(a)+','+IntToStr(b)','+IntToStr(c)+')';

So you can see, it's really better to use parameters like described
before.
This is also better for strings, where you have to insert "Text..."

I use classes for this and a sample is:

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      
 Self.Items[i].fIsChanged := False;        if Self.Items[i].fID = 0
then        begin {Insert}          DebugLn('  Datensatz wird neu
angelegt');          SQL.Clear;          SQL.Add('INSERT INTO
Artikel');          SQL.Add(            '(fk_artikelgruppe,
 art_nummer, art_herstellernummer, art_name, art_beschreibung,
art_datei)');          SQL.ADD('VALUES');          SQL.ADD(          
'(:fk_artikelgruppe,  :art_nummer, :art_herstellernummer, :art_name,
:art_beschreibung, :art_datei);');          Prepare;        end      
else        begin {Update}          DebugLn('  Datensatz ' +
IntToStr(Self.Items[i].fID) + ' wird aktualisiert');         
SQL.Clear;          SQL.ADD('UPDATE Artikel Set');         
SQL.ADD('fk_artikelgruppe := :fk_artikelgruppe,');         
SQL.ADD('art_nummer := :art_nummer,');         
SQL.ADD('art_herstellernummer := :art_herstellernummer,');         
SQL.ADD('art_name := :art_name,');          SQL.ADD('art_beschreibung
:= :art_beschreibung,');          SQL.ADD('art_datei := :art_datei'); 
        SQL.ADD('WHERE idartikel = :idartikel;');         
Params.ParamByName('idartikel').AsInteger := Self.Items[i].fID;      
end;        Params.ParamByName('fk_artikelgruppe').AsInteger :=      
  Self.Items[i].fIDArtikelgruppe;       
Params.ParamByName('art_nummer').AsString :=
Self.Items[i].fArtikelnummer;       
Params.ParamByName('art_herstellernummer').AsString :=         
Self.Items[i].fHerstellernummer;       
Params.ParamByName('art_name').AsString :=
Self.Items[i].fBezeichnung;       
Params.ParamByName('art_beschreibung').AsString :=
Self.Items[i].fBeschreibung;       
Params.ParamByName('art_datei').AsString := Self.Items[i].fDatei;    
   try          ExecSQL;          DebugLn('    Gespeichert !');      
except          On E: Exception do            debugln(' ' +
E.Message);        end;

----------------------------------------------------------------------
Noobie · 2020-03-21 09:49

Thanks guys! I'll try that.