DBD::Oracle and Perl"s internal representation of strings

Hi,

I just posted this question on Perl Monks:

http://perlmonks.com/?node_id=649489

Am I correct in my analysis of what's going?

I would be happy if I could pass strings to DBD::Oracle without having
to worry about Perl's internal representation of the string, and it would
be ok if an exception was thrown whenever a string contained a character > 255.

Is there a way to configure DBD::Oracle to do that?

Thanks,
ER

Re: DBD::Oracle and Perl"s internal representation of strings

E R wrote:
> Hi,
>
> I just posted this question on Perl Monks:
>
> http://perlmonks.com/?node_id=649489
>
> Am I correct in my analysis of what's going?
>
> I would be happy if I could pass strings to DBD::Oracle without having
> to worry about Perl's internal representation of the string, and it would
> be ok if an exception was thrown whenever a string contained a character > 255.
>
> Is there a way to configure DBD::Oracle to do that?
>
> Thanks,
> ER
>
>
I have already answered your reply on perl monks. Of course someone here
may disagree with it ;-)

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBD::Oracle and Perl"s internal representation of strings

Alright, so here's another mystery:

use DBI;
....
$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1";

$dbh = DBI->connect(...);
$c = chr(228);
print "result: ", select_scalar("SELECT 1 FROM DUAL WHERE '$c' =
chr(228)"), "\n";

The select_scalar subroutine merely returns the first column of the
first row of the query.

The above code emits:

result:

However, if I change NLS_LANG to "AMERICAN_AMERICA.US7ASCII", it emits:

result: 1

Is there another NLS_LANG setting I should use, or should I check my
versions of OCI, DBD::Oracle and perl? I think I'm running OCI version
8. My perl is 5.8.0.

Thanks,
ER

Re: DBD::Oracle and Perl"s internal representation of strings

E R wrote:
> Alright, so here's another mystery:
>
> use DBI;
> ...
> $ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1";
>
> $dbh = DBI->connect(...);
> $c = chr(228);
> print "result: ", select_scalar("SELECT 1 FROM DUAL WHERE '$c' =
> chr(228)"), "\n";
>
> The select_scalar subroutine merely returns the first column of the
> first row of the query.
>
> The above code emits:
>
> result:
>
> However, if I change NLS_LANG to "AMERICAN_AMERICA.US7ASCII", it emits:
>
> result: 1
>
> Is there another NLS_LANG setting I should use, or should I check my
> versions of OCI, DBD::Oracle and perl? I think I'm running OCI version
> 8. My perl is 5.8.0.
>
> Thanks,
> ER
>
>
Are you actually running the code you keep sending? In:

sub select_scalar {
my ($dbi, $sql) = @_;
my $sth = $dbi->prepare($sql);
$sth->execute();
my $r = $sth->fetch_row_array();
return $r->[0];
}

there is no such method fetch_row_array - it should be fetchrow_array.

In:

$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1"

there is an extra ".

In:

select_scalar("SELECT 1.....

you have missed off the $dbi argument select_scalar requires.

It is also a good idea to put RaiseError=>1 in you DBI connect for
examples like this so any errors are trapped and use strict, use
warnings never goes amiss.

So fixing all of that we have:

use strict;
use warnings;
use DBI;
$ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1";
my $dbi = DBI->connect('dbi:Oracle:xxx','xxx','xxx',{RaiseError=>1})
|| die $DBI::errstr;

my $c = chr(228);
print "result: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '$c' =
chr(228)"), "\n";

sub select_scalar {
my ($dbi, $sql) = @_;
my $sth = $dbi->prepare($sql);
$sth->execute();
my $r = $sth->fetchrow_array();
return $r->[0];
}

I think you may be on rather too old an Oracle (at 8) for this. What do
you get when you add:

my $sth = $dbi->prepare(q{select chr(228) from dual});
$sth->execute;
DBI::dump_results($sth);

just after the connect call and what does:

my $nls_params = $dbi->ora_nls_parameters();
foreach my $k (keys %$nls_params) {
print "$k, $nls_params->{$k}\n";
}

output.

Rather interestingly I get "DBD::Oracle::st fetch failed: ORA-29275:
partial multibyte character (DBD ERROR: OCIStmtFetch) [for Statement
"select chr(228) from dual"]" for the select so there might be something
there but then again my database character set is AL32UTF8.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: DBD::Oracle and Perl"s internal representation of strings

On 07/11/07 17:38, E R wrote:
> Hi,
>
> I just posted this question on Perl Monks:
>
> http://perlmonks.com/?node_id=649489
>
> Am I correct in my analysis of what's going?

You might be interested in a thread which I started just over
two years ago. See

http://www.nntp.perl.org/group/perl.dbi.users/2005/08/msg273 35.html

As you will see, the thread never reached a satisfactory conclusion.

Sadly, I have not been able to find the time to work on anything
DBI related since that posting.

--
Charles Jardine - Computing Service, University of Cambridge
cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679

Re: DBD::Oracle and Perl"s internal representation of strings

On Nov 9, 2007 4:47 AM, Charles Jardine wrote:
> On 07/11/07 17:38, E R wrote:
> > Hi,
> >
> > I just posted this question on Perl Monks:
> >
> > http://perlmonks.com/?node_id=649489
> >
> > Am I correct in my analysis of what's going?
>
> You might be interested in a thread which I started just over
> two years ago. See
>
> http://www.nntp.perl.org/group/perl.dbi.users/2005/08/msg273 35.html

This is exactly the issue I am trying to raise. The only solution
seems to be to explicitly
encode all the strings in the encoding you want before passing them to
DBD::Oracle.
For example, instead of:

$sth = $dbi->prepare($sql);
$sth->execute(@values);

one would have to write something like:

$sth = $dbi->prepare(Encode::encode('iso-8859-1', $sql));
$sth->execute(map { Encode::encode('iso-8859-1', $_) } @values);

(Of course, there are more efficient ways to do this.)

Consequently, for those of us who are using only iso-8859-1 in our SQL
and in our database, it would be very useful to have an interface to
DBD::Oracle which performed this encoding automatically. An exception
could be thrown if a string containing a character > 255 were passed.
I could write such an interface in Perl, but I was wondering if the
same thing could be effected within DBD::Oracle through some
configuration settings.

> As you will see, the thread never reached a satisfactory conclusion.

I haven't read through the entire thread, but it seems that
DBD::Oracle has to be treated as what I call an "octet sequence
interface". That is, the caller is responsible for properly encoding
and decoding the results. At least this is what I have to do. Someday
it would be nice to have a true "text" interface that would
automatically negotiate all the character encoding issues along the
pipeline from Perl to the database.

> Sadly, I have not been able to find the time to work on anything
> DBI related since that posting.

Thanks for bringing this thread up - it's been very helpful!