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

Nostalgia

Could not help but post this amazing song that sums up exactly how i feel now now.
I mean how often does this happen in a lifetime??

https://www.youtube.com/watch?v=pzWnR9RwDcs

Life of a DBA - Cleaning the DataBase

Cleaning swabs., originally uploaded by suprio.

A very recent post that I have liked so much that I feel obliged to add it to my blog. Thanks Anwesha.

http://anwesha.blogspot.com/2009/02/life-of-dba.html

Abstract Monkharap... Well... almost abstract

10:27 AM
Made a call at 9:15 in the morning to hear the song of awekening. It sounded so cozy and so endearing and I almost felt warm and happy. Waiting anxiously to continue what I have set in motion long back...

2:15 PM
I made another call and this time the world darkened. As if time had conspired and as if my best friend died. I could barely succeed in keeping from choking over the knot in my chest and my breathing was uneven. (Thank heavens it was over the phone for boys dont cry do they?). I could not believe I was actually feeling a bit good about the episode in a very strange way... It was acting... and I felt God to that act... I was deep within torn between a wish to howl out in pain but there was this civilised me... telling me to be sober and to wish all the best... It was a very disturbing moment... a moment I could have casually avoided with a simple question long back... but how was I to know... you dont know till you ask.... how much you are worth. When you dont say anything it means you havent said anything! and I could bet my right hand that there was no commitment anywhere or between anyone... yet the try to develop something out of thin air was refreshing... I wished all the best with a Rock in my pocket... I will throw it at my luck in leisure.

4:31 PM
The shadow was lifted. All along this span My Earth was in the umbra of the Moon. The Celestrial transpiration to align themselves in line for the importance of the day in my life... spooky! but I just learnt and thought I'd document.
Today My Earth witnessed an eclipse of the Sun. Albeit Partial.

Anti-ageing

Raja stared at the haze, the morning coffee steaming in the small porcellean mug that he held to his lips, the morning was yet to begin and the fog yet to dissipate. Presently he looked over his shoulders to witness a colleague light up a ciggerette in the officially "No Smoking" zone and looked back over the lake that was barely a trace in the shifting fog. He thought about the things that has been keeping him aloof and bothered. Something about this fog also bothered him. Actually everything with a bit of intrigue or uncertainty nags Raja. His thoughts were interrupted:

The colleague called out... 'Mr Chatterjee would you like a fag?'

'No thanks! I've been trying to quit you see' Raja said politely and clenched his left fist in his pocket. It is difficult quitting a habit like smoking... but he will try... will he not? It will stop his ageing...says the medical Journal he read somehere.

It was very warm the night before and he had tossed and turned on his bed till 3. The climate was one reason alright but there was something else too. He had thought about the incident that night and wondered if he had been correct. Did he hear clearly? was the person the other end of the line calling him by his name? "Raja".. yet he felt as if it could not have been true... He did not for some reason believe that it was just the name and not "Mr. Chatterjee" as Nupur is used to calling him. That means nothing... He must have been mistaken... she called him Mr. Chatterjee alright... Nobody calls him by my name as he wished they would... he was always Mr. Chatterjee to everyone in office and in para and everywhere... why did he have to be born with that limp and why was he so ugly... he looked 40 and in reality he is only 28... okay 29 then ... no more...

He was happy and very merry in the most infantile way after the conversation which was primarily regarding shifts and Rotas but the note in which it started... 'Rajaaaa... Hello, yes,... this is about the Rota...' it kind of knocked out the words from Raja... he was not that old after all... maybe the turmeric and rosemary paste is helping... What was said in the conversation he did not remember... he had approved of all that was asked and put the phone down in a haze...

He had looked at himself in the mirror and admired the unruly hair that hung about him, gave it a run with his fingers and posed like the bond! Bang! phooooh... he blew the imaginary smoke off the barrel of the imaginary pistol he had made with his index and middle finger... and gave a spin... God! what about tomorrow's shift?!!! Raja was not even sure if he was to go in tomorrow... he wished he would call back Nupur and try and speak about the shifts once again but thought otherwise... maybe this time she will call me 'Mr Chatterjee'... let it be let me bask in the short stint of glory and youth.

His thoughts were interrupted again... Nupur had just joined him on the table at the cafeteria she was with Sangitha Nair... a south Indian Brahmin very talkative and friendly in a good natured way...

'Morning Mr Chatterjee' Sangitha called out over the Vada mouthful...

Raja Hung onto his seat

Nupur was on a phone and was about to hang up ... 'Ok cahlo bye and take care... yes baba will speak soon' She hung up, sipped the steaming mug, turned...

'Good Morning MR CHATTERJEE'

The Sun was finally shining through the fog.

Heart has its reason which reason knows nothing of

I remember reading a book Wuthering Heights, a very famous one and the only one written by the author, Emily Jane Brontë, where the female protagonist chooses to marry one of her childhood acquaintance over her love Heathcliff. That, one can ditch love, and marry a person for his wealth and position, and then justify her action saying she wanted to bring Heathcliff out of his misery by helping him seemed too feeble a cause that warranted such a choice. I also ignored many of the dialogues the protagonist says to also show her love for Heathcliff. But today, having seen the world for a good many days more, I feel I must have had a wrong perception.
A person's action is defined by querying the action with where, when, how or even better still as a coordinate in the complicated plane of time and on many more such abstract dimentions. A simple fact that I have been writing this piece of essay in the lines of my present thoughts depends on so many different factors. To elaborate: let me talk about Mother Teresa, who chose to work for poor people in Kolkata and not in Albania, her place of Birth. It could be a more reason defying and a more heart-felt decision on her part and would have been a more plausable introduction to my discussion. To reason it out why I chose to write about Wuthering Heights and not about Mother Teressa, I can say plainly it was emotionaly induced, heart-felt decision which I cannot reason with now. It was a state of my mind where it seemed most logical to have started with the introduction I had started with.
Maybe, down the line, when I am a bit more wise, I would look back at my essay and realise that I lacked any reason why I started off the way I did. My wisdom and my state of mind will then determine whether I had been logical or just another person guided by my heart. And hence the saying Heart has its reason which reason knows nothing of.