Just in case the op doesn't make the connection, he needs to test his
variables for undef before the execute, and if one is undef, then put
null("") in it, something like this:
if (!defined($a)) {$a = "";}
if (!defined($b)) {$b = "";}
... and so on - you get the idea...
$sth=$dbh->prepare(insert into OPER_QUERY values (?,?,?,?,....?)
)
$sth->execute($a,$b,$c,$d,...$x) or die;
HTH.
Hardy Merrill
>>> "Reidy, Ron" <[EMAIL PROTECTED]> 08/13/04 01:13PM >>>
>From your original post ...
!! 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,)')'
The error is ---------------------------------------^
This is invalid SQL, as are the others in this statement like this.
Because are building dynamic SQL and your variables are 'undef'ined,
this is the statement you are sending to the RDBMS engine. You need to
ensure your variables have a value and all should be well.
However, you should still look at bind variables.
-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-----Original Message-----
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 11:07 AM
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.
