Row display and row change

Open the Row Display window from the Data Display window by any of the following methods:

• Use the Row menu
• Any of the five yellow icons (Row detail, update row, delete row, insert duplicate row, insert new row)
• Click in a row then right-click to display the quick menu.

In addition, you can activate this window from the Database Explorer window, by right-clicking a table and selecting Add New Row. This allows you to insert a row into a table without first having to display the table.

The Row Display window has two uses:

• to display the details of a table/query row
• to provide an easy way to update your data.

Toolbar

Some of these buttons will not be visible or active, depending on what mode (display, update etc) you are in.

You can customize the appearance and the buttons that appear on this toolbar.

Displaying rows

The Row Display window opens with the current table/query row displayed, or the first row if no rows were selected.

• use the arrow icons or the Goto menu-item to move to other rows in your table.
• for a particularly large value, click on the row then hit F2 or Edit > Extended Edit to see the data in a larger window.
• click on Print for the two print options: Print Screen and Print Data.
• resize the rows to display large column values.

Changing rows

Most of the other features of this window are to do with updating / deleting / inserting rows in your table.

To amend your table data, select one of the “update modes”:

• Update mode to update this or other rows in the table.
• Delete mode to delete this or other rows in the table.
• Insert Dup mode to insert a new row in the table, using the values of the current row as a base.
• Insert New mode to insert a new row in the table with the table columns initialised to default values.

  These will be the default values of the table columns as defined to the database. If these aren’t available, the default values will be blank, zero or current date/time depending on the data type.

• Preview SQL displays the SQL that is being generated. You will see the effect of any Where and Update options that have been set.

Updating a single table row

• Tick cell/s in the Where column to indicate the table columns that comprise the unique key. If your table has a primary key or unique key, these columns will be marked for you automatically.

  For Oracle and Informix users, if the ROWID is in the result-set, it will be used as the Where column.

• Change the data values. As you change a value, the Upd column will be ticked.
• Click on the Update button.

Get the existing values of a Column

• you can click on Get Values to get the existing values of a column. These will display as a dropdown list of values to select from.
• by default, Get Values will get the existing values of the column in the table. However if Options > Row Display/Update > Get Values from Foreign Keys is specified, AQT will see whether there is a foreign key defined for that column. If so, it will get the values of the foreign key column.
• clicking on Get Ext Values will show you the Extended Get Values dialog. This provides more options for the Get Values function.
• clicking on Get Constants will show you common constant values and expressions for the data type.
• once you have displayed a drop-down list using one of these buttons, it will be redisplayed when you next click on the column.
• for Date / Time and Timestamp columns, there will also be a button Select Date. This allows you to select a date/time value from a calendar box.

Updating multiple rows (mass update)

• Tick cell/s in the Where column to indicate which rows in your table are to be updated
• Change the data values. As you change a value, the Upd column will be ticked.
• Optionally, click on the Check Number button. AQT will tell you how many rows will be updated by your update. This is a useful safeguard before running a complex or critical update.
• The Preview button will display the SQL that AQT will run to perform your update.
• Click on the Update button.
• If you have Safe Update Mode selected, and you have updated more than one row, you will be prompted for whether this update is to proceed.

Note:

Delete mode

In Delete mode, one extra column, Where, will be displayed in the grid. Tick cells to specify which of the table columns form the Where clause for the delete.

Use the Preview SQL and Check Number buttons as described in the previous paragraph to check the effect of your proposed deletions before you run the delete by using the Delete button. Check Number is a useful safeguard before running an update that is complex or critical.

Updating / Deleting every row

When in Update or Delete mode, AQT will insist that you have a Where column selected. This is to prevent inadvertent updating / deleting of the entire table.

If you really wish to update / delete every row in the table, this can be done by:

• selecting some Where column (to keep AQT happy)
• click on Preview SQL
• remove the Where clause from the SQL
• click on Run

Insert mode

In this mode, one extra column, Include, will be displayed in the grid. Use this column to specify which table columns are to be included in the generated Insert statement. By default all table columns are included, however you might wish to exclude some table columns from this grid if you want them to pick up their default or generated value.

You can include all or no columns in the Insert with Edit > Include All or Include None.

Field view options

For large text fields, F2 or Extended Edit will display the entire value.

View a drop-down list of all existing values of the field by hitting F3 or Get Values. A drop-down arrow will appear on the value-cell. Click on this to get the list of values.

Ext Get Vals will show you the Extended Get Values dialog; this is similar to Get Values but gives you more options about the values retrieved.

F4 or Get Constants will give you a drop-down list of typical constants and literals for the column.

Altered data

If you have changed your column values but want to return to their original values, hit Ctrl+R or click on Row > Reload Current.

After you have updated / deleted / inserted some data, these changes will not be reflected in the Data Display. Refresh reruns the query to repopulate the data in the window.

Copy / Paste

You can copy multiple column values by selecting them and hitting Ctrl+C (you select multiple values by dragging the mouse over them).

You can paste data by clicking on a cell and hitting Ctrl+V.

• if you are edit-mode on the cell (row-pointer on the left is a little pencil), the data will be pasted into the cell.
• if you are not in edit mode, the data will be copied into either a single or multiple cells. AQT will examine the clipboard to see whether it holds a single value or multiple values. Multiple values are delimited by either a linefeed or a tab. This means that, when copying data from Excel or AQT, you can copy a set of values either horizontally (in a row) or vertically (in a column), and it will paste successfully into AQT.

Auto-generated data

If your table has an auto-generated column then (during Insert mode), AQT will place (auto) in the column. You should not supply data for this column as it will be generated by the database.

Key columns

If your table has a primary key or unique key, these columns will automatically be set as the Where columns during Update and Delete modes.

You can see which are your key columns by clicking on Edit > Show Primary Key (or Ctrl+K). The names of your primary key columns will be shown in blue.

If your table/view doesn't have a key, clicking on Edit > Show Primary Key will allow you define a User-Defined Key.

Internal RowID

For Oracle, Informix and SAP-DB, there is an internal Rowid that can be displayed with the table. You can display this column automatically by selecting option Options > Display Options > Show Rowid. If this RowID is being displayed, AQT will use it as the Where column for Update/Delete modes. This is useful if your table does not have a unique key.

Date, Time and Timestamp columns

• When you go into one of the update modes, any date / time / timestamp columns will be changed from their display-format to their update-format. If your timestamp column has a zero time-part (which is normally suppressed during the display of the column), you will notice that this has been added to the column. This allows you to update this time part of the column.
• For Oracle, you will get an option Add To_Date. This specifies whether you want the TO_DATE function applied to any date values being updated, or the TO_TIMESTAMP function for timestamp values. Click on Show Quotes to see the column value with this function included.
• You will get a button Select Date. Clicking on this shows you a date-picker window, which allows you to select a date, timestamp or time value.

LOB columns

AQT makes it easy to load a value of a BLOB/CLOB column from a file. Once you set focus on a LOB column a button Load from File will be displayed. Clicking on this allows you to specify a file to be loaded into the LOB during the Update/Insert.

• Once you have selected a file, you will get <aqtfile>filename in the column value. This is a control keyword to tell AQT to load the column from a file. You can also invoke this function by manually typing <aqtfile>filename (specify a valid filename) into this column.
• Click on Preview SQL to see how AQT processes the LOB insert/update. It will replace the <aqtfile>filename with a parameter marker (?), and use an AQT control statement to set the value of this parameter. It is done this way because files can only be loaded into LOB values by using parameters.

Special Values

• If you specify a value of ?, AQT will interpret this as a parameter marker and prompt you for the value when you do the update/insert. If you really want a value of ? inserted into your database, use the Show Quotes option to show how AQT will use quotes, then add your own quotes to get the result you want.
• If you specify a value of NULL, AQT will set the column to NULL. If you really want a value of NULL inserted into your database, use the Show Quotes option.

Using values with special meanings

By default, AQT encloses all string values in quotes, except

• Null
• ?
• Date/time columns with a non-numeric value

This works well in almost all situations, however you may need to be able to specify whether a string is enclosed in quotes.

Examples of this are:

• if you want to set your column to a system variable. Examples: SYSDATE, USER.
• if you set the column to another column, or a function involving another column.

  Examples: Current_Salary/100, Substr(Last_Name,1,1), Units_Ordered – Units_Delivered.

• you want to specify a value of ? or NULL.
• You want to include trailing blanks with your character value (normally AQT will remove trailing blanks).

To see whether AQT has enclosed a value in quotes, use the Show Quotes option to view the field as formatted. You can then add or remove quotes, as needed.

For example, without Show Quotes set:

Running a Change

• to run your change, click on the Update / Delete / Insert button.
• if you change more than one row, and you have Safe update mode selected, you will be prompted as to whether you want the change to proceed or not. This allows you to back out the change should it affect more rows than you expected.
• alternatively, before running your change you can click on Transaction to enter transaction mode. This will enable you back-out your change if you decide that it didn’t do what you want.

Preview SQL

Before running your change you can click on Preview SQL to see the SQL statement that will be run. While in the Preview window:

• you can modify the SQL
• click on Run to run the SQL.
• click on Copy to SQL Window to copy this SQL to the Run SQL window.
• click on Save Query to save the SQL. Retrieve Query retrieves a saved query.
• if you change this SQL but wish to return to its original version, click on Refresh SQL.

Check number of rows that will be affected

Before running an Update or Delete you can click on the Check Number button. This will run a query against the database to tell you the number of rows which will be affected by your Update/Delete. This is a useful safeguard before running a complex or critical update.

If you have a large table, this option could take a few seconds.

Sorting the Grid

By default, the grid is not sortable. If you wish to do this, click on Edit > Enable sorting. Once this option has been checked, the columns in the grid can be sorted by clicking on the column headers.

Deleting Duplicate Rows

If your table doesn't have a unique index, it is possible to end up with two (or more) identical rows in the table. Deleting one of the duplicate rows can be difficult to do using SQL, as any Where condition will result in both rows being deleted.

If your database allows you to display internal rowids, then you can use these to delete one of the duplicate rows.

Otherwise, the following procedure can be used:

• display one of the duplicate rows
• click on the Del button in the toolbar to go into Delete mode.
• Click on Delete. You will probably get a message saying You have affected 2 rows. Do you want this to proceed?. Click on Yes. Both the duplicate rows will then be deleted.
• now click on the Dup button in the toolbar to go into Load Duplicate Row mode.
• click on Insert. This will insert the row back into the table.