SQL Parameterized query

To execute the SQL query, you have to provide the parameters in the prepared statement. This parameterized query avoids SQL injection issues. AgileApps Cloud supports parameterized SQL query as well as the plain SQL query.
Example of Parameterized query: Select * from <object> where field1 = ?
In the AgileApps Cloud platform, define the SQL parameterized query in custom Java class under Developer Resources.

To create SQL parameterized query

1. Define the object array of parameter values.

Example: Object[] arr = {object1,object2,object3};

2. Define the SQL parameterized query:

Result r = Functions.execSQL("select * from <object> where field1 = ? and field2 = ? and field3 = ? ", arr);

Note: The order of the values in the parameterized query must be in the same order as defined in the object array.

Examples

DateTime or Date fields –

Timestamp timestamp1 = Timestamp.valueOf("2017-02-10 08:55:00");

Note: Use only 24-hour time format.

Time field

Time t = Time.valueOf("05:20:00");

String type

String test_text = "testing 1’st data";

Other objects

Object[] arr = {timestamp1,t,test_text};

Note: The following operations and clauses are supported: SELECT, FROM, WHERE, GROUP, BY, ASC, DESC, HAVING, WITH, ORDER, IS, NOT, EQUAL, NOT EQUAL, AND, OR, GREATER, LESSER, GREATER THAN EQUAL, LESSER THAN EQUAL, IN, LIKE, REGEXP, INNER JOIN, LEFT JOIN, RIGHT JOIN.

AAL fields to Java object Mapping

Field Display Type	Java Type
April 2017	✔
March 2017	✕
February 2017	✔
January 2017	✔
December 2016	✕
November 2016	✕