sqlibm-mobilefirstworklight-adapters

IBM Worklight - Error while using variable in a SQL Adapter query


I am using SQL adapter in worklight where I need to have a variable that I need to use it in the query.

I read here and followed the same. But it`s showing the below error.

Pasted the complete error message on using a variable in the SQL adapter.

  [ERROR   ] FWLSE0099E: An error occurred while invoking procedure  [project Sample]Device/SqlStatementFWLSE0100E:  parameters: [project Sample]{
   "arr": [
      {
         "parameters": [
            null
         ],
         "preparedStatement": "UPDATE devices SET DeviceQuantity=$[count] WHERE DeviceNames = 'DellTestLap';"
      }
   ]
}
Parameter index out of range (1 > number of parameters, which is 0)..
Performed query:
UPDATE devices SET DeviceQuantity=$[count] WHERE DeviceNames = 'DellTestLap';
FWLSE0101E: Caused by:  [project Sample]java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). 
                                                                                                               com.worklight.common.log.filters.ErrorFilter

Project.js

     function UpdateDeviceDetails(){


            var count = 2;
            var invocationData2 = {
            adapter : 'Device', // adapter name
            procedure : 'UpdateDeviceDetails', // procedure name
            parameters : [count] 

        };



        WL.Client.invokeProcedure(invocationData2,{
            onSuccess : QuerySuccess,
            onFailure : QueryFailure
        });
}

Adapter.js

var DeviceDetails  = WL.Server.createSQLStatement("UPDATE devices SET DeviceQuantity=$[count] WHERE DeviceNames = 'DellTestLap';");

function UpdateDeviceDetails(count) {

    return WL.Server.invokeSQLStatement({
        preparedStatement :DeviceDetails,
        parameters : [count]
    });
}

Solution

  • I've never used the $[variable_name] syntax with SQL adapters. I've always used "?"

    "UPDATE devices SET DeviceQuantity=? WHERE DeviceNames = 'DellTestLap';"

    However, assuming that this syntax does work, how is your code referencing the name "count"? The variable "count" is resolved as the number 2. The SQL statement won't be able to know to reference the name count just by the variable name. It would make more sense if the variable passed to parameters was more like this:

    return WL.Server.invokeSQLStatement({
            preparedStatement :DeviceDetails,
            parameters : [{ count: 2 }]
        });
    

    That being said, I've never used this syntax before, I just use the "?" syntax.