Error passing table name value as parameter in TSQLQuery

[vladvons]

SQLQuery.SQL.Add('SELECT  *  FROM  :InTableName t1 WHERE  Table_id = 1'); //OK
SQLQuery.Params.ParamByName('InTableName').AsString   := TableName; //OK
SQLQuery.Active := true; // Syntax error near $1 t1 (Line 4)

Code: [Select]

1 SELECT
2  *
3 FROM
4   :InTableName t1
5 WHERE
6  Table_id = 1

I suppose ':InTableName' is ignored by parser in this case and treated as empty string.

How to pass parameter into :InTableName?

[BigChimp]

http://forum.lazarus.freepascal.org/index.php/topic,22176.msg130458.html#msg130458

... though your code does seem ok at first glance! What db are you using?

[vladvons]

Lazarus 1.1.99 pre + FPC 2.6.2 + PostgreSQL

[BigChimp]

Hmm, sqlite program below, compiled with fpc x64 trunk r25583, Windows

shows:

An unhandled exception occurred at $0000000100043F9D:
EDatabaseError: near "?": syntax error
  $0000000100043F9D line 2131 of fcl-db/src/base/db.pas
  $000000010003E6E6 line 166 of fcl-db/src/sqldb/sqlite/sqlite3conn.pp
  $000000010003EC89 line 246 of fcl-db/src/sqldb/sqlite/sqlite3conn.pp
  $000000010003F057 line 359 of fcl-db/src/sqldb/sqlite/sqlite3conn.pp
  $0000000100038B3F line 871 of fcl-db/src/sqldb/sqldb.pp
  $0000000100038C15 line 887 of fcl-db/src/sqldb/sqldb.pp
  $000000010003B09D line 1548 of fcl-db/src/sqldb/sqldb.pp
  $000000010003BC72 line 1878 of fcl-db/src/sqldb/sqldb.pp
  $000000010004674E line 403 of fcl-db/src/base/dataset.inc
  $0000000100047721 line 955 of fcl-db/src/base/dataset.inc
  $0000000100047C31 line 1080 of fcl-db/src/base/dataset.inc
  $000000010003AF4D line 1516 of fcl-db/src/sqldb/sqldb.pp
  $00000001000497C3 line 2041 of fcl-db/src/base/dataset.inc
  $0000000100001A10 line 50 of sqlitetest.lpr
  $0000000100001B36 line 64 of sqlitetest.lpr
  $000000010001549F line 215 of system.pp
  $000000010001629F line 482 of system.pp

Code: [Select]

program sqlitetest;

{$mode objfpc}{$H+}

uses {$IFDEF UNIX} {$IFDEF UseCThreads}
  cthreads, {$ENDIF} {$ENDIF}
  Classes,
  SysUtils,
  sqldb,
  sqlite3conn;

const
  TestTable = 'testt'; //name used for created table
var
  Conn: TSQLite3Connection; {*REPLACE WITH RELEVANT CONNECTION *}
  Tran: TSQLTransaction;
  Q: TSQLQuery;
begin
  Conn := TSQLite3Connection.Create(nil); {*REPLACE WITH RELEVANT CONNECTION *}
  Tran := TSQLTransaction.Create(nil);
  Q := TSQLQuery.Create(nil);
  try
    Conn.HostName := '';
    Conn.UserName := 'SYSDBA';    //shouldn't hurt for sqlite
    Conn.Password := 'masterkey'; //shouldn't hurt for sqlite
    Conn.DatabaseName := 'sqlitetest.sqlite';
    Conn.Transaction := Tran;
    Q.DataBase := Conn;
    Conn.Open;

    try
      Tran.StartTransaction;
      Q.SQL.Text := 'CREATE TABLE ' + TestTable + '(x INTEGER PRIMARY KEY ASC, Table_id integer, z integer);';
      Q.ExecSQL;
      Tran.Commit;
    except
      Tran.RollBack; // assume table already exists
    end;

    Tran.StartTransaction;
    Q.SQL.Text := 'INSERT INTO ' + TestTable + '(Table_id,z) values(1,42);';
    Q.ExecSQL;
    Q.SQL.Text := 'INSERT INTO ' + TestTable + '(Table_id,z) values(2,5);';
    Q.ExecSQL;
    Tran.Commit;

    Tran.StartTransaction;
    Q.SQL.Text := 'select * from :InTableName tl WHERE Table_id=1 ';
    Q.Params.ParamByName('InTableName').AsString := TestTable;
    Q.Open;
    Q.Last; //force recordcount update
    writeln('recordcount: ' + IntToStr(q.RecordCount));
    Q.Close;
    Tran.Commit;

    Conn.Close;
  finally
    Q.Free;
    Tran.Free;
    Conn.Free;
  end;
  writeln('Program complete. Press a key to continue.');
  readln;
end.


[LacaK]

AFAIK when preparing statement there can be used parameters, but not for table name. (but for values)
Because statement is precompiled f.e. object names should be known in time of preparation.

[BigChimp]

@Lacak: mmmm, yes, and presumably (just guessing) various dbs do have extensions to that that do allow substituting table names for params?
Read a bit about dynamic SQL in MS SQL Server but that doesn't sound very safe. Also don't know what Postgresql (which the OP uses) would use.

[vladvons]

1) LacaK you are right.
Object names should be known in time of preparation.
So such statement is unavailable in TSQLQuery.

Database accsessing component from Devart for such reason has TUniQuery.MacroByName() method.
UniQuery1.MacroByName('InTableName').Value := TableName;
(note that variable in SQL should have a '&' prefix)

Code: [Select]

1 SELECT
2  *
3 FROM
4   &InTableName t1
5 WHERE
6  Table_id = 1

hope in near feature we will get it from FPC team

2)
BigChimp how do you get such readable application calls stack presentation?
I couldt find in Lazarus

Code: [Select]

An unhandled exception occurred at $0000000100043F9D:
EDatabaseError: near "?": syntax error
  $0000000100043F9D line 2131 of fcl-db/src/base/db.pas
  $000000010003E6E6 line 166 of fcl-db/src/sqldb/sqlite/sqlite3conn.pp
  $000000010003EC89 line 246 of fcl-db/src/sqldb/sqlite/sqlite3conn.pp
  .......


[BigChimp]

Laz+FPC x64 on Windows compiled with
fpcopt="-gw2 -gl -O-1"
lazopt="-gw2 -gl -O-1"

[vladvons]

where ere placed 'fpcopt' and 'lazopt'?

in bin\i386-win32\fpc.cfg or in Makefile of root package directory ?
 

[BigChimp]

In the make invocation (make all, IIRC).
I just use fpcup of course :
eg. something like
fpcup64 --inifile="mysettings.ini" --inisection=win64full

mysettings.ini:

Code: [Select]

; example settings for fpcup
; use e.g.
; fpcup --inifile=settings.ini --inisection=win64full
; to compile win64 fpc/laz

[general]
; win32 quick: update only
; a full fpcup run must have been done before (e.g. win32full)
; does not update packages
fpcdir=c:\development\fpctrunk
fpcurl=http://svn.freepascal.org/svn/fpc/trunk
lazdir=c:\development\lazarus
primary-config-path=c:\development\lazarussettings
fpcopt="-gw2 -gl -O-1"
;lazopt="-gw2 -gl -O-1 "
;-dHEAPTRC_WINDOW
fpcuplinkname=""
lazlinkname=Laz32FPCTrunk
binutilsdir=c:\development\fpcbootstrap
fpcbootstrapdir=c:\development\fpcbootstrap
noconfirm=true
keeplocalchanges=true

skip=crosswin32-64

[win32full]
; Full download & compile for win32.
; Will create win32=>win64 cross compiler, and
; the 32 bit IDE.
; Uses FPC trunk instead of the default
fpcdir=c:\development\fpctrunk
fpcurl=http://svn.freepascal.org/svn/fpc/trunk
lazdir=c:\development\lazarus
primary-config-path=c:\development\lazarussettings
fpcopt="-gw2 -gl -O-1"
;lazopt="-gw2 -gl -O-1 "
; -dHEAPTRC_WINDOW
fpcuplinkname=""
lazlinkname=Laz32FPCTrunk
binutilsdir=c:\development\fpcbootstrap
fpcbootstrapdir=c:\development\fpcbootstrap
keeplocalchanges=true
noconfirm=true
fpspreadsheet=0
synapsetrunk=0
ljgridutils=0
brookframework=0

[win64full]
; Run this with the fpcup 64 bits program for Windows
; It's meant to generate a native 64 bit environment
; Uses FPC trunk instead of the default
fpcdir=c:\development\fpctrunk
fpcurl=http://svn.freepascal.org/svn/fpc/trunk
lazdir=c:\development\lazarus64_fpctrunk
primary-config-path=c:\development\lazarus64settings_fpctrunk
fpcopt="-gw2 -gl -O-1"
lazopt="-gw2 -gl -O-1"
fpcuplinkname=""
lazlinkname=Laz64FPCTrunk
binutilsdir=c:\development\fpcbootstrap64
fpcbootstrapdir=c:\development\fpcbootstrap64
noconfirm=true
keeplocalchanges=true
fpspreadsheet=1
synapsetrunk=1
ljgridutils=1
brookframework=1