SQLIDE is a program that lets you work with relational databases. You can create tables, run SQL commands directly and it has some basic features for editing and analyzing data.
Thank to the following people (in no particular order) for the following:
This program is licensed under the GNU GPL. You can read the license agreement by selecting "Help->License" or by clicking on the "License" button on the SQLIDE About Box.
Basically, it's a free license with absolutely no warranty.
This tutorial will walk you through doing some database access using SQLIDE.
Start SQLIDE. If you have never set up a server profile, you will be prompted to create a new one.
Create a new server profile. You will need to download a driver for the database server you are using. If you only want to test, you can download a good, if limited, embedded SQL database at http://www.hsqldb.org.
This tutorial will show you how you can create the server profile for HSQLDB. The tutorial uses this database for most sections except where more advanced features are needed, in which case we will use PostgreSQL. SQLIDE works with most JDBC drivers.
After you download the drivers from the web, you will have one (or
more) JAR files that you need to add to this connection's CLASSPATH. A
Class path is a java concept that allows you to load libraries
dynamically (in this case, the database access libraries).
Now that you have the drivers, press "Next". You will then see a
dialog box prompting you the name of your connection and the server
type. Select the server type from the list first, and this will
typically set sensible defaults.
Note: If your server type has no equivalent on the list, you can consult the documentation of your JDBC driver to still get a succesful connection.
Always consult the documentation for your JDBC driver so you can set better values than the recommended, in particular for the driver class name and the URL. The URL suggested is typically meant as a help so you can set your own values. For example, in our case, the URL jdbc:hsqldb:/home/dmartinez/MyDBFile refers to the fact that the embedded Hypersonic database file will be created in your home directory.
Once you make the changes you need for this page, you will be
asked to set other connection parameters on the next page. These are
only necessary if your JDBC connection requires it, so we will just
click on "I'm Done!".
We are done creating our connection. Next you will see the main Window of SQL-IDE, which is always the first window you see now that you have at least one connection set up (to create more connections or change the configuration of the existing connection, select "Tools->Configure").
So now we are in the main window.
Double-click on "My Environment". You will see a list of your connections. Double-clicking on a connection connects you (you will se a login dialog) and opens the browser to see the metadata (the list of catalogs, tables and stored procedures).
Like this you can browse the information on the entire database, and you can see the details of the selected item in the browser at the bottom Information panel.
So now that we've been browsing for a little bit, let's try executing some statements against this database. On the menu, select "Plug-In->Interactive SQL", which will bring up an interactive window to execute queries against this database connection directly. On the editor side, you will se the interactive SQL window.
With the interactive SQL Window you can execute queries by simply tyuping in the statement and pressing the "Go" button or Ctrl-Enter. You will see the result set on a table below your query, and a log section at the bottom of the editor.
Now let's try something interesting. Using the editor, select a
numeric ID field in a table you have on your connection and right-click
on it. You will now see a list of useful queries that are related to
this column. Select a query from the list and it will be automatically
typed into the interactive SQL:
Now press Control-Enter. You will see the results of the data in the
bottom.
Now, if your database supports it (Postgres and Oracle do,
Hypersonic doesn't), you can request for the query to be updatable.
Enable updatable and execute the query again. If your database supports
it, you will get a message in the log saying "Updatable Resultset
obtained". You will also get buttons for insert, delete, commit and
rollback.
Note: In order to get an updatable resultset in oracle, you need to get the Oracle ROWID, which does not come in when importing the tables with an asterisk unless you qualify the tabnle with an alias. The Oracle RowID can be obtained easily by formatting your query like "SELECT U.* FROM MYTABLE U"
You can navigate up and down the history list of queries you have entered by using Control-Up and Control-Down.
Something other you can do is this: If you typically type in a query
that is very complex, you can save it for posterity and give it a name,
and it will appear on the context menu for this connection for whenever
you right-click on any of the elements within that connection. For
example, below you can see that we have created an interesting Join
that shows songs and the bands that play them. We right-click on the
selected item on the browser and select "Add this query to
[MyConnection]". You will be prompted for a name. I entered "My Songs
Join" so I remember it.
So for now on, whenever I right-click on the browser inside this
connection I have this query as an option:
Those are the basics of using SQLIDE. There are other editors in addition to the Interactive SQL, such as a table editor.
SQL stands for "Structured Query Language". It is a language that most relational databases support in their own way. This section explains common useful commands you can use.
SQL Commands sometimes return one (or more) set of results in tabular format, and sometimes don't return anything. The commands themselves are case-insensitive, but the case sensitivity of the object names (in the database) depends on the server you use.
When specifying a syntax, the following conventions are used:
Basic Syntax:
SELECT {FIELDSPEC} FROM {TABLENAME} [, TABLENAME..]} [WHERE {EXPRESSION}] [ORDER BY {EXPRESSION}
This is the basic command used to return tabular-formatted information from a tablespace. Some databases treat the details a little differently, but in general you select a series of fields (asterisk means all fields) or expressions from a table (or series of tables, if you are specifying a "join"). You can specify a sort order with ORDER BY, and limit the amount of records that are returned with the WHERE clause.
Examples:
SELECT * FROM ADDRESSBOOK WHERE LASTNAME = 'Smith'
SELECT * FROM ADDRESSBOOK ORDER BY LASTNAME, FIRSTNAME
SELECT * FROM ADDRESSBOOK WHERE LASTNAME LIKE 'M%' ORDER BY CITY, LASTNAME
Basic Syntax:
UPDATE {TABLENAME} SET {FIELD} = {EXPRESSION} [WHERE {EXPRESSION}]
This is a command that modifies existing data. Although the WHERE is optional, it is recommended you always use it, since an UPDATE without a where clause will modify all the rows in the table. The command is pretty straightforward and allows you to make individual and bulk updates.
Examples:
UPDATE ADDRESSBOOK SET STATE = 'CA' WHERE CITY = 'Los Angeles'
UPDATE ADDRESSBOOK SET FIRSTNAME = 'Elvis', LASTNAME = 'Presley' WHERE ALIAS = 'The King'
Basic Syntax:
DELETE FROM {TABLENAME} [WHERE {EXPRESSION}]
This command deletes information from the specified table. As in update, the WHERE clause is optional but you should always use it (ommiting a WHERE clause on a DELETE removes all the data in the table).
Examples:
DELETE FROM ADDRESSBOOK WHERE COMMENTS = 'Annoying Dude'
Basic Syntax:
INSERT INTO {TABLENAME} [ ( {FIELDS} ) ] VALUES ( {VALUES} )
INSERT INTO {TABLENAME} [ ( {FIELDS} ) ] SELECT ..
This command inserts new records into the specified table. If you don't specify a list of fields, you need to specify all the values in the order the database has been designed. If you do specify a set of fields, there has to be the same amount of fields than values.
If you combine the INSERT with a SELECT statement, you can insert several rows at a time.
Examples:
INSERT INTO PERSON_TYPE VALUES ( 1, 'Cool Person' )
INSERT INTO ADDRESSBOOK ( PERSONID, FIRSTNAME, LASTNAME ) VALUES ( 1, 'Elvis', 'Presley' )
INSERT INTO ADDRESSBOOK ( PERSONID, FIRSTNAME, LASTNAME ) VALUES ( SELECT PERSONID, FIRSTNAME, LASTNAME FROM ADDRESSBOOK_BACKUP WHERE LASTNAME LIKE 'M%' )
This is just a few of the basic data manipulation commands you can use. See your database user guide for a complete explanation of the language and instructions specific to your database.
TODO
TODO