SQL sentences

Use Executes an SQL statement.

This action executes an SQL statement on the database. its result is a JSONObject with the following structure

{ data: [], error: "" }

error is empty if the operation was successful. Data is a JSONArray object with an array of JSONObject objects with the data from the result of the SQL statement. each attribute of this object corresponds to a column within the SQL statement.

The SQL statement can be combined with the values ​​of variables, in this case, each variable must be in the form ${xxxxx} where xxxxx corresponds to the name of the variable.

Example:

SELECT j.id, j.publishdate, j.userid, j.name, j.category, j.city, c.name AS cityname, j.price, j.detail, j.enabled, u.photo,
u.name AS publisher
FROM acom_joboffers j LEFT OUTER JOIN jnu_users u ON j.userid = u.id, acom_cities c WHERE
j.city = c.id AND j.enabled = 'true'
AND j.city = ? ${category} 
ORDER BY j.publishdate DESC

Where ${category} is a variable whose value is:

category = " AND j.category IN ('a', 'b', 'c') ";

Execute an Update statement.

This action allows executing INSERT or UPDATE statements in the database, its result is a JSONObject object with the following data:

• totalrows: number of records affected by the transaction
• error: string with the description of the error in case the operation has failed. If the transaction was successful, this string is empty.
• pkX: when the statement is INSERT and the table has an autonumber field as its primary key, this variable returns the key obtained. X is the number of the key (1 .... n). If the table has only one primary key, the variable would be pk1. The value is of type INTEGER