IBM Mobilefirst SQL adapter with parameters for select IN statement

Question

I've a problem calling a MobileFirst SQL adapter to retrieve result from an Oracle Select-where-IN statement.

My environment is MobileFirst v7.0

The adapter definition is:

sqlGetResultsStatement = WL.Server.createSQLStatement ("select * from table where field IN (?)");

function getResults(param) {
    return WL.Server.invokeSQLStatement({
        preparedStatement : sqlGetResultsStatement,
        parameters : [param]
    });
}

If I call adapter with a single value in parameter (e.g. '0001') all works fine, and I obtain results. But if I call adapter with this type of parameter: "'0001','0002','0003'" I obtain an empty-resultset (w/o errors) response like this

{
   "isSuccessful": true,
   "resultSet": [
   ]
}

Is there something in the call that is wrong?

Answer 1

You cannot pass in a set of values to a single prepared statement parameter. One of the key features of prepared statements is that it helps prevent SQL injection attacks and thus it makes sense that when there is one parameter inside of the statement it is considered as a single value, that's why it encloses the value you passed in with quotes "'0001','0002','0003'". Also, MobileFirst doesn't allow you to create prepared statements inside if functions in the JS adapters and therefore you cannot modify the number of parameters when the procedure is invoked. With that being said there are two approaches you can take to accomplish this.

Javascript Adapter Determine the maximum number of parameters that you will ever pass to this procedure and add the parameters to the prepared statement beforehand. Let's say you are never going to pass more than 10 parameters, then I would use something like:

    var MAX_PARAMS = 10;

    sqlGetResultsStatement = WL.Server.createSQLStatement ("select * from table where field IN (?,?,?,?,?,?,?,?,?,?)");

    function getResults(param) {
        /*
         * (arguments) is an array of parameters passed to the procedure
         */
        return WL.Server.invokeSQLStatement({
            preparedStatement : sqlGetResultsStatement,
            parameters : fillVars(arguments)
        });
    }
    // helper function to fill all the values for the SQL Statement Parameters
    function fillVars(vars) {
        var list = [];

        for(var i = 0; i < MAX_PARAMS; i++) {
            if(vars.length >= i + 1) {
                list.push(vars[i]);
            } else {
                // some value that will not be in your db
                list.push(null);
            }
        }

        return list;
    }

Java Adapter The other option would be to use a Java Adapter and connect to your DB directly and write your own queries/prepared statements. FYI: this option will give you more flexibility but you will have to include DB driver jar files and write all the DB connection/querying logic, etc.