AlchemyJ by Axisoft

4.0

ajReadRec function

Description

The ajReadRec function is used for retrieving records from a database table. Please take note that to run this function from Excel, you would need to set up the DB Connection String from the Properties dialog box.

Syntax

ajReadRec(table_name, column_headers, [filter_type], [filter_condition], [sorting_criteria], [page_number], [rows_per_page], [table_schema], [transpose], [include_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only] )

Argument NameDescription
table_name
(required)		Specify the name of the table to read from.
column_headers
(required)		Specify the columns to be returned. It could be a range of single row or column where each cell is a table column name or a data name defined in DB Schema. CLOB and BLOB are not supported.
filter_type
(optional)		Specify the type of filtering. 3 types are supported 0, 1 and 2.
filter_condition
(optional)		Specify the range that defines the filter condition.

When

filter_type = 0. The range defines the where clause of a SQL statement. The range will be concatenated into a single string. For example Name = 'peter' and class = 'B'.

filter_type = 1. The range defines the filter condition in the Kendo grid style. There are 6 columns in the range. The first row defines the header. They are Data Name, Operator 1, Search Criteria 1, And / Or, Operator 2 and Search Criteria 2. The following operators are supported:
  • eq - equal to
  • neq - not equal to
  • lt - less than
  • lte - less than or equal to
  • gt - greater than
  • gte - greater than or equal to
  • isnull - is null
  • isnotnull - is not null
  • contains - contains
  • doesnotcontain - does not contain
  • startswith - starts with
  • doesnotstartwith - does not start with
  • endswith - ends with
  • doesnotendwith - does not end with

filter_type =2. The range defines a filter condition similar to the format used in MS Query. Two columns are used to define one filter condition. So the number of columns should always be a multiple of 2. E.g. 2, 4, 6. The first column defines the operator and the second consider defines the value. For example, < 10

* Notes
An error will occur if the range value is not in the format of the corresponding filter type.
For filter_type = 1 or 2, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string).
If filter_type = 2, it will validate the correctness of Column Header and DataType against database schema.
sorting_criteria
(optional)		Specify the range which defines the sorting criteria. The range should contain 3 columns. The first row is the header. They are Data Name, Sorting Order and Sorting Direction. Sorting Direction can be "ASC" or "DESC".
page_number
(optional)		Specify the page of the returned records.
rows_per_page
(optional)		Specify the number of rows per page of the returned records.
table_schema
(optional)		Specify the range that defines the DB Schema.
transpose
(optional)

If it equals FALSE, the return records will not be transposed.

If it equals TRUE, the returned records will be transposed from row to column.

If you do not specify anything, the default value will always be TRUE.
include_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 should 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:

1) Content type: Table records in array format.

2) Method: Within a cell / cell array

Example

We will use the following table in our examples. The table name is tb_customer. It has 5 columns and 5 rows.

Example 1

Retrieve all records from the table. Only two parameters are used here.

As you can see, all records of the table are loaded to the selected area. Since the Db_Schema parameter is missing, the Column_header must be the actual column names of the table instead of the data name.

Example 2

Retrieve records where Gender equals F. filter_type is 0.

Example 3

Retrieve records where Customer Level 1 or 2. filter_type 1 is used in this case.

Example 4

Retrieve records where Gender equals F, Customer Level greater than 2, and order them by Name in descending order. filter_type 2 is used in this case. In addition, the table column names are mapped to the data name in the DB Schema. Therefore, it uses the data name in the filter_condition and Sorting_criteria.

← ajCountRecajUpdateRec →