The hanging behavior is odd. I (and presumably many people on the list)
have used bind variables for years without trouble. It may be that -w and 'use strict;' will give you helpful info; these were both required rules (as much as I could enforce them) back in my days of iron-fisted DBA.
--Chris
On Aug 13, 2004, at 1:28 PM, Shah, Urmil wrote:
Ok I tried to use method 3 and as I said it hangs. Since it's a dynamic sql some variables are undefined and they do not have any value, now if they don't have any value is it possible that DBI/Oracle will interpret as empty values ? else I have to check the values of each variable and then hardcode to null if there is no value.
The print statement shows what is going to be populated. If it is empty I want them to be empty.
My print statement and Trace output:
Values to insert: 08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,
DBI::db=HASH(0x49ea08) trace level set to 1 in DBI 1.20-nothread Note: perl is running without the recommended perl -w option <- prepare('insert into OPER_QUERY values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' CODE)= DBI::st=HASH(0x49eaa4) at oper_query.pl line 352 DBI::db=HASH(0x49ea08) trace level set to 2 in DBI 1.20-nothread Note: perl is running without the recommended perl -w option <- trace= 1 at oper_query.pl line 353
^C !! ERROR: 1013 'ORA-01013: user requested cancel of current operation (DBD: oexec error)' <- execute('08/12/04' 'DMOS5' ...)= undef at oper_query.pl line 354 DBD::Oracle::st execute failed: ORA-01013: user requested cancel of current operation (DBD: oexec error) at oper_query.pl line 354. DBD::Oracle::st execute failed: ORA-01013: user requested cancel of current operation (DBD: oexec error) at oper_query.pl line 354. <- DESTROY= undef -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x49ea08)~INNER) <- DESTROY= undef during global destruction
Regards, Urmil
-----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 12:16 PM To: Kong, Alan; Shah, Urmil; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem
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.