RE: DBD-Oracle error when inserting a date

[Reidy, Ron]

>From your first post - 

Is your data wrapped in " chars?

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-----Original Message-----
From: Bart Kelsey [mailto:[EMAIL PROTECTED]
Sent: Friday, August 05, 2005 1:59 PM
To: Reidy, Ron
Cc: dbi-users@perl.org
Subject: Re: DBD-Oracle error when inserting a date


Reidy, Ron wrote:
> Can you show more of a code snippet (your SQL statement, prepare(), bind(), 
> and execute() calls)?
>
>   
The purpose of this program is to inert arbitrary data given a table 
definition file, with the column names listed in it, and another file 
with the corresponding data, row by row... 

The SQL statement that causes the error, once it is built, looks like this:

insert into abbrev (code, type, num, description, who_added, date_added) 
values (?, ?, ?, ?, ?, to_date(?, 'MM/DD/YYYY'))

I've marked my prepare and execute statements in the code.  Note that 
I'm binding in the execute statement.

********

foreach $table (@tables) {
  my(@fields, @data, $line, %datatype);
  open(IN, "<:utf8", "${table}.dat");
  while($line = <IN>) {
    chomp($line);
    while($line =~ /\\$/) {
      $line =~ s/\\$//;
      $line .= "\n" . <IN>;
      chomp($line);
    }
    $line =~ s/\\\|/\007/sg;
    push(@data, $line);
  }
  close(IN);
 
  my($sth) = $dbh->prepare("select column_name, data_type from 
user_tab_cols where lower(table_name) = lower(?)");
  $sth->execute($table);
  while(my($df, $dt) = $sth->fetchrow_array) {
    $datatype{$df} = $dt;
    print("$df: $dt\n");
  }
 
  open(IN, "<:utf8", "${table}.dbload");
  <IN>;
  $line = <IN>;
  if($line =~ /\(([^(]*)\)/) {
    @fields = split(/,/, $1);
  }
  print("$table: ", join(", ", @fields), "\n");
  close(IN);
 
  $dbh->do("delete from $table");

  my($sql) = "insert into $table (";
  $sql .= join(", ", @fields);
  $sql .= ") values (";
  my(@q, $x);
  foreach $x (@fields) {
    $x =~ s/\*//g;
    $x = uc($x);
    #if($x =~ /\*$/) {
    if($datatype{$x} =~ /TIMESTAMP/) {
      push(@q, "to_timestamp(?, 'YYYY-MM-DD HH24:MI:SS')");
    } elsif($datatype{$x} eq "DATE") {
      push(@q, "to_date(?, 'MM/DD/YYYY')");
    } else {
      push(@q, "?");
    }
  }
  $sql .= join(", ", @q);
  $sql .= ")";
  $sql =~ s/\*//g;

  ##########  PREPARE STATEMENT HERE ###########
  my($sth2) = $dbh->prepare($sql);
 
  foreach $line (@data) {
    #print("$line\n");
    my(@row) = split(/\|/, $line);
    my(@dat);
    for(my($i) = 0; $i < scalar(@fields); $i++) {
      $row[$i] =~ s/\007/\|/g;
      push(@dat, $row[$i]);
    }
    #############  EXECUTE STATEMENT HERE ############
    $sth2->execute(@dat);
  }
 
  $dbh->commit;
}


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is 
intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.