Using the SQL Script Tool

The SQL script tool can be accessed under Tools|Run SQL Script. This is avery powerful tool to easily manipulate or update data in your database, but also so powerful that you can delete (empty) your whole database with one simple command.

SO USE THIS TOOL WITH CARE AND ONLY IF YOU KNOW WHAT YOU ARE DOING!

Sometimes you may receive a scipt from the Aquabase developers which you need to run in order to achieve an update or if you have made a mistake. In that case it is generally safe to run the script, BUT: do not change anything in the script!

The tool uses the SQL language (as the underlying Aquabase databases are based on SQL) to execute lines of code separated by a delimiter (normally the ;). This delimiter can, however, be selected before running the script and it might be necessary to change the delimiter depending on the script (e.g. if it contains triggers).

A few simple scripts could be:

UPDATE basicinf SET INFO_SOURC = ‘F’ WHERE INFO_SOURC = ”;

This would update the INFO_SOURC field in the BASICINF table with an “F”, but only if the field has a no value (“No information”) stored in it. This is different from:

UPDATE basicinf SET INFO_SOURC = ‘F’ WHERE INFO_SOURC IS NULL;

This would do the same, but only for fields which are empty (NULL), so nothing is stored in it. It might be necessary to check for both cases.

Be careful when updating the X_COORD and Y_COORD fields as the coordinates stored in them are used to automatically update the LONGITUDE and LATITUDE fields (and the GEOMETRY), but this will work only in Aquabase and not any other application or when executing a script. Also: while the DATE_ENTRY field can be updated with a script or manually (or auotmatically by Aquabase) the DATE_UPDTD get updated automatically with triggers in the database and should therefore be left alone.