|
|
|
|
|
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:
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.
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).
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.
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.
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.
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.
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.
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.
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.
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 .
In order to view and load LOB columns, you must
Statement has failed, however your database does not return any errors".ORA-01461: can bind a LONG value only for insert into a LONG column"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.