Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B6E379FBB97 for ; Mon, 4 Jun 2007 07:29:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33390-06 for ; Mon, 4 Jun 2007 07:29:54 -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 E4FDE9FBB54 for ; Mon, 4 Jun 2007 07:29:53 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Mon, 04 Jun 2007 12:14:58 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Mon, 04 Jun 2007 12:29:50 +0200 Message-Id: <466405B4.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Mon, 04 Jun 2007 12:29:40 +0200 From: "Bart Degryse" To: Subject: perlu: did I find a bug, or did I make one? References: <465D44F3.A3DD.0030.0@indicator.be><20070530141349.GA76707@winni e.fuhr.org> In-Reply-To: <20070530141349.GA76707@winnie.fuhr.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part20070884.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.263 tagged_above=0 required=5 tests=AWL=0.125, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001, UPPERCASE_25_50=0 X-Spam-Level: X-Archive-Number: 200706/7 X-Sequence-Number: 28497 --=__Part20070884.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Situation: I'm writing a function that fetches data in an Oracle database and stores = it in postgresql database. The function works, but I can't seem to get the = error handling right. I get something but it's not what I expect. This is = what I get: executing 14 generated 4 errors ERROR: lil foutje Address Belgium ERROR: lil foutje Address Belgium ERROR: lil foutje Address Belgium ERROR: lil foutje Address Belgium And this is what I expect to get: executing 14 generated 4 errors ERROR: lil foutje Address Belgium ERROR: lil foutje Address France ERROR: bol nog een foutje Italie ERROR: bol nog een foutje Beglie =20 This is the data in Oracle Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('national', 'This country', 'ash', 30); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('be', 'Address Belgium', 'lil', 501); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('fr', 'Address France', 'lil', 496); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('it', 'Italie', 'bol', 3138); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('national', 'National', '012', 687181679); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('internatio', 'International countries', 'ash', 29); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('be', 'Beglie', 'bol', 3187); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('sp', 'Address Spain', 'bar', 1302174); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('internatio', 'International countries', 'as0', 29); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('national', 'This country', 'as0', 30); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('national', 'National', '011', 216774985); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('internatio', 'International', '011', 216774984); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('national', 'National', 'hlm', 451094066); Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) = Values ('internatio', 'International', 'hlm', 451094067); This is the target table definition in PostgreSQL CREATE TABLE "public"."afh_test" ( "addrformat" VARCHAR(10) NOT NULL,=20 "name" VARCHAR(30) NOT NULL,=20 "dataareaid" VARCHAR(3) NOT NULL,=20 "recid" NUMERIC(10,0) NOT NULL ) WITHOUT OIDS; =20 CREATE UNIQUE INDEX "afh_test_idx" ON "public"."afh_test" USING btree ("addrformat", "dataareaid"); =20 CREATE TRIGGER "afh_test_tr" BEFORE INSERT=20 ON "public"."afh_test" FOR EACH ROW=20 EXECUTE PROCEDURE "public"."temp_func1"(); =20 CREATE OR REPLACE FUNCTION "public"."temp_func1" () RETURNS trigger AS $body$ BEGIN IF NEW.dataareaid =3D 'lil' THEN RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name; elsIF NEW.dataareaid =3D 'bol' THEN RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name; END IF; RETURN NULL; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; =20 This is the function that retrieves the Oracle data and inserts it in the = target table CREATE OR REPLACE FUNCTION "public"."dbi_insert3" () RETURNS integer AS $body$ use DBI; $query =3D 'SELECT * FROM AddressFormatHeading'; $target =3D 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) = VALUES (?,?,?,?)'; =20 my $dbh_ora =3D DBI->connect('dbi:Oracle:database=3Dbmssa;host=3Dfirev120= -1.indicator.be;sid=3Dmars', 'bmssa', '8QD6ibmD') or die "Couldn't connect to database: " . DBI->errstr; my $dbh_pg =3D DBI->connect('dbi:Pg:dbname=3Ddefrevdev;host=3D10.100.1.21= ;port=3D2345', 'defrevsys', 'Y2I6vbEW') or die "Couldn't connect to database: " . DBI->errstr; =20 my $sel =3D $dbh_ora->prepare($query) or elog(ERROR, "Couldn't prepare statement: " . $dbh_ora->errst= r); $sel->execute; my $ins =3D $dbh_pg->prepare($target) or elog(ERROR, "Couldn't prepare statement: " . $dbh_pg->errstr= ); my $fetch_tuple_sub =3D sub { $sel->fetchrow_arrayref }; my @tuple_status; my $rc =3D $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status); if (DBI->err) { elog(INFO, DBI->errstr."\n"); my @errors =3D grep { ref $_ } @tuple_status; foreach my $error (@errors) { elog(INFO, $error->[1]); } } $dbh_ora->disconnect; $dbh_pg->disconnect; $body$ LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; =20 And this ... well you can guess... select dbi_insert3(); =20 Thanks for any help! --=__Part20070884.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
Situation:
I'm writing a function that fetches data in an Oracle database and = stores it in postgresql database. The function works, but I can't seem to = get the error handling right. I get something but it's not what I = expect. This is what I get:
executing 14 generated 4 = errors
ERROR:  lil foutje Address Belgium
ERROR:  lil = foutje Address Belgium
ERROR:  lil foutje Address Belgium
ERROR:=   lil foutje Address Belgium
And this is what I expect to get:
executing 14 generated 4 errors
ERROR:  lil foutje = Address Belgium
ERROR:  lil foutje Address France
ERROR= :  bol nog een foutje Italie
ERROR:  bol nog een fou= tje Beglie
 
This is the data in Oracle
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, = RECID) Values ('national', 'This country', 'ash', 30);
Insert into = addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', = 'Address Belgium', 'lil', 501);
Insert into addressformatheading = (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('fr', 'Address France', = 'lil', 496);
Insert into addressformatheading (ADDRFORMAT, NAME, = DATAAREAID, RECID) Values ('it', 'Italie', 'bol', 3138);
Insert into = addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values = ('national', 'National', '012', 687181679);
Insert into addressformathea= ding (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'Internati= onal countries', 'ash', 29);
Insert into addressformatheading (ADDRFORMA= T, NAME, DATAAREAID, RECID) Values ('be', 'Beglie', 'bol', 3187);
Insert= into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values = ('sp', 'Address Spain', 'bar', 1302174);
Insert into addressformatheadin= g (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'Internationa= l countries', 'as0', 29);
Insert into addressformatheading (ADDRFORMAT, = NAME, DATAAREAID, RECID) Values ('national', 'This country', 'as0', = 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, = RECID) Values ('national', 'National', '011', 216774985);
Insert into = addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values = ('internatio', 'International', '011', 216774984);
Insert into = addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values = ('national', 'National', 'hlm', 451094066);
Insert into addressformathea= ding (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'Internati= onal', 'hlm', 451094067);
This is the target table definition in PostgreSQL
CREATE TABLE "public"."afh_test" (
  "addrformat" VARCHAR(10) = NOT NULL,
  "name" VARCHAR(30) NOT NULL,
  "dataareaid" = VARCHAR(3) NOT NULL,
  "recid" NUMERIC(10,0) NOT NULL
) = WITHOUT OIDS;
 
CREATE UNIQUE INDEX "afh_test_idx" ON "public"."afh_test"
  = USING btree ("addrformat", "dataareaid");
 
CREATE TRIGGER "afh_test_tr" BEFORE INSERT
ON "public"."afh_test" = FOR EACH ROW
EXECUTE PROCEDURE "public"."temp_func1"();
 
CREATE OR REPLACE FUNCTION "public"."temp_func1" () RETURNS trigger = AS
$body$
BEGIN
  IF NEW.dataareaid =3D 'lil' THEN
 &= nbsp;  RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
&nbs= p; elsIF NEW.dataareaid =3D 'bol' THEN
    RAISE = EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name;
  END = IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' = VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
This is the function that retrieves the Oracle data and inserts it in = the target table
CREATE OR REPLACE FUNCTION "public"."dbi_insert3" () RETURNS integer = AS
$body$
  use DBI;
  $query =3D 'SELECT * FROM = AddressFormatHeading';
  $target =3D 'INSERT INTO afh_test = (addrformat, name, dataareaid, recid) VALUES (?,?,?,?)';
 
  my $dbh_ora =3D DBI->connect('dbi:Oracle:database=3Dbmssa;ho= st=3Dfirev120-1.indicator.be;sid=3Dmars', 'bmssa', '8QD6ibmD')
 &nb= sp;            =   or die "Couldn't connect to database: " . DBI->errstr;
  = my $dbh_pg =3D DBI->connect('dbi:Pg:dbname=3Ddefrevdev;host=3D10.100.1.2= 1;port=3D2345', 'defrevsys', 'Y2I6vbEW')
     &= nbsp;         or die "Couldn't = connect to database: " . DBI->errstr;
 
  my $sel =3D $dbh_ora->prepare($query)
   &= nbsp;        or elog(ERROR, "Couldn't = prepare statement: " . $dbh_ora->errstr);
  $sel->execute;  my $ins =3D $dbh_pg->prepare($target)
    = ;        or elog(ERROR, "Couldn't = prepare statement: " . $dbh_pg->errstr);
  my $fetch_tuple_sub = =3D sub { $sel->fetchrow_arrayref };
  my @tuple_status;
&nbs= p; my $rc =3D $ins->execute_for_fetch($fetch_tuple_sub, \@tuple_status);=
  if (DBI->err) {
    elog(INFO, DBI->errs= tr."\n");
    my @errors =3D grep { ref $_ } @tuple_statu= s;
    foreach my $error (@errors) {
   = ;   elog(INFO, $error->[1]);
    }
 = }
  $dbh_ora->disconnect;
  $dbh_pg->disconnect;
$= body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY = INVOKER;
 
And this ... well you can guess...
select dbi_insert3();
 
Thanks for any help!
--=__Part20070884.0__=--