Thread: Prepared Statement Issues.

Prepared Statement Issues.

From
Greg Johnson
Date:
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






Re: Prepared Statement Issues.

From
Paul Thomas
Date:
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   |
+------------------------------+---------------------------------------------+

Re: Prepared Statement Issues.

From
Oliver Jowett
Date:
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

Cursor based statements

From
Dave Cramer
Date:
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>


Re: Cursor based statements ADDENDUM

From
Dave Cramer
Date:
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>


Re: Cursor based statements ADDENDUM

From
Oliver Jowett
Date:
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

Re: Cursor based statements ADDENDUM

From
Dave Cramer
Date:
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>