See below ...
-----Original Message----- From: Bart Kersteter [mailto:[EMAIL PROTECTED] Sent: Tue 8/17/2004 8:39 AM To: [EMAIL PROTECTED] Cc: Subject: Trouble with bind variables Hello, I am working on a small script that pulls user metrics from databases on a number of different servers and pushes them into a 'metrics warehouse' of sorts for use in trend analysis among other things. I am having trouble with my insert statement and would like some help from all of you. I am using this statement to try and insert the data: *----------------------------------- my ($userid, $year, $week, $cpu, $io); $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io); my $insert_sql = qq{ insert into user_history_stats (database, server, year, week, username, cpu, io) values ('$orasid', '$server', ?, ?, ?, ?, ?) }; This is not bind variables ----------^-----------^ So of course you must place single ticks ("'") around these vars. my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert statement: " . $target_dbh->errstr;; *------------------------------------- $orasid and $server are loaded from a text file that I loop through to get the list of databases that I'm pulling metrics from. My problem is this: When I put the single quotes around $orasid and $server, I get the following error: *----------------------------------- DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into ("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " insert into user_history_stats (database, server, year, week, username, cpu, io) values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at ./collect_user_metrics.pl line 64, <GEN0> line 1. All your vars are undef (NULL). Are NULL values allowed in these columns? DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into ("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " insert into user_history_stats (database, server, year, week, username, cpu, io) values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at ./collect_user_metrics.pl line 64, <GEN0> line 1. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). *------------------------------------- The Bind variables aren't being set properly for some reason. If I take the single quotes out, I get this: *------------------------------------- DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 121 in ' insert into user_history_stats (database, server, year, week, username, cpu, io) values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for Statement " insert into user_history_stats (database, server, year, week, username, cpu, io) values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: :p4='187.31', :p5='8', :p1='2004', :p2='16', :p3='AGDBA'] at ./collect_user_metrics.pl line 64, <GEN0> line 1. DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error possibly near <*> indicator at char 121 in ' inse rt into user_history_stats (database, server, year, week, username, cpu, io) values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for Statement " insert into user_history_stats (data base, server, year, week, username, cpu, io) values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: :p4='187.31', :p5='8', :p1='2004', :p2='16', :p3= 'AGDBA'] at ./collect_user_metrics.pl line 64, <GEN0> line 1. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). Of course this is an error. What is the literal "fd1p"? *-------------------------------------- As you can see here, the database is rightly rejecting the first two values for not being enclosed in quotes, but the bind variables are all set correctly. I have not been able to find the right way to load all of this data. Can anyone see something I'm doing wrong, or perhaps suggest a better way to set up the INSERT statement? Did you read the DBI docs? It looks to me like your sequence of processing is not correct: 1. Your call to bind_param() is wrong (looks like the wrong handle). 2. Your call to execute() does not bind vars to the statement when called, nor does the statement handle have vars bound befor the call to exeute(). Thanks in advance, You're welcome. -- Ron Reidy Sr DBA Array BioPharma, Inc. Bart Bart Kersteter Senior DBA - Corporate Database Assurant 576 Bielenberg Drive Woodbury, MN 55125 [EMAIL PROTECTED] (651) 361-5796 ************************************************************************************** This e-mail message and all attachments transmitted with it may contain legally privileged and/or confidential information intended solely for the use of the addressee(s). If the reader of this message is not the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, forwarding or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message and all copies and backups thereof. Thank you. ************************************************************************************** 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.
