Thread: Re: IN clauses via setObject(Collection) [Was: Re: Prepare
> 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.
You are missing something here. In the original select statement that started
this mess of a discussion, you wanted to pass the SQL set Syntax into a
PreparedStatement. Something like this:
PreparedStatement stmt = c.prepareStatement("select * from mytable where data in
?");
stmt.setObject (1, "(1,2,3,4,5)", Types.INTEGER);
I questioned if passing the syntax of an SQL set was valid, stating my belief
that only passing data via a ? was valid, and stating my definition that data is
something that can be stored in a column. You claimed that because you can
create a new datatype for representing a set that sets are data. However that
has nothing to do with the orignal question. If you created a set datatype, it
would be passed to a PreparedStatement as a single data element (a single data
element that contained other data). In particular it would not have the correct
syntax to work in the example above.
So, yes you should be able to create a set datatype and pass that into the
PreparedStatment (JDBC allows for this using the SQLData interface, I don't know
if postgresql supports it), but the data that is representing a set datatype
would not be able to be inserted where SQL set Syntax is expected. The set
datatype and the SQL set Syntax are completely distinct objects.
Darin
PS. This will be my last post on this subject. I think that my thoughts on it
have been made clear.
> > >So, yes you should be able to create a set datatype and pass that into the >PreparedStatment (JDBC allows for this using the SQLData interface, I don't know >if postgresql supports it), > I know, that it doesn't > but the data that is representing a set datatype >would not be able to be inserted where SQL set Syntax is expected. > Why the hell not??? > The set >datatype and the SQL set Syntax are completely distinct objects. > > An int datatype and an int syntax are two distinct objects too :-) That's precisely the job of the JDBC driver to convert datatypes I pass in into their representation with the correct syntax that backend would understand. I would have no use of the driver doing anything else - what's the point in passign data in if the syntax it gets converted into isn't understood by the server? Dima
Just for reference, neither DB2 nor Oracle support passing the full list of values as a single '?' parameter. In both cases you have to specify several ? parameters (or :host variables) for each of the expected values. Although I find this an annoying limitation, the fact remains that no significant DBMS does what is being asked here. PostgreSQL currently does not even accept '?' in the list. I have asked about the possibility to support something in there. But even if the backend decides to accept the full list it would be a PostgreSQL-only feature/enhancement and not portable. I feel very comfortable implementing standards but not at extending them. If one day the standard body decides to implement it in a way different from the way you did you end up with backward compatibility problems. Not to mention that you end up tricking people into using some non-standard exception and preventing them to use another DBMS. Although I see no reason to use anything but PostgreSQL, this looks somewhat like M$ tactics ;-) -- Fernando Nasser Red Hat - Toronto E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9