#--------------------------------------------------------------------------
#    Name: zxORGtable.jy
#    Role: Demonstrate how to use the zxJDBC package to connect to our
#          Apache Derby Network Server, issue an SQL query, and display
#          the results.
#
#  Author: Robert A. (Bob) Gibson [rag] - bgibson@us.ibm.com
#
# Example: jython zxORGtable.jy
#
# History:
#
#   When   Ver Who What
# -------- --- --- -----------------------------------------------------
# 05/01/23 0.0 rag New - Based strictly upon personal needs & requirements
#--------------------------------------------------------------------------

#--------------------------------------------------------------------------
# import the zxJDBC package so we can use it
#--------------------------------------------------------------------------
from com.ziclix.python.sql import zxJDBC

#--------------------------------------------------------------------------
# Initialize some variables to establish the connection
#--------------------------------------------------------------------------
url = 'jdbc:derby:net://localhost:1527/sample'
driver = 'com.ibm.db2.jcc.DB2Driver'
user = password = 'SAMP'

#--------------------------------------------------------------------------
# Instantiate our database using the zxJDBC.connection method
#--------------------------------------------------------------------------
db = zxJDBC.connect( url, user, password, driver )

#--------------------------------------------------------------------------
# Instantiate a static database cursor object
#--------------------------------------------------------------------------
cursor = db.cursor()

#--------------------------------------------------------------------------
# Execute the SQL query - All fields and all rows from the SAMP.ORG table
#--------------------------------------------------------------------------
cursor.execute( 'SELECT * FROM ORG' )

#--------------------------------------------------------------------------
# Display the table field description information
#--------------------------------------------------------------------------
print 'table field descriptions:\n' + '-' * 24
for field in cursor.description : print field

#--------------------------------------------------------------------------
# Read all rows into 'result', so we can easily display it
#--------------------------------------------------------------------------
result = cursor.fetchall()

#--------------------------------------------------------------------------
# Display the information from each row, then the number of rows returned
#--------------------------------------------------------------------------
print '\nORG tables rows:\n' + '-' * 15
for row in result : print row

print '\n' + str( len( result ) ) + ' rows selected'

#--------------------------------------------------------------------------
# Close the cursor, and database object.
# Note: Since autocommit has not been enabled, we have to issue commit()
#       before closing the database
#--------------------------------------------------------------------------
cursor.close()
db.commit()
db.close()