ajDBRunSQL function
Description
The ajDBRunSQL function executes a SQL statement and returns the result.
Syntax
ajDBRunSQL(sql_statement, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only])
| Argument Name | Argument Type | Description |
|---|---|---|
| sql_statement (required) | Range / Array | The range of cells to form the SQL statement to be executed. The range of cells can span across multiple rows and columns. Please take note that only a single SQL statement is supported. For example, this function does not support using “;” to combine multiple SQL statements. For security best practices, it is recommended to use ‘?’ (Oracle only) placeholder in the query string. Such placeholders will be replaced with parameters from the ‘parameter’ argument. This can prevent SQL injection attack as data will be sanitized. |
| parameter (optional) | Range / Array | The range of cells to form the list of input and output parameters. Refer to the SQLParameters Snippet to see how to use this snippet to define the required fields. |
| return_type (optional) | Double | 0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. For PostgreSQL, the returning keyword will return a set of records, so should use 0 for this case. 2 indicates to return the number of the affected records. The default value is 0. |
| transpose (optional) | Boolean | If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. The default value is FALSE. |
| return_header (optional) | Boolean | If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE. |
| data_source_id (optional) | String | The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary". |
| convert_to_text (optional) | Boolean | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE. |
| run_condition (optional) | Boolean | The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE. |
| run_by_function_point_only (optional) | Boolean | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE. |
The function will return:
- Return Value: Database operations results or the count
- Return Type: Single Value / Multiple values (array formula)
Before using ExecuteSQL, the database connection must be set up in the Data Source Connection in ##ExternalResources.
Example
Below is the SQL statement that we will execute and the expected result.
Click AlchemyJ ribbon Insert Snippet and select SQL Parameters . A table will be inserted. In this example, it sets the parameter 1 value is 10 and the second parameter is "%A%". Use the range B4:E7 as the second parameter for ajDBRunSQL. This function returns a set of records in the defined cell range.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| Invalid SQL Statement. |
| Invalid return type, it must be 0, 1, or 2. |
| Return header is TRUE, but return type is not 0 or 1. |
| For PostgreSQL, the return type is 0 or 2 |
| DB connection error. |