ajDBUpdateLOB function

Description

The ajDBUpdateLOB function uploads a file to a Binary Large Object (BLOB) column or a big string to a Character Large Object (CLOB) column in a database table satisfying the criteria specified in filter_condition. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.

Syntax

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

Argument Name	Argument Type	Description
table_name (required)	String	The name of the table to store the large object.
lob_info (required)	Range / Array	The range that contains the LOB field information. Refer to the LOB Info snippet to see how you can use this snippet to define the required fields.
filter_type (required)	Double	The type of filter. The values can be 0, 1 or 2.
filter_condition (required)	Range / Array	The range that defines the filter condition. 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. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. filter_type =2. The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Refer to the Filter Condition snippet to see how you can use this snippet to define the required fields for filter type 1, 2.
table_schema (required)	Range / Array	Specifies the 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)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise 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:

1) Return Value: Number of affected records.

2) Return Type: Single Value

Example

We will use the following table in our examples. The table name is tb_supporting_doc. It has 5 columns and 2 rows data in it.

Click Add Component button in AlchemyJ ribbon, select DB Schema to add the DBSchema worksheet. It will prompt a window to input the table name, input 'tb_supporting_doc', and click OK. Columns information will be loaded to DBSchema worksheet.

Example 1 - Save a File to Database

Upload the file to the CONTENT field of the record with ID 1001. 1 record is affected.

Example 2 - Save a big string to Database

1. Select a cell, for example, F20. Click More Tools button in AlchemyJ ribbon, select Configure Big String Value. Input the value and click Update Big String Value.

   

2. A big string ID will be shown in F20. Save the big string ID to the TEXT field of the record with ID 1002. 1 record is affected.

   

   

Click here to download the use case workbooks for further reference.

Notes

1. ajDBUpdateLOB is an update function. Before using ajDBUpdateLOB to upload a file or a big string, a record to hold that file or big string must be created first. If the filter condition does not match anything, the function will return 0.

2. If there are more than one record found, all matched records will be updated with the file or data.

3. The column that stores the file should be a Binary Large Object or a Character Large Object. Different database shall use different data type for large object. The data type list as below:

   Database Type	Character Large Object	Binary Large Object
   Oracle	CLOB	BLOB
   MySQL	text	blob, longblob, binary
   MSSQL	text	image, varbinary, binary
   PostgreSQL	text	bytea

4. Only one file can be stored in a LOB field and only one big string can be stored in CLOB.

5. For more details about the filter options, you can refer to the ajDBReadRecord function.

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
And/Or in filter condition is invalid.
CLOB defined in DB Schema but CLOB character set in LOB info is empty.
CLOB defined in DB Schema but CLOB character set field in LOB info is empty.
Column header is not a single row range or a single column range.
Content in filter condition range does not meet the expected format of specified filter type.
Data name in filter condition does not exist in the provided table schema.
Data name in filter condition is empty.
DB connection error.
Either filter condition or table schema not provided.
Failed to create LOB Content Folder.
Filter type is invalid, it must be 0, 1, or 2.
Invalid filter condition, it can only be 0, 1 or 2.
Invalid operator value in filter condition.
LOB character set field cannot be found in the database.
LOB content field in LOB info is empty.
LOB content folder in LOB info is empty.
LOB content folder in lob info not found.
LOB field name cannot be found in the database.
LOB file name in LOB info is empty.
LOB info is empty.
LOB info range is invalid.
No Key order value defined in DB Schema.
Operator in filter condition is empty.
Table name does not exist in provided DB Schema.
Table name is empty.
The provided column header cannot be found in the specified table schema range.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.
Total number of filter condition columns is not even number.
Upload file is not found.