ODBC Database Utility
(odbcutil/odbcwin)


The Jargon Software odbcutil and odbcwin programs provide an easy way to administer a local ODBC database (such as an Oracle Lite database). The odbcutil client app has small screens for use on PDA devices such as the Pocket PC, while odbcwin has larger screens for use on a PC. The functionality is identical, only the screen component sizes are different. In this document, all references to odbcutil.ini and odbcutil.xml apply to odbcwin.ini and odbcwin.xml as well.

Installation.

Install the odbc programs like any other Version 3 application. The xml and ini files are in xml\Progress\util in your Jargon Writer directory. The Progress program (odbcutil.p) is included in zip and tar files in the Progress\nt and Progress\unix directories.

Terminology.

ODBC is an acronym for "Open DataBase Connectivity", a standard database access method developed by the SQL Access group in 1992 that can be used with SQL-compliant databases. SQL is an acronym for "Structured Query Language", a standard language for accessing relational databases.

In this document, the following terms are synonymous. The first set of terms are standard SQL terminology, while the second set may be more familiar to developers with a Progress 4GL or similar background.

Table = File
Row = Record
Column = Field
Metadata = Schema

Home Screen.

The home screen is where you connect to a database and select an action. On this screen you specify the database name, userid and password to be used for the ODBC connection.

There is also a combobox which can contain default database names. These defaults are loaded from a comma-separated list value for the DBNames parameter in the odbcutil.ini (or odbcwin.ini) file. 

Example: DBNames=JOE,POLITE
If you select a database name in the combobox, it will populate the textfields below it with that name and default userid/password values.

Click "Connect to DB" to connect to the specified local database, which must already exist and which must be defined in the device's ODBC connection settings. If the connection is successful, a status message will show this below the connection button.

If the database has an application schema (also known as metadata or table definitions), the combobox of table names will be populated with all the table names in the database. Only application tables will be listed, not internal meta-tables used by the database. (However, meta-tables can be queried from the SQL Queries screen if you know their names. For example, you can see Oracle's internal table of application table metadata by querying "SELECT * FROM ALL_TABLES". For the internal table of indexes, enter "SELECT * FROM ALL_INDEXES".)

Some of the buttons at the bottom of the home screen operate on the selected table, while others can be used without regard to which table is currently selected. The buttons are:

Table Fields/Data

    Views the schema and data contents of the selected table, with options to filter which records are displayed, view field details, and update field values.

Table Statistics

    Displays the selected table name and its record (row) count.

Table Export

    Export all rows (records) from the selected table to a text file as SQL "INSERT" statements. This can be used to do dump/reloads or to copy data from one database to another.

SQL Queries

    Enter one SQL "SELECT" statement and view or export the results, with or without column labels, in one of three output formats.

SQL Updates

    Execute one or more SQL statements that update the database. These can include DDL (Data Definition Language) statements that add, change or delete database schema information (metadata), and/or DML (Data Manipulation Language) statements that add, change or delete data in the database tables. The statements can be keyed in or loaded from a text file on the local device, a web server, or a middleware server (Progress AppServer and WebSpeed currently supported).

Exit

    Exits the utility and returns to previous app or startup menu.

Table Fields/Data

Opens a "[TABLENAME] Table Info" screen that shows the metadata for the selected DB table, including the field name, label, data type, size, decimal places (for decimal fields) and default value. The "Field Value" column shows the data contents of one row (record) in the table. Select the row to be displayed by using the First/Prev/Next/Last navigation buttons.

The sequence in which rows are displayed is decided by the database engine and may or may not be in primary key sequence or in the order in which rows were inserted into the table.

The Filter button brings up a "Filter Records" screen where you can set a filter for which records are shown in the "Table Info" screen.

The first set of fields lets you select any column name in the table, select a comparison operator (such as "=" or ">") and enter a value of the correct data type for the selected column. Do NOT put quotes around the value.

The second set of fields allows filtering by the contents of any character field, using "Contains", "Begins with" or "Ends with" operators and a value to match. Do NOT put quotes around the value. If a selection was made in the first set of fields, choose "AND" or "OR" in the combobox above this second filter, depending on whether you want both conditions ("AND") or either condition ("OR") to apply as a filter.

A third filter phrase containing any valid SQL "WHERE" clause can be entered in the bottom textarea. It must follow strict SQL syntax, including use of single quotes around values. If a previous selection has been made, you must begin with "AND" or "OR" as well. 

Example of 3rd filter: AND (STATE = 'MN' OR STATE = 'WI')
To apply the filters and return to the "Table Info" screen, click "OK".

To clear all filters, click "Clear Filters", then click "OK" to return to the "Table Info" screen.

The Detail button on the "Table Info" screen displays all known metadata and the full data value for one selected field in the currently displayed record. Click on a row in the table to select the field, then click "Detail" to see the detail information about that field. Click "OK" to close the detail screen.

The Update button on the "Table Info" screen lets you update the database, one field at a time. Click on a row in the table to select the field you want to update in the currently displayed record, then click "Update". This displays the current value of a selected field and allows you to change this value by keying a new value into the "New Value" textarea. Click "Submit" to accept the new value, or "Refresh" to redisplay the old values if a keying error was made. Click "Back" to return to the "Table Info" screen. This feature is especially useful for setting system control values or low-volume maintenance or testing actions such as changing one field value in one record of a table.

Note that Oracle "null" values are displayed and entered as empty strings in character fields, so an update value containing no characters or spaces will be stored in the database as the null (empty) value. This handling of nulls as empty strings is done for cosmetics and compatibility with other systems such as Progress databases.

The Back button on the "Table Info" screen returns to the Home screen.

Table Statistics

This button displays the table name and number of rows (records) in the table. It uses a "SELECT DISTINCT COUNT(*) FROM [TABLENAME]" statement to do this.

Table Export

This opens an "Export Table Data" screen where you can select the output filename for a text file that will be created with SQL "INSERT" statements for all rows (records) in the selected table, with all column (field) names and values included. This can be very useful for doing database dump/reloads, or to copy data to another database.

Check the "Include DELETE FROM TABLE stmt?" checkbox if you want the file to contain a "DELETE FROM [TABLENAME]" statement as the first line. This will remove all old data before loading the new data when this export file is later executed against this or another database to load the data. If the data being dumped is only an incremental set of data to be added to some other data in the table in another database, do not check this option.

Verify or change the "Output to Filename" directory/file name to be sure it is valid. The default value for this pathname is a file named "[TABLENAME].sql" in the directory given by the ExportsDir parameter in the odbcutil.ini file. If this parameter is not defined, the ReportsDir parameter will be used. If neither is defined, the default will be "/My Documents/[TABLENAME].sql".

Click "Export" to export all rows of the selected table to the specified output file on your local device. The Export Status box will show the export status and results, including any errors.

When done, click "Back" to return to the Home screen.

SQL Queries

This opens a "SQL Query" screen where you can enter (or paste) a single SQL "SELECT" statement. The statement must follow correct SQL syntax, including using single quotes around character values (if any). An ending semicolon is optional. Expressions may be given a "dummy" field name by using the "AS [name]" clause.

Example that counts customers in each zip code in states beginning with "M", for the CUSTOMER table in Jargon Software's "JOE" database: 

    SELECT STATE, ZIP, COUNT(*) AS CT
    FROM CUSTOMER
    WHERE STATE LIKE 'M%'
    GROUP BY STATE, ZIP;

Select the desired output format:

    (1) Fixed width columns (one record per line, fixed field spacing)
    (2) Delimited columns (one record per line, variable delimited field spacing)
    (3) One column per line (one field per line, blank lines between records)

If option 2 is chosen, enter a delimiter character such as a comma

For all options, check the "Column Labels" checkbox if you want the column labels to be displayed.

  • For option 1 the labels will be on line 1, underlined by sets of dashes on line 2.
  • For option 2, they will be on line 1, delimited by the same character used as the data delimiter.
  • For option 3, they will be at the start of each line, with an equal sign between the label and the data value, for example "ZIP=55401".

Select where to send the output results:

    View Only - show on screen
    Create File - create new text file
    Replace File - create new text file (replacing old one)
    Serial Port - output to serial port (on PDA)
    Infrared Port - output to infrared port (on PDA)

If the Create/Replace File options are chosen, verify or change the "Output to Filename" pathname of the output file. The default value for this pathname is a file named "QUERY.txt" in the directory given by the ReportsDir parameter in the odbcutil.ini file. If this parameter is not defined, the default will be "/My Documents/QUERY.txt".

Click "Run" to execute the SQL query statement against the local database and send the results to the specified output destination.

The "Clear" button clears the SQL Statements textarea without changing any other options. The "Back" button returns to the Home screen.

SQL Updates

This opens a "SQL Updates" screen where you can enter, paste or download a set of SQL update statements. The statements must follow correct SQL syntax, including using single quotes around character values (if any). An ending semicolon is required at the end of each statement if there are multiple statements, but is optional on the last (or only) statement.

These statements will normally be SQL DDL statements that create, change or delete metadata (schema) content (CREATE, ALTER, DROP), or SQL DML statements that add, change or delete data values (INSERT, UPDATE, DELETE).

A text file of prewritten statements can be loaded in three ways:

  • From a file on your local device (PC or PDA) or on a network shared drive that is accessible from your device. Jargon Reader will read the file and load its contents into the textarea.

  • From a file on a middleware server, such as a Progress AppServer or WebSpeed server. Jargon Reader will send an HTTP POST request to a middleware procedure (via a web server), which will read the file on the remote server from the specified server pathname and send the contents to the textarea by means of a host "jsi" method. Note: only a Progress middleware procedure (util/odbcutil.p) is currently available, however this can be easily done in other environments as well. Contact Jargon for details.

  • From a text file URL on a web server, that can be served by the web server (meaning it is in a location under the web server's document root directory). Jargon Reader will send an HTTP GET request to the web server to retrieve the text file from the specified URL.
When a new access method is chosen from the combobox, an appropriate default pathname or URL will be put into the SQL File Pathname textfield. The values for each of these three default values is given by the SQLFileLocal, SQLFileMWare and SQLFileURL parameters in the odbcutil.ini file. If these values are set up for your normal environment, you will only have to change the filename, not the entire pathname or URL value each time.

Click "Get SQL File" to load a file of SQL statements via the specified access method from the specified pathname or URL. Or, enter or paste a set of SQL statements into the textarea if no file is available for loading.

Check the "Show SQL stmt detail in log?" if you want to see the full text of each SQL statement displayed in the log. This is NOT recommended for very large sets of statements as it may overflow the log textarea. If not checked, a running record count will be displayed every 100 records, and only statements with errors will be displayed in full (with a line number).

Click "Execute SQL" to execute all the SQL statements in the textarea against the local database. The execution status of each statement and a final summary will be displayed in a scrolling textpane. Click "Clear" to clear this log, and "Back" to return to the SQL Updates screen.

Click "Clear" to clear the SQL statements textarea if you wish to load or enter another set of statements. Click "Back" to return to the Home screen.

Return to Help Index