Params
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2014-11-05 04:47

I tried to use TSQLQuery and a parameter to create a new schema in
Postgres, but failed.
Its almost as if sqlDB adds citationmarks around the parameter when
the query is executed and that makes it fail. But thats only a wild
guess.

So why didn't this code work?

Code: [Select]with Query do..

SQL.Text:= 'CREATE SCHEMA IF NOT EXISTS :schema_name AUTHORIZATION
sysdba';
ParamByName('schema_name').AsString:= 'SomeSchemaName';
ExecSQL;
(Transaction as TSQLTransaction).CommitRetaining;
(However, the schema was created fine if I used the 'infamous' Format
function)

Code: [Select]SQL.Text:= Format('CREATE SCHEMA IF NOT EXISTS %s
AUTHORIZATION sysdba',[ASchemaName]);
@BigChimp: I removed the duplicate example with Format from the wiki.

----------------------------------------------------------------------
Mike.Cornflake · 2014-11-05 05:31

Are you allowed to have Schema Names as Parameters?  I thought
Parameters were only for field values (which is why the "string" is
being quoted), they're not a general string search/replace option.

But I suspect what you need is something along the line of

Code: [Select]Format('CREATE SCHEMA IF NOT EXISTS %s AUTHORIZATION
sysdba', ['SomeSchemaName']);
I could be wrong...

----------------------------------------------------------------------
BigChimp · 2014-11-05 07:13

Quote from: kapibara@BigChimp: I removed the duplicate example with
Format from the wiki.
Thanks!

Quote from: Mike.Cornflake on November 05, 2014, 06:31:24 amAre you
allowed to have Schema Names as Parameters?  I thought Parameters were
only for field values (which is why the "string" is being quoted),
they're not a general string search/replace option.
Correct, they're only for fields.

----------------------------------------------------------------------
goodname · 2014-11-05 14:55

PostgreSQL has a function to escape identifiers such as table names or
schema names. It is called called PQescapeIdentifier. Unfortunately it
is not currently recognized by SQLdb.

http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-
ESCAPE-STRING