Curt Sampson wrote:
> On Thu, 11 Apr 2002, Barry Lind wrote:
>
>
>>I'm not sure that JDBC would use this feature directly. When a
>>PreparableStatement is created in JDBC there is nothing that indicates
>>how many times this statement is going to be used. Many (most IMHO)
>>will be used only once.
>
>
> Well, the particular PreparedStatement instance may be used only
> once, yes. But it's quite likely that other, identical PreparedStatement
> objects would be used time and time again, so it's still good if
> you don't need to do much work on the second and subsequent
> preparations of that statement.
>
But since the syntax for prepare is: PREPARE <name> AS <statement> you
can't easily reuse sql prepared by other PreparedStatement objects since
you don't know if the sql you are about to execute has or has not yet
been prepared or what <name> was used in that prepare. Thus you will
always need to do a new prepare. (This only is true if the driver is
trying to automatically use PREPARE/EXECUTE, which was the senario I was
talking about).
>
>>If it only is used once, it will actually perform worse than
>>without the feature (since you need to issue two sql statements to the
>>backend to accomplish what you were doing in one before).
>
>
> I'm not sure that it would be much worse unless you need to wait
> for an acknowledgement from the back-end for the first statement.
> If you had a back-end command along the lines of "prepare this
> statement and execute it with these parameters," it would have
> pretty much the same performance as giving the statement directly
> with the parameters already substituted in, right?
>
I didn't say it would be much worse, but it won't be faster than not
using PREPARE.
>
>>Thus if someone wanted to use this functionality from jdbc they would
>>need to do it manually, i.e. issue the prepare and execute statements
>>manually instead of the jdbc driver doing it automatically for them.
>
>
> I'd say that this is awfully frequent, anyway. I use PreparedStatements
> for pretty much any non-constant input, because it's just not safe
> or portable to try to escape parameters yourself.
>
I agree this is useful, and you can write user code to take advantage of
the functionality. I am just pointing out that I don't think the driver
can behind the scenes use this capability automatically.
--Barry
