Array for "In"?

Tue, 23 Sep 2003 20:30:47 -0700

I am having a heck of a time doing a "SELECT FROM TAB WHERE COL1='val' and COL2 IN ('1','2')" where the '1','2' is coming from an array that is returned from a multi-select box built with CGI $q->scrolling_list with "multiple" set to "true". I see that bind_param_array can take an array as a bind value, but I want to have several arrays of varying sizes in my query. bind_param_array will not do this, nor will it work for a SELECT.

I've had to massage my values,
inserting my single quotes ahead of time so it isn't querying on something like $Milestone(as shown in the trace). Here is a snippet of the trace, this is what prompted me to make the new $qMilestone variable with the quotes:

    -> prepare_cached in DBD::_::db for DBD::Oracle::db
(DBI::db=HASH(0x3131d8)~0x2d8624 'SELECT
ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM
MASTERTABLE WHERE ACTIVE LIKE '$Active' AND MILESTONE IN ('$Milestone')
ORDER by ID')

---And here is the code snippet that I am working with now:

        $qMilestone = join ',', $q->param("qMilestone");
        $qMilestone = "\'$qMilestone\'";
        $qActive = "\'$Active\'";
        print "<br>qMilestone=$qMilestone and qActive=$qActive<br>";
        print "<br>'SELECT
ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM
MASTERTABLE WHERE ACTIVE LIKE $qActive AND MILESTONE IN ($qMilestone) ORDER
by ID'";
        $sql = "SELECT
ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM
MASTERTABLE WHERE ACTIVE LIKE $qActive AND MILESTONE IN ($qMilestone) ORDER
by ID";

}

sub dbLoop {

        $dbh->trace(3,"trace.log");
        $sthMaster = $dbh->prepare_cached($sql);
        $sthHistory = $dbh->prepare_cached('SELECT STATUS,COMMENTS,LABEL
FROM STATUSCURRENTTAB WHERE ID in ?');

        $sthMaster->execute();
        while (($_ID, $_Owner, $_Base, $_Plat, $_DB, $_Ch, $_MApp ) =
$sthMaster->fetchrow_array()) {
                $_Owner = $Owner{$_Owner};

---The problem is, I'm only getting one row back in my query, and it's
matching Milestone 2. I'm seeing a message that says "perhaps you need to call execute first" even though I AM calling it first. Below is a snippet from the trace... after this it goes on to the next query:

    DBI::db=HASH(0x2e375c) trace level set to 3 in DBI 1.38-nothread
    -> prepare_cached in DBD::_::db for DBD::Oracle::db
(DBI::db=HASH(0x313310)~0x2e375c 'SELECT
ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM
MASTERTABLE WHERE ACTIVE LIKE '_' AND MILESTONE IN ('1','2') ORDER by ID')
1   -> FETCH for DBD::Oracle::db (DBI::db=HASH(0x2e375c)~INNER 'CachedKids')
    .. FETCH DBI::db=HASH(0x2e375c) 'CachedKids' = undef
1   <- FETCH= undef at DBI.pm line 1460 via /var/apache/cgi-bin/Status.cgi
line 115
1   -> STORE for DBD::Oracle::db (DBI::db=HASH(0x2e375c)~INNER 'CachedKids'
HASH(0x371b1c))
    STORE DBI::db=HASH(0x2e375c) 'CachedKids' => HASH(0x371b1c)
1   <- STORE= 1 at DBI.pm line 1461 via /var/apache/cgi-bin/Status.cgi line
115
1   -> prepare for DBD::Oracle::db (DBI::db=HASH(0x2e375c)~INNER 'SELECT
ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM
MASTERTABLE WHERE ACTIVE LIKE '_' AND MILESTONE IN ('1','2') ORDER by ID'
undef)
    dbih_setup_handle(DBI::st=HASH(0x35d4bc)=>DBI::st=HASH(0x3719f0),
DBD::Oracle::st, 2497c8, Null!)
    dbih_make_com(DBI::db=HASH(0x2e375c), 2b5530, DBD::Oracle::st, 208, 0)
thr#0
    dbd_st_prepare'd sql SELECT
    dbd_describe SELECT (EXPLICIT, lb 80)...
    fbh 1: 'ID' NULLable, otype   1->  5, dbsize 10/11, p10.s0
    fbh 2: 'OWNER'      NULLable, otype   1->  5, dbsize 15/16, p15.s0
    fbh 3: 'BASEVERSION'        NULLable, otype   1->  5, dbsize 3/4, p3.s0
    fbh 4: 'PLATFORM'   NULLable, otype   1->  5, dbsize 5/6, p5.s0
    fbh 5: 'DATABASE'   NULLable, otype   1->  5, dbsize 1/2, p1.s0
    fbh 6: 'DBCHARSET'  NULLable, otype   1->  5, dbsize 1/2, p1.s0
    fbh 7: 'MIGAPPSERVER'       NULLable, otype   1->  5, dbsize 5/6, p5.s0
    dbd_describe'd 7 columns (row bytes: 40 max, 19 est avg, cache: 235)
1   <- prepare= DBI::st=HASH(0x35d4bc) at DBI.pm line 1473 via
/var/apache/cgi-bin/Status.cgi line 115
    <- prepare_cached= DBI::st=HASH(0x35d4bc) at Status.cgi line 115 via
/var/apache/cgi-bin/Status.cgi line 66

--Any ideas why this isn't working? I saw it work with hardcoded values, and
I am now generating a $sql that works when used in sqlplus.... maybe I've
just been looking at this code too long & need to go do something else for a
little bit.... but if you can see the problem, I'd sure appreciate a pointer
or two...

Thx,

Dave

Reply via email to