Recent

Author Topic: Writing to Mysql database  (Read 503 times)

Noobie

  • Newbie
  • Posts: 3
Writing to Mysql database
« on: March 20, 2020, 01:03:48 pm »
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

  • Hero Member
  • *****
  • Posts: 3428
Re: Writing to Mysql database
« Reply #1 on: March 20, 2020, 01:20:40 pm »
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][+][-]
  1. SQLQuery1.Params.ParamByName('a').AsInteger := a;
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus/FPC 2.0.8/3.0.4 & 2.0.10/3.2.0 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

MacWomble

  • Jr. Member
  • **
  • Posts: 77
Re: Writing to Mysql database
« Reply #2 on: March 20, 2020, 02:52:55 pm »
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][+][-]
  1.        Self.Items[i].fIsChanged := False;
  2.         if Self.Items[i].fID = 0 then
  3.         begin {Insert}
  4.           DebugLn('  Datensatz wird neu angelegt');
  5.           SQL.Clear;
  6.           SQL.Add('INSERT INTO Artikel');
  7.           SQL.Add(
  8.             '(fk_artikelgruppe,  art_nummer, art_herstellernummer, art_name, art_beschreibung, art_datei)');
  9.           SQL.ADD('VALUES');
  10.           SQL.ADD(
  11.             '(:fk_artikelgruppe,  :art_nummer, :art_herstellernummer, :art_name, :art_beschreibung, :art_datei);');
  12.           Prepare;
  13.         end
  14.         else
  15.         begin {Update}
  16.           DebugLn('  Datensatz ' + IntToStr(Self.Items[i].fID) + ' wird aktualisiert');
  17.           SQL.Clear;
  18.           SQL.ADD('UPDATE Artikel Set');
  19.           SQL.ADD('fk_artikelgruppe := :fk_artikelgruppe,');
  20.           SQL.ADD('art_nummer := :art_nummer,');
  21.           SQL.ADD('art_herstellernummer := :art_herstellernummer,');
  22.           SQL.ADD('art_name := :art_name,');
  23.           SQL.ADD('art_beschreibung := :art_beschreibung,');
  24.           SQL.ADD('art_datei := :art_datei');
  25.           SQL.ADD('WHERE idartikel = :idartikel;');
  26.           Params.ParamByName('idartikel').AsInteger := Self.Items[i].fID;
  27.         end;
  28.         Params.ParamByName('fk_artikelgruppe').AsInteger :=
  29.           Self.Items[i].fIDArtikelgruppe;
  30.         Params.ParamByName('art_nummer').AsString := Self.Items[i].fArtikelnummer;
  31.         Params.ParamByName('art_herstellernummer').AsString :=
  32.           Self.Items[i].fHerstellernummer;
  33.         Params.ParamByName('art_name').AsString := Self.Items[i].fBezeichnung;
  34.         Params.ParamByName('art_beschreibung').AsString := Self.Items[i].fBeschreibung;
  35.         Params.ParamByName('art_datei').AsString := Self.Items[i].fDatei;
  36.  
  37.         try
  38.           ExecSQL;
  39.           DebugLn('    Gespeichert !');
  40.         except
  41.           On E: Exception do
  42.             debugln(' ' + E.Message);
  43.         end;
« Last Edit: March 20, 2020, 02:56:48 pm by MacWomble »
Mint 19.3 Cinnamon, FPC/ Lazarus Trunk 64Bit

Noobie

  • Newbie
  • Posts: 3
Re: Writing to Mysql database
« Reply #3 on: March 21, 2020, 10:49:44 am »
Thanks guys! I'll try that.

 

TinyPortal © 2005-2018