When we do connections to JDBC connected databases, as much as possible we want to leverage the Database Server side capabilities to do the querying, aggregating and everything else for us.

So if we need to do filtering or math add it to the query statement so it is done server side, by the Database server not the Thingworx runtime server.

Besides that there is much more that can be done, since databases are powerful (all the good stuff like joins, union, distinct, generated and calculated fields and what not)

The more we can do Database server side the better it is for the Thingworx runtime performance.

Now everyone hopefully knows the [[  ]] parameter substitution. So we can easily build an SQL service that has several input parameters, it will look like:

Select * from table where item1=[[par1]] AND item2 =[[par2]] etc.

But we can take this up a notch with the super powerful yet super dangerous <<   >>

Now we can do a service that just says <<sqlQuery>> and use another service to build something like: select * from table where item1 in “val1,val2,val3” etc.

 

If you can avoid it, only use [[  ]] but if needed there always is <<   >> but you must make sure that you properly secure that service with the system user and validate the service that invokes this service, since <<  >> is vulnerable to SQL Injection