He should use bind variables (method 3). ----------------- Ron Reidy Senior DBA Array BioPharma, Inc.
-----Original Message----- From: Kong, Alan [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 11:13 AM To: Shah, Urmil; Reidy, Ron; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem Try the mothed 2 with non-numeric fields single-quoted. -----Original Message----- From: Shah, Urmil [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:07 PM To: Reidy, Ron; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem I tried to use different methods to insert but all fails. 1) $sth=$dbh->prepare(insert into OPER_QUERY values ($a,$b,$c,$d....) ) $sth->execute() or die; 2) $sth=$dbh->prepare(insert into OPER_QUERY (col1, col2, col3..co15) values ($a,$b,$c,$d....) ) $sth->execute() or die; 3) $sth=$dbh->prepare(insert into OPER_QUERY values (?,?,?,?,....?) ) $sth->execute($a,$b,$c,$d,...$x) or die; For case 1 and 2 it gives error saying the value is empty. In case 3 it just hangs and does not populate anything and I have to kill the script. If there is any other way to insert please let me know. Urmil -----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 11:57 AM To: Shah, Urmil; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem You have a NULL (not the word NULL) in your statement. FYI - You should look into using bind variables. ----------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: Shah, Urmil [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 10:43 AM To: [EMAIL PROTECTED] Subject: SQL- Insert problem Need help in trying to insert an sql statement. 1) I have 15 columns in oracle database defined. 2) Through Perl DBI I am trying to loop and populate these 15 columns for 'n' of rows. 3) Some of the column values that I am trying to insert is null/empty and it is ok if they are empty <-- It gives me error when it find null value. Code: foreach my $type(sort keys %H_CNT_BY_PGM){ foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){ $sth1 = $dbh->prepare(qq{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 ($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_t ype_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_typ e_err,$rmenu_temp_type_err,$rmenu_lot_type_err)}) or die"Cannot prepare sql:$DBI::errstr\n"; $sth1->execute() or die"Cannot prepare sql:$DBI::errstr\n"; }#foreach }#foreach dbi Trace(1) output : start inserting: 08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32, DBI::db=HASH(0x49fcbc) trace level set to 1 in DBI 1.20-nothread Note: perl is running without the recommended perl -w option !! ERROR: 936 'ORA-00936: missing expression (DBD: error possibly near <*> indicator at char 252 in '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(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')' <- prepare('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(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,)' CODE)= undef at oper_query.pl line 351 DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD: error possibly near <*> indicator at char 252 in '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(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)') at oper_query.pl line 351. DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD: error possibly near <*> indicator at char 252 in '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(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)') at oper_query.pl line 351. <- DESTROY= undef <- DESTROY= undef during global destruction This column is an integer and its ok if it is null I don't have any constraints defined of not null on this column. * I tried to prepare the statement outside the foreach loop and then pass on the values for those columns in the loop it hangs. Eg: $sth=$dbh->prepare(qq{insert into OPER_QUERY values (?,?,?,?.....?) or die........................; foreach loop{ $sth->execute($mydate,$type, ..............) or die .............. } Here it Hangs and does not insert any values. Can any one help on whats going on. I do not want to check each of 15 paramater values to see if they are null or not null before inserting in the db. Regards, Urmil 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. 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.
