Thread: Prepared Statement Issues.
I recently have been trying to update to the 113 build of jdbc drivers, but I am having some problems. I have several prepared statements where I pass in order by column and the order of the sort. select id, name, address, state from people order by (?) (?) However, with the 113 build I cant seem to set the column name to order by or the sort direction anymore. With the 112 build I just set the SQL type to INTEGER and it passed through. I also used the same "hack" to pass an array of values to IN statements. select id, name, address, state from people where id in (?); What java sql type do I need to use for these types of queries? Thanks For you Help! ________________________________________________________________________ Greg Johnson The InterProse Consulting Group, Inc. http://www.interprose.com t (360) 604-3531 x225 f (360) 604-3579
On 23/10/2003 16:33 Greg Johnson wrote: > I recently have been trying to update to the 113 build of jdbc drivers, > but I am having some problems. I have several prepared statements where > I pass in order by column and the order of the sort. > > select id, name, address, state from people order by (?) (?) Placeholders are for values not names. If it worked in a previous version of the driver then that sounds like a bug which has been fixed. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Thu, Oct 23, 2003 at 08:33:50AM -0700, Greg Johnson wrote: > I recently have been trying to update to the 113 build of jdbc drivers, > but I am having some problems. I have several prepared statements where > I pass in order by column and the order of the sort. > > select id, name, address, state from people order by (?) (?) > > However, with the 113 build I cant seem to set the column name to order > by or the sort direction anymore. With the 112 build I just set the SQL > type to INTEGER and it passed through. I also used the same "hack" to > pass an array of values to IN statements. > > select id, name, address, state from people where id in (?); > > What java sql type do I need to use for these types of queries? You can't use ? placeholders to embed things that aren't actually single data values in queries any longer. This came out of fixing a SQL injection hole that appears if the driver allows parameters to make it into the query unescaped. There was quite a bit of discussion at the time about how to fix the injection hole while supporting existing apps, but unfortunately noone could find an acceptable way of doing it. To get the same effect with recent drivers, you'll have to construct the expanded query string yourself. You can still use ?-placeholders for real data values, obviously. -O
Just going through the code and the CursorFetchTest fails on the 3rd iteration This is because the code can't deal with changing the fetch size on an already declared fetch size. the question is do we want that ability. the spec of course is ambiguous. Dave -- Dave Cramer <Dave@micro-automation.net>
It appears that this is a 7.4 issues, previous versions used to destroy a cursor if it existed, 7.4 does not do this. Dave On Mon, 2003-10-27 at 16:52, Dave Cramer wrote: > Just going through the code and the CursorFetchTest fails on the 3rd > iteration > > This is because the code can't deal with changing the fetch size on an > already declared fetch size. > > the question is do we want that ability. the spec of course is > ambiguous. > > Dave -- Dave Cramer <Dave@micro-automation.net>
On Mon, Oct 27, 2003 at 06:15:55PM -0500, Dave Cramer wrote: > It appears that this is a 7.4 issues, previous versions used to destroy > a cursor if it existed, 7.4 does not do this. I sent some patches to Barry last week which should fix this (among other things they deal with fetchsize better, and regenerate the cursor name on each query execution) -- although I don't have a 7.4 server to test against. He was going to hold them until post-7.4 before applying, though, since they weren't trivial changes. > Dave > > On Mon, 2003-10-27 at 16:52, Dave Cramer wrote: > > Just going through the code and the CursorFetchTest fails on the 3rd > > iteration > > > > This is because the code can't deal with changing the fetch size on an > > already declared fetch size. > > > > the question is do we want that ability. the spec of course is > > ambiguous. What's ambiguous exactly? At most the fetchsize is a hint, we should still behave correctly regardless of what value that hint takes. -O
Well, they need to be in before 7.4 because it is broken. I'll be patching the code in a few minutes. You should send patches to the list so I don't end up wasting time fixing stuff you've already fixed. --dc-- On Mon, 2003-10-27 at 21:18, Oliver Jowett wrote: > On Mon, Oct 27, 2003 at 06:15:55PM -0500, Dave Cramer wrote: > > It appears that this is a 7.4 issues, previous versions used to destroy > > a cursor if it existed, 7.4 does not do this. > > I sent some patches to Barry last week which should fix this (among other > things they deal with fetchsize better, and regenerate the cursor name on > each query execution) -- although I don't have a 7.4 server to test against. > He was going to hold them until post-7.4 before applying, though, since they > weren't trivial changes. > > > Dave > > > > On Mon, 2003-10-27 at 16:52, Dave Cramer wrote: > > > Just going through the code and the CursorFetchTest fails on the 3rd > > > iteration > > > > > > This is because the code can't deal with changing the fetch size on an > > > already declared fetch size. > > > > > > the question is do we want that ability. the spec of course is > > > ambiguous. > > What's ambiguous exactly? At most the fetchsize is a hint, we should still > behave correctly regardless of what value that hint takes. > > -O > -- Dave Cramer <Dave@micro-automation.net>