#--------------------------------------------------------------------------
#    Name: ORGtable.jy
#    Role: Demonstrate how to connect to our Apache Derby Network Server,
#          issue an SQL query, and display the results, without using the
#          zxJDBC package
#
#  Author: Robert A. (Bob) Gibson [rag] - bgibson@us.ibm.com
#
# Example: jython ORGtable.jy
#
# History:
#
#   When   Ver Who What
# -------- --- --- -----------------------------------------------------
# 05/01/29 0.1 rag Fix - change "import java.sql" to "from java.sql ..."
# 05/01/24 0.0 rag New - Based strictly upon personal needs & requirements
#--------------------------------------------------------------------------

#--------------------------------------------------------------------------
# import the things that we need
#--------------------------------------------------------------------------
from java.sql  import DriverManager, Types as types
from java.lang import Class
from java.util import Properties

#--------------------------------------------------------------------------
# RSMD() - Routine used to gather the "interesting" ResultSet MetaData
#--------------------------------------------------------------------------
def RSMD( rs, NumTypes = None ) :
  #------------------------------------------------------------------------
  # Check for first invocation, so we can initialize NumType
  #------------------------------------------------------------------------
  if NumTypes == None :
    NumTypes = [ 
      types.BIGINT  ,
      types.DECIMAL ,
      types.DOUBLE  ,
      types.FLOAT   ,
      types.INTEGER ,
      types.NUMERIC ,
      types.REAL    ,
      types.SMALLINT,
      types.TINYINT  
    ]                

  #------------------------------------------------------------------------
  # Obtain MetaData for specified ResultSet
  #------------------------------------------------------------------------
  result = ()
  rsmd   = rs.getMetaData()
  for col in range( 1, rsmd.getColumnCount() + 1 ) :
    Type = rsmd.getColumnType( col )
    if Type in NumTypes :                   # Only numeric values have...
      precision = rsmd.getPrecision( col )  # precision, and
      scale     = rsmd.getScale( col )      # scale
    else :                                  #
      precision = scale = None              #
    row =  ( rsmd.getColumnLabel( col ),
             rsmd.getColumnDisplaySize( col ),
             Type,
             precision,
             scale,
             rsmd.getColumnTypeName( col )
           )
    result += ( row, )
  return result

#--------------------------------------------------------------------------
# printRSMD() - Routine used to display the ResultSet MetaData of interest
#--------------------------------------------------------------------------
def printRSMD( rsmd, TableName ) :
  truncated = ''
  print '\n Fields contained in:', TableName
  print '\n| Size  |  Label                 |Type |Type Name'
  print '+-------+------------------------+-----+--------------------'
  for col in range( len( rsmd ) ) :
    Label, Size, Type, precision, scale, TypeName = rsmd[ col ]
    if len( Label ) > 24 :
      Label = Label[:23] + '*'
      truncated = '*'
    print '|%7d|%-24s|%5d|%-20s' % ( Size, Label, Type, TypeName )
  print
  if truncated != '' : print ' * The specified field was truncated.'
  return

#--------------------------------------------------------------------------
# Routine executed when script is invoked at the command line
#--------------------------------------------------------------------------
def main() :
  #------------------------------------------------------------------------
  # Initialize the URL and driver variables, just like we did in zxORGtable
  #------------------------------------------------------------------------
  url    = 'jdbc:derby:net://localhost:1527/sample'
  driver = 'com.ibm.db2.jcc.DB2Driver'

  #------------------------------------------------------------------------
  # To define the userid and password, we need to use class Properties
  #------------------------------------------------------------------------
  props = Properties()
  props.setProperty( 'user', 'SAMP' )
  props.setProperty( 'password', 'SAMP' )

  #------------------------------------------------------------------------
  # Instantiate the Java Common Client (JCC) Driver instance
  #------------------------------------------------------------------------
  jcc  = Class.forName( driver ).newInstance()

  #------------------------------------------------------------------------
  #  Use a DriverManager to establish the connection to our Derby database
  #------------------------------------------------------------------------
  conn = DriverManager.getConnection( url, props )

  #------------------------------------------------------------------------
  # Instantiate a statement object so can issue database commands
  #------------------------------------------------------------------------
  stmt = conn.createStatement()

  #------------------------------------------------------------------------
  # Define the SQL query to be issued
  #------------------------------------------------------------------------
  Query = 'SELECT * FROM ORG'

  #------------------------------------------------------------------------
  # Issue the query using the statement context on the associated connection
  #------------------------------------------------------------------------
  rs = stmt.executeQuery( Query )

  #------------------------------------------------------------------------
  # Use some support routines to obtain and display the ResultSetMetaData
  #------------------------------------------------------------------------
  rsmd = RSMD( rs )
  printRSMD( rsmd, Query )

  #------------------------------------------------------------------------
  # Retrieve and display each row of data in the database
  #------------------------------------------------------------------------
  rowCount = 0
  while ( rs.next() ) :
    rowCount += 1
    row = ( rs.getInt( 1 ),
            rs.getString( 2 ),
            rs.getInt( 3 ),
            rs.getString( 4 ),
            rs.getString( 5 )
          )
    print row
  print '\n' + str( rowCount ) + ' rows selected'

  rs.close()
  stmt.close()
  conn.close()

#--------------------------------------------------------------------------
# Entry point for script.  main() is only executed when script is invoked.
#--------------------------------------------------------------------------
if __name__ == "__main__" :
  main()