Inquirer 1.0.1 (15.4 Kb)

See also Inquirer Description and Installation and Inquirer Development Tutorial Now that Inquirer is available, we can test the SQL Statements that JDBC supports. The JavaSoft's documentation tell us that JDBC supports databases ANSI SQL-2 compliant. In this short Guide we'll list some simple SQL statements and send them to a database trought JDBC using Inquirer. For any question about the installation procedure of Inquirer see Inquirer Description and Installation

Creating the Database

First of all we have to create the database. If you want to use an existing database, plese make a backup copy of your data.

This is the procedure to create a database in Windows 95 using the ODBC service:

1. click on Start
2. click on Settings
3. click on Control Panel
4. double click on ODBC
5. choose Add...
6. choose the Microsoft Access Driver and click OK
7. fill the fields and choose a directory

The database is now created. If you use an existing database substitute the following steps

6. choose a Driver compatible with your database and click OK
7. fill the fields, click select and find your database in the directory tree

Now you can start Inquirer and connect it to the database. If you use the ODBC service, the first two fields are:
Driver sun.jdbc.odbc.JdbcOdbcDriver and Database jdbc:odbc:name_of_database

When you leave the Database textfield using the TAB key, Inquirer tries to connect to the database (see the Status Bar for any exception). If all works well, the Status Bar shows "Connected to jdbc:odbc:..." and you are in the Query textfield.

Creating a Table

Every database has some table in it. Tables are collection of columns (representing properties) and every row is an item (represented by the values in the columns). For example, let's create a table with a phone book of our friends. We must specify the name of the table and the columns in it using the statement CREATE TABLE, for example

CREATE TABLE friends ([First Name] CHAR(30), [Last Name] CHAR(30), [Age] INTEGER, [Phone] INTEGER)

The query is submitted hitting the ENTER key. In the Status Bar you see that an exception was caught: this exception says that no Result Set was produced and it's generated because we used executeQuery instead of executeUpdate in the code. However the query is executed and I don't want to fix it in this demo because I want to show the differences between the Query and Update statements.

To show the results of the query you must submit this statement

SELECT * FROM friends

and four Lists will be shown in the ScrollPane.

You saw in the CREATE TABLE sintax that you must specify the type of the data in every column. For example, [First Name] is a string of 30 characters, while [Age] is an integer. Here is a list of ANSI SQL-2 types with the corresponding Java Type Class Wrappers

Deleting a Table

DROP TABLE friends

It's early yet to use it now.

Selecting Data

To select data in a table you've to use SELECT ... FROM .... If you want to select all the data of your friends write

SELECT * FROM friends

while if you want to show selected columns

SELECT [First Name],[Phone] FROM friends

Finally, if you want to display some of your friends (your teenage friends)

SELECT * FROM friends WHERE age<20 AND age>12

and so on. The WHERE word permits to select a particular collection of rows. But we can't visualize anything because we haven't inserted any data yet. In fact...

Adding Data

... we have four empty columns. Let's start adding data. The statement is INSERT INTO ... VALUES ... as follows:

INSERT INTO friends ([First Name],[Last Name],[Age],[Phone]) VALUES ('Mary','Taylor',25,457880)

To show the results of the query you must submit again the SELECT * FROM friends statement.

Updating Data

If you made some mistake inserting the data, you simply update it with UPDATE ... SET ... WHERE ... . Again the WHERE word permits to select a particular collection of rows. Here is an example (the phone number was wrong):

UPDATE friends SET phone=458770 WHERE [Last Name]='Taylor'

Deleting Data

DELETE FROM friends WHERE [First Name]='Mary'

Basic SQL Statements Summary

More Questions?

For any question or suggestion, please use the comments form or write directly to TETRACTYS Freeware.

		In the next Issue Java Tutorials will be AWT: A Dockable Toolbar