Support positional parameters

[niksabaldun]

I am opening this feature request again as my first attempt (#181) was summarily dismissed without good explanation. Positional parameters are useful in certain use cases, and their absence from sql2o is certainly causing me a lot of problems. They should be quite trivial to implement, so why not?

[dimzon]

Positional parameters (JDBC way) is evil/bad practice because you have not a way to control, change their order. I mean it's too common case when you need to change parameter order when you rewrite an SQL query. Also if you use same parameter twice you must bind them twice. Imaginate you must rewrite java-method signature every time (near every time) you change its implementation. You can emulate positional parameters using :pNNN naming pattern - what's wrong with it?

[niksabaldun]

In my case, I am building SQL dynamically, I am not writing it by hand. I analyze Java bean class and build SELECT/UPDATE/INSERT statements based on class metadata which I get by reflection. Positional parameters are much easier to work with in that case. Sometimes I need to exclude a field from existing UPDATE statement, which would be trivial replace operation with positional parameters. If I use :pN naming pattern, I have to renumber parameters which are left, which requires some fairly complex regex stuff.

There could be more examples where positional parameters are useful. The point is, why remove existing functionality? Surely you can't predict every possible use case someone could encounter.

Not to mention, :pN naming pattern is proprietary and thus not portable, which is a bad practice in itself. If I decide to use another library or go back to plain JDBC, I would have to rewrite all my SQL queries.

[aaberg]

The Query object doesn't handle positional parameters, and I don't think it should either. But the Query object is just a decorator class for a PreparedStatement, so perhaps this can easily be solved simply by make the PreparedStatement instance accessible through a getter?

Something like this:

final String sql = "select ....";

try (Connection con = sql2o.open()) {
    Query query = con.createQuery(sql);

    query.getStatement().setString(1, "someStringParam");
    ...

    return query.executeAndFetch();
}

[niksabaldun]

Making JDBC counterparts of sql2o classes accessible through getters is definitely a good thing, regardless of this discussion.

For me, it makes sense to support both named and positional parameters (JPA supports both, for example). Whether you decide to support positional parameters or not, I believe :pN naming pattern should be removed. It is non-portable and counter-intuitive (also undocumented, AFAIK). The withParams method of Query class makes sense only with positional parameters, IMHO.

[kkrgwbj]

i am agree,pN naming pattern should be removed. It is non-portable and counter-intuitive (also undocumented, AFAIK)

[wotbstars]

Not supporting positional parameters is a problem for me as well. I want to quickly migrate legacy code written in JDBC way. JDBC queries do not support named parameters and moving code is not a trivial task as every query has to be rewritten and method signatures have to be updated as it used to be Object ... args

It is required to be replaced by a parameter map what needs an individual handling for each place where invocation of method is made.

[liuzhongshu]

I agree. Not supporting positional parameters is a problem for me too, I construct my SQL dynamically, positional parameters are more nature.