There's no solution to your problem below - there is some commentary that
may, or may not, be of relevance.
On Nov 29, 2007 8:06 AM, BAIER, ANTHONY (TONY), ATTSI <[EMAIL PROTECTED]>
wrote:
> Can you take a quick look at the code block below and error messages
> being generated when executing.
>
> Any idea why the last 2 characters of the $sql variable are getting
> chopped off when "prepare" is executed?
>
> How do I prevent the program from termininating and letting me handle
> the error handling? I tried the following DBI::CONNECT statement put
> it did not help.
>
> my $dbh = DBI->connect($data_source, $dbUser, $dbPassword, {
> PrintError=>0, RaiseError=>0, AutoCommit=>0 });
>
If you are going to have DBI not act on errors, you must do the error
checking yourself.
If you are debugging a problem, use PrintError => 1 and/or RaiseError => 1.
Code Block in Error:
>
> $sql = "insert into
> odba_user.dbh_high_memory_read_sqls
> (report_id, query_no,buffer_gets,
> no_executions, sql_text)
> values
> ($reportId, $queryNumber,
> $readCount,
> $execCount, '$queryText')";
>
>
This is a bad way of processing input data with SQL -- you are setting
yourself up for an SQL injection attack.
For a wonderful, comical demonstration of an SQL injection attack, see:
http://xkcd.com/327/
Use placeholders - or, learn about $dbh->quote.
> print "\n\nflag11a sql [$sql]\n\n";
>
> $sth = $dbh->prepare("$sql");
>
No error check?
> undef $rc;
> $rc = $sth->execute();
>
An odd way of doing business...
unless (defined $rc) {
> printf LOGFILE "statement
> execution failed:\n\"$sql\"\n$DBI::errstr\n";
> # ignore these insert errors
> # $errorCode = 1;
> print "flag11\n";
> }
>
>
> My Print Statement of $sql
>
> flag11a sql [insert into odba_user.dbh_high_memory_read_sqls
> (report_id, query_no, buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or
> ((last_date
> is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
> ''Y'' = :5)
> ) and (this_date is null) order by next_date, job')]
>
This looks correct - is there a problem here?
> Perl DBI::PrintError Results (where is the trailing single quote ')
>
> DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly
> terminated
> (DBD ERROR: OCIStmtPrepare) [for Statement "insert into
> odba_user.dbh_high_mem
> ory_read_sqls
>
> (report_id, query_no, buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or
> ((last_date
> is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
> ''Y'' = :5)
> ) and (this_date is null) order by next_date, job at
> ../../bin/DBhealthParseDB.p
> l line 653, <REPORTFILE> line 319.
>
This I agree seems to be missing some data - two characters as you say. You
are fortunate that your SQL does not contain any single quotes, of course --
that's the SQL injection issue above.
> Can't call method "execute" on an undefined value at
> ../../bin/DBhealthParseDB.
> l line 655, <REPORTFILE> line 319.
>
This is because you ignored the error from prepare and blithely tried to use
the $sth that wasn't available.
>
> Issuing rollback() for database handle being DESTROY'd without explicit
> disconn
> ct(), <REPORTFILE> line 319.
>
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2007.0914 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
