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