Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id EA5139FBC14
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 30 May 2007 04:33:56 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 96213-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 30 May 2007 04:33:50 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id D83D59FB671
	for <pgsql-sql@postgresql.org>; Wed, 30 May 2007 04:33:52 -0300 (ADT)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Wed, 30 May 2007 09:19:06 +0200
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Wed, 30 May 2007 09:33:50 +0200
Message-Id: <465D44F3.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Wed, 30 May 2007 09:33:40 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: spi and error messages
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__Part9FB889C4.0__="
X-imss-version: 2.047
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.267 tagged_above=0 required=5 tests=AWL=0.129,
	BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 200705/189
X-Sequence-Number: 28469

--=__Part9FB889C4.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Hi,
I'm writing some function to fetch data from an Oracle database and store =
it in a PostgreSQL database.
=20
CREATE OR REPLACE FUNCTION public.replicate_billing(text, date, date) =
RETURNS void AS
$body$
  use DBI;
  my $dbh_ora =3D DBI->connect('dbi:Oracle:database=3Dbmssa;host=3Dfirev120=
-1.indicator.be;sid=3Dmars', 'bmssa', '***********', {PrintError =3D> 0});
  elog(ERROR, DBI->errstr) unless ($dbh_ora);
  my $query =3D 'SELECT ... FROM ... WHERE ...';
  my $sel =3D $dbh_ora->prepare($query);
  elog(ERROR, $dbh_ora->errstr);
  sel->execute;
  elog(ERROR, $dbh_ora->errstr);
  my $target =3D 'INSERT INTO ... VALUES ($1,$2,$3)';
  my $plan =3D spi_prepare($target, 'varchar', 'varchar', 'date');
  elog(ERROR, ???????);
  ...
  spi_freeplan($plan);
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
As you can see I raise an error if connecting to Oracle fails and if =
preparing or executing the plan for fetching data fails.=20
Likewise I would like to raise an error if preparing the insert statement =
fails. As error message I would like to use the message generated by =
postgresql itself just like I do in the Oracle part of my function. I =
can't seem to find however how to do that.=20
In general how should I catch the error message generated if one of the =
spi functions (spi_exec_query, spi_query, spi_fetchrow, spi_prepare,...) =
fails?
=20
Thanks for your help.

--=__Part9FB889C4.0__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-15=
">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Hi,</DIV>
<DIV>I'm writing some function to fetch data from an Oracle database and =
store it in a PostgreSQL database.</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE OR REPLACE FUNCTION public.replicate_billing(text, date, date) =
RETURNS void AS<BR>$body$<BR>&nbsp; use DBI;<BR>&nbsp; my $dbh_ora =3D =
DBI-&gt;connect('dbi:Oracle:database=3Dbmssa;host=3Dfirev120-1.indicator.be=
;sid=3Dmars', 'bmssa', '***********', {PrintError =3D&gt; 0});<BR>&nbsp; =
elog(ERROR, DBI-&gt;errstr) unless ($dbh_ora);<BR>&nbsp; my $query =3D =
'SELECT&nbsp;...&nbsp;FROM ... WHERE ...';<BR>&nbsp; my $sel =3D $dbh_ora-&=
gt;prepare($query);<BR>&nbsp; elog(ERROR, $dbh_ora-&gt;errstr);<BR>&nbsp; =
sel-&gt;execute;<BR>&nbsp; elog(ERROR, $dbh_ora-&gt;errstr);<BR>&nbsp; my =
$target =3D 'INSERT INTO ... VALUES ($1,$2,$3)';<BR>&nbsp; my $plan =3D =
spi_prepare($target, 'varchar', 'varchar', 'date');<BR>&nbsp; elog(ERROR, =
???????);</DIV>
<DIV>&nbsp; ...</DIV>
<DIV>&nbsp; spi_freeplan($plan);</DIV>
<DIV>$body$<BR>LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY =
INVOKER;<BR></DIV>
<DIV>As you can see I raise an error if connecting to Oracle fails and if =
preparing or executing&nbsp;the plan for fetching data fails. </DIV>
<DIV>Likewise I would like to raise an error if preparing the insert =
statement fails. As error message I would like to use the message =
generated by postgresql itself just like I do in the Oracle part of my =
function. I can't seem to find however how to do that. </DIV>
<DIV>In general how should I catch the error message generated if&nbsp;one =
of the spi functions (spi_exec_query, spi_query, spi_fetchrow, spi_prepare,=
...) fails?</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks for your help.</DIV></BODY></HTML>

--=__Part9FB889C4.0__=--