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;
}
