Saturday, May 16, 2009

Creating a singe table database

I said in my last blog that in the previous blog I had been a bit harsh on the Java community. That may be true, but I was not harsh on the Java Tutorial trail JDBC(TM) Database Access, which is possibly the worst piece of writing in the history of mankind. The trail dedicates an entire branch to material which could reasonably be assumed to be known by anyone venturing into the realms of JDBC, and then falls into a heap in the branch which purports to teach the technical mechanics.

The Getting Started lesson follows the pattern of the rest of the trail - full of redundant material and totally inadequate in areas of interest. It begins by describing three steps. The first step includes: "Install the latest version of the Java™ platform on your machine". Now surely a Java novice is not going to make JDBC his or her first lesson. So surely it is reasonable to assume that anyone interested in this lesson already has the Java™ platform installed and running. The first step continues with a recommendation to install NetBeans.

The second step is to install drivers and the third step is to install a database. Now if the reader has elected to use NetBeans, they need neither a database nor a driver as both are already installed. And if they prefer to use a different database, surely the main reason for that choice will be that they are already using it. So it's a bit silly to include installing a database in a list of things to do. And in the exceptionally unlikely event that a programming novice with no database installed on their computer decides to sit down and learn all about JDBC and use a database other than that which has been recommended, surely the database should be installed before the JDBC driver.

I had a database already on my machine, which I preferred to use, so the item in the checklist of most interest to me was the second. And sadly the whole paragraph was complete gibberish, and contained no useful information whatsoever. To add to the confusion, under Item 3, install a database, which itself turned out only to repeat Item 1 (install NetBeans), there is a lengthy (but essentially unhelpful) description of driver types.

The lesson Setting Up a Database begins: "Before you write a JDBC™ application, you need to set up a database called COFFEEBREAK." This is a false statement. It is distracting and annoying. The next statement is: "You learn how to create a database through your vendor documentation." This is abdication of role as a tutorial. To entitle a lesson "Setting Up a Database" and then refer the reader to "vendor documentation" is utterly silly. A simple reference to 3rd party documentation isn't a lesson, and it doesn't belong in a tutorial. If it provided links to a list of suitable 3rd parties it would belong on a "useful links" page, but this "lesson" doesn't even do that.

The "lesson" goes on to recommend (for the third time) using NetBeans and the Java DB bundled with it and it does provide a link to some NetBeans documentation, which is much more professionally written. But instead just posting the link, how hard would be for the "lesson" in the Java tutorial to copy (albeit with an acknowledgement of source) from this documentation, that which a beginner needs to know to create a database? For example the tutorial could include the screenshot shown below, explaining that it is taken from the services window of NetBeans.


The tutorial could then explain simply that to create a database you right click on a DBMS node, such as Java DB, and select Create Database. It shouldn't have taxed the author's brain too much, but it would have made life a little easier for a new user, and it would have converted a waste of space non-lesson into a short and simple lesson.

The Establishing a Connection lesson may possibly include some useful code snippets at the end (I haven't tried them yet), but the opening is as much gibberish as the previous lessons. For the new user, who has followed the many time repeated recommendation to use NetBeans, the lesson could save itself a lot of ink and refer once again to the screenshot above and say simply that to make a connection using the GUI, you right click on a database node and select Connect. How hard is that?

The Setting Up Tables lesson contains way too much information about the author's pet example, but besides that is harmless. Halfway down the page there is a paragraph entitled "Creating a Tables Using the NetBeans IDE" and it does provide a screenshot. I copied the exact details from the screen shot and it worked. However, this silly little example does not provide the learner with all that they need to know. For example if you uncheck one of the null checkboxes (i.e. if you specify "not null") for any field you get an error and the table is not created.

The NetBeans documentation suggests that under certain circumstances it is better to create a table with a manually written SQL command than using the GUI. For example if you want an index field to fill up automatically, you cannot set this in the GUI.

What does the illustrious tutorial say about SQL? Very little of use. It sends the innocent reader to a generic documentation page for Derby (a DBMS name which seems interchangeable with Java DB), but this is not very helpful. I used Google to find an SQL language reference page (in the same documentation, but buried) for Derby, and that gave me the command and syntax I needed. I ended up with the following statement to create a table for myself:

create table "JONNO".Items
(
Itemid INT GENERATED BY DEFAULT AS IDENTITY,
OpCode SMALLINT,
Itemdet CHAR,
Raw SMALLINT,
Rate DOUBLE,
primary key (Itemid)
);

Referring again to the screenshot above, I found that if I right clicked on my database node and selected Execute Command, an SQL window opened with a connection to that database and I could run the command from that window, either by right clicking from within the window and selecting Run Statement, or by clicking the leftmost window icon, which called itself Run SQL. After running the above statement, the ITEMS table (shown in the screen) was added to my JONNO database.

No comments: