# If you store histogram and array in file, remove "memory" from protocol
#

from java.io import *
from jhplot import *
from java.sql import *
from java.lang import Class
from java.util import Properties


def putObject(h): # convert to bytes 
     bStr=ByteArrayOutputStream()
     objOut=ObjectOutputStream(bStr)
     objOut.writeObject(h)
     objOut.flush()
     objOut.close()
     bStr.close()
     return bStr.toByteArray()

def getObject(rs,key): # read an object using key 
     data = rs.getBytes(key)
     bInStream=ByteArrayInputStream(data)
     objIn= ObjectInputStream(bInStream)
     return objIn.readObject();

driver = "org.apache.derby.jdbc.EmbeddedDriver"
protocol = "jdbc:derby:memory:"
dbName="derbyDB"
table="location"

Class.forName(driver).newInstance()
props = Properties();
props.put("user", "jhepwork")
props.put("password", "secret")

######### write histogram and 2D in  database
scon=protocol+dbName+";create=true"
conn = DriverManager.getConnection(scon, props)
s = conn.createStatement()

p1=P1D("test")
p1.add(1,2); p1.add(2,3)
h1=H1D("histo",10,-1,1)
h1.fillGauss(100,0,1)

try:
  s.execute("drop table "+table) 
except SQLException: print "No need to remove table"

s.execute("create table "+table+\
          "(name varchar(40), data blob(16M))")
pstmt = conn.prepareStatement("Insert Into "+table+" Values(?, ?)")
pstmt.setString(1, "p1d") 
pstmt.setBytes(2, putObject(p1))
pstmt.execute()
conn.commit() 

pstmt = conn.prepareStatement("Insert Into "+table+" Values(?, ?)")
pstmt.setString(1, "histogram")
pstmt.setBytes(2, putObject(h1))
pstmt.execute()
conn.commit()
s.close()
conn.commit()

######### read in memory database 
scon=protocol+dbName+";create=false"
conn = DriverManager.getConnection(scon, props)

query="SELECT * FROM "+table
s = conn.createStatement()
rs = s.executeQuery(query)
while (rs.next()):
     id = rs.getString("name")
     obj = getObject(rs,"data")
     print obj.toString()
s.close()

try:
 DriverManager.getConnection("jdbc:derby:;shutdown=true")
except SQLException: print "all done"