SQL with ? for parameters

[vertex-github]

Hi

Im attempting to upgrade our old and large project that leveraged our forked sql2o back onito the latest 1.8.0 sql2o. However im running into issues with all our queries that use ? for parameters, such as:

sql2o.open().createQueryWithParams( "INSERT INTO tUser(id) VALUES(?)", userId ).executeUpdate();

I dont recall if this is a customization we made to sql20 1.5 (? maybe 1.6) but its not working with 1.8 - is there a new config param or did the official sql20 never support ? in queries like this? Its been so long since we forked this and its worked great ever since I simply dont recall what changes we made. Its old enough that when we forked the source we were in svn. We imported into git around 2016 so lost all the change history.

[vertex-github]

I guess I added it 10 years ago :-)

This seems to work ok with our application (based on 1.8.0 source)

public class ParameterParser implements CharParser{

    private final Map<String, List<Integer>> parameterMap;
    int paramIdx = 1;

    public ParameterParser(Map<String, List<Integer>> parameterMap) {
        this.parameterMap = parameterMap;
    }

    @Override
    public boolean canParse(char c, String sql, int idx) {
        // Vertex changes to support ? in SQL in addition to named params
        if( c == '?' ) {
            return true;
        }
        return sql.length() > idx + 1 && c == ':' && Character.isJavaIdentifierStart( sql.charAt(idx + 1) ) && sql.charAt(idx-1) != ':';
    }

    @Override
    public int parse(char c, int idx, StringBuilder parsedSql, String sql, int length) {
        // Vertex changes to support ? in SQL in addition to named params
        if( c == '?' ) {
            String paramName = "p" + paramIdx;
            List<Integer> indices = parameterMap.computeIfAbsent( paramName, k -> new ArrayList<>() );
            indices.add(paramIdx++);
        }
        else {
            int startIdx = idx;
            idx += 1;
            while( idx + 1 < length && Character.isJavaIdentifierPart( sql.charAt( idx + 1 ) ) )
            {
                idx += 1;
            }
            String name = sql.substring(startIdx + 1, idx + 1);
            List<Integer> indices = parameterMap.computeIfAbsent( name, k -> new ArrayList<>() );
            indices.add(paramIdx++);
        }

        parsedSql.append("?");

        return idx;
    }
}

[aaberg]

Hi,

I checked the code for Sql2o version 1.5.1, and the code for the createQueryWithParams method looks more or less the same. So, I don't think this has been officially supported. But as you say, it's been a while since the library was updated, so I might be mistaken.

The createQueryWithParams method expects parameters to be named :p1, :p2, and so on, which is kind of silly. I would prefer if the variant you are using also works since ? is the official character for parameters in Java. If it isn't too much work to fix, I might just do that. I'll check it out and let you know what I find out.

[vertex-github]

The changes above just parse out the ? and make fake param names.

[aaberg]

You posted your comment a minute before me there 😄. Thank you for posting that, I'll check it out.