Re: Looking for help with odd DBD::Oracle::db prepare behavior

[John Scoles]

Hard to say whitout seeing your origninal Perl code that strips out the '-'
Just a quick suggestion is to use scalar()  (should force them to be a 
number) around each befor you add the ','.

Getting in staments like this working in DBD::Oracle is one of my little pet 
projects if I ever get any time again.

cheers


----- Original Message ----- 
From: "Nelli, Joseph R. Jr." <[EMAIL PROTECTED]>
To: "Adriano Ferreira" <[EMAIL PROTECTED]>; "Nelli, Joseph R. Jr." 
<[EMAIL PROTECTED]>; <dbi-users@perl.org>
Sent: Tuesday, April 03, 2007 8:27 AM
Subject: RE: Looking for help with odd DBD::Oracle::db prepare behavior


Thanks for the pointer. I get very interesting results using Dumper. My
print statement shows:

new_delete = delete from damping.search_criteria where
rdb_search_criteria_id in ( 56554 , 56558 , 56559 )

But the Dumper print statement shows:

$VAR1 = "delete from damping.search_criteria where rdb_search_criteria_id 
in
( 56554 , \00056558 , \00056559 )";

Any ideas on how this may be happening or how to get around it? I'm
basically passing the following string through a cgi argument:
"56554-56558-56559-" . I then convert the dashes to commas so I can place
the string into the delete statement.

-----Original Message-----
From: Adriano Ferreira [mailto:[EMAIL PROTECTED]
Sent: Monday, April 02, 2007 4:22 PM
To: Nelli, Joseph R. Jr.; dbi-users@perl.org
Subject: Re: Looking for help with odd DBD::Oracle::db prepare behavior

On 4/2/07, Nelli, Joseph R. Jr. <[EMAIL PROTECTED]> wrote:
I've been using DBD::Oracle::db from my Perl scripts for some time and
have
had no problems. Today, I've started to get some odd behavior. I create a
delete string and attempt to prepare it. I get a ORA-00936 error meesage.
I
don't know why. I create a  delete string with multiple items in the
"where
.. in" clause:

        new_delete = delete from damping.search_criteria where
rdb_search_criteria_id in ( 56554 , 56558 )

I then call the prepare statement with the new_delete string and get the
following:

        DBD::Oracle::db prepare failed: ORA-00936:
        missing expression (DBD: error possibly near <*> indicator at 
char
79 in
        'delete from damping.search_criteria where rdb_search_criteria_id
in
( 56554 , <*>') at
        /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 
10887.

I then decided to try breaking up the statement into individual calls:

        new_delete = delete from damping.search_criteria where
rdb_search_criteria_id = 56554

The first statement prepared and executed fine.

        new_delete = delete from damping.search_criteria where
rdb_search_criteria_id = 56558

The second statement failed to prepare:

        DBD::Oracle::db prepare failed: ORA-00936:
        missing expression (DBD: error possibly near <*> indicator at 
char
68 in
        'delete from damping.search_criteria where rdb_search_criteria_id
=
<*>' at
        /pw/test/svr4/PWWW/cgi-bin/eng/PSA/ITE/DDH/manager.cgi line 
10887.

Can anyone explain what the problem is and how to get around it?

Just a wild guess: are you sure there's nothing strange in between
your SQL? Something like non-printing characters and the like?

Build your SQL string and take a look at it with a dumper. For instance,

use Data::Dump 'dump';
print dump $sql;

# or

use YAML 'Dump';
print Dump $sql;

# or

use Data::Dumper 'Dumper';
$Data::Dumper::Useqq = 1;
print Dumper $sql;