#-------------------------------------------------------------------------- # Name: zxUserTables.jy # Role: Connect to user specified Derby Server (using zxJDBC package), # Identify all user tables, and # Display the fields in each user table # # Author: Robert A. (Bob) Gibson [rag] - bgibson@us.ibm.com # # Example: jython zxUserTables.jy -d TestDB # # History: # # When Ver Who What # -------- --- --- ----------------------------------------------------- # 05/02/02 0.3 rag Fix - Trivial typographical errors in comments # 05/01/28 0.2 rag Fix - Change "import java.sql" to rename Types as types # 05/01/24 0.1 rag Add - specific exception test for zxJDBC.DatabaseError # 05/01/01 0.0 rag New - Based upon UserTables.jy, and using zxJDBC package #-------------------------------------------------------------------------- import sys # The sys module is used all over... from java.sql import Types as types from com.ziclix.python.sql import zxJDBC #-------------------------------------------------------------------------- # derbyClient() - Invoked to connect to derby server, and query tables. #-------------------------------------------------------------------------- def derbyClient( opt, args ) : #------------------------------------------------------------------------ # 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 #------------------------------------------------------------------------ db = cursor = None; #------------------------------------------------------------------------ # Use the specified information to connect to the Derby database #------------------------------------------------------------------------ try : driver = 'com.ibm.db2.jcc.DB2Driver' db = zxJDBC.connect( dbURL, opt[ '-u' ], opt[ '-p' ] , driver ) if opt[ '-v' ] : print 'zxJDBC connection created successfully.' except zxJDBC.DatabaseError, errorMsg: print errorMsg print '\nConnection request failed.' 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 #------------------------------------------------------------------------ # We can either enable autocommit, or be responsible for issuing commit() #------------------------------------------------------------------------ db.autocommit = 1 # Enable autocommit for this program #------------------------------------------------------------------------ # Create a dynamic cursor (so whole ResultSet isn't loaded into memory) #------------------------------------------------------------------------ try : cursor = db.cursor( 1 ) except : print 'Unable to create a dynamic cursor using database context.' db.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 cursor.execute( Q ) except : print 'Unable to successfully query SYS.SYSTABLES using:' print '\n' + Q cursor.close() # If execute fails, is cursor still a valid object? db.close() raise SystemExit #------------------------------------------------------------------------ # What was returned? First, let's look at the ResultSet MetaData() #------------------------------------------------------------------------ printInfo( cursor.description, '* FROM SYS.SYSTABLES, SYS.SYSSCHEMAS' ) #------------------------------------------------------------------------ # Display the TableType & TableName column values using strings as index #------------------------------------------------------------------------ print "|TableName" print "+--------------------" rows = 0 # Number of rows processed UserTables = [] # Initialize list of UserTables row = cursor.fetchone() # Attempt to get first row while row : # While data exists in the database rows += 1 # row exists, increment row # Table = row[ 6 ] + '.' + row[ 1 ] # SchemaName.TableName UserTables += [ Table ] # Add to list of UserTables print '|%s' % Table # Display fully quelified tablename row = cursor.fetchone() # Attempt to get next row 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 cursor.execute( Q ) printInfo( cursor.description, Table ) except : print '\nUnable to query ' + Table cursor.close() # Is cursor still a valid object at this point? cursor = None #------------------------------------------------------------------------ # Cleanup. Close all DB related objects #------------------------------------------------------------------------ if cursor != None : cursor.close() # db.commit() # Required if autocommit not enabled if db != None : db.close() del( cursor, db ) #-------------------------------------------------------------------------- # 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 ) #-------------------------------------------------------------------------- # printInfo() - Routine used to display the cursor description information #-------------------------------------------------------------------------- def printInfo( description, TableName, TypeName = None ) : if TypeName == None : #---------------------------------------------------------------------- # Unfortunately, the description does not include the TypeName info #---------------------------------------------------------------------- TypeName = { types.ARRAY : 'ARRAY' , types.BIGINT : 'BIGINT' , types.BINARY : 'BINARY' , types.BIT : 'BIT' , types.BLOB : 'BLOB' , types.CHAR : 'CHAR' , types.CLOB : 'CLOB' , types.DATE : 'DATE' , types.DECIMAL : 'DECIMAL' , types.DISTINCT : 'DISTINCT' , types.DOUBLE : 'DOUBLE' , types.FLOAT : 'FLOAT' , types.INTEGER : 'INTEGER' , types.JAVA_OBJECT : 'JAVA_OBJECT' , types.LONGVARBINARY: 'LONGVARBINARY', types.LONGVARCHAR : 'LONGVARCHAR' , types.NULL : 'NULL' , types.NUMERIC : 'NUMERIC' , types.OTHER : 'OTHER' , types.REAL : 'REAL' , types.REF : 'REF' , types.SMALLINT : 'SMALLINT' , types.STRUCT : 'STRUCT' , types.TIME : 'TIME' , types.TIMESTAMP : 'TIMESTAMP' , types.TINYINT : 'TINYINT' , types.VARBINARY : 'VARBINARY' , types.VARCHAR : 'VARCHAR' } #------------------------------------------------------------------------ # Remainder of info exists in description... #------------------------------------------------------------------------ truncated = '' print '\n Fields contained in:', TableName print '\n| Size | Label |Type |Type Name' print '+-------+------------------------+-----+--------------------' for col in range( len( description ) ) : ( Label, Type, Size, iSize, precision, scale, null_ok ) = description[ col ] if len( Label ) > 24 : Label = Label[:23] + '*' truncated = '*' print '|%7d|%-24s|%5d|%-20s' % ( Size, Label, Type, TypeName[ Type ] ) 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()