This discussion is archived
4 Replies Latest reply: Jan 25, 2012 12:33 AM by 789390 RSS

Prepared statement with placeholders - the order or the names are important

789390 Newbie
Currently Being Moderated
Hi,

I'll explain my question, using the example from the OCCI documentation.

What if I do something like:
<pre>
// note the order
stmt->setSQL("BEGIN countFruit(:2, :1); END:");
stmt->setString(1, "Apples");
stmt->setString(2, "Oranges");
</pre>

This will become
<pre>
BEGIN countFruit( 'Apples', 'Oranges' ); END
</pre>
or
<pre>
BEGIN countFruit( 'Oranges', 'Apples' ); END
</pre>

So, if the answer is:

* *"the first one is correct"*, then - may I use whatever I want for placeholders (some strings, for example), just starting with ':' ?

* *"the second one is correct"*, then:

* * does this mean, that I can't use strings as placeholders (because setXXX does not take strings as first parameter, but a number - as position)

* * should I use consequent numbers, or I may use whatever I want. I mean, is this valid: "BEGIN countFruit(:13, :666); END:". So, if it is, does this mean, that I should use setXXX( 13, ...) and setXXX( 666, ...), instead of setXXX(1, ...) and setXXX(2, ...) ?

-----

Sorry for the dummy questions, but I didn't see this explained in the doc (at least for 10g). And yes, I can test it, but this doesn't mean anything, it may be undefined behavior or server configuration, or whatever and I just want to be sure :)

Thanks in advance.

Best Regards,
Kiril Kirov
  • 1. Re: Prepared statement with placeholders - the order or the names are important
    423410 Explorer
    Currently Being Moderated
    You can give anything for bind variable.
    OCCI uses OCIBindByPos and the following is documented

    In the context of SQL statements, the position n indicates the bind parameter at the nth position. However, in the context of PL/SQL statements, OCIBindByPos() has a different interpretation for the position parameter: the position n in the bind call indicates a binding for the nth unique parameter name in the statement when scanned left to right.

    For SQL statements the summary is irrespective of what you name the bind variables, its the position that matters
    So you can only use the position numbers in setXXX methods and not 13/666
  • 2. Re: Prepared statement with placeholders - the order or the names are important
    789390 Newbie
    Currently Being Moderated
    Looks like I have missed that.

    I'm not sure I understand the difference, when using SQL and PL/SQL. Here's what I get, please correct me if I'm wrong:

    -----

    For PL/SQL (for example - SP call), if I have
    <pre>
    BEGIN stored_procedure( :11, :22, :22, :33 ); END
    </pre>
    calling
    <pre>
    stmt->setString(3, "Apples");
    </pre>
    will set *"the third unique parameter"* - in this example, will replace ':33', right?

    If so, will executing
    <pre>
    stmt->setString(2, "Oranges");
    </pre> replace both ':22' placeholders?

    -----

    And for standard SQL query, it's just the nth position. For example:
    <pre>
    INSERT INTO table VALUES(:11, :22, :22, :33);
    </pre>
    calling
    <pre>
    stmt->setString(3, "Apples");
    </pre>
    will replace the second ':22', right? It will become something like:
    <pre>
    INSERT INTO table VALUES(:11, :22, 'Apples', :33);
    </pre>
    ?
    -----

    About the summary: that would mean, that
    <pre>
    stmt->setSQL("BEGIN countFruit(:13, :666); END");
    stmt->setString(1, "Apples");
    stmt->setString(2, "Oranges");
    </pre> is the correct way and it will be "translated" to:
    <pre>
    BEGIN countFruit( 'Apples', 'Oranges' ); END
    </pre>
    right?

    -----

    One more case, sorry..
    <pre>
    stmt->setSQL("BEGIN countFruit(:1, :2, :3, :3, :2 ); END");
    </pre>
    How to set the second ':2' ? If ONLY "scanned left to right" is important and it stops on match (does not scan ALL parameters), does this mean, that to set the second ':2', I should use
    <pre>
    stmt->setString(4, "Apples");
    </pre>
    ? Or it scans all placeholders and replaces the repeatable ones? In that case, I'll have to use
    <pre>
    stmt->setString(2, "Apples");
    </pre>
    to replace both ':2' ?

    Edited by: Kiril Kirov on Jan 24, 2012 3:36 PM
  • 3. Re: Prepared statement with placeholders - the order or the names are important
    423410 Explorer
    Currently Being Moderated
    Your understanding is correct in the first, second and third case.
    In the last case, you can bind only once for :1, :2 & :3 and it would take the same values for all repeatable ones.
  • 4. Re: Prepared statement with placeholders - the order or the names are important
    789390 Newbie
    Currently Being Moderated
    That's good :)

    Thank you for the support.

    helpful + correct + answered question

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points