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
PostgreSQL driver DBD::Pg releases version 3.0.0
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Version 3.0.0 of DBD::Pg, the Perl interface to Postgres, has just been released. As you can see from the version number, this is a major release. In addition to many bugfixes and enhancements, the behavior of pg_enable_utf8 has been changed. The minimum versions of Perl (5.8.1) and DBI (1.614) have been increased. The new version is available from CPAN. Checksums: 58c2613bcb241279aca4c111ba16db48 DBD-Pg-3.0.0.tar.gz 03ded628d453718cbceaea906da3412df5a7137a DBD-Pg-3.0.0.tar.gz The complete list of changes: Version 3.0.0 - Major change in UTF-8 handling. If client_encoding is set to UTF-8, always mark returned Perl strings as utf8. See the pg_enable_utf8 docs for more information. [Greg Sabino Mullane, David E. Wheeler, David Christensen] - Bump DBI requirement to 1.614 - Bump Perl requirement to 5.8.1 - Add new handle attribute, switch_prepared, to control when we stop using PQexecParams and start using PQexecPrepared. The default is 2: in previous versions, the effective behavior was 1 (i.e. PQexecParams was never used). [Greg Sabino Mullane] - Better handling of items inside of arrays, particularly bytea arrays. [Greg Sabino Mullane] (CPAN bug #91454) - Map SQL_CHAR back to bpchar, not char [Greg Sabino Mullane, reported by H.Merijn Brand] - Do not force oids to Perl ints [Greg Sabino Mullane] (CPAN bug #85836) - Return better sqlstate codes on fatal errors [Rainer Weikusat] - Better prepared statement names to avoid bug [Spencer Sun] (CPAN bug #88827) - Add pg_expression field to statistics_info output to show functional index information [Greg Sabino Mullane] (CPAN bug #76608) - Adjust lo_import_with_oid check for 8.3 (CPAN bug #83145) - Better handling of libpq errors to return SQLSTATE 08000 [Stephen Keller] - Make sure CREATE TABLE .. AS SELECT returns rows in non do() cases - Add support for AutoInactiveDestroy [David Dick] (CPAN bug #68893) - Fix ORDINAL_POSITION in foreign_key_info [Dagfinn Ilmari Mannsker] (CPAN bug #88794) - Fix foreign_key_info with unspecified schema [Dagfinn Ilmari Mannsker] (CPAN bug #88787) - Allow foreign_key_info to work when pg_expand_array is off [Greg Sabino Mullane and Tim Bunce] (CPAN bug #51780) - Remove math.h linking, as we no longer need it (CPAN bug #79256) - Spelling fixes (CPAN bug #78168) - Better wording for the AutoCommit docs (CPAN bug #82536) - Change NOTICE to DEBUG1 in t/02attribs.t test for handle attribute PrintWarn: implicit index creation is now quieter in Postgres. [Erik Rijkers] - Use correct SQL_BIGINT constant for int8 [Dagfinn Ilmari Mannsker] - Fix assertion when binding array columns on debug perls = 5.16 [Dagfinn Ilmari Mannsker] - Adjust test to use 3 digit exponential values [Greg Sabino Mullane] (CPAN bug #59449) - Avoid reinstalling driver methods in threads [Dagfinn Ilmari Mannsker] (CPAN bug #83638) - Make sure App::Info does not prompt for pg_config location if AUTOMATED_TESTING or PERL_MM_USE_DEFAULT is set [David E. Wheeler] (CPAN bug #90799) - Fix typo in docs for pg_placeholder_dollaronly [Bryan Carpenter] (CPAN bug #91400) - Cleanup dangling largeobjects in tests [Fitz Elliott] (CPAN bug #92212) - Fix skip test counting in t/09arrays.t [Greg Sabino Mullane] (CPAN bug #79544) - Explicitly specify en_US for spell checking [Dagfinn Ilmari Mannsker] (CPAN bug #91804) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201402050932 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlLyS34ACgkQvJuQZxSWSsjLcgCeM413ALNdM7rj4SfYMnyEmee5 VtAAnj56tNtWoK9dWJKVg1wHgTsXBXok =6wwX -END PGP SIGNATURE-
DBI doc buglet
Hi Under connect(), 9 lines down, the text '(i.e., the $data_source prefix is dbi::)' should only have 1 colon. -- Ron Savage http://savage.net.au/
