LIKE Operator in XS

[Former Member]

I have set up a new XS (Extended Application) instance and was able to successfully execute simple queries with JavaScript.

But I didn't manage to use the LIKE operator.

When I do:

"SELECT * FROM NEO_123456789ABECD.MYTABLE1 WHERE name like '%?%'";

I get this error:

dberror(setString): 9 - index out of bounds: param index exceeded the param size 0, but 1

This is the whole code:

function getDataFromTable() {
    var Name = $.request.parameters.get('Name');
    var conn = $.db.getConnection();
    var pstmt;
    var rs;
    var query;
    var output = {
        results : []
    };
    try {
        query = "SELECT * FROM NEO_123456789ABECD.MYTABLE1 WHERE name like '%?%'";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, Name);
        rs = pstmt.executeQuery();

        var record = {};
        while (rs.next()) {
            record.id = rs.getString(1);
            record.name = rs.getString(2);
            record.desc = rs.getString(3);
            output.results.push(record);
        }
        rs.close();
        pstmt.close();
        conn.close();
    } catch (e) {
        $.response.status = $.net.http.INTERNAL_SERVER_ERROR;
        $.response.setBody(e.message);
        return;
    }
    var body = JSON.stringify(output);
    $.response.contentType = 'application/json';
    $.response.setBody(body);
    $.response.status = $.net.http.OK;
}

[thomas_jung · Accepted solution]

Instead of this:

1. query = "SELECT * FROM NEO_123456789ABECD.MYTABLE1 WHERE name like '%?%'"; 
2.         pstmt = conn.prepareStatement(query); 
3.         pstmt.setString(1, Name); 

Try this:

1. query = "SELECT * FROM NEO_123456789ABECD.MYTABLE1 WHERE name like ?"; 
2.         pstmt = conn.prepareStatement(query); 
3.         pstmt.setString(1, "%"+Name+"%"); 

Because what you are trying to do is set the literal string '%?%' in the like condition. This is why it won't accept a parameter.  Just set the parameter like normal and add the wild card characters as you set the parameter value.