Sunday, June 27, 2010

Checking the database

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.

Thursday, June 10, 2010

MS Outlook Slow to Start

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.

Tuesday, June 8, 2010

Revisiting old code

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.