sqlquery table name as parameter

Forum > Database

(1/2) > >>

Beniamin:
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:

--- 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?

--- End quote ---
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:
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!

lucamar:
Small example using Format():

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---sqlquery2.SQL.AddText( Format( 'CREATE TABLE `oferte`.%s (`idnew_table` INT NOT NULL, PRIMARY KEY (`idnew_table`));', [client.text]));

dsiders:

--- Quote from: Beniamin on December 31, 2018, 08:34:51 am ---Can you give me some example with this "format" function.

--- End quote ---

There are several in the documentation. https://www.freepascal.org/docs-html/rtl/sysutils/format.html

Navigation

[0] Message Index

[#] Next page