Export using SQL

In 4D v12.1, mechanisms for exporting data from the database using SQL have been enhanced so that developers can configure the operation more specifically according to the type of data being exported:

• The SQL EXPORT DATABASE and SQL EXPORT SELECTION commands now accept an additional parameter that allows you to embed binary data in the export file,
• The 4D SQL engine now supports data stored in hexadecimal format.

The SQL EXPORT DATABASE and SQL EXPORT SELECTION commands accept an additional optional parameter: fieldLimitSize. This parameter is only taken into account when the database contains BLOB, Picture or Text type fields. 

In this parameter, you must pass a value in bytes indicating the size below which the BLOB, Picture and/or Text type fields (text stored externally, i.e., stored outside of records) will be exported within the main "Export.SQL" file, and not in separate files inside the BLOB subfolders.

You can use this parameter to control the quantity of external files generated during an export and therefore reduce how long the operation lasts, in particular when the database contains a large number of BLOB, Picture and/or Text files containing limited size data.

When they are included in the main "Export.SQL" file, the binary data (BLOB or Picture fields) are stored in hexadecimal format. In order to be able to interpret them, the 4D SQL engine now supports this notation (see below). 

The 4D SQL engine now supports hexadecimal notation for literal values (literal). Hexadecimal values consist of numbers (0 to 9) and/or letters (A to F) and can express all data types as bytes. A byte is always defined by two hexadecimal values.

To indicate the use of this notation in an SQL command, you must use the standard SQL syntax for hexadecimal values:

X'<hexadecimal value>'

For example, for the decimal value 15, you can write X'0f'. You can set a blank value (zero byte) by writing X''. 

Note: This functionality is related to the new parameter for the SQL EXPORT DATABASE and SQL EXPORT SELECTION commands, since any binary data that may be embedded in an export file will be stored in hexadecimal format.