AlchemyJ by Axisoft

4.0

ajDownloadLob function

Description

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.

Syntax

ajDownloadLob( table_name, lob_info, filter_type, filter_condition, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )

Argument NameDescription
table_name
(required)		The name of the table that stores the file.
lob_info
(required)		The Excel range that contains the LOB field information. It can have 6 columns in the range with the following headers:
  • LOB Content Field - The name of the column that stores the file.
  • LOB Content Folder - The local folder path that the file should be saved to.
  • LOB File Name Field - Optional. The name of the column that stores the filename.
  • LOB File Name - Optional. The filename to be used. This overrides the value stored in the LOB File Name field.
  • CLOB Character Set Field - Optional. The name of the column that stores the character set code of the file. Include this column if CLOB is used. utf-8 will be used when this field is omitted.
  • CLOB Character Set - Optional. The character set code used when saving the file from the CLOB column. This overrides the value stored in the column defined by Character Set Field.
filter_type
(required)		The type of filter. The value can be 0, 1 or 2.
filter_condition
(required)		The Excel range that defines the filter condition.

When

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:
  • 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 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.
table_schema
(required)		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.
data_source_id
(optional)

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'.
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: Number of affected records.

2) Method: Within a cell / cell array.

Example

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.

Notes

  1. The column that stores the file should be a LOB (Large Object) or a CLOB (Character Large Object).
  2. Only one file can be stored in a LOB field.
  3. If the filter condition does not match anything, the function will return 0.
  4. 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.
  5. If the file exists, it will be overwritten.
  6. For more details about filter options, you can refer to the ajReadRec function.
← ajUploadLobajCheckFileExists →