> 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____________________________________ _____|_____|_____|_____|_____|_____|_____|_____|