Re: parameter issues in DBD:Oracle

On 2010-03-26 11:13:14 -0700, Bruce Johnson wrote:
> On Mar 26, 2010, at 11:01 AM, Bruce Johnson wrote:
>> Does the DBD enclose all parameters in ''s?

Not really. It passes the parameters separately from the query.


>> Am I actually trying to execute:
>>
>> select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),  
>> to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in  
>> ('105, 106, 110')
>
> To answer my own question, yes it does appear to be that way.
>
> If I put the clause inline in the SQL (as 'in($rlist)' ) rather than as a 
> parameter (as 'in(?)' ), it works as expected (or at least it does when I 
> fix my most common datetime conversion error: "MM == Months, MI== 
> Minutes, yah idjit Johnson!")
>
> Is there a way to pass an unquoted list as a parameter?

No. A parameter is a parameter. You want to pass a list of parameters.
If you put it inline, you do the same:

>> select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'), to_char(stoptime, 'HH:MI')
>> from reservedroomtest
>> where roomid in (105, 106, 110)
                    ^    ^    ^
                    |    |    |
                    |    |    +-- 3rd parameter
                    |    +------- 2nd parameter
                    +------------ 1st parameter

> I can't do it as 
> 'in(?,?,?,?)' etc, because the number of list elements varies from  
> execution to execution.

Sure you can:

    $qry = "select * from reservedroomtest where roomid in ("
           . join(",", map "?", @list) 
           . ")";
    $sth = $dbh->prepare_cached($qry);
    $sth->execute(@list);

        hp


-- 
   _  | Peter J. Holzer    | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR       | eingeprägt stehen: "Ein ewig Rätsel will ich
| |   | [email protected]      | bleiben, mir und andern."
__/   | http://www.hjp.at/ |    -- Wolfram Heinrich in desd

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iD8DBQFLrdkOMdFfQa64PCwRAkbIAJ4pc/S+UxRUVoOil/373NekZqlXcwCeLMiq
pm0AFjgtwEo+NgvteL2WwZk=
=SOwc
-----END PGP SIGNATURE-----