ajExecuteSql function
Description
The ajExecuteSql function executes a SQL statement and returns the result.
Syntax
ajExecuteSql(sql_statement, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only])
| Argument Name | Description |
|---|---|
| sql_statement (required) | 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 syntax is supported. For example, this function does not support “;” combine with other SQL statements. For security best practices, it is recommended to use ‘?’ or ‘: |
| parameter (optional) | The range of cells to form the list of input and output parameters to be specified in the following format. The range of cells can span across multiple rows and columns. <field_name>-##-<value>-##-<data_type>-##-<in_out_flag>-||- <field_name>-##-<value>-##-<data_type>-##-<in_out_flag>-||- ... Where, -||- denotes AlchemyJ parameter separator. -##- denotes AlchemyJ parameter type separator. For example, RECORD_ID1-##-21-##-NUMBER-##-IN-||- RECORD_ID2-##-23-##- NUMBER-##-IN-||- OUTPUT -##- NUMBER -##- OUT Please take note that both separator symbols can be configured in %%AppConfig Worksheet. If you do not specify anything, the default value will always be an empty string (“”). |
| return_type (optional) | 0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. 2 indicates to return the number of the affected records. If you do not specify anything, the default value will always be 0. |
| transpose (optional) | If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. If you do not specify anything, the default value will always be FALSE. |
| return_header (optional) | If it equals TRUE, the return result will include the column headers as the first row. If it equals FALSE, the return result will contain data only. This only applies when ‘return_type’ is 0. If you do not specify anything, the default value will always be FALSE. |
| data_source_id (optional) | Specify the data source ID. The parameter is used to define which database shall be executed for the function. If you do not specify anything, the default value will always be 'primary'. |
| convert_to_text (optional) | 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 but any string value that has more than 256 characters will cause an error. If you do not specify anything, the default value will always be FALSE. |
| run_condition (optional) | The function will run when the value is TRUE. Otherwise, it will not run. If you do not specify anything, the default value will always be TRUE. |
| run_by_function_point_only (optional) | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Run Function Point. If it equals TRUE, the function can be executed with Run Function Point (AlchemyJ toolbar \ Run Function Point) only. If you do not specify anything, the default value will always be TRUE. |
The function will return:
- Content type: Database operations results or the count
- Method: Within a cell / cell array
Before using ExecuteSQL, the database connection must be set up from the Properties dialog box.
Example
Below is the SQL statement we will execute and the expected result.
ajExecuteSql returns a set of records in the defined cell range.
#VALUE! will be returned when there is an error in the usage of the function.