IBM Worklight Adapter
IBM Worklight comes with a few built-in adapters which enable developers to allow their app to connect to the Worklight server with various benefits such as fast development, security and transparency.
SQL Adapter
One of the available adapters is the SQL adapter. Worklight supports MySQL, Oracle 11g and DB2 databases. The SQL adapter is used to execute parameterized SQL queries and stored procedures that retrieve or update data in the database.
Filtered Search
One of the ways that we may want to access the back-end databases is by doing a filtered search. In a typical scenario, for a filtered search to be done, there will be a few fields in a form. For example, if we're doing a search on employees, some of the fields could be gender, department, and location. Sometimes, we may fill all the search fields, but sometime, 1 or more fields are left blank. And sometimes, we will leave all field as blanks to view all the employees. Hence, the SQL statement needed to be generated dynamically to only search a field when it is not empty.
Dynamic SQL
For SQL Adapter, we use the WL.Server.createSQLStatement API to prepare an SQL query. This API must always be defined or called outside the javascript function. Then WL.Server.invokeSQLStatement API is used to invoke the prepared queries inside the function. Furthermore, once the prepared queries has been called (outside), it cannot be modified or changed.
Example:
var preparedStatement1 = WL.Server.createSQLStatement("SELECT * FROM employee WHERE firstname = ? AND lastname = ?"); // WL.Server.createSQLStatement is called outside the getEmployee function.
function getEmployee(firstname, lastname) {
return WL.Server.invokeSQLStatement({ // WL.Server.invokeSQLStatement is called within the getEmployee function.
preparedStatement : preparedStatement1,
parameters : [firstname, lastname]
});
}
function getEmployee(firstname, lastname) {
return WL.Server.invokeSQLStatement({ // WL.Server.invokeSQLStatement is called within the getEmployee function.
preparedStatement : preparedStatement1,
parameters : [firstname, lastname]
});
}
So, when the SQL adapter is calling the function, they are stuck with the fixed statement. This will become a problem when we need to use SQL adapter for a filtered search, where we need to defined the SQL dynamically, depends whether or not the field is empty. Otherwise, we will need to create multiple SQL statements based on which field is empty.
Using the same example as above, if we want to display all employee with lastname = 'john', the prepared SQL statement should be as follows:
SELECT * FROM employee WHERE lastname = ?
If we use the original prepared SQL statement, if firstname is empty, it will return empty unless the firstname is an empty string. Hence, in a typical situation, the prepared SQL statement will be created dynamically, depending on whether or not the field/parameter is empty. If it is empty, then it will not be appended to the statement.
However, in IBM Worklight SQL Adapter case, the prepared SQL statement must be called outside the function and cannot be changed once created. (Probably) One of the reasons that dynamic SQL was not allowed is to prevent possible SQL injection attacks. So, we need to find another workaround to do this.
The solution
Looking back at the original SQL statement:
SELECT * FROM employee WHERE firstname = ? AND lastname = ?
As mentioned earlier, if either of the value is an empty string, result may be emtpy.
However, what if we add some sort of checking mechanism, to see if the value is emtpy or not, then "OR" it with the actual statement? Check out the following SQL statement:
SELECT * FROM employee WHERE (firstname = ? OR '' = ?) AND (lastname = ? OR '' = ?)
As you may have noticed, I've add " OR '' = ? " to the statement for each of the field (column). With this, let's assume that firstname is empty string. Usually, this is to stimulate searching for employees with certain lastname, for example "Bob". So based on the above prepared SQL statement, it will translate as the following:
SELECT * FROM employee WHERE (firstname = '' OR '' = '') AND (lastname = 'Bob' OR '' = 'Bob')
=>
SELECT * FROM employee WHERE (FALSE OR TRUE) AND (lastname = 'Bob' OR FALSE)
=>
SELECT * FROM employee WHERE (TRUE) AND (lastname = 'Bob')
=>
SELECT * FROM employee WHERE (lastname = 'Bob')
=>
SELECT * FROM employee WHERE (FALSE OR TRUE) AND (lastname = 'Bob' OR FALSE)
=>
SELECT * FROM employee WHERE (TRUE) AND (lastname = 'Bob')
=>
SELECT * FROM employee WHERE (lastname = 'Bob')
Hence, the result will list out all employees with the lastname of 'Bob'.
Next, how do we use the prepared SQL statement on the javascript function? Please look at the following example:
var preparedStatement1 = WL.Server.createSQLStatement("SELECT * FROM employee WHERE (firstname = ? OR '' = ?) AND (lastname = ? OR '' = ?)");
function getEmployee(firstname, lastname) {
return WL.Server.invokeSQLStatement({
preparedStatement : preparedStatement1,
parameters : [firstname, firstname, lastname, lastname]
});
}
function getEmployee(firstname, lastname) {
return WL.Server.invokeSQLStatement({
preparedStatement : preparedStatement1,
parameters : [firstname, firstname, lastname, lastname]
});
}
If you noticed, the variables/values in the parameters are repeated twice. The first occurrence is to compare the value with the actual column, and the 2nd occurrence is to check if the value is an empty string or not.
So with the above, you can call the javascript as the following:
getEmployee('', 'Bob');
getEmployee('John', 'Bob');
getEmployee('John', '');
getEmployee('John', 'Bob');
getEmployee('John', '');
However, if you would like to allow null or undefined value to be used as well, you can modify the javascript function as the following:
function getEmployee(firstname, lastname) {
firstname = firstname || ''; // set the default value as an empty string if the variable is null or undefined
lastname = lastname || ''; // set the default value as an empty string if the variable is null or undefined
return WL.Server.invokeSQLStatement({
preparedStatement : preparedStatement1,
parameters : [firstname, firstname, lastname, lastname]
});
}
firstname = firstname || ''; // set the default value as an empty string if the variable is null or undefined
lastname = lastname || ''; // set the default value as an empty string if the variable is null or undefined
return WL.Server.invokeSQLStatement({
preparedStatement : preparedStatement1,
parameters : [firstname, firstname, lastname, lastname]
});
}
Note: To be continued in Part 2 (working in progress)
