search

aaberg / sql2o

sql2o is a small library, which makes it easy to convert the result of your sql-statements into objects. No resultset hacking required. Kind of like an orm, but without the sql-generation capabilities. Supports named parameters.
http://sql2o.org
MIT License
1.14k stars 230 forks source link

ORDER BY does not work with addParameter #332

Open gphendry opened 5 years ago

gphendry commented 5 years ago

Currently using query.addParameter to specify custom parameters in an ORDER BY clause does not work properly.

In a query like: 

String sql = "SELECT * " +
             "FROM report_A" +
             "ORDER BY :order :sequence "+
             "LIMIT :from, :limit";

When calling addParameter to substitute a custom value for :order the custom value is completely ignored and treated as blank.

The only way to pass custom ORDER BY parameters currently is to manually concatenate them into the original query string like so:

String sql = "SELECT * " +
         "FROM report_A" +
         "ORDER BY " + order " " + SEQUENCE +
         "LIMIT :from, :limit";

Which seems inconsistent with how every other parameter is treated.

skradel commented 4 years ago

SQL/JDBC don't work this way and this has nothing to do with sql2o -- for example, you also cannot pass table/view/schema/column identifiers in as parameters.

aaberg commented 4 years ago

Hi,

That is not a problem with sql2o, but the database server not supporting parameters as part of the ORDER BY clause

Regards Lars Aaberg

man. 29. jul. 2019, 20:24 skrev Graham Hendry notifications@github.com:

Currently using query.addParameter to specify custom parameters in an ORDER BY clause does not work properly.

In a query like:

String sql = "SELECT * " + "FROM report_A" + "ORDER BY :order :sequence "+ "LIMIT :from, :limit";

When calling addParameter to substitute a custom value for :order the custom value is completely ignored and treated as blank.

The only way to pass custom ORDER BY parameters currently is to manually concatenate them into the original query string like so:

String sql = "SELECT * " + "FROM report_A" + "ORDER BY " + order " " + SEQUENCE + "LIMIT :from, :limit";

Which seems inconsistent with how every other parameter is treated.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/aaberg/sql2o/issues/332?email_source=notifications&email_token=AAE7EJB7C6W6K77E4PNYC53QB4YVHA5CNFSM4IHVAVGKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HCDSYQQ, or mute the thread https://github.com/notifications/unsubscribe-auth/AAE7EJGTYGEMVMB3Y5BZNL3QB4YVHANCNFSM4IHVAVGA .