> From: "Shah, Urmil" <[EMAIL PROTECTED]>
> Date: 2004/08/13 Fri PM 04:42:54 GMT
> Need help in trying to insert an sql statement.
As everyone else has been saying, you need to use placeholders.
See my script below... Note that I tested with MSSQL, but
> Regards,
> Urmil
Regards,
amonotod
#!perl -w
use strict;
eval ("use DBI");
if ($@) { die "This system does not have the DBI installed....\n"; }
my ($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,
$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,
$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err);
# Note that I have declared the variables, but not assigned any value to them...
my %H_CNT_BY_PGM;
my $olimit = 10;
while ($olimit > 0) {
my $type = "";
my $typeLen = rand(10)+1;
while ($typeLen > 0) { $type .= chr((rand(26)+97)); $typeLen--;}
#print "$type \n";
my $ilimit = 10;
while ($ilimit > 0) {
my $pgm = "";
my $pgmLen = rand(10)+1;
while ($pgmLen > 0) { $pgm .= chr((rand(26)+97)); $pgmLen--;}
#print "\t$pgm \n";
$H_CNT_BY_PGM{$type}{$pgm} = 1;
$ilimit--;
}
$olimit--;
}
my $dbh = DBI->connect( "dbi:Oracle:host=ORADEV9I;sid=ORADEV9I", 'scott', 'tiger',
{RaiseError => 0, AutoCommit => 1} );
my $sqlStatement = "DROP TABLE OPER_QUERY";
my $sth = $dbh->prepare($sqlStatement) or die("Cannot prepare sql:$dbh->errstr\n");
unless ($sth->execute) {
print "errors: $dbh->errstr \n";
print "\nErrors were encountered during table drop, exiting...\n";
}
$sqlStatement = <<SQL;
create table OPER_QUERY (
end_date varchar (1),
site varchar (1),
tester_type varchar (15),
program varchar (15),
total varchar (1),
oper_yes varchar (1),
rmenu_yes varchar (1),
opr_dev_type varchar (1),
opr_test_type varchar (1),
opr_temp_type varchar (1),
opr_lot_type varchar (1),
rmenu_dev_type varchar (1),
rmenu_test_type varchar (1),
rmenu_temp_type varchar (1),
rmenu_lot_type varchar (1)
)
SQL
$sth = $dbh->prepare($sqlStatement) or die("Cannot prepare sql:$dbh->errstr\n");
unless ($sth->execute) {
print "errors: $dbh->errstr \n";
print "\nErrors were encountered during table create, exiting...\n";
}
$sqlStatement = "insert into OPER_QUERY (end_date,site,tester_type,program,".
"total,oper_yes,rmenu_yes,opr_dev_type,opr_test_type,".
"opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type,".
"rmenu_temp_type,rmenu_lot_type) values (?,?,?,?,?,?,?,?,?,".
"?,?,?,?,?,?)";
$sth = $dbh->prepare($sqlStatement) or die("Cannot prepare sql:$dbh->errstr\n");
foreach my $type(sort keys %H_CNT_BY_PGM){
foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){
$sth->execute($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,
$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,
$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err)
or die"Cannot send the data... $dbh->errstr\n";
}
}
--
`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|
