Sunday, July 31, 2011

Microsoft Internet Information Services (IIS)

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).

Saturday, July 30, 2011

Running Code for a GUI Applet

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.

Thursday, July 28, 2011

Creating a MySQL database for a GUI applet

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 DATABASE
dbAMJ");
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.

Wednesday, July 27, 2011

Further Diagnostics with MySQL

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.

Tuesday, July 26, 2011

References to the MySQL manual

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.

Monday, July 25, 2011

Diagnostics with MySQL

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.

Sunday, July 24, 2011

More ambitious MySQL-JDBC examples

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:

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

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.

Saturday, July 23, 2011

More simple MySQL-JDBC examples

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.

Friday, July 22, 2011

Encoding a local connection to MySQL

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

Thursday, July 21, 2011

MySQL versus Derby - file locations

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:

  1. 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.
  2. 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:

  1. 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.
  2. 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.
  3. 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:

  1. 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.
  2. 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.

Tuesday, July 19, 2011

MySQL versus Derby - JDBC code

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:

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

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

public class Jdbc11 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

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.

Monday, July 18, 2011

Running MySQL from the command screen

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.