freddo411 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm using Class::DBI and I'm trying to update a field in my Oracle DB that is of "Date" type. My routines has been working well for numbers and strings but I'm having some difficult with the date datatype.

In the past, when I was just using DBI I would use some SQL like: 

update tablename set foo = 'bar', 
date = to_date( '03/03/2004', 'MM/DD/YYYY' )
where id = '1'

[download]

With Class::DBI I've been passing a hash into it where the hash has column/value pairs. 

        # Do the DB insert
        $dn_entry->set( %dn_out  );
        $dn_entry->update;
        $dn_entry->dbi_commit();

[download]

I tried filling my hash with the same to_date function call string like above and I got this error.... 

DB error: Can't update 50025: DBD::Oracle::
st execute failed: ORA-01858: a non-numeric character was found where 
+a numeric 
was expected (DBD ERROR: OCIStmtExecute) [for statement ``UPDATE Domai
+nNames SET  domainname = ?, expire_date = ?, dn_id = ? WHERE  dn_id=?
+ '']) at ../Modules/site_perl/5.005/Ima/DBI.pm line 720.

[download]

Any ideas how to use Class::DBI and oracle together to set date field values?

Cheers

-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday

Replies are listed 'Best First'.
Re: Class::DBI / Oracle date field Q
by blokhead (Monsignor) on May 07, 2004 at 00:06 UTC
    Class::DBI is (wisely) quoting all of your input before it puts it in an SQL query. What the database sees is something like this: 
    update tablename set foo = 'bar', 
date = 'to_date( \'03/03/2004\', \'MM/DD/YYYY\' )'
where id = '1'

    [download]
    It tries to set the date column to the string "to_date( '03/03/2004', 'MM/DD/YYYY' )" and fails.

    You'll have to do any date calculations & conversions yourself and pass in the result using whatever format Oracle recognizes (unix epoch? YYYYMMDDHHMMSS format?). Or maybe even use attribute inflation/deflation to make your life a little easier. Just pass in (for instance) a Time::Piece object instead of a date string. There's even an example of this using Time::Piece in Class::DBI's POD.

    Bottom line: For safety reasons, anything you set your Class::DBI objects' fields to will never be interperted as SQL. Otherwise, its queries would fail every time you did something involving quotation marks.

    blokhead

[reply]
[d/l]
      Thanks for the helful advice. At the moment, under time pressure, I don't grok the details of inflation, but I get the general idea.

      It seems that with Class::DBI only numbers and strings can be passed into the DB.

      Here's a follow up so that everyone knows what I did to fix the problem:

      Oracle will by default turn a string into a date datatype based upon only its very strict idea of what a date string should be patterned like. I am using Oracle 8i, which will allow me to set the default date pattern for my session. I will do this so that I have a predictable pattern to make my strings. I do this in my Class::DBI setup like so: 

        package CDNR::DBI;
  use base 'Class::DBI::Oracle';
  use Config::General;
  my %hostCF = ParseConfig("../mymodules/config.$hostname");

  CDNR::DBI->set_db('Main', $hostCF{'dbconnect'},
                             $hostCF{'dbname'},
                             $hostCF{'dbpw'});

  my $dbh = CDNR::DBI->db_Main;
  my $qs = "Alter session set nls_date_format = 'Mon-DD-YYYY'";
  $dbh->do($qs) or die "DB err alter date";

      [download]

      At this point I can pass a string to the Class::DBI for inserting into a "date" database field as longs as the string looks like Mon-DD-YYYY.

      cheers

      -------------------------------------
      Nothing is too wonderful to be true
      -- Michael Faraday

[reply]
[d/l]
      As I understand the DBI, the quote function is automaticly called for prepare() but not for do(). So in this case passing a $q = $dbh->quote($q); before the do() instead of trying to \ the quotes might help.
[reply]
Re: Class::DBI / Oracle date field Q
by pelagic (Priest) on May 07, 2004 at 06:30 UTC
    In my tests this worked fine:
    my $sql = qq[
    UPDATE foo_table
    SET    foo_date =  to_date( '03/03/2004', 'MM/DD/YYYY' )
    WHERE  id = 1
];

    [download]
     update
    Sorry, I didn't read your question correctly.

    pelagic
[reply]
[d/l]
      How? That's just a scalar variable. The SQL it contains may work on its own, but it won't in the OPs context.
[reply]
        There might be a straightforward way, however the workaround will be to issue sql alter session set nls_date_format='DD/MON/YYYY'; and then your insert/update.
[reply]
[d/l]

Back to Seekers of Perl Wisdom