Example of using JDBC to access Database table and extract values from Fudge Message


#1

Hi

Here is my simple example of reading database exchange table and extracting Blob detail field, converting to Fudge Message format and reading values.

import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.fudgemsg.FudgeContext;
import com.opengamma.master.exchange.ManageableExchange;
import com.opengamma.util.fudgemsg.OpenGammaFudgeContext;

public class Main {

private static Connection conn;
private static Blob blob;
private Statement st;
private static ResultSet rs;
private static Main db;
private static ManageableExchange exchange;
protected final FudgeContext FUDGE_CONTEXT;

public Main(String db_file_name_prefix) {
FUDGE_CONTEXT = OpenGammaFudgeContext.getInstance();
try {
conn = DriverManager.getConnection(“jdbc:hsqldb:” + db_file_name_prefix, “OpenGamma”, “OpenGamma”);
} catch (SQLException e) {
// e.printStackTrace();
}
}

public synchronized void query(String expression) {
try {
st = conn.createStatement();
} catch (SQLException e) {
// e.printStackTrace();
}
try {
rs = st.executeQuery(expression);
} catch (SQLException e) {
//e.printStackTrace();
}

try {
  while (rs.next()) {

    blob = rs.getBlob("DETAIL");

    byte[] bdata = new byte[0];
    try {
      bdata = blob.getBytes(1, (int) blob.length());
    } catch (SQLException e) {
      e.printStackTrace();
    }
    exchange = FUDGE_CONTEXT.readObject(ManageableExchange.class, new ByteArrayInputStream(bdata));
    System.out.println();
    System.out.println("-----------------------------------------");
    System.out.println("Detail bean : " + exchange.detail().bean());
    System.out.println("Exchange Name: " + exchange.getName());
    System.out.println("ISOMic: " + exchange.getISOMic());
    System.out.println("External Id Bundle: " + exchange.getExternalIdBundle());
    System.out.println("Region Id Bundle: " + exchange.getRegionIdBundle());
    System.out.println("Time Zone: " + exchange.getTimeZone());
    System.out.println("Detail: " + exchange.detail());


  }
} catch (SQLException e) {
  e.printStackTrace();
}

}

public static void main(String[] args) {

try {
  db = new Main("/home/cme_user/OG-Platform/examples/examples-simulated/data/masterdb/hsqldb/example-db");
  System.out.println("Database Accessed Successfully");
} catch (Exception ex1) {
  //ex1.printStackTrace();
  System.out.println("Database Accessed Failed");
}

db.query("SELECT * FROM PUBLIC.EXG_EXCHANGE");
System.out.println("Queried Table Successful");
try {
  conn.close();
} catch (SQLException e) {
  // e.printStackTrace();
}

}
}

I have the following exception errors at end of output, any idea how to solve? or better solutions?


Detail bean : ManageableExchange{uniqueId=DbExg~1000~0, externalIdBundle=Bundle[ISO_MIC~XLON], name=London Stock Exchange, regionIdBundle=Bundle[ISO_COUNTRY_ALPHA2~GB], timeZone=Europe/London, detail=[]}
Exchange Name: London Stock Exchange
ISOMic: XLON
External Id Bundle: Bundle[ISO_MIC~XLON]
Region Id Bundle: Bundle[ISO_COUNTRY_ALPHA2~GB]
Time Zone: Europe/London
Detail: ManageableExchange:detail=[]


Detail bean : ManageableExchange{uniqueId=DbExg~1001~0, externalIdBundle=Bundle[ISO_MIC~XNYS], name=New York Stock Exchange, regionIdBundle=Bundle[ISO_COUNTRY_ALPHA2~US], timeZone=America/New_York, detail=[]}
Exchange Name: New York Stock Exchange
ISOMic: XNYS
External Id Bundle: Bundle[ISO_MIC~XNYS]
Region Id Bundle: Bundle[ISO_COUNTRY_ALPHA2~US]
Time Zone: America/New_York
Detail: ManageableExchange:detail=[]

Exception in thread “main” org.fudgemsg.wire.FudgeRuntimeIOException
at org.fudgemsg.wire.FudgeDataInputStreamReader.next(FudgeDataInputStreamReader.java:199)
at org.fudgemsg.wire.FudgeMsgReader.readMessageEnvelope(FudgeMsgReader.java:170)
at org.fudgemsg.wire.FudgeMsgReader.nextMessageEnvelope(FudgeMsgReader.java:149)
at org.fudgemsg.wire.FudgeMsgReader.nextMessage(FudgeMsgReader.java:134)
at org.fudgemsg.mapping.FudgeObjectReader.read(FudgeObjectReader.java:121)
at org.fudgemsg.FudgeContext.readObject(FudgeContext.java:446)
at com.opengamma.examples.bloomberg.server.Main.showQuery(Main.java:60)
at com.opengamma.examples.bloomberg.server.Main.query(Main.java:46)
at com.opengamma.examples.bloomberg.server.Main.main(Main.java:89)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.io.EOFException
at java.io.DataInputStream.readShort(DataInputStream.java:315)
at org.fudgemsg.wire.FudgeDataInputStreamReader.consumeMessageEnvelope(FudgeDataInputStreamReader.java:384)
at org.fudgemsg.wire.FudgeDataInputStreamReader.next(FudgeDataInputStreamReader.java:185)
… 13 more