Thread: Re: IN clauses via setObject(Collection) [Was: Re: Prepare
> > 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? You do understand that SQL is a standard outside of what postgresql actually implements right? The standard exists completely independant of pgsql. The people who designed JDBC designed it work using SQL style operations. The functionality that a PreparedStatement has is clearly designed to be that of a PREPARE'd SQL statement. The "efficiency" aspect of the PreparedStatement is not specified by the JDBC interface and so the JDBC is allowed to implement the features of a PreparedStatement without actually using a PREPARE'd statement in SQL. However it seems clear that the intent is that a PreparedStatement be as close to what can be done by an SQL PREPARE'd statement. 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. The people who designed the SQL standard know way more about databases then you or I, and so I would be willing to accept that they had a significant reason to not want to support this behaviour. > 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 :-) The definition I am trying to use is the one defined by SQL. 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. > You can create *any* datatype in postgres, and create a > column of that > type. You can have SET as a datatype. Moreover, you can 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. > 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. Darin
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
Darin Ohashi wrote: >>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. >> Unfortunately it does. How can we justify a feature that only works when we are simulating prepared statements and fails when we are using the real PREPARED statements as provided by the server? The current implementation of PREPARE in the backend does not allow us the syntax to specify in any way that we intend to provide a list of values for the ? parameter that would go into the IN clause and even give a syntax error if we try to specify a ? in there (with or without the parenthesis). I don't have the embedded SQL'99 spec here to check that, but if there isn't a standard way to do that it will be hard to convince the backend folks to implement it. Haven't they thought of this case, where someone wants to dynamically provide an <in value list> ? This would probably require a special protocol feature to upload the list of values as the parsing has already been done so we cannot just send the "(1, 2, 3, )' string. We need to send the actual length of the list followed by the values. This is getting too complicated... :-( -- Fernando Nasser Red Hat - Toronto E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser wrote: > Darin Ohashi wrote: > >>> 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. >>> > > Unfortunately it does. How can we justify a feature that only works > when we are simulating prepared statements and fails when we are using > the real PREPARED statements as provided by the server? It should not *fail* of course. The driver should take care of it. If the backend does not support PREPARE/EXECUTE with sets as parameters, the driver should know about that, and handle it as a special case Dima
Dmitry Tkach wrote:> Fernando Nasser wrote: > >> Darin Ohashi wrote: >> >>>> 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. >>>> >> >> Unfortunately it does. How can we justify a feature that only works >> when we are simulating prepared statements and fails when we are using >> the real PREPARED statements as provided by the server? > > > It should not *fail* of course. > The driver should take care of it. If the backend does not support > PREPARE/EXECUTE with sets as parameters, the driver should know about > that, and handle it as a special case > You are suggesting that when the driver detects that one of the parameters is the value for an IN clause (i.e., an <in values list>) it should abstain to use the server prepared statements and falls back to use the client emulation instead? Isn't that a little unsettling for the user, who is expecting that the prepared statements are being handled by the server? You understand, also, that we would have to keep the client emulation code around with all complications and limitations that it has (for not being in the server)even when all supported backend versions have PREPARED implemented. -- Fernando Nasser Red Hat - Toronto E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
> > You are suggesting that when the driver detects that one of the > parameters is the value for an IN clause (i.e., an <in values list>) > it should abstain to use the server prepared statements and falls back > to use the client emulation instead? Isn't that a little unsettling > for the user, who is expecting that the prepared statements are being > handled by the server? Yep. It is indeed a little unsetting... Much *less* unsetting though then just not being able to do that at all... > > > You understand, also, that we would have to keep the client emulation > code around with all complications and limitations that it has (for > not being in the server)even when all supported backend versions have > PREPARED implemented. > > Well... yes. Unless all supported backend versions will also have PREPARE implemented to understand sets... The only alternative is to have this code "with all complications and limitantions" be kept and maintain by every user on their own... Dima