This is not recommended since I implemented this script by using "<< >>" to add and update conditions in where clauses.

 

Note that it is extremely DANGEROUS to use << ... >> string substitution, because it puts you at risk of SQL Injection. We recommend you use caution when considering this method of parameter passing. However, to create a very dynamic set of Queries, you need to pass in the Table names as << Name of the Table >>. Also, if you need to use the IN clause, your collection will need to be passed in with << Item1, Item2, Item3,>>.


Nevertheless, we want and need to query data from database by using a data filter widget or user inputs. So, I made a simple sample for it and hopefully can help you.

 

This sample script was written for PostgrsSql, so please update the query script when you use other databases like Oracle, MSSQL, MySQL or so. And this was tested on ThingWorx 8.

 

Step 1. Create a SQL query service

In your script, you can write a script as "Select * From "SampleTable" Where <<query>>;". The "query" is from input parameters, so you can add more parameters if needed.

 

Step 2. Create a service to query data based on user defined conditions.

In this script, you need to create query strings for the query service above ("GetAllRecords"). This sample is tested with a "DataFilter" widget, so if you want to use any other widget please check query parameters (fields) and values. This was tested for String, Number and DateTime type.

The first input parameter for this service is "filters" which is for "String" and "Number" type data and the "datetimefilter" is used for "DateTime" format.

For "DateTime" format, you need to convert the format of its value to timestamp. There is a snippet called "dateFormat" under "DateFunctions" category, so I used it to change its format.

The whole script is as below.

 

var queryString = "1 = 1"; // Just for dummy since a sql query has a where clause.
for each (var filterObj in filters) {
    if (queryString.length > 1) queryString = queryString + " AND ";
   
    switch(filterObj.type) {
        case "EQ":
            queryString = queryString + " \"" + filterObj.fieldName + "\" = " + filterObj.value + " ";
            break;
        case "LIKE":
            var value = filterObj.value.toString();
            //logger.warn(value);
            value = value.replace("*", "%");
            value = value.replace("*", "%");
            queryString = queryString + " \"" + filterObj.fieldName + "\" LIKE '" + value + "' ";
            break;
        case "NE":
            queryString = queryString + " \"" + filterObj.fieldName + "\" <> " + filterObj.value + " ";
            break;
        case "GT":
            queryString = queryString + " \"" + filterObj.fieldName + "\" > " + filterObj.value + " ";
            break;
        case "LT":
            queryString = queryString + " \"" + filterObj.fieldName + "\" < " + filterObj.value + " ";
            break;
        case "GE":
            queryString = queryString + " \"" + filterObj.fieldName + "\" >= " + filterObj.value + " ";
            break;
        case "LE":
            queryString = queryString + " \"" + filterObj.fieldName + "\" <= " + filterObj.value + " ";
            break;
        default:
            queryString = queryString + " \"" + filterObj.fieldName + "\" = " + filterObj.value + "' ";
    }
}

for each (var datefilterObj in datetimefilter) {
   
    // dateValue:DATETIME
    var dateValue = new Date(formattedDate);

    // dateFormat(dateValue:DATETIME,dateFormat:STRING):STRING
    var formattedDate = dateFormat(dateValue, "yyyy-MM-dd hh:mm:ss");
    //logger.warn("formattedDate : " + formattedFromDate);

    if (queryString.length > 1) queryString = queryString + " AND ";
   
    switch(datefilterObj.type) {
        case "EQ":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" = '" + formattedDate + "' ";
            break;
        case "BETWEEN":
            // dateValue:DATETIME
            var fromDateValue = new Date(datefilterObj.from);
            var toDateValue = new Date(datefilterObj.to);

            // dateFormat(dateValue:DATETIME,dateFormat:STRING):STRING
            var formattedFromDate = dateFormat(fromDateValue, "yyyy-MM-dd hh:mm:ssZ");
            var formattedToDate = dateFormat(toDateValue, "yyyy-MM-dd hh:mm:ssZ");
           
            //logger.warn("formattedFromDate : " + formattedFromDate);
            //logger.warn("formattedFromDate : " + formattedToDate);
           
            datefilterObj.from = formattedFromDate;
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" >= '" + formattedFromDate + "' ";
            queryString = queryString + " AND ";
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" <= '" + formattedToDate + "' ";
                       
            break;
        case "NE":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" <> '" + formattedDate + "' ";
            break;
        case "GT":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" > '" + formattedDate + "' ";
            break;
        case "LT":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" < '" + formattedDate + "' ";
            break;
        case "GE":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" >= '" + formattedDate + "' ";
            break;
        case "LE":
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" <= '" + formattedDate + "' ";
            break;
        default:
            queryString = queryString + " \"" + datefilterObj.fieldName + "\" = '" + formattedDate + "' ";
    }
}
//logger.warn("queryString : " + queryString);

// This is to call "GetAllRecords" service from me tab.
var params = {
    query: queryString /* STRING */
};

// result: INFOTABLE dataShape: "undefined"
var result = me.GetAllRecords(params);

 

Step 3. Create a mashup and test

I created a mashup with two data filter widgets and one grid. One data filter is for "Number" and "String" format field and another is for "DateTime" format.