Saturday 3 December 2016

My tryst with Derby and Jython

Following my successful venture with hsqldb I was emboldened to try Apache Derby 13.1.3.1.1 formerly Cloudscape DB IBM (R) and it was also pleasantly inspiring. The code used is as follows:

from __future__ import with_statement
from com.ziclix.python.sql import zxJDBC
from org.apache.log4j import *

"""
        This class works fine for Derby 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\DerbyDB'.split("\\")) + "\\"
             ,"GLOBAL_dbname"             : "firstderby"
             ,"GLOBAL_dbautocommit"       : True
             ,"GLOBAL_dbCloseproperties"  : ";shutdown=true"
             ,"GLOBAL_createdb"           : "false"
             ,"GLOBAL_driver"             : "org.apache.derby.jdbc.EmbeddedDriver"
             ,"GLOBAL_urlPrefix"          : "jdbc:derby:"
             ,"GLOBAL_user"               : "APP"
             ,"GLOBAL_password"           : ""
             ,"GLOBAL_LogfileCTX"         : "DERBY"
             ,"GLOBAL_LogfileConfig"      : "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\DerbyDB'.split("\\")) + "\\" + "log4j_Derby.properties"
             ,"GLOBAL_txtFileHasHdr"      : "true"
             ,"GLOBAL_txtFileProps"       : "fs=,;all_quoted=true;encoding=UTF-8;file_loc=" + "\\\\".join(r'C:\Users\mukhers2\Documents\Jython\DerbyDB'.split("\\")) + "\\;ignore_first="
             ,"GLOBAL_txtFileExtn"        : ".csv"
             }


class DerbyApp(object):

    __Author__   = "Suprio Mukherjee"
    __Version__  = "V1.0"
    __Name__     = "DerbyApp.jy"

    def __init__(self, dbname = GLOB_VARS.get("GLOBAL_dbname"), createdb = GLOB_VARS.get("GLOBAL_createdb"), dbloc = GLOB_VARS.get("GLOBAL_dbloc")):

        self.status = 0 #-- No connectiontion
        self.dbname = dbname
        self.dbloc  = dbloc
        self.driver = GLOB_VARS.get("GLOBAL_driver")
        self.url    = GLOB_VARS.get("GLOBAL_urlPrefix") + self.dbloc + self.dbname +  ";create=" + createdb
        self.user   = GLOB_VARS.get("GLOBAL_user")
        self.passwd = GLOB_VARS.get("GLOBAL_password")

        self.log    = Logger.getLogger(GLOB_VARS.get("GLOBAL_LogfileCTX"))
        PropertyConfigurator.configure(GLOB_VARS.get("GLOBAL_LogfileConfig"))
        self.log.info('**Application %s is initializing' %(self.dbname))

        try:
            self.log.debug('Try to connect to a database..')
            self.conn = zxJDBC.connect(self.url, self.user, self.passwd, self.driver)

        except zxJDBC.Error, e:
            self.log.warn('Problem connecting to database ' + str(e))
            self.log.info('Closing the logger.')
            self.log.shutdown()
            raise Exception ('Problem connecting to database ', e)

        else:
            self.conn.autocommit = GLOB_VARS.get("GLOBAL_dbautocommit")
            self.log.debug('Connection is successful. Database is open.')
            self.status = 1
            self.cur    = self.conn.cursor()


    def executesql(self, sqlstmt):

        sqlstmt = " ".join(map(lambda x : x.strip(), sqlstmt.split('\n')))

        if self.status == 1:
            self.log.debug('SQL statement received and prepared. ' + sqlstmt)

            try:
                self.cur.execute(sqlstmt)

            except zxJDBC.Error, e:
                self.log.warn('SQL statement execution encountered ERROR: ' + str(e))
                self.__del__()
                raise Exception(e, sqlstmt)

            else:
                if self.cur.description is not None:
                    self.log.debug('Sending results ..')
                    return self.cur.fetchall()

                else:
                    if (sqlstmt == "COMMIT" or sqlstmt =="commit") :
                        self.log.debug('Committed.')
                        return "Committed."

                    else:
                        self.log.debug('SQL Execution was Successful.')
                        return "Execution was Successful. "
        else:
            return "No Connection"


    def getmetadata(self, sql_or_tablename, rec_seperator = ",", sendasstring="true"):
        """
            getmetadata(sql_or_tablename): Sends the metadata for the table
                                           as a tuple.
        """

        sql_or_tablename = " ".join(map(lambda x : x.strip(), sql_or_tablename.split('\n')))

        if self.status == 1:
            header = ""
            stmt   = "SELECT * FROM (" + sql_or_tablename + ")"

            try:
                self.log.debug('Metadata preparing for : ' + sql_or_tablename)
                self.cur.execute(stmt)

            except zxJDBC.Error, e:
                self.log.warn('SQL statement for metadata fetch encountered ERROR: ' + str(e))
                self.__del__()
                raise Exception(e, sql_or_tablename)

            else:
                self.log.debug('Metadata fetched. Preparing the resultset...')
                for meta in self.cur.description:
                    header = header + meta[0] + rec_seperator

                header = header[:-1]

                if sendasstring == "true":
                    self.log.debug('Returning as string..')
                    return header
                else:
                    self.log.debug('Returning as a tuple..')
                    return header.split(rec_seperator)
        else:
            return "No Connection"


    def getdata(self, tablename):

        tablename = " ".join(map(lambda x : x.strip(), tablename.split('\n')))

        if self.status == 1:
            tablename = sqlstmt = " ".join(map(lambda x : x.strip(), tablename.split('\n')))
            stmt      = "select * from (" + tablename + ")"

            try:
                self.log.debug('Trying to fetch data for : ' + tablename)
                self.cur.execute(stmt)

            except zxJDBC.Error, e:
                self.log.warn('SQL statement execution encountered ERROR: ' + str(e))
                self.__del__()
                raise Exception(e, stmt)

            else:
                self.log.debug('Sending the data .. ')
                return self.cur.fetchall()

        else:
            return "No Connection"


    def txtfile(self, filename = "SampleFile", tablename = None, hasheader = GLOB_VARS.get("GLOBAL_txtFileHasHdr"), properties = GLOB_VARS.get("GLOBAL_txtFileProps")):
        """
            createtxttbl() :
        """
        fileprops ={}
        for i in properties.split(';'):
            j = i.split('=')
            fileprops[j[0]]=j[1]

        filename  = fileprops.get("file_loc") + filename + GLOB_VARS.get("GLOBAL_txtFileExtn")
        has_quote = '"' if (fileprops.get("all_quoted") == "true") else ''
        self.log.debug('File : %s' %(filename))

        if tablename is None:
            try:
                self.log.debug('Trying to read from the file : %s' %(filename))

                with open(filename, 'r') as afile:
                    if hasheader == "true":
                        self.log.debug('Ignoring header')
                        afile.readline()

                    self.log.debug('Returning all lines of the file')
                    return afile.readlines()

            except Exception, e:
                self.log.warn('Error reading the file : %s' %(filename))
                self.__del__()
                raise Exception(e, filename)

        else:
            try:
                self.log.debug('Creating file : %s for write.' %(filename))

                with open(filename, 'w') as afile:
                    if hasheader == "true":
                        self.log.debug('Writing the header')
                        print >> afile, self.getmetadata(tablename, rec_seperator = fileprops.get("fs"), sendasstring="true")

                    for arecord_tup in self.executesql("SELECT * FROM " + tablename):
                        row = ""
                        for f in range(len(arecord_tup)):
                            row = row + fileprops.get("fs") + has_quote + '\"\"'.join(str(arecord_tup[f]).split('\"')) + has_quote

                        row = row[1:]
                        print >> afile , row

            except Exception, e:
                self.log.warn('Error writing to the file : %s' %(filename))
                self.__del__()
                raise Exception(e, filename, query, hasheader, properties, fileprops)

            else:
                self.log.debug("Sql execution results written to" + filename)
                return "Query executed Successfully"


    def constatus(self):

        if self.status == 1 :
            self.log.info('Connectec=d to .. ' + self.dbloc + self.dbname)
            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)))

        return self.status


    def check_exists(self, object_name ,object_type ):
        sql = "SELECT 1 FROM SYS.SYS" + object_type + "S A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = '" + GLOB_VARS.get("GLOBAL_user")  + "' AND " + object_type + "NAME='" + object_name + "'"

        if self.status == 1 :
            try:
                self.log.debug('Checking if the  %s: %s exists in the Database using the query : %s' %(object_type, object_name, sql))
                if self.executesql(sql):
                    self.log.debug('The %s : %s exists in the Database.' %(object_type, object_name))
                    return True

                else:
                    self.log.debug('%s %s not found in the database.' %(object_type, object_name))
                    return False

            except Exception, e:
                self.log.warn('Error encountered while checking for ' + object_name)
                self.__del__()
                raise e
        else:
            return Exception ("No connection to database")

    def __del__(self):

        if self.status == 1 :
            self.log.info('Closing DB Connection .. ' + self.dbloc + self.dbname)

            try:
                self.conn = zxJDBC.connect(GLOB_VARS.get("GLOBAL_urlPrefix") + self.dbloc + self.dbname + GLOB_VARS.get("GLOBAL_dbCloseproperties"),self.user,self.passwd,self.driver)

            except zxJDBC.Error, e:
                if str(e).find('XJ015'):
                    self.status = 0 #-- No connectiontion
                    self.log.info('DB Connection closed.')
                    self.log.info('Closing the logger.')
                    self.log.shutdown()
                    return "Database shutdown normally"

                else:
                    self.log.warn('Database shutdown failed ' + str(e))
                    return Exception(e)

        else:
            return "Nothing to close"

if __name__ == '__main__':

    App = DerbyApp()
    print App.constatus()

    if not App.check_exists('XXX', 'TABLE'):
        print App.executesql("CREATE TABLE " + GLOB_VARS.get("GLOBAL_user")  + ".XXX (ID BIGINT PRIMARY KEY, APPUSER VARCHAR(10), ATTIME DATE)")

    if not App.check_exists('SXXX', 'SEQUENCE'):
        print App.executesql("CREATE SEQUENCE " + GLOB_VARS.get("GLOBAL_user")  + ".SXXX AS BIGINT START WITH 1 INCREMENT BY 1 NO CYCLE")

    print App.executesql("INSERT INTO " + GLOB_VARS.get("GLOBAL_user")  + ".XXX (ID , APPUSER, ATTIME) " +\
                         "VALUES (NEXT VALUE FOR " + GLOB_VARS.get("GLOBAL_user")  + ".SXXX,'" +\
                                                     GLOB_VARS.get("GLOBAL_user")  + \
                                                     "', CURRENT DATE)"\
                        )

    print App.getmetadata('XXX')
    print App.getdata('XXX')
    print App.txtfile(filename = "SampleFile", tablename = GLOB_VARS.get("GLOBAL_user")  + ".XXX")
    print App.txtfile(filename = "SampleFile")
    App.__del__()

As you will see that the code is similar to the hsqldb but for a few places. The output for the execution in my textpad was as follows:

17:09:02,164 | INFO | [DERBY] **Application firstderby is initializing
 main17:09:02,164 | DEBUG | [DERBY] Try to connect to a database..
 main17:09:02,913 | DEBUG | [DERBY] Connection is successful. Database is open.
 main17:09:02,928 | INFO | [DERBY] Connectec=d to .. C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\firstderby
 main17:09:02,928 | INFO | [DERBY] Various other Global parameters are :
 main17:09:02,928 | INFO | [DERBY]   GLOBAL_createdb                : false
 main17:09:02,928 | INFO | [DERBY]   GLOBAL_LogfileConfig           : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\log4j_Derby.properties
 main17:09:02,928 | INFO | [DERBY]   GLOBAL_txtFileHasHdr           : true
 main17:09:02,928 | INFO | [DERBY]   GLOBAL_txtFileProps            : fs=,;all_quoted=true;encoding=UTF-8;file_loc=C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\;ignore_first=
 main17:09:02,928 | INFO | [DERBY]   GLOBAL_LogfileCTX              : DERBY
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_dbautocommit            : True
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_dbloc                   : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_password                :
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_dbCloseproperties       : ;shutdown=true
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_driver                  : org.apache.derby.jdbc.EmbeddedDriver
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_dbname                  : firstderby
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_user                    : APP
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_txtFileExtn             : .csv
 main17:09:02,944 | INFO | [DERBY]   GLOBAL_urlPrefix               : jdbc:derby:
 main1
17:09:02,959 | DEBUG | [DERBY] Checking if the  TABLE: XXX exists in the Database using the query : SELECT 1 FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND TABLENAME='XXX'
 main17:09:02,959 | DEBUG | [DERBY] SQL statement received and prepared. SELECT 1 FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND TABLENAME='XXX'
 main17:09:03,256 | DEBUG | [DERBY] Sending results ..
 main17:09:03,256 | DEBUG | [DERBY] The TABLE : XXX exists in the Database.
 main17:09:03,256 | DEBUG | [DERBY] Checking if the  SEQUENCE: SXXX exists in the Database using the query : SELECT 1 FROM SYS.SYSSEQUENCES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND SEQUENCENAME='SXXX'
 main17:09:03,256 | DEBUG | [DERBY] SQL statement received and prepared. SELECT 1 FROM SYS.SYSSEQUENCES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND SEQUENCENAME='SXXX'
 main17:09:03,303 | DEBUG | [DERBY] Sending results ..
 main17:09:03,303 | DEBUG | [DERBY] The SEQUENCE : SXXX exists in the Database.
 main17:09:03,303 | DEBUG | [DERBY] SQL statement received and prepared. INSERT INTO APP.XXX (ID , APPUSER, ATTIME) VALUES (NEXT VALUE FOR APP.SXXX,'APP', CURRENT DATE)
 main17:09:03,381 | DEBUG | [DERBY] SQL Execution was Successful.
 mainExecution was Successful.
17:09:03,396 | DEBUG | [DERBY] Metadata preparing for : XXX
 main17:09:03,599 | DEBUG | [DERBY] Metadata fetched. Preparing the resultset...
 main17:09:03,599 | DEBUG | [DERBY] Returning as string..
 mainID,APPUSER,ATTIME
17:09:03,599 | DEBUG | [DERBY] Trying to fetch data for : XXX
 main17:09:03,615 | DEBUG | [DERBY] Sending the data ..
 main[(1L, u'APP', datetime.date(2016, 12, 3)), (2L, u'APP', datetime.date(2016, 12, 3)), (3L, u'APP', datetime.date(2016, 12, 3)), (4L, u'APP', datetime.date(2016, 12, 3)), (5L, u'APP', datetime.date(2016, 12, 3)), (6L, u'APP', datetime.date(2016, 12, 3)), (7L, u'APP', datetime.date(2016, 12, 3)), (8L, u'APP', datetime.date(2016, 12, 3)), (9L, u'APP', datetime.date(2016, 12, 3)), (109L, u'APP', datetime.date(2016, 12, 3)), (110L, u'APP', datetime.date(2016, 12, 3)), (111L, u'APP', datetime.date(2016, 12, 3)), (112L, u'APP', datetime.date(2016, 12, 3)), (113L, u'APP', datetime.date(2016, 12, 3)), (114L, u'APP', datetime.date(2016, 12, 3)), (115L, u'APP', datetime.date(2016, 12, 3))]
17:09:03,646 | DEBUG | [DERBY] File : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
 main17:09:03,646 | DEBUG | [DERBY] Creating file : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv for write.
 main17:09:03,661 | DEBUG | [DERBY] Writing the header
 main17:09:03,693 | DEBUG | [DERBY] Metadata preparing for : APP.XXX
 main17:09:03,724 | DEBUG | [DERBY] Metadata fetched. Preparing the resultset...
 main17:09:03,739 | DEBUG | [DERBY] Returning as string..
 main17:09:03,739 | DEBUG | [DERBY] SQL statement received and prepared. SELECT * FROM APP.XXX
 main17:09:03,755 | DEBUG | [DERBY] Sending results ..
 main17:09:03,755 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,755 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,755 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,755 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,786 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,786 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,786 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,786 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,786 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,802 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,802 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,817 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,817 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,833 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,833 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,833 | DEBUG | [DERBY] Writing the rows...
 main17:09:03,833 | DEBUG | [DERBY] Sql execution results written toC:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
 mainQuery executed Successfully
17:09:03,864 | DEBUG | [DERBY] File : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
 main17:09:03,880 | DEBUG | [DERBY] Trying to read from the file : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
 main17:09:03,880 | DEBUG | [DERBY] Ignoring header
 main17:09:03,880 | DEBUG | [DERBY] Returning all lines of the file
 main['"1","APP","2016-12-03"\n', '"2","APP","2016-12-03"\n', '"3","APP","2016-12-03"\n', '"4","APP","2016-12-03"\n', '"5","APP","2016-12-03"\n', '"6","APP","2016-12-03"\n', '"7","APP","2016-12-03"\n', '"8","APP","2016-12-03"\n', '"9","APP","2016-12-03"\n', '"109","APP","2016-12-03"\n', '"110","APP","2016-12-03"\n', '"111","APP","2016-12-03"\n', '"112","APP","2016-12-03"\n', '"113","APP","2016-12-03"\n', '"114","APP","2016-12-03"\n', '"115","APP","2016-12-03"\n']
17:09:03,927 | INFO | [DERBY] Closing DB Connection .. C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\firstderby
 main17:09:04,005 | INFO | [DERBY] DB Connection closed.
 main17:09:04,005 | INFO | [DERBY] Closing the logger.
 main
Tool completed successfully


The file was also produced correctly:


The setting for the log4j was as follows:

#define loging level and output
log4j.rootLogger=debug, stdout, LOGFILE
#log4j.rootLogger=info, LOGFILE
# this 2 lines tie the apache logging into log4j
#log4j.logger.org.apache.axis.SOAPPart=DEBUG
#log4j.logger.httpclient.wire.header=info
#log4j.logger.org.apache.commons.httpclient=DEBUG

# where is the logging going.
# This is for std out and defines the log output format
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{HH:mm:ss,SSS} | %p | [%c] %m%n %t

#log it to a file as well. and define a filename, max file size and number of backups
log4j.appender.LOGFILE=org.apache.log4j.RollingFileAppender
log4j.appender.LOGFILE.File=C:\\Users\\mukhers2\\Documents\\Jython\\LOGS\\DERBY.log
log4j.appender.LOGFILE.MaxFileSize=100KB
# Keep one backup file
log4j.appender.LOGFILE.MaxBackupIndex=1

log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
# Pattern for logfile - only diff is that date is added
#log4j.appender.LOGFILE.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} | %p | [%c] %m%n
# Other Examples: only time, loglog level, loggerName
log4j.appender.LOGFILE.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss},%p,%c %m%n
#above plus filename, linenumber, Class Name, method name
#log4j.appender.LOGFILE.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss},%p,%c,%F,%L,%C{1},%M %m%n


The logfile produces the following output:

2016-12-03 17:09:02,INFO,DERBY **Application firstderby is initializing
2016-12-03 17:09:02,DEBUG,DERBY Try to connect to a database..
2016-12-03 17:09:02,DEBUG,DERBY Connection is successful. Database is open.
2016-12-03 17:09:02,INFO,DERBY Connectec=d to .. C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\firstderby
2016-12-03 17:09:02,INFO,DERBY Various other Global parameters are :
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_createdb                : false
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_LogfileConfig           : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\log4j_Derby.properties
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_txtFileHasHdr           : true
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_txtFileProps            : fs=,;all_quoted=true;encoding=UTF-8;file_loc=C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\;ignore_first=
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_LogfileCTX              : DERBY
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_dbautocommit            : True
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_dbloc                   : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_password                :
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_dbCloseproperties       : ;shutdown=true
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_driver                  : org.apache.derby.jdbc.EmbeddedDriver
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_dbname                  : firstderby
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_user                    : APP
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_txtFileExtn             : .csv
2016-12-03 17:09:02,INFO,DERBY   GLOBAL_urlPrefix               : jdbc:derby:
2016-12-03 17:09:02,DEBUG,DERBY Checking if the  TABLE: XXX exists in the Database using the query : SELECT 1 FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND TABLENAME='XXX'
2016-12-03 17:09:02,DEBUG,DERBY SQL statement received and prepared. SELECT 1 FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND TABLENAME='XXX'
2016-12-03 17:09:03,DEBUG,DERBY Sending results ..
2016-12-03 17:09:03,DEBUG,DERBY The TABLE : XXX exists in the Database.
2016-12-03 17:09:03,DEBUG,DERBY Checking if the  SEQUENCE: SXXX exists in the Database using the query : SELECT 1 FROM SYS.SYSSEQUENCES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND SEQUENCENAME='SXXX'
2016-12-03 17:09:03,DEBUG,DERBY SQL statement received and prepared. SELECT 1 FROM SYS.SYSSEQUENCES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE B.SCHEMANAME = 'APP' AND SEQUENCENAME='SXXX'
2016-12-03 17:09:03,DEBUG,DERBY Sending results ..
2016-12-03 17:09:03,DEBUG,DERBY The SEQUENCE : SXXX exists in the Database.
2016-12-03 17:09:03,DEBUG,DERBY SQL statement received and prepared. INSERT INTO APP.XXX (ID , APPUSER, ATTIME) VALUES (NEXT VALUE FOR APP.SXXX,'APP', CURRENT DATE)
2016-12-03 17:09:03,DEBUG,DERBY SQL Execution was Successful.
2016-12-03 17:09:03,DEBUG,DERBY Metadata preparing for : XXX
2016-12-03 17:09:03,DEBUG,DERBY Metadata fetched. Preparing the resultset...
2016-12-03 17:09:03,DEBUG,DERBY Returning as string..
2016-12-03 17:09:03,DEBUG,DERBY Trying to fetch data for : XXX
2016-12-03 17:09:03,DEBUG,DERBY Sending the data ..
2016-12-03 17:09:03,DEBUG,DERBY File : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
2016-12-03 17:09:03,DEBUG,DERBY Creating file : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv for write.
2016-12-03 17:09:03,DEBUG,DERBY Writing the header
2016-12-03 17:09:03,DEBUG,DERBY Metadata preparing for : APP.XXX
2016-12-03 17:09:03,DEBUG,DERBY Metadata fetched. Preparing the resultset...
2016-12-03 17:09:03,DEBUG,DERBY Returning as string..
2016-12-03 17:09:03,DEBUG,DERBY SQL statement received and prepared. SELECT * FROM APP.XXX
2016-12-03 17:09:03,DEBUG,DERBY Sending results ..
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Writing the rows...
2016-12-03 17:09:03,DEBUG,DERBY Sql execution results written toC:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
2016-12-03 17:09:03,DEBUG,DERBY File : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
2016-12-03 17:09:03,DEBUG,DERBY Trying to read from the file : C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\SampleFile.csv
2016-12-03 17:09:03,DEBUG,DERBY Ignoring header
2016-12-03 17:09:03,DEBUG,DERBY Returning all lines of the file
2016-12-03 17:09:03,INFO,DERBY Closing DB Connection .. C:\\Users\\mukhers2\\Documents\\Jython\\DerbyDB\firstderby
2016-12-03 17:09:04,INFO,DERBY DB Connection closed.
2016-12-03 17:09:04,INFO,DERBY Closing the logger.



Friday 2 December 2016

Working with HSQLDB and Jython Example

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 :)