Skip to Content
avatar image
Former Member

LIKE Operator in XS

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;
}
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 27, 2015 at 10:03 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded