Problem with selecting rows from a database link

------_=_NextPart_001_01C7F96B.4B57F7AA
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hey guys

=20

I am trying to query a table using a database link, using the following
code:

=20

use strict;

=20

use DBI;

=20

my $col_value =3D 'test';

my $query =3D 'select col_1,col_2,col_3 from table_name@link_name where
col_1 =3D ?';

=20

my $dbh =3D DBI ->connect ('dbi:Oracle:dbs','username','password',{
RaiseError =3D> 1, AutoCommit =3D> 0 }) || die "Database connetion not =
made:
$DBI::errstr";

=20

my $sth =3D $dbh->prepare ($query)|| die "Unable to prepare\n";

=20

$sth->execute($col_value) or die "Can't execute SQL statement:
$DBI::errstr Stopped\n";

my ($col_1,$col_2,$col_3);=20

=20

$sth->bind_columns(\($col_1,$col_2,$col_3))||die "Unable to fetch\n";

=20

print "$col_1: $col_2 :$col_3\n" while $sth1->fetchrow_arrayref;

=20

$dbh->disconnect;

=20

Now what happens is that, the first time this program is run, it returns
the correct result. However after the first successful run it fails to
run again. It gives NO error, it just fails to return the rows. If a
create a new link and use the new link in the query, it then works for
the first run, and then fails after. The same thing happens if I decide
to query a view that was created using the link.=20

=20

The link is connected to a 9I database.

Perl version: 5.8.6

DBI version =3D 1.59

DBD:Oracle =3D 1.19

=20

Regards,

Ray


------_=_NextPart_001_01C7F96B.4B57F7AA--

Re: Problem with selecting rows from a database link

On Monday 17 Sep 2007, Crosbourne, Rainaldo wrote:
> Hey guys
>
>
>
> I am trying to query a table using a database link, using the following
> code:
>
>
>
> use strict;
>
>
>
> use DBI;
>
>
>
> my $col_value = 'test';
>
> my $query = 'select col_1,col_2,col_3 from table_name@link_name where
> col_1 = ?';
>
>
>
> my $dbh = DBI ->connect ('dbi:Oracle:dbs','username','password',{
> RaiseError => 1, AutoCommit => 0 }) || die "Database connetion not made:
> $DBI::errstr";
>
>
>
> my $sth = $dbh->prepare ($query)|| die "Unable to prepare\n";
>
>
>
> $sth->execute($col_value) or die "Can't execute SQL statement:
> $DBI::errstr Stopped\n";
>
> my ($col_1,$col_2,$col_3);
>
>
>
> $sth->bind_columns(\($col_1,$col_2,$col_3))||die "Unable to fetch\n";
>
>
>
> print "$col_1: $col_2 :$col_3\n" while $sth1->fetchrow_arrayref;
>
>
>
> $dbh->disconnect;
>
>
>
> Now what happens is that, the first time this program is run, it returns
> the correct result. However after the first successful run it fails to
> run again. It gives NO error, it just fails to return the rows. If a
> create a new link and use the new link in the query, it then works for
> the first run, and then fails after. The same thing happens if I decide
> to query a view that was created using the link.

A thought. Are you using persistent perl or perhaps Apache's mod_Perl?

Because if you are, then variables will stay in the same state they were in
when the previous run ended rather than being re-initialised when you re-run
(unless you explicitly initialise them)

It sounds like the kind of "gotcha" I've had trouble with in the past using
persistent perl.



--
Tony Green
Ipswich, Suffolk, England
http://www.beermad.org.uk
http://no2id-ip.web-brewer.co.uk