Hello,
I have come upon a problem with traversing a "tree" in a
DB2 project called config database. The problem can be reduced to
the following script.
The script works with $sql1, where I do not use a placeholder,
and SEGVs with $sql2 with a placeholder for sup_ord_id.
Can anybody provide me with a hint?
#! usr/db/bin/perl5 -w
use strict 'vars';
use DBI;
my $dbh;
my $sql1=q{
WITH PROD ( product_id, sup_ord_id, product_class, product_type) AS
(
SELECT * FROM CO_PRODUCT WHERE sup_ord_id='TMRAEF'
UNION ALL
SELECT child.* FROM PROD parent, CO_PRODUCT child
WHERE parent.product_id = child.sup_ord_id
)
SELECT * FROM PROD WHERE product_class = 'TMR'
};
my $sql2=q{
WITH PROD ( product_id, sup_ord_id, product_class, product_type) AS
(
SELECT * FROM CO_PRODUCT WHERE sup_ord_id=?
UNION ALL
SELECT child.* FROM PROD parent, CO_PRODUCT child
WHERE parent.product_id = child.sup_ord_id
)
SELECT * FROM PROD WHERE product_class = 'TMR'
};
sub dumprows {
my $dbh=shift;
my $sql=shift;
my @parms=@_;
my $sth;
print "SQL: $sql\n";
$sth=$dbh->prepare($sql);
if ( scalar @parms>0 ) {
print STDERR "parms: ",join(' ',@parms),"\n";
$sth->execute(@parms);
}
else {
print STDERR "call with no parms\n";
$sth->execute;
}
while ( my $row = $sth->fetchrow_hashref ) {
foreach my $i (keys %{$row}) {
print ">$i = $row->{$i}\n";
}
}
}
$dbh=DBI->connect("dbi:DB2:CONFIG","user","pwd");
dumprows($dbh,$sql1);
dumprows($dbh,$sql2,$ARGV[0]||"TMRAEF");
$dbh->disconnect;
exit 0;
The table CO_PRODUCT is populated with this sample data:
COLUMNS: product_class, product_id, sup_ord_id, product_type
# MN-function, IND-name, dependency, whatsit
TMR, AEF, TMRAEF, PRODUCT
TMR, SENT36, TMRSENT, PRODUCT
TMR, NTMON36, SENT36, PRODUCT
TMR, TMEM36, SENT36, PRODUCT
TMR, OS2MON, SENT36, PRODUCT
I have to develop the DB2 version on NT4SP5 with:
This is perl, v5.6.0 built for MSWin32-x86-multi-thread
(with 1 registered patch, see perl -V for more detail)
Binary build 623 provided by ActiveState Tool Corp.
http://www.ActiveState.com
Built 16:27:07 Dec 15 2000
# perl5 -MDBI -e 'print $DBI::VERSION,"\n"'
1.14
# perl5 -MDBD::DB2 -e 'print $DBD::DB2::VERSION,"\n"'
0.74
--
Tschau...Thomas
"Do you wanna be a legend or a passing footprint on the sands of time?"
Senior Consultant, Tivoli Certified Enterprise Consultant + Instructor
santix AG, Max-Planck-Str. 7, D-85716 Unterschleissheim, Germany
+49-89-321506-0, Fax -99, [EMAIL PROTECTED], www.santix.de/~ths
Office Frankfurt/Main: Roentgenstr. 7, D-60388 Bergen-Enkheim, Germany
+49-6109-7329-30, Fax +49-6109-369375, Mobile +49-171-4416678
