Thread: Re: IN clauses via setObject(Collection) [Was: Re: Prepare
At this point I think I need to re-ask my original question. Is the ability to
pass a set throught a ? in a JDBC PreparedStatement something that should be
do-able at all, regardless of if you can find an inoffensive way through the
JDBC interface?
I believe that a JBDC PreparedStatement is meant to be just a front end for an
SQL PREPARE statement. If it is not valid to send a set in an SQL PREPARE
statement (which I have not yet been given a definative answer for) should there
be away to pass it through a JDBC PreparedStatement? What happens when you want
JDBC PreparedStatements to be PREPARE'd statements? Will this ability be
dropped at that time or will some messy hack be defined to continue to support
this behaviour?
BTW, Dima, I would define "data" as it is defined by SQL, unfortunately I don't
know that exact definition. However it looks like something is data if a column
can be created to store it. So I would say that an array is a data element, a
set is a Syntax element that contains data.
Darin
> > > I think Dima is arguing that Collection could be treated
> as an special case
> > > where it indicates a list of something instead of a
> single something.
> > >
> > > So, we would iterate through this Collection using its
> iterator and, for
> > > each Object obtained, act like a setObject has been used
> with that Object
> > > and the JAVA TYPE as an argument.
> > >
> > > The Types.OTHER is used for database specific SQL types
> or for Dynamic Data
> > > Access support. As the Collection class is not a data
> type there is no
> > > chance of confusion.
> >
> > Ya, I understand. My main objection is that setObject(n, object,
> > Types.INTEGER), in all other cases, means "interpret object
> as an integer,
> > or fail if it can't be cast in that way".
>
> Also.. what would we do with this object?
>
> public class AnnoyingObject implements java.util.Collection,
> java.sql.Array {
> // ...
> }
>
> then setObject(n, new AnnoyingObject(), Types.ARRAY);
>
> Is that an Array, or an IN clause of Arrays? :)
>
> (Array is the obvious candidate for also being a Collection,
> but potentially
> you could do it with other types too)
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
> joining column's datatypes do not match
>
Darin Ohashi wrote: >At this point I think I need to re-ask my original question. Is the ability to >pass a set throught a ? in a JDBC PreparedStatement something that should be >do-able at all, regardless of if you can find an inoffensive way through the >JDBC interface? > >I believe that a JBDC PreparedStatement is meant to be just a front end for an >SQL PREPARE statement. If it is not valid to send a set in an SQL PREPARE >statement (which I have not yet been given a definative answer for) should there >be away to pass it through a JDBC PreparedStatement? > There is no technical reason why sql prepare cannot support "IN ?" If it is not supported by the backend at the moment should not affect the general decision of whether or not having such a feature is useful to have in the jdbc driver or not. For example, in 7.2 there is no SQL PREPARE *at all*. Following your logic, should we conclude that PreparedStatement should not then be implemented in 7.2 jdbc? > What happens when you want > JDBC PreparedStatements to be PREPARE'd statements? Will this ability be > dropped at that time or will some messy hack be defined to continue to support >this behaviour? > If the backend doesn't support this case, it will have to be an exception... until the support is added to the backend (which, I am sure, will happen eventually). > >BTW, Dima, I would define "data" as it is defined by SQL, unfortunately I don't >know that exact definition. > Nice :-) Are you saying that you are using this term without knowing what it means (even to you)??? :-) > However it looks like something is data if a column >can be created to store it. So I would say that an array is a data element, a >set is a Syntax element that contains data. > Ok. Then, I guess, we should conclude that using "IN ?" should not be supported *IF* your definition of "data" is accepted. I don't see any reason why it should (be accepted) though :-) It seems to serve no other purpose then to exclude "IN ?" functionality ... From the theoretical standpoint, the *only* difference between an array and a set is that the former can contain duplicates, and the latter cannot... I don't see any sane reason whatsoever to declare one being "data", and the other one being "syntax". While, like you I am not sure what "data" means (and would much prefer to avoid using that term at all therefore), I am pretty sure that an array, and a set should be *simultaneously* considered "data" or "not data". Defining data as something you can create a column for doesn't seem to make any sense to me, as I said above. And, moreover, it actually creates more problems to your point than it solves. You can create *any* datatype in postgres, and create a column of that type. You can have SET as a datatype. Moreover, you can create compound (row/struct) types - *none* of those is supported by JDBC currently. Dima
On 21/07/2003 17:38 Darin Ohashi wrote: > > At this point I think I need to re-ask my original question. Is the > ability to > pass a set throught a ? in a JDBC PreparedStatement something that should > be > do-able at all, regardless of if you can find an inoffensive way through > the > JDBC interface? It certainly should _not_ be doable using setObject(). It's clear to me (look at the first sentance in the javadoc for setObject(int, Object)) that it must treat Object as a single object and produce a single value which is matched against its paramter marker. > > I believe that a JBDC PreparedStatement is meant to be just a front end > for an > SQL PREPARE statement. If it is not valid to send a set in an SQL > PREPARE > statement (which I have not yet been given a definative answer for) > should there > be away to pass it through a JDBC PreparedStatement? What happens when > you want > JDBC PreparedStatements to be PREPARE'd statements? Will this ability be > dropped at that time or will some messy hack be defined to continue to > support > this behaviour? My understanding is that currently you need to tell the statement to use server-side prepares otherwise the driver executes it as a non-prepared statement, in which case the back-end is un-troubled by exploitation of this bug. If server-side prepares are used, I would expect (haven't tried it) that, based on the documentation of the EXECUTE statement, an error would be raised because of the mismatch in the number of arguments. Somehow, I don't think you'll persuade the core developers to bastardize the back-end to make it work with a bastardized, non-complient botch in the JDBC driver. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+