Re: Prepared Statements

Darin Ohashi wrote:

>The final SQL statement produced by a PS should be any valid SQL string, but a
>PS should not have to deal with being able to put that string together from
>arbitrary pieces of data.
>
I agree with you on this one - not *arbitrary* pieces of data, no.

> I would be surprised if there was not a document
>somewhere that specified what is and is not valid to pass via a "?".
>
>
If there is any, I've never heard of it...

>Looking at this particular example, it looks to me (of course, I have a limited
>amount of SQL experience) that "(1,2,3,4)" contains syntax.  Using (?,?,?,?) the
>"?" replace data and that should work.  As "(1,2,3,4)" contains syntax, I don't
>think it is a valid thing to substitute in.
>
Well... "(1,2,3,4)" *does* contain syntax. I agree on this one too.
*But*, (1,2,34)   itself is *not* just an arbitrary set of data, but a
syntactical construct that represents a set.
I never said that using setObject (1, "(1,2,3,4,5)") is the ideal
solution to my problem - it is just a workaround to the drivers
inability to pass in a set any other way.
I would much prefer to use something like setObject (1,
javaSetOfIntegers) if I could.
But we don't have it, and what I have now is better than nothing at all.

>
>Well, if you allow syntax to be substituted in to the PS, then the backend can't
>precompile because it does not have a syntatically complete statement.  What you
>substitute in could completely change the query optimization.
>
>
Sure. Even if you substitute just an int, it can very well change the
optimal query plan
select ... where x=1 or select ... where x = 2000 could result in two
totally different plans.
This is a known complication of precompiled statements.

One just has to know when it is better to precompile your queries and
when it is not.

>
>
>>>Do other JDBC drivers support this kind of substitution?
>>>
>>>
>>>
>>Yes. They do (at least, the ones I worked with do).
>>
>>
>
>Do they use actual precompiled statements or just with string concats?
>
>
Yes, they do precompile the statements.

>
>
>>No, that's not what I want...
>>What I want is an abstraction of the implementation-specific details,
>>that lets me effectively execute sql queries.
>>If it can precompile my query, I want it to precompile it. If
>>it cannot,
>>I can live with that (for a while).
>>I just don't want to *know* what it is doing.
>>
>>
>
>I accept that, but then you have to be willing to live by the rules of the spec,
>and I suspect this behaviour violates the spec.
>
>
>
It doesn't violate it. I'll agree that it probably *extends* the spec...
But I just don't think it is such a bad thing...


Dima