Query Parameter for Query Services

[EUNJY]

Hello.

For the dynamic query, I created a SQL Query Service and used it in the Javascript Service as shown in the attached photo.

But I get the following error:Unable to Invoke Service test on dashboardThing : org.json.JSONObject cannot be cast to java.lang.String

I use Database Thing.

What should I do?
Thank you.

[Constantine]

Hello @EUNJY,

It doesn't work this way, you can't pass those JSON objects as SQL query parameters, it only accepts primitive values like strings, numbers and timestamps (maybe also arrays, I'm not sure), but definitely not JSON objects. Instead of that you would need to convert your QUERY into a number of string parameters, pass them to the service one-by-one and use in separate "where xyz = [[abc]]" clauses.

But if this query JSON comes from the filter widget, then manually converting it into an SQL query will be pretty complex, just because of the different types of operators you can use in this widget. This widget works well with DataTables and Streams, it's not really designed to be used for your SQL services. If your database is small (few thousand rows max.), then as a workaround you can fetch ALL rows in your service and then filter the resulting infotable with this query object (it can be applied to infotables as well). Otherwise, if you really need to do flexible filtering on a large data set, you would need to find another approach, for example by using a bunch of simple input widgets instead of one single Filter widget.

Regards,
Constantine

[EUNJY]

"where xyz = [[abc]]"
I am using this method but what I want is this:
"[[abc]]"

I want to use the whole where clause as a parameter.
I'm not using a filter widget yet, but I think I should look for DataTables and Streams.

Thank you.

[Constantine]

---

@EUNJY wrote:

I want to use the whole where clause as a parameter.

As I wrote, this is not possible with SQL services, only with DataTables, Streams and Infotables.

/ Constantine

[EUNJY]

What I wanted was to use Dynamic Query in a Service.
It's sad that it's not possible.
Because I don't know how to use Data Table yet.
I will study the Data Table.

Thank you.

[EUNJY]

What I wanted was to use Dynamic Query in a Service.
It's sad that it's not possible.
Because I don't know how to use Data Table yet.
I will study the Data Table.

Thank you.

[wposner-2]

I told you how to create a dynamic query and didn’t say anything about using a data table.

Parse the JSON, build your where clause, and the call your sql command—passing it as a parameter.

[EUNJY]

Sorry for many questions,

Does that mean I can't use dynamic query unless I use data table?
I created a data table but can't find the DB connect configuration.

Really thank you.

[wposner-2]

Pass your query object to a regular JS service and parse out your JSON.  Programmatically create your where clause as a string based on the various conditions in your query JSON.  Once you've built out your string then call your SQL service and pass in the where clause as a parameter. 

[EUNJY]

I don't know the exact usage but...
An error also occurs when the query statement is returned as 'Query' type and used as a parameter of the SQL service.

// result: QUERY
var query = me.testUserQuery ();

// result: INFOTABLE dataShape: "ncomzUser"
var result = me.testUser ({
query: query / * QUERY * /
});

This error occurs when:
org.json.JSONObject cannot be cast to java.lang.String-

The Query used looks like this:

var query =
{
     "filters": {
         "type": "EQ",
         "fieldName": "gend_cd",
         "value": 'M'
     }
};
var result = query;

Thank you.