Sybase TEXT field updates..
am 04.10.2007 22:40:25 von amonotodHello all,
So, I'm trying to do an insert into a two column table, with VARCHAR(40) key column and a TEXT column as the second field. The below code should work, but for some reason is not...
I have some other code that does work, but it doesn't use placeholders. Is that the issue? The code that does work actually updates the same table I'm trying to update below. The problem is, with this set of data, I cannot guarantee that there will be no quotes, so I feel that I need to use placeholders...
my $sqlStatement = "insert into ATTRIBUTE (Name, Value) values (?,?)";
my $updateStatement = "update ATTRIBUTE set Value = ? where Name = ?";
if (uc($arg_db_type) eq "SYBASE") {
$sqlStatement = "insert into ATTRIBUTE (NAME) values (?)";
}
my $sth = $dbh->prepare($sqlStatement);
my $sth2 = $dbh->prepare($updateStatement);
foreach my $comment (@comments) {
$comment =~ s/()//g;
$comment =~ s/(^\s+|\s+$)//;
if ($comment =~ m/DISTRIBUTION STATEMENT/i) {
if (uc($arg_db_type) eq "SYBASE") {
unless ($sth->execute('DISTRIBUTION_NOTICE')) { logNdie("inserting Destruction comment in ATTRIBUTE"); }
unless ($sth2->execute($comment,'DISTRIBUTION_NOTICE')) { logNdie("updating Destruction comment in ATTRIBUTE"); }
} else {
unless ($sth->execute('DISTRIBUTION_NOTICE',$comment)) { logNdie("inserting Destruction comment in ATTRIBUTE"); }
}
log_it("inserting Destruction comment in ATTRIBUTE");
}
foreach my $col (@cols) {
if (exists $textTables{$tablemap}) { #There is a table with a VARCHAR > 255, converted to TEXT for Sybase.
if (uc( $textTables{$tablemap}{$colcount}[1] ) eq "TEXT") {
if ($rets[$colcount] ne "") {
push (@updArrays, "update $tablemap set $col = '$rets[$colcount]'");
}
} else {
if ($rets[$colcount] eq "") { push (@insArrays, undef); }
else { push (@insArrays, $rets[$colcount]); }
}
} else { #There is no table with a VARCHAR > 255, converted to TEXT for Sybase.
if ($rets[$colcount] eq "") { push (@insArrays, undef); }
else { push (@insArrays, $rets[$colcount]); }
}
$colcount++;
}
unless ($DataInsert->execute(@insArrays )) {
print LOGFILE "\nErrors were encountered during data load...\n";
print LOGFILE "errors: ". $DataInsert->errstr ."\n";
}
if (exists $textTables{$tablemap}) { #There is a table with a VARCHAR > 255, converted to TEXT for Sybase.
foreach (@updArrays) {
my $updStatement = $_ ." where $cols[0] = $rets[0]";
$DataUpdate = $dbh2->prepare($updStatement);
unless ($DataUpdate->execute()) {
print LOGFILE "\nErrors were encountered during data load...\n";
print LOGFILE "errors: ". $DataUpdate->errstr ."\n";
}
}
}
Thanks for any tips...
amonotod
--
`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|