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.

No comments: