Recent

Author Topic: sqlquery table name as parameter  (Read 3068 times)

Beniamin

  • Newbie
  • Posts: 4
sqlquery table name as parameter
« on: December 31, 2018, 05:36:14 am »
Hello!

I'm trying to make a program in lazarus that should create a table in mysql.

Below the code I wrote:

sqlquery2.SQL.AddText('CREATE TABLE `oferte`.:id (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));');
sqlquery2.Params.ParamByName('id').asstring:=client.text;

When I launch this code, I got the following error:

    "You have an error into your sql syntax..."

I have tried:

sqlquery2.SQL.AddText('CREATE TABLE `oferte`.`test1` (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));');

The program is running as expected and the table is created into the database with no error.

I also wrote a code with param passing in sql for inserting data into a table and it worked fine.

The code that I run which is OK:

sqlquery1.sql.Clear;
sqlquery1.SQL.Text:='insert into stocpiese(denumire,stoc,furnizor,client,pretintrare,adaoscom,'+'cod,pretiesire, moneda ,schimbvalutar,datacumpararii)'+'values ( (:den),(:stoc),(:furn),(:client),(:pretin),(:adcom),'+'(:cod),(:preties),(:moneda),(:schimb),(:data));';
sqlquery1.Params.ParamByName('den').AsString:=denumire.text;
sqlquery1.Params.ParamByName('stoc').AsString:=stoc.text;
sqlquery1.Params.ParamByName('furn').AsString:=furnizorsele.text;
sqlquery1.Params.ParamByName('client').AsString:=clientsele.text;
sqlquery1.Params.ParamByName('pretin').AsString:=pretintrare.text;
sqlquery1.Params.ParamByName('adcom').AsString:=adcom.text;
sqlquery1.Params.ParamByName('cod').AsString:=codsele.text;
sqlquery1.Params.ParamByName('preties').AsString:=pretiesire.text;
sqlquery1.Params.ParamByName('moneda').AsString:='lei';
sqlquery1.Params.ParamByName('schimb').AsString:=edit3.text;
sqlquery1.Params.ParamByName('data').AsString:=FormatDateTime('dd-mm-yy', data+201);

if(MySQL57Connection1.connected=false)then MySQL57Connection1.connected:=true;
if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;
sqlquery1.ExecSQL;
SQLTransaction1.CommitRetaining;
sqlquery1.Open;                                                                   

I do not understand, am I missing something?

HeavyUser

  • Sr. Member
  • ****
  • Posts: 262
Re: sqlquery table name as parameter
« Reply #1 on: December 31, 2018, 05:48:04 am »
Hello!

I'm trying to make a program in lazarus that should create a table in mysql.

Below the code I wrote:

sqlquery2.SQL.AddText('CREATE TABLE `oferte`.:id (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));');
sqlquery2.Params.ParamByName('id').asstring:=client.text;

When I launch this code, I got the following error:

    "You have an error into your sql syntax..."

I have tried:

sqlquery2.SQL.AddText('CREATE TABLE `oferte`.`test1` (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));');

The program is running as expected and the table is created into the database with no error.

I also wrote a code with param passing in sql for inserting data into a table and it worked fine.

The code that I run which is OK:

sqlquery1.sql.Clear;
sqlquery1.SQL.Text:='insert into stocpiese(denumire,stoc,furnizor,client,pretintrare,adaoscom,'+'cod,pretiesire, moneda ,schimbvalutar,datacumpararii)'+'values ( (:den),(:stoc),(:furn),(:client),(:pretin),(:adcom),'+'(:cod),(:preties),(:moneda),(:schimb),(:data));';
sqlquery1.Params.ParamByName('den').AsString:=denumire.text;
sqlquery1.Params.ParamByName('stoc').AsString:=stoc.text;
sqlquery1.Params.ParamByName('furn').AsString:=furnizorsele.text;
sqlquery1.Params.ParamByName('client').AsString:=clientsele.text;
sqlquery1.Params.ParamByName('pretin').AsString:=pretintrare.text;
sqlquery1.Params.ParamByName('adcom').AsString:=adcom.text;
sqlquery1.Params.ParamByName('cod').AsString:=codsele.text;
sqlquery1.Params.ParamByName('preties').AsString:=pretiesire.text;
sqlquery1.Params.ParamByName('moneda').AsString:='lei';
sqlquery1.Params.ParamByName('schimb').AsString:=edit3.text;
sqlquery1.Params.ParamByName('data').AsString:=FormatDateTime('dd-mm-yy', data+201);

if(MySQL57Connection1.connected=false)then MySQL57Connection1.connected:=true;
if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;
sqlquery1.ExecSQL;
SQLTransaction1.CommitRetaining;
sqlquery1.Open;                                                                   

I do not understand, am I missing something?
1) ddl statements can not be parametrised. Keep in mind that parametrised queries must be supported by the server. The idea behind them is that the server will compile the query and optimize it so paramters can be changed with out degrading the execution speed.
2) the table name is mandatory for the compiler and optimizer to work.

The only think you can do is use the format function to complete the command on client side and then execute it on the server.

Beniamin

  • Newbie
  • Posts: 4
Re: sqlquery table name as parameter
« Reply #2 on: December 31, 2018, 08:34:51 am »
Can you give me some example with this "format" function.
I'm a beginner here and I don't catch every time the terms .

Thanks for your fast response!
« Last Edit: December 31, 2018, 08:37:55 am by Beniamin »

lucamar

  • Hero Member
  • *****
  • Posts: 1060
Re: sqlquery table name as parameter
« Reply #3 on: December 31, 2018, 12:24:24 pm »
Small example using Format():

Code: Pascal  [Select]
  1. sqlquery2.SQL.AddText(
  2.   Format(
  3.     'CREATE TABLE `oferte`.%s (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));',
  4.     [client.text]));
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 1.8.4/FPC 3.0.4 on:
(K)Ubuntu 12..16, Windows XP SP3 (Home/Prof.) and various DOS incarnations.

dsiders

  • Full Member
  • ***
  • Posts: 147
Re: sqlquery table name as parameter
« Reply #4 on: December 31, 2018, 01:29:20 pm »
Can you give me some example with this "format" function.

There are several in the documentation. https://www.freepascal.org/docs-html/rtl/sysutils/format.html
Lazarus 2.0 / FPC 3.0.4 / Windows 8.1 64-bit

Beniamin

  • Newbie
  • Posts: 4
Re: sqlquery table name as parameter
« Reply #5 on: January 05, 2019, 11:58:40 am »
Thank all of you!

Beniamin

  • Newbie
  • Posts: 4
Re: sqlquery table name as parameter
« Reply #6 on: January 05, 2019, 12:30:21 pm »
I tried this format idea and it worked as a charm!
Thanks again! :D :D :D