Re: Prepared Statements

Well, maybe we could discuss another way of passing sets to the preparedstatement? I would be happy to have a better way to do it too...
Although I still think is a bad idea to break compatibility :-)

On Fri, 18 Jul 2003 16:39:02 -0400
Dmitry Tkach <dmitry(at)openratings(dot)com> wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--

/~\ The ASCII Felipe Schnack (felipes(at)ritterdosreis(dot)br)
\ / Ribbon Campaign Analista de Sistemas
X Against HTML Cel.: 51-91287530
/ \ Email! Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter(at)ritterdosreis(dot)br
Fone: 51-32303341