Program: TBLEDIT

Title: Database Table Editor

Version: 1.5

• Introduction
• Running TBLEDIT
• Menus and Controls
  • Grid Tab
  • Record Tab
• Custom Lookups
• SQL Update
• SQL Delete

Back to:

• Main Index
• Data Cataloguing Index

Introduction

TBLEDIT is a utility that allows direct editing of database tables. Tables can be opened from all databases supported by LEA applications (see Using Databases in LEA Applications - LEA Database Formats).

TBLEDIT also enables the contents of tables to be searched and printed.

Running TBLEDIT

Select a table to open from the File-Open menu, drag a table file from an Explorer window onto the TBLEDIT window, or specify a database and table to open on the command-line:

TBLEDIT can be run with a command-line similar to the following:

TBLEDIT [[-remote] -uCONNECTSTRING] [-pPARAMFILE] [TABLENAME]

or

TBLEDIT [-pPARAMFILE] [TABLEPATH] 

where:
The optional switch -p specifies a parameter file to load containing editing options and lookup field settings (see Custom Lookups below).
The optional switch -u specifies an Oracle connection string in the form USERNAME/PASSWORD@DATABASE in the same format as used by Oracle SQL*Plus and other Oracle utilities. You can also use default OS-user authentication if the database is configured to accept this by using a connection string /@DATABASE. The additional switch -remote allows you to connect to an Oracle database when the Oracle client is not installed - in this case the connection string should be of the form USERNAME/PASSWORD@HOSTNAME:PORTNUMBER:SID (see Using Databases in LEA Applications - Oracle Connection)
TABLENAME is the name of a table, view or synonym within the database specified above. If no CONNECTSTRING is specified, you will be prompted with a login dialogue to connect to Oracle. The table name should include a schema if it is not a public synonym or owned by the specified user.
TABLEPATH is the complete path of a dBASE database table (DBF) file.

Menus and Controls

TBLEDIT displays a database table in grid or single record format. You can resize the columns to display more information on screen, re-order and hide fields (for easier editing, and printing). You can also search for specific values in fields, and (for Oracle tables only) filter the displayed records.

TBLEDIT has the following menu options:

• File
  • Open  
    Select a table from the Table Selection Dialogue - see Using Databases in LEA Applications.
  • Close
    Close the currently open table
  • Import
    Add data from an external source to the current table:
    • Import From File - select a comma-delimited (CSV) file from the file selection dialogue.
    • Import From Table - select a table from the table selection dialogue.
    A PRMEDIT dialogue will be displayed (see Using Databases in LEA Applications - Database Import and Export for the import parameters). Records from the selected source will be added to the current table. If an error occurs on any row, then the import will terminate immediately.
  • Export
    Output the current table:
    • Export To File - you can export either to formatted text files (HTML or column-delimited text suitable for word-processing), or comma-delimited (CSV) files, suitable for import into Microsoft Excel, or import into other databases. If you output as comma-delimited, a PRMEDIT dialogue will prompt you for the export options (see Using Databases in LEA Applications - Database Import and Export)
    • Export To Table - select a table from the table selection dialogue. The current table will be copied to the destination you specify.
  • Print 
    Print a simple report of the displayed database table data grid. To print only highlighted rows from the grid, click the 'Selected' radio button in the Print dialogue.
    Column-width in the report will be based on the displayed width of the columns in the grid. You can use the Print Preview option (below) to determine whether the output will fit onto the page.
  • Print Preview
    Display a facsimile of the printed output. Note that all records will be displayed - you cannot choose to display just highlighted records..
  • Page Setup
    Set the default printer, page size and paper orientation in a Page Setup dialogue.
• Parameters
  • Edit 
    Editing parameters are displayed in a standard PRMEDIT dialogue. They can be loaded and saved in files with extension TBE
    There are two edit modes to choose from:
    
    • Cached (default) - this uses the commit/rollback model for editing. Changes you make to the table data must be explicitly saved using the Commit Changes option below. This option also allows changes to be undone, using the Rollback option below, which will return to the last saved version of your table.
      For Oracle tables, a transaction will start when you start editing the table data. Edited records, inserted records and deleted records will not be visible to other users until you explicitly commit your changes.
      For dBASE tables, all records from the disk file are loaded into memory. Changes will not be written back to the DBF file on disk until you commit your changes. This edit mode is not suitable for multi-user editing of dBASE tables, as the commit operation will overwrite the entire disk file with your current record set, thus losing concurrent changes made by another user. This edit mode does not allow editing of dBASE memo fields.
    • Immediate - each edited record, inserted record or deleted record will be posted directly to the underlying table, and be visible to other users. Use this edit mode to edit dBASE memo fields.
    Lookup fields can be used to help edit tables which use referential integrity. When you edit a field, a drop-down list can supply a list of the available values for this field. Select one of the following options:
    
    • None - no field lookups used, and drop-down lists will not be displayed.
    • Foreign Key (default) - for Oracle databases, where the definition of the table includes foreign key constraints, all the values of the referenced field will be displayed in a drop-down list. This option does not work with dBASE tables since there is no built-in referential integrity.
    • Custom - see Defining Custom Lookups below.
• Database - this menu is only applicable when the edit mode is Cached.
  • Commit Changes 
    Save your modifications to the table, and make them visible to other users. Once you commit changes, they cannot be undone.
  • Rollback 
    Undo all your modifications since the last Commit.
• Table
  • First 
    Jump to the first record in the grid
  • Previous 
    Move up to the previous record in the grid
  • Next 
    Move down to the next record in the grid
  • Last 
    Jump to the last record in the grid
  • Insert New Record 
    Add a new empty record to the table.
  • Edit Current Record 
    Edit the field values for the current record
  • Post Changes to Current Record 
    Write a modified record to the table. Moving to another record while in Edit or Insert mode will implicity post the current record if it has been modified.
  • Delete Current Record 
    Prompts you to delete the current record from the table.
  • Cancel Changes to Current Record 
    Undo modifications made to one or more fields for the current record.
  • Refresh/reload 
    Reload the current table. This will display any modifications which have been made to the table by other users since you opened it.
  • Adjust Columns
    Re-size the grid columns to the minimum width so that all columns are wide enough to display the text for each row currently displayed.
  • Set Column Order
    Displays the Table Column Order dialogue, allowing you to re-order or hide columns in the displayed data. See Using Databases in LEA Applications
  • View Table Structure (TBLVIEW)
    Opens the current table in TBLVIEW to examine its structure
• Data
  • SQL Update
    Specify a field value to modify and a field value to match to update multiple records. This option is only valid for Oracle tables. See SQL Update below.
  • SQL Delete
    Specify a field value to match to delete multiple records. This option is only valid for Oracle tables. See SQL Delete below.

Grid Tab

The Grid tab displays the contents of the table in a grid. You can use the Table menu options to navigate and edit record data.

For Oracle tables/views, you can order on a column in ascending or descending order by clicking the column header. To order by more than one column, hold down SHIFT while clicking the column header. You can also filter the displayed records using the filter bar, to display only records which match some criteria you specify. Click the top-left corner of the grid to display a popup menu, and click Filter bar. Enter your filter conditions, and then right-click the top-left corner of the grid to apply your filter.

The filter bar will look something like this:

You can type expressions in the edit boxes under the column titles to filter the records displayed. Each filter statement is of the form

[RELATIONALSIGN]STATEMENT

Where RELATIONALSIGN is one of the following =, <>, <, >, <=, >=. Omitting the sign has the same effect as using the equals sign (=).
STATEMENT is any alphanumeric text which is of a valid data type for the corresponding field. For string fields you can specify a filter mask (wildcard) in the statement substituting masked characters with an asterisk or percent ('*' or '%') symbol. To substitute a single character use the underscore ('_') symbol.

The example displayed above limits the displayed records to those where the FILEPATH contains 'IMAGE' and the FILESIZE is greater than 1000.

The data grid also allows interactive searching for a specific record (applicable for both Oracle and dBASE tables). Click the top-left corner of the grid to display a popup menu, and click Search bar. This will display the search bar at the top of the grid. Then select the column and start typing the leading characters of the record you want to locate in the grid. The current record pointer will be moved to the nearest match found in the table.

Memo/CLOB/LONG fields will be displayed in the grid with a ... button in each grid cell. Click the button to open the memo field editor (see Using Databases in LEA Applications)

Record Tab

The Record tab displays a single-record view of the table. The fields are displayed vertically, in the same order as the grid columns (see the Table-Set Column Order menu option).

You can widen the edit fields by dragging the header. Memo and Oracle CLOB fields can be viewed and edited either in a separate form by double-clicking on the edit field (see the memo field editor), or in-place by clicking the Expand >> button to the right of the edit field. You can resize the in-place memo field by dragging the header and the horizontal grab-bar underneath the memo.

You can navigate and edit records in the same way as in the grid using the Table menu options.

Custom Lookups

You can create customised lookup fields with drop-down lists of available values to help when editing tables which enforce referential integrity or to standardise field values. In the Parameters-Edit menu option, set the Lookup Fields parameter to Custom, then enter the parameters for each lookup field you want to create.

This option is intended for use when the foreign key field is an identifier for a value in another table, rather than the value itself. For example, a PROJECT table may contain a field CUSTOMER_ID, which references the CUSTOMER_ID field in the CUSTOMER table, where the actual field of interest is the field CUSTOMER_NAME.

This option works with both Oracle and dBASE tables.

You can define multiple custom lookup fields for a table (up to 20 in this version of TBLEDIT). Use the standard PRMEDIT mutiple page options to navigate your lookup parameters.

Key Field - the name of the field in the current table to match in the reference table.

Reference Table - the name of the table containing the lookup values. For Oracle tables this should be of the form SCHEMA.TABLENAME. For dBASE tables this should be a complete path.

Reference Field - the name of the field in the reference table to match with the Key Field.

Result Field - the name of the field in the reference table whose values will be displayed.

Hide Key Field YES/NO - (default YES) - specifies whether the original Key Field will be hidden by default, and replaced by the lookup field, or whether both the original field and the new lookup field will be displayed.

Note: custom lookup fields are table-specific. You cannot create generic custom lookup parameters. If an error is encountered when setting custom lookups, the Lookup parameters will be reset to 'None'.

SQL Update

The Data-SQL Update menu option allows you to update multiple records in the table using a constructed SQL UPDATE statement.

Update Field - select the field to update from the drop-down list

Update Value - enter the new value for the Update Field.

Where Field - select the field to match from the drop-down list.

Where Operation - select the operation type for the field match:

• EQUALS - the value entered in Where Value must match exactly - uses Case Sensitive
• STARTS WITH - only valid for string fields - uses Case Sensitive
• CONTAINS - only valid for string fields - uses Case Sensitive
• BETWEEN - only valid for numeric and date fields - uses Where Value and Value 2
• > - only valid for numeric and date fields
• < - only valid for numeric and date fields
• >= - only valid for numeric and date fields
• <= - only valid for numeric and date fields
• IS NULL
• IS NOT NULL

Where Value - enter the value for the match operation - not required for IS NULL or IS NOT NULL operations.

Value 2 - enter the second value required for a BETWEEN match.

Case Sensitive - YES/NO - determines whether string matches are case sensitive.

An SQL UPDATE statement is constructed of the form

update <CURRENT TABLE>
set <UPDATE FIELD> = <UPDATE VALUE>
where <WHERE FIELD> <OPERATION> [<WHERE VALUE> [and <VALUE 2>]]

SQL Delete

The Data-SQL Delete menu option allows you to delete multiple records in the table using a constructed SQL DELETE statement.

Where Field - select the field to match from the drop-down list.

Where Operation - select the operation type for the field match:

• EQUALS - the value entered in Where Value must match exactly - uses Case Sensitive
• STARTS WITH - only valid for string fields - uses Case Sensitive
• CONTAINS - only valid for string fields - uses Case Sensitive
• BETWEEN - only valid for numeric and date fields - uses Where Value and Value 2
• > - only valid for numeric and date fields
• < - only valid for numeric and date fields
• >= - only valid for numeric and date fields
• <= - only valid for numeric and date fields
• IS NULL
• IS NOT NULL

Where Value - enter the value for the match operation - not required for IS NULL or IS NOT NULL operations.

Value 2 - enter the second value required for a BETWEEN match.

Case Sensitive- YES/NO - determines whether string matches are case sensitive.

An SQL DELETE statement is constructed of the form

delete from <CURRENT TABLE>
where <WHERE FIELD> <OPERATION> [<WHERE VALUE> [and <VALUE 2>]]

See also:
[Using Databases in LEA Applications]
TBLVIEW - Database Table Viewer