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)" <Philip.Garrett@xxxxxxxxxxx>
To: "Martin Evans" <martin.evans@xxxxxxxxxxxx>; <dbi-users@xxxxxxxx>
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@xxxxxxxx/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);



.

Relevant Pages

  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... this is not Perl stuff but Oracle PL/SQL. ... deleting them in same proc ... create mytable (id int, created timestamp); ... Perl calls the first one to get the unique ids and the timestamp ...
    (perl.dbi.users)
  • Re: Date different
    ... >> oracle changed the default types returned for their newest oracle drivers, ... What is going on with DATE and TIMESTAMP? ... The more obvious mapping to java.sql.Date was somewhat ... You set the system property by including a -D option in your java command ...
    (comp.lang.java.programmer)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... between my desktop and my db server. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • Re: DBD Oracle 1.20 ORA-24334 Error
    ... On the premise instant client and also tried to run perl Makefile.PL -V ... the status after having connected to Oracle ... Failed 2/33 tests, 93.94% okay ...
    (perl.dbi.users)