Learning Java

HTML
tag revisited

2011-12-07T07:43:00.000-08:00

I was abruptly forced to revisit this topic when I discovered that my <div> tag laid out login page, which seemed to look so great in Windows Internet Explorer (see below), looked absolutely crap in Firefox (see further below).

So I did some reading, and the theme seemed to be that tables were never intended to do layout. Well I reckon there should be a counter argument, that the <div> tag was never intended to present data.

It seems to me that much of the orthodoxy around HTML is written by graphic artists or publishers, and most of the web sites purported to show the power of <div> tags and CSS, are littered with graphic art and fancy fonts.

For anyone used to programming, meshing <div> tags and CSS is not technically difficult, but perhaps it is not second nature to graphic artists and literary publishers. The instructions emphasize the importance of closing tags, and they suggest using borders during the design phase to get a visual aid on the layout of the divisions.

So I tried that. I show below the HTML for the beginning of a login page:

<body> <div id="container"> <div id="pageHeader"> <h1><span>Rasch-ItemBank</span></h1> <h3><span>A <a class="top" href="http://www.interactived.com/softway.htm"> Softway</a> Open Source Project <br> Hosted by <a class="top" href="http://java.net/projects">Java.net</a></span></h3> </div> <div id="pageBody"> <h3><span><h3>Initial Login Screen</h3></span></h3> <div id="cellA1"> <p><span>Please select user or administrator</span></p> </div> <div id="cellB1"> <p><span><select name="usertype" class=cbox> <option value="user">Student User</option> <option value="admin">Administrator</option> </select></span></p> </div> </div> </div> </body> </html>

And below is the CSS to go with it:

#container { padding: 0 25px 0 120px; margin: 0; position: relative; border: 1px dotted red; } #pageHeader { margin: 5px; border: 1px dotted orange; } #pageBody { margin: 5px; border: 1px dotted green; } #cellA1 { margin: 2px; height: 35px; float: left; border: 1px dotted fuchsia; } #cellB1 { margin: 2px; height: 35px; float: left; border: 1px dotted fuchsia; }

I put little margins around each "division", and I made the borders different colors for clarity. Having rainbows on my mind, I followed a rainbow sequence: Richard of .. gave .. vain. I show below how it came out:

Notice how the red border, which is supposed to embrace everything, stops short, and leaves off two divisions. And the green border, which was created to embrace the "table" and its heading, wraps itself around the heading only.

After many hours of fiddling, I noticed that the "divisions" behave better if they contain background images, but it strikes me that padding out divisions with background images to make them behave predictably, is conceptually not far removed from padding out real tables with spacer gifs.

So I shall put my login form, and all other forms back into tables. I may use CSS to format the tables, and I may use divisions in pages and areas of pages devoted to text and graphics. But my data and forms will be laid out in the traditional manner, using tables.

Bringing a Custom Swing Component to Life

2011-11-21T20:52:00.000-08:00

I am sometimes down on the Java documentation, but in the sand plains of lugubrious and often confusing material there is the occasional gem. One example is a lesson from The Java Tutorial entitled Performing Custom Painting. I was directed to it by a reply to this thread in the Oracle Java Desktop forum.

When I first began my efforts to create a rainbow colored Gaussian distribution curve I began with one of the Tutorial lessons on colors. I have unfortunately lost the URL for the lesson but the code began something like this:

import java.awt.Color; import java.awt.Graphics; import java.awt.Graphics2D; import javax.swing.JFrame; import javax.swing.JPanel; public class Colors extends JPanel { public void paintComponent(Graphics g) { super.paintComponent(g); Graphics2D g2d = (Graphics2D) g; g2d.setColor(new Color(255, 0, 0));//vivid red g2d.fillRect(10, 15, 90, 60);

...

} public static void main(String[] args) { JFrame frame = new JFrame("Colors"); frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); frame.add(new Colors()); frame.setSize(360, 300); frame.setLocationRelativeTo(null); frame.setVisible(true); } }

It looked as shown below.

I modified this by putting the rectangles side by side and end on to produce a crude histogram as shown below:

I reduced the width of the rectangles (to one pixel) and their number (to 800), and made their height and color the subject of mathematical functions. The colors were produced by three out of phase sine waves. That idea from that came from this article by Jim Bumgardner. His explanation is very thorough, so I shall not repeat it here, but in recognition of the idea, my first rainbow colored curve was a sine wave, as shown below:

The mathematical function for a sine wave in Java is really simple:

y = Math.sin(x);

There is, alas, no inbuilt function for a Normal/Gaussian distribution curve, but Wikipedia gives the function as:

I used the middle part of this expression to produce the rainbow colored Gaussian distribution curve shown at the bottom of my previous post. But as I said there, it did nothing. I could not send messages to it or make it change.

I will admit that when I first read the reply to my forum post recommending the Custom Painting tutorial, I was not that optimistic, and I didn't look at it properly until after I had tried all the articles described in my previous blog post. But my cynicism was misplaced, and I should have started there.

The essential code construction from the tutorial begins as follows:

import javax.swing.SwingUtilities; import javax.swing.JFrame; public class SwingPaintDemo1 { public static void main(String[] args) { SwingUtilities.invokeLater(new Runnable() { public void run() { createAndShowGUI(); } }); } private static void createAndShowGUI() { System.out.println("Created GUI on EDT? "+ SwingUtilities.isEventDispatchThread()); JFrame f = new JFrame("Swing Paint Demo"); f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); f.setSize(250,250); f.setVisible(true); }

And the first essential point to note is that it uses:

The SwingUtilities helper class to construct this GUI on the Event Dispatch Thread.

I don't fully understand this, but I've been told to do it before, and it was only when I used this structure for my "custom swing component" that I could get it to work properly.

The second slightly strange thing that the tutorial did was to create its own "custom" JPanel:

class MyPanel extends JPanel { public MyPanel() { setBorder(BorderFactory.createLineBorder(Color.black)); } public Dimension getPreferredSize() { return new Dimension(250,200); } public void paintComponent(Graphics g) { super.paintComponent(g); // Draw Text g.drawString("This is my custom Panel!",10,20); } }

This was all in the same source file, which was modified by replacing:

f.setSize(250,250);

with

f.add(new MyPanel()); f.pack();

It looks trivial, but by following this structure, I was able to use the .pack() command in my applet. Using my original code construction (as shown above), the first time I inserted the new component, I thought it hadn't worked at all because it did not show, and it was only after adding padding to the gridbaglayout and manually resizing the applet that I could see it.

Another point worth noting is the line which inherits functionality from the parent component. This is described in the tutorial as follows:

Most of the standard Swing components have their look and feel implemented by separate "UI Delegate" objects. The invocation of super.paintComponent(g) passes the graphics context off to the component's UI delegate, which paints the panel's background.

This avoided me having to mimic the code structure of the standard swing components. I could focus on the code that made my component different.

The third thing that the tutorial did was to create a "sprite" and code to move it around. My "sprite" is my rainbow colored histogram, and I didn't need "event" code to drag it around the page. But I did need code to alter one or more of the parameters used to build the histogram. The code used by the tutorial was:

... previous imports import java.awt.event.MouseEvent; import java.awt.event.MouseListener; import java.awt.event.MouseAdapter; import java.awt.event.MouseMotionListener; import java.awt.event.MouseMotionAdapter; ... previous unchanged code public MyPanel() { setBorder(BorderFactory.createLineBorder(Color.black)); addMouseListener(new MouseAdapter() { public void mousePressed(MouseEvent e) { moveSquare(e.getX(),e.getY()); } }); addMouseMotionListener(new MouseAdapter() { public void mouseDragged(MouseEvent e) { moveSquare(e.getX(),e.getY()); } }); } private void moveSquare(int x, int y) { int OFFSET = 1; if ((squareX!=x) || (squareY!=y)) { repaint(squareX,squareY,squareW+OFFSET,squareH+OFFSET); squareX=x; squareY=y; repaint(squareX,squareY,squareW+OFFSET,squareH+OFFSET); } } public Dimension getPreferredSize() { return new Dimension(250,200); } protected void paintComponent(Graphics g) { super.paintComponent(g); g.drawString("This is my custom Panel!",10,20); g.setColor(Color.RED); g.fillRect(squareX,squareY,squareW,squareH); g.setColor(Color.BLACK); g.drawRect(squareX,squareY,squareW,squareH); } }

The fourth slightly strange thing that the tutorial did was to put the "sprite" is its own class:

class RedSquare{ private int xPos = 50; private int yPos = 50; private int width = 20; private int height = 20; public void setX(int xPos){ this.xPos = xPos; } public int getX(){ return xPos; } ... more set/get functions public void paintSquare(Graphics g){ g.setColor(Color.RED); g.fillRect(xPos,yPos,width,height); g.setColor(Color.BLACK); g.drawRect(xPos,yPos,width,height); } }

I'm not sure whether this was strictly necessary, but I followed the same structure, putting my histogram into the paintSquare(Graphics g) method, although I called it rainbowHist(Graphics g). I also renamed MyPanel() to MyHist(). The six set/get functions I replaced with two:

public void setMaxValue(int MaxValue){ this.MaxValue = MaxValue; } public void setActValue(int ActValue){ this.ActValue = ActValue; }

The moveSquare(int x, int y) function I made as follows:

public void moveSquare(int MaxValue, int ActValue){ myHist.setMaxValue(MaxValue); myHist.setActValue(ActValue); repaint(); }

I didn't need the mouse listeners, so I removed them altogether.

Already my custom component was beginning to look a bit like a JProgressBar, with a MaxValue and an ActValue, and "progress" indicated by the relationship between the two. In my initial version I made the number of columns in the histogram a linear function of ActValue as a proportion of MaxValue, just like the colored bit in an ordinary JProgressBar.

But I needed to revise this, because I wanted "progress" to be indicated by the area under the curve, not the distance along the x axis.

Wikipedia gives the area under the curve, or "Cumulative distribution function" as:

In this expression "erf" is an abbreviation for "error function", and I was interested to read that much of the work around and even the name of the function derives from measurement theory. Indeed, one of the approximation expressions is:

And this could almost have been lifted straight from the Rasch book. I must say, I've never liked that expression (in fact it gives me the heebie jeebies), so I moved straight down to the Abramowitz and Stegun approximations, and used the first, because I'm aiming for a visual impression here, and don't need seven decimal places of accuracy:

Because we are talking about probability here, the theoretical total area under the graph is 1. So while in the initial version, the critical parameter was number of columns:

for (double i=0; i<ActNoofColumns; i++){

I now set the histogram to build completely by default:

for (double i=0; i<MaxNoofColumns; i++){

and inserted a break to trigger when the area approximation equates to ActValue as a proportion of MaxValue:

if (jonathan > ProportionofMax) break;

I used the variable jonathan, because Wikipedia was a bit vague about the left hand portion of the curve when the mean is zero (and x<0). I guess the measurement theorists who did this work didn't care, because they were only interested in the extreme right hand end of the curve. Wiki suggested:

erf(x)=-erf(-x)

This is correct, but I missed the leading minus sign on the right hand side of the expression, so I did a bit of fiddling around. Anyway, it eventually worked. The illustration below shows my custom component under the JprogressBar it will replace (as well as the curve shown in full for an instance of the component not yet callibrated).

Creating a Custom Swing Component

2011-11-15T21:34:00.000-08:00

I always return to my blog when I'm stuck, and I'm stuck right now.

I want to create a custom Swing component. Specifically, I want something like a JProgressBar, with the following changes:

In place of a flat Foreground color, I want a rainbow spectrum, showing only the red range for low Values, and the whole spectrum for values close to the Maximum value;
Instead of a rectangular box, I want the progress "bar" to take the shape of a normal, or Gaussian, distribution curve;
I want progress to be displayed by the area under the curve, rather than by a simple linear scale along the x axis.

It's not that there isn't stuff out there. I have five tabs open in my browser, specifically addressing the creation of custom components in Swing, as well as the source code for the JProgressBar. It is that, like everything to do with Java, it is bloody difficult to read.

I shall begin from the horse's mouth as it were, with an article on the Java.net website entitled: How to Write a Custom Swing Component. It begins by defining the "building blocks" of Swing components as:

The component class itself, which provides an API for creating, changing, and querying the component basic state.
The model interface and the model default implementation(s) that handle the component business logic and change notifications.
The UI delegate that handles component layout, event handling (mouse and keyboard), and painting.

I understand the first four words of that lot. I hate it when documents, especially formal ones, use abbreviations without definitions, but a Google search on API produces a first page full of formal Sun/Oracle documents, which do that, even in the title.

According to an archived (by which I mean so old or unimportant that Oracle has not woven itself into the URL) glossary, API is defined as:

Application Programming Interface. The specification of how a programmer writing an application accesses the behavior and state of classes and objects.

So if you insert the full definition into the first bullet, you get:

The component class itself, which provides a specification of how a programmer writing an application accesses the behavior and state of classes and objects for creating, changing, and querying the component basic state.

And that is gibberish, like so much of the material used to describe Java, and the closer you get to "the horse" often the more confusing it gets. To be fair, if you insert the words behind the acronym, it looks a little better:

The component class itself, which provides an Application Programming Interface for creating, changing, and querying the component basic state.

But then it starts to overlap with the second "building block", which begins:

The model interface ...

When I first read that, I wasn't sure whether it said model or modal. Either way it is not clear how the model interface differs from the Application Programming Interface.

The third "building block" opens with:

The UI delegate ...

I used the same glossary to look up UI, and it wasn't even in there. A Google search on UI brings up a slew of pages on GUI, and the weight of evidence suggests "User Interface". So now we have Application Programming Interface, model interface, and a User Interface, each purportedly in their own "building block".

Are we really dealing with distinct building "building blocks" here, or a "blob" of amorphous building material with fuzzily defined functionality zones?

The next title in the article, after Basic Building Blocks, is The Component Class: UI Delegate Plumbing, which looks to me very like a composite of building blocks 1 and 3. The third heading is The Model Interface, so presumably it refers to the second building block. It includes some code, with two class declarations, and it opens with:

This (sic) is ... the most important class for a custom component.

The article continues, on and on, in a similarly confusing fashion, so I thought I'd cut to the chase and have a look at the source code and see if I could make it work. The code for the main class JFlexiSlider.java, begins with:

package org.jvnet.flamingo.slider;

import javax.swing.*; import org.jvnet.flamingo.slider.FlexiRangeModel.Range; import org.jvnet.flamingo.slider.ui.BasicFlexiSliderUI; import org.jvnet.flamingo.slider.ui.FlexiSliderUI;

Three questions arise from this. Is the web address given for the imported classes in the public domain? Are the classes listed still there? And if so, is Java smart enough to navigate through the Internet to find them? When I tried to compile the class. The first of 18 errors was:

package org.jvnet.flamingo.slider.FlexiRangeModel does not exist

Out of curiosity I typed jvnet.org into my browser and it came up blank. I ran a whois on jvnet.org, and this confirmed that the domain name is registered, and to Oracle. So I guess that when this code was written, all the package and import information was meaningful, but now it certainly isn't. And to cut a long story short, the effort of addressing each error in turn to make the code run outweighed any possible benefit, so I gave up.

My next port of call was to the source code of the existing JProgressBar. One of eleven imported classes was:

import javax.swing.plaf.ProgressBarUI;

So I checked out the source code for this, which was:

public abstract class ProgressBarUI extends ComponentUI { }

To do a proper job I should have downloaded the source code for ComponentUI as well, but to honest, the whole thing was rendered virtually unreadable by all the comments, so I reached another boredom threshold.

I then went to a much more readable article entitled Creating a custom component in Swing by a Danish gentleman called Christian Petersen. As my Rasch Itembank Project is inspired by the Danish Mathematician, Georg Rasch, I was happy to be reading something by one of his countrymen.

There were no remote or unreachable packages in the code given with this article (in fact there were no packages at all), and the code compiled and ran perfectly first time. Inspired by this success, and the simplicity of his approach, I went right back to basics, to a lesson in The Java Tutorial entitled Compiling and Running Swing Programs. This lesson gives a link to source code for a "program" called HelloWorldSwing.

HelloWorldSwing essentially displays a Swing component called JLabel, in another component called JFrame. I took this code and replaced the JLabel with the class for my rainbow colored Gaussian curve as follows:

import javax.swing.*; public class HelloWorldSwing { private static void createAndShowGUI() { //Create and set up the window. JFrame frame = new JFrame("HelloWorldSwing"); frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //Add the ubiquitous "Hello World" label. NormJ2 label2 = new NormJ2(); frame.getContentPane().add(label2); //Display the window. frame.setSize(520, 300); frame.setVisible(true); } public static void main(String[] args) { //Schedule a job for the event-dispatching thread: //creating and showing this application's GUI. javax.swing.SwingUtilities.invokeLater(new Runnable() { public void run() { createAndShowGUI(); } }); }
}

And it came up as shown below:

I've left all the comments and the HelloWorldSwing heading (and even the component name: label2) to emphasize that creating a custom swing component is really as simple as that. You don't need packages, collections of remote and difficult to find "ui" classes. You just insert one class, compiled with Swing components, into another.

As components go, mine is currently somewhat limited. It looks pretty (though I say it myself), but it does nothing else. The purpose of all the guff at the head of this post is to bring components alive, but there has to be a simpler, more commonsense, way to do it.

Mixing and Matching

2011-11-12T04:10:00.000-08:00

My unfamiliarity with PHP and JavaScript is illustrated by the fact that it has only just occurred to me that I don't have to choose between one or the other, but can in fact enjoy both. So for example, bringing my Add User screen into the PHP fold was simply a matter of changing the file extension and adding:

session_start();

at the top of the file. Everything else remained the same. All the JavaScript remained untouched, all the business rules remained the same, and if the business rules were satisfied, the same PHP file was called to run the data transaction. For display purposes the username was called, and usertype was called to ensure only administrators added to the database.

I had also been scratching my head about passing PHP variables back to the Applet to enrich the data stored in my database, but then it finally sunk into my head that they didn't need to be passed backwards and forwards. Variables (such as username, and IP) gathered by PHP, could simply be stored as session variables, while the user navigated from the login page to the Applet, and when the Applet called a PHP script, those same variables could be called on the page hosting the PHP script. They could then be woven into enhanced SQL commands on that page, without ever having been near the Applet itself.

Security Points

2011-10-29T20:56:00.001-07:00

The first point, about the security of my web site, is that Active Math Java is a test bed for code developed for the Rasch-ItemBank open source project. It is intended as a free resource for use by any child anywhere in the world with access to a computer connected to the Internet and running Java.

So the purpose of my login page is not to secure a web resource for which users pay money. Nor is it like a forum, where the login page protects the forum from spammers. It is simply there for the convenience of certain users who have requested the ability to track the performance of their children.

The second point, about the security of web sites in general, is that secure pages usually include a server side script, such as PHP, and if security is desired, all the pages have to be in the same or compatible format.

Using PHP has some advantages, besides security, such as retaining variables across pages, within sessions.

I have enjoyed writing business rules in JavaScript, for a mock login page and a mock add user page, because I have never liked JavaScript, and I enjoyed getting something to work with it. However, I am now resigned to rewriting everything in PHP, not for security, but for the convenience of retaining and passing variables about the place.

Oh boy am I glad this is a blog about learning Java and related languages and not a formal lesson. If it were a formal lesson, any readers might be really pissed off by now, because I have done about 3 180 degree turns.

Add User to Database

2011-10-28T23:03:00.000-07:00

Having created an HTML layout and an external CSS page, and having written business rules for a login page, creating a page with a form to add users to the database was relatively easy.

The layout was almost identical to the login screen, except for what I call combo boxes for the age of the student users, and to comply with convention, a password confirmation field. But for that convention, the business rules could have been cut and pasted verbatim from the login page, and that would have made the job nice and easy.

Identification of users is not mission critical for me, and if teachers can't record and type in accurately a password on the first attempt, my first instinct was to say "who cares?". But part of the point of this exercise is to make my web site/application look "professional", so I put in the second field.

The login rules comprised four independent conditions nested within a super condition, triggered by any of the four being met, the alternative (or else) to which was the calling of the database query code. So where to put the fifth condition, the inequality of the passwords?

The nest of four are together because they are not mutually exclusive. Most often the user will click the button with nothing in either field, and you want a message to appear over both fields. Or the name might be too long, and the password field empty.

Technically the fifth condition might overlap with one or more of the other four. For example the password fields may be unequal and too long. But if they are too long, and they are told to make them equal first, they will be annoyed if they are told to shorten them after they have made them equal. I guess if I had enough space for messages I could tell them both, but I don't. So in my business rules they have to get the length right (between 1 and eight characters) first, and then make the passwords match. The rules are as follows:

Then if all those rules are satisfied, the PHP script for the data connection is called:

else { //Code below is to set up parameters for the php script if (window.XMLHttpRequest) //format and create request variable according to browser type {// code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } //end of current option else {// code for IE6, IE5 xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } // end of old option xmlhttp.onreadystatechange=function() { // this code displays response text if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("untencell1").innerHTML=xmlhttp.responseText; } } //the code below calls the php script on the server myarg = "('"+subname+"','"+subpass+"','"+subtype+"',"+subagey+","+subagem+")" xmlhttp.open("POST","adduser.php?myarg="+myarg,true); xmlhttp.send(); } //end of master conditional statement } //end of function

I took the opportunity to tidy up the layout a bit. The "row" divisions I had set up (following the structure of a table) seemed a bit redundant, so I lost them, and I defined the warning divisions more concisely, to stop the form jumping about, when the warnings come up. And I made everything a bit wider:

.inputtable {margin:50; background-color:#BDEDFF} .row {width:520;} .col1 {text-align:right; float:left; width:300; height:25; margin:0 5 0 5;} .col2 {text-align:left; float:right; width:200; margin:0 5 0 5;} .itext {width:150;} .cbox {width:150; margin-left:5;} .warning {color="red";margin-bottom:0;margin-top:0;float:left; width:520; }

The PHP script was similar in structure to the login script, but differed in important details (such as running an INSERT command rather than a SELECT query):

<?php include('newinfo.php');// collect database variables. $myarg = $_GET['myarg']; // collect passed variable // To protect MySQL injection $myarg = stripslashes($myarg); // Connect to server and select database. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "INSERT INTO $table1 (Softid, PartPass, PartType, AgeYears, AgeMonths) VALUES "; $query = $query . $myarg; $result = mysql_query($query) or die(mysql_error() . $query ); if($result==1){ echo"<p>Hooray you have added a user</p>"; } else { echo"<p>Oh Dear! Something went wrong. Query: <br/>$query </p>"; } mysql_close($con); ?>

If the password were unequal, but everything else correct, the screen looked as shown below:

And after successfully adding a user is looked as shown below:

Check Login Details against the Database

2011-10-26T04:22:00.000-07:00

After rewriting my business rules in JavaScript, the time now really had come to create the live database interface. So I busily changed the file extension back to .php and added back the include() statement, and started adding PHP code to the JavaScript function called by the button click event, and paused when I needed to pass a javascript variable to PHP. I had a feeling that what I was doing would not work, and a quick Google search confirmed that it would not.

The whole point of converting my business rules to JavaScript was to keep the field input checking local. But as PHP scripts run on the server, you need to call something on the server, and pass any variables to that.

It was a bit frustrating because neither of the login page examples I'd found on the web used any business rules at all; they just called a PHP script from the form submit button, one on the same page, one on another.

As a first pass I tried AJAX.

I had sidestepped the issue in my business rules test page with the line:

alert ("Well Done. Your username and password were correctly entered.");

I now replaced this with:

if (window.XMLHttpRequest) //format and create request variable according to browser type {// code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } //end of current option else {// code for IE6, IE5 xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } // end of old option xmlhttp.onreadystatechange=function() { // this code displays response text if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("untencell1").innerHTML=xmlhttp.responseText; } } //the code below calls the php script on the server xmlhttp.open("POST","logincheck.php?id="+subname+"&pw="+subpass,true); xmlhttp.send();

I also added one more division to receive a message back from the PHP script:

<div id=untencell1></div>

This cell was placed under the button but within the form division. The PHP script was:

<?php include('newinfo.php');// collect database variables. $id = $_GET['id']; // collect passed variable 1 $pw = $_GET['pw']; // collect passed variable 2 // To protect MySQL injection $id = stripslashes($id); $pw = stripslashes($pw); // $id = mysql_real_escape_string($id); // $pw = mysql_real_escape_string($pw); // Connect to server and select databse. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "SELECT Softid, PartPass FROM $table1 WHERE Softid = '$id' and PartPass = '$pw'"; $result = mysql_query($query) or die(mysql_error() . $query ); // Mysql_num_row is counting table row $count=mysql_num_rows($result); if($count==1){ echo"<p>Hooray you are logged in</p>"; } else { echo"<p>Oh Dear! Something went wrong. Query: <br/>$query </p>"; } mysql_close($con); ?>

A couple of things still need fixing. First the user is not taken anywhere useful after logging in successfully. That is because there is currently nowhere to go. The second is that the diagnostic display of the query on login failure needs removing, after everything is definitely working.

After a successful login, the page currently looks as shown below:

JavaScript Business Rules

2011-10-25T09:05:00.001-07:00

In my last two entries, I created the front end interface for a login screen, and wrote a couple of simple business rules in a PHP script to ensure both username and password fields are filled before running a query on the database. The time has now come to write the query and check it against the field entries.

Reading tutorials on the topic, I am first fascinated by the level of paranoia and then I become paranoid myself. So instead of getting on with writing a query, I add another business rule prohibiting usernames or passwords over eight characters. Eight is the length of the fields in the database, so it is as silly for users to enter 9 characters as no characters, so I might as well stop them doing it. It won't stop every SQL injection exploit, but it will preclude them from attempting to write essays.

I'm looking at a couple of tutorials for inspiration. They both use different SQL injection protection code. One uses the ereg() function, which is apparently being phased out, so I won't use that. The other takes the user to another page on login, which won't work with my business rules code.

I have to say I am not sure why I wrote the business rules into a PHP script. If I used Java Script for the business rules, I could take the user to a new page on successful login, and I could completely avoid unnecessary calls on the server itself, as well as on the database. But it will require a complete rethink.

To start fiddling, I change the login page back to an HTML file and remove all PHP. I then replace the PHP scripted error messages with division placed in the same place. The first is as follows:

<div id=ubercell1 class=warning></div><br/>

In the CSS file the class warning takes the color red. I then reduce the form tag down from:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

to:

<form>

And the button is changed from:

<input type="submit" name="login" value="Log in to Portal" class=itext />

to:

<input type="button" value="Log in to Portal" class=itext onclick="busrules(this.form)" />

And of course the PHP code is replaced by the JavaScript function busrules(form). I spent some time fiddling with the JavaScript, because I don't know it very well. The essential logic is that any one or more of four possible errors will generate a warning and preclude the call to the database. When an error is corrected the warning message must be cleared, but where there is no warning (i.e. if the user gets it right first time) nothing needs to happen. And in this test page, if everything is OK, an alert message says everything is OK.

The rest of the page code remained the same, and the page looked the same, but without needing a call back to the server, the error messages came up a lot quicker than they did using PHP. The full JavaScript was:

PHP Business Rules

2011-10-23T02:02:00.000-07:00

The purpose of the business rules layer is to prevent gibberish being written to a database. If you have a field designated to record currency values, you don't want someone posting a long letter to their mother to it. Of course if you try to write text to a numeric field the database itself will probably reject it, but doing so wastes server resources and risks corrupting the database.

And in an age of web applications, where servers and clients are separated by long distance and heavy traffic, sending redundant requests to the server wastes time and annoys the user.

There are two aspects to the business rules layer:

The coded rules;
The front end manifestation when one or more of the conditions set out in the rules is not met.

In the olden days, if you tried to make an illegal entry into a database field, a new window or dialog box opened up with a rude message, and sometimes the computer would beep at you. The dialog box would then have to be closed manually by the user before they could continue with their work.

Nowadays a more subtle approach is preferred. Usually a message is written on to the data entry form itself, just above or close to the field with the inappropriate or missing entry.

In the case of a login screen, nothing is being written to the database, and a null field is conceptually similar to an incorrect entry, so some applications skip business rules in the login screen and send null fields for checking against the database along with everything else.

My server is particularly slow, and I am always aware of the implicit cost of making unnecessary calls on the database, so I shall write a couple of lines to ensure users at least put something in both username and password fields.

The login screen shown in my previous post was written in HTML and tested at home. I shall now have to migrate across to PHP and as I don't have PHP installed at home, I shall have to work on my web host server.

The PHP script will open with the include() statement and a variable declaration for the error condition:

include('dbinfo.php'); // get database information $error = false; //boolean used to contain error condition

It will continue with the equivalent of event code for the button click event. In this case there is no need to register an actual event, because the login button is calling the containing page:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

The code for the whole page is rerun when the button is clicked, so there is no need to register the event as such, but rather the condition of having been clicked:

if (isset($_POST['login'])) { // variables filled with form inputs $usertype = $_POST['usertype']; $username = $_POST['username']; $password = $_POST['password']; // check that neither field is empty if ( !empty($firstname) && !empty($lastname) ) { //run crunchy code } else { $error = true; // error condition met } // end of empty field check } // end of buttonclicked conditional code

The "crunchy code" will be discussed later. For now, I need to insert a couple of extra lines into the page to use the $error variable. The first insert goes at the top of the <div> I called row2, which holds the username field:

<?php if ( $error && empty($username) ) { echo '<span style="gt;Oh Dear! You did not enter your name.</span><br>',"\n"; } ?>

The second insert goes at the top of the <div> I called row3, which holds the password field:

<?php if ( $error && empty($password) ) { echo '<span style="gt;Oh Dear! You did not enter your password.</span><br>',"\n"; } ?>

I have to admit that this is easier using the <div> tag than it would have been using a table. A table would have required a new row, and a cell spanning two column widths for each insert. Using the <div> tag, I just squashed the new code inside the containing division and above the two floating "cells".

The code for the whole page has now become:

<?php include('dbinfo.php'); // get database information $error = false; //boolean used to contain error condition /** * The code below is ignored when the page loads * but is run on reload after button click. */ if (isset($_POST['login'])) { // variables filled with form inputs $usertype = $_POST['usertype']; $username = $_POST['username']; $password = $_POST['password']; // check that neither field is empty if ( !empty($firstname) && !empty($lastname) ) { //run crunchy code } else { $error = true; // error condition met } // end of input check } // end of buttonclicked conditional code ?> <html> <head> <title>Active Math Java Private Portal</title> <link rel="stylesheet" type="text/css" href="pportal.css" /> </head> <body> <div id="container"> <div id="header"> <h1 class="top">Rasch-ItemBank</h1> <h3 class="top">A <a class="top" href="http://www.interactived.com/softway.htm"> Softway</a> Open Source Project <br/> Hosted by <a class="top" href="http://java.net/projects">Java.net</a> </h3> </div> <div id="left"> <b>Menu</b><br /> <a class="menu" href="http://www.interactived.com/softway.htm">Home</a><br /> <a class="menu" href="http://www.interactived.com/research.htm">Research</a><br /> <a class="menu" href="http://www.interactived.com/software.htm">Software</a> </div> <div id="content" style="height:400px;width:85%;"> <h1 class="main">Active Math Java</h1> <h3 class="main">Private Portal<br/> The Blueridge School of Apalit, Inc. </h3> <div id=logintable class=inputtable> <div id=row1 class=row> <h3>Initial Login Screen</h3> <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <div id=row1 class=row> <div id=cell11 class=col1>Please select user or admin</div> <div id=cell12 class=col2> <select name="usertype" class=cbox> <option value="user">user</option> <option value="admin">admin</option> </select> </div> </div><br/> <div id=row2 class=row> <?php if ( $error && empty($username) ) { echo '<span style="gt;Oh Dear! You did not enter your name.</span><br>',"\n"; } ?> <div id=cell21 class=col1>Please enter your name</div> <div id=cell22 class=col2><input type="text" name="username" class=itext /></div> </div><br/> <div id=row3 class=row> <?php if ( $error && empty($password) ) { echo '<span style="gt;Oh Dear! You did not enter your password.</span><br>',"\n"; } ?> <div id=cell31 class=col1>Please enter your password</div> <div id=cell32 class=col2><input type="password" name="password" class=itext /></div> </div><br/> <div id=row4 class=row> <div id=cell41 class=col1>Click button to log in</div> <div id=cell42 class=col2><input type="submit" name="login" value="Log in to Portal" class=itext /></div> </div><br/> </form> </div> </div> </div> <div id="footer"> Helping Children to Achieve their Potential</div> </div> </body> </html>

And when I clicked the login button, with two empty fields, the page came up as shown below:

Creating a login page

2011-10-22T09:35:00.000-07:00

There are three steps to creating a login page, or really any page which exchanges information with a database.

Creating the front end interface;
Writing the business rules;
Writing the interface with the database.

In a previous post I already described a form which added records to a data table. In that example I was a bit sloppy, because I omitted any business rules code. My excuse was that the data table in that example was intended to be populated automatically, so the input form would never be used in real life.

I am now dealing with a request from a school to track student performance, so I have to create a manual database portal or interface for them. The first step is the login screen. At a trivial level, this is just a couple of fields and a button, so not much thought needs to go into it.

But as I am using the exercise to stimulate a web site redesign, I have chosen to go a little deeper. My usual method of making forms look reasonably neat is to shove them in a table. But the HTML purists don't like table any more, so I decided to look into using the <div> tag instead. At the same time, I extended my use of the external CSS page, which I used in my previous post.

Whether correctly or not, I followed the pattern of a table, with a series of horizontal rows, each nested with two "cells", one floating to the left, the other to the right, and all inside a "zone" which I classified as "inputtable", which in turn lay inside the page (code not shown) created in my previous post:

<div id=logintable class=inputtable> <div id=row0 class=row> <h3>Initial Login Screen</h3> <form> <div id=row1 class=row> <div id=cell11 class=col1>Please select user or admin</div> <div id=cell12 class=col2> <select name="usertype" class=cbox> <option value="user">user</option> <option value="admin">admin</option> </select> </div> </div><br/> <div id=row2 class=row> <div id=cell21 class=col1>Please enter your name</div> <div id=cell22 class=col2><input type="text" name="username" class=itext /></div> </div><br/> <div id=row3 class=row> <div id=cell31 class=col1>Please enter your password</div> <div id=cell32 class=col2><input type="password" name="password" class=itext /></div> </div><br/> <div id=row4 class=row> <div id=cell41 class=col1>Click button to log in</div> <div id=cell42 class=col2><input type="submit" value="Log in to Portal" class=itext /></div> </div><br/> </form> </div> </div>

To accommodate the extra classes, the CSS page was expanded as follows:

body { margin:10px 0px; padding:0px; text-align:center; } #container {width:800;} #header {background-color:#151B8D;text-align:left;} #left {background-color:#5CB3FF;height:500px;width:15%;float:left;text-align:left;} #content {height:400px;width:85%;} #footer {background-color:#488AC7;clear:both;text-align:center;} a:link {text-decoration:none;} /* unvisited link */ a:visited {text-decoration:none;} /* visited link */ a:hover {text-decoration:underline;} /* mouse over link */ a:active {text-decoration:none;} /* selected link */ h1.top {margin-bottom:0;color=#FFFF00;} h3.top {margin-bottom:0;margin-top:0;color=#FFFF00;} a.top {color=#FFFF00;} h1.main {margin-bottom:0;text-align:center;} h3.main {margin-bottom:0;margin-top:0;text-align:center;} a.menu {color=#000000;} .inputtable {margin:50; background-color:#BDEDFF} .row {width:420;} .col1 {text-align:right; float:left; width:200; margin:0 5 0 5;} .col2 {text-align:left; float:right; width:200; margin:0 5 0 5;} .itext {width:150;} .cbox {width:150; margin-left:5;}

And the finished page looked as follows:

Web site redesign

2011-10-21T04:26:00.000-07:00

In my quest to get my Applet talking to a commercially hosted MySQL database, I was forced to update my knowledge of a number of related areas, including HTML. Coincidentally, I get a lot of spam telling me how dreary and old fashioned my MS Front page designed website is.

A school has asked for a private portal to the Applet, and I want the page to remain outside the main website navigation structure. I could just give them a form on a plain page. Or I could use the opportunity to play with HTML layouts and CSS.

My favorite tutorial site, W3schools, has a nice sample layout, similar to their own. I have adapted this with my own colors and text. I have also changed the behavior of links, so that they look more like surrounding test, except when they are hovered over. My HTML was as follows:

<html> <head> <title>Active Math Java Private Portal</title> <style type="text/css"> a:link {text-decoration:none;} /* unvisited link */ a:visited {text-decoration:none;} /* visited link */ a:hover {text-decoration:underline;} /* mouse over link */ a:active {text-decoration:none;} /* selected link */ </style> </head> <body> <div id="container" style="width:100%"> <div id="header" style="background-color:#151B8D"> <h1 style="margin-bottom:0;color=#FFFF00;">Rasch-ItemBank</h1> <h3 style="margin-bottom:0;margin-top:0;color=#FFFF00;">A <a href="http://www.interactived.com/softway.htm" style="color=#FFFF00;"> Softway</a> Open Source Project</h3> <h3 style="margin-bottom:0;margin-top:0;color=#FFFF00;">Hosted by <a href="http://java.net/projects" style="color=#FFFF00;">Java.net</a></h3> </div> <div id="menu" style="background-color:#82CAFA;height:400px;width:15%;float:left;"> <b>Menu</b><br /> <a href="http://www.interactived.com/softway.htm" style="color=#000000;">Home</a><br /> <a href="http://www.interactived.com/research.htm" style="color=#000000;">Research</a><br /> <a href="http://www.interactived.com/software.htm" style="color=#000000;">Software</a></div> <div id="content" style="height:400px;width:85%;"> <h1 style="margin-bottom:0;text-align:center">Active Math Java</h1> <h3 style="margin-bottom:0;margin-top:0;text-align:center">Private Portal</h3> <h3 style="margin-bottom:0;margin-top:0;text-align:center"> The Blueridge School of Apalit, Inc. </h3> </div> <div id="footer" style="background-color:#56A5EC;clear:both;text-align:center;"> Helping Children to Achieve their Potential</div> </div> </body> </html>

And it came up as shown below:

The tutorial recommended using an external CSS style sheet, so as to facilitate site wide design changes. That's fine for colors and fonts, but a question running through my mind is how they achieve site wide menu changes. Their own source code betrays few secrets these days because it might have been generated with a script, although it certainly represents a thorough example of using the <div> tag.

I found the CSS tutorial a bit confusing when it came to classes, but part of the confusion arose because of my inability to type. Anyway, after some fiddling around, my CSS page looked like this:

a:link {text-decoration:none;} /* unvisited link */ a:visited {text-decoration:none;} /* visited link */ a:hover {text-decoration:underline;} /* mouse over link */ a:active {text-decoration:none;} /* selected link */ h1.top {margin-bottom:0;color=#FFFF00;} h3.top {margin-bottom:0;margin-top:0;color=#FFFF00;} a.top {color=#FFFF00;} h1.main {margin-bottom:0;text-align:center;} h3.main {margin-bottom:0;margin-top:0;text-align:center;} a.menu {color=#000000;} #header {background-color:#151B8D;} #left {background-color:#82CAFA;height:400px;width:15%;float:left;} #content {height:400px;width:85%;} #footer {background-color:#56A5EC;clear:both;text-align:center;}

And the HTML became:

<html> <head> <title>Active Math Java Private Portal</title> <link rel="stylesheet" type="text/css" href="pportal.css" /> </head> <body> <div id="container" style="width:100%"> <div id="header"> <h1 class="top">Rasch-ItemBank</h1> <h3 class="top">A <a class="top" href="http://www.interactived.com/softway.htm"> Softway</a> Open Source Project <br/> Hosted by <a class="top" href="http://java.net/projects">Java.net</a> </h3> </div> <div id="left"> <b>Menu</b><br /> <a class="menu" href="http://www.interactived.com/softway.htm">Home</a><br /> <a class="menu" href="http://www.interactived.com/research.htm">Research</a><br /> <a class="menu" href="http://www.interactived.com/software.htm">Software</a> </div> <div id="content" style="height:400px;width:85%;"> <h1 class="main">Active Math Java</h1> <h3 class="main">Private Portal<br/> The Blueridge School of Apalit, Inc.</h3> </div> <div id="footer"> Helping Children to Achieve their Potential</div> </div> </body> </html>

And to my enormous surprise it ended up looking exactly the same as that produced by the single page as shown above.

Applet to AJAX to PHP methodology

2011-09-24T00:59:00.000-07:00

I have an Applet, which adds lines to a database one at a time, by sending an SQL string via AJAX to a PHP script. The Applet data insertion method is as follows:

private void addItem3(String newWord) { if(LIVE) { if(jso != null ) try { jso.call("updateWebPage", new String[] {newWord}); } catch (Exception ex) { addItem2("jso call failed... "); ex.printStackTrace(); } } }

Where the parameter newWord being passed to that method might look like:

INSERT INTO mytable (Partid, OpCode, ItemLeft, ItemRight, Raw, Rate) VALUES (684, 1, 2, 5, 1, 34)

The JavaScript function updateWebPage and the PHP script were given in my previous post, so I won't repeat them here.

The thing is, even a single instance of the Applet can generate new lines very quickly, sometimes once a second. So a classroom of 25 students might generate 25 lines a second. How will my ISP server react to all these requests to open a connection, insert a single line of data, and close the connection.

In my experience, a typical eCommerce interaction involves a fairly long data reading session (browsing a catalogue) and then at the end, perhaps the insertion of a single line of data into a purchase order table. And from forum browsing, most Applet games seem to download some data at the start of the game, and then perhaps upload a line of data at the the end of the game. And if it is a game like Pacman, it might last for 10 or 15 minutes, or longer. So I am inclined to believe that your average eCommerce server, and certainly not a budget one like the one I use, is not designed to cope with such frequent data updates from a single source.

This blog is intended for theory questions and problems. Practical implementation thoughts and solutions are the domain of my Rasch blog, so I think I'll transfer this one over there.

Applet to JavaScript to PHP post to Database

2011-09-20T00:09:00.000-07:00

This post brings together the previous two. In the first, I called PHP code from a JavaScript function using an AJAX command. In the second, I passed an SQL command to a JavaScript function. In this post I describe bringing the two together. Instead of simply displaying the SQL in a text box on the web page, passing it to a PHP file.

The applet code was unchanged, but the JavaScript function was extended from:

<script type="text/javascript"> function updateWebPage(myArg) { document.getElementById("txt1").innerHTML=myArg; } </script>

to:

And the HTML table had an extra couple of cells added:

<table border=1 align='center' cellpadding=0 cellspacing=0 > <tr><td style='text-align:center; background-color:#C0C0C0'>Compiled Java Applet</td></tr> <tr><td><applet code="JSHelloWorld2.class" width="500" height="80" MAYSCRIPT style="border-width:0;" name="jsap" id="jsap"> </applet> </td></tr> <tr><td style='text-align:center; background-color:#C0C0C0'>HTML Textbox filled by JavaScript</td></tr> <tr><td><textarea style='width:500px; height:50px' name='txt1' id='txt1'>Query goes here</textarea></td></tr> <tr><td style='text-align:center; background-color:#C0C0C0'>HTML diagnostic messages rendered by PHP script</td></tr> <tr><td><div id="cbxItem">PHP info will populate this space</div></td></tr> </table>

The PH script was:

<?php $id = $_GET['id']; include('dbinfo.php');// collect database variables and connect. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); // first use encodeURIComponent on javascript to encode the string // receive json string and prepare it to json_decode $jsonStr = stripslashes ($id); $query = $jsonStr; $result = mysql_query($query) or die(mysql_error()); echo"<p>The query is: $query </p>"; echo"<p>The result is: $result </p>"; mysql_close($con);
?>

I hate it when people put obscure references in code, so for anyone who has not been following this blog, the dbinfo.php structure was given in the Simple Web Application – Data Display page.

On opening, the web page looked as shown below:

And after clicking the button on the applet, it was as shown below:

The result of 1 (or true) returned by the query indicated that a line had been successfully inserted. Inspection of the database using the Simple Web App, confirmed that this was the case. The only minor problem was the loss of the plus sign in "Itemdet", but that can be fixed.

Pass SQL command from Applet to JavaScript function

2011-09-19T21:11:00.000-07:00

My last post recorded an important milestone for me, because I managed to call PHP code from a JavaScript function.

On an earlier occasion, I had managed to call a JavaScript function from an Applet.

My next task was to put the two together, and I did this in two steps the first of which (reported in the current post) was to adapt the previous Applet to JavaScript example to display an SQL command. This was essentially a cosmetic exercise, but it acted as a refresher on the code.

The only change to the Applet code was to replace "Hello World" with an SQL string. There is no need to reprint that here as it was given in my previous post on the topic.

In the HTML, I modified the table to display the Applet above the text box, and I widened them both. I also removed the update Applet function, as it was not required for the current exercise. It was then as follows:

<html> <body> <script type="text/javascript"> function updateWebPage(myArg) { document.getElementById("txt1").innerHTML=myArg; } </script> <form> <table border=0 align='center' cellpadding=0 cellspacing=0 > <tr> <td valign='top'> <table> <tr><td style='text-align:center; background-color:#EEEEEE'>Applet</td></tr> <tr><td> <applet code="JSHelloWorld2.class" width="500" height="80" MAYSCRIPT style="border-width:0;" name="jsap" id="jsap"> </applet> </td></tr> <tr><td style='text-align:center; background-color:#EEEEEE'>JavaScript</td></tr> <tr><td><textarea style='width:500px; height:50px' name='txt1' id='txt1'>Query goes here</textarea></td></tr> </table> </form> </body> </html>

It came up initially as shown below:

After clicking the Applet button it was then as shown below:

And to my amazement, I could modify the text (see below) as many times as I liked, and the modified text was transferred across every time.

PHP and JavaScript/AJAX database query

2011-09-18T00:37:00.000-07:00

Including the term JavaScript here is tautologous really because AJAX written out in full is Asynchronous JavaScript and XML. But as combining PHP and JavaScript has become something of a holy grail for me, I thought I'd include it anyway. In fact my September 9 blog entry was originally posted under the title PHP and JavaScript (so keen was I to use it), until I reflected on the text overnight and realised there was actually nothing about JavaScript in the post.

I have been running around in circles a bit over the last couple of months, but they have been good circles, because they have enriched my understanding of a number of web related topics. And my current circle has taken me back to the generally excellent and easy to follow W3Schools website. In my post on their AJAX tutorial, I bemoaned that fact that their exchanging data with a server example only read data from a "measly old text file", rather than a database. However, had I dug deeper in my first post on PHP, I might have noticed that the w3schools tutorial on the topic gives an example of communicating with a database, using AJAX and PHP, in one of the advanced sections. I shall now work through that example.

My first step is to cut and paste their exact web code to see if it works. Should I post it here? When I first began this blog, I posted almost no code, but included an array of links to sources. A couple of years later, when I went back to reread some of my early posts, I found that many of my sources had changed their URL or disappeared altogether. So I started posting code. But the question then arises, should the full 30 or more line copyright notice be included with every code snippet. I think not. That would render the blog unreadable. "Fair Practice Law" in most countries allows you to copy snippets, certainly from literature, as long as you acknowledge your source. Would that apply to a full working web page? Possibly not. I shall refrain from quoting it, and apologise if the source subsequently disappears.

Of course I have posted code for some working web pages in my last few entries, (and in fact below) but I think one, of a couple of important questions, is did I copy something and change it, or did I write something of my own and draw inspiration from a source. It is something of a moot point, but I think there are a couple of things to bear in mind. Did I begin with a downloaded file, or did I begin with a clean sheet of paper? In this case I began clean, because of the database communication differences. A second important question is did I use a single source for inspiration? Again, I used more than one source, for practical and cosmetic reasons.

Meanwhile, back in reality, I have cut and pasted the HTML, and it works. And while I am not posting the HTML, I shall post a screen shot (below), because on an empty page it looks a bit different. But it certainly works. I had a quick look at the dropdown box, and the names were all there as in the tutorial. Obviously I refrained from selecting one because I haven't written a PHP file. And this is where I have to put my thinking cap on, because for this to be useful to me, it has to work with my data, and there are no "names" in my database.

Getting it to work for me involved one major change to the HTML, and a few cosmetic changes. The major change was to rewrite the dropdown box from scratch to make it meaningful for my data. The cosmetic changes were to variable names - not strictly necessary, but they always say variable names should try to reflect what they contain.

An important point to note here, for the unwary, is that parameters passed by AJAX seem to in the form of strings. The index field in my database is an integer, so I was tempted to try and pass an integer through AJAX. But from my notes on AJAX the syntax of the open method is:

xmlhttp.open("METHOD","URL",async);

Where URL is, and will always be a string. So in my code (shown below), my attempts to change the idx variable (in the function, and in the dropdown box) to a number were futile, and had to be abandoned. The variable was passed as a string, but the character was included in the SQL statement (shown further below) as a number.

A final point is that I included some diagnostic lines, but these were pretty redundant as PHP errors were picked up in the parse, and SQL errors were also caught by an error trap in the code. After all this, my HTML was:

<html> <head> <head> <title>AJAXPHPexpt1</title> <script type="text/javascript"> function showItem(idx) { if (idx=="") { document.getElementById("cbxItem").innerHTML=""; return; } if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } else {// code for IE6, IE5 xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("cbxItem").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","getitem.php?id="+idx,true); xmlhttp.send(); } </script> </head> <body> <form> <select name="itemselection" onchange="showItem(this.value)"> <option value="">Select an item:</option> <option value="2">2+2=</option> <option value="4">2+3=</option> <option value="7">4+3=</option> </select> </form> <br /> <div id="cbxItem"><b>Item info will be listed here.</b></div> </body> </html>

And my PHP code was:

<?php $id = $_GET['id']; include('dbinfo.php');// collect database variables and connect. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "SELECT * FROM mytable WHERE Itemid=".$id; $result = mysql_query($query) or die(mysql_error()); echo"<p>The item id is now: $id </p>"; echo"<p>The query is: $query </p>"; echo '<table style="text-align:center;"> <tr bgcolor="#CCCCCC"> <td width="60"><strong>Itemid</strong></td> <td width="60"><strong>Partid</strong></td> <td width="60"><strong>OpCode</strong></td> <td width="60"><strong>Itemdet</strong></td> <td width="60"><strong>Raw</strong></td> <td width="60"><strong>Rate</strong></td> </tr>'; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['Itemid'] . "</td>"; echo "<td>" . $row['OpCode'] . "</td>"; echo "<td>" . $row['Partid'] . "</td>"; echo "<td>" . $row['Itemdet'] . "</td>"; echo "<td>" . $row['Raw'] . "</td>"; echo "<td>" . $row['Rate'] . "</td>"; echo "</tr>"; } mysql_close($con); ?>

And after selecting an item, the page was as shown below:

Simple Web Application – Delete Data line

2011-09-17T00:22:00.000-07:00

Once again I should like to acknowledge the Virginia Tech Simple Web Application sample code, which provided a template for this little project, which has really helped to improve my practical understanding of PHP.

Having said that, the code, required to delete lines from the database, is pretty slim. Once again I bloated it out with some diagnostic HTML, just to check the query. I hate running blind.

The bloated code was as follows:

<?php $id = $_GET['id']; if (empty($id)) { Header("Location: listcontacts.php"); exit; } include('dbinfo.php');// collect database variables and connect. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "DELETE FROM mytable WHERE Itemid=".$id; $result = mysql_query($query) or die(mysql_error()); mysql_close($con); //close connection because job finished // Header("Location: jsphp4.php"); //return to main display form ?> <html> <head> <title>Delete item diagnostics</title> </head> <body> <h1>Delete Item</h1> <p>The item id is now: <?php echo $id ?> </p> <p>The query is: <?php echo $query ?> </p> </body> </html>

And the page was as shown below:

After checking the query, the slimmed down “production” code was:

<?php $id = $_GET['id']; if (empty($id)) { Header("Location: listcontacts.php"); exit; } include('dbinfo.php');// collect database variables and connect. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "DELETE FROM mytable WHERE Itemid=".$id; $result = mysql_query($query) or die(mysql_error()); mysql_close($con); //close connection because job finished Header("Location: jsphp4.php"); //return to main display form ?>

And of course there is no pic to show, because after the delete code has run we are returned to the main page.

In fact this whole exercise derived from a desire to check how that is done - modifying data in a database while (having the illusion of) remaining on the same page.

So now I have called a JavaScript function from an Applet, and I have called a PHP script, which has modified data in a database, from a button on a web page.

My next step is to call a PHP script from an Applet.

Simple Web Application – Add Data Form

2011-09-16T01:06:00.000-07:00

While I must still acknowledge the Virginia Tech Simple Web Application sample code, at this stage in the project I was primarily cutting and pasting from the code shown in my previous posts, and manually making a few small changes from the sample code.

The code used in the Add data line form is very similar to that for the Edit data line form, except that there is no existing data to display, and the SQL command is INSERT rather than UPDATE.

I also removed the input validation code. Any data entered manually into my database is bogus and must be deleted after this exercise has finished, so it really doesn't matter what fields are filled or left empty.

With no data to display, there no need to confirm that a data line was being correctly passed from the main display form, and no need to confirm that the line was being correctly read. I therefore left in just a couple of diagnostic messages at the top of the form:

<p>The query is: <?php echo $query ?> </p> <p>Status is: <?php echo $jonathan ?> </p>

Of course to view these lines after clicking the add button, it is necessary to comment out the instruction to return to the main form right after adding data as follows:

// Header("Location: jsphp4.php"); //return to main display form

When everything is working the comment strokes (on the left) can be removed, and the diagnostic lines can be removed from the HTML.

My working (diagnostic) code was as follows:

<?php include('dbinfo.php');// collect database variables and connect. $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); if (isset($_POST['addcontact'])) { // run this when the user hits the "Add Contact" button $jonathan="Add loop"; $Partid = $_POST['Partid']; $OpCode = $_POST['OpCode']; $Itemdet = $_POST['Itemdet']; $Raw = $_POST['Raw']; $Rate = $_POST['Rate']; // input validation removed from under here $query = "INSERT INTO mytable (Partid, OpCode, Itemdet, Raw, Rate) VALUES (".$Partid.",".$OpCode.",'".$Itemdet."',".$Raw.",".$Rate.")"; $result = mysql_query($query) or die(mysql_error()); //adds form inputs mysql_close($con); //close connection because job finished Header("Location: jsphp4.php"); //return to main display form } ?> <html> <head> <title>Add a Line</title> </head> <body> <h1>Add a Line</h1> <p>The query is: <?php echo $query ?> </p> <p>Status is: <?php echo $jonathan ?> </p> <form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <table style="text-align:right;"> <tr><td>Partid:<input name="Partid" type="text" value="<?php echo $Partid; ?>"></td></tr> <tr><td>Opcode:<input name="OpCode" type="text" value="<?php echo $OpCode; ?>"></td></tr> <tr><td>Itemdet:<input name="Itemdet" type="text" value="<?php echo $Itemdet; ?>"></td></tr> <tr><td>Raw:<input name="Raw" type="text" value="<?php echo $Raw; ?>"></td></tr> <tr><td>Rate:<input name="Rate" type="text" value="<?php echo $Rate; ?>"></td></tr> <tr><td><input type="submit" name="addcontact" value="Add Line"></td></tr> </table> </form> <p><a href="jsphp4.php">Click here to return to List</a></p> </body> </html>

And after clicking the Add Line button, the page, with diagnostic code, was as shown below:

After removing the diagnostic code the page came up as shown below:

Simple Web Application – Data edit form

2011-09-15T18:13:00.001-07:00

The Virginia Tech Simple Web Application sample code works perfectly on their server, but as I reported in my previous post, getting something similar to run with my own database has been a fairly long and frustrating task.

I said in my last post that I constructed my own PHP PEAR database layer, as a result of which, my code and the sample code diverged from an early point. Specifically:

include('dbconnect.php');

was stretched out to:

include('dbinfo.php'); $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con);

I had some specific problems with certain lines of the sample code. One was:

$result = $database->query($query);

I didn’t understand the funny little -> in this line (I think it might have something to do with Perl, which takes it beyond the scope of this post), nor the general syntax, so I replaced it with:

$result = mysql_query($query) or die(mysql_error());

Another example was

$contact = $result->fetchRow(DB_FETCHMODE_ASSOC,0);

Which I replaced with

$contact = mysql_fetch_row($result);

I also replaced the text references to array members:

$Itemid = $contact['Itemid'];

With array member indices:

$Itemid = $contact[0];

Getting the form to work at all was a long slow process, so I added a series of diagnostic variables displayed in notes above the form. For example:

$jonathan="Default loop";

These enabled me to track which branches of code were executing, and which variables were being properly filled.

Further divergence derived from differences in the style of the data. The sample code set out to teach among other things the different methods of entering personal data on a form (radio buttons etc.), whereas my data is mainly numeric. This first required me modify the SQL to accommodate my data types. And on the cosmetic side, because my data is uniform in style, I wanted a uniform look. I therefore put my data entry fields into a table, whereas the sample code used a lot of line breaks.

After all this, my code was as follows:

<?php // get contact id $id = $_GET['id']; if (!empty($id)) { $_SESSION['recordId']=$id; } else { $id = $_SESSION['recordId']; } // if the script has been called without ID or the user hit "Cancel" just return to listing if (empty($id) || isset($_POST['cancel'])) { Header("Location: jsphp4.php"); exit; } include('dbinfo.php'); $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $query = "SELECT * FROM mytable WHERE Itemid=".$id; $result = mysql_query($query) or die(mysql_error()); $contact = mysql_fetch_row($result); $Itemid = $contact[0]; $OpCode = $contact[1]; $Partid = $contact[2]; $Itemdet = $contact[3]; $Raw = $contact[4]; $Rate = $contact[5]; mysql_close($con); ?> <html> <head> <title>Edit Items Table</title> </head> <body> <a href="jsphp4.php">Add a Line</a> | <a href="jsphp4.php">Session Data</a> <h1>Edit Item</h1> <p>The item id is now: <?php echo $id ?> </p> <p>The query is: <?php echo $query ?> </p> <p>The op code is: <?php echo $query ?> </p> <form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <table style="text-align:right;"> <tr><td>Itemid:<input name="Itemid" type="text" value="<?php echo $Itemid; ?>"></td></tr> <tr><td>Opcode:<input name="OpCode" type="text" value="<?php echo $OpCode; ?>"></td></tr> <tr><td>Partid:<input name="Partid" type="text" value="<?php echo $Partid; ?>"></td></tr> <tr><td>Itemdet:<input name="Itemdet" type="text" value="<?php echo $Itemdet; ?>"></td></tr> <tr><td>Raw:<input name="Raw" type="text" value="<?php echo $Raw; ?>"></td></tr> <tr><td>Rate:<input name="Rate" type="text" value="<?php echo $Rate; ?>"></td></tr> <tr><td><input type="hidden" name="id" value="<?php echo $id; ?>"> <tr><td><input type="submit" name="ok" value=" OK "> <tr><td><input type="submit" name="cancel" value="Cancel"> </table> </form> </body> </html>

And it looked like this:

Simple Web Application – Data Display page

2011-09-14T19:18:00.000-07:00

I can read tutorials 'til I'm blue in the face, but unless I try to adapt the sample code to do something completely different, I don't retain anything. The live "Try it yourself" windows are quite fun, but unless I can get code running on my own or my ISP's server, I can't expose my misunderstandings or the gaps in my knowledge.

The Virginia Tech simple web application sample code works perfectly on their server, but recreating something to perform similar functions with my own database on my own server has been a fairly long and frustrating job.

I said in my last post that I could not see any gaps in the sample code. On closer inspection, I have found a couple, of which the most important was their connection code. They refer to a PHP PEAR database layer in the text and a file called DB.php in their code, but they don’t show what is in the file. So I followed the PHP PEAR link and constructed my own file based on the template given there:

<?php $dbhost = 'localhost'; $dbuser = 'theuser'; $dbpass = 'thepassword'; $dbname = 'phptst'; ?>

This meant that my code and the sample code were beginning to diverge from a fundamental point. Because my PHP PEAR database layer was different, my connection code in the main page was different. Because of that, my query code had to be different, and because of that, my display code had to be different. In fact in the end I based the whole thing on the example previously quoted on my Playing with PHP entry.

In the end, the code in my display table was like the inverse, or a mirror image of the sample code. Their display table was written in HTML, peppered with PHP variables. My display table was a long PHP statement, peppered with HTML strings.

My code was as shown below:

<?php // if the user hasn't specified any "sort" the default is "Partid" if ( isset($_GET['sort']) && $_GET['sort']=='OpCode' ) { $_SESSION['sort'] = 'OpCode'; } else { $_SESSION['sort'] = 'Partid'; } include('dbinfo.php'); $con = mysql_connect( $dbhost, $dbuser, $dbpass ); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($dbname, $con); $result = mysql_query("SELECT * FROM mytable") or die(mysql_error()); ?> <html> <head> <title>Session Data</title> </head> <script language="JavaScript">  </script> <body> <a href="addcontact.php">Add a Line</a> | My Items <h1>Session Data</h1> <table style="text-align:center;"> <tr bgcolor="#CCCCCC"> <td width="70"><strong>Itemid</strong></td> <td width="70"><strong><?php if ($_SESSION['sort']!='OpCode') { echo '<a href="',$_SERVER['PHP_SELF'],'?sort=OpCode">Partid</a>'; } else { echo 'OpCode ↓'; } ?></strong></td> <td width="70"><strong><?php if ($_SESSION['sort']!='Partid') { echo '<a href="',$_SERVER['PHP_SELF'],'?sort=Partid">OpCode</a>'; } else { echo 'OpCode ↓'; } ?></strong></td> <td width="70"><strong>Itemdet</strong></td> <td width="70"><strong>Raw</strong></td> <td width="70"><strong>Rate</strong></td> <td width="70"><strong>Edit</strong></td> <td width="70"><strong>Delete</strong></td> </tr> <?php while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['Itemid'] . "</td>"; echo "<td>" . $row['OpCode'] . "</td>"; echo "<td>" . $row['Partid'] . "</td>"; echo "<td>" . $row['Itemdet'] . "</td>"; echo "<td>" . $row['Raw'] . "</td>"; echo "<td>" . $row['Rate'] . "</td>"; echo '<td><a href="editcontact.php?id=' . $row['Itemid'] . '">edit</a></td>'; echo '<td><a href="deletecontact.php?id=' . $row['Itemid'] . '">delete</td>'; echo "</tr>"; } ?> </table> </body>

And the page looked like this:

A closer look at PHP database update options

2011-09-09T13:14:00.000-07:00

A frustration I've had with most tutorials on using JavaScript with PHP to update a database is that the "add data" function nearly always calls a new page. In my application, I want data added to a database quietly in the background, and I certainly don't want the user dragged away from the Applet page every time a line is added to the database.

After much searching, and searches excluding terms such as "forum", (forum questions seem to dominate this topic in Google), I found an excellent page hosted by Virginia Tech. This is a really good single page tutorial, which takes the reader through all the basics of PHP, from echo "Hello World!"; to a simple web application, which includes all the essential functions of adding data, displaying data, editing data and deleting records. Better still, for each task, it includes the PHP source code, produced HTML, and rendered HTML. There are no zip files to download, and certainly at first glance, no holes or gaps in the code.

Most importantly, for me, the tutorial makes explicitly clear the code required to stay on the same page while information is being posted from a form. So if information is entered on an ordinary HTML page (with a name and extension like myForm.htm) and is to be processed by a page called myCode.php, the opening form tag should look like this:

<form method="post" action="myCode.php">

But if the posting is to be handled on a form containing the processing code (which will need the extension .php - so it might be called something like myCodedForm.php), the opening form tag should look like this:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

Alternatively it might look like this:

<form method="post" action="myCodedForm.php">

My plan is to adapt the examples in this tutorial to the creation of a dummy application, which adds lines to my own database, and to describe my progress here. I shall skip the database creation steps, because that has already been described here. That was for a Derby database, but I also described connecting to a MySQL version with PHP here. I shall focus on the code used to add lines, to view the table contents, and to delete lines.

My progress may be a little slow. I have read a couple of tutorials on JavaScript and PHP, but my practical working knowledge is currently very limited. I shall fiddle away, and post any interesting results as they arise.

Applet to JavaScript Communication

2011-09-06T01:25:00.000-07:00

The raditha.com site, to which I referred in my Javascript to Applet Communication - Example 2 entry, also includes a page on Applet to JavaScript Communication, but I could not find the required HTML/JavaScript, either on the page or in the code files included at the end of the page. This was disappointing because overall it is the best site I have found on what Mozilla calls LiveConnect.

The Java code works, and I shall cite a simplified (less graphics) version here:

import javax.swing.*; import netscape.javascript.JSObject; import java.awt.*; import java.awt.event.*; public class JSHelloWorld2 extends JApplet { JTextArea txt = new JTextArea(100,100); JButton btn = new JButton("Update Page"); JSObject jso; public JSHelloWorld2() { txt.setText("Hello World"); getContentPane().setLayout(new BorderLayout()); getContentPane().add(txt); getContentPane().add(btn,BorderLayout.SOUTH); btn.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { if(jso != null ) try { jso.call("updateWebPage", new String[] {txt.getText()}); } catch (Exception ex) { ex.printStackTrace(); } } }); } public void init() { jso = JSObject.getWindow(this); } public void setText(String s) { txt.setText(s); } }

And the HTML/JavaScript I used was:

<html> <body> <script type="text/javascript"> function updateApplet() { document.jsap.setText(document.forms[0].txt1.value); } function updateWebPage(myArg) { document.getElementById("txt1").innerHTML=myArg; } </script> <form> <table border=0 align='center' cellpadding=0 cellspacing=0 > <tr> <td valign='top' width=180> <table> <tr><td style='text-align:center; background-color:#EEEEEE'>Applet</td></tr> <tr><td> <applet code="JSHelloWorld2.class" width="180" height="180" MAYSCRIPT style="border-width:0;" name="jsap" id="jsap"> </applet> </td></tr> </table> </td> <td valign='top' width=180> <table width=180> <tr><td style='text-align:center; background-color:#EEEEEE'>JavaScript</td></tr> <tr><td><textarea style='width:180px; height:150px' name='txt1' id='txt1'>Type and click!</textarea></td></tr> <tr><td align='center'><input type=button value='Update applet' onClick='updateApplet()'></td></tr> </table> </td></tr> </form> </body> </html>

The updateWebPage function is referred to in the Java code, but I had to add the function to the web page, which when it first loads comes up as shown below.

This is very similar to the page used to demonstrate Javascript to Applet Communication except that there is an extra button in the applet. Clicking the button on the page has the same effect as before. The text in the page text box is copied across to the applet. But now if you click the button on the Applet, the text from there is copied across to the box on the page (see below).

I could only get it to work once for every page load, but the fact that it works at all is a major step forward for me. I have at last succeeded in calling a JavaScript function from within an applet.

My next step is to learn how to combine JavaScript with PHP to add lines to my database. Build this into a function, and I should be able to call it from within my own applet.

LiveConnect and JavaScript Wrappers

2011-09-05T21:45:00.001-07:00

From the LiveConnect page on the Mozilla web site:

LiveConnect is the name of an application programming interface that provides JavaScript with the ability to call methods of Java classes and vice-versa using the existing Java infrastructure.

LiveConnect use by applets is enabled via the use of the "MAYSCRIPT" attribute in applet tags on an HTML page, following which the applet may refer to classes in the netscape.javascript package to access Javascript objects, and scripts may directly call applet methods (using the syntax document.applets.name.methodName()).

From the LiveConnect Overview page on the same site:

In JavaScript, a wrapper is an object of the target language data type that encloses an object of the source language. When programming in JavaScript, you can use a wrapper object to access methods and fields of the Java object; calling a method or accessing a property on the wrapper results in a call on the Java object. On the Java side, JavaScript objects are wrapped in an instance of the class netscape.javascript.JSObject and passed to Java.

When a JavaScript object is sent to Java, the runtime engine creates a Java wrapper of type JSObject; when a JSObject is sent from Java to JavaScript, the runtime engine unwraps it to its original JavaScript object type. The JSObject class provides an interface for invoking JavaScript methods and examining JavaScript properties.

The LiveConnect page has a number of dead links, including one called "LiveConnect Testcases", which sounded interesting, and another called "Core JavaScript 1.5 Reference: LiveConnect". I found a current JavaScript Reference page, but I could find no reference to LiveConnect on the page.

Javascript to Applet Communication - Example 2

2011-09-04T21:25:00.000-07:00

I have found another article on this topic where the code works and I shall cite it here because the Java code is simpler and therefore perhaps more elegant than the previous one.

import javax.swing.*; import netscape.javascript.JSObject; public class JSHelloWorld extends JApplet { JTextArea txt = new JTextArea(100,100); public JSHelloWorld() { txt.setText("Hello World"); getContentPane().add(txt); } public void setText(String s) { txt.setText(s); } }

The HTML, on the other hand is a bit more wordy, because the Applet and the JavaScript form are laid out side by side, but I like it:

<html> <body> <script type="text/javascript"> function updateApplet() { document.jsap.setText(document.forms[0].txt1.value); } </script> <form> <table border=0 align='center' cellpadding=0 cellspacing=0 > <tr><td valign='top' width=180> <table> <tr><td style='text-align:center; background-color:#EEEEEE'>Applet</td></tr> <tr><td><applet code="JSHelloWorld.class" width="180" height="180" MAYSCRIPT style="border-width:0;" name="jsap" id="jsap"> </applet> </td></tr></table> </td><td valign='top' width=180> <table width=180> <tr><td style='text-align:center; background-color:#EEEEEE'>JavaScript</td></tr> <tr><td><textarea style='width:180px; height:150px' name='txt1'>Type and click!</textarea></td></tr> <tr><td align='center'><input type=button value='Update applet' onClick='updateApplet()'></td></tr> </table> </td></tr> </form> </body> </html>

This comes up as shown below.

You can then type something into the box on the form, or you can leave it as it is and click the button. It then becomes as shown below.

Full original versions of the code can be downloaded from this page.

Java Applet/Javascript Communication

2011-09-02T12:43:00.000-07:00

So I have established that for the last decade, not many people have used Applets in web pages. The resulting paucity tutorials or recent examples made it very difficult even to scratch the surface of how to do this. I know when I write notes here, I often skip steps, which seem too obvious or easy, so that when I revisit an entry written two years ago, even I find it hard to follow. So I can't criticize others for being cryptic, or telling only half the story. But it has been frustrating wading through articles with gaps, or code that doesn't work.

But where to begin with an attempt at a full story? I think a revisit to paths and class paths.

In order to compile and run java classes, you need to set a path to the JDK binaries folder. This can be done once at the start of a command window session, or even as a Windows environment variable.

In order to run classes which connect to a database, you need set a "class" path to a special .jar package containing classes pertaining to communication with the database, every time you run the class.

Java Applet/Javascript Communication requires yet another variant. In order to compile a class which will communicate with Javascript, you have to set a class path, every time you compile the class, to a .jar package with the all-informative name of plugin.jar. This contains the classes needed for an applet to call javascript functions. It can be found in /lib directory of the JRE installation (e.g. C:\Program Files\Java\jre7\lib). The command to compile the class myClass then becomes:

javac -classpath "C:\Program Files\Java\jre7\lib\plugin.jar" myClass.java

I wasted a lot of time adding plugin.jar to the JDK binaries folder, and to my code folder, and couldn't think why sample code cut and pasted from web pages wouldn't compile. It doesn't matter where the .jar file is, as long as a path to it is included with every compile command.

The second step included in all the articles on java applet to javascript communication is to add the import:

import netscape.javascript.*;

If you don't include a class path to plugin.jar in your compile command, this line will generate the first of many compile errors.

The third step is to write some code in the java applet which actually works. I hunted and hunted for this. The best example I found was on this web page. I should like give them credit for showing me a solution, but I have modified the code to look more like my earlier Hello World example. The applet code is:

import java.applet.*; import java.awt.*; import java.awt.event.*; import netscape.javascript.*; public class HelloWorldB extends Applet { String text="Hello world!"; JSObject win, doc, form, textField; public void init() { win = JSObject.getWindow(this); doc = (JSObject) win.getMember("document"); form = (JSObject) doc.getMember("textForm"); textField = (JSObject) form.getMember("textField"); setLayout(new BorderLayout()); Panel buttons = new Panel(); Button displayTextButton = new Button("Display Text"); displayTextButton.addActionListener(new ButtonEventHandler()); buttons.add(displayTextButton); add("South",buttons); } public void paint(Graphics g) { g.drawString(text,30,30); } class ButtonEventHandler implements ActionListener { public void actionPerformed(ActionEvent e){ String s = e.getActionCommand(); if("Display Text".equals(s)) { text= (String) textField.getMember("value"); repaint(); } } } }

The fourth step included in the articles on java applet to javascript communication is to add "MAYSCRIPT" to the Applet tag.

The web code is then:

<html> <body> <applet code="HelloWorldB.class" WIDTH=200 HEIGHT=70 MAYSCRIPT> </applet> <form NAME="textForm"> <P>Enter text and click button:<br> <INPUT TYPE="text" NAME="textField" SIZE="20"></P> </FORM> </body> </html>

This comes up as shown below:

Then you add text as shown below:

And when you click the button it come up as:

This example takes information from a web page and displays it in an applet. I am actually hoping to do the reverse, but it is a step in the right direction.

An Historical History of Java Applets

2011-08-31T06:46:00.000-07:00

I have stumbled across an eleven year old (2000 vintage) article, which already describes Applets as if they were "has been" technology. I quote:

SUN Microsystems introduced Java applets with much fanfare back in 1995. Applets immediately took the web world by storm because they added the ability to display dynamic web content within browsers in what was essentially a static HTML world.

During those initial days, it appeared that using Java applets was the best way to add dynamic content to web pages [but when] Dynamic HTML finally started taking shape, things changed drastically. The Document Object Model (DOM) exposes elements within a web page as programmable components with their own set of properties and methods ... Applets suddenly started to look old and primitive. The W3C’s endorsement of Dynamic HTML finally set the tone for the new breed of sophisticated, dynamic web pages.

Oh dear!

The author does mention some advantages to Applets. I shall cite just a couple, relevant to me:

The JDK comes with many useful classes that are typically found only in a high-level class library.
Applets can communicate back to the web server for sending customized messages, uploading / downloading [data].

The shuffling function I use in my Applet. I'm sure that would not be available in JavaScript. And there are other fairly complex calculations I intend to build in, which I am sure would be better run in compiled code than in a script.

And Applets are supposed to communicate with a server, which is what I am trying to do.

Yet almost all the material at least on the top page of a web search is as old as the article I am citing today.

Java Applet Revision

2011-08-30T19:32:00.000-07:00

I first posted an entry on this topic back in February 2009. To refresh my memory, I'll just run through the steps of adding a very simple applet to a web page again. The code for the simple applet is as follows:

import javax.swing.JApplet; import java.awt.Graphics; public class HelloWorld extends JApplet { public void paint(Graphics g) { g.drawRect(0, 0, getSize().width - 1, getSize().height - 1); g.drawString("Hello world!", 5, 15); } }

This was borrowed from the old (pre-Oracle) Java tutorial. A slightly more sophisticated version is offered in the current Java Applet tutorial.

The code to include the applet in a web page might be:

And the page might look like this.

Asynchronous JavaScript and XML (AJAX)

2011-08-29T23:12:00.000-07:00

I am looking at the W3Schools tutorial on AJAX. The tutorial begins with the statements:

AJAX is not a new programming language, but a new way to use existing standards. AJAX is the art of exchanging data with a server, and update parts of a web page - without reloading the whole page.

It is the exchanging data with a server phrase which interests me.

AJAX was made popular in 2005 by Google, with Google Suggest. Google Suggest [uses] AJAX to create a very dynamic web interface: When you start typing in Google's search box, a JavaScript sends the letters off to a server and the server returns a list of suggestions.

I used the same concept in the 90's in a VB application I created for the WA Trotting Association to help them maintain a database of competition entrants. So that idea is not new, but I guess doing it on a web page must be.

Apparently modern browsers (IE7+, Firefox, Chrome, Safari, and Opera) support the keystone of AJAX, the XMLHttpRequest object. Earlier versions of Internet Explorer (IE5 and IE6) use the MS ActiveXObject. The code examples in the tutorial allow for older IE versions, but in the example quoted below, I have edited this out for simplicity of analysis.

<html> <head> <script type="text/javascript"> function loadXMLDoc() { var xmlhttp; xmlhttp=new XMLHttpRequest(); xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("myDiv").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","ajax_info.txt",true); xmlhttp.send(); } </script> </head> <body> <div id="myDiv"><h2>Let AJAX change this text</h2></div> <button type="button" onclick="loadXMLDoc()">Change Content</button> </body> </html>

The HTML code above sets out a web page comprising a header and a button (see below).

The header is enclosed within the <div> tag. A <div> tag usually divides a big web page into sections. It seems a bit redundant here, so I tried removing it, and labeling the header instead:

<h2 id="myDiv">Let AJAX change this text</h2>

It still worked, but the text, invoked by the button click, all appeared in the header style.

The JavaScript is all contained in the function loadXMLDoc, which is called by the button click event.

Within the function, the variable xmlhttp is declared and set as a new XMLHttpRequest object. I'd love to find this XMLHttpRequest object in a reference library, but I can't find it on the W3Schools JavaScript/DOM reference page. So from the tutorial I learn of two methods: open() and send(). The open() method seems to require the following parameters:

method - the type of request: GET or POST
url - the location of the file on the server
async - true (asynchronous) or false (synchronous)

The send() method allows the following parameter:

string: Used for POST requests

In the code above, the open() method is used with

method = "GET",
url = "ajax_info.txt" and
async = true.

The text contained in the file ajax_info.txt is:

<p>AJAX is not a new programming language.</p> <p>AJAX is a technique for creating fast and dynamic web pages.</p>

Now from the code, it seems the XMLHttpRequest object has at least 3 properties:

.readyState
.status
.responseText

Just by reading the code it is not clear whether .responseText is populated by .open or .send. Intuitive logic would suggest the former, but in that case it is not clear what .send doing in there as well, although in the tutorial it says:

To send a request to a server, we use the open() and send() methods of the XMLHttpRequest object

And it shows the two methods being used one after the other, as they are in the code. The lesson then goes on to say a little more about the .responseText property and one more:

.responseText - holds the response data as a string
.responseXML - holds the response data as a XML data

It gives a further example using XML, which I shall skip at this stage, because on the next page we get clarification on the .readyState and .status properties, together with one I had failed to notice, .onreadystatechange. The last one appears to be both a property and an event:

The onreadystatechange event is triggered every time the readyState changes.
The onreadystatechange property stores a function (or the name of a function) to be called automatically each time the readyState property changes.
The readyState property holds the status of the XMLHttpRequest.

I have to say this leaves a question mark over what the .status property holds - a question not answered by the tutorial. What it does give is a list of acceptable values for these properties. For .readyState these are:

0: request not initialized
1: server connection established
2: request received
3: processing request
4: request finished and response is ready

For .status these are:

200: "OK"
404: Page not found

By my calculation the property changes four times with every server request, so the onreadystatechange event must be triggered four times. I guess that explains the conditional clause in the function:

xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("myDiv").innerHTML=xmlhttp.responseText; } }

This function is called four times, but it only does anything on the fourth occasion, when "the request finished and response is ready" and the .status property is "OK". (And on closer reading this is mentioned in the tutorial.)

At first I was confused by the above code segment, because I didn't realize it is an unnamed function within the function loadXMLDoc() and is called by a different event. The loadXMLDoc() function is called be the button click event, while the unnamed function is both contained by the onreadystatechange property and called by the onreadystatechange event.

So when the button is clicked, everything inside the tag labeled "myDiv" is replaced with the text in the file. In its original version this appears as regular text (see below).

In my alternative version both paragraphs appear as headers because they both fall within the header tag (see below).

If the button is clicked again, nothing appears to happen, because the text is replaced by identical text. Again, I was initially confused here, because I thought the conditional clause had something to do with what was being displayed.

Anyway, this is all a bit disappointing, because I also misunderstood the exchanging data with a server phrase to mean exchanging data with a database on a server, not a measly old text file.

Document Object Model (DOM)

2011-08-26T02:04:00.000-07:00

I remember once attending a seminar by Microsoft where they were describing themselves as the cleverest people in the world for inventing what they called the Component Object Model (COM). It was only years later that I learned that they didn't really invent the idea at all, but copied it from other object oriented programming languages.

Anyway, the Document Object Model (DOM), although it rhymes with COM, has nothing to do with Microsoft, but is a World Wide Web Consortium (W3C) standard defined as:

A platform and language-neutral interface that allows programs and scripts to dynamically access and update the content, structure, and style of a document.

According to the W3Schools tutorial on DOM:

The DOM is separated into 3 different parts / levels:

Core DOM - standard model for any structured document
XML DOM - standard model for XML documents
HTML DOM - standard model for HTML documents

The DOM defines the objects and properties of all document elements, and the methods (interface) to access them. [It] is a standard for how to get, change, add, or delete [XML/HTML] elements.

According to the tutorial:

DOM views an HTML document as a tree-structure ... called a node-tree.

And it goes on to show a diagram of a node tree. I'm not sure how useful this form of representation is for me, but I'll show it below for completeness:

Essentially, the DOM breaks web documents into a set of objects, which it calls nodes, and these nodes have properties which can be read or acted upon by methods. The Tutorial provides a link to a reference page listing DOM objects and their properties, along with JavaScript objects and Browser objects.

The Tutorial gives a "hello world" example, which to me illustrates the extraordinary messiness of this language/methodology. Before citing the example, I shall mention that two of the objects listed on the reference page are the document object and the HTMLElement object. One of the methods listed for the is the getElementById() method. One of the properties listed for the HTMLElement object is .innerHTML, which in any other language would probably be .text. Another is .id. So now here is the code:

<html> <body> <p id="intro">Hello World!</p> <script type="text/javascript"> txt=document.getElementById("intro").innerHTML; document.write("<p>The intro paragraph text is: " + txt + "</p>"); </script> </body> </html>

So the first paragraph is assigned the id "intro" by the HTML. Then in the JavaScript this HTMLElement object is referred to by its id, and its innerHTML property is assigned to the JavaScript variable txt. The content of this variable is then concatenated with the text of a second paragraph created with the JavaScript document.write method. The result is shown in the image below:

That seems a lot of work to achieve something fairly trivial, but at least now I have some understanding of what DOM is all about. It is essentially a labeling system for HTML elements, which enables them to be changed programmatically (usually with JavaScript). In fact it is really just an extension of JavaScript to embrace HTML elements and their properties.

eXtensible Markup Language (XML)

2011-08-24T04:57:00.000-07:00

I am looking at the W3Schools tutorial on XML. I like the clarification on the roles of HTML and XML:

HTML was designed to display data, with focus on how data looks.
HTML truncates multiple white-space characters to one single white-space.
XML was designed to transport and store data, with focus on what data is.
White-space is Preserved in XML. XML Stores New Line as LF.
XML tags are not predefined. You must define your own tags.
XML documents must include an XML declaration and can include a DOCTYPE declaration, which in turn refers to "system" of tags/elements, set out in either a DTD file or in a Schema.
XML does not DO anything other than to transport and store data.
XML data is stored in plain text format. This provides a software and hardware independent way of storing data.
XML documents must contain a root element. This element is "the parent" of all other elements. All elements can have sub elements (child elements).

The tutorial gives an example:

<?xml version="1.0" encoding="ISO-8859-1"?> <bookstore> <book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price> </book> <book category="CHILDREN"> <title lang="en">Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> <book category="WEB"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price> </book> </bookstore>

Here first line is the XML declaration and the root element is <bookstore>. The <bookstore> contains 3 <book> elements, each of which had a lang attribute, and in turn has 4 child elements. It seems to me analogous to a data table entitled "bookstore", with 6 columns and 3 rows. I say 6 columns, because in a data table, the lang attribute and the book category would be stored as columns, possibly integer indices linked to other tables.

My interpretation is confirmed in a section entitled Elements vs. Attributes, where the following examples are said to be equivalent:

<person sex="female"> <firstname>Anna</firstname> <lastname>Smith</lastname> </person> <person> <sex>female</sex> <firstname>Anna</firstname> <lastname>Smith</lastname> </person>

Indeed the tutorial goes on to say:

There are no rules about when to use attributes or when to use elements [but while] attributes are handy in HTML. In XML my advice is to avoid them. Use elements instead.

My first impression is that this is not an "efficient" way to store data, not least because the row "headers" have to be repeated in every row. But then HTML tables are messy to look at (in HTML code) and nightmarish to produce (manually).

Interestingly, although XML does not render in a browser in the same way as HTML, it does come up a bit like code in a code editor (see below). So "parent" elements can be expanded or contracted to show or hide their "child" elements. In the image below, the "cooking" book element has been expanded, but the other two book elements left closed.

It's interesting, but I am still no closer to connecting my Applet to a database.

JavaScript by w3schools

2011-08-22T20:39:00.000-07:00

I was planning to slot in an entry on XML here, but I note the W3Schools tutorial on XML puts JavaScript as required knowledge. As I branched into web language revision because HTML and XHTML were required knowledge for JavaScript, I might as well start on that now.

I have to say that having praised the W3Schools tutorial on PHP, I am a little disappointed by this one. It is all over the place. I came here because the Mozilla Getting Started with JavaScript page began with a "corny" example but zero explanation of the language structure or syntax. And now this one does the same thing:

<html> <head> <script type="text/javascript"> function displayDate() { document.getElementById("demo").innerHTML=Date(); } </script> </head> <body> <h1>My First Web Page</h1> <p id="demo">This is a paragraph.</p> <button type="button" onclick="displayDate()">Display Date</button> </body> </html>

It's rubbish without a proper explanation. If all I want to do is reverse engineer someone else's code, I won't bother with a tutorial, I'll just search for code examples.

So what do we have in the next few pages that is useful?

To insert a JavaScript into an HTML page, use the <script> tag. Inside the <script> tag use the type attribute to define the scripting language.

This is shown in line 3 of the code quoted above.

The lines between the <script> and </script> contain the JavaScript and are executed by the browser. JavaScripts can be put in the <body> and in the <head> sections of an HTML page. JavaScripts in an HTML page will be executed when the page loads. This is not always what we want. Sometimes we want to execute a JavaScript when an event occurs, such as when a user clicks a button. When this is the case we can put the script inside a function [which is called by the event].

This strategy is executed in line 4 of the code quoted above, where the function displayDate is defined, and in the third from last line, where a button click is set to call the function.

You can place an unlimited number of scripts in your document, and you can have scripts in both the body and the head section at the same time. It is a common practice to put all functions [either] in the head section, or at the bottom of the page. This way they are all in one place and do not interfere with page content.

JavaScript can also be placed in external files. External JavaScript files often contain code to be used on several different web pages. External JavaScript files have the file extension .js. To use an external script, point to the .js file in the "src" attribute of the <script> tag:

For example:

<head> <script type="text/javascript" src="xxx.js"></script> </head>

I thought they might give an example of a .js document, but they did not.

Let's hope the XML lesson is more interesting.

Web language Revision

2011-08-21T17:23:00.000-07:00

I'm probably stretching the definition of revision here. My only formal tuition in web languages was in HyperText Markup Language (HTML)(1 - except there was no number then) and it wasn't very formal. From memory it was a free one hour lecture provided by UWA to encourage research students to use the new medium to publicize their work.

Anyway, disappointed by the Mozilla Getting Started with JavaScript page, I went back to a Google search on "JavaScript Tutorial" and W3Schools was on top of the list again. Curious, I checked Wikipedia to find:

W3Schools is not affiliated in any way with the World Wide Web Consortium (W3C). It is created and owned by Refsnes Data, a Norwegian family-owned software development and consulting company.

Affiliated with W3C or not, I liked the style of their PHP tutorial, so I checked out their JavaScript tutorial. Listed as required knowledge was HTML and CSS, and a link was provided to their Home Page, in case of any doubt. I followed the link, because the initials CSS meant nothing to me. And listed with CSS were the languages of the web - HTML, and XHTML. So I decided the time had come for a refresher and catch up on what has happened in the last fifteen years.

I noted a few more formatting tags than were available in the olden days - whether one really needs them all or not is another question. Styles are completely new to me, and it looks as if I have been doing a few things wrong, or at least, not consistently with current standards. But at least I have a jogged a vague recollection that CSS stands for Cascading Style Sheets. I also noted on the images page that the language and syntax of what used to be the ismap attribute has changed a lot. It's not something I use much, but I now have an easy link to the correct form if I need it.

Forms came in well after my time. I've cribbed other people's code, but it's nice to see it all laid out properly. Frames came a little after my time (HTML2 I think) and I never liked them. I was pleased to read that they are being phased out and will not be supported in future HTML versions. The Iframe is completely new to me. Defined as a web page within a web page, I can see there might be applications for it.

When I was a boy, you began an HTML document with the HTML tag, and that told the browser what type of document it was. Nowadays, apparently, you need a document type declaration as well. In practice you can get away without it with ordinary HTML documents (none of the pages on my web site use it), but perhaps you need it for other document types, such as XHTML.

This brings me to the tutorial on EXtensible HyperText Markup Language (XHTML), which I am surprised to learn is simply a stricter and cleaner version of HTML:

XHTML consists of all the elements in HTML 4.01, combined with the strict syntax of XML.

XHTML elements must be properly nested
XHTML elements must always be closed
XHTML elements must be in lowercase
XHTML documents must have one root element

An XHTML document must have a DOCTYPE declaration. The html, head, title, and body elements must also be present.

Next came the tutorial on the search term which led me into this - CSS. To be honest, now that I have confirmed what the initials stand for, I can't see that it will be of much use to me. For my purposes, the "styles" defined in HTML(1) - H1, H2 etc - were more than enough for me. I'm not looking to edit a magazine here; I just want a page to host my Applet. Just in case, a style sheet is referenced inside the header as follows:

<head> <link rel="stylesheet" type="text/css" href="mystyle.css" /> </head>

And the style sheet itself is simply a text document saved with the .css extension and written out something like this:

hr {color:sienna;} p {margin-left:20px;} body {background-image:url("images/back40.gif");}

And the "cascading" works as follows:

Generally speaking we can say that all the styles will "cascade" into a new "virtual" style sheet by the following rules, where number four has the highest priority:

Browser default
External style sheet
Internal style sheet (in the head section)
Inline style (inside an HTML element)

So, an inline style (inside an HTML element) has the highest priority, which means that it will override a style defined inside the <head> tag, or in an external style sheet, or in a browser (a default value).

The tutorial goes on and on, but I think I have what I need for now. And that's it for what W3Schools calls the HTML group of tutorials. When I saw XHTML in there I was hoping it might be just a trendy way of saying XML, but I see now that it is part of a whole group on its own, so it is probably worthy of its own blog entry.

Mozilla JavaScript Learning Material

2011-08-17T22:45:00.000-07:00

A Google search on "JavaScript Tutorial" brings up an abundance of online learning materials. I added Mozilla to the search string to find their Getting Started with JavaScript page.

I can't resist commenting on the style of the page. There are comments like:

JavaScript is a very easy language to start programming with. All you need is a text editor and a web browser to get started.

And:

JavaScript is a great programming language for introductory computer languages. It allows instant feedback to the new student and teaches them about tools they will likely find useful in their real life. This is in stark contrast to C, C++ and Java which are really only useful for dedicated software developers.

And then the first code example is:

<span onclick="alert ('Hello World!');">Click Here</span>

With zero explanation besides a list of mouse "events" other than onclick. To be fair, the code works. When slotted into an empty browser page, it comes up as shown below:

and if you click over the text it comes up as shown below:

The second code example is a more complex and wordy way of achieving the same thing:

<script type="text/javascript"> function onclick_callback () { alert ("Hello, World!"); } </script> <span onclick="onclick_callback();">Click Here</span>

Slotting that into an empty web page achieves exactly the same as shown in the illustrations above.

The third code example shows an even more complex way of achieving almost the same thing:

<script type="text/javascript"> function onclick_callback(event) { var eType = event.type; /* the following is for compatability */ /* Moz populates the target property of the event object */ /* IE populates the srcElement property */ var eTarget = event.target || event.srcElement; alert ("Captured Event (type="+ eType +", target="+ eTarget); } </script> <span onclick="onclick_callback(event);">Click Here</span>

This shows the same text as before and the following message:

The fourth code example introduces still more complexity, and this time the message box (shown below) is invoked by a larger range of possible mouse events:

This is great, but it's not really what I'm looking for.

Playing with PHP

2011-08-14T04:17:00.000-07:00

I'm not sure who w3schools are, but they have a tutorial on PHP. They offer a list of things you need, including MySQL, Apache and PHP. I'm going to ignore this, because I am tired of getting things to work at home and then scratching my head to make them work on a production server.

I do note however that my web host claims to offer MySQL, Apache and PHP on its server. I shall therefore do all of my experimenting in a live environment. Unprofessional perhaps, but how many people look at my web site anyway?

The first code sample is a good old "Hello World" message:

<html> <body> <?php echo "Hello World"; ?> </body> </html>

I popped this in a text file, called it hello.php, and uploaded it to my web host. I then typed:

http://www.interactived.com/hello.php

into my browser, and what do you know, the following page appeared:

So far so good.

There followed a number of boring chapters on linguistics, most of which I tried out but will omit here. The next more interesting one was an example of a form:

<html> <body> <form action="welcome.php" method="post"> <p align=right> Name: <input type="text" name="fname" /><br> Age: <input type="text" name="age" /><br> <input type="submit" /> </p> </form> </body> </html>

I saved this as hello.htm, uploaded it and typed:

http://www.interactived.com/hello.htm

into my browser. A nice neat form appeared on the screen (see below) and I typed Jonathan into the name field and 24 into the age field.

Before clicking the Submit button, I also saved the following code as welcome.php and uploaded it:

<html> <body> Welcome <?php echo $_POST["fname"]; ?>!<br /> You are <?php echo $_POST["age"]; ?> years old. </body> </html>

Then, when I clicked the Submit button, the following text appeared in the browser:

Most interestingly (for me at least), when I tried to view the source code, it came up as shown below.

So the source code displayed was the rendered HTML, and NOT the PHP source code. This is quite good news, although I am still not quite sure how secure that PHP file is.

After skipping more linguistics chapters the time came to try a data connection. As I had deleted nearly everything from my table in a previous exercise, I decided to try an insert:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("dbAMJ", $con); mysql_query("INSERT INTO Items (Partid, OpCode, Itemdet, Raw, Rate) VALUES (1, 1, '2+2=', 1, 1), (1, 1, '3+3=', 1, 1)"); mysql_close($con); ?>

I substituted my credentials, saved it as dbtestin.php, uploaded it and tried it in the browser. That code displays nothing on success (or failure of the sql query), so I visited my web host again and used their phpMyAdmin facility to check that the extra lines had been inserted, and sure enough they had.

I then tried the following code to apply a simple query:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("dbAMJ", $con); $result = mysql_query("SELECT * FROM Items") or die(mysql_error()); echo "<table border='1'> <tr> <th>Itemdet</th> <th>Raw</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['Itemdet'] . "</td>"; echo "<td>" . $row['Raw'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?>

The following table was displayed:

So it seems that I can run PHP on my live web site, and I can use it to connect to my database. Furthermore, the PHP source code does not appear to be accessible for viewing in the browser. This would appear to be a step in the right direction.

J2EE

2011-08-13T04:26:00.000-07:00

Three years ago I began this blog with a discussion of The Java Tutorial. I didn't read it from top to bottom. I took what I needed to do what I wanted to do. I left some quite large chunks unread, so when I decided to research Java Servlets, I fully expected to find them there.

Certainly they were part of a tutorial, but it had an unfamiliar feel to it. When I clicked on the home link, I found myself looking at the J2EE 1.3 Tutorial - "A beginner's guide to developing enterprise applications on the Java 2 Platform, Enterprise Edition SDK version 1.3".

"Beginners guide" sounds good, but "developing enterprise applications" is something I neither need nor want to do.

In my first post I suggested that using NetBeans to craft "Hello World" was like using a sledge hammer to crack a nut, and at first glance, using J2EE to make a simple connection between my little applet and a database is worthy of the same analogy. There has to be a simpler way to do it.

During my VB programming days I used ASP, the MS equivalent of JSP, and it really wasn't that hard. In those days, I owned the server, and controlled everything on it, including user accounts and passwords. From memory, the user had to supply credentials every time they accessed the intranet site, so the issue of embedding credentials in code didn't arise.

I have just touched base with my web host, and from my account stats, as well as a MySQL version number, I see one for Apache and PHP. I guess that means I can use PHP. I've never done so before, but from the w3schools site, connecting to a MySQL database doesn't look very hard.

A couple of questions arise. First where is the PHP code hidden, and is that a better place to hide my username and password than an Applet? Second, can my Applet talk to the PHP code and vice versa? I suspect the answer to both questions is NO, but at least reading up on PHP looks a little less daunting than J2EE.

A few notes on JavaScript

2011-08-11T21:35:00.000-07:00

I am one the 99% of the world who has long nursed the misconception that JavaScript has something to do with Java. And why not? The name sort of implies it.

It was only after I searched without success on the Java Tutorial site for some mention of JavaScript that I stumbled across the knowledge that JavaScript was in fact developed by Netscape, under the name LiveScript. Armed with that knowledge, it is not surprising to find the best reference information on Mozilla site.

According to Wikipedia, the nonsensical name choice had something to do with Netscape wanting to cash in on the popularity and cachet of Java, and Sun wanting support for Java technology in the then popular Netscape browser.

For a while only the Netscape family of browsers supported JavaScript. Microsoft later produced what it calls JScript to run in its browser. JScript is similar but not identical to Javascript.

According to Foldoc JavaScript runs a hundred times slower than C, and ten times slower than Java.

According to Wikipedia:

JavaScript very quickly gained widespread success as a client-side scripting language for web pages [and] has become one of the most popular programming languages on the web. Initially ... many professional programmers denigrated the language because its target audience was web authors and other such "amateurs" ... The advent of Ajax returned JavaScript to the spotlight and brought more professional programming attention.

So what is Ajax? The link is to another Wiki page, which gives lots of details, but I'll quote a bit of the history, as it mentions Applets:

In the 1990s, most web sites were based on complete HTML pages; each user action required that the page be re-loaded from the server (or a new page loaded) ... Asynchronous loading of content first became practical when Java applets were introduced in the first version of the Java language in 1995. These allow compiled client-side code to load data asynchronously from the web server after a web page is loaded.

But as I have reported in previous posts, Applets are fraught with difficulties. This brings me back to the definition of Ajax, which is an acronym for "asynchronous JavaScript and XML"

In summary, JavaScript is a scripting language used in web pages in conjunction with other languages and technologies to achieve ends more complex than can be achieved with simple HTML. The language looks a bit like Java (and C) and meshes with Java, but it was developed independently by the creators of the Netscape browser.

Forum Comments on Java Applets

2011-08-06T08:22:00.000-07:00

It is so long since I last posted on the once was Sun/Java now is Oracle developer forum my account seems to have been ditched. While I wait to have it resurrected I've done a little searching in the Java Database Connectivity (JDBC) forum using the search term "Applet".

My first search, using the default 3 month period, yielded nothing. No one had posted anything about or even mentioned Applets in the last three months. So either they are very easy to use and work very well, so hardly anyone has any problems with them, or maybe ... not very many people use them at all.

My stomach was beginning to churn a bit, when I extended the search period to the current year - and with good reason. Somebody, a little bit like me I think, had written a game in an applet, and wanted to record top scores in a database. He described his connection problem in this thread, and the first reply began:

Doing JDBC calls from an Applet is such a bad idea that I am glad you are having trouble with it. You might want to reconsider your design! If you persist on going this way, I can tell you that there will be many more even more difficult hurdles that you will need to overcome which would make this problem seem like a piece of cake ...

The OP opened his question with the phrase:

I'm trying to write a very basic test applet ...

One reply to this specific phrase was:

You may as well be hunting unicorns. Applets are never easy.

Another reply, to the post as a whole began:

Apart from the bad idea of connecting to your db from an applet (security is one that springs to mind ...

and concluded his reply with:

...Exactly how this wires up for an applet I can't say since I've not written one in a decade.

So perhaps 10 years ago Applets were "hip" for whatever reason, but now they seem definitely not to be.

Another thread actually opened with a quote from another website, inviting comments and clarification. The quote was:

It is generally not a good idea to use JDBC directly in an Applet. You need to presume some brat will decompile your Applet and use that knowledge to create a substitute Applet that causes as much havoc as possible. If your Applet has direct access to JDBC, the brat’s substitute can snoop or pillage the database to its heart’s content.

Most of the posts are pretty uncomplimentary about Applets and the ODBC-JDBC bridge (which I looked at but gave up on because it was too hard to get it to work), but one of them suggests a solution using servlets:

What you could do is write two servlets. One is called "gimmethescores". The applet calls http://www.example.com/gimmethescores and gets a list of high scores to display. You can use a simple line by line text format, or XML, or whatever you wish.

The other servlet is "setscore". After the game is over, the applet calls http://www.example.com/setscore?user=jim&score=1050. The servlet then checks the parameters look sane and updates the high score table.

So now I'm thinking I need to study up on servlets.

Webcity

2011-08-04T06:20:00.000-07:00

My web site is hosted by WebCity, an Australian domain registrar and web host, whose motto is "Self Service Savings". And when they say self service they mean self service. They offer a very comprehensive self help library, supplemented by a range of good video tutorials, but the whole site is somewhat cryptic, if you don't know your way around, and they don't really like being contacted.

However, they are also true to their word on savings. Certainly they saved me money on my original domain registrar and on my traditional ISP. The ISP offered free web space, but charged a ridiculous annual fee to domain holders. This didn't include domain registration, which I paid for separately. I guess it covered the one off three minute task of letting the domain registrar know my physical web address, but apart from that, I have no idea how they justified it.

Webcity also offer a MySQL server, and when I plucked courage to ask them about it they assured me that it was on the same server as my web code, and that the server name "localhost" should be used for connections. That sounded pretty good news. It might be an unusual set up, but it gives me a chance to try out my existing Applet code on a commercial server, before re-writing in a more rigorous or generalist way. I therefore set about creating a database on their server.

I mentioned that the Webcity web site is a bit cryptic. Most websites offer on their home page a bold login button, or clearly labeled user id and password fields. Not so this one. You have to know to type "yourdomain/cPanel" into the address field of your browser, and then you will be offered user id and password fields to fill in.

The cPanel, as the name suggests, is a bit like the Control Panel in Windows, with an array of icons allowing you to tweak this and that. They are arranged into groups such as mail files, logs etc. Well down the page is a group called Databases. The first item called MySQL databases offers a GUI to create databases and users. Another item, called phpMyAdmin, offers a GUI to create a table and define fields in one tab, and in another tab it offers an SQL window to type or paste one's own queries. I cut and pasted the one I had used to create the dbAMJ database on my own server. It ran fine, and I added a few lines in the format I intend to use. That was fine, and I deleted a couple as well.

I then modified my applet code to include the following URL for the database:

String url = "jdbc:mysql://localhost:3306/int22853_dbAMJ";

I modified the username and password for Webcity, compiled and uploaded the code files and uploaded the MySQL JDBC driver. I included in the code modifications a few extra diagnostic messages. The applet is designed to display these in a text field.

The applet loaded, and it displayed a message to confirm that the JDBC driver had been loaded. It then displayed an extra message to confirm that it had moved to the next line of code. But nothing came back from the connection "Try" loop. There was no success message and no fail or error message. It was as if it just did not run.

It did this from the website hosted by Webcity, and it did exactly the same on the site hosted on my own IIS server. So after a break of two years, I think I need to post a question in the Java forum.

Limitations of Java Applets

2011-08-03T16:44:00.000-07:00

Having installed IIS, I was still struggling to get my Applet to connect to the MySQL database, and I stumbled across this post. The grammar is questionable but of relevance to me is:

This keeps coming up ... applets trying to make JDBC database connections don't work in general ... Mainly because JDBC is a server side technology. Where applets may only always and forever communicate with the HTTP server ... which they originated from (same server as their code base) ... and in general the database will almost always be on a different server.

In other words, it doesn't matter how hard I work to get the Applet working at home, when I upload to my ISP it probably won't work, because their MySQL server is most likely on a different box to the web server with my code on it. I guess I could send them a question, but if I want to make my code robust, I probably need a more general solution. The post quoted above goes on to suggest solutions, but at this stage of my knowledge development they are all gibberish to me. I'll quote them because they include a nice collection of search strings for possible use in my research:

So, we need some kind of web service or custom query mechanism, e.g. XML RPC over HTTP, or JSON over HTTP ... where we have the APPLET invoke HTTP requests to a Servlet in the server ... which receives these requests (also handles authentication, authorization) and then uses a JDBC connection to the database to invoke some query, unpack the result set into the HTTP servlet response in a suitable format (e.g XML, JSON, etc, what ever was used for request and what ever the applet is expecting as a response).

[This] means you'd need a web app at least with a servlet listening on a URL that your applet is able to invoke, and have the database JDBC connection available to this servlet.

Years ago I posted a question on some Java forum about JDBC and Applets, and I was asked why I was using an Applet and not writing a web application with Java Script. I gave some bullshit answer at the time, but the honest answer would have been that I had never used Java script, and didn't know the first thing about it. I have used Java, I have used VB script, and I have used HTML, so I can't believe it would be that hard; but I guess I need to hit the text books again.

Microsoft Internet Information Services (IIS)

2011-07-31T20:30:00.000-07:00

When I referred to my last post on Microsoft IIS, I was disappointed to find that it was little more than a set of cryptic references to broken links. So in case I ever need to do this again, I shall include more detail this time.

IIS is a Windows component, which by default is not installed on the non-server editions. To install it, you open Windows components (via Add or Remove Programs in XP), and check the IIS box (see above). It really helps if you have the install CD used to install your system, and for it to have the same Service Pack level as your current OS (and with XP this should be SP3).

Once installed you can open IIS via Administrative Tools in the Control Panel (see above).

Expand the local computer node and the websites node to find the default web site (which contains the IIS help files - see above).

Right click over this and, from the context menu, select New and Virtual Directory (see above).

This opens the Virtual directory creation wizard (see above).

Give the virtual directory a name which means something to you. I called mine AMWeb, with AM being short for Active Math (see above).

I then used the Browse button to navigate to a local copy of my web site (see above).

The next page sets the permissions for the web site. I checked everything except write (see above).

After clicking through the final page of the wizard, you can see the IIS layout again with a new node for the web site.

For belt and braces, I right click over the default web site and select properties (see above).

From there I select the Home Directory tab and make the permissions match my web site (see above). I'm not sure you really need to do this, but it makes me feel better.

However, I am sure you need to carry out one more task. Using Windows Explorer navigate to the directory actually holding your web files. Hover over the folder node, right click, and select Sharing and Security (see above).

Check Share this folder on the network (see above).

It was only after doing this that I was actually able to view my web in a browser (see above).

Running Code for a GUI Applet

2011-07-30T05:11:00.000-07:00

On my first attempt at running the modified Applet from within a web page, I was delighted to observe that it worked. I then noticed the following lines of code:

/** * These are implementation variables */ boolean dbDEBUG = false; boolean mathDEBUG = false; boolean LIVE = false;

I had very cunningly (in my opinion) put in a safety check. By default, no attempt is made to attempt to connect to a database, and the applet just runs without it. So I had not been so clever with the coding change as I initially thought. The applet had run, but without attempting to implement the new connection code. So with great trepidation I changed both boolean LIVE and boolean dbDEBUG to true, recompiled, and tried again.

Again to my delight, the applet opened, and there was even a debug message saying:

starting... driver succeeds...

However my joy subsided when I clicked the "Click to begin" button, entered an answer clicked "Check Answer" and the debug field read:

SQLException: nullSQLException: null.

The original text file class, used to create the database in Derby, included some code to populate a few lines. On reflection, I could see why I included this, and that perhaps it would have been a good idea to run it first.

So I modified the class to connect to MySQL, renamed it to ItemdbTest, and compiled it. I also modified another text file/command line class, ItemdbQuery, which runs a simple query and displays the table contents. The SQL for the insert was:

"INSERT INTO Items " + "(Partid, OpCode, Itemdet, Raw, Rate) " + "VALUES (1, 1, '1+1=', 1, 1), " + "(1, 1, '2+2=', 1, 1), " + "(1, 1, '3+3=', 1, 1)";

That ran fine. The SQL for the simple query was:

"SELECT * FROM Items"

and the output was:

try connection connection succeeds try query 1 1 1 1+1= 1 1 2 1 1 2+2= 1 1 3 1 1 3+3= 1 1 query succeeds disconnection succeeds

Now the SQL used here is supposed to reflect that in the Applet, so it was strange that the Applet is falling over, when a similar command from the command line was working.

On further investigation, the answer lay on the What Applets Can and Cannot Do page in the Java Tutorial. I remember when I was first trying to use the Derby embedded driver, an applet can't make changes on the local machine. So I shall have to set up IIS on either this or another machine, set MySQL to accept outside connections, and start again.

Creating a MySQL database for a GUI applet

2011-07-28T05:34:00.000-07:00

Thus far I have been working entirely with what I call text file code, run from the command line, and with nothing to see beyond a few cryptic messages to the screen. The process was interesting because it revealed differences in the required coding modifications extend beyond making the connection and include minor but critical differences in the SQL itself.

The next step is to modify a GUI applet. It is ages since I cobbled this together, so I have no idea where to begin. From memory there is something sitting on a website, which does not connect to a database, and there is a link (deliberately not hyperlinked here) to another applet sitting on my own server. That server has long been consigned to the bin, and by now the disk back up probably also erased.

My habit with code is to store it in many places and to give the locations labels which will have absolutely no meaning to me when I come looking for it later. While I was fumbling around my local hard drive, I suddenly remembered that I have a Java.net project: Rasch-itembank. What a great place to store code!

The database connection was coded as follows:

/** * This method loads the database driver */ public String dataDriv() { try { String ldriver = "org.apache.derby.jdbc.ClientDriver"; Class.forName(ldriver); buffer = "driver succeeds... "; } catch(Exception ex) { buffer = "driver error: " + ex.getMessage(); } return buffer; } /** * This method makes the connection */ public String dataConn() { try { // String strconn = "jdbc:derby://192.168.2.3:1527/dbAMJ"; String strconn = "jdbc:derby://202.65.70.207:1527/dbAMJ"; liveconn = DriverManager.getConnection( strconn ); smt = liveconn.createStatement(); buffer = "connection succeeds... "; } catch(Exception ex) { buffer = "connection error: " + ex.getMessage(); } return buffer; }

Converting for MySQL this will require replacing:

String ldriver = "org.apache.derby.jdbc.ClientDriver";

with

String ldriver = "com.mysql.jdbc.Driver";

and

String strconn = "jdbc:derby://202.65.70.207:1527/dbAMJ"; liveconn = DriverManager.getConnection( strconn );

with

String url = "jdbc:mysql://localhost:3306/dbAMJ";liveconn = DriverManager.getConnection(url,"jhipp", "h1pp0");

Prior running this, dbAMJ needed to be created on the MySQL server, and I needed to look up the SQL I ran to do this. Eventually, I found it, in another small text file class. It was going to require a lot of editing, because it included the "Create=true" switch, which probably doesn't work for MySQL. I therefore opted to adapt the class I had used to create my financial database as follows:

import java.sql.*; public class AMdbCreate { public static void main(String args[]) throws ClassNotFoundException, SQLException { /** * These are shared variable declarations */ Connection connect = null; Statement stat = null; /** * This is the start of the main code. */ System.out.println("try connection"); try { // Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/mysql"; connect = DriverManager.getConnection(url,"root", "my password"); stat = connect.createStatement(); System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * These are the makedatabase lines */ System.out.println("try makedatabase dbAMJ"); try { stat.executeUpdate("CREATE DATABASE dbAMJ"); System.out.println("makedatabase succeeds"); } catch(Exception ex) { System.out.println("makedatabase fails"); System.out.println("CREATE DATABASEdbAMJ"); System.err.println("SQLException: " + ex.getMessage()); } finally { // Close connection if (connect!= null) { try { connect.close(); System.out.println("disconnection succeeds"); } catch (SQLException sqlEx) { System.out.println("disconnection fails"); } } } } }

This ran successfully with the following output:

try connection connection succeeds try makedatabase dbAMJ makedatabase succeeds disconnection succeeds

I then adapted the other two classes I had used to add a user and a table to my financial database. Adding the user was fine, but as I half expected, there was a problem creating the table. The screen output was as follows:

try connection connection succeeds try maketable maketable fails CREATE TABLE Items (Itemid INT GENERATED BY DEFAULT AS IDENTITY, Partid BIGINT, OpCode SMALLINT, Itemdet CHAR(9), Raw SMALLINT, Rate DOUBLE, primary key (Itemid)) SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GENERATED BY DEFAULT AS IDENTITY, Partid BIGINT, OpCode SMALLINT, Itemdet CHAR(9' at line 1

I suspected the problem lay with the "auto-increment" option on the item id. The syntax I used for this had come from the Derby SQL reference manual.

A quick Google search revealed a page in the MySQL online manual, which gave the correct language and syntax for MySQL.

All I had to do was change my Itemid field as follows:

Itemid INT NOT NULL AUTO_INCREMENT,

And the table was created without a problem. I am now ready to try running the applet.

Further Diagnostics with MySQL

2011-07-27T03:56:00.000-07:00

I shall now go back to the diagnostic class (FindbSDiag1 shown below), and bring back some but not all of the commented out code.

My first screen dump was very fussy, but it confirmed that the buy queries were running successfully:

Buy Query = SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'ABS' for update try Buy Query Buy Query succeeds fBCumQ= 0.0 fSQ= 2000.0 Buy Query = SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'TIM' for update try Buy Query Buy Query succeeds fBCumQ= 0.0 fSQ= 7000.0

So I took out the rubbish and was left with a nice neat buy query screen dump, mirroring the one for the sell queries:

SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'ZFX' for update SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'BIL' for update SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'QCH' for update SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'CDO' for update SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'LIP' for update

Thus far then, the code is working correctly.

And it turns out that the next "problem" was a couple of "diagnostic lines" inserted by me at the very start of the diagnostic process.

Having corrected this, the diagnostic program now (in theory) runs exactly as the original was supposed to run, with the few extra comments. As a few changes have now been made to accommodate MySQL, I'll print the whole thing here (c/cf the original FindbQ1 for Derby class shown here):

import java.sql.*; import java.util.*; import java.io.*; public class FindbSDiag1 { public static void main(String args[]) throws ClassNotFoundException, SQLException, IOException { /** * These are shared variable declarations */ Connection connect = null; Statement statS = null; Statement statB = null; Statement statI = null; ResultSet resultBuy = null; String Sidx = new String(); String Bidx = new String(); String strDate = new String(); String strTrType = new String(); String strCode = new String(); String strPrice = new String(); String strTrQ = new String(); String dInsert = new String(); String dDelete = new String(); float fSPrice = 0; float fSQ = 0; float fSProc = 0; float fBCumQ = 0; float fBCurQ = 0; float fBEQ = 0; int iBEQ = 0; float fBPrice = 0; float fBCurCost = 0; float fBCumCost = 0; float fProfit = 0; float fComm = 22; /** * This is the start of the main code. */ System.out.println("try connection"); try { // Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/dbFin"; connect = DriverManager.getConnection(url,"jhipp", "h1pp0"); statS = connect.createStatement(); statB = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); statI = connect.createStatement(); System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * These are optional delete lines */ System.out.println("try delete"); try { statI.executeUpdate("DELETE FROM TransComp"); System.out.println("delete succeeds"); } catch(Exception ex) { System.out.println("delete fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * These are query execution lines */ System.out.println("try sell query loop"); try { String aquery = "SELECT * FROM TransRaw" + " where TrType = 'Sell'" + " order by dDate, TRidx"; ResultSet resultSell = statS.executeQuery(aquery); System.out.println("Sell Query = " + aquery); /** * Display sell results. */ while(resultSell.next()) { Sidx = resultSell.getString(1); // System.out.println("Sidx= " + Sidx); strDate = resultSell.getString(2); strTrType = resultSell.getString(3); strCode = resultSell.getString(4); strPrice = resultSell.getString(5); strTrQ = resultSell.getString(6); fSPrice = resultSell.getFloat(5); fSQ = -resultSell.getFloat(6); fSProc = fSPrice * fSQ; fSProc = fSProc - fComm; dInsert = "INSERT INTO TransComp " + "VALUES (" + Sidx + ", '" + strDate + "', '" + strTrType + "', '" + strCode + "', " + strPrice + ", " + strTrQ + ")"; // System.out.println("Sell insert Query = " + dInsert); // System.out.println(dInsert); /** * These are the insert lines */ // System.out.println("try insert sell"); try { statI.executeUpdate(dInsert); // System.out.println("insert sell succeeds"); } catch(Exception ex) { System.out.println("insert sell fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * Display buy results. */ String bquery = "SELECT * FROM TransRaw" + " where TrType = 'Buy'" + " and StCode = '" + strCode + "' for update"; System.out.println(bquery); // System.out.println("try Buy Query"); try { resultBuy = statB.executeQuery(bquery); // System.out.println("Buy Query succeeds"); } catch(Exception ex) { System.out.println("Buy Query fails"); System.err.println("SQLException: " + ex.getMessage()); } fBCumQ = 0; fBCumCost = 0; fProfit = 0; // System.out.println("fBCumQ= " + fBCumQ); // System.out.println("fSQ= " + fSQ); while(fBCumQ < fSQ){ if(resultBuy.next()) { Bidx = resultBuy.getString(1); System.out.println("Bidx= " + Bidx); strDate = resultBuy.getString(2); strTrType = resultBuy.getString(3); strCode = resultBuy.getString(4); strPrice = resultBuy.getString(5); strTrQ = resultBuy.getString(6); fBPrice = resultBuy.getFloat(5); fBCurQ = resultBuy.getFloat(6); fBCumQ = fBCumQ + fBCurQ; if(fBCumQ > fSQ){ fBEQ = fBCumQ - fSQ; fBCurQ = fBCurQ - fBEQ; fBCumQ = fBCumQ - fBEQ; iBEQ = (int)(fBEQ); resultBuy.updateInt("TrQ", iBEQ); } else { dDelete = "DELETE FROM TransRaw " + "where TRidx = " + Bidx; statI.executeUpdate(dDelete); } fBCurCost = fBCurQ * fBPrice; fBCurCost = fBCurCost + fComm; fBCumCost = fBCumCost + fBCurCost; dInsert = "INSERT INTO TransComp " + "VALUES (" + Sidx + ", '" + strDate + "', '" + strTrType + "', '" + strCode + "', " + strPrice + ", " + fBCurQ + ")"; System.out.println("TransComp insert = " + dInsert); /** * These are the insert lines */ System.out.println("try TransComp insert"); try { statI.executeUpdate(dInsert); System.out.println("TransComp insert succeeds"); } catch(Exception ex) { System.out.println("TransComp insert fails"); System.err.println("SQLException: " + ex.getMessage()); } } } fProfit = fSProc - fBCumCost; System.out.println("Proceeds = " + fSProc); System.out.println("Costs = " + fBCumCost); System.out.println("Profit = " + fProfit); resultBuy.close(); } //end of while(resultSell.next()) resultSell.close(); statS.close(); // statB.close(); statI.close(); System.out.println("sell query loop succeeds"); } catch(Exception ex) { System.out.println("sell query loop fails"); System.err.println("SQLException: " + ex.getMessage()); } finally { // Close connection if (connect!= null) { try { connect.close(); System.out.println("disconnection succeeds"); } catch (SQLException sqlEx) { System.out.println("disconnection fails"); } } } } }

My final task is to modify the FindbQSim2 class for MySQL, and hope that it makes the transition more smoothly than the last one.

And as luck would have it, I simply had to modify the five connection lines and it worked as it should. I can now complete my annual tax return!

The Java classes described in this and the immediately preceding posts are not trivial, but only one of them required a couple of changes in addition to the connection line changes. From this one might deduce that SQL for MySQL is not identical to SQL for Derby, but it seems similar enough to make the transition not insurmountable.

References to the MySQL manual

2011-07-26T03:00:00.000-07:00

The MySQL manual can be downloaded from the MySQL Documentation: MySQL Reference Manuals page of the MySQL web site. A wide range of formats and versions are offered. I chose a PDF file for version 5.5.

My first port of call was Chapter 10 on Data types, and specifically 10.3 on date and time types. I presume I must have done the same for Derby, and used that to guide the design of my text export file. Anyway, it is already obvious that the rules are different for MySQL, and for clarity I quote from the manual:

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere.

I therefore need to change my selections in the MS Access data export wizard advanced options as follows, with the date delimiter changed to "-" and the date order changed to YMD:

and the dataset should now look like this:

1,2006-09-05,Buy,BSL,6.86,200 2,2006-09-12,Buy,TFC,0.38,2500 3,2006-09-12,Buy,ZFX,10.65,100

I now need to re-import the data to the MySQL database. Fortunately my FindbFill import class deletes everything first (to allow for frequent stuff-ups), so the new dataset should be clean. Unfortunately the name of the text file is hard coded, so I do need to go in and change this, before running it. I then run the modified FindbQ1 class to check the modified screen output. For any reader with a very high boredom threshold, these classes are shown in their original form here.

try sell query loop Sell Query = SELECT * FROM TransRaw where TrType = 'Sell' order by dDate, TRidx INSERT INTO TransComp VALUES (420, '2006-09-12', 'Sell', 'ZXF', 10.65, -100) INSERT INTO TransComp VALUES (410, '2006-12-12', 'Sell', 'BIL', 12.44, -80) INSERT INTO TransComp VALUES (418, '2007-08-01', 'Sell', 'QCH', 5.9, -300) INSERT INTO TransComp VALUES (412, '2007-08-15', 'Sell', 'CDO', 6.2, -220) INSERT INTO TransComp VALUES (417, '2007-11-07', 'Sell', 'LIP', 0.95, -1300)

The output, shown above confirms that the dates have now been stored correctly, and the program is looping through the data in the correct order.

Now to address the second problem identified in my last post, from the Derby reference manual:

A SELECT statement consists of a query with an optional ORDER BY clause and an optional FOR UPDATE clause...The FOR UPDATE clause makes the result an updatable cursor.

And the format seems to be:

Select [fields] from [table] order by [fields] for update of [fields]

Which is precisely the format used in my soft query, shown at the base of this page. Now from the MySQL manual, the syntax of the Select statement is as follows:

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

So at first sight, the only thing wrong with my SQL statement is the little word "of". The field name TrQ also seems to be redundant. So I took them both out and re-ran the FindbQ1S (simply the MySQL version of the FindbQ1) class.

Now the original error dump was:

try connection connection succeeds try delete delete succeeds try query INSERT INTO TransComp VALUES (337, '2001-06-20', 'Sell', 'CRT', 0.45, -6000) try insert insert succeeds query fails SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of TrQ' at line 1 disconnection succeeds

This time, the error dump was:

try connection connection succeeds try delete delete succeeds try sell query Sell insert Query = INSERT INTO TransComp VALUES (420, '2006-09-12', 'Sell', 'ZFX', 10.65, -100) try insert sell insert sell succeeds Buy Query = SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'ZFX' for update try Buy Query Buy Query succeeds fBCumQ= 0.0 fSQ= 100.0 query fails SQLException: Before start of result set disconnection succeeds

And it represents progress to the extent that second query, the buy query, which caused the initial problem, is now running. However, it appears the diagnostic work must continue, because there is another exception, perhaps further inside the loops.

Diagnostics with MySQL

2011-07-25T16:28:00.000-07:00

I have changed my mind and decided to record my diagnostic work because it may be of interest to someone else moving from a Derby database to MySQL, and perhaps more honestly, it helps me to think.

The problem I am addressing is twofold. First I am running routines written two years ago, and although they were written by me, with the passage of time they now look like double Dutch. Second I am using an aging dataset, which has become messy with the passage of time. I know the code ran two years ago, and one year ago, so the problem probably relates to something I have done in the last year. The trouble is, I have done a lot, mostly in the name of "cleaning it up" or "filling holes" in the dataset.

Shares, like all real world data, don't behave in a simple or uniform way. Companies change their names, they merge with and get taken over by other companies, and they change the nominal unit value of their shares on a whim. The question is how to deal with this. Do you use "transactions", which didn't take place, but which attempt to adjust to the current situation, or do you break all accounting and audit protocols by overwriting old transactions with updated parameters. Either way, you muck up the data and make it more difficult to analyze in the sort of methodical fashion which lends itself to easy programming.

The error reported was:

SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of TrQ' at line 1

Now if the problematic SQL query were hard coded, it would be quite easy to check it against the manual as recommended. But in this case it is not. The query is generated from within the code. Indeed it is generated from within an iterative loop, and at first site, the problem does not seem occur on the first iteration. Stranger still, when I ran it using Derby, the program crashed, but in a different place.

So I need to break up the code such that the loops begin to run, but pass their output to the screen rather than trying to use it run more queries.

My initial screen dump was in the form:

Sidx= 470 Sell insert Query = INSERT INTO TransComp VALUES (470, '2027-10-20', 'Sell', 'CEY', 6.2, -400) try insert sell insert sell succeeds Buy Query = SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'CEY' for update of TrQ Sidx= 471 Sell insert Query = INSERT INTO TransComp VALUES (471, '2027-10-20', 'Sell', 'AVE', 1.77, -1000) try insert sell insert sell succeeds Buy Query = SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'AVE' for update of TrQ

This confirmed that sales data were being successfully inserted, but the dump was too long, and I lost the top. To save the bother of writing to a text file, I shortened it. The next dump was in the form:

try sell query loop Sell Query = SELECT * FROM TransRaw where TrType = 'Sell' order by dDate, TRidx INSERT INTO TransComp VALUES (337, '2001-06-20', 'Sell', 'CRT', 0.45, -6000) INSERT INTO TransComp VALUES (418, '2001-08-20', 'Sell', 'QCH', 5.9, -300) INSERT INTO TransComp VALUES (443, '2001-09-20', 'Sell', 'CBA', 51.2, -125) INSERT INTO TransComp VALUES (404, '2002-08-20', 'Sell', 'CBA', 53.48, -125) INSERT INTO TransComp VALUES (475, '2002-11-20', 'Sell', 'CBA', 49.99, -100)

This all fitted on one screen, and the first thing I noticed was that the dates are all wrong. I also noticed that CRT is indeed the first item in this jumbled up dataset. I therefore ran the same dump using Derby, and the first five items were now:

try sell query loop Sell Query = SELECT * FROM TransRaw where TrType = 'Sell' order by dDate, TRidx INSERT INTO TransComp VALUES (420, '2006-09-12', 'Sell ', 'ZFX', 10.65, -100) INSERT INTO TransComp VALUES (410, '2006-12-12', 'Sell ', 'BIL', 12.44, -80) INSERT INTO TransComp VALUES (418, '2007-08-01', 'Sell ', 'QCH', 5.9, -300) INSERT INTO TransComp VALUES (412, '2007-08-15', 'Sell ', 'CDO', 6.2, -220) INSERT INTO TransComp VALUES (417, '2007-11-07', 'Sell ', 'LIP', 0.95, -1300)

The dates here are correct, and the order is as I would expect. I must confess here, that while I spent a few days browsing the Derby documentation, I haven't even glanced at that for MySQL yet. I just assumed it would be the same, and thought I could "wing it". I guess that assumption has now been proven false. I really need to have a look at the manual (as the error message suggested), but first let's have a closer look at the lines for CRT:

INSERT INTO TransComp VALUES (337, '2001-06-20', 'Sell', 'CRT', 0.45, -6000) INSERT INTO TransComp VALUES (337, '2009-06-01', 'Sell ', 'CRT', 0.45, -6000)

The first line above is taken using MySQL, and the second using Derby. And the first observation is that MySQL is using day information for the year and converting the first two digits of the year to the day. That should be easy enough to fix. The second is that MySQL is removing trailing spaces from the transaction type, while Derby is not. I've used a 5 character field: Derby displays it with 5 characters: 'Sell ', while MySQL truncatates it to 'Sell'.

Finally for today, using the first screen dump above, let's look closely at the Buy query, which MySQL rejected:

SELECT * FROM TransRaw where TrType = 'Buy' and StCode = 'CEY' for update of TrQ

Just now I can't remember what I meant by "for update of TrQ". Perhaps I need to revisit both manuals.

More ambitious MySQL-JDBC examples

2011-07-24T05:25:00.000-07:00

The Java classes described in my last two entries were ideal for a first try at writing code for the MySQL Connector/J driver, because they were essentially trivial - they made a connection, submitted a single SQL query (I call them all queries - some people call them commands or statements) and then disconnected from the database. I shall now try a slightly more ambitious class, intended to populate a table from a text file. Before running it, I need to create the text file in the following format:

Somebody very clever would probably write an SQL query to do that, but as the raw data resides in an Access database, I use the export wizard.

The pane above is the first pane of the export wizard. The default selection for export type is delimited, so I leave that and click Next.

The pane above is the second pane of the export wizard. I select Comma as my delimiter, and I change my text qualifier to {none}. I also click on the Advanced button.

The pane above is the pane opened by the Advanced button in Pane 2. Here I change the date delimiter to a full stop, and I select the Leading Zeros in Dates check box. I then click OK to return to Pane 2 and Next to move to Pane 3.

The pane above is the third pane of the export wizard. Here I simply change the file name to Trans110630.csv and click finish. I then open the file with Notepad and make 3 modifications.

The pane above shows the first modification. Here I replace [space]0:00:00 with nothing.

The pane above shows the second modification. Here I replace .00 with nothing.

The pane above shows the third modification. Here I replace $ with nothing. And I end up with something looking like Pane 1 above. I then move this file to the fin folder in my local code files folder. It is from here that I will run the new class, which in it's JDBC-Derby format is shown on this page. I shall refrain from reprinting the entire code file here, but simply confirm that the following 5 lines from the original file:

// Load database driver Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); // Make connection String connn = "jdbc:derby:dbFin"; connect = DriverManager.getConnection( connn );

were replaced by the following 5 lines in the new code file:

// Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/dbFin"; connect = DriverManager.getConnection(url,"jhipp", "h1pp0");

Obviously, the text file name was updated as well, and the message came back:

try connection connection succeeds try delete delete succeeds 502 records successfully inserted disconnection succeeds

which confirmed that the code also worked with the MySQL database.

So far so good, but the real manipulation is in the class, FindbQ1, shown in full on this page. So I converted and ran the code. Once again, there was no problem connecting, but there was an SQL syntax error, which will need investigating:

To investigate, I reran my Derby-JDBC code with the same text file to see if it comes up with the same or a similar error, and it did. This seems to be a problem with my own code or data, and as it is beyond the scope of the current topic, I shall go silent again until I have fixed it.

More simple MySQL-JDBC examples

2011-07-23T03:14:00.000-07:00

In order to consolidate my understanding of JDBC using the MySQL Connector/J driver, I shall convert all of the files from my financial mini-application, for which I described the JDBC-Derby connection in detail here. They are all examples of what I would call text file code, with each class very short and easy to follow. I shall work through half a dozen of these before attempting to convert any gui code.

In the first example I shall add a user to my dbFin database. R.G. Baldwin, to whose article I have referred in previous posts, achieved this in the same file/class as the creation of a database. I wanted to keep my first MySQL-JDBC class very simple. I didn't want confusion over the syntax of long strings mucking it up. So I shall create a special class to achieve that practically redundant but theoretically possibly important task.

The class having been created, the code was as follows:

import java.sql.*; public class FindbUAdd { public static void main(String args[]) throws ClassNotFoundException, SQLException { /** * These are shared variable declarations */ Connection connect = null; Statement stat = null; String dCreate = "GRANT SELECT,INSERT,UPDATE,DELETE," + "CREATE,DROP " + "ON dbFin.* TO 'jhipp'@'localhost' " + "IDENTIFIED BY 'h1pp0'"; /** * This is the start of the main code. */ System.out.println("try connection"); try { // Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/dbFin"; connect = DriverManager.getConnection(url,"root", "my password"); stat = connect.createStatement(); System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * These are the makeuser lines */ System.out.println("try makeuser"); try { stat.executeUpdate(dCreate); System.out.println("makeuser succeeds"); } catch(Exception ex) { System.out.println("makeuser fails"); System.out.println(dCreate); System.err.println("SQLException: " + ex.getMessage()); } finally { // Close connection if (connect!= null) { try { connect.close(); System.out.println("disconnection succeeds"); } catch (SQLException sqlEx) { System.out.println("disconnection fails"); } } } } }

So far, with the code included in my previous post, and the code above, we have achieved what was achieved with twelve characters included in a single line using JDBC for Derby. The twelve characters were:

;create=true

included at the end of:

String connn = "jdbc:derby:dbFin;create=true";

The substantive part of my original FindbCreate class was the creation of a table. I shall now attempt this with a third mini-class:

import java.sql.*;
public class FintabCreate { public static void main(String args[]) throws ClassNotFoundException, SQLException { /** * These are shared variable declarations */ Connection connect = null; Statement stat = null; String dCreate = "CREATE TABLE TransRaw " + "(TRidx INTEGER, " + "dDate DATE, TrType CHAR(5), StCode CHAR(3), " + "TrPrice REAL, TrQ INTEGER, primary key (TRidx))"; /** * This is the start of the main code. */ System.out.println("try connection"); try { // Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/dbFin"; connect = DriverManager.getConnection(url,"jhipp", "h1pp0"); stat = connect.createStatement(); System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage());
} /** * These are the maketable lines */ System.out.println("try maketable"); try { stat.executeUpdate(dCreate); System.out.println("maketable succeeds"); } catch(Exception ex) { System.out.println("maketable fails"); System.out.println(dCreate); System.err.println("SQLException: " + ex.getMessage()); } finally { // Close connection if (connect!= null) { try { connect.close(); System.out.println("disconnection succeeds"); } catch (SQLException sqlEx) { System.out.println("disconnection fails"); } } } } }

The output from this was:

try connection connection succeeds try maketable maketable succeeds disconnection succeeds

This now takes me to the end of my original FindbCreate class, and is a suitable point for a break.

Encoding a local connection to MySQL

2011-07-22T17:24:00.000-07:00

The code, which I intend to modify, is described in detail on this page. My first task is to modify the batch file which sets the path to the binaries directories to incorporate the current jdk version:

cd My Documents\DDrive\CodeLocal\Fin set path=C:\Program Files\Java\jdk1.6.0_26\bin

This batch file also makes current the directory where my code files are kept. It leads me to the second task, which is to modify the code files. The first modification is to replace:

// Load database driver Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

with

// Load database driver Class.forName("com.mysql.jdbc.Driver");

The second modification is to replace:

// Make connection String connn = "jdbc:derby:dbFin;create=true"; connect = DriverManager.getConnection( connn );

with

// Make connection String url = "jdbc:mysql://localhost:3306/mysql"; connect = DriverManager.getConnection(url,"root", "my password");

The third modification is to create the dbFin database as follows:

stat.executeUpdate("CREATE DATABASE dbFin");

Finally, for simplicity, I shall remove the make table query and leave the disconnection sequence unchanged.

Before modifying the batch file to run the code, I must compile it as follows:

javac FindbCreate.java

This having been done, the one line batch file to run the code becomes:

Java -classpath "C:\Documents and Settings\Jonathan\My Documents\DDrive\ CodeLocal\Fin\mysql-connector-java-5.1.17-bin.jar"; "C:\Documents and Settings\Jonathan\My Documents\DDrive\ CodeLocal\Fin" FindbCreate

Running it was quite hard work, not least because it was late evening and I'd hit the grog again, so the batch files were full of typos. I had also forgotten that I had set my own password on the root user. Anyway, in the clear light of the next morning it ran correctly. For completeness the full text of the code file was:

import java.sql.*; public class FindbCreate { public static void main(String args[]) throws ClassNotFoundException, SQLException { /** * These are shared variable declarations */ Connection connect = null; Statement stat = null; /** * This is the start of the main code. */ System.out.println("try connection"); try { // Load database driver Class.forName("com.mysql.jdbc.Driver"); // Make connection String url = "jdbc:mysql://localhost:3306/mysql"; connect = DriverManager.getConnection(url,"root", "my password"); stat = connect.createStatement(); System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage()); } /** * These are the makedatabase lines */ System.out.println("try makedatabase"); try { stat.executeUpdate("CREATE DATABASE dbFin"); System.out.println("makedatabase succeeds"); } catch(Exception ex) { System.out.println("makedatabase fails"); System.out.println("CREATE DATABASE dbFin"); System.err.println("SQLException: " + ex.getMessage()); } finally { // Close connection if (connect!= null) { try { connect.close(); System.out.println("disconnection succeeds"); } catch (SQLException sqlEx) { System.out.println("disconnection fails"); } } } } }

And the return messages were:

try connection connection succeeds try makedatabase makedatabase succeeds disconnection succeeds

MySQL versus Derby - file locations

2011-07-21T18:06:00.000-07:00

If you use the embedded driver for Derby, the default location for any database created is in the same folder as the driver, derby.jar. In the case of my financial mini-app I placed this package in the same folder as my code files, and I gave it a name derived from the function of the code - fin. When I ran the code, Derby created a subfolder called dbFin to contain the data files. It was all very convenient.

According to my understanding of MySQL:

The server has to running. By default, when you install it starts as a service with Windows. R.G. Baldwin, in 2004, deselected this option. Computers have moved on since then, so I left it in, and the four cores of my Ci5 processor (boasting a little here, because a couple of years ago I was still using a PII) still run at 0-1% as they did before the installation. So I don't need to worry about getting the server running, because it is running.
The JDBC driver, MySQL Connector/J, needs to be installed on your computer and included in the class path. R.G. Baldwin placed it in his jre libraries folder. My preferred option would be to place the mysql-connector-java-3.0.15-ga-bin.jar file with my own code files, and I shall try that as a first option.

In order to run my existing code:

I need a copy of the Java Development Kit (JDK) installed. Again, I've had it before, but I checked on the (now) Oracle Java Downloads page for the current version. I had JDK update 3, and the current release is update 26. As I happened to check during off peak hours for my ISP, I downloaded the current release and installed that.
Before running any code I need to set a path to the JDK binaries. This can be done as an environment variable, or just prior to runtime. I usually use a batch file, which also takes me to the locale of my own code.
In the command to run my code, I need to set the classpath for my own binaries and for the JDBC driver. Again, I usually use a one line batch file, because it is easy to edit and run.

In order to convert my code to use MySQL:

I need to modify my classpath to point to the MySQL Connector/J driver, and of course ensure that the mysql-connector-java-3.0.15-ga-bin.jar file is actually where the classpath is pointing.
I need to modify two lines of my own connection code, to load the MySQL JDBC driver, and to refer to the MySQL database. I also need to add a command to create the database, if I have not already done so.

My next entry will record the execution of these steps, and the results of doing so.

MySQL versus Derby - JDBC code

2011-07-19T06:41:00.000-07:00

I have mentioned that I am using a developer.com article as a template, but before trying to compile and run it, I shall compare the code outlined therein with that in my own description of a simple connection to a Derby database.

The article rather neatly summarises the five critical steps in encoding any JDBC connection to a database:

Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.
Get a Connection object that recodesents a connection to the database server (analogous to logging onto the server).
Get one or more Statement objects for use in manipulating the database.
Use the Statement objects to manipulate the database.
Close the connection to the database.

The first section of code from the article runs as follows:

The approximately corresponding section in my code is:

public class FindbCreate { public static void main(String args[]) throws ClassNotFoundException, SQLException { /** * These are shared variable declarations */ Connection connect = null; Statement stat = null;

I also define an SQL string in this section, but for comparison I'll leave it out for now. The article continues with:

//Register the JDBC driver for MySQL. Class.forName("com.mysql.jdbc.Driver");

The corresponding section of my code goes:

System.out.println("try connection"); try { // Load database driver Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

You may note that I declare my variables outside the try statement, and I send a message to myself, so I know how far the program has got if it subsequently crashes, but apart from that the structure is similar. The main difference is the name of the JDBC driver, with mine being for Derby, and his for MySQL. In passing in might be noted that Derby offers the option of an embedded driver, which does not require a server to be running, while MySQL does not.

The article breaks the connection code into two snippets, which I shall combine into one:

//Define URL of database server for // database named mysql on the localhost // with the default port number 3306. String url = "jdbc:mysql://localhost:3306/mysql"; //Get a connection to the database for a // user named root with a blank password. Connection con = DriverManager.getConnection( url,"root", "");

My corresponding code is:

// Make connection String connn = "jdbc:derby:dbFin;create=true"; connect = DriverManager.getConnection( connn );

So the article puts the database URL into a string and hard codes the rest of the connection string, whereas I put the whole connection string into a string. The article names the so called "master database", named mysql, and logs in as root. I don't think the Derby embedded driver requires users or passwords, and I named my own database, and appended create = true in case I had codeviously forgotten to create it. I am not sure whether that construct will work with MySQL.

The code in the article then sends a couple of messages about the connection. I'll skip these. The next substantive line is:

stmt = con.createStatement();

The corresponding line in my code is:

stat = connect.createStatement();

And my code includes an error trap after this, in case anything has gone wrong:

System.out.println("connection succeeds"); } catch(Exception ex) { System.out.println("connection fails"); System.err.println("SQLException: " + ex.getMessage()); }

The next snippet from the article creates the database "JunkDB":

stmt.executeUpdate("CREATE DATABASE JunkDB");

This was achieved in my code with the connection string, but as I said I am not sure whether it will work with MySQL, so I'll follow the format of the article when I try it out.

From this point on comparisons between my code and his are a bit redundant, because we are both just running a series of SQL statements, so I'll leave it there.

Running MySQL from the command screen

2011-07-18T17:22:00.000-07:00

In the time it has taken me to edge towards my current goal of encoding a link to MySQL in Java, I have rebuilt my computer again, and so lost the installation of MySQL described in my last entry. This has forced me to retrace my steps a bit, and in so doing I have stumbled upon another handy resource. On a site called developer.com, there is an article entitled "JDBC and MySQL: Installation and Preparation of MySQL". I could have done with this when I was first learning JDBC. It is very well written - concise, yet thorough.

I visited the MySQL download page before installing. The current version is 5.5.14 rc. I had previously downloaded 5.5.9, and as it is a 100MB download, I decided to use my existing installation package. I pretty much followed my own installation instructions.

I picked up one ambiguity. I recommended "Custom Installation" from the Server Instance Configuration Wizard. I should have noted for clarity that this wizard runs after the software installation, and that for the software installation the "Standard Installation" works fine.

I also picked up sloppy word ordering. I mentioned that I had opted to have the path to the MySQL binaries folder added as an environment variable in the section on running MySQL. So when I reinstalled after dinner and a couple of drinks, I forgot to do this. The path note would therefore have been better placed in the installation section. So I added the environment variable manually, and as often happens with me, it didn't work. So I ran MySQL from the binaries folder as follows:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u root -p

And it ran fine, which confirmed that the installation had gone correctly.

This takes be back where I ended my last blog entry. This time I went a step or two further, taking my lead from the article mentioned above. First I created a local database, with the following one line command:

mysql> CREATE DATABASE JunkDB;

from which the response back was:

Query OK, 1 row affected (0.00 sec)

A small but satisfying point was that in the article the recorded time was 0.13 sec, indicating that 7 years after the article was written computers are running at least 26 times faster than they were then.

Second I added a new user:

mysql> GRANT SELECT,INSERT,UPDATE, -> DELETE,CREATE,DROP -> ON JunkDB.* -> TO 'auser'@'localhost' -> IDENTIFIED BY 'drowssap'; Query OK, 0 rows affected (0.00 sec)

Please note that if you want to copy and paste this, get rid of the little arrows. MySQL seems to add these after any line return, not preceded by ; or \g. Please note also that the last line is a response. So if you use a text file to clean up the command for pasting, after cleaning it should look this this:

GRANT SELECT,INSERT,UPDATE, DELETE,CREATE,DROP ON JunkDB.* TO 'auser'@'localhost' IDENTIFIED BY 'drowssap';

I copied exactly from the article, because my knowledge of the command syntax is too week to want to fiddle, although it was explained inter alia that 'auser' is the username and 'drowssap' is the password.

The third and final step before have a crack at some code was to download the JDBC connector driver for MySQL: Connector/J. I recommend doing this from the MySQL connector page, which can be reached easily from the main download page. The download from there was an ordinary zip file, and for now I just extracted the contents into an easy to find location. From the article, the key file was:

mysql-connector-java-5.1.17-bin.jar

which was in the root directory of the extracted files.

Installing MySQL

2011-02-23T02:12:00.000-08:00

Before attempting to encode a link to MySQL, I thought I'd install MySQL on my local machine and have a bit of a play. My first port of call was the home page of the MySQL web site. I couldn't see any obvious link to installation or user instructions, so I tried the Windows page, but again it was all about selling the produce rather than using it. So I did a How to search on Google and ended up on a wikihow page which fitted the bill.

The screenshots on the installation page were from version 4.1 (I was installing 5.5), but they still pretty close to the mark. The recommendation to select "Standard Installation" from the Server Instance Configuration Wizard was a bad one because it hung. The default selection for custom installation worked better, not least because it automated the addition of the correct port to the Windows firewall.

Another reason the installation hung on my machine was AVG blocking the wizard, and it was not immediately obvious how to turn it off. After search for a forum post, it turns out I had to open Resident Shield, Advanced Settings, from the main console (see below).

From there is was pretty to uncheck "enable resident shield" (see below) to switch AVG off.

Meanwhile the wizard, having hung, needed to be closed with Task Manager, under the applications tab. Fortunately the initial installation of MySQL had placed an item in the Start menu under "Start">"All Programs">"MySQL">"MySQL Server 4.x" to restart the Server Instance Configuration Wizard, the first page of which gave the option to remove the MySQL service. I needed to do this so the wizard could then be rerun on a clean slate.

The installation page in wikihow seemed to think there would also be a menu item to Start MySQL in the command line, which there wasn't. However, the How to Send sql Queries to MySQL page had the syntax required to start mysql.exe straight from the command line.

In my case I was logging in as the root so the command was:

D:\Documents and Settings\Jonno>mysql -u root -p

During the setup I had opted to have the path to the MySQL binaries folder added as an environment variable, which is why I could run the command from my Windows home folder. The response came back:

Enter password: *******

I put in my password and got:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.9 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

I didn't want to enter any sql from the command line, so I entered:

mysql> exit

And got a very polite:

Bye D:\Documents and Settings\Jonno>

Thus ended the lesson.

Rasch Itembank Blog

2011-02-17T23:22:00.000-08:00

So I have started a blog for my Rasch Itembank Project, and the address is:

http://rasch-itembank.blogspot.com

The Learning Java blog will now be kept for java related issues, including issues related to data storage and access.

I am currently using a Derby database, and I had a few teething problems with that. Anyway after failing to find an ISP who would host a derby server for me, I have now found one who will host MySQL. So that will be a new learning curve for me.

Rounding up the year

2010-11-08T23:24:00.000-08:00

This blog was created to record my journey in learning Java. While that journey might not have completely reached its conclusion, it has certainly started to plateau. I posted 22 pages in the last four months of 2008, 66 in 2009, and just 11 in the whole of 2010.

Most of my 2010 posts related to my psychometric project, which is another journey really, and I think is worthy of its own blog. If people run a search on "learning java" they probably won't be very interested in the ups and downs of my math project, but if they run a search on "Rasch" or "Scoring Rates", I'd like them to find me writing a blog focused on the topic, rather than rambling about the ins and outs of Java.

I shall post a link to the new blog here when it begins (which might not be until some time in 2011), and keep any new posts in this blog more closely related to learning new tricks in java.

Checking the database

2010-06-27T05:00:00.000-07:00

The code currently uploaded to my java project at covers only the front end of the application. I have not formalized the back end. It remains a collection of code scraps, which I use from time to time to check what is in the database, or less often to change what is in the database.

I have not formalized it because I am currently the only user. In previous incarnations of the app I have incorporated data queries, with all sorts of filters and user customization, which no one has understood how to use.

My current ad hoc query code is as follows:

public class ItemdbQuery {
public static void main(String args[])
   throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.ClientDriver");
// Make connection
String connn = "jdbc:derby://192.168.2.2:1527/dbAMJ";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
  }  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
  }
// Execute a query
System.out.println("try query");
try {
String aquery = "SELECT * FROM Items";
ResultSet resultSet = stat.executeQuery(aquery);
// Display results
while(resultSet.next()) {
      System.out.println(resultSet.getString(1) + " " +
      resultSet.getString(2) + " " +
      resultSet.getString(3) + " " +
      resultSet.getString(4) + " " +
      resultSet.getString(5) + " " +
      resultSet.getString(6));
}
resultSet.close();
stat.close();
System.out.println("query succeeds");
  }  catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
  } finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
  }
   }
}

This code differs from that used for my financial transaction query in that the database is hosted remotely, so the derby "Clientdriver" was used, and the connection string included the local IP address of my server.

A sample of the data output is given below:

23 1247061594801 1 3 + 2 =   1 8.683068
24 1247061594801 1 5 + 3 =   1 36.518562
25 1247061594801 1 2 + 5 =   1 39.164494

The fields are essentially: an index, a datestamp, an operation code, an item, the raw score for the item, and the scoring rate. This can be sucked into a spreadsheet or GUI database for further analysis.

MS Outlook Slow to Start

2010-06-10T06:22:00.000-07:00

It is embarrassing, whatever ones business, if a client rings up for an appointment, and you have to keep them waiting for 30 or more seconds while MS Outlook cranks into action. It is especially embarrassing if the client is calling to ask you to build a new computer for them, which they hope will be quick and easy to use.

My old excuse was that I was using a Dell Pentium III laptop, which for sentimental reasons I didn't have the heart to throw out. But I am now using a recently built system using a Pentium Core 2 Duo processor and 2 GB RAM to run Windows XP pro. So what is my excuse now?

When I built the system I partitioned the hard drive and put an identical OS on both partitions. The idea was that the C drive would be my top copy, and the D drive would be for experimentation. But I was so paranoid about intalling anything on the C drive, the D drive became my de-facto working drive. And it is the D drive on which MS Outlook displays the flash screen for 30 seconds before revealing any emails or appointments. On the clean C drive, MS Outlook starts in between 1 and 3 seconds. I could scrub the D drive and start again, but I thought I'd do some research first.

The first place I looked was the Microsoft forum. It used to be good, but it has gone badly downhill over the last few years. Anyway, using the title of the current blog as a search string rendered quite a short list of posts and absolutely nothing useful. Plugging the same search string into Google yielded a few hopeful looking forum posts. I tried this one and this one and this one. Sadly none came close to fixing my problem.

To summarize, deleting or renaming the extend.dat file did nothing. Running "Detect and Repair" from within Outlook did nothing. Backing up my personal folders file to a safe location and deleting the original to force Outlook to build a clean empty one did nothing. Uninstalling MS Outlook and reinstalling did nothing to solve the problem. It just took 30 seconds to display and empty mail folder. Uninstalling my AVG antivirus software did not help MS Outlook to start any faster.

So I thought the problem might be in the Windows registry, and I thought I'd try using Windows restore. I remember it took a few weeks to corrupt MS Outlook, so I chose a restore point from the day after I installed it. My AV software had been installed before that, and nothing had been installed since. (If nothing had been installed since, the question might arise, how did I corrupt it, but let's leave that one for now). Anyway let me caution anyone else tempted to use Windows restore not to. It is perhaps the stupidest invention ever to come out of Microsoft, and in the ten years since it came out, I have only ever once achieved a successful outcome with it. Mostly it achieves nothing, and often it corrupts the whole system. This for me was one of those occasions. My AV software disappeared/ceased to function, and MS Outlook still displayed the flash screen for 30 seconds before showing my now empty mailbox.

After wasting the best part of 4 hours fiddling around achieving nothing, my conclusion is that the only sensible solution is to ditch the OS. So how did the situation arise? I don't know exactly. My mailboxes are large, but they are large on the C drive and that installation works fine. I was using the import feature a lot, when I was alternating between the C drive and the D drive, and maybe the mailbox got corrupted. But in that case why did the problem remain after I deleted my mail box and created a new one? The only other possible explanation lies in the integration with AVG - maybe something happened during an update. But again it seems unlikely, as I have another computer using Outlook (admittedly with an empty mailbox) and AVG, and it works fine.

So it will remain another Windows mystery. I'll go back to using my C drive, and either ditch the D drive, or not use Outlook in it, or leave Outlook running all the time so I don't have to wait for it to start. I'll back up my C drive on a regular basis, so I really can "restore" to an earlier time if I need to. And I won't hate Microsoft - I use their software, and I still don't completely like the alternatives - but I will wonder whether the day will come when such problems really can be fixed with a diagnostic tool, rather than wiping a drive sector and starting again.

Revisiting old code

2010-06-08T23:14:00.000-07:00

When I first began this blog, I was pretty down on the official Java Tutorial on the Sun website. I was especially down on the Java Tutorial trail JDBC(TM) Database Access, which describes how to make a connection with a database. But a year later, I find myself scanning my own blog, which I thought comprised the finest pearls of clarity and wisdom, and I can't find any description, let alone a better description, of how to connect to a database without using a GUI app like NetBeans.

There is an entry, entitled "Building a financial mini-app", which describes an application of such a connection, but it is distinctly unhelpful to anyone wanting to build such a tool for themselves. Indeed I found it distinctly unhelpful even to me, trying to re-apply my own code. I shall therefore attempt to re-write the page in a slightly less cryptic fashion.

This has nothing to do with my Rasch-Itembank project at java.net, but for me it is a useful exercise in manipulating data. The purpose of the code is to calculate my capital gains for tax purposes under Australian Law.

I shall begin at the beginning by very beginning, with I couple of short batch files I use to set paths. In the Windows home directory I have a batch file called cd1.bat, which sets the path to the Java binaries directory and relocates us to the home of the planned new database and code intended to create it:

cd My Documents\CodeLocal\Fin set path=D:\Program Files\Java\jdk1.6.0_03\bin

Some people set the path to the Java binaries directory as a Windows environmental variable, but when I began I was using a ropy old computer with a corrupted OS, and the environmental variable never stuck, so I got into the habit of setting it for every session.

In the \Fin directory I have another batch file called cd3.bat, which sets the class path for both the derby.jar file and my own class file, which is called FindbCreate.class:

>Java -classpath "D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar"; "D:\Docs\Jonno\My Documents\CodeLocal\Fin" FindbCreate>

NB This is a one line batch file which has been broken up for display purposes. The java code behind the FindbCreate.class, used to create the database and the first table, is as follows:

import java.sql.*;

public class FindbCreate {
public static void main(String args[])
   throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "CREATE TABLE TransRaw " +
"(TRidx INTEGER, " +
"dDate DATE, TrType CHAR(5), StCode CHAR(3), " +
"TrPrice REAL, TrQ INTEGER, primary key (TRidx))";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin;create=true";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
  }  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
  }
/**
* These are the maketable lines
*/
System.out.println("try maketable");
try {
stat.executeUpdate(dCreate);
System.out.println("maketable succeeds");
  }  catch(Exception ex) {
System.out.println("maketable fails");
System.out.println(dCreate);
System.err.println("SQLException: " + ex.getMessage());
  } finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
  }
   }
}

And if all goes well, the following should appear in the command screen:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbCreate
try connection
connection succeeds
try maketable
maketable succeeds
disconnection succeeds

The database needs a second table. This could have been included in the code which created the first table, but when I am feeling my way, I like to take things one step at a time. The code I used to create the second table is shown below. Note the connect string no longer includes the create=true switch, because when you run this code, the database already exists.

import java.sql.*;

public class FindbCreateT2 {
public static void main(String args[])
  throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "CREATE TABLE TransComp " +
"(Sidx INTEGER, " +
"dDate DATE, TrType CHAR(5), StCode CHAR(3), " +
"TrPrice REAL, TrQ INTEGER)";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
 }  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
 }
/**
* These are the maketable lines
*/
System.out.println("try maketable");
try {
stat.executeUpdate(dCreate);
System.out.println("maketable succeeds");
 }  catch(Exception ex) {
System.out.println("maketable fails");
System.out.println(dCreate);
System.err.println("SQLException: " + ex.getMessage());
 } finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
 }
  }
}

To run this code you can create a new batch file or edit cd3.bat to call the class FindbCreateT2.

And if all goes well, the following should appear in the command screen:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbCreateT2
try connection
connection succeeds
try maketable
maketable succeeds
disconnection succeeds

The first table, now needs to be filled. Once again, the code to do so could have been included in either of the previously shown classes, but I have broken it out into a separate class for the time being:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbFill {
public static void main(String args[])
 throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
  BufferedReader reader = null;
String strContents = new String();
String strTridx = new String();
float fTridx = 0;
int iTridx = 0;
String strDate = new String();
String strTrType = new String();
String strCode = new String();
String strPrice = new String();
float fPrice = 0;
String strTrQ = new String();
float fTrQ = 0;
int iTrQ = 0;
int iCount = 0;
Connection connect = null;
Statement stat = null;
String dInsert = "";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
}  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are optional delete lines
*/
System.out.println("try delete");
try {
stat.executeUpdate("DELETE FROM TransRaw where TRidx > 0");
System.out.println("delete succeeds");
}  catch(Exception ex) {
System.out.println("delete fails");
System.out.println(dInsert);
System.err.println("SQLException: " + ex.getMessage());
}
/**
* This code reads the text file.
*/
  try {
      reader = new BufferedReader(new FileReader("Tr100608.txt"));
      String l;
      while ((l = reader.readLine()) != null) {
StringTokenizer strToken = new StringTokenizer(l, ",", false);
strTridx = strToken.nextToken();
strDate = strToken.nextToken();
strTrType = strToken.nextToken();
strCode = strToken.nextToken();
strPrice = strToken.nextToken();
strTrQ = strToken.nextToken();
dInsert = "INSERT INTO TransRaw " +
"VALUES (" + strTridx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + strTrQ + ")";
/**
* These are the insert lines
*/
//  System.out.println("try insert");
//  System.out.println(dInsert);
try {
stat.executeUpdate(dInsert);
iCount = iCount + 1;
//   System.out.println("insert succeeds");
 }  catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
 }
}
  }  catch(Exception ex) {
System.out.println("Error: " + ex.getMessage());
}
/**
* These are the closing statements
*/
finally {
/**
* Close input stream
*/
if (reader != null) {
reader.close();
}
System.out.println(iCount + " records successfully inserted");
/**
* Close connection
*/
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
 }
}

Prior to running this code it is essential to have the source text file ready and in the same directory as the java class file. I should have a query written so that I could quote the SQL, but instead I just exported a table from "MS Access" and tweaked the text file a bit so that it looked like this:

1,05.09.2006,Buy,BSL,6.86,200
2,12.09.2006,Buy,TFC,0.38,2500
3,12.09.2006,Buy,ZFX,10.65,100
4,12.09.2006,Buy,TLS,3.51,300
5,13.09.2006,Buy,CSR,2.85,400 ...

When you run the FindbFill class from the command line, if all goes well the output messages should be as follows:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbFill
try connection
connection succeeds
try delete
delete succeeds
378 records successfully inserted
disconnection succeeds

If you get the format of the text file wrong, you might get a few error messages like these:

insert fails SQLException:
Syntax error: Encountered "," at line 1, column 33.
insert fails SQLException:
Lexical error at line 1, column 64.
Encountered: "$" (36), after: "".

After filling the TransRaw table, the penultimate step is to transform the data and fill the TransComp table. Once again, I have written the code for this in a class of its own. The code is as follows:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbQ1 {
public static void main(String args[])
throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement statS = null;
Statement statB = null;
Statement statI = null;
ResultSet resultBuy = null;
String Sidx = new String();
String Bidx = new String();
String strDate = new String();
String strTrType = new String();
String strCode = new String();
String strPrice = new String();
String strTrQ = new String();
String dInsert = new String();
String dDelete = new String();
float fSPrice = 0;
float fSQ = 0;
float fSProc = 0;
float fBCumQ = 0;
float fBCurQ = 0;
float fBEQ = 0;
int iBEQ = 0;
float fBPrice = 0;
float fBCurCost = 0;
float fBCumCost = 0;
float fProfit = 0;
float fComm = 22;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
statS = connect.createStatement();
statB = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
statI = connect.createStatement();
System.out.println("connection succeeds");
}  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are optional delete lines
*/
System.out.println("try delete");
try {
statI.executeUpdate("DELETE FROM TransComp");
System.out.println("delete succeeds");
}  catch(Exception ex) {
System.out.println("delete fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are query execution lines
*/
System.out.println("try query");
try {
String aquery = "SELECT * FROM TransRaw" +
" where TrType = 'Sell'" +
" order by dDate, TRidx";
ResultSet resultSell = statS.executeQuery(aquery);
/**
* Display sell results.
*/
while(resultSell.next()) {
   Sidx = resultSell.getString(1);
   strDate = resultSell.getString(2);
   strTrType = resultSell.getString(3);
   strCode = resultSell.getString(4);
   strPrice = resultSell.getString(5);
   strTrQ = resultSell.getString(6);
   fSPrice = resultSell.getFloat(5);
   fSQ = -resultSell.getFloat(6);
   fSProc = fSPrice * fSQ;
   fSProc = fSProc - fComm;
dInsert = "INSERT INTO TransComp " +
"VALUES (" + Sidx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + strTrQ + ")";
System.out.println(dInsert);
/**
* These are the insert lines
*/
System.out.println("try insert");
try {
statI.executeUpdate(dInsert);
System.out.println("insert succeeds");
 }  catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
 }
/**
* Display buy results.
*/
String bquery = "SELECT * FROM TransRaw" +
" where TrType = 'Buy'" +
" and StCode = '" +
strCode +
"' for update of TrQ";
resultBuy = statB.executeQuery(bquery);
fBCumQ = 0;
fBCumCost = 0;
fProfit = 0;
while(fBCumQ < bidx =" resultBuy.getString(1);" strdate =" resultBuy.getString(2);" strtrtype =" resultBuy.getString(3);" strcode =" resultBuy.getString(4);" strprice =" resultBuy.getString(5);" strtrq =" resultBuy.getString(6);" fbprice =" resultBuy.getFloat(5);" fbcurq =" resultBuy.getFloat(6);" fbcumq =" fBCumQ"> fSQ){
     fBEQ = fBCumQ - fSQ;
     fBCurQ = fBCurQ - fBEQ;
     fBCumQ = fBCumQ - fBEQ;
     iBEQ = (int)(fBEQ);
     resultBuy.updateInt("TrQ", iBEQ);
    } else {
     dDelete = "DELETE FROM TransRaw " +
     "where TRidx = " + Bidx;
     statI.executeUpdate(dDelete);
    }
    fBCurCost = fBCurQ * fBPrice;
    fBCurCost = fBCurCost + fComm;
    fBCumCost = fBCumCost + fBCurCost;
dInsert = "INSERT INTO TransComp " +
"VALUES (" + Sidx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + fBCurQ + ")";
System.out.println(dInsert);
/**
* These are the insert lines
*/
System.out.println("try insert");
try {
statI.executeUpdate(dInsert);
System.out.println("insert succeeds");
  }  catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
  }
}
}
fProfit = fSProc - fBCumCost;
System.out.println("Proceeds = " + fSProc);
System.out.println("Costs    = " + fBCumCost);
System.out.println("Profit   = " + fProfit);
resultBuy.close();
}
resultSell.close();
statS.close();
statB.close();
statI.close();
System.out.println("query succeeds");
}  catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

The output from this one gives all the information I need to complete my tax return, but the command window is a less than ideal place to store it, and the format is not suitable for cutting and pasting into a data table. I therefore have a final chunk of code, which queries the TransComp table, and dumps the data cleanly into the command window. The code is as follows:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbQSim2 {
public static void main(String args[])
throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
}  catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are query execution lines
*/
System.out.println("try query");
try {
String aquery = "SELECT * FROM TransComp";
ResultSet resultSet = stat.executeQuery(aquery);
// Display results
while(resultSet.next()) {
   System.out.println(resultSet.getString(1) + ", " +
   resultSet.getString(2) + ", " +
   resultSet.getString(3) + ", " +
   resultSet.getString(4) + ", " +
   resultSet.getString(5) + ", " +
   resultSet.getString(6));
}
resultSet.close();
stat.close();
System.out.println("query succeeds");
}  catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

Had time permitted I could have output the data to a text file, but it was just as easy to copy and paste from the command line. I imported this to a table in "MS Access" and used the GUI to make a nice WYSIWYG report.

Conundrum

2010-05-29T03:03:00.000-07:00

The idea of my Rasch-Itembank open-source project at java.net is to produce a large numeracy estimation item bank, which updates the estimated difficulty parameters in real time, as it is used. To work properly, the client needs to generate items from the item bank according to the latest item difficulty estimation.

The most reliable current working version of my Java Math Test client is a complete cheat. It has absolutely no connection with a database, it is ISP hosted, and it usually works first time, as long as the user has the JRE installed.

A second version, hosted by me, is a partial cheat. It connects to a database to record performance data, but it uses hard coded arrays of items, whose difficulty has been estimated from static data collected in the past. This version is less reliable for many reasons. I don't always have the server turned on, I don't always have the database turned on, and sometimes my IP address changes.

The conundrum is that to get improved estimates of item difficulty, the client needs to be used, and to be used a lot, it needs to be reliable. Using hard coded item arrays, if the connection to the database fails, session data is lost, but the client continues to function, so the user is not pissed off.

However, in this more reliable configuration, a key selling point - that item difficulty and student ability mutually interact in real time, to product the best possible estimates of both - becomes something of a deception.

I think the best compromise solution is to leave reliable versions on my web site, perhaps with the hard coded arrays updated from time to time as more data comes in, and to develop code for the true blue dinky dye version without actually deploying it until data connection reliability can be assured.

Structural Changes to the Repository

2010-05-28T05:33:00.000-07:00

For a couple of reasons I have made some structural changes to my remote code store, or repository. First, if I want other people to contribute, I need to make it easy to understand what goes where. Secondly, I wanted to play around a bit more with subversion (svn) commands.

My first task was to create some new directories. When I read the book, it looked as if you used svn commands just like DOS or "shell" commands, and instead of them. So I tried:

svn add "my documents\code\100515\client"

without having first created the client subdirectory. But it didn't work. The wording given by the --help switch makes it clearer:

svn add --help add: Put files and directories under version control, scheduling them for addition to repository. usage: add PATH...

In other words the add command brings a folder under the svn umbrella, but it cannot be used to create one.

Next I wanted to move some source files from the trunk to the new subdirectory. The command and the displayed results were as follows.

svn move "my documents\code\100515\AMJApp.java" "my documents\code\100515\client\src" A My Documents\Code\100515\Client\src\AMJApp.java D My Documents\Code\100515\AMJApp.java

This time the svn command really did replace the equivalent DOS command. I repeated the process for all my source files.

I then committed the changes:

svn commit "my documents\code\100515" -m "Added a preliminary directory structure" Deleting My Documents\Code\100515\AMJApp.java Adding My Documents\Code\100515\Client Adding My Documents\Code\100515\Client\src Adding My Documents\Code\100515\Client\src\AMJApp.java Adding My Documents\Code\100515\Client\src\QAdd.java Adding My Documents\Code\100515\Client\src\QDiv.java Adding My Documents\Code\100515\Client\src\QMul.java Adding My Documents\Code\100515\Client\src\QSub.java Adding My Documents\Code\100515\Client\src\QTrack.java Deleting My Documents\Code\100515\QAdd.java Deleting My Documents\Code\100515\QDiv.java Deleting My Documents\Code\100515\QMul.java Deleting My Documents\Code\100515\QSub.java Deleting My Documents\Code\100515\QTrack.java

Updating Source Code

2010-05-16T02:20:00.000-07:00

I think I was a little too cryptic in my blog entitled Posting Source Code. I referred back to it when I needed to update the source code on my project site, and found it fell short of being helpful.

I shall try to be a bit more explicit in describing how I checked out what was in the repository, and after making some changes, updated it.

When first using CollabNet Subversion I made the mistake of cluttering my home computer with the server. I am on a new machine now, and I took care to get the command-line client from the CollabNet Download page.

After installing the client, I was pleasantly surprised to observe that it ran happily from my Windows home directory. That is in stark contrast to Java, which requires paths to be set up by the user.

The command I needed to download my code into a working directory was

svn checkout URL [PATH] --USERNAME

where URL is location of my repository and PATH is the path to the local directory. My URL is:

https://java.net/projects/rasch-itembank/svn/rasch-itembank/trunk

and my local path for the day is:

"my documents\code\100515"

To edit the source code I use jEdit, and because this is an exercise in downloading from and uploading to a remote repository, I just add a few comments to each source file.

I was fiddling about with this overnight, so I used the update command to check for any other activity. This was an academic exercise, because I am currently the only user, but I wanted to work through the proper procedure. The syntax of the command is:

svn update [PATH]

In my case this translates as:

svn update "my documents\code\100515"

The response came back:

At revision 5

The absence of any user listing confirms that no one else has worked on the code in the repository.

Again following on with the procedure recommended in the book, I used the status command as follows:

svn status "my documents\code\100515"

And got the following list of modified files.

M My Documents\Code\100515\QDiv.java M My Documents\Code\100515\QTrack.java M My Documents\Code\100515\AMJApp.java M My Documents\Code\100515\QAdd.java M My Documents\Code\100515\QSub.java M My Documents\Code\100515\QMul.java

I used the status command, with identical syntax, to display a full list of the changes I had made.

As there are currently no other users I could skip the conflict resolution steps, because the were no conflicts to resolve. I could therefore jump straight to the commit command as follows:

svn commit "my documents\code\100515" -m "Just added a few comments to test the update procedure"

And got the following list of actions carried out.

Sending My Documents\Code\100515\AMJApp.java Sending My Documents\Code\100515\QAdd.java Sending My Documents\Code\100515\QDiv.java Sending My Documents\Code\100515\QMul.java Sending My Documents\Code\100515\QSub.java Sending My Documents\Code\100515\QTrack.java Transmitting file data ...... Committed revision 6.

Hopefully this is a clearer set of instructions than my last effort, so if I come back here in six months time, I'll actually understand what I've written.

Parameter Estimation

2010-01-08T00:06:00.000-08:00

The Rasch book, and the Winsteps documentation, about which I have written at some length, are both essentially about parameter estimation. And one of the objectives I want to achieve with my java applet, is real time parameter estimation. I revisited the Rasch book, and spent some time trying to follow the argument, when really I should have just gone straight for a math text book.

Rasch presents his mathematical argument as if it is new or somehow unique to the paradigm he is presenting; perhaps it was at the time, although by his own references to physics in the introduction I doubt it. Perhaps in those days it was conventional to present an argument in full, rather than simply refer the reader to one or more generics methods, and assume the reader has the ability to look them up. Certainly the book could have been a lot shorter, had he done so.

At the end of the day there is no substitute for the Law of Large Numbers, which essentially states that when your sample becomes large enough the experimental frequency of events will approximate to the expected frequency, and the need for esoteric and highfalutin mathematics is dispensed with. That is why, regardless of whether people accept my preference for scoring rates over raw scores, there is a strong argument for establishing a global item bank for frequently used items in the estimation of numeracy and other psychometric parameters.

The one feature of Rasch-parameter estimation, which perhaps is unique, or at least unusual, is the need for the simultaneous estimation of two parameters. If you toss a coin a hundred times and show roughly 50 heads, you can deduce that the probability of throwing a head is roughly 50%. If a child sits ten tests and scores an average of 95%, you can deduce the mean probability of success on the test items was 95%, but in Rasch theory, that probability itself derives from two parameters - the ability of the child and the difficulty of the test. These results in isolation do not tell you whether the child is very clever, or the test is very easy. This is the problem which Winsteps seeks to address with its data transformations and iterations, and which I have sought to address with my scoring rate quotient. Unfortunately, although Rasch showed in theory how to isolate each parameter in turn, he offered little advice on practical methodology, so to some extent there is no single authoritative source on the matter.

Probability Theory

2010-01-06T21:00:00.000-08:00

Now that I've published some code, a risk arises that someone might read it, and thereby discover that the code does not really conform with my project description. I should really rewrite it as quickly as possible, to minimise the risk of discovery, but there is a theoretical matter I should like to address first.

After writing my blog of 9 August 2009, I thought I had stumbled across something worthy of more formal publication. So I contacted my doctoral supervisors at UWA, and some people at the Institute for Objective Measurement, but to cut a long story short nothing has happened.

To be honest I was looking for help with the theory as much as a co-author. The probability theory was a bit out of my depth, so I was looking for someone who knew more about it. But apart from one pragmatic and generally expedient former supervisor, who offered to "help" (which really means proof read), because he felt sorry for me, I drew a blank.

I guess I have this fantasy of communicating with intelligent people of like interest, like you read about in books or see in TV docos about famous scientists; but it never really works out. I am not trying to put myself on a plane with famous scientists, but I am a qualified and published academic in a certain field, and it is disappointing to me that I have failed to track down anyone of like mind, either when I was enrolled at the university or since.

I had two supervisors, one of whom checked for grammatical and spelling mistakes and the other of whom checked for calculation errors. They did a good job, and I passed, but it was intellectually disappointing that they never really understood where I was coming from or what I was driving at.

And during that time I published five papers in refereed journals in three countries, the UK, Australia, and the US, and I had not one item of correspondence arising from any of them. I had emails from people interested in my website, but nothing from the printed journals.

So why do I want to publish in a refereed journal again? Fair question. Partly a litmus test, for approval rating, and partly, because, rightly or wrongly, they look good on a CV or personal web site. But it is not that important.

What is important, whether I am writing for a journal or a blog, is that I get right what I am trying to say. And having failed to find an academic who is either qualified or interested to help me, I shall simply have to gem up on the theory myself.

The good news is what social scientists and especially educationalists find esoteric and rarefied, scientists in other areas find bog standard. So the stuff I need to learn is available in undergraduate text books for mathematics and the natural sciences. I shall use the Probability and statistics EBook, published by UCLA. I have referred to it before, and probably mentioned that it is quite fun to read, with lots of graphic examples.

Posting Source Code

2010-01-05T06:35:00.001-08:00

CollabNet Subversion turns out not to be quite so hard to learn as I expected.

I had set out with a fundamental misconception, believing I needed to set up the server on my end as well.

When I realised that Sun had already done all that work for me at their end, and that I simply had to use the client to post code on their server, and that I could edit the local files with whatever I wanted at my end, it all became much clearer to me.

Furthermore, Sun even provided the URL for my project on their server together with the syntax for the checkout command.

It was pretty easy to combine the URL in this example with the import command given in the book to post my source code in the project. I say pretty easy; there was a bit of fiddling around. The checkout command includes a local path to which files should be copied, and obviously that is not needed for the import (which term from my perspective really means export or post), and the -m switch/parameter needed to be filled with a comment.

But at the end of the day, a first draft of my code is posted, and I feel much better.

Going Open Source

2010-01-04T18:32:00.000-08:00

I have decided to go open source. It has been my intention to do so for a long time, but I hesitated for several months, because I thought my more commercially minded friends would call me a fool. So I put some feelers out into the commercial world, and they were substantially ignored. With hindsight I should have followed my first instinct without hesitation, because the exercise has been a blow to my confidence. I am now launching myself into the open source community with a very dented ego and a low self esteem.

To make it worse, going open source is not a simple exercise. You don't just say hello world this is my source code, share it with me. You have to master a technology called version control, and at first glance the learning curve looks as steep as embracing java itself. I feel is if I've started all over again, and this time, instead if having my head held high, my heart is in my boots.

When I began this blog 18 months ago, I was full of optimism. I was quite excited about learning a new programming language, and I was enthusiastic about rewriting my code to make it better than before. I was very optimistic about using the web as a distribution tool, because it meant the whole world could use my software as soon as it was written, without me using a litre of fuel, or buying a postage stamp.

Now it seems that so many other people have mastered the art of delivering tools on the web, the availability of software, free and otherwise, way exceeds the ability or inclination of people to use it.

So wilh heavy heart, I am beginning to read an online book entitled "Version Control with Subversion". It seems well written, but long. Right now I cannot even envisage how my manually edited and managed source code files will fit into its system, so I must resign myself to many hours of dry reading. I'll crack open a box of matches to hold open my eyes.

The nub

2009-12-09T07:06:00.000-08:00

Sometimes people ask why I do what I do. Why do I write software nobody wants to use? Why do I care about the reliability and validity of psychometric data? Why do I want to make available assessment tools, which are accurate and meaningful, as well as cheap and easy to use?

I answer a question with a question. How is it, I say, that scientists such as Newton and Einstein were born at exactly the right time and in exactly the right place for their work to be understood, appreciated, tested, and applied?

The answer, I reply to myself, is that Newtons and Einsteins have been born in mud huts throughout the globe, throughout history; and they are still being born. The Newton we read about happened to the lucky one born in the right place at the right time, wealthy enough to receive an education and to study as a vocation, in a society disposed to listen, rather than ignore him, imprison him, or burn him at the stake. Einstein was also a lucky one, born at a time when his more esoteric theories could be put to the test and applied.

My mission is to dredge out the genius buried in the mud huts and slums of the world.

Why do I care? I care because I know it is there. I have seen children begging in the slums of India, with minds crying out to be heard.

I have another blog, which I use to express disdain for the society in which we live, so I shall refrain from raving on here. I shall simply suggest that every intelligent child lifted from the mire may contribute to lifting society itself from the mire, in later life.

Marketing

2009-10-20T03:32:00.000-07:00

I am crap at marketing. In a perfect world, I'd never have to sell anything. I'd just be. But the world is not perfect. And why waste time creating computer software if you can't sell it?

One of the reasons I began the quest to learn Java was that the version of my software written in VB6 is sitting on 600 CD's, which now gather dust in my living room. I spent a year writing to, calling and visiting schools, but the revenue generated was barely compensation for the installation time, let alone marketing, let alone development.

Out of 221 schools on the target list (essentially primary schools in the metropolitan area of Perth, Western Australia), 61 agreed to a meeting and a trial on at least one computer, and 16 actually paid for a full site license. The official price was $660, but I let some have it for half that, and I always threw in a day's worth of installation and training. I didn't charge for the two or three presale visits, and for the three to six month evaluation and decision making period, I had no income. After a year of this, and a negative income after operating costs, I pulled up stumps and let the software rot.

Was the product crap? Of course I am biased, but I don't think so. A group of schools in the remote Pilbara region used Federal Government grants to fly me out to install the software and train staff. A number of schools paid me to address "PD" staff training sessions. Two regional radio stations interviewed me on the software and what it was trying to do.

What went wrong? Essentially, I ran out of steam. For one person to develop, market, and support a product is to much to ask.

Successful "education" software is mainly produced by games companies, who have an existing marketing and support infrastructure. Even the software is produced mainly by games developers, and the curriculum content is traditionally minimal.

Latterly, some software has begun to appear on the web, with a slightly higher quality content. Mathletics is one. My daughters use it at school, and for a while they were keen to use it for homework. But their interest waned after a few weeks. Now they use my software at home just as often, perhaps to please me.

The bottom line is that designing software, which covers the curriculum, records progress, and is sufficiently interesting to captivate the attention of children for more than a few hours, is a task so enormous, so difficult, and so expensive, that neither the public nor the private sector has yet attempted to do it properly.

I freely admit that both my CD software and my web-based software only scratches at the surface of the primary school math curriculum. But I'm not going to sit at home and dedicate the rest of my life to reinventing the wheel if I can't get people to use what I have done already.

Selling my web-based software really shouldn't be that hard. It's free! But so far, offering it to just two schools to try out has been like trying to push shit up hill.

The first was the local school in the country town, where I now live. I have lived here, repairing computers, for five years, I have four children, and I sit on the local Council, so getting an appointment was not a problem. And the meeting went very well. I had been told that the new deputy principals, a husband and wife team, were dead keen on computers, and on their application in education. And sure enough, they understood what I was saying and were enthusiastic about what they saw. They said they would address the next staff meeting on the subject and get back to me.

Silence followed.

Many weeks later the same team were invited into a Council function. A few days before the function, I put in a phone call to ask what had happened, but the call was not returned. At the function itself, where I was in the role of host, rather than salesman, I sought them out again to ask what had happened. Unable to run away, or avoid me, I sensed awkwardness, even fear. After much vacillation and skirting around the subject, all I got out of them was that I would need to talk to the principal. So far I have not bothered. The application is not permanently hosted yet, and it would be premature to make a fuss. I called on them, because I had been told they would be interested. They were, but some invisible barrier rose up and prevented further progress.

The second was my daughters' primary school. Again, as a parent, I had easy access to an audience with the school principal, and just as with the first school, she purported to like what she saw. She even later wrote a polite note extolling the virtues of the software, but enclosed the CD, which she was returning to me. So I tried the guy who emails the school newsletter, and he passed me to the lady who runs the computers. I got the CD back to her, but after a couple of failed attempts to meet (my contact with the girls is infrequent and erratic), she was also wanting to return the CD, saying the school had access to "plenty of resources". I'll remember that the next time they write to parents asking for money.

Prior to contacting these schools, I had assumed they would be friendly, and suitable candidates for a pre-release trial of the software, tolerant of glitches, and willing to try subsequent editions. They have made it clear that they are not. And while ten years have passed since I was last knocking on doors, and awareness of technology has improved, teachers are as conservative as ever, entrenched in their daily routine, and deeply suspicious of anything unfamiliar.

More on Scoring Rates

2009-10-18T04:37:00.000-07:00

Following on from my last blog, the table below shows the raw scoring rates on two additional items, H and K. Item H is twice as hard as item I, and is only addressed by Student B. Item K is twice as easy as Item J and is only addressed by Student A. This scenario synthesises one which might be generated by a computer based adaptive arithmetic test, which presents more difficult items to more able students and easier items to less able students.

Raw Rates	Item H	Item I	Item J	Item K	Session Mean
Student A		4	8	16	9.33
Student B	4	8	16		9.33
It Mean	4	6	12	16	9.33

From the table, the effect of the adaptive component of the computer based arithmetic test has been similar to that of a very good handicapper in a horse race. By presenting more difficult items to the more able student and easier items to the less able student, it has produced a dead heat in the result. An examiner looking at the raw rates might be misled into thinking that both students had the same ability. Hence the need to adjust the results to take into account the difficulty of the items presented to each student.

Similarly the adaptive component of the test has distorted the item mean scores of those items presented to only one student. Take Item H. The item mean scoring rate is shown as 4. However, had the item been presented to Student A, from the stated assumptions of the example, one might have expected the scoring rate to have been 2 capm, and the item mean scoring rate would then have been 3, not 4. In the case of Item K, the item mean scoring rate is shown as 16. From the stated assumptions of the example, had this item been presented to Student B, one might have expected the scoring rate to have been 32 capm, and the item mean scoring rate would then have been 24. Item H has been made to look relatively easier than it is, because it was only presented to the more able student, and Item K has been made to look relatively harder than it is, because it was only presented to the less able student.

The scoring rate quotients calculate out as follows:

Quotients	Item H	Item I	Item J	Item K	Session Mean
Student A		0.43	0.86	1.71	1.00
Student B	0.43	0.86	1.71		1.00
It Mean	0.43	0.64	1.29	1.71

The session rates can then be adjusted, using the item quotients to calculate the adjusted rate. I am reversing the order here from my previous blog. In the previous blog I adjusted the item rates first, but in this example, it is the session rates which most clearly "need" adjusting, and the item rates in fact cannot be adjusted.

Adjusted Rates	Item H	Item I	Item J	Item K	Session Mean
Student A		6.22	6.22	9.33	7.26
Student B	9.33	12.44	12.44		11.41
It Mean	9.33	9.33	9.33	9.33	9.33

The adjusted mean scoring rate for Student B is now higher than that for Student A, but not by a factor of 2. Just looking at the numbers for this example, it very clear that the data set is "incomplete". The "missing" data from Student A on item H and from Student B on Item K is distorting the results. The session quotient method of transforming the data offsets the distortion partially, but not completely.

And in this example, adjusting the item rates, using the session mean quotients, is not very useful, as the session means were identical, and the session mean quotients were all unity. It follows that iterations would not achieve much, because no matter how many times you divide by one, you move no further forward.

A closer look at scoring rates

2009-10-09T01:38:00.000-07:00

In my blog of 25 August, I described some iterative transformations on scoring rate data from a computer based arithmetic test. I said I would report the results of further iterations, if I liked them, and from the time that has past it should be obvious to anyone reading this that I didn't.

The transformations were based on what I called the scoring rate quotient (SRQ). Essentially I divided the scoring rate for every item in every test session by the mean of all scoring rates for every item in every test session to produce the SRQ for individual session-item combinations and to calculate the mean SRQ for every session and for every item.

To illustrate, imagine two students, A and B, addressing two items, I and J. Imagine in this case that Student B scores at twice the rate of Student A and that Item I is twice as difficult as Item J. The raw scoring rates might look as follows:

Raw Rates	Item I	Item J	Session Mean
Student A	4	8	6
Student B	8	16	12
Item Mean	6	12	9

The scoring rate quotients would then be as follows:

Quotients	Item I	Item J	Session Mean
Student A	0.44	0.89	0.67
Student B	0.89	1.78	1.33
Item Mean	0.67	1.33	1.00

The session quotients can then be used to recalculate the item rates.

Adjusted Rates	Item I	Item J	Session Mean
Student A	6	12	9
Student B	6	12	9
Item Mean	6	12	9

Or the item quotients can then be used to recalculate the session rates.

Adjusted Rates	Item I	Item J	Session Mean
Student A	6	6	6
Student B	12	12	12
Item Mean	9	9	9

Expressing this algebraically, the means are calculated as follows:

Session Mean A	=	(R_IA + R_JA)/2	(1)
Session Mean B	=	(R_I_B + R_J_B)/2
Item Mean I	=	(R_IA + R_IB)/2
Item Mean J	=	(R_J_A + R_J_B)/2

Grand Mean A

(R_IA + R_{JA +}R_I_B + R_J_B)/4

(2)

The scoring rate quotients are then calculated by dividing the raw scoring rates by the grand mean:

SRQ IA	=	4R_IA/(R_IA + R_{JA +}R_I_B + R_J_B)	(3)
SRQ IB	=	4R_IB/(R_IA + R_{JA +}R_I_B + R_J_B)
SRQ JA	=	4R_JA/(R_IA + R_{JA +}R_I_B + R_J_B)
SRQ JB	=	4R_JB/(R_IA + R_{JA +}R_I_B + R_J_B)

The session mean quotients are then:

Session A Mean Quotient	=	4(R_IA + R_JA)/2(R_IA + R_{JA +}R_I_B + R_J_B)	(4)
	=	2(R_IA + R_JA)/(R_IA + R_{JA +}R_I_B + R_J_B)
Session B Mean Quotient	=	2(R_IB + R_JB)/(R_IA + R_{JA +}R_I_B + R_J_B)

The item mean quotients are:

	Item I Mean Quotient	=	2(R_IA + R_IB)/(R_IA + R_{JA +}R_I_B + R_J_B)
	Item J Mean Quotient	=	2(R_J_A + R_J_B)/(R_IA + R_{JA +}R_I_B + R_J_B)

And the grand mean of the quotients is:

	Grand Mean SRQ	=	4(R_IA + R_{JA +}R_I_B + R_J_B)/4(R_IA + R_{JA +}R_I_B + R_J_B)	(5)
		=	1

The adjusted item rates are calculated by dividing the raw item rates by the session mean quotients.

Adj Item IA	=	R_IA(R_IA + R_{JA +}R_I_B + R_J_B)/2(R_IA + R_JA)	(6)
Adj Item JA	=	R_JA(R_IA + R_{JA +}R_I_B + R_J_B)/2(R_IA + R_JA)
Adj Item IB	=	R_IB(R_IA + R_{JA +}R_I_B + R_J_B)/2(R_IB + R_JB)
Adj Item IB	=	R_IB(R_IA + R_{JA +}R_I_B + R_J_B)/2(R_IB + R_JB)

now we have stipulated that item I is twice as hard as item J so:

	R_JA	=	2R_IA	(7)
and	R_JB	=	2R_IB

So we can re-write expression 6 as:

	Adj Item IA	=	R_IA(R_IA + 2R_{IA +}R_I_B + 2R_I_B)/2(R_IA + 2R_IA)
		=	R_IA(3R_IA + 3R_I_B)/6R_IA
		=	(3R_IA + 3R_I_B)/6
		=	(R_IA + R_I_B)/2	(8)
	Adj Item IB	=	R_IB(R_IA + 2R_{IA +}R_I_B + 2R_I_B)/2(R_IB + 2R_IB)
		=	R_IB(3R_IA + 3R_I_B)/6R_IB
		=	(R_IA + R_I_B)/2
so	Adj Item IA	=	Adj Item IB

Thus the adjusted item rate for item I is identical for both sessions, and also equal to the item mean for item I. The same is true for item J.

Of course this is the special case envisaged by Rasch, where all items are completed by all students. It was nice to work through this special case, because, in my mind at least, it indicates that a single pass transformation is sufficient, and that there is no need for multiple iterations.

In my next blog, I shall have a closer look at the more general case where not all items are completed by all students.

UK Car Hire - caveat emptor

2009-10-08T21:16:00.001-07:00

The Web is a great boon to travellers. Where once you had to sit like a prune on a travel agents chair while they fiddled about for hours on a computer, now you can tailor make your own holiday from your own living room.

As well as the vendor sites, there are these web sites, which purport to search for and sort whatever you are looking for. At the top of the list are what purport to be the best bargains, and the unwary might simply click on these and look no further.

If you type UK car hire into Google, an array of sites like this one appear high on the list. It invites you to enter dates, a pickup location, and a car type, and then runs a search "of up to 40 companies" for you.

A couple of things should be born in mind when interpreting these results. One is that only 4 companies actually have representation at the airport. From memory these are Avis, Budget, Europcar, and Hertz. The others are scattered through West London, take ages to pick you up, and are hellish difficult to find when you need to drop the car off.

Another is that the "search" website quotes a very bare hire price. When I ran a search a couple of weeks ago, Thrifty quoted £322 for 9 days, which is approximately £ 36 a day - and very reasonable it seemed. They told me in advance that 2 child seats would add £5 a day each, bringing the rental to £46 a day - still quite reasonable. What they did not tell me that collision damage waiver, which used to be about £5 a day, was now an extra £18 a day. That, and a few other extras, brought the total price for the nine days to £649, which was more than double the original quote at nearly £76 a day. They also offered to sting me for even more if I wanted automatic transmission.

So while the "search" websites might be a useful first step to identify suitable car models, the wary traveller should then go to the four airport based companies and get detailed quotes including all extras, collision damage waiver, and automatic transmission, if required.

Acronis True Image Hanging

2009-09-20T21:21:00.000-07:00

One of my clients uses Acronis True Image Home for their nightly back-up. I don't know the product well, but I like what I've seen so far - mostly. It seems pretty thorough. The backups run like clockwork every night. And I have successfully recovered a system which caught a nasty virus, such that a reformat was the only cost effective fix.

But the other day, the front end application, the management console, would not start. It just froze, with a flash screen reporting that it was checking disk D. Quite why it needs to check Disk D before opening is a mystery, especially because it is not included in the backup and it need never need be read from. On the machine in question D is the manufacturer's "recovery" sector. Quite frankly they are a waste of space, and the word recovery is a complete misnomer - "Factory Reset" sector would be more honest and appropriate. And given that nothing useful is ever written there, it mystifies me that Acronis should hang while trying to read that disk sector.

Be that as it may, it was hanging and I was in a quandary as to what to do. I tried going away and coming back a few hours later. It was stress reducing, but it didn't fix the problem. I tried searching on strings like "Acronis True Image Hanging", but all that told me was that Acronis seems to hang a lot, in a wide range of circumstances, and there doesn't seem to be any consensus on a fix.

So if there is something I would criticise about Acronis it is the heavy reliance on wizards. Perhaps there is a setting somewhere, which gives an "expert" view, and enables manual editing of tasks, but I certainly haven't found it.

My dilemma was increased by the fact that the nightly backup was working perfectly. All I wanted to do was change the backup folder, to initiate a new full backup and a new month long string of incremental backups. I didn't want to fiddle around uninstalling and reinstalling Acronis, because then I'd lose my working scheduled task. I was quite tempted to run away, and not tell the client that anything was wrong, and let the incremental backups just go on for years and years in the same folder. But I decided that would be irresponsible.

After much deliberation I decided that there had to be a script somewhere, controlling the scheduled tasks, so I set about looking for it. I had a look in the "program files" folder, but there was nothing very promising there. All the dates were way too old. So I changed the folder settings to show both hidden folders and protected operating system files, and went to hunt for application data in documents and settings (all of this is in XP pro by the way). I first looked in the user folder, but there was nothing for Acronis there.

Then I remembered the option in the standard Windows install which says "Do you want this program to be available to all users?" So I checked out All Users\Application Data and sure enough, there was a directory called "scripts". Bingo! The file had a very funny name, and I won't print it here in case some malicious bot is probing my blog, but sure enough it opened with notepad, and it was just an ordinary text file with a script in full English. I hope no one from Acronis reads this and encrypts the next edition, because it made me like the product more. The path to the backup file was easy to find, and I just had to modify two characters to change it to the new folder for the current month.

To my enormous surprise, the script ran perfectly that night, and left the new full backup in the new folder as I intended. Next month I'll just go straight into the script and not bother with the GUI.

Learning Java

HTML tag revisited

Bringing a Custom Swing Component to Life

Creating a Custom Swing Component

Mixing and Matching

Security Points

Add User to Database

Check Login Details against the Database

JavaScript Business Rules

PHP Business Rules

Creating a login page

Web site redesign

Applet to AJAX to PHP methodology

Applet to JavaScript to PHP post to Database

Pass SQL command from Applet to JavaScript function

PHP and JavaScript/AJAX database query

Simple Web Application – Delete Data line

Simple Web Application – Add Data Form

Simple Web Application – Data edit form

Simple Web Application – Data Display page

A closer look at PHP database update options

Applet to JavaScript Communication

LiveConnect and JavaScript Wrappers

Javascript to Applet Communication - Example 2

Java Applet/Javascript Communication

An Historical History of Java Applets

Java Applet Revision

Asynchronous JavaScript and XML (AJAX)

Document Object Model (DOM)

eXtensible Markup Language (XML)

JavaScript by w3schools

Web language Revision

Mozilla JavaScript Learning Material

Playing with PHP

J2EE

A few notes on JavaScript

Forum Comments on Java Applets

Webcity

Limitations of Java Applets

Microsoft Internet Information Services (IIS)

Running Code for a GUI Applet

Creating a MySQL database for a GUI applet

Further Diagnostics with MySQL

References to the MySQL manual

Diagnostics with MySQL

More ambitious MySQL-JDBC examples

More simple MySQL-JDBC examples

Encoding a local connection to MySQL

MySQL versus Derby - file locations

MySQL versus Derby - JDBC code

Running MySQL from the command screen

Installing MySQL

Rasch Itembank Blog

Rounding up the year

Checking the database

MS Outlook Slow to Start

Revisiting old code

Conundrum

Structural Changes to the Repository

Updating Source Code

Parameter Estimation

Probability Theory

Posting Source Code

Going Open Source

The nub

Marketing

More on Scoring Rates

A closer look at scoring rates

UK Car Hire - caveat emptor

Acronis True Image Hanging

HTML
tag revisited