Thread: Questions about pq library and ecpg

Hi,

I'm a new subscriber of this mailing list and new user of postgresql, let me
first greet everybody reading this mail.
I have a couple of questions about libpq and ecpg.
Is it possible to accomplish a query on a table whose name is specified in a
variable? That is, something like the following:

strcpy(str, 'table');
result=PQexec(conn, "insert into str values('Rome', 10)");

I guess a code like the above one would not give the expected result..... Is
there a way to work that out?
I'm trying to use the ecpg preprocessor to pass arguments of a query in
variables, but I get a few errors.... Here's the piece of code that doesn't
work:

 exec sql begin declare section;
 char str[80];
 exec sql end declare section;
 strcpy(str, "squadre");
 exec sql connect 'prova';
 exec sql select * from :str;
 exec sql commit;
 exec sql insert into :str values('Samp', 10);
 exec sql commit;
 exec sql disconnect 'prova';

ecpg gives an error message saying: parse error in line 10, that is line "exec
sql insert into :str values('Samp', 10);". If I replace var str with the name
of the table, it works correctly instead. The syntax seems to be correct so I
have no idea about where the error is.
The last question for now is the following: how can I display the content of a
table using ecpg instructions? The statement "exec sql select * from :str;",
doesn't print anything on screen..... Any trick please? Probably this is a
stupid question, so forgive me, I'm new to postgresql! :-)
Thanks to everybody.

Greetings,

  Antonello De Santis

At 16:43 +0200 on 27/03/1999, Antonello De Santis wrote:


> strcpy(str, 'table');
> result=PQexec(conn, "insert into str values('Rome', 10)");
>
> I guess a code like the above one would not give the expected result..... Is
> there a way to work that out?
> I'm trying to use the ecpg preprocessor to pass arguments of a query in
> variables, but I get a few errors....

IMO, this is too dynamic for ecpg. That is, last time I messed around with
embedded SQL, the format you presented could be used only to substitute
data values, not parts of the query like the name of a table or a field.

It can easily be done with libpq, though. Instead of what you wrote above,
you simply have to build the query correctly:

strcpy( str, 'table_name' );

sprintf( query, "INSERT INTO %s VALUES ('%s', %d)", str, "Rome", 10 );

result = PQexec( conn, query );

...

After the sprintf, the variable query (make sure you allocate enough
memory) will contain "INSERT INTO table_name VALUES ('Rome', 10)".

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

On Sat, Mar 27, 1999 at 03:43:23PM +0100, Antonello De Santis wrote:
> ...
> Is it possible to accomplish a query on a table whose name is specified in a
> variable? That is, something like the following:
>
> strcpy(str, 'table');
> result=PQexec(conn, "insert into str values('Rome', 10)");
>
> I guess a code like the above one would not give the expected result..... Is
> there a way to work that out?
> I'm trying to use the ecpg preprocessor to pass arguments of a query in
> variables, but I get a few errors.... Here's the piece of code that doesn't
> work:
> ...

Yes, that won't work. But if you commands do not have to transfer data from
the database to C variables you can use "exec sql execute immediate :str"
for whatever command you wrote into the variable string. Or you could use
PREPARE to prepare the statement and then use EXECUTE to execute it as often
as you like.

Check test1.pgc in the source tree for an example.

Michael
--
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

On Sun, Mar 28, 1999 at 06:37:28PM +0200, Herouth Maoz wrote:
> It can easily be done with libpq, though. Instead of what you wrote above,
> you simply have to build the query correctly:
>
> strcpy( str, 'table_name' );
>
> sprintf( query, "INSERT INTO %s VALUES ('%s', %d)", str, "Rome", 10 );
>
> result = PQexec( conn, query );

Which is essantially the same as

exec sql execute immediate :query;

:-)

Michael
--
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!