problems inserting a utf8 timestamp with DBD::Oracle

Hi,

Hoping someone can help me here as I'm really stuck with this. I have a
large amount of code which reads some XML and inserts data from the XML
into a database. One particular insert fails:

insert into document
(book,category,country,expiry,id,last_update,mnem,name,odec, route,source,tdate,ttimestamp,ttype,version)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

and the parameters are:

SD, HR, UK, 2017-07-08 14:10:00, XXXXXXXXXXX, 1183290197812, XX, xxxxx,
N, 534453, xxxxxxxxxx, 2007-07-01, 1183269593843, xxxxxx, 1.2.6

the error is:

DBD::Oracle::st execute failed: ORA-01843: not a valid month (DBD ERROR:
error possibly near <*> indicator at char 146 in 'insert into document
(book,category,country,expiry,id,last_update,mnem,name,odec, route,source,tdate,ttimestamp,ttype,version)
values(:p1,:p2,:p3,:<*>p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15)')
at /usr/lib/perl5/site_perl/5.8.8/DBIx/Log4perl/st.pm line 38.

There is nothing obviously wrong with p4 and 2017-07-08 14:10:00 as the
session was previously altered to set the nls_timestamp_format to
'yyyy-mm-dd hh24:mi:ss. I have even verified that by looking at
NLS_SESSION_PARAMETERS just before the execute call.

After much head scratching I discovered the problem P4 has utf8 set on
it and downgrading it makes the code work i.e. when utf8::is_utf8
returns 1 on the timestamp scalar it fails and if I do a
utf8::downgrade(scalar) on it before calling execute it works.

When I examine a DBD:Oracle (v 1.19 BTW) trace file the only difference
I can see between the one that fails:

bind :p4 <== "2017-07-08 14:10:00" (type 0)
rebinding :p4 (is-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p4 <== "2017-07-08 14:10:00" (size 19/20/0, ptype 4, otype 1)
bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0,
at_exec 1)
OCIBindByName(8ef1a50,8ed3a8c,8dfdad8,":p4",3,8ece658,19,1,8 ed3aa4,0,8ed3
a9c,0,0,2)=SUCCESS
OCIBindDynamic(8ef1460,8dfdad8,8ed3a68,cfece0,8ed3a68,cfefc0 )=SUCCESS
rebinding :p4 with UTF8 value so setting csform=SQLCS_IMPLICIT
OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a76b,0,32,8dfdad8)=SUC CESS
OCIAttrGet(8ef1460,OCI_HTYPE_BIND,8ed3a78,0,31,8dfdad8)=SUCC ESS
bind :p4 <== "2017-07-08 14:10:00" (in, is-utf8, csid
873->0->873, ftype 1, csform 0->2, maxlen 19, maxdata_size 0)
OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a768,0,31,8dfdad8)=SUC CESS

and the successful one:

bind :p4 <== '2017-07-08 14:10:00' (type 0)
rebinding :p4 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
bind :p4 <== '2017-07-08 14:10:00' (size 19/20/0, ptype 4, otype 1)
bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0,
at_exec 1)
OCIBindByName(9330bc8,9315214,923ed10,":p4",3,926b1e0,19,1,9 31522c,0,9315
224,0,0,2)=SUCCESS
OCIBindDynamic(93305d8,923ed10,93151f0,1c4ce0,93151f0,1c4fc0 )=SUCCESS
OCIAttrGet(93305d8,OCI_HTYPE_BIND,9315200,0,31,923ed10)=SUCC ESS
bind :p4 <== '2017-07-08 14:10:00' (in, not-utf8, csid
873->0->873, ftype 1, csform 0->0, maxlen 19, maxdata_size 0)

is the us-utf8 versus not-utf8.

I have tried reducing the problem to a small script but just setting the
UTF8 flag on any timestamp does not make it fail so I'm unsure where to
go now. Any ideas?

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

RE: problems inserting a utf8 timestamp with DBD::Oracle

Hi Martin,

Martin Evans wrote:
> Hoping someone can help me here as I'm really stuck with this. I have
> a large amount of code which reads some XML and inserts data from the
> XML into a database. One particular insert fails:
>=20
[snip]
> After much head scratching I discovered the problem P4 has utf8 set on
> it and downgrading it makes the code work i.e. when utf8::is_utf8
> returns 1 on the timestamp scalar it fails and if I do a
> utf8::downgrade(scalar) on it before calling execute it works.

I think you've triggered this bug:
http://www.mail-archive.com/dbi-users@perl.org/msg26927.html

If I remember correctly, the fix is to set ora_ph_csform:

use DBD::Oracle qw(SQLCS_NCHAR);
...
# default for all future placeholders
$dbh->{'ora_ph_csform'} =3D SQLCS_NCHAR;

The bug only manifests when you bind a utf8-on value to a statement
parameter that was originally bound to a non-utf8 value.

- Philip

Re: problems inserting a utf8 timestamp with DBD::Oracle

Thanks for the very quick response Philip with something that certainly
looks like a possibility....

Garrett, Philip (MAN-Corporate) wrote:
> Hi Martin,
>
> Martin Evans wrote:
>> Hoping someone can help me here as I'm really stuck with this. I have
>> a large amount of code which reads some XML and inserts data from the
>> XML into a database. One particular insert fails:
>>
> [snip]
>> After much head scratching I discovered the problem P4 has utf8 set on
>> it and downgrading it makes the code work i.e. when utf8::is_utf8
>> returns 1 on the timestamp scalar it fails and if I do a
>> utf8::downgrade(scalar) on it before calling execute it works.
>
> I think you've triggered this bug:
> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html

I've read that but I don't see the resolution.

> If I remember correctly, the fix is to set ora_ph_csform:
>
> use DBD::Oracle qw(SQLCS_NCHAR);
> ...
> # default for all future placeholders
> $dbh->{'ora_ph_csform'} = SQLCS_NCHAR;

I've tried that - no luck :-(

> The bug only manifests when you bind a utf8-on value to a statement
> parameter that was originally bound to a non-utf8 value.

The whole DBI interaction is:

$do = ['begin dbms_output.enable(:1); end;',undef,20000];
do: 'alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss''
do: 'alter session set nls_date_format = 'yyyy-mm-dd''
prepare_cached: 'select * from document where id = ?'
execute: "XXXXXXXXXXXX"
finish

The following is generated with a brand new statement handle:

prepare_cached:
'insert into document
(book,category,country,expiry,id,last_update,mnem,name,ode
c,route,source,tdate,ttimestamp,ttype,version)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?)'
$execute = ['SD','HR','UK','2017-07-08
14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
'534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];

I've even tried disconnecting just before the prepare_cached call and
reconnecting or opening a brand new connection for the prepare_cached
call - still no luck. So either, it is not the same bug or the
resolution is not quite right.

Thanks.

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

RE: problems inserting a utf8 timestamp with DBD::Oracle

Martin Evans wrote:
>>> Hoping someone can help me here as I'm really stuck with this. I
>>> have a large amount of code which reads some XML and inserts data
>>> from the XML into a database. One particular insert fails:
>>>=20
>> [snip]
>>> After much head scratching I discovered the problem P4 has utf8 set
>>> on it and downgrading it makes the code work i.e. when utf8::is_utf8
>>> returns 1 on the timestamp scalar it fails and if I do a
>>> utf8::downgrade(scalar) on it before calling execute it works.
>>=20
>> I think you've triggered this bug:
>> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html
>=20
> I've read that but I don't see the resolution.

There wasn't an official one, afaik. :-/


>> If I remember correctly, the fix is to set ora_ph_csform:
>>=20
>> use DBD::Oracle qw(SQLCS_NCHAR);
>> ...
>> # default for all future placeholders
>> $dbh->{'ora_ph_csform'} =3D SQLCS_NCHAR;
>=20
> I've tried that - no luck :-(

Surprisingly, setting ora_ph_csform as above actually *caused* the
problem for me. See below...


>> The bug only manifests when you bind a utf8-on value to a statement
>> parameter that was originally bound to a non-utf8 value.
>=20
> The whole DBI interaction is:
>=20
> $do =3D ['begin dbms_output.enable(:1); end;',undef,20000];
> do: 'alter session set nls_timestamp_format =3D 'yyyy-mm-dd =
hh24:mi:ss''
> do: 'alter session set nls_date_format =3D 'yyyy-mm-dd''
> prepare_cached: 'select * from document where id =3D ?'
> execute: "XXXXXXXXXXXX"
> finish
>=20
> The following is generated with a brand new statement handle:
>=20
> prepare_cached:
> 'insert into document
> (book,category,country,expiry,id,last_update,mnem,name,ode
> c,route,source,tdate,ttimestamp,ttype,version)
> values(?,?,?,?,?,?,?,?,?,?,?,?,?,
> ?,?)'
> $execute =3D ['SD','HR','UK','2017-07-08
> 14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
> '534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];
>=20
> I've even tried disconnecting just before the prepare_cached call and
> reconnecting or opening a brand new connection for the prepare_cached
> call - still no luck. So either, it is not the same bug or the
> resolution is not quite right.

I think you're right. It's a different bug, but I'd wager it's related.

The attached script reproduces the problem reliably on my system. There
are three lines in the script that will trigger the error when
uncommented: lines 11, 12, and 19.

The problem manifests on (at least):
DBI 1.50
DBD::Oracle 1.18, 1.19
Oracle client 9.2.0.4.0
Oracle server 9.2.0.7.0, 10.2.0.3.0

What's *really* strange is that if I remove the "dt" column from the
table, and run the script using only the "ts" timestamp column, there
is no error.

- Philip


#!/usr/bin/env perl
use strict;
use warnings;

use DBI;
use DBD::Oracle qw(SQLCS_NCHAR);
use Encode qw(decode);
use utf8;

# uncomment either one of these to trigger error
#$ENV{'NLS_LANG'} =3D 'AMERICAN_AMERICA.AL32UTF8';
#$ENV{'NLS_NCHAR'} =3D 'AL32UTF8';

my $dbh =3D DBI->connect( undef, undef, undef, # $ENV{DBI_DSN}, etc.
{ AutoCommit =3D> 1, RaiseError =3D> 1, PrintError =3D> 0 })
|| die $DBI::errstr;

# or, uncomment this to trigger error
#$dbh->{ora_ph_csform} =3D SQLCS_NCHAR;

eval { $dbh->do('drop table utf8test cascade constraints'); };
$dbh->do('create table utf8test (dt date, ts timestamp)');

my $fmt =3D 'YYYY-MM-DD HH24:MI:SS';
$dbh->do("alter session set nls_date_format =3D '$fmt'");
$dbh->do("alter session set nls_timestamp_format =3D '$fmt'");

my $dml =3D 'insert into utf8test (dt,ts) values (?,?)';
my $sth =3D $dbh->prepare($dml);

my $utf8_date =3D decode('utf8','2007-07-05 12:00:00');
$sth->execute($utf8_date, $utf8_date);

Re: problems inserting a utf8 timestamp with DBD::Oracle

I think the key is
"Oracle client 9.2.0.4.0" with "Oracle server 9.2.0.7.0, 10.2.0.3.0"

seems this only pepps its head up when using this combination

can you recreate it using the "instant client" and "Oracle server 9.2.0.7.0,
10.2.0.3.0"??

If it is a bug in Oracle (No!!! say it isn't so ... Not possiable??) we
will ever see a patch for it????

----- Original Message -----
From: "Garrett, Philip (MAN-Corporate)"
To: "Martin Evans" ;
Sent: Thursday, July 05, 2007 1:48 PM
Subject: RE: problems inserting a utf8 timestamp with DBD::Oracle


Martin Evans wrote:
>>> Hoping someone can help me here as I'm really stuck with this. I
>>> have a large amount of code which reads some XML and inserts data
>>> from the XML into a database. One particular insert fails:
>>>
>> [snip]
>>> After much head scratching I discovered the problem P4 has utf8 set
>>> on it and downgrading it makes the code work i.e. when utf8::is_utf8
>>> returns 1 on the timestamp scalar it fails and if I do a
>>> utf8::downgrade(scalar) on it before calling execute it works.
>>
>> I think you've triggered this bug:
>> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html
>
> I've read that but I don't see the resolution.

There wasn't an official one, afaik. :-/


>> If I remember correctly, the fix is to set ora_ph_csform:
>>
>> use DBD::Oracle qw(SQLCS_NCHAR);
>> ...
>> # default for all future placeholders
>> $dbh->{'ora_ph_csform'} = SQLCS_NCHAR;
>
> I've tried that - no luck :-(

Surprisingly, setting ora_ph_csform as above actually *caused* the
problem for me. See below...


>> The bug only manifests when you bind a utf8-on value to a statement
>> parameter that was originally bound to a non-utf8 value.
>
> The whole DBI interaction is:
>
> $do = ['begin dbms_output.enable(:1); end;',undef,20000];
> do: 'alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss''
> do: 'alter session set nls_date_format = 'yyyy-mm-dd''
> prepare_cached: 'select * from document where id = ?'
> execute: "XXXXXXXXXXXX"
> finish
>
> The following is generated with a brand new statement handle:
>
> prepare_cached:
> 'insert into document
> (book,category,country,expiry,id,last_update,mnem,name,ode
> c,route,source,tdate,ttimestamp,ttype,version)
> values(?,?,?,?,?,?,?,?,?,?,?,?,?,
> ?,?)'
> $execute = ['SD','HR','UK','2017-07-08
> 14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
> '534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];
>
> I've even tried disconnecting just before the prepare_cached call and
> reconnecting or opening a brand new connection for the prepare_cached
> call - still no luck. So either, it is not the same bug or the
> resolution is not quite right.

I think you're right. It's a different bug, but I'd wager it's related.

The attached script reproduces the problem reliably on my system. There
are three lines in the script that will trigger the error when
uncommented: lines 11, 12, and 19.

The problem manifests on (at least):
DBI 1.50
DBD::Oracle 1.18, 1.19
Oracle client 9.2.0.4.0
Oracle server 9.2.0.7.0, 10.2.0.3.0

What's *really* strange is that if I remove the "dt" column from the
table, and run the script using only the "ts" timestamp column, there
is no error.

- Philip


#!/usr/bin/env perl
use strict;
use warnings;

use DBI;
use DBD::Oracle qw(SQLCS_NCHAR);
use Encode qw(decode);
use utf8;

# uncomment either one of these to trigger error
#$ENV{'NLS_LANG'} = 'AMERICAN_AMERICA.AL32UTF8';
#$ENV{'NLS_NCHAR'} = 'AL32UTF8';

my $dbh = DBI->connect( undef, undef, undef, # $ENV{DBI_DSN}, etc.
{ AutoCommit => 1, RaiseError => 1, PrintError => 0 })
|| die $DBI::errstr;

# or, uncomment this to trigger error
#$dbh->{ora_ph_csform} = SQLCS_NCHAR;

eval { $dbh->do('drop table utf8test cascade constraints'); };
$dbh->do('create table utf8test (dt date, ts timestamp)');

my $fmt = 'YYYY-MM-DD HH24:MI:SS';
$dbh->do("alter session set nls_date_format = '$fmt'");
$dbh->do("alter session set nls_timestamp_format = '$fmt'");

my $dml = 'insert into utf8test (dt,ts) values (?,?)';
my $sth = $dbh->prepare($dml);

my $utf8_date = decode('utf8','2007-07-05 12:00:00');
$sth->execute($utf8_date, $utf8_date);

Re: problems inserting a utf8 timestamp with DBD::Oracle

John Scoles wrote:
> I think the key is
> "Oracle client 9.2.0.4.0" with "Oracle server 9.2.0.7.0, 10.2.0.3.0"
>
Don't think so as I'm not using Oracle 9.
> seems this only pepps its head up when using this combination
>
> can you recreate it using the "instant client" and "Oracle server
> 9.2.0.7.0, 10.2.0.3.0"??
>
yes
> If it is a bug in Oracle (No!!! say it isn't so ... Not possiable??)
> we will ever see a patch for it????
>
Well I'm not saying it isn't a bug in Oracle (I have various workarounds
for other bugs - especially problems with constraints) but this one is a
real problem I cannot easily workaround as really I need a timestamp and
a date (see below).

Client is Oracle 10.2.0 XE
Server is 10.2.0 XE or 10.2.0 Enterprise (neither work).
I have reproduced the problem in a simple perl script but only when
ora_ph_csform is set to SQLCS_NCHAR (which I am not doing in my larger
more complex code).

use DBI;
use DBIx::Log4perl;
use BET::DB;
use Sys::SigAction qw( set_sig_handler );
use DBD::Oracle qw(SQLCS_NCHAR);

$h = DBI->connect("dbi:Oracle:xxx","xxx","xxx", {ChopBlanks => 1, Sho
wErrorStatement => 0, FetchHashKeyName => 'NAME_lc', LongReadLen => 256,
Warn =>
1, AutoCommit => 1, PrintError => 1, RaiseError => 1});
$h->{'ora_ph_csform'} = SQLCS_NCHAR;
$h->do(q{alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss'});
$h->do(q{alter session set nls_date_format = 'yyyy-mm-dd'});

eval {$h->do(q{drop table fred});};
$h->do(q{create table fred (a date, b timestamp)});
$s = $h->prepare_cached("insert into fred (b,a) values(?,?)");
$x = "2017-07-08 14:10:00";
$s->execute($x,"2007-07-01");

#eval {$h->do(q{drop table fred});};
#$h->do(q{create table fred (a timestamp, b timestamp)});
#$s = $h->prepare_cached("insert into fred (b,a) values(?,?)");
#$x = "2017-07-08 14:10:00";
#$s->execute($x,"2007-07-01");

If you run the above it fails with the month error. If you then comment
out the 2nd block of code and uncomment the 3rd block of code it works
i.e it appears it does not like having a timestamp and a date in the
same insert.

This is not my exact problem as I wasn't setting SQLCS_NCHAR until
Philip mentioned it but I am inserting into a row a timestamp and a
date. If I remove either the timestamp or the date from the insert and
don't set SQLCS_NCHAR it works. If I change the date field to a
timestamp field it works.

The really sad thing (and I hate saying this) is that the code was
designed to work with many databases (because we had not made our mind
up and were still prototyping) and works fine with mysql! We made our
mind up and it fell on Oracle (surprise) after having to made quite a
lot of changes to DBD::mysql (earlier this year) and a few to DBD::db2.
It was this project that fueled my patches to DBD::Oracle and DBI for
array bound columns, all those patches for unicode etc to DBD::mysql and
the ones to DBD::db2 for finish problems. I'm really stuck now but still
looking for a resolution other than change my Date column to Timestamp.

I've seen Philips other posting and will respond to that later.

Martin
> ----- Original Message ----- From: "Garrett, Philip (MAN-Corporate)"
>
> To: "Martin Evans" ;
> Sent: Thursday, July 05, 2007 1:48 PM
> Subject: RE: problems inserting a utf8 timestamp with DBD::Oracle
>
>
> Martin Evans wrote:
>>>> Hoping someone can help me here as I'm really stuck with this. I
>>>> have a large amount of code which reads some XML and inserts data
>>>> from the XML into a database. One particular insert fails:
>>>>
>>> [snip]
>>>> After much head scratching I discovered the problem P4 has utf8 set
>>>> on it and downgrading it makes the code work i.e. when utf8::is_utf8
>>>> returns 1 on the timestamp scalar it fails and if I do a
>>>> utf8::downgrade(scalar) on it before calling execute it works.
>>>
>>> I think you've triggered this bug:
>>> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html
>>
>> I've read that but I don't see the resolution.
>
> There wasn't an official one, afaik. :-/
>
>
>>> If I remember correctly, the fix is to set ora_ph_csform:
>>>
>>> use DBD::Oracle qw(SQLCS_NCHAR);
>>> ...
>>> # default for all future placeholders
>>> $dbh->{'ora_ph_csform'} = SQLCS_NCHAR;
>>
>> I've tried that - no luck :-(
>
> Surprisingly, setting ora_ph_csform as above actually *caused* the
> problem for me. See below...
>
>
>>> The bug only manifests when you bind a utf8-on value to a statement
>>> parameter that was originally bound to a non-utf8 value.
>>
>> The whole DBI interaction is:
>>
>> $do = ['begin dbms_output.enable(:1); end;',undef,20000];
>> do: 'alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss''
>> do: 'alter session set nls_date_format = 'yyyy-mm-dd''
>> prepare_cached: 'select * from document where id = ?'
>> execute: "XXXXXXXXXXXX"
>> finish
>>
>> The following is generated with a brand new statement handle:
>>
>> prepare_cached:
>> 'insert into document
>> (book,category,country,expiry,id,last_update,mnem,name,ode
>> c,route,source,tdate,ttimestamp,ttype,version)
>> values(?,?,?,?,?,?,?,?,?,?,?,?,?,
>> ?,?)'
>> $execute = ['SD','HR','UK','2017-07-08
>> 14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
>> '534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];
>>
>> I've even tried disconnecting just before the prepare_cached call and
>> reconnecting or opening a brand new connection for the prepare_cached
>> call - still no luck. So either, it is not the same bug or the
>> resolution is not quite right.
>
> I think you're right. It's a different bug, but I'd wager it's related.
>
> The attached script reproduces the problem reliably on my system. There
> are three lines in the script that will trigger the error when
> uncommented: lines 11, 12, and 19.
>
> The problem manifests on (at least):
> DBI 1.50
> DBD::Oracle 1.18, 1.19
> Oracle client 9.2.0.4.0
> Oracle server 9.2.0.7.0, 10.2.0.3.0
>
> What's *really* strange is that if I remove the "dt" column from the
> table, and run the script using only the "ts" timestamp column, there
> is no error.
>
> - Philip
>
>
> #!/usr/bin/env perl
> use strict;
> use warnings;
>
> use DBI;
> use DBD::Oracle qw(SQLCS_NCHAR);
> use Encode qw(decode);
> use utf8;
>
> # uncomment either one of these to trigger error
> #$ENV{'NLS_LANG'} = 'AMERICAN_AMERICA.AL32UTF8';
> #$ENV{'NLS_NCHAR'} = 'AL32UTF8';
>
> my $dbh = DBI->connect( undef, undef, undef, # $ENV{DBI_DSN}, etc.
> { AutoCommit => 1, RaiseError => 1, PrintError => 0 })
> || die $DBI::errstr;
>
> # or, uncomment this to trigger error
> #$dbh->{ora_ph_csform} = SQLCS_NCHAR;
>
> eval { $dbh->do('drop table utf8test cascade constraints'); };
> $dbh->do('create table utf8test (dt date, ts timestamp)');
>
> my $fmt = 'YYYY-MM-DD HH24:MI:SS';
> $dbh->do("alter session set nls_date_format = '$fmt'");
> $dbh->do("alter session set nls_timestamp_format = '$fmt'");
>
> my $dml = 'insert into utf8test (dt,ts) values (?,?)';
> my $sth = $dbh->prepare($dml);
>
> my $utf8_date = decode('utf8','2007-07-05 12:00:00');
> $sth->execute($utf8_date, $utf8_date);
>
>

Re: problems inserting a utf8 timestamp with DBD::Oracle

Garrett, Philip (MAN-Corporate) wrote:
> Martin Evans wrote:
>
>>>> Hoping someone can help me here as I'm really stuck with this. I
>>>> have a large amount of code which reads some XML and inserts data
>>>> from the XML into a database. One particular insert fails:
>>>>
>>>>
>>> [snip]
>>>
>>>> After much head scratching I discovered the problem P4 has utf8 set
>>>> on it and downgrading it makes the code work i.e. when utf8::is_utf8
>>>> returns 1 on the timestamp scalar it fails and if I do a
>>>> utf8::downgrade(scalar) on it before calling execute it works.
>>>>
>>> I think you've triggered this bug:
>>> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html
>>>
>> I've read that but I don't see the resolution.
>>
>
> There wasn't an official one, afaik. :-/
>
Just checking. I think I got the wrong end of the stick - I thought you
were meaning setting SQLCS_NCHAR fixes the problem but it appears it is
the other way around.

>>> If I remember correctly, the fix is to set ora_ph_csform:
>>>
>>> use DBD::Oracle qw(SQLCS_NCHAR);
>>> ...
>>> # default for all future placeholders
>>> $dbh->{'ora_ph_csform'} = SQLCS_NCHAR;
>>>
>> I've tried that - no luck :-(
>>
>
> Surprisingly, setting ora_ph_csform as above actually *caused* the
> problem for me. See below...
>
>
It makes my problems worse also as I was not setting it in the first place.
>>> The bug only manifests when you bind a utf8-on value to a statement
>>> parameter that was originally bound to a non-utf8 value.
>>>
>> The whole DBI interaction is:
>>
>> $do = ['begin dbms_output.enable(:1); end;',undef,20000];
>> do: 'alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss''
>> do: 'alter session set nls_date_format = 'yyyy-mm-dd''
>> prepare_cached: 'select * from document where id = ?'
>> execute: "XXXXXXXXXXXX"
>> finish
>>
>> The following is generated with a brand new statement handle:
>>
>> prepare_cached:
>> 'insert into document
>> (book,category,country,expiry,id,last_update,mnem,name,ode
>> c,route,source,tdate,ttimestamp,ttype,version)
>> values(?,?,?,?,?,?,?,?,?,?,?,?,?,
>> ?,?)'
>> $execute = ['SD','HR','UK','2017-07-08
>> 14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
>> '534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];
>>
>> I've even tried disconnecting just before the prepare_cached call and
>> reconnecting or opening a brand new connection for the prepare_cached
>> call - still no luck. So either, it is not the same bug or the
>> resolution is not quite right.
>>
>
> I think you're right. It's a different bug, but I'd wager it's related.
>
>
See my other post - it looks remarkably similar to me now. For a start
your example has a timestamp and a date as mine does.

> The attached script reproduces the problem reliably on my system. There
> are three lines in the script that will trigger the error when
> uncommented: lines 11, 12, and 19.
>
> The problem manifests on (at least):
> DBI 1.50
> DBD::Oracle 1.18, 1.19
> Oracle client 9.2.0.4.0
> Oracle server 9.2.0.7.0, 10.2.0.3.0
>
> What's *really* strange is that if I remove the "dt" column from the
> table, and run the script using only the "ts" timestamp column, there
> is no error.
>
>
Same for me. I can insert a row containing only dates or only timestamps
no problem - soon as I mix them - bang!
What led me astray at first is that if I have a table with a timestamp
and a date and the timestamp is in the insert list first, Oracle reports
the timestamp parameter in error - so naturally you are drawn to that.
If you then remove the date in the insert problem goes away so you start
thinking it is the date field.
> - Philip
>
>
> #!/usr/bin/env perl
> use strict;
> use warnings;
>
> use DBI;
> use DBD::Oracle qw(SQLCS_NCHAR);
> use Encode qw(decode);
> use utf8;
>
> # uncomment either one of these to trigger error
> #$ENV{'NLS_LANG'} = 'AMERICAN_AMERICA.AL32UTF8';
> #$ENV{'NLS_NCHAR'} = 'AL32UTF8';
>
> my $dbh = DBI->connect( undef, undef, undef, # $ENV{DBI_DSN}, etc.
> { AutoCommit => 1, RaiseError => 1, PrintError => 0 })
> || die $DBI::errstr;
>
> # or, uncomment this to trigger error
> #$dbh->{ora_ph_csform} = SQLCS_NCHAR;
>
> eval { $dbh->do('drop table utf8test cascade constraints'); };
> $dbh->do('create table utf8test (dt date, ts timestamp)');
>
> my $fmt = 'YYYY-MM-DD HH24:MI:SS';
> $dbh->do("alter session set nls_date_format = '$fmt'");
> $dbh->do("alter session set nls_timestamp_format = '$fmt'");
>
> my $dml = 'insert into utf8test (dt,ts) values (?,?)';
> my $sth = $dbh->prepare($dml);
>
> my $utf8_date = decode('utf8','2007-07-05 12:00:00');
> $sth->execute($utf8_date, $utf8_date);
>
>
This is remarkably similar to the final test case I came up with that
does not work. I've run the above exactly as it stands and it fails with
"ORA-01843: not a valid month" i.e. I ran without uncommenting anything
HOWEVER, my NLS_LANG is already set to AMERICAN_AMERICA.AL32UTF8 before
the script was run (as per advice on making unicode/utf8 work with
Oracle and DBD::Oracle).

Out of interest what did you end up doing in the end - make all your
date/times timestamps?

Thanks again for the input Philip.

Martin

RE: problems inserting a utf8 timestamp with DBD::Oracle

Martin J. Evans wrote:
> Garrett, Philip (MAN-Corporate) wrote:
>> Martin Evans wrote:
>>=20
>>>>> Hoping someone can help me here as I'm really stuck with this. I
>>>>> have a large amount of code which reads some XML and inserts data
>>>>> from the XML into a database. One particular insert fails:
>>>>>=20
>>>>>=20
>>>> [snip]
>>>>=20
>>>>> After much head scratching I discovered the problem P4 has utf8
>>>>> set on it and downgrading it makes the code work i.e. when
>>>>> utf8::is_utf8 returns 1 on the timestamp scalar it fails and if I
>>>>> do a utf8::downgrade(scalar) on it before calling execute it
>>>>> works.=20
>>>>>=20
>>>> I think you've triggered this bug:
>>>> http://www.mail-archive.com/dbi-users@perl.org/msg26927.html
>>>>=20
>>> I've read that but I don't see the resolution.
>>>=20
>>=20
>> There wasn't an official one, afaik. :-/
>>=20
> Just checking. I think I got the wrong end of the stick - I thought
> you were meaning setting SQLCS_NCHAR fixes the problem but it appears
> it is the other way around.

I (wrongly) assumed that your problem and mine were the same. Setting
SQLCS_NCHAR fixed my particular problem.


>>>> If I remember correctly, the fix is to set ora_ph_csform:
>>>>=20
>>>> use DBD::Oracle qw(SQLCS_NCHAR);
>>>> ...
>>>> # default for all future placeholders
>>>> $dbh->{'ora_ph_csform'} =3D SQLCS_NCHAR;
>>>>=20
>>> I've tried that - no luck :-(
>>>=20
>>=20
>> Surprisingly, setting ora_ph_csform as above actually *caused* the
>> problem for me. See below...=20
>>=20
>>=20
> It makes my problems worse also as I was not setting it in the first
> place.=20
>
>>>> The bug only manifests when you bind a utf8-on value to a statement
>>>> parameter that was originally bound to a non-utf8 value.
>>>>=20
>>> The whole DBI interaction is:
>>>=20
>>> $do =3D ['begin dbms_output.enable(:1); end;',undef,20000];
>>> do: 'alter session set nls_timestamp_format =3D 'yyyy-mm-dd
>>> hh24:mi:ss'' do: 'alter session set nls_date_format =3D =
'yyyy-mm-dd''
>>> prepare_cached: 'select * from document where id =3D ?'
>>> execute: "XXXXXXXXXXXX"
>>> finish
>>>=20
>>> The following is generated with a brand new statement handle:
>>>=20
>>> prepare_cached:
>>> 'insert into document
>>> (book,category,country,expiry,id,last_update,mnem,name,ode
>>> c,route,source,tdate,ttimestamp,ttype,version)
>>> values(?,?,?,?,?,?,?,?,?,?,?,?,?,
>>> ?,?)'
>>> $execute =3D ['SD','HR','UK','2017-07-08
>>> 14:10:00','XXXXXXXXXXX','1183290197812','ES','XXXXX','N',
>>>
'534453','XXXXXXXXXX','2007-07-01','1183269593843','XXXXXX', 'XXXXX'];
>>>=20
>>> I've even tried disconnecting just before the prepare_cached call
>>> and reconnecting or opening a brand new connection for the
>>> prepare_cached call - still no luck. So either, it is not the same
>>> bug or the resolution is not quite right.
>>>=20
>>=20
>> I think you're right. It's a different bug, but I'd wager it's
>> related.=20
>>=20
>>=20
> See my other post - it looks remarkably similar to me now. For a start
> your example has a timestamp and a date as mine does.
>=20
>> The attached script reproduces the problem reliably on my system.
>> There are three lines in the script that will trigger the error when
>> uncommented: lines 11, 12, and 19.
>>=20
>> The problem manifests on (at least):
>> DBI 1.50
>> DBD::Oracle 1.18, 1.19
>> Oracle client 9.2.0.4.0
>> Oracle server 9.2.0.7.0, 10.2.0.3.0
>>=20
>> What's *really* strange is that if I remove the "dt" column from the
>> table, and run the script using only the "ts" timestamp column,
>> there is no error.=20
>>=20
> =20
> Same for me. I can insert a row containing only dates or only
> timestamps no problem - soon as I mix them - bang! What led me astray
> at first is that if I have a table with a timestamp and a date and the
> timestamp is in the insert list first, Oracle reports the timestamp
> parameter in error - so naturally you are drawn to that.
> If you then remove the date in the insert problem goes away so you
> start thinking it is the date field.
>
>>=20
>> #!/usr/bin/env perl
>> use strict;
>> use warnings;
>>=20
>> use DBI;
>> use DBD::Oracle qw(SQLCS_NCHAR);
>> use Encode qw(decode);
>> use utf8;
>>=20
>> # uncomment either one of these to trigger error
>> #$ENV{'NLS_LANG'} =3D 'AMERICAN_AMERICA.AL32UTF8';
>> #$ENV{'NLS_NCHAR'} =3D 'AL32UTF8';
>>=20
>> my $dbh =3D DBI->connect( undef, undef, undef, # $ENV{DBI_DSN}, etc.
>> { AutoCommit =3D> 1, RaiseError =3D> 1, PrintError =3D> 0 }) =
||
>> die $DBI::errstr;=20
>>=20
>> # or, uncomment this to trigger error
>> #$dbh->{ora_ph_csform} =3D SQLCS_NCHAR;
>>=20
>> eval { $dbh->do('drop table utf8test cascade constraints'); };
>> $dbh->do('create table utf8test (dt date, ts timestamp)');
>>=20
>> my $fmt =3D 'YYYY-MM-DD HH24:MI:SS';
>> $dbh->do("alter session set nls_date_format =3D '$fmt'");
>> $dbh->do("alter session set nls_timestamp_format =3D '$fmt'");
>>=20
>> my $dml =3D 'insert into utf8test (dt,ts) values (?,?)';
>> my $sth =3D $dbh->prepare($dml);
>>=20
>> my $utf8_date =3D decode('utf8','2007-07-05 12:00:00');
>> $sth->execute($utf8_date, $utf8_date);
>>=20
>>=20
> This is remarkably similar to the final test case I came up with that
> does not work. I've run the above exactly as it stands and it fails
> with "ORA-01843: not a valid month" i.e. I ran without uncommenting
> anything HOWEVER, my NLS_LANG is already set to
> AMERICAN_AMERICA.AL32UTF8 before the script was run (as per advice on
> making unicode/utf8 work with Oracle and DBD::Oracle).
>=20
> Out of interest what did you end up doing in the end - make all your
> date/times timestamps?

Luckily for me, my problem was solved using the ora_ph_csform setting. I
suspect I haven't had your problem because the timestamps I'm using are
not from the outside world -- they're generated from DateTime objects
and won't have the utf8 flag set.

- Philip