Previous Topic

Next Topic

Book Contents

Book Index

More info on Load

Refresh Target Tab

If the Target table definition is changed, the Refresh button (top of Data Loader window) will reload the columns in the Target tab.

Type Conversion

Nothing special needs to be done in order to do type conversion of data (for instance, from string to numeric values). When loading data, AQT supplies all column data as strings. The ODBC driver (which does the low-level interface to the database), will convert the string values to the appropriate data type. As long as the data values are valid (eg. all numeric values are indeed numeric) then type conversion will happen automatically.

Binary, LOB and Image Columns

The Data Loader will load binary columns, and large columns such as BLOBs and CLOBs. There is no limit on the size of a LOB column being loaded. This can be used to transfer LOB data between databases of different types.

If you are using the Load using insert statements method, AQT will load the LOBs by using the parameterised insert method (as described in Inserting BLOBs or CLOBs). This saved SQL script will include these parameterised inserts and can be run as an SQL script through the Run SQL window (though you will have to do this prior to your AQT session ending, otherwise AQT will purge the LOB files).

Load speed

The Data Loader is written to run fast. However if you have a very large table then it will be quicker to use the native utilities of your database, rather than the AQT Data Loader.

If you switch on Options > Debug Mode, timing information on the Data Loader will be written to the debug file. This can help understand the performance of the loader.

Save/Open Load Specifications

Once you have set up the options for loading a table, you can SAVE these options with File > Save Load Options. This will save to file the specifications for this load. If you later use File > Open Load Options, the load specification will be opened from a disk file and set up in the Load data window.

Batch mode

You can also run the Data Loader in batch mode. This is done by specifying an --aqt load batch command. When you do File > Save Load Options or File > View Load Options, this will generate an --aqt load batch command that you can use in your batch file. This batch command can also be used in the Run SQL window.

See Scripting - Load for more on this.

Loading Multiple Tables

If you wish to load multiple tables, you need to run multiple --aqt load statements. You can use the Generate Text utility to generate these --aqt load statements. The procedure for doing this is described in more detail in Scripting - Load.

Load-Update with MySQL

When you run an Update in MySQL. it returns the message Number of rows affected=0 if the data has not changed. For instance if you run:

UPDATE Customer_Details SET customer_type ='T' WHERE cust_code = '000001'

you will get Number of rows affected=1

If you run the same statement a second time, you will get Number of rows affected=0. This is because the second time this is run, no data is actually changed so MySQL return a row count of zero.

This is different behaviour from all other databases, which will always return a row count of 1 for this statement. A row count of zero is used to indicate that no data has been found (by the WHERE clause).

This causes a problem with Load-Update; when it sees a row count of zero, it assumes that the row-update has failed to find the row, so gives an error. If you are using the default options, the load will then terminate after 20 such errors.

You can get around this problem by specifying (on the Errors tab) Other Errors > Continue.

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