Kbase P164820: 4GL/ABL: How to store the contents of a binary file in a database table?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/29/2010 |
|
Status: Verified
GOAL:
4GL/ABL: How to store the contents of a binary file in a database table?
GOAL:
How to store the contents of a binary file containing NULL characters in a database table field?
GOAL:
How to save the contents of a binary file containing NULL characters and whose size is less than 32K in a database table field retrievable by SQL?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge Category: Language (4GL/ABL)
FIX:
Although the following procedures works under Progress 9.x and OpenEdge 10.x, it was written mainly to be used under Progress 9.x which does not support the BLOB data type fields that can store 1 GB of binary data.
If you are using OpenEdge 10.x, it is better to store the binary data in a BLOB field instead of RAW field as the BLOB data type has a 1 GB maximum size.
Also, please keep in mind that the 32K limit referenced in this solution is on the row size, not on individual fields, so if the record contains other fields in addition to the RAW field, the maximum data that may be stored in that RAW field will be less than its maximum 32K limit.
The following procedure reads the contents of a binary file ( or any file ) into a MEMPTR variable, creates a new record in the database table and assigns the file contents , via the MEMPTR variable, to the RAW field in that database table. The code fails if the file size exceeds 32K maximum limit of the RAW field. The code will succeed if the file size is within 32K maximum limit of the RAW field regardless of whether the file contains NULL characters or not and regardless of whether the contents of the files are binary or not.
Once the data is stored in the database table, it can be retrieved using the SQL SELECT statement executed over an ODBC or a JDBC connection object:
/* Define variables to store file data and file name */
DEFINE VARIABLE mVariable AS MEMPTR NO-UNDO.
DEFINE VARIABLE cFileWithNulls AS CHARACTER NO-UNDO.
/* Initialize file name and MEMPTR variable size */
ASSIGN
cFileWithNulls = "SomeBinaryFileWithNulls"
FILE-INFO:FILE-NAME = cFileWithNulls
SET-SIZE(mVariable) = FILE-INFO:FILE-SIZE.
/* Import file with nulls into the MEMPTR variable */
INPUT FROM VALUE(cFileWithNulls) BINARY NO-MAP NO-CONVERT.
IMPORT UNFORMATTED mVariable.
INPUT CLOSE.
/* Copy MEMPTR variable to database table RAW field */
CREATE dbTableName.
ASSIGN
dbTableName.RawFieldName = mVariable.