Skip to main content
E
EUNJY1-Visitor
1-Visitor
March 6, 2020
Question

Query Parameter for Query Services

  • March 6, 2020
  • 2 replies
  • 5590 views

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.

2 replies

C
Constantine18-Opal
18-Opal
March 6, 2020

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

 

 

    E
    EUNJY1-VisitorAuthor
    1-Visitor
    March 10, 2020

    "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.

    C
    Constantine18-Opal
    18-Opal
    March 10, 2020
    @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

    W
    wposner-21-Visitor
    1-Visitor
    March 10, 2020

    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. 

    E
    EUNJY1-VisitorAuthor
    1-Visitor
    March 11, 2020

    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.

    Terms & ConditionsAccessibility statement