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.

Comments

Post a Comment

Popular posts from this blog

A few notes on JavaScript

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
Read more

Forum Comments on Java Applets

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 y
Read more

Creating a Custom Swing Component

Image
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
Read more