#!/usr/bin/env python # #---------------------------------------------------------------------- # Name: bcp (st_db_wrapper.py w/hard-coded freebcp option) # # Description: This tool was created in order to allow Solaris/Sybase-like # isql/bcp access, where the DB password is automaticaly supplied # for the user. # # It uses the sqsh isql-replacement task (www.sqsh.org) or # freebcp, and handles the DB password lookup via the # $ACAREA/{username}.dat file. # # The tool grabs these options if provided, on the command # line, to try and determine which DB password it should # lookup: # -P DBpasswd (if user supplied the password, no lookup needed) # -S DBserver (the DB server to look up the password for, # tries env var DSQUERY if -S not provided) # -U DBusername (the DB username to look up the password for, # tries env var LOGNAME if -U not provided) # # These options, and any others on the command line, will be # passed on directly to the db command invocation, along with # the password (either provided or looked up), so they # must be valid for the db command. All of the main options # that we are used to seem to be valid for sqsh and freebcp # (e.g. -i inputsql -e -o outputsql -D dbname etc.) # # Exit Status: returns whatever exit status the db command utility passed back # # History: # 03/20/10 MSwam first version # 05/25/10 LGardner Read login name's .dat file and not dbUser. # Allow dbuser lookup password when != login name # Manually parse options needed for login # Combine our_isql.py and our_bcp.py into one script # 10/06/10 MSwam Because using a UNIX alias won't work to invoke bcp # in our scripts, I had to copy st_db_wrapper.py twice, # making isql-specific and bcp-specific versions. # 11/04/10 MSwam Added usage message (taken from underlying freebcp) #---------------------------------------------------------------------- import os import sys import subprocess # initialize dbPass = None dbServer = None dbUser = None loginUser = None dbServerFound = False dbPassFound = False dbUserFound = False argList = sys.argv[1:] if len(argList) == 0: print 'usage: bcp [[database_name.]owner.]table_name {in | out} datafile' print '[-m maxerrors] [-f formatfile] [-e errfile]' print '[-F firstrow] [-L lastrow] [-b batchsize]' print '[-n] [-c] [-t field_terminator] [-r row_terminator]' print '[-U username] [-P password] [-I interfaces_file] [-S server]' print '[-v] [-d] [-h "hint [,...]" [-O "set connection_option on|off, ...]"' print '[-A packet size] [-T text or image size] [-E]' sys.exit(-1) # parse the server, password and user name if they exist from the # command line. Can't use getops since would have to specify every # possible command line option which is way too much work. i = 0 while i < len(argList): arg = argList[i] if arg.startswith('-S') : dbServer = arg[2:] if dbServer == '' : i = i + 1 dbServer = argList[i] dbServerFound = True elif arg.startswith('-P') : dbPass = arg[2:] if dbPass == '' : i = i + 1 dbPass = argList[i] dbPassFound = True elif arg.startswith('-U') : dbUser = arg[2:] if dbUser == '' : i = i + 1 dbUser = argList[i] dbUserFound = True i = i + 1 #print "sys.argv="+str(sys.argv) # If the password does not exist we will have to do a lookup in the # ACAREA/.dat file for the password if not dbPass: # no password supplied, so we need to dig it out # get the system-supplied login name. This is because it's possible for # the same system login to have multiple names in the the db. We will # need the login to find the .dat file but will need the name in the # database to do the lookup in the .dat file. loginUser = os.environ["LOGNAME"] if not dbUser: # Make the dbUser the same as the loginUser. This will be the typical # case. Most people have only one account per server and it's their # system login name. dbUser = loginUser if not dbServer: # use DSQUERY, if available try: dbServer = os.environ["DSQUERY"] except KeyError: print "ERROR: No dbServer specified (-S) and DSQUERY envvar undefined." sys.exit(-1) # Create the .dat file name based on the system login name then open # the file and read it into a map. passwdMap = {} passwdFileName = os.environ["ACAREA"] + os.sep + loginUser + ".dat" passwdFile = open(passwdFileName, "r") for line in passwdFile: parts = line.split() passwdMap[parts[0]] = parts[1] passwdFile.close() # There are two types of entries in the .dat file. The most common # entry is the entry. This does not list a user # name but assumes it is the system login. The second type of entry # is for users with multiple database logins. This entry is of the # form . An example of the # types of entries are: # ROBBIE abcdefghi # ROBBIEDADSDBO jklmnopqr # # If the dbUser is not the same as the login then we need an uppercase # version of the dbUser appended to the dbServer which hopefully is # already uppercased. If they are the same a null string will be # appended which will just leave the server. We then use that to # do the map file lookup which should give the correct password for # that dbUser. try: dbUserUC = '' if dbUser != loginUser: dbUserUC = dbUser.upper() dbKey = dbServer+str(dbUserUC) dbPass = passwdMap[dbKey] except KeyError: print "ERROR: dbServer name "+dbServer+" not found in "+passwdFileName+" for "+dbKey sys.exit(-1) # We don't know whether the password, server or user in the the # argument list and since we may have derived it above we don't want # to have to write it twice. For each of the 3 options if it's in # the argument list do not write it out again. We only want to write # it out if we derived it above. addOn = [] if dbPassFound == False: addOn.append("-P") addOn.append(dbPass) if dbServerFound == False: addOn.append("-S") addOn.append(dbServer) if dbUserFound == False: addOn.append("-U") addOn.append(dbUser) #for opt in addOn: print opt # now make the db call, supplying all the arguments as well as any # derived options. The reason for shell=False is that we have lots of # shell interpreted characters that we don't want interpreted. Like the # -t "|" option. try: #=================================================================== #HARDCODE freebcp on front, making this the bcp version of the tool #=================================================================== HARDCODE = [] HARDCODE.append("freebcp") args = HARDCODE + sys.argv[1:] + addOn #for opt in args: print opt retcode = subprocess.call(args, shell=False) if retcode < 0: print >>sys.stderr, "Child process was terminated by signal", -retcode except OSError, e: print >>sys.stderr, "Execution failed:", e sys.exit(retcode)