Re: Prepared Statements - Mailing list pgsql-jdbc

> I think yes.
> Depends on your definiton of "in general", of course,
> Where I come from, at least "in general", the
> PreparedStatement should
> be able to handle *everything* you can do with sql.

Well, what I meant by "in general", is more along the lines of "is this
behaviour covered by the spec".  Does the JDBC spec explicitly deny this kind of
behaviour.

My understanding, which might be wrong, is that "?" is a placeholder for a data
value and not a placeholder for SQL syntax, so, for example I would never expect
a PS would be able to do something like this:

ps := c.prepareStatement( "??" );
ps.setString( 1, "SELECT * " );
ps.setString( 2, "FROM table" );
ps.execute();

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 would be surprised if there was not a document
somewhere that specified what is and is not valid to pass via a "?".

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.

> Depends on the implementation...
> If the backend doesn't support it, the driver should take care of
> working around that
> (e.g. by switching to 'non-precompiled' version in this case)
> until the
> backend is able to support it (I don't see any reason why it
> just cannot
> be done theoretically).

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.

>
> > 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?

>> I am not going to lose it :-)
> Thanks to the driver being opensource I have control over what I am
> going to lose :-)

Lose it from the official driver.  You can, of course, do what ever you like
with your personal version of the driver.

>
> >
> >Instead of creating your own driver, why not just subclass
> Statement into
> >something that looks like a PreparedStatement but just glues
> strings together?
> >
> Ummm... because it already exists? That's what postgres
> PreparedStatement does (by default in 7.3, and always in previous
> versions)...

That is what it currently does, I don't think that is what it will do in the
future when (if) it actually represents  a precompiled statement.

> 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.

Darin