After having scoured some of the materials available in the internet for the working example of the hsqldb database with jython I was frustrated to see materials pointing to some IBM proprietary materials that looked not so jythonic either.
I took the long route to solve my problems myself with the documentation at hand and here is what I came up with for someone willing not to re-invent the wheel:
from com.ziclix.python.sql import zxJDBC
from org.apache.log4j import *
"""
This class works fine for hsqldb databases
App specific global settings done here.
Change this if you want to setup this class for your environment.
"""
GLOB_VARS = { "GLOBAL_dbloc" : "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\hsqldbs'.split("\\")) + "\\"
,"GLOBAL_dbname" : "firsthsqldb"
,"GLOBAL_dbproperties" : ";shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create="
,"GLOBAL_createdb" : "false"
,"GLOBAL_driver" : "org.hsqldb.jdbcDriver"
,"GLOBAL_urlPrefix" : "jdbc:hsqldb:file:"
,"GLOBAL_user" : "sa"
,"GLOBAL_password" : ""
,"GLOBAL_LogfileCTX" : "HSQLDB"
,"GLOBAL_LogfileConfig" : "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\hsqldbs'.split("\\")) + "\\" + "log4j.properties"
,"GLOBAL_txtFileHasHdr" : "false"
,"GLOBAL_txtFileProps" : ";fs=,;all_quoted=true;encoding=UTF-8;ignore_first="
,"GLOBAL_txtFileExtn" : ".csv"
}
class HSQLDB(object):
"""
HSQLDB.jy Ver 1.0 27-Nov-16 Initial write
Author: Suprio Mukherjee
This class provides the following methods:
__init__() : Constructor is called whenever the object is created.
Valid parameters Optional and positional:
1. createdb = GLOBAL_createdb
2. dbname = GLOBAL_dbname
3. dbloc = GLOBAL_dbloc
constatus() : Displays the database location and the status of connection
and returns [1=connected 0=not connected]
executesql() : Executes an SQL query without output.
getmetadata(tablename) : Sends the metadata for the table.
createtxttbl() : Provides file interface by name.
Ex: createtxttbl("TEMP_TABLE",{1:"TESTCOL1 VARCHAR(10)",2:"TESTCOL2 INT"})
Creates a Table TEMP_TABLE on filesystem as a .CSV File and creates the columns in the
order given for key value. Important for Merge statements at atrget.
__del__() : Closes the database and performs a shutdown.
"""
def __init__(self, createdb = GLOB_VARS.get("GLOBAL_createdb"), dbname = GLOB_VARS.get("GLOBAL_dbname"), dbloc = GLOB_VARS.get("GLOBAL_dbloc")):
#-- No connectiontion
self.status = 0
self.dbloc = dbloc
self.driver = GLOB_VARS.get("GLOBAL_driver")
self.url = GLOB_VARS.get("GLOBAL_urlPrefix") + self.dbloc + dbname
self.user = GLOB_VARS.get("GLOBAL_user")
self.passwd = GLOB_VARS.get("GLOBAL_password")
self.files = {}
self.log = Logger.getLogger(GLOB_VARS.get("GLOBAL_LogfileCTX"))
#----------use the config data in the properties file
PropertyConfigurator.configure(GLOB_VARS.get("GLOBAL_LogfileConfig"))
self.log.info('This is the start of the log file')
self.log.info('Try to connect to a database..')
#------ Try to connect to a database ------
try:
self.conn = zxJDBC.connect(self.url + GLOB_VARS.get("GLOBAL_dbproperties") + createdb, self.user, self.passwd, self.driver)
except zxJDBC.Error, e:
self.log.warn("Problem connecting to database." + str(e))
raise Exception ("Problem connecting to database.",e)
else:
#-- Connection successful and a connection exists
self.log.info('Database connected and connection opened.')
self.status = 1
self.cur = self.conn.cursor()
def getmetadata(self, sql_or_tablename):
"""
getmetadata(sql_or_tablename): Sends the metadata for the table
as a tuple.
"""
if self.status == 1:
header =""
stmt = "SELECT * FROM (" + sql_or_tablename + ") WHERE 1=2"
self.log.debug('Metadata fetching for ' + stmt)
try:
self.cur.execute(sql_or_tablename)
except zxJDBC.Error, e:
self.log.warn(str(e) + sql_or_tablename)
self.__del__()
raise Exception(e, sql_or_tablename)
else:
for meta in self.cur.description:
header = header + meta[0] + ","
header = header[:-1]
return header.split(",")
else:
return "No Connection"
def createtxttbl(self, tablename="DUMMYTABLE", cols={1:"DUMMY CHAR(1)",}, hasheader = GLOB_VARS.get("GLOBAL_txtFileHasHdr"), properties = GLOB_VARS.get("GLOBAL_txtFileProps")):
"""
createtxttbl() : Provides file interface by name.
"""
if self.status == 1:
columns =""
tabletext = 'CREATE TEXT TABLE IF NOT EXISTS ' + tablename + "("
for c in range(0,len(cols),1):
columns = columns + ", "+ cols.get(c + 1)
clolumns = columns[1:]
tabletext = tabletext + clolumns + ")"
self.log.debug('Create Table text: ' + tabletext)
try:
self.cur.execute(tabletext)
except zxJDBC.Error, e:
self.log.warn(str(e) + tabletext)
self.__del__()
raise Exception(e, tabletext)
else:
self.log.debug('Success ' + self.dbloc + tablename + ' Created.')
tabletext = "SET TABLE " + tablename + " SOURCE '" + tablename + GLOB_VARS.get("GLOBAL_txtFileExtn") + properties + hasheader + "'"
self.log.debug('Trying to Source the table' + tabletext)
try:
self.cur.execute(tabletext)
except zxJDBC.Error, e:
self.log.warn(str(e) + tabletext)
self.__del__()
raise Exception(e, tabletext)
else:
self.files[tablename] = self.dbloc + tablename
self.log.info('Success ' + self.dbloc + tablename + ' Sourced for Rear and Write.')
return tablename
else:
return "No Connection"
def executesql(self, sql):
"""
executesql(sql): Sends the data for the sql without
header in an iterable object of tuplesonly if the
sql is a select. otherwise sends the status if one.
"""
sql = " ".join(map(lambda x: x.strip(),sql.split('\n')))
if self.status == 1:
self.log.debug('SQL received and transformed :' + sql)
try:
self.log.debug('Tring to execute the SQL')
self.cur.execute(sql)
except zxJDBC.Error, e:
self.log.warn(str(e) + sql)
self.__del__()
raise Exception(e, sql)
else:
if self.cur.description is not None:
self.log.debug('Sending results ..')
return self.cur.fetchall()
else:
if (sql == "COMMIT" or sql =="commit") :
self.log.debug('Committed.')
return "Committed."
else:
self.log.debug('SQL Execution was Successful.')
return "Execution was Successful. "
else:
return "No Connection"
def constatus(self):
"""
constatus() : Displays the database location and the
status of connection.
"""
if self.status == 1 :
self.log.info("Connected to .. " + self.url + " using:" + self.driver + " as:" + self.user)
self.log.info("Various other Global parameters are :")
for propname in GLOB_VARS.iterkeys():
self.log.info(" %30s : %s" %(propname.ljust(30,' '), GLOB_VARS.get(propname)))
else:
print 'No Connection'
return self.status
def __del__(self):
"""
__del__(): Closes the database and performs a shutdown.
Called from various exceptions and by the user
explicitly to close the connection.
"""
if self.status == 1 :
if len(self.files):
for k in self.files.iterkeys():
try:
self.log.info("Closing file " + self.files.get(k))
self.cur.execute(" SET TABLE " + k + " SOURCE OFF")
except Exception, e:
self.log.warn("Error Closing file " + self.files.get(k) + str(e))
else:
self.log.info("File closed " + self.files.get(k))
self.files.clear()
if self.cur:
self.log.info("Closing DB Cusror..")
self.cur.close()
if self.conn:
self.log.info("Closing DB conn..")
self.log.info("Shutting down.. " + self.url)
self.conn.close()
self.status = 0 #-- No connectiontion
self.log.info("Database shutdown normally")
self.log.info("Shutting down Log.")
self.log.shutdown()
return "Database shutdown normally"
else:
return "Nothing to close"
def check_exists(app, object_name ,object_type ):
sql = "SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA." + object_type + "S WHERE " + object_type + "_SCHEMA ='PUBLIC' AND " + object_type + "_NAME='" + object_name + "'"
try:
if app.executesql(sql):
return 1
else:
return 0
except Exception, e:
app.__del__()
raise e
if __name__ == '__main__':
App = HSQLDB()
App.constatus()
if not check_exists(App,"SEQ_LOGCNT","SEQUENCE"):
print App.executesql("CREATE SEQUENCE IF NOT EXISTS SEQ_LOGCNT AS BIGINT START WITH 1 INCREMENT BY 1")
if not check_exists(App,"APPLICATION_LOGS","TABLE"):
print App.executesql("CREATE TABLE IF NOT EXISTS APPLICATION_LOGS (ID INT PRIMARY KEY, NAME VARCHAR(12), ATTIME DATE)")
print App.executesql("INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)")
print App.executesql("COMMIT")
if not check_exists(App,"APPLICATION_USAGE","TABLE"):
print App.createtxttbl("APPLICATION_USAGE", {1:"S_NO BIGINT", 2:"USER VARCHAR(12)", 3:"LOGGED_ON_AT DATE"})
print App.executesql("""MERGE INTO APPLICATION_USAGE
USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k)
ON APPLICATION_USAGE.S_NO = VALS.i
WHEN NOT MATCHED THEN
INSERT VALUES VALS.i, VALS.j, VALS.k""")
print App.executesql("COMMIT")
App.__del__()
App.constatus()
When I checked the folder after running this code in textpad surely there was the files that I were hoping to see:
And this is the run log from textpad:
23:11:20,074 | INFO | [HSQLDB] This is the start of the log file
main23:11:20,074 | INFO | [HSQLDB] Try to connect to a database..
main23:11:20,979 | INFO | [hsqldb.db..ENGINE] open start - state not modified
main23:11:21,073 | INFO | [HSQLDB] Database connected and connection opened.
main23:11:21,088 | INFO | [HSQLDB] Connected to .. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb using:org.hsqldb.jdbcDriver as:sa
main23:11:21,088 | INFO | [HSQLDB] Various other Global parameters are :
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_createdb : false
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_LogfileConfig : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\log4j.properties
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_txtFileHasHdr : false
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_txtFileProps : ;fs=,;all_quoted=true;encoding=UTF-8;ignore_first=
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_LogfileCTX : HSQLDB
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_dbloc : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_password :
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_dbproperties : ;shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create=
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_driver : org.hsqldb.jdbcDriver
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_dbname : firsthsqldb
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_user : sa
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_txtFileExtn : .csv
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_urlPrefix : jdbc:hsqldb:file:
main23:11:21,120 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA ='PUBLIC' AND SEQUENCE_NAME='SEQ_LOGCNT'
main23:11:21,120 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,135 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,151 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_LOGS'
main23:11:21,151 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,151 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,151 | DEBUG | [HSQLDB] SQL received and transformed :INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)
main23:11:21,151 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,166 | DEBUG | [HSQLDB] SQL Execution was Successful.
mainExecution was Successful.
23:11:21,166 | DEBUG | [HSQLDB] SQL received and transformed :COMMIT
main23:11:21,166 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,182 | DEBUG | [HSQLDB] Committed.
mainCommitted.
23:11:21,182 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_USAGE'
main23:11:21,182 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,182 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,182 | DEBUG | [HSQLDB] SQL received and transformed :MERGE INTO APPLICATION_USAGE USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k) ON APPLICATION_USAGE.S_NO = VALS.i WHEN NOT MATCHED THEN INSERT VALUES VALS.i, VALS.j, VALS.k
main23:11:21,198 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,198 | DEBUG | [HSQLDB] SQL Execution was Successful.
mainExecution was Successful.
23:11:21,213 | DEBUG | [HSQLDB] SQL received and transformed :COMMIT
main23:11:21,213 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,213 | DEBUG | [HSQLDB] Committed.
mainCommitted.
23:11:21,213 | INFO | [HSQLDB] Closing DB Cusror..
main23:11:21,213 | INFO | [HSQLDB] Closing DB conn..
main23:11:21,213 | INFO | [HSQLDB] Shutting down.. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb
main23:11:21,260 | INFO | [hsqldb.db..ENGINE] Database closed
main23:11:21,369 | INFO | [HSQLDB] Database shutdown normally
main23:11:21,369 | INFO | [HSQLDB] Shutting down Log.
mainNo Connection
Tool completed successfully
The following were my log4j.properties file contents:
And the following was the log file generated:
2016-12-02 23:11:20,INFO,HSQLDB This is the start of the log file
2016-12-02 23:11:20,INFO,HSQLDB Try to connect to a database..
2016-12-02 23:11:20,INFO,hsqldb.db..ENGINE open start - state not modified
2016-12-02 23:11:21,INFO,HSQLDB Database connected and connection opened.
2016-12-02 23:11:21,INFO,HSQLDB Connected to .. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb using:org.hsqldb.jdbcDriver as:sa
2016-12-02 23:11:21,INFO,HSQLDB Various other Global parameters are :
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_createdb : false
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_LogfileConfig : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\log4j.properties
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileHasHdr : false
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileProps : ;fs=,;all_quoted=true;encoding=UTF-8;ignore_first=
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_LogfileCTX : HSQLDB
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbloc : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_password :
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbproperties : ;shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create=
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_driver : org.hsqldb.jdbcDriver
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbname : firsthsqldb
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_user : sa
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileExtn : .csv
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_urlPrefix : jdbc:hsqldb:file:
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA ='PUBLIC' AND SEQUENCE_NAME='SEQ_LOGCNT'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_LOGS'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB SQL Execution was Successful.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :COMMIT
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Committed.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_USAGE'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :MERGE INTO APPLICATION_USAGE USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k) ON APPLICATION_USAGE.S_NO = VALS.i WHEN NOT MATCHED THEN INSERT VALUES VALS.i, VALS.j, VALS.k
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB SQL Execution was Successful.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :COMMIT
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Committed.
2016-12-02 23:11:21,INFO,HSQLDB Closing DB Cusror..
2016-12-02 23:11:21,INFO,HSQLDB Closing DB conn..
2016-12-02 23:11:21,INFO,HSQLDB Shutting down.. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb
2016-12-02 23:11:21,INFO,hsqldb.db..ENGINE Database closed
2016-12-02 23:11:21,INFO,HSQLDB Database shutdown normally
2016-12-02 23:11:21,INFO,HSQLDB Shutting down Log.
Do let me know If you face any problems and happy hsqldb-eeing :)
I took the long route to solve my problems myself with the documentation at hand and here is what I came up with for someone willing not to re-invent the wheel:
from com.ziclix.python.sql import zxJDBC
from org.apache.log4j import *
"""
This class works fine for hsqldb databases
App specific global settings done here.
Change this if you want to setup this class for your environment.
"""
GLOB_VARS = { "GLOBAL_dbloc" : "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\hsqldbs'.split("\\")) + "\\"
,"GLOBAL_dbname" : "firsthsqldb"
,"GLOBAL_dbproperties" : ";shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create="
,"GLOBAL_createdb" : "false"
,"GLOBAL_driver" : "org.hsqldb.jdbcDriver"
,"GLOBAL_urlPrefix" : "jdbc:hsqldb:file:"
,"GLOBAL_user" : "sa"
,"GLOBAL_password" : ""
,"GLOBAL_LogfileCTX" : "HSQLDB"
,"GLOBAL_LogfileConfig" : "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\hsqldbs'.split("\\")) + "\\" + "log4j.properties"
,"GLOBAL_txtFileHasHdr" : "false"
,"GLOBAL_txtFileProps" : ";fs=,;all_quoted=true;encoding=UTF-8;ignore_first="
,"GLOBAL_txtFileExtn" : ".csv"
}
class HSQLDB(object):
"""
HSQLDB.jy Ver 1.0 27-Nov-16 Initial write
Author: Suprio Mukherjee
This class provides the following methods:
__init__() : Constructor is called whenever the object is created.
Valid parameters Optional and positional:
1. createdb = GLOBAL_createdb
2. dbname = GLOBAL_dbname
3. dbloc = GLOBAL_dbloc
constatus() : Displays the database location and the status of connection
and returns [1=connected 0=not connected]
executesql() : Executes an SQL query without output.
getmetadata(tablename) : Sends the metadata for the table.
createtxttbl() : Provides file interface by name.
Ex: createtxttbl("TEMP_TABLE",{1:"TESTCOL1 VARCHAR(10)",2:"TESTCOL2 INT"})
Creates a Table TEMP_TABLE on filesystem as a .CSV File and creates the columns in the
order given for key value. Important for Merge statements at atrget.
__del__() : Closes the database and performs a shutdown.
"""
def __init__(self, createdb = GLOB_VARS.get("GLOBAL_createdb"), dbname = GLOB_VARS.get("GLOBAL_dbname"), dbloc = GLOB_VARS.get("GLOBAL_dbloc")):
#-- No connectiontion
self.status = 0
self.dbloc = dbloc
self.driver = GLOB_VARS.get("GLOBAL_driver")
self.url = GLOB_VARS.get("GLOBAL_urlPrefix") + self.dbloc + dbname
self.user = GLOB_VARS.get("GLOBAL_user")
self.passwd = GLOB_VARS.get("GLOBAL_password")
self.files = {}
self.log = Logger.getLogger(GLOB_VARS.get("GLOBAL_LogfileCTX"))
#----------use the config data in the properties file
PropertyConfigurator.configure(GLOB_VARS.get("GLOBAL_LogfileConfig"))
self.log.info('This is the start of the log file')
self.log.info('Try to connect to a database..')
#------ Try to connect to a database ------
try:
self.conn = zxJDBC.connect(self.url + GLOB_VARS.get("GLOBAL_dbproperties") + createdb, self.user, self.passwd, self.driver)
except zxJDBC.Error, e:
self.log.warn("Problem connecting to database." + str(e))
raise Exception ("Problem connecting to database.",e)
else:
#-- Connection successful and a connection exists
self.log.info('Database connected and connection opened.')
self.status = 1
self.cur = self.conn.cursor()
def getmetadata(self, sql_or_tablename):
"""
getmetadata(sql_or_tablename): Sends the metadata for the table
as a tuple.
"""
if self.status == 1:
header =""
stmt = "SELECT * FROM (" + sql_or_tablename + ") WHERE 1=2"
self.log.debug('Metadata fetching for ' + stmt)
try:
self.cur.execute(sql_or_tablename)
except zxJDBC.Error, e:
self.log.warn(str(e) + sql_or_tablename)
self.__del__()
raise Exception(e, sql_or_tablename)
else:
for meta in self.cur.description:
header = header + meta[0] + ","
header = header[:-1]
return header.split(",")
else:
return "No Connection"
def createtxttbl(self, tablename="DUMMYTABLE", cols={1:"DUMMY CHAR(1)",}, hasheader = GLOB_VARS.get("GLOBAL_txtFileHasHdr"), properties = GLOB_VARS.get("GLOBAL_txtFileProps")):
"""
createtxttbl() : Provides file interface by name.
"""
if self.status == 1:
columns =""
tabletext = 'CREATE TEXT TABLE IF NOT EXISTS ' + tablename + "("
for c in range(0,len(cols),1):
columns = columns + ", "+ cols.get(c + 1)
clolumns = columns[1:]
tabletext = tabletext + clolumns + ")"
self.log.debug('Create Table text: ' + tabletext)
try:
self.cur.execute(tabletext)
except zxJDBC.Error, e:
self.log.warn(str(e) + tabletext)
self.__del__()
raise Exception(e, tabletext)
else:
self.log.debug('Success ' + self.dbloc + tablename + ' Created.')
tabletext = "SET TABLE " + tablename + " SOURCE '" + tablename + GLOB_VARS.get("GLOBAL_txtFileExtn") + properties + hasheader + "'"
self.log.debug('Trying to Source the table' + tabletext)
try:
self.cur.execute(tabletext)
except zxJDBC.Error, e:
self.log.warn(str(e) + tabletext)
self.__del__()
raise Exception(e, tabletext)
else:
self.files[tablename] = self.dbloc + tablename
self.log.info('Success ' + self.dbloc + tablename + ' Sourced for Rear and Write.')
return tablename
else:
return "No Connection"
def executesql(self, sql):
"""
executesql(sql): Sends the data for the sql without
header in an iterable object of tuplesonly if the
sql is a select. otherwise sends the status if one.
"""
sql = " ".join(map(lambda x: x.strip(),sql.split('\n')))
if self.status == 1:
self.log.debug('SQL received and transformed :' + sql)
try:
self.log.debug('Tring to execute the SQL')
self.cur.execute(sql)
except zxJDBC.Error, e:
self.log.warn(str(e) + sql)
self.__del__()
raise Exception(e, sql)
else:
if self.cur.description is not None:
self.log.debug('Sending results ..')
return self.cur.fetchall()
else:
if (sql == "COMMIT" or sql =="commit") :
self.log.debug('Committed.')
return "Committed."
else:
self.log.debug('SQL Execution was Successful.')
return "Execution was Successful. "
else:
return "No Connection"
def constatus(self):
"""
constatus() : Displays the database location and the
status of connection.
"""
if self.status == 1 :
self.log.info("Connected to .. " + self.url + " using:" + self.driver + " as:" + self.user)
self.log.info("Various other Global parameters are :")
for propname in GLOB_VARS.iterkeys():
self.log.info(" %30s : %s" %(propname.ljust(30,' '), GLOB_VARS.get(propname)))
else:
print 'No Connection'
return self.status
def __del__(self):
"""
__del__(): Closes the database and performs a shutdown.
Called from various exceptions and by the user
explicitly to close the connection.
"""
if self.status == 1 :
if len(self.files):
for k in self.files.iterkeys():
try:
self.log.info("Closing file " + self.files.get(k))
self.cur.execute(" SET TABLE " + k + " SOURCE OFF")
except Exception, e:
self.log.warn("Error Closing file " + self.files.get(k) + str(e))
else:
self.log.info("File closed " + self.files.get(k))
self.files.clear()
if self.cur:
self.log.info("Closing DB Cusror..")
self.cur.close()
if self.conn:
self.log.info("Closing DB conn..")
self.log.info("Shutting down.. " + self.url)
self.conn.close()
self.status = 0 #-- No connectiontion
self.log.info("Database shutdown normally")
self.log.info("Shutting down Log.")
self.log.shutdown()
return "Database shutdown normally"
else:
return "Nothing to close"
def check_exists(app, object_name ,object_type ):
sql = "SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA." + object_type + "S WHERE " + object_type + "_SCHEMA ='PUBLIC' AND " + object_type + "_NAME='" + object_name + "'"
try:
if app.executesql(sql):
return 1
else:
return 0
except Exception, e:
app.__del__()
raise e
if __name__ == '__main__':
App = HSQLDB()
App.constatus()
if not check_exists(App,"SEQ_LOGCNT","SEQUENCE"):
print App.executesql("CREATE SEQUENCE IF NOT EXISTS SEQ_LOGCNT AS BIGINT START WITH 1 INCREMENT BY 1")
if not check_exists(App,"APPLICATION_LOGS","TABLE"):
print App.executesql("CREATE TABLE IF NOT EXISTS APPLICATION_LOGS (ID INT PRIMARY KEY, NAME VARCHAR(12), ATTIME DATE)")
print App.executesql("INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)")
print App.executesql("COMMIT")
if not check_exists(App,"APPLICATION_USAGE","TABLE"):
print App.createtxttbl("APPLICATION_USAGE", {1:"S_NO BIGINT", 2:"USER VARCHAR(12)", 3:"LOGGED_ON_AT DATE"})
print App.executesql("""MERGE INTO APPLICATION_USAGE
USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k)
ON APPLICATION_USAGE.S_NO = VALS.i
WHEN NOT MATCHED THEN
INSERT VALUES VALS.i, VALS.j, VALS.k""")
print App.executesql("COMMIT")
App.__del__()
App.constatus()
When I checked the folder after running this code in textpad surely there was the files that I were hoping to see:
And this is the run log from textpad:
23:11:20,074 | INFO | [HSQLDB] This is the start of the log file
main23:11:20,074 | INFO | [HSQLDB] Try to connect to a database..
main23:11:20,979 | INFO | [hsqldb.db..ENGINE] open start - state not modified
main23:11:21,073 | INFO | [HSQLDB] Database connected and connection opened.
main23:11:21,088 | INFO | [HSQLDB] Connected to .. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb using:org.hsqldb.jdbcDriver as:sa
main23:11:21,088 | INFO | [HSQLDB] Various other Global parameters are :
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_createdb : false
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_LogfileConfig : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\log4j.properties
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_txtFileHasHdr : false
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_txtFileProps : ;fs=,;all_quoted=true;encoding=UTF-8;ignore_first=
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_LogfileCTX : HSQLDB
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_dbloc : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\
main23:11:21,088 | INFO | [HSQLDB] GLOBAL_password :
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_dbproperties : ;shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create=
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_driver : org.hsqldb.jdbcDriver
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_dbname : firsthsqldb
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_user : sa
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_txtFileExtn : .csv
main23:11:21,104 | INFO | [HSQLDB] GLOBAL_urlPrefix : jdbc:hsqldb:file:
main23:11:21,120 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA ='PUBLIC' AND SEQUENCE_NAME='SEQ_LOGCNT'
main23:11:21,120 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,135 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,151 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_LOGS'
main23:11:21,151 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,151 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,151 | DEBUG | [HSQLDB] SQL received and transformed :INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)
main23:11:21,151 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,166 | DEBUG | [HSQLDB] SQL Execution was Successful.
mainExecution was Successful.
23:11:21,166 | DEBUG | [HSQLDB] SQL received and transformed :COMMIT
main23:11:21,166 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,182 | DEBUG | [HSQLDB] Committed.
mainCommitted.
23:11:21,182 | DEBUG | [HSQLDB] SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_USAGE'
main23:11:21,182 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,182 | DEBUG | [HSQLDB] Sending results ..
main23:11:21,182 | DEBUG | [HSQLDB] SQL received and transformed :MERGE INTO APPLICATION_USAGE USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k) ON APPLICATION_USAGE.S_NO = VALS.i WHEN NOT MATCHED THEN INSERT VALUES VALS.i, VALS.j, VALS.k
main23:11:21,198 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,198 | DEBUG | [HSQLDB] SQL Execution was Successful.
mainExecution was Successful.
23:11:21,213 | DEBUG | [HSQLDB] SQL received and transformed :COMMIT
main23:11:21,213 | DEBUG | [HSQLDB] Tring to execute the SQL
main23:11:21,213 | DEBUG | [HSQLDB] Committed.
mainCommitted.
23:11:21,213 | INFO | [HSQLDB] Closing DB Cusror..
main23:11:21,213 | INFO | [HSQLDB] Closing DB conn..
main23:11:21,213 | INFO | [HSQLDB] Shutting down.. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb
main23:11:21,260 | INFO | [hsqldb.db..ENGINE] Database closed
main23:11:21,369 | INFO | [HSQLDB] Database shutdown normally
main23:11:21,369 | INFO | [HSQLDB] Shutting down Log.
mainNo Connection
Tool completed successfully
The following were my log4j.properties file contents:
2016-12-02 23:11:20,INFO,HSQLDB This is the start of the log file
2016-12-02 23:11:20,INFO,HSQLDB Try to connect to a database..
2016-12-02 23:11:20,INFO,hsqldb.db..ENGINE open start - state not modified
2016-12-02 23:11:21,INFO,HSQLDB Database connected and connection opened.
2016-12-02 23:11:21,INFO,HSQLDB Connected to .. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb using:org.hsqldb.jdbcDriver as:sa
2016-12-02 23:11:21,INFO,HSQLDB Various other Global parameters are :
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_createdb : false
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_LogfileConfig : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\log4j.properties
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileHasHdr : false
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileProps : ;fs=,;all_quoted=true;encoding=UTF-8;ignore_first=
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_LogfileCTX : HSQLDB
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbloc : C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_password :
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbproperties : ;shutdown=true;sql.syntax_ora=true;setAutoCommit=false;hsqldb.script_format=3;textdb.allow_full_path=false;create=
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_driver : org.hsqldb.jdbcDriver
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_dbname : firsthsqldb
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_user : sa
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_txtFileExtn : .csv
2016-12-02 23:11:21,INFO,HSQLDB GLOBAL_urlPrefix : jdbc:hsqldb:file:
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA ='PUBLIC' AND SEQUENCE_NAME='SEQ_LOGCNT'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_LOGS'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :INSERT INTO APPLICATION_LOGS (ID, NAME, ATTIME) VALUES (NEXT VALUE FOR SEQ_LOGCNT, 'sa', CURRENT_DATE)
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB SQL Execution was Successful.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :COMMIT
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Committed.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :SELECT 1 FROM PUBLIC.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='PUBLIC' AND TABLE_NAME='APPLICATION_USAGE'
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Sending results ..
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :MERGE INTO APPLICATION_USAGE USING (SELECT ID, NAME, ATTIME FROM APPLICATION_LOGS) AS VALS(i, j, k) ON APPLICATION_USAGE.S_NO = VALS.i WHEN NOT MATCHED THEN INSERT VALUES VALS.i, VALS.j, VALS.k
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB SQL Execution was Successful.
2016-12-02 23:11:21,DEBUG,HSQLDB SQL received and transformed :COMMIT
2016-12-02 23:11:21,DEBUG,HSQLDB Tring to execute the SQL
2016-12-02 23:11:21,DEBUG,HSQLDB Committed.
2016-12-02 23:11:21,INFO,HSQLDB Closing DB Cusror..
2016-12-02 23:11:21,INFO,HSQLDB Closing DB conn..
2016-12-02 23:11:21,INFO,HSQLDB Shutting down.. jdbc:hsqldb:file:C:\\Users\\mukhers2\\Documents\\Jython\\hsqldbs\firsthsqldb
2016-12-02 23:11:21,INFO,hsqldb.db..ENGINE Database closed
2016-12-02 23:11:21,INFO,HSQLDB Database shutdown normally
2016-12-02 23:11:21,INFO,HSQLDB Shutting down Log.
Do let me know If you face any problems and happy hsqldb-eeing :)
No comments:
Post a Comment