Sunday, May 31, 2009

Swimming against the current

One of the things I notice when reading the Java/NetBeans documentation on database "applications" is that they all envisage an "application" which just scrolls through a recordset displaying records. This so dull. Why would anyone want to create another "application" like this, when there are so many out there which do exactly that. Why write something from scratch when you can buy a product off the shelf like MS Access which does exactly that, and does it jolly well? Why does everyone just copy everyone else?

I want my app to connect to a database, read stuff from it, and write stuff to it, but without my users even knowing it is there. My users are students doing a math activity. My database is of no interest to them, and they as individuals are of no interest to my database. What is of interest is the way they interact with items stored in the database, and I want to use this interaction to refine parameters associated with the items.

If I select New Desktop Application in NetBeans, it produces a form which ploughs through a table in a database, one record at a time. And it produces a myriad of classes, which presumably mimic all the stuff which happens automatically in an off the shelf product like MS Access. I don't want that. I want a simple connection to a database which enables me to read data, process it with my own code, and then to record stuff derived from the processing.

I hardly need record that when I cut and pasted code from the Establishing a Connection lesson in the JDBC trail of the Java Tutorial did not work. I should like to contrast this trail with the Collections or the Basic I/O trails, which were so well written. Everything in those trails made sense, and the code all worked, not least because it was complete. For example, check out the code on this page. It starts with a Copyright statement, which is fair enough for something well written, then it gives three import lines, a class declaration, and code. And if I copy it into a file on my computer, it compiles and runs.

Compare this with the code on the Establishing a Connection page, and, well, what can I say .. there is no comparison. It is supposed to be a lesson, so it is not unreasonable to expect it to include what needs to be imported to make the code work. This lesson is so badly written it ought to be deleted. I have posted a question about it in the Java Tutorial General Discussion forum. And to add to my frustration, when I searched Google using the search string "jdbc:derby data connection", my question was top of the list. What does this say about the volume of material written on the subject? Alarmingly little it seems.

Fortunately, sixth on the list of the same search was a forum post, which helped me at least to write a class, created with a text editor and run from the command line, which created a database, added a table, added lines to the table, queried the table and displayed the table results. I won't post the code here (it never looks very good in a blog), but I have posted it in this forum thread, which also tells the whole story.

I feel quite tired now - not sure what to do next. I've been working on this for a whole month, but I still don't feel much closer to weaving data connection code into my web applet.

Saturday, May 30, 2009

Windows 7 Release Candidate

I don't like Windows 7. Perhaps I am a Luddite. Perhaps I just resist change, because I prefer the familiar look and feel of XP.

For whatever reason, having tried the beta, I was inclined to ignore the release candidate. It gobbles up my download limit, and it is time wasting. Two things changed my mind. The first was comment in the press, by which I really mean APC magazine. The second was the inevitability of the role out, and the need for me to become familiar with it in order to service my customers.

On the first, I have a fairly low opinion of the press, and I have developed an especially low opinion of APC mag. I don't know why I read the editorials, because I find the editor intensely irritating. He strikes me as a man of little brain, and he seems to write not what he believes (if indeed he has beliefs) but what he has heard, or what he thinks will make him seem trendy or with it.

He raved over Windows Vista, when he thought it was exciting, but as time progressed and it became clear that people hated it, he joined the bandwagon and lambasted it. So why am I influenced by such a person raving over Windows 7? I guess there comes a point when a person becomes sufficiently sponge like, that they can be used as a litmus or indicator of more general opinion, and listening to them saves a lot of reading.

On the second, there is little to say, except that it will be used, because the average person is even thicker than a technical magazine editor, and they just buy whatever is in the shops.

So I waved goodbye to whatever was left of my download limit, and tied up my router for 36 hours while RC1 came in. I burnt the DVD, as one would expect to do with an iso file, booted to Win7 beta, and let the DVD run.

For like ages there was a "files loading" message, and then there was a silly message, with the meaningless error code 0x0, saying that the download had failed but urging me to try again. How silly. If an install fails once, why would it run successfully on a second attempt? But like an idiot, I followed the recommendation, tried again, and exactly the same thing happened again.

I was a bit surprised. After describing Windows 7 beta as like a poor quality Linux release, and then reading great things about it in APC mag, I thought perhaps that either I had been mistaken, or that it had got better. But after a futile hour, with my best computer effectively lying idle, my original opinion of Windows 7 was restored.

Thank goodness we live in a democracy. Imagine an Orwellian world where people like Microsoft executives really controlled everything in our lives, and where there really was no choice but to accept it. How like "1984", and how dull it would be. Microsoft continues to survive and thrive because most people are unworthy of democracy. Most people are born to be herded like sheep, and are happiest when they are being herded.

So I framed a question for the forum, honed a titled, and then searched on segments of my title. Disappointingly, there was nothing new. There was nothing specific on error 0x0, but on upgrade fails, there was the usual nonsense about upgrading graphics drivers.

There is no point in trying something out if one doesn't give it a reasonable go, and it is wrong to criticise something if one hasn't looked at it reasonably thoroughly. I therefore followed the links in this thread to update my nVidia driver. I used the online update tool, all from within Win7, and to be fair it worked very well. It was, however, a complete waste of time, because my third attempt at the update install failed at exactly the same point as before. By now an entire evening had been wasted.

I decided the time had come to post a question in the Windows 7 forum. This forum has a clever feature which runs a search after you enter the title to your intended post. One of the five threads displayed looked interesting, so I postponed my post and had a look at it. Of most interest were the following bullet points:

  1. While the error message is displayed, press SHIFT-F10 to open a command prompt
  2. Locate your \windows\panther\unattendgc folder (please note the unattendgc folder in this case)
  3. Copy out the setupact.log file and paste relevant information into the thread (this setupact.log should not be really long)

To be honest I couldn't see why this had to be done "while the error message is displayed". A log is a log, and of it's very nature static, unless it is being updated. My computer was back in XP by now (we don't use Win7 as a working system), but I navigated to my Win7 partition and found the setupact.log file. It's last amended date was February, so while the thread had made interesting reading, it was of no practical use to me right now. So I posted my question.

It's always disappointing when a forum question receives no replies, but while waiting I amused myself browsing. Sometimes browsing is better than search a forum, because different people describe things in different ways, and it is sometimes hard to guess how other people might have described one's own problem.

While browsing I found this thread. It suggested using 7-zip "to extract all the contents of the iso to a directory of your choosing and run the setup from there". I thought this was a jolly clever idea and wondered why I hadn't thought of it myself. I use a number of file compression and extraction tools, and at first I thought any would do. My computer with the Win7 iso on it happened to have WinRar installed so I tried that. All I got was a text file.

So I dug into my archive for a version of 7-zip downloaded some time ago (maybe 2005) installed it and tried again. I like 7-zip. I have used it many times and it had never let me down before, but this time it froze. That was version 4.32.

I store a lot of utilities, and my inclination is to use what I have, rather than downloading more. One of them is IsoBuster. My version was 2.1. The latest version is 2.5, but it doesn't see more, and may be more restrictive. Anyway, without being a real expert, I thought the layout (shown below) was unusual and interesting. Note the two "folders" with identical names, one of type iso, and one of type udf (whatever that means). In the iso folder there is just a text file. In the udf folder there is all the stuff you would expect on an install CD/DVD.


IsoBuster expects you to pay to extract files, which led me back to the 7-zip website, to download the latest version (4.65). After installing this I was able to extract all the install files on to my hard drive.

I booted back into Win7 to start the install again. The process ran much faster than from the DVD. There was no error, but instead an irritating message saying that the beta was not upgradeable. Why when Microsoft sends emails out to beta testers, advising of the release of RC1, don't they put in the email a note to the effect that the RC will have to be clean installed? It would save a lot of time an exasperation.

I happened to have an empty sector on my hard drive which I had been saving for Ubuntu (there just aren't enough hours in the day). So I shoved the RC in here, and the install went swiftly and without any problems.

All in all the exercise used up 24 hours, with no other productive work done in that time. Microsoft beta software is not free. Pricing one's time at even the most modest rate, the retail value of the product has been way exceeded, just installing one edition. On top of that is the download time, and the value of the bandwidth used, and the value of space taken up in a computer.

Having done all that my wife and I spent an evening using the product. My wife browsed using Internet Explorer 8, without issue, and we watched a couple of recorded TV shows using Media Player on the default settings. The sound wasn't quite right, but the picture was fine, and I liked the curtain effect which came up after a video had finished playing.

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.

Thursday, May 14, 2009

JDBC drivers for MS Access

On re-reading my last blog I think I was a little harsh on the Java community, because when I started posting questions about connecting a Java app to an Access database, a single post in the Java Forum produced an informative thread with two participants besides myself, while two posts in the MS Community Forum produced no response at all. So while the Java community might not be hugely interested in problems connecting to a Microsoft database, the Microsoft community seems totally uninterested in a Java related problem.

Further digging revealed that while MS have grudgingly produced a JDBC driver for SQL Server, they have never produced one for MS Access. The Sun Website provides a list of 3rd party vendors producing JDBC drivers for MS Access but paying for a driver seems to defeat the object of using an open source IDE. I had a look at some of them to crib tips on connection syntax, but nothing I tried worked on my installation of NetBeans on two different computers.

So, sadly, it seems that if I am to continue down the Java path, I must abandon MS Access, and with it the last connection to the client-server app I built using first VB5 and then a second edition using VB6 all those years ago.

I shall now use a database that ships with NetBeans. I'm not sure which one yet, and since I can't get a connection to my old Access database, I'm not sure exactly how I shall import all my old data.

I shall return to the JDBC Basics trail, this time resigned to rebuilding from scratch, and resigned to a potentially long slow process.

Wednesday, May 6, 2009

JDBC(TM) Database Access

I now have a mini-applet sitting here. The applet embodies Rasch analysis to the extent that items are grouped by difficulty and offered to students according to revealed ability. But the data used to estimate item difficulty is both static and old. If my applet is to be apply Rasch theory in real time, the estimate of item difficulty must be modified and updated while students are using the applet. Currently items are dished up from an array, which is shuffled each time the applet is used. If my applet is to be apply Rasch theory in real time, it needs to serve items from a database, which both includes historical data on each item, and updates it according to current usage.

This will require quite a sophisticated database application. As the front end sits on the web, and may be used by many students at the same time, there must be carefully thought out locking protocols, to avoid either loss or corruption of data. It's not really my area of expertise. I have attached multiple clients to the Microsoft Jet database engine using Visual Basic 6. All the locking side of things was "off the shelf" from MS, and required no conscious thought from me. It worked reasonably well. Schools using 15-20 workstations simultaneously were able to use the application daily for up to a year without major data corruption. If they remembered to build and repair regularly, they could run for longer.

Working with Java will be a whole new ball game, not least because of the DIY mentality that seems to embrace the Java Community. When I post questions in the forum, there is always a negative reaction if forum users suspect I am using NetBeans. The replies always emphasise the importance of building everything from scratch and learning everything from first principles.

Before deluging the forum with questions I should therefore perhaps better have a glance at the JDBC(TM) Database Access trail. The trail comprises two lessons, and the first lesson opens hopefully with the following claim:

JDBC helps you to write java applications that manage these three programming activities:

  1. Connect to a data source, like a database
  2. Send queries and update statements to the database
  3. Retrieve and process the results received from the database in answer to your query

This is exactly what I want to do, and it is made to sound very easy. The next section describes the JDBC Product Components, which again looks fine, but the acid test will be whether I can apply these components to attach my app to my database. The architecture section is also fine in so far as it goes. It's nice (though not very surprising) to know that the option of a three tier structure is built into the language, but for now a two tier structure will do for me. I just want to make a connection. The section on relational databases and SQL seems out of place here. Surely on-one will be trying to connect an app to a database if they don't know what a database is or how to write a query.

The second lesson, from its list of contents looks as if it might offer substance to fulfil the promises of the first. But it quite quickly in my opinion falls into a heap. A good lesson should satisfy two tests. First it should provide all the information required to complete the task it purports to teach. Second it should provide only the information required to complete the task it purports to teach.

This lesson fails on both counts. First it dedicates a whole section to database theory, which the student could reasonably be assumed to know. And then it signally fails to provide adequate information to make a connection to an existing database. The way the Getting Started lesson is written it could just as well, and more honestly, be limited to the following statement:

"Download NetBeans and follow the instructions therein to construct a database application."

A lot of technicians knock Microsoft Access. Yet is perhaps surprising how many "production" systems out there which use Microsoft Access as a back end. Why is this? The short answer is that it is easy to use. And when you come to create a database application in Visual Basic, it is very easy to make a connection with the Access database you've been working with for years and grown to love. And if you graduate to larger organizations, which require a more robust back end, such as SQL Server, it is still very easy to suck the data from your old Access database. In fact it's pretty easy connecting to any database which ODBC compliant.

I've said before that it the creators of Java wanted to encourage programmers from other languages to jump ship, one might expect them to make the transition easy. But rather it seems they prefer preserve the purity of their own elite little club, shunning Microsoft users, and putting up obstacles to the transition, rather than making it easy.