SQL Parameterized query

From AgileApps Support Wiki
Revision as of 08:38, 2 June 2017 by imported>Aeric
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


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);

Notepad.png

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");

Notepad.png

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};

Notepad.png

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
TextField String
DateTime Java.sql.TimeStamp
Time Java.sql.Time
Date Java.sql.TimeStamp
Global Picklist String
Picklist String
Dependent Picklist String
RichTextArea String
Auto Number String
Percentage Double
Number with decimals Double
Number Int
Email Address String
URL String
Lookup String
Phone/Fax String
Currency Double
Formula Double
Checkbox Int
Multicheckbox String
Geolocation String
Radiobuttons String
Multiobject lookup String