Browse Schema

SQL4X Manager J offers two different ways to browse your database schema:

		Database Navigator The Database Navigator is built with administrators and developers in mind. Besides the basic schema information it displays many information on table content, structure (layout) and primary and foreign keys. Depending on the speed of your network connection and your database server's performance retrieving these values can take quite a while. On fast servers with fast connections this is no problem and you can see all the information you need in one place immediately.
		Database Quick Access For slow connections or if you don't need all the information about keys and table structure Database Quick Access is the best solution. With Database Quick Access only the basic schema information is retrieved and displayed. This is quite fast and straightforward. To display table content you can double-click a table name to start the SQL Query Assistant which gives you the choice of either displaying the full table or create a SQL query with an easy to use graphical assistant.

Basic information on browsing the database schema

This section gives you some basic information on term used with database browsing and database management and explains why different database servers have different options.

What is a schema?

Schema is a term for the structures and information a database server must maintain to access and organize tables. A schema can identify users and associate access privileges.

All current database servers use SQL to access their data and define structure. SQL gives a framework on how the actual tables have to be structured but not how database management and organization has to take place. The schema is part of the database management and every vendor has it's own solutions to the management problem depending on the size of installations and available features.

SQL4X Manager J is based on JDBC, which gives universal access to any SQL compliant database server. JDBC defines Catalog as database name and Schema as user or project name.

What is a Catalog?

Similar to a Schema a Catalog is used to organize and manage tables. Usually all tables in a project or for a specific purpose are grouped together in a catalog (also known as database in the terminology of several database vendors).

A Catalog allows better management (for instance all tables out of a Catalog can and should be backed up together).

What is displayed with Database Navigator and Database Quick Access?

Both display Schema and Catalog information. Database Quick Access queries your database server if Schema and/or Catalog information are supported and displays Schema and Catalog names only if they are supported. Database Navigator simply leaves the Catalog or Schema view empty if it is not supported by the database server.

Both display all Catalogs and Schemas which have been reported by your database server on request. This means you will get different Catalog and Schema lists for different users with different access privileges.

Edit Data

To edit your data use the Database Navigator. The Database Navigator can update, add and delete rows with basic and enhanced data types (like BLOB, etc.).

What data can be edited?

There is no limitation on the type of data, but you should be aware that only tables with a primary key can be edited. The primary key is needed to uniquely identify records. If a record cannot be uniquely identified an update or delete statement could affect rows that weren't intended for this command. To prevent failures a "don't write" icon is displayed on the left side in the status line (below the toolbar) to indicate tables without primary key.

A primary key has to be part of the table structure. Keep this in mind when creating a new table. In most cases a column id with an auto increment type is created for this purpose.

How to edit simple text and numeric data

Double click the value you want to change in the table view, enter the new value and press RETURN. The new value is immediately saved to your database server.

How to edit multi-line text

Use the BLOB Editor to change multi-line text. First select the row you want to modify, display the BLOB drawer (with the Show BLOB icon in the toolbar), select the field with the field selection on top of the BLOB drawer. If the data type has not been recognized automatically you can choose the display type with the tab view at the bottom of the BLOB drawer (Image, RTF, Text). Currently GIF, JPEG, TIFF, RTF and plain text are supported.

After updating your text field click "Save Text" or "Save RTF" to save your changes.

How to save an Image from the database to the harddisk

Display the image the same way as a mulit-line text (see above), click on the "Action" menu and choose save. Your image is saved as TIFF.

Can I use special characters in my database?

Yes and no. In most cases you can use any special characters you like. SQL4X Manager J parses your text and data before running the SQL command and encodes your special characters. The problem is that every database vendor has a slightly different way of encoding text. If you are using a database server besides Oracle, MS SQL Server, Sybase, MySQL, PostgreSQL or OpenBase you should check this with some unimportant data first.

How to add new records

To add a new record click on the plus sign (+) and enter your values in the newly created line. As soon as you are finished with all necessary values click the Save new Record icon or the Cancel Edit icon to delete your values again (see image above).

Filter Records with Data Viewer

Click on the "Filter records" icon in the Database Navigator window to bring up the Filter dialog. You can either enter a SQL where-clause manually or select the filter criteria with the graphical interface:

How to choose which columns to display

By default all columns of a table are displayed with the Data Viewer. To hide a column uncheck the check box in the "Show" column of the Filter dialog. To activate the filter click "Apply Filter".

How to choose which records to display

If you want to display only records that meet a certain criteria select the comparison operator in the "Display" column and enter the criteria in the "Value" column. To choose records by wildcard select the "like" operator. You can use the wildcard charcters % (for any number of characters) and ? (for one character). Do not enclose your value in apostrophes (even if it is a string value). All comparison values are automatically converted to strings for security reasons.

If you need to filter for any other specific data type you can enter the comparison in the "SQL where-clause" field. The comparison includes a field name (time_id in the following example), a comparison operator (>) and a comparison value (to_date('16-Jan-1998')). The following statement can be used to filter specific records from an Oracle Database server by date:

time_id > to_date('16-Jan-1998')

This will display only records which time_id field contains a date after 16th January 1998. to_date is a special function available with Oracle to convert text values to date values.