Re: postgresql-8.0.jar and failure of ORDER BY parameters

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Guillaume Cottenceau <gc@mnc.ch> writes:
> >         SELECT .... ORDER BY ?, ?
>
> >     actionSt.setInt( 1, 4 );
> >     actionSt.setInt( 2, 5 );
>
> This would appear to be asking for a sort by two constant values,
> which of course is not going to order the data usefully at all
> --- all the rows will have the same sort keys.
>
> I suppose that it behaved differently in older versions of the JDBC
> driver that didn't implement real server-side prepared statements.
> If the parameters are substituted textually before the server sees
> the command, then you have "ORDER BY 4,5" which is interpreted according
> to the old SQL92 syntax --- ie, sort by fourth and fifth columns of the
> output.  But anything beyond a simple integer literal --- in particular,

Yes, that's what it did. I thought this was "normal" behaviour
(even if it's cumbersome IMHO) so I have not explained it.

I'd better write for example:

        actionSt.setString( 1, "surname" )

in order to sort by the value of the column named "surname", but
this has never "worked". Is it supposed to work now? I just tried
that but it didn't change the resultset, btw.

> a parameter symbol --- is treated as an expression value per SQL99.
>
> So no, you can't do it like that.  The fact that it happened to "work"
> before was an artifact of the implementation.

Is there any way to give parameters to ORDER BY in a
preparedStatement then?

Thanks.

--
Guillaume Cottenceau