Re: IN clauses via setObject(Collection) [Was: Re: Prepare

Darin Ohashi wrote:

>If the SQL standard does not allow passing sets into PREPARE'd statements, that
>is a technical reason why postgresql's (and basically every other database's)
>PREPARE should not support "IN ?" with a set.
>
Here is what postgres docs say about that standard:

SQL92 includes a PREPARE statement, but it is only for use in embedded
SQL clients. The PREPARE statement implemented by PostgreSQL also uses a
somewhat different syntax.

I don't believe jdbc qualifies as an "embedded SQL client"... so, this
seems to mean that there is no standard that would define this behaviour...

> The people who designed the SQL
>standard know way more about databases then you or I,
>
... I would not bet my lunch on that :-)

> and so I would be willing
>to accept that they had a significant reason to not want to support this
>behaviour.
>
>
... most of what's in that standard came from existing implementations -
if nobody has implemented a feature at the time the standard was
created, that feature would not make it into the standard.
That doesn't mean that this feature is bad or not useful, or harmful, or
whatever. It doesn't even mean that the standard disallows it - it just
means that you don't have to implement it to deserve to be called
"stadard compliant" (but not the other way around - if you do implement
it, that, of course, does not disqualify you from the standard").

On a different note, you seem to be confuising two completely different
things - SQL and JDBC. There are many things in JDBC, that are not even
mentioned in SQL standard. CallableStatement is one of them (IIRC, the
first time anything like stored procedure was mentioned in a SQL
standard was SQL99, and jdbc had it long before that). Predefined SQL
types in jdbc is another example (look at TINYINT), etc., etc...

And, once again - standards do not define the complete set of all the
possible useful features - just the *minimal* set, that must be
implemented for compliance.
If something is not mentioned in the standard it doesn't mean at all
that it should not work - just that it doesn't have to.

>
>The definition I am trying to use is the one defined by SQL.
>
Where???

>I don't have the
>exact text of the definition, however I think that I have a good sense of what
>the definition is. Besides which, defining data (in the context of SQL) to be
>something that can be stored as an element of a table, sounds like a perfectly
>reasonable definition to me.
>
>
Fine. As I said earlier, you *can* store sets in postgres. Does that
qualify them as "data" in your view?
What are we arguing about then?

BTW, you can also store things like complex numbers for example... How
do you set a PS parameter to a complex number?
Accodring to your logic, it should be possible, shouldn't it?

>Sigh. You can create a datatype with any name, but just because you call your
>datatype a set does not mean you can put it where SQL set Syntax is used.
>
I am not talking about names. I don't care what the name of datatype is.
Since I can create a datatype to represent a set, that, accroding to
your logic, makes it "data" (because I can create a table column of that
type, and store a set in it), thus, there should be a way to pass it in
through jdbc.
This is what *you* said, not me.

>
>
>
>>create compound
>>(row/struct) types - *none* of those is supported by JDBC currently.
>>
>>
>
>Carefuly with your terminology. The JDBC interface does support STRUCT. If
>postgresql's JDBC Driver does not support STRUCT, that is a limitation of
>pgsql's JDBC Driver.
>
>
Yep.
That's all we are talking about.
The *only* way to pass user-defined datatypes into PreparedStatement in
postgres right now is through setObject (), and that functionality is
being taken away because of those "security issues"...
If it did support STRUCT, it would not be a problem, and we would not
have this discussion.

See, I don't really care *how* you can pass arbitrary data in - I only
care *if* that can be done.
If there was a way to pass it in, using SQLData (STRUCT) class, I could
not care less about those proposed changes to the setObject(), as long
as I could still use SQLData to pass that stuff in.

Dima