FWD » Core Development » Database

PostgreSQL Large Objects
PostgreSQL Binary Files Stored in the DB

H2 BLOB Type
H2 CLOB Type
Binary Data Type in H2

SQL Server BLOB Options
BLOB and CLOB and NCLOB Data Types in SQLServer

Simple JDBC Example of BLOB Usage

The CLOB and BLOB implementation in the 4GL is pretty simple. They describe the actual contents of the field as a "locator".

BLOB (Binary Large OBject) specifies a database table or temp-table field that contains a BLOB locator, which points to the associated BLOB data stored in the database. You must use a MEMPTR to manipulate the binary contents of a BLOB field in ABL.

CLOB (Character Large OBject) specifies a database table or temp-table field that contains a CLOB locator, which points to the associated CLOB data stored in the database. You must use a LONGCHAR to manipulate the character contents of a CLOB field in ABL.

It turns out that "locator" is just a text name by which the LOB can be found, at least as represented in the database export. As a result, the data import for BLOBs and CLOBs should be quite straightforward.

Consider this snippet of schema definition:

ADD FIELD "laRge_xml_dOCument" OF "customer_notes" AS clob 
  FORMAT "x(8)" 
  INITIAL ?
  POSITION 7
  LOB-AREA "Primary Data Area" 
  LOB-BYTES 104857600
  LOB-SIZE 100M
  CLOB-CODEPAGE "iso8859-1" 
  CLOB-COLLATION "basic" 
  CLOB-TYPE 2
  ORDER 60

ADD FIELD "BINARY_DOCUMENT" OF "customer_notes" AS blob 
  DESCRIPTION "Binary encoded customer documentation." 
  FORMAT "x(8)" 
  INITIAL ?
  POSITION 8
  LOB-AREA "Primary Data Area" 
  LOB-BYTES 104857600
  LOB-SIZE 100M
  ORDER 70

A portion of the customer_notes.d file might look like this:

0 "" "" "" "hello" 0 "laRge_xml_dOCument!ISO8859-1!12104_1.blb" "BINARY_DOCUMENT12104_27.blb" 
0 "" "" "" "goodbye" 14 "laRge_xml_dOCument!ISO8859-1!12104_2.blb" ?
0 "" "" "" "what" 1 ? "BINARY_DOCUMENT12104_28.blb" 
0 "" "" "" "ever" 29 ? ?

If there is no locator, it will be set to ?. If there is a locator, it takes these forms:

• BLOB <field_name_with_exact_case_of_field_in_schema_file><seed_value_or_some_number>_<ordinal_value_unique_to_that_specific_lob>.blb
• CLOB <field_name_with_exact_case_of_field_in_schema_file>!<CODEPAGE>!<seed_value_or_some_number>_<ordinal_value_unique_to_that_specific_lob>.blb

Also in the same dump directory, there will be a file with the exact name as each non-unknown locator string. In the example above, the following files would be present:

laRge_xml_dOCument!ISO8859-1!12104_1.blb
laRge_xml_dOCument!ISO8859-1!12104_2.blb
BINARY_DOCUMENT12104_27.blb
BINARY_DOCUMENT12104_28.blb

I think these files just contain the unmodified LOB. I'm sure of it in the case of the CLOB, since the contents are easily read if you have the proper codepage support. I suspect it is true for BLOB too, but this has to be checked.

FYI, this suggests that a simple and generic LOB implementation could store just a locator in the filesystem for the file and then the persistence layer would handle the direct reading/writing of the file as needed. The would be easy and would work for all database types without figuring out the individual funkiness for each database. The down side is that direct usage of the database would not be able to access the LOBs without also having file system access. For most direct SQL users, this would be a problem. So it probably isn't the right way to go.

The way Hibernate maps BLOBs is to byte[] or to java.sql.Blob; CLOBs to String or to java.sql.Clob.

Using the byte[] or String forms (would be raw and longchar for us, respectively) is the much simpler approach. DMOs are POJOs; the data would be accessible to business logic at any time, like any other field, once a record is retrieved or created. The downside is memory consumption; it doesn't take many DMOs with big LOBs stored in memory to cause a problem.

Using the Blob and Clob forms greatly complicates the implementation, because the data must be retrieved lazily, when accessed by business logic with a DMO getter or setter. This requires having an active Hibernate Session available at that moment. The FWD persistence layer is not designed that way. A Session object is only available when populating or saving a DMO; it is not guaranteed to be around at all times.

The downside is memory consumption; it doesn't take many DMOs with big LOBs stored in memory to cause a problem.

There is no guarantee that the CLOB/BLOB field will be accessed. This could be massive amounts of data just to retrieve each record.

Note that you can only interact with these fields using something like COPY-LOB. In other words, with an explicit 4GL feature that is aware of the support.

Perhaps my "generic LOB implementation" mentioned in #3500-2 is a good option.

I think the data has to be accessible via the database.

Eric Faulhaber wrote:

I think the data has to be accessible via the database.

That is clearly better for apps that want to use the data later from non-4GL code. But I don't think it is reasonable to use byte[] and String for the data.

Turns out, I need to fetch the LOB data at the time the record is retrieved, otherwise we can't be sure we can get it later. A JDBC Blob or Clob is only a valid locator for the duration of the transaction that was open when it was created. We can't be sure that transaction will be open when the LOB field is accessed some arbitrary time later. So, we have to fetch the data at the time the DMO is created.

Nevertheless, I am using Blob and Clob for the Hibernate user type classes anyway, instead of byte[] and String, respectively, This will allow us some flexibility, based on the query. That is, the Hibernate mapping will return a Blob/Clob, which in the common case will be used immediately to fetch the LOB data (while we are sure the transaction is still open). However, we can consider optimizing this. For example, once we honor field lists, we can skip the LOB fetch if the field is excluded from the field list. As another optimization to avoid memory problems, we can avoid fetching full records (i.e., primary keys only), when a table contains a LOB.

I've added basic conversion and runtime support for BLOB and CLOB in 3750a/11334. Does not include changes to data import yet, but I've tested it with a temp-table test case.

3750a/11346 adds support for the import of BLOBs and CLOBs. It requires that all LOB export files reside in a subdirectory of the data export directory, named lobs.

What work is left in this task?

Greg Shah wrote:

What work is left in this task?

Basic LOB CRUD access is supported, but some language features that use LOBs need to have runtime support updated. The ones I can think of are BUFFER-COPY and BUFFER-COMPARE; these need review and work to ensure LOBs are handled correctly (and the NO-LOBS option is honored).

See also #2135-11.

Please note 3750b revision 11370 contains a refactored form of the BUFFER-{COPY|COMPARE} conversion and runtime. This was needed to add support for the NO-LOBS option to these statements, without exploding the API. Re-conversion is needed, otherwise any old forms of these converted statements/methods will try to invoke runtime APIs that no longer exist.

Can I close this task?

Greg Shah wrote:

Can I close this task?

Yes.