Lazarus

Free Pascal => Database => Topic started by: Beniamin on December 31, 2018, 05:36:14 am

Title: sqlquery table name as parameter
Post by: Beniamin 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?

Title: Re: sqlquery table name as parameter
Post by: HeavyUser on December 31, 2018, 05:48:04 am

Quote from: Beniamin 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?

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.

Title: Re: sqlquery table name as parameter
Post by: Beniamin 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!

Title: Re: sqlquery table name as parameter
Post by: lucamar on December 31, 2018, 12:24:24 pm

Small example using Format():

Code: Pascal [Select][+]

sqlquery2.SQL.AddText(
  Format(
    'CREATE TABLE `oferte`.%s (`idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`));',
    [client.text]));

Title: Re: sqlquery table name as parameter
Post by: dsiders on December 31, 2018, 01:29:20 pm

Quote from: Beniamin on December 31, 2018, 08:34:51 am

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

Title: Re: sqlquery table name as parameter
Post by: Beniamin on January 05, 2019, 11:58:40 am

Thank all of you!

Title: Re: sqlquery table name as parameter
Post by: Beniamin on January 05, 2019, 12:30:21 pm

I tried this format idea and it worked as a charm!
Thanks again! :D :D :D