FAQ
I've got tons of sql that uses ? placeholders.

Is there a way to accurately determine which question marks represent
placeholders and which ones don't?

For instance:

SELECT LENGTH('?') FROM DUAL;

v.s.

SELECT LENGTH(?) FROM DUAL;

Thanks,
ER

Search Discussions

  • Nelson, Erick [HDS] at Apr 20, 2009 at 10:05 pm
    Maybe trap for oracle error number 1008 ?

    my @sql = (
    q{select * from sfile where id = ?},
    q{select * from sfile where id = '?'},
    );
    for my $sql (@sql) {
    my $stmt = $dbh->prepare($sql);
    eval {
    $stmt->execute();
    };
    if ($@) {
    if ($stmt->err() == 1008) {
    print "statement: $sql is missing a bind var\n";
    }
    }
    }

    perl etest
    statement: select * from sfile where id = ? is missing a bind var

    -----Original Message-----
    From: E R
    Sent: Monday, April 20, 2009 2:56 PM
    To: dbi-users@perl.org
    Subject: new question: accurately detecting ? placeholders

    I've got tons of sql that uses ? placeholders.

    Is there a way to accurately determine which question marks represent
    placeholders and which ones don't?

    For instance:

    SELECT LENGTH('?') FROM DUAL;

    v.s.

    SELECT LENGTH(?) FROM DUAL;

    Thanks,
    ER
  • Douglas Wilson at Apr 20, 2009 at 10:15 pm

    On Mon, Apr 20, 2009 at 2:56 PM, E R wrote:
    I've got tons of sql that uses ? placeholders.

    Is there a way to accurately determine which question marks represent
    placeholders and which ones don't?
    I don't know if this is "really" what you need or not, but there is
    a NUM_OF_PARAMS statement handle attribute that'll tell you how many
    placeholders are in the statement.

    HTH,
    Douglas Wilson
  • E R at Apr 20, 2009 at 10:22 pm
    What I "really" want is to transcribe a SQL statement that uses ?
    placeholders into one that uses :val placeholders (for use with
    EXPLAIN PLAN.)

    So I need to have something that will tell me that the ? at character
    position X is/is not a real placeholder, etc.

    I'm guessing that there's nothing in DBD::Oracle which will do that for me.

    On Mon, Apr 20, 2009 at 5:14 PM, Douglas Wilson
    wrote:
    On Mon, Apr 20, 2009 at 2:56 PM, E R wrote:
    I've got tons of sql that uses ? placeholders.

    Is there a way to accurately determine which question marks represent
    placeholders and which ones don't?
    I don't know if this is "really" what you need or not, but there is
    a NUM_OF_PARAMS statement handle attribute that'll tell you how many
    placeholders are in the statement.

    HTH,
    Douglas Wilson
  • Tim Bunce at Apr 20, 2009 at 10:37 pm

    On Mon, Apr 20, 2009 at 04:56:12PM -0500, E R wrote:
    I've got tons of sql that uses ? placeholders.

    Is there a way to accurately determine which question marks represent
    placeholders and which ones don't?

    For instance:

    SELECT LENGTH('?') FROM DUAL;
    v.s.
    SELECT LENGTH(?) FROM DUAL;
    Assuming your database and driver don't offer anything specific,
    you might find the DBI's (undocumented) preparse() method useful.
    The only docs are the tests:

    http://cpansearch.perl.org/src/TIMB/DBI-1.607/t/60preparse.t

    Tim.

    p.s. undocumented, unsupported, liable to change yada yada yada
  • E R at Apr 21, 2009 at 2:16 pm
    Thanks for the tip.

    As it turns out, my real problem was that I am supplying bind
    parameters to the EXPLAIN PLAN statement. Either form of placeholder
    (? or :XXX) will work in a EXPLAIN PLAN statement, but you do not want
    to pass any bind parameters when executing the statement.

    Thanks again to everyone for their help.

    ER
    On Mon, Apr 20, 2009 at 5:36 PM, Tim Bunce wrote:
    On Mon, Apr 20, 2009 at 04:56:12PM -0500, E R wrote:
    I've got tons of sql that uses ? placeholders.

    Is there a way to accurately determine which question marks represent
    placeholders and which ones don't?

    For instance:

    SELECT LENGTH('?') FROM DUAL;
    v.s.
    SELECT LENGTH(?) FROM DUAL;
    Assuming your database and driver don't offer anything specific,
    you might find the DBI's (undocumented) preparse() method useful.
    The only docs are the tests:

    http://cpansearch.perl.org/src/TIMB/DBI-1.607/t/60preparse.t

    Tim.

    p.s. undocumented, unsupported, liable to change yada yada yada

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedApr 20, '09 at 9:56p
activeApr 21, '09 at 2:16p
posts6
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase