ECPG insert into table name as a variable - Mailing list pgsql-interfaces

From Lee Kindness
Subject ECPG insert into table name as a variable
Date
Msg-id 16241.50701.544612.198427@kelvin.csl.co.uk
Whole thread Raw
In response to ECPG insert into table name as a variable  (Ryan Mooney <ryan@pcslink.com>)
List pgsql-interfaces
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
CURSORFOR 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
viaa 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"
whereasif 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
anddidn'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 > <-=-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-><-=-=-=-=-=-=-> 
 


pgsql-interfaces by date:

Previous
From: Michael Meskes
Date:
Subject: Re: ECPG insert into table name as a variable
Next
From: David Schweikert
Date:
Subject: ANNOUNCE: Gedafe 1.2.0