#-------------------------------------------------------------------------- # Name: UserTables.jy # Role: Connect to user specified Derby Server, # Identify all user tables, and # Display the fields in each user table # # Author: Robert A. (Bob) Gibson [rag] - bgibson@us.ibm.com # # Example: jython UserTables.jy -d TestDB # # History: # # When Ver Who What # -------- --- --- ----------------------------------------------------- # 05/01/28 0.1 rag Fix - Change "import java.sql" statement # 04/12/29 0.0 rag New - Based strictly upon personal needs & requirements #-------------------------------------------------------------------------- import sys # The sys module is used all over... from java.sql import Types as types, DriverManager #-------------------------------------------------------------------------- # derbyClient() - Invoked to connect to derby server, and query tables. #-------------------------------------------------------------------------- def derbyClient( opt, args ) : from java.lang import Class from java.util import Properties #------------------------------------------------------------------------ # Build the DB URL from the component parts #------------------------------------------------------------------------ dbURL = 'jdbc:derby:net://' + opt[ '-H' ] + ':' + opt[ '-P'] dbURL += '/' + opt[ '-d' ] if opt[ '-v' ] : print 'dbURL = "' + dbURL + '"' #------------------------------------------------------------------------ # Database related variables #------------------------------------------------------------------------ jcc = conn = stmt = rs = rsmd = None; #------------------------------------------------------------------------ # Instantiate the Java Common Client (JCC) Driver instance #------------------------------------------------------------------------ try : jcc = Class.forName( 'com.ibm.db2.jcc.DB2Driver' ).newInstance() if opt[ '-v' ] : print 'JCC driver instantiated.' except : print '\nUnable to instantiate JCC driver instance' print '\nCheck your CLASSPATH for: db2jcc.jar & db2jcc_license_c.jar' raise SystemExit #------------------------------------------------------------------------ # Instantiate the properties required to connect to Derby DB #------------------------------------------------------------------------ P = Properties() P.setProperty( 'user' , opt[ '-u' ] ) P.setProperty( 'password', opt[ '-p' ] ) if opt[ '-v' ] : print 'Properties: ' + str( P ) #------------------------------------------------------------------------ # Use the specified information to connect to the Derby database #------------------------------------------------------------------------ try : conn = DriverManager.getConnection( dbURL, P ) if opt[ '-v' ] : print 'DriverManager created successfully.' except : print "Couldn't connect to the specified Derby Server." print '- Does CLASSPATH contain the proper entries?' print '- Has the Derby Server been started?' print '- Is the Hostname "' + opt[ '-H' ] + '" correct?' print '- Is the Portnum "' + opt[ '-P' ] + '" correct?' print '- Is the DBname "' + opt[ '-d' ] + '" correct?' print '- Does database "' + opt[ '-d' ] + '" exist?' raise SystemExit #------------------------------------------------------------------------ # Instantiate a statement object so can issue database commands #------------------------------------------------------------------------ try : stmt = conn.createStatement() if opt[ '-v' ] : print 'Statement created successfully.' except : print 'Unable to create a statement context using current DB connection.' conn.close() raise SystemExit #------------------------------------------------------------------------ # What does SYS.SYSTABLES joined with SYS.SYSSCHEMAS contain? #------------------------------------------------------------------------ try : Q = 'SELECT * FROM SYS.SYSTABLES AS T, SYS.SYSSCHEMAS AS S ' Q += "WHERE T.SchemaID = S.SchemaID AND TableType <> 'S'" if opt[ '-v' ] : print 'Query: ' + Q rs = stmt.executeQuery( Q ) except : print 'Unable to successfully query SYS.SYSTABLES using:' print '\n' + Q stmt.close() conn.close() raise SystemExit #------------------------------------------------------------------------ # What was returned? First, let's look at the ResultSet MetaData() #------------------------------------------------------------------------ rsmd = RSMD( rs ) printRSMD( rsmd, '* FROM SYS.SYSTABLES, SYS.SYSSCHEMAS' ) #------------------------------------------------------------------------ # Display the TableType & TableName column values using strings as index #------------------------------------------------------------------------ print "|TableName" print "+--------------------" rows = 0 UserTables = [] while ( rs.next() ) : rows += 1 Table = rs.getString( 'SchemaName' ) + '.' + rs.getString( 'TableName' ) UserTables += [ Table ] print '|%s' % Table if rows == 1 : plural = '' else : plural = 's' print '\n%d row%s selected' % ( rows, plural ) #------------------------------------------------------------------------ # Determine and display the column information for each UserTable #------------------------------------------------------------------------ for Table in UserTables : try : Q = 'SELECT * FROM ' + Table if opt[ '-v' ] : print 'Query: ' + Q rs = stmt.executeQuery( Q ) rsmd = RSMD( rs ) printRSMD( rsmd, Table ) except : print '\nUnable to query ' + Table rs.close() rs = None #------------------------------------------------------------------------ # Cleanup. Close all DB related objects #------------------------------------------------------------------------ if rs != None : rs.close() if stmt != None : stmt.close() if conn != None : conn.close() del( jcc, conn, stmt, rs, rsmd ) #-------------------------------------------------------------------------- # main() - Routine where actual processing is performed when script is run #-------------------------------------------------------------------------- def main() : import getopt # Things needed to parse parms #------------------------------------------------------------------------ # Check the user specified parms for valid/recognized options #------------------------------------------------------------------------ try : opts, args = getopt.getopt( sys.argv[1:], 'H:P:d:u:p:v?' ) except getopt.GetoptError : Usage( sys.argv[ 0 ] ) # Display usage info sys.exit( 2 ) # Indicate command line syntax error #------------------------------------------------------------------------ # Initialize the option dictionary #------------------------------------------------------------------------ opt = { '-H' : None , # Hostname Derby Server Hostname '-P' : None , # PortNum Derby Server PortNum '-d' : None , # DBname Database name '-u' : None , # Userid '-p' : None , # Password '-v' : None , # Verbose flag '-?' : None } # Usage info request #------------------------------------------------------------------------ # Given: opts contains only valid parameters #------------------------------------------------------------------------ for o, a in opts : if opt[ o ] != None : print 'Parameter error: Only 1 "' + o + '" parameter is allowed.\n' Usage( sys.argv[ 0 ] ) # sys.exit( 2 ) # Indicate command line syntax error elif o == '-?' : # Request for Usage info? Usage( sys.argv[ 0 ] ) # Satisfy request sys.exit( 0 ) # Successful request else : # Valid option - process it if o == '-v' : # "verbose" request? opt[ o ] = 1 # Yes - set flag "True" else : # opt[ o ] = a # No - save user specified value #------------------------------------------------------------------------ # Check for unspecified options, and specify default values for each #------------------------------------------------------------------------ if opt[ '-H' ] == None : opt[ '-H' ] = 'localhost' if opt[ '-P' ] == None : opt[ '-P' ] = '1527' if opt[ '-d' ] == None : opt[ '-d' ] = 'sample' if opt[ '-u' ] == None : opt[ '-u' ] = 'SAMP' if opt[ '-p' ] == None : opt[ '-p' ] = 'SAMP' if opt[ '-v' ] == None : opt[ '-v' ] = 0 #------------------------------------------------------------------------ # Display the default/user specified options if "verbose" is enabled #------------------------------------------------------------------------ if opt[ '-v' ] : print 'Hostname: ' + opt[ '-H' ] print 'Portnum : ' + opt[ '-P' ] print 'DBname : ' + opt[ '-d' ] print 'UserID : ' + opt[ '-u' ] print 'Password: ' + opt[ '-p' ] print 'Verbose : ' + str( opt[ '-v' ] ) #---------------------------------------------------------------------- # The remainder of the command line arguments are table names... #---------------------------------------------------------------------- tables = delim = '' for arg in args : tables += delim + arg delim = ' ' print 'Tables : ' + tables #------------------------------------------------------------------------ # Invoke derbyClient routine to perform the actual work #------------------------------------------------------------------------ derbyClient( opt, args ) #-------------------------------------------------------------------------- # 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.' #-------------------------------------------------------------------------- # Usage() - Routine used to display script usage and parameter information #-------------------------------------------------------------------------- def Usage( pgm ) : import os ( path, filename ) = os.path.split( pgm ) if filename[-3:] == '.jy' : filename = filename[:-3] Parms = ''' [-H Hostname] [-P Portnum] [-d DBname] [-u userid] [-p password] [-v] [-?] [tablename] ... Parm Attribute Default Meaning ---- --------- --------- ----------------------------------------------- -H Hostname localhost Name of host on which Derby Server is executing -P Portnum 1527 Port number on which Derby Server is listening -d DBname sample Name of DB to which connection should be issued -u userid SAMP User ID authorized to connect to Derby server -p password SAMP Password authorized to connect to Derby server -v Option used to enable verbose messages (traces) -? Option used to request the display of this info Example: UserTables -d TestDB''' print ' Usage: ' + filename + Parms return #-------------------------------------------------------------------------- # Initialization code - Run when script is being executed, and not imported #-------------------------------------------------------------------------- if __name__ == "__main__" : main()