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.

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