IN query not returning any result, worklight SQL Adapter

Question

I have created SQL adapter to work with Oracle DB, and using Worklight 6.1.

Following is the code of SQL Adapter:

var checkOwner = WL.Server.createSQLStatement("SELECT * FROM shopowners WHERE macaddress IN (?)");

function checkShopOwners ( deviceList ){
WL.Logger.warn("In checkShopOwners");

var allDeviceJSONArray = JSON.parse(deviceList);
var preparedMACAddresses = "";

for ( var macaddress in allDeviceJSONArray ) {
    WL.Logger.warn(allDeviceJSONArray[macaddress]);
    preparedMACAddresses += "\"" + allDeviceJSONArray[macaddress] + "\",";
}
preparedMACAddresses = preparedMACAddresses.substring(0, preparedMACAddresses.length - 1);
WL.Logger.warn(preparedMACAddresses);

var options = {
    preparedStatement : checkOwner,
    parameters : [ preparedMACAddresses ]
};

var result = WL.Server.invokeSQLStatement(options);

WL.Logger.warn("result : " + JSON.stringify(result));

if ( result.isSuccessful ) {
    WL.Logger.warn("Shop owner selected");
}
else {
    WL.Logger.warn("result : " + JSON.stringify(result));
}
}

This query doesn't returning any result. I am passing deviceList as JSON array which contains MAC address of device.

One of the MAC address is there in my DB, but still it is not returning any result.

Any help would be appreciated.

P.S:

1. The preparedMACAddresses results in something like this. "xx:xx:xx:xx:xx","yy:yy:yy:yy:yy"
2. I have also tried to make preparedMACAddresses like this. 'xx:xx:xx:xx:xx','yy:yy:yy:yy:yy', but this is also not working.
3. If I make, var checkOwner = WL.Server.createSQLStatement("SELECT * FROM shopowners WHERE macaddress = ?"); and pass one of the mac address to the query, it is returning the resultSet.
4. My worklight studio version is 6.1.0.01-20140418-0637

Answer 1

You're passing a single scalar bind value with ?, and a single string of comma-separated values. Your query is looking for a row where the MAC is "xx:xx:xx:xx:xx","yy:yy:yy:yy:yy", and not any row matching either of the individual addressed. IN will not break your string into tokens automatically.

There are various methods to achieve this, but I'm not sure what your platform will support; I don't see that you could pass an Oracle array (e.g. sys.odcivarchar2list) of individual strings that are treated as a table, for example.

Here's one method that might work though, if you can change your query. You can tokenise your string with something like this:

WITH cte1 AS (
  SELECT ? AS mac_list FROM dual
),
cte2 AS (
  SELECT regexp_substr(mac_list, '[^,]+', 1, level) AS macaddress
  FROM cte1
  CONNECT BY regexp_substr(mac_list, '[^,]+', 1, level) IS NOT NULL
)
SELECT * FROM cte2;

With the bind variable set to string xx:xx:xx:xx:xx,yy:yy:yy:yy:yy (with no single- or double-quotes around each element!) that gives:

MACADDRESS   
--------------
xx:xx:xx:xx:xx
yy:yy:yy:yy:yy

So your real query can join to that, something like:

WITH cte1 AS (
  SELECT ? AS mac_list FROM dual
),
cte2 AS (
  SELECT regexp_substr(mac_list, '[^,]+', 1, level) AS macaddress
  FROM cte1
  CONNECT BY regexp_substr(mac_list, '[^,]+', 1, level) IS NOT NULL
)
SELECT * FROM cte2 JOIN shopowners ON shopowner.macaddress = cte2.macaddress;

I've used two CTEs here so you only have to pass your bind parameter once; you could use it directly in the regex clauses but since it would then be referred to twice you'd have to pass it twice.