picoSQL - iSQLViewer

Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

iSQL(IndependentSQL)-Viewer is an OpenSource JDBC 2.0-compatibile application for accessing and administering a generic database and it is not developed by Picosoft. It supports interactive transactions, schema viewing, running batches and it has some import/export filters.

• Development Status: 6 - Mature
• Environment: Cocoa (MacOS X), Win32 (MS Windows), X11 Applications
• Intended Audience: Developers, System Administrators
• License: Mozilla Public License 1.1 (MPL 1.1)
• Natural Language: English
• Operating System: Windows, OS Independent, Linux
• Programming Language: Java
• Topic: Front-Ends, Software Development, Systems Administration

If you have Java installed on your computer, you can execute:

You will see the iSQLViewer splash screen and then the main window:

The main window is divided in three subwindows:

• the upper subwindow allows you to insert SQL commands;
• the middle subwindows shows the query rusult.
• the lower subwindows shows all the messages.

You can connect to picoSQL driver using the Services menu and selecting picoSQL. You can see the driver configuration clicking on Service Manager from the Tools menu.

you can see the following window:

The meaning of the fieldsJDBC Driver and JDBC URL is explained in jdbc.html.

When you connect to picoSQL and no problem arises, you can see the following messages in the lower subwindow.

Now, if you click on JDBC Explorer ... from the menu Tools, you can see the following informations about the database:

• TABLE: here you can find all the tables of the database.
  the tables __TABLES, __COLUMNS and __VIEWS are are system tables and they contain all the informations about the database.
• for each table you can see all the relevant informations; in the following example you can see the 3 columns of table GENERE, that are DESCRIZIONE, GENERE and ID_GENERE.
  • Column(s): shows the columns of the table and its relevant features.
  • Indicies: shows the indecies defined on the table.
• GeneralInfo: shows the driver features.
• Table Type(s): shows the tables types supported.
• Data Type(s): shows the data types supported.

Schema(s), Catalog(s), UserDataType(s) e Procedure(s) are not supported by picoSQL.

You can execute SQL commands typing them in the top subwindow, for example:
select genere.genere,autore.nome,autore.cognome, libro.titolo,editore.editore,libro.prezzo,libro.data_pubblicazione from genere,libro,autore, editore where genere.id_genere = libro.id_genere and autore.id_autore = libro.id_autore and editore.id_editore = libro.id_editore and autore.cognome = "Bertacca"

To execute the query you must click on the following button:

You get the query result in the middle subwindow.

If you click the mouse right button in the middle subwindow you get the following menu:

If you click the mouse right button in the lower subwindow you get the following menu:

You can execute SQL script too: in the distribution you can find some examples of SQL scripts under .iSQL-Viewer. Some scripts are used to load the test database. You can execute, for examples, the scripts createEXAMPLE.sql and loadEXAMPLE.sql.
You can access the scripts configuration window clicking the button:

The scripts configuration window is the following:

In this picture you can see the script and the service already setted, of course you must input this data every time. Clicking the Next button, you can see a dialog windows that allow you to configure the batch file parser.

Clicking again the Next button, you see the execution window.

You get the query result as messages in the lower subwindow of the the main window.

In order to see the new created table, you must disconnect from database clicking Disconnect from the Services menu and then reconnect again using the same menu; this allows iSQLViewer to re-read the database configuration.

In the same way, you can execut the loadEXAMPLE.sql. You get the following messages on the lower subwindow in the main window.

Now, you can type select * from EXAMPLE in the top subwindow and get:

At the end, you can execute the script dropEXAMPLE.sql to drop the new table. You must disconnect and reconnect to see the updates on the database schema.