Previous Topic

Next Topic

Book Contents

Book Index

Inserting BLOBs or CLOBs

AQT provides an easy way to load a BLOB or CLOB value from a file.

This feature also works for other column types, generically called LOBs:

There are two reasons you may wish to load a LOB column from a file:

To load a LOB value from a file

There are a number of way of loading a LOB from a file. These all use parameter markers, which is the method AQT uses for supplying the LOB data.

Parameter Marker

In the Run SQL window, use a parameter marker (?) in the Insert/Update statement where the LOB is to go. Example:

Insert into blob_table (keycol, blobcol) values(1,?)

When you run this SQL, you will be shown the Enter Query Parameters window. There will be a button Load from File. Click on this to specify the file you wish to load into the LOB column (the text <aqtfile>filename will appear in the parameter value).

Parameterised Insert

Alternatively, in the Run SQL window, you can supply the parameter value (to avoid being prompted):

--aqt parms,parmno=1,value=<aqtfile>filename

Insert into blob_table (keycol, blobcol) values(1,?)

This method is used internally in AQT for loading LOB data.

Insert / Update Row window

In the Insert/Update Row window, specify a ? in the LOB column value. When you run the insert/update, you will be prompted with the Enter Query Parameters window from which you can specify the filename.

Load from File in Insert/Update window

In the Insert/Update Row window, when you click on a LOB column this will show a button Load from File. Click on this to specify the file to be loaded into the column. This will generate a Parameterised Insert statement - click on Preview SQL to see this.

Enter Query Parameters widow

This window prompts you for parameters needed for a query. This will show a Load from File button for parameters of types BLOB, CLOB, IMAGE and CHARACTER/TEXT greater than 1000 bytes.

You will not see this button in Oracle (as Oracle does not give the parameter type). Even though this button is not present you can still load the column from a file. You do this by typing <aqtfile>filename into the parameter value.

Data Loader

The Data Loader can be used to copy data between tables (even if they are on different database). This supports loading LOB data; and can therefore be used for moving LOB data between tables.

Export as Inserts

The Export Data function can export data from a table as a series of insert statements. This function supports LOBs; when data is exported, the LOBs are written as files, and the insert statements formatted as parameterised Insert statements (as described earlier). This script can then be used to load the data (and LOBs) into another table.

LOBs and DB2/UDB

When this feature is used with VARCHAR and LONG VARCHAR columns it sometimes gets a “truncation error” message. The reason for this are not yet understood.

Loading Zero-Length LOBs

Both Oracle and DB2 cannot load zero-length LOBs. The ODBC Driver hangs or crashes when this is attempted. To circumvent this, for these databases AQT will load a single blank into the LOB .

LOBs and Oracle

In order to view and load LOB columns, you must

LOBs and Sybase

AQT has trouble loading LOBs into Sybase. The reason for this is that Sybase doesn't return correct information on the data type of parameters. This means that when you run a query such as:

Insert into blob_table (keycol, blobcol) values(1,?)

AQT doesn't know data type of the ?, so can't pass the data to it correctly. The solution is to manually tell AQT the data type; this is done as follows:

--aqt parms,parmno=1,value=<aqtfile>filename,dtype=image

Insert into blob_table (keycol, blobcol) values(1,?)

Here the data type of the parameter is specified with the dtype parameter.

This is done automatically when you insert a LOB using the Insert / Update Row window, however you must manually code the dtype when you run this statement in the Run SQL window.

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.