pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedECPG insert into table name as a variable
3+ messages / 3 participants
[nested] [flat]
* ECPG insert into table name as a variable
@ 2003-09-23 21:12 Ryan Mooney <ryan@pcslink.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ryan Mooney @ 2003-09-23 21:12 UTC (permalink / raw)
To: pgsql-interfaces
I was doing testing on an app and wanted to make a table name a variable
so I can change it via a config file (instead of re-compile/relink).
like so:
EXEC SQL INSERT INTO :tname (host, point, type, cooked) VALUES (:host, :name, :type, :cooked);
This fails ecpg preprocess with "ERROR: syntax error at or near ":tname" whereas if the table
name is specified statically it works fine, like:
EXEC SQL INSERT INTO tname (host, point, type, cooked) VALUES (:host, :name, :type, :cooked);
When I tried hacking the resulting C code (as a test exercise) like:
{ ECPGdo(__LINE__, 0, 1, NULL, "insert into ? ( host , point , type , cooked ) values ( ? , ? , ? , ? )",
ECPGt_char,&(tname),(long)0,(long)1,1*sizeof(char),
ECPGt_char,&(host),(long)0,(long)1,1*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(name),(long)0,(long)1,1*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(type),(long)1,(long)1,1*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(cooked),(long)1,(long)1,1*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(starttime),(long)30,(long)1,30*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(interval),(long)30,(long)1,30*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
it got all funny and didn't work very well (well ok it didn't work at all):
pg.pgc:129 SQL Insert failed: Invalid statement name (null) in line 124.
So, I was wondering am I just missing some obvious way to do this, or
is this not supported at all?
Thanks
--
>-=-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-=-<
Ryan Mooney ryan@pcslink.com
<-=-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-=->
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ECPG insert into table name as a variable
@ 2003-09-24 07:25 Michael Meskes <meskes@postgresql.org>
parent: Ryan Mooney <ryan@pcslink.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Meskes @ 2003-09-24 07:25 UTC (permalink / raw)
To: Ryan Mooney <ryan@pcslink.com>; +Cc: pgsql-interfaces
On Tue, Sep 23, 2003 at 11:12:07AM -1000, Ryan Mooney wrote:
> EXEC SQL INSERT INTO :tname (host, point, type, cooked) VALUES (:host, :name, :type, :cooked);
This is not possible as of now, but of course could be implemented.
> When I tried hacking the resulting C code (as a test exercise) like:
>
> { ECPGdo(__LINE__, 0, 1, NULL, "insert into ? ( host , point , type , cooked ) values ( ? , ? , ? , ? )",
> ECPGt_char,&(tname),(long)0,(long)1,1*sizeof(char),
You need to specify the indicator here as this:
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(host),(long)0,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(name),(long)0,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(type),(long)1,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(cooked),(long)1,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,(starttime),(long)30,(long)1,30*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,(interval),(long)30,(long)1,30*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
>
> it got all funny and didn't work very well (well ok it didn't work at all):
> pg.pgc:129 SQL Insert failed: Invalid statement name (null) in line 124.
>
> So, I was wondering am I just missing some obvious way to do this, or
> is this not supported at all?
So far it is not, sorry.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 3+ messages in thread
* ECPG insert into table name as a variable
@ 2003-09-24 16:27 Lee Kindness <lkindness@csl.co.uk>
parent: Ryan Mooney <ryan@pcslink.com>
1 sibling, 0 replies; 3+ messages in thread
From: Lee Kindness @ 2003-09-24 16:27 UTC (permalink / raw)
To: Ryan Mooney <ryan@pcslink.com>; +Cc: pgsql-interfaces@postgresql.org, Lee Kindness <lkindness@csl.co.uk>
No, not like that. But it'd be nice...
No do what you want consider something along the lines of:
snprintf(l_cmd, sizeof(l_cmd),
"ALTER TABLE %s ADD COLUMN %s%d FLOAT4 NULL",
tables[t].table, tables[t].column, n);
EXEC SQL EXECUTE IMMEDIATE :l_cmd;
or, more useful:
else
snprintf(l_cmd, sizeof(l_cmd), "SELECT src.shot, %s FROM %s src WHERE %s IS NOT NULL AND channel = %d", attribute, table, nullattr, channel);
EXEC_SQL_BEGIN;
/* declare & open */
EXEC SQL PREPARE lofsdb_SelectAttr_stmt FROM :l_cmd;
EXEC SQL DECLARE lofsdb_SelectAttr_cur CURSOR FOR lofsdb_SelectAttr_stmt;
EXEC SQL OPEN lofsdb_SelectAttr_cur;
and then just treat the CURSOR as normal... But for INSERTs the first
EXECUTE IMMEDIATE will do.
L.
Ryan Mooney writes:
>
>
> I was doing testing on an app and wanted to make a table name a variable
> so I can change it via a config file (instead of re-compile/relink).
>
> like so:
> EXEC SQL INSERT INTO :tname (host, point, type, cooked) VALUES (:host, :name, :type, :cooked);
>
> This fails ecpg preprocess with "ERROR: syntax error at or near ":tname" whereas if the table
> name is specified statically it works fine, like:
> EXEC SQL INSERT INTO tname (host, point, type, cooked) VALUES (:host, :name, :type, :cooked);
>
> When I tried hacking the resulting C code (as a test exercise) like:
>
> { ECPGdo(__LINE__, 0, 1, NULL, "insert into ? ( host , point , type , cooked ) values ( ? , ? , ? , ? )",
> ECPGt_char,&(tname),(long)0,(long)1,1*sizeof(char),
> ECPGt_char,&(host),(long)0,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(name),(long)0,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(type),(long)1,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,&(cooked),(long)1,(long)1,1*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,(starttime),(long)30,(long)1,30*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
> ECPGt_char,(interval),(long)30,(long)1,30*sizeof(char),
> ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
>
> it got all funny and didn't work very well (well ok it didn't work at all):
> pg.pgc:129 SQL Insert failed: Invalid statement name (null) in line 124.
>
> So, I was wondering am I just missing some obvious way to do this, or
> is this not supported at all?
>
> Thanks
>
>
> --
> >-=-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-<>-=-=-=-=-=-=-<
> Ryan Mooney ryan@pcslink.com
> <-=-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-=->
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-09-24 16:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-23 21:12 ECPG insert into table name as a variable Ryan Mooney <ryan@pcslink.com>
2003-09-24 07:25 ` Michael Meskes <meskes@postgresql.org>
2003-09-24 16:27 ` Lee Kindness <lkindness@csl.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox