Skip site navigation (1) Skip section navigation (2)

'execute immediate' problem

From: Kamil Andrusz <wizz(at)mniam(dot)net>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: 'execute immediate' problem
Date: 2004-06-02 09:15:23
Message-ID: 8765aa5nis.fsf@shwurzbung.mniam.net (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-interfaces by date

Next:From: Bruce MomjianDate: 2004-06-02 19:56:28
Subject: Re: Bug in translation of DEALLOCATE PREPARE
Previous:From: Owens, SteveDate: 2004-06-01 13:55:42
Subject: Re: Bug in translation of DEALLOCATE PREPARE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group