pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed'execute immediate' problem
2+ messages / 2 participants
[nested] [flat]
* 'execute immediate' problem
@ 2004-06-02 09:15 Kamil Andrusz <wizz@mniam.net>
0 siblings, 1 reply; 2+ messages in thread
From: Kamil Andrusz @ 2004-06-02 09:15 UTC (permalink / raw)
To: pgsql-interfaces
Hello list,
I've go a litlle test program, that should transfer data from one db to
another. It's just a quick hack to check if it would work at all.
----
#include <stdio.h>
#include <sys/types.h>
#include <unistd.h>
#include <string.h>
#include <errno.h>
#include <err.h>
int
main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char node_name[255];
char node_type[255];
char stmt[1024];
char ins[1024];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR sqlprint;
EXEC SQL WHENEVER NOT FOUND continue;
EXEC SQL CONNECT TO test1 AS src_link USER test;
EXEC SQL CONNECT TO test2 AS dst_link USER test;
sprintf(stmt, "SELECT node_name, node_type FROM node ORDER BY node_type");
EXEC SQL PREPARE node_p FROM :stmt;
EXEC SQL AT src_link DECLARE node_c CURSOR FOR node_p;
EXEC SQL AT src_link OPEN node_c;
EXEC SQL WHENEVER SQLERROR sqlprint;
EXEC SQL WHENEVER NOT FOUND goto _done;
for (;;) {
EXEC SQL AT src_link FETCH node_c INTO :node_name, :node_type;
sprintf(ins, "INSERT INTO node (node_name, node_type) VALUES ('%s', '%s')",
node_name, node_type);
printf("%s;\n", ins);
EXEC SQL AT dst_link EXECUTE IMMEDIATE :ins;
}
_done:
EXEC SQL AT src_link CLOSE node_c;
EXEC SQL AT src_link FREE node_p;
EXEC SQL DISCONNECT dst_link;
EXEC SQL DISCONNECT src_link;
exit(0);
}
----
The problem is, the 'EXEC SQL AT dst_link EXECUTE IMMEDIATE :ins;'
doesn't seem to work. When I do a 'select * from node' on the test2
database it shows 0 rows. The 'debug' printf shows, that the :ins
statement is correct.
The node table in both db's have two columns, node_name and node_type.
I'm running PostgreSQL 7.3.5 on OpenBSD 3.5.
I think I'm missing something obvious. Any hints?
Regards,
Kamil Andrusz
--
It's just a matter of opinion.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: 'execute immediate' problem
@ 2004-06-22 06:53 Michael Meskes <meskes@postgresql.org>
parent: Kamil Andrusz <wizz@mniam.net>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Meskes @ 2004-06-22 06:53 UTC (permalink / raw)
To: Kamil Andrusz <wizz@mniam.net>; +Cc: pgsql-interfaces
On Wed, Jun 02, 2004 at 11:15:23AM +0200, Kamil Andrusz wrote:
> Hello list,
>
> I've go a litlle test program, that should transfer data from one db to
> another. It's just a quick hack to check if it would work at all.
> ...
> I think I'm missing something obvious. Any hints?
Yes, you program does not commit the transaction. Thus it gets rolled
back on disconnect.
Keep in mind that ecpg always opens a transaction unless you use the
command line option "-t".
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] 2+ messages in thread
end of thread, other threads:[~2004-06-22 06:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-02 09:15 'execute immediate' problem Kamil Andrusz <wizz@mniam.net>
2004-06-22 06:53 ` Michael Meskes <meskes@postgresql.org>
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