Program: TBLVIEW

Title: Database Table Viewer

Version: 2.1

• Introduction
• Running TBLVIEW
• Menus and Controls

Back to:

• Main Index
• Data Cataloguing Index

Introduction

TBLVIEW is a utility that allows viewing of database tables, views and user-defined SQL SELECT queries. Tables can be opened from all databases supported by LEA applications (see Using Databases in LEA Applications - LEA Database Formats).

TBLVIEW also enables tables to be searched and printed, and exported to text and HTML files.

TBLVIEW displays data as read-only - tables cannot be altered and values cannot be edited. To import data into tables, or to edit values use the TBLEDIT utility.

Running TBLVIEW

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

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

TBLVIEW [[-remote] -uCONNECTSTRING] [-d] [TABLENAME]

or

TBLVIEW [-d] [TABLEPATH] 

where:
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 optional 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).
The optional switch -d instructs TBLVIEW to display the Definition tab rather than the default Data tab.
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 file.

Menus and Controls

TBLVIEW displays the data and structure of database tables and SQL SELECT queries. It has the following menu options:

• File
  • Open  
    Select a table from the Table Selection Dialogue - see Using Databases in LEA Applications.
    The table data will be displayed in a grid in the Data tab, and the table's structure will be displayed in the Definition tab.
  • Export Data to File
    Export the table data as one of the following formats:
    
    • ASCII column-delimited text file (suitable for word processing)
    • HTML table
    • ASCII comma-delimited text file (suitable for import to Microsoft Excel)
    Select the output type and filename from the Save dialogue. If you have resized any grid columns to zero width, or removed them from the display using the Set Column Order menu option, they will not be output. For output to a column-delimited text file, the width of the columns in the text file will be based on the displayed width of the columns in the grid. Columns in the output file will be in the same order as displayed in the grid.
  • Export Structure
    Export the table structure (See Table Definition below) as an ASCII text file or as HTML.
  • 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.
  • Close Window
    Close the current TBLVIEW window.
• Database
  • Connect to Oracle
    Display a login dialogue to set an explicit connection to an Oracle database. You can also connect to Oracle via the table selection dialogue displayed by the File-Open menu option.
  • Disconnect from Oracle
    Close the current connection to an Oracle database. If anyTBLVIEW window is currently displaying an Oracle table/view/query then this will be closed as well.
  • Edit Query
    Edit an ad-hoc SELECT query to run against your Oracle database. The results of the query will be displayed in the data grid.
    See Query Editor below.
• 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
  • 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 in read-only mode.
  • Adjust Column Widths
    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
• Window
  
  • New TBLVIEW Window
    Open a new TBLVIEW window to look at another table, or a different view of the same table.
    Any TBLVIEW window can open a table from any supported database (ie Oracle or dBASE), however, with Oracle tables all the TBLVIEW windows share the same connection to Oracle, so if you reconnect as a different user, all currently open Oracle tables/views/queries will be closed. To view Oracle tables as a different user, start a new copy of TBLVIEW from the Lynx Launcher.
    
  • Window List
    Display a modeless dialogue box listing all current TBLVIEW windows. Select the window's title to switch focus to that window.
  • Tile Windows
    Tile all open TBLVIEW windows horizontally on the screen.
  • Cascade Windows
    Arranges all open TBLVIEW windows in a cascade with the currently active window at the front.

  All TBLVIEW windows are listed in the Window menu - you can switch between windows by selecting the window's title from the menu.

Data Tab

The Data tab displays the contents of the table/view/query in a grid.

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 fields will be displayed in the grid with a ... button in each grid cell. Click the button to open the memo field viewer (see Using Databases in LEA Applications)

Definition Tab

The definition tab displays the structure of the current table or view.

Each field name is listed with its native data type.

For dBASE tables, the native type and width/precision of each field in the table is listed.

For Oracle tables and views the following information is displayed from the database's data dictionary:

• Column definitions - the column name and native type are displayed. If the column has a default value or expression then this is also displayed. Double-click on an entry in the list to view the comments for the column.
• Constraints - all constraints (not null, primary key, foreign key and check) on the current Oracle table are listed. Current status (ENABLED/DISABLED and IMMEDIATE/DEFERRED) is also displayed for each constraint.
  The text of CHECK constraints can be viewed in full (if the text is more than one line) by double-clicking in the 'Check Text' column.
  FOREIGN KEY constraints display the name of the reference table and column. You can click on the reference table name to open it in a new TBLVIEW window (see Window menu options above). For multiple-column foreign keys each column is displayed as a separate item.
• Indexes - list all indexes currently defined on the table. This will include the primary key index.
• Triggers - triggers defined on tables and views are only visible to the owner of the table/view to which they belong by default, unless the current user has the 'CREATE ANY TRIGGER' system privilege. Even if triggers are not visible, they may still be active. TBLVIEW will list all visible triggers on the current table/view. Double-click on a row to see the code in the trigger body.
• Privileges - this will indicate if the current Oracle user has permission to read and write data to/from the table/view. The current Oracle user's system and object privileges are evaluated to determine whether you have SELECT, INSERT, UPDATE and DELETE privileges on the current table/view.
• View Definition - for Oracle views this will display the SQL SELECT query which defines the view.

The information displayed here can also be saved to file using the Export Structure menu option.

For user-defined queries created with the Database-Edit Query menu option, structure information is not available.

Query Editor

This dialogue is displayed by the Database-Query Editor menu option, and is only valid when you are connected to an Oracle database.

You can enter any valid Oracle SQL SELECT query, and the results will be displayed in the data grid.
You cannot use this query editor to execute INSERT, UPDATE or DELETE statements, or perform any other DML or DDL..

The buttons in the toolbar and popup menu provide the following options:

• Load SQL  - select a file containing an SQL query and load the query into the edit window.
• Save SQL  - save the text of the current query to a file.
• Cut  - cut the current selection to the clipboard.
• Copy  - copy the current selection to the clipboard.
• Paste  - paste from the clipboard at the current cursor location.
• Undo  - undo the last edit operation.
• Previous Query 
• Next Query  - each time a new query is run it is added to a list which can be accessed using the previous and next buttons.
• Run Query  - display the results of the current query in the data grid.

See also:
[Using Databases in LEA Applications]
TBLEDIT - Database Table Editor