RE: Surprising DBD::Oracle error raised
You are fetching off the end of a cursor so you would expect an error on any sql
Would be the same sort of thing as
my @test= (1,2);
print $test[100];
If you know your recordset will be small I would use fetchall_arrrayref or
fetchall_hashref rather than just fetch.
The normal way is to fetch is in a loop with 'while'. As there is no way to
tell how many records will be in
your set before you do you SQL unless you tell the SQL to return only x rows.
Cheers
John
Date: Tue, 4 Feb 2014 13:36:50 -0600
Subject: Surprising DBD::Oracle error raised
From: davidni...@gmail.com
To: dbi-users@perl.org
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.
Advise?
--
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash
Re: Surprising DBD::Oracle error raised
On 04/02/14 19:36, David Nicol wrote:
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.
Advise?
That is in deed interesting. When I run the following with DBD::ODBC to MS SQL
Server:
use strict;
use warnings;
use DBI;
my $h = DBI-connect();
eval {
$h-do(q/drop table mje/);
};
$h-do(q/create table mje (a int)/);
my $s = $h-prepare(q/insert into mje values(?)/);
$s-execute(1);
$s-execute(2);
$s = $h-prepare(q/select * from mje where a = 1/);
$s-execute;
my ($row) = $s-fetchrow_array;
print $row\n;
$row = $s-fetch;
print $row\n;
I get:
1
Use of uninitialized value $row in concatenation (.) or string at
mje/fetch_off_end.pl line 20.
However, I get the same with DBD::Oracle so how is you code different from the
above.
Martin
RE: Surprising DBD::Oracle error raised
Well isn't he is calling with the alias 'fetch' and he is calling it in I think
scalar context
my ($row) = $s-fetchrow_array;
vs
if ($price_sth-fetch)
There is the odd chance that he is doing the SQL against a 'view', 'cursor'
or alike but I doupt that is it.
Date: Wed, 5 Feb 2014 13:25:03 +
From: martin.ev...@easysoft.com
To: davidni...@gmail.com; dbi-users@perl.org
Subject: Re: Surprising DBD::Oracle error raised
On 04/02/14 19:36, David Nicol wrote:
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.
Advise?
That is in deed interesting. When I run the following with DBD::ODBC to MS
SQL Server:
use strict;
use warnings;
use DBI;
my $h = DBI-connect();
eval {
$h-do(q/drop table mje/);
};
$h-do(q/create table mje (a int)/);
my $s = $h-prepare(q/insert into mje values(?)/);
$s-execute(1);
$s-execute(2);
$s = $h-prepare(q/select * from mje where a = 1/);
$s-execute;
my ($row) = $s-fetchrow_array;
print $row\n;
$row = $s-fetch;
print $row\n;
I get:
1
Use of uninitialized value $row in concatenation (.) or string at
mje/fetch_off_end.pl line 20.
However, I get the same with DBD::Oracle so how is you code different from
the above.
Martin
Re: Surprising DBD::Oracle error raised
On 05/02/14 13:36, John Scoles wrote:
Well isn't he is calling with the alias 'fetch'
isn't he:
if ($price_sth-fetch) {
and he is calling it in I think scalar context
$row = $s-fetch;
so am I.
my ($row) = $s-fetchrow_array;
As far as I can see I did the same.
vs
if ($price_sth-fetch)
There is the odd chance that he is doing the SQL against a 'view', 'cursor'
or alike but I doupt that is it.
Perhaps David can tell us more.
Martin
Date: Wed, 5 Feb 2014 13:25:03 +
From: martin.ev...@easysoft.com
To: davidni...@gmail.com; dbi-users@perl.org
Subject: Re: Surprising DBD::Oracle error raised
On 04/02/14 19:36, David Nicol wrote:
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.
Advise?
That is in deed interesting. When I run the following with DBD::ODBC to MS
SQL Server:
use strict;
use warnings;
use DBI;
my $h = DBI-connect();
eval {
$h-do(q/drop table mje/);
};
$h-do(q/create table mje (a int)/);
my $s = $h-prepare(q/insert into mje values(?)/);
$s-execute(1);
$s-execute(2);
$s = $h-prepare(q/select * from mje where a = 1/);
$s-execute;
my ($row) = $s-fetchrow_array;
print $row\n;
$row = $s-fetch;
print $row\n;
I get:
1
Use of uninitialized value $row in concatenation (.) or string at
mje/fetch_off_end.pl line 20.
However, I get the same with DBD::Oracle so how is you code different from
the above.
Martin
Re: Surprising DBD::Oracle error raised
In message cafwsco-tqp5yycpptawzqkkyhz6qjs3d_g+yv5r9x9apdbm...@mail.gmail.com
, David Nicol writes:
the error message claimed I hadn't executed the statement.
Where is your DBI_TRACE?
I can't reproduce.
John
groenv...@acm.org
use strict;
use DBI;
my $dbh = DBI-connect( dbi:Oracle:, scott, tiger,
{ RaiseError = 1 } );
my $sth = $dbh-prepare( qq{
SELECT 'FOO','BAR' FROM dual
} );
$sth-execute;
my ( $foo ) = $sth-fetchrow_array;
if ($sth-fetch) {
warn should not enter;
}
$sth-finish;
$dbh-disconnect;
Re: Surprising DBD::Oracle error raised
On Wed, Feb 5, 2014 at 7:25 AM, Martin J. Evans
martin.ev...@easysoft.com wrote:
That is indeed interesting. When I run the following with DBD::ODBC to MS
SQL Server:
use strict;
use warnings;
use DBI;
my $h = DBI-connect();
eval {
$h-do(q/drop table mje/);
};
$h-do(q/create table mje (a int)/);
my $s = $h-prepare(q/insert into mje values(?)/);
$s-execute(1);
$s-execute(2);
$s = $h-prepare(q/select a from mje where a = 1/);
$s-execute;
my ($row) = $s-fetchrow_array; # fetch result
print $row\n;
$row = $s-fetch; # fetch status
print $row\n;
I get:
1
Use of uninitialized value $row in concatenation (.) or string at
mje/fetch_off_end.pl line 20.
However, I get the same with DBD::Oracle so how is you code different from
the above.
Martin
The code is the same. Rather, close enough, my second fetch was in
boolean context
while yours is in a storage context, but I don't think Perl cares,
both contexts are scalar.
And I've got RaiseError set. And my SQL, which joins two tables, has
three placeholders, bound with bind_param between the prepare_cached
and the execute.
And your warning verifies that the second fetch returned an undef, as
documented, while mine threw an exception falsely claiming that the
statement hadn't been executed yet:
An Oracle error has occurred :
DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps
you need to call execute first)
SQL Text
select a from mje where a = 1
...
And you're certainly using more recent builds of everything.
--
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash
Re: Surprising DBD::Oracle error raised
On 05/02/14 14:08, David Nicol wrote:
On Wed, Feb 5, 2014 at 7:25 AM, Martin J. Evans
martin.ev...@easysoft.com wrote:
That is indeed interesting. When I run the following with DBD::ODBC to MS
SQL Server:
use strict;
use warnings;
use DBI;
my $h = DBI-connect();
eval {
$h-do(q/drop table mje/);
};
$h-do(q/create table mje (a int)/);
my $s = $h-prepare(q/insert into mje values(?)/);
$s-execute(1);
$s-execute(2);
$s = $h-prepare(q/select a from mje where a = 1/);
$s-execute;
my ($row) = $s-fetchrow_array; # fetch result
print $row\n;
$row = $s-fetch; # fetch status
print $row\n;
I get:
1
Use of uninitialized value $row in concatenation (.) or string at
mje/fetch_off_end.pl line 20.
However, I get the same with DBD::Oracle so how is you code different from
the above.
Martin
The code is the same. Rather, close enough, my second fetch was in
boolean context
while yours is in a storage context, but I don't think Perl cares,
both contexts are scalar.
And I've got RaiseError set.
So have I now and it made no difference.
And my SQL, which joins two tables, has
three placeholders, bound with bind_param between the prepare_cached
and the execute.
Mine also use prepare_cached and a bind_param now - no change.
And your warning verifies that the second fetch returned an undef, as
documented, while mine threw an exception falsely claiming that the
statement hadn't been executed yet:
An Oracle error has occurred :
DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps
you need to call execute first)
SQL Text
select a from mje where a = 1
...
And you're certainly using more recent builds of everything.
Which is probably it.
Can you try updated versions so we can rule this out or alternatively, tell me
what you are using and I'll try and reproduce it.
Martin
Surprising DBD::Oracle error raised
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.
Advise?
--
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash
Re: Surprising DBD::Oracle error raised
In message CAFwScO-0DchsbSLPSmR9JY+KmBUF3BfUREH9XdDSVgEAo=p...@mail.gmail.com
, David Nicol writes:
$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
I expected the fetch to return undef, but it throws an Oracle error.
What does your DBI_TRACE reveal?
John
groenv...@acm.org
Re: Surprising DBD::Oracle error raised
the error message claimed I hadn't executed the statement.
I changed it to
if (eval { $price_sth-fetch}) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
but I think I'll change it again to
if ($price_sth-{Active} and $price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}
but if that's the right thing to do, the documentation (in
DBD::Oracle) should mention it in fetchrow_array's section IMO.
--
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash
