how to fix parameters for sqlquery in lazarus

Question

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?

You can't parameterize a table name. You can only parameterize column values. — Ken White, Dec 30 '18 at 04:23
Ok, then how can I create tables on run time with names chosen thru an edit box? — Beniamin, Dec 31 '18 at 04:13
You shouldn't assume you can use back-quotes (`) in Sql. Delphi will forward them to your Sql backend, but whether, and in what contexts, the server may accept them is a different question and likely to be in the negative for most servers. — MartynA, Jan 12 '19 at 20:37

score 0 · Answer 1 · answered Jan 09 '19 at 08:53

You can create a stored procedure like this:

DELIMITER $$
CREATE PROCEDURE `create_my_table`(IN `table_name` TEXT)
BEGIN

SET @s=CONCAT('CREATE TABLE IF NOT EXISTS ',
  `table_name`,
  ' (
    `idnew_table` INT NOT NULL,  PRIMARY KEY (`idnew_table`)
  )
);

  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

and then pass any string (or parameter) when calling it:

CALL create_my_table("anything");

You can also see my answer here

how to fix parameters for sqlquery in lazarus

1 Answers1