The ajDownloadLob function retrieves a file from a Large Object column in a database table. Please take note that to run this function from Excel, you need to set up the DB Connection String from the Properties dialog box.
ajDownloadLob( table_name, lob_info, filter_type, filter_condition, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )
|The name of the table that stores the file.|
|The Excel range that contains the LOB field information. It can have 6 columns in the range with the following headers:|
|The type of filter. The value can be 0, 1 or 2.|
|The Excel range that defines the filter condition.|
filter_type = 0. The Excel range defines the where clause of a SQL statement. The Excel range will be concatenated into a single string. For example, Name = 'peter' and class = 'B'.
filter_type = 1. The Excel range defines the filter condition in the Kendo grid style. There are 6 columns in the Excel range. The first row defines the header. They are Data Name, Operator 1, Search Criteria 1, And / Or, Operator 2, Search Criteria 2. The following operators are supported:
filter_type =2. The Excel range defines a filter condition similar to the format used in MS Query. Two columns are used to define one filter condition. Therefore, 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 column defines the value. For example, < 10
* Take note of the following:
An error will occur if the Excel 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 Column Header and DataType against the database schema.
|Specifies the Excel range that defines the DB Schema.|
The key column(s) must be included in the Column_headers and Data. Otherwise, an error will be raised.
To 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'.
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.
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: Number of affected records.
2) Method: Within a cell / cell array.
In this example, we will use the following table. The table name is tb_supporting_doc. It has 5 columns and 2 rows.
Download the file from the CONTENT field of the record with ID 1001. 1 record is affected. Since the LOB File Name is provided (picture1.png0, it overrides the value defined in the LOB File Name Field column. IF(AlchemyJ_FunctionPoint = "Download Doc" is to prevent the function from being triggered repeatedly.
If the folder path does not exist, it will be created automatically.
- The column that stores the file should be a LOB (Large Object) or a CLOB (Character Large Object).
- Only one file can be stored in a LOB field.
- If the filter condition does not match anything, the function will return 0.
- It is expected that the filter condition should match one record only. If more than one record is found, the file in the last record will be saved.
- If the file exists, it will be overwritten.
- For more details about filter options, you can refer to the ajReadRec function.