def run_test_191(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, config.user, "emp_photo")
else:
result = ibm_db.columns(conn, None, None, "EMP_PHOTO")
i = 0
row = ibm_db.fetch_both(result)
while (row):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ((row['column_name'] != 'emp_rowid') and (i < 3)):
print("%s,%s,%s,%s\n" %
(row['table_schem'], row['table_name'],
row['column_name'], row['is_nullable']))
else:
if ((row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3)):
print("%s,%s,%s,%s\n" %
(row['TABLE_SCHEM'], row['TABLE_NAME'],
row['COLUMN_NAME'], row['IS_NULLABLE']))
i = i + 1
row = ibm_db.fetch_both(result)
print("done!")
else:
print("no connection: ", ibm_db.conn_errormsg())
def run_test_191(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,config.user,"emp_photo");
else:
result = ibm_db.columns(conn,None,None,"EMP_PHOTO");
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ( (row['column_name'] != 'emp_rowid') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['table_schem'],
row['table_name'], row['column_name'], row['is_nullable']))
else :
if ( (row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['TABLE_SCHEM'],
row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE']))
i = i + 1
row = ibm_db.fetch_both(result)
print("done!")
else:
print("no connection: ", ibm_db.conn_errormsg())
def run_test_190(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, config.user, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_tuple(result)
while (row):
str = row[1] + "/" + row[3]
print(str)
row = ibm_db.fetch_tuple(result)
print("done!")
else:
print("no connection:", ibm_db.conn_errormsg())
def run_test_190(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
if server.DBMS_NAME[0:3] == "IDS":
result = ibm_db.columns(conn, None, config.user, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_tuple(result)
while row:
str = row[1] + "/" + row[3]
print str
row = ibm_db.fetch_tuple(result)
print "done!"
else:
print "no connection:", ibm_db.conn_errormsg()
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password, op)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password, op)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
# Create An Instance Of The Db2ConnectionMgr Class And Use It To Connect To A Db2 Database
conn = Db2ConnectionMgr('DB', dbName, '', '', userID, passWord)
conn.openConnection()
if conn.returnCode is True:
dbConnection = conn.connectionID
else:
conn.closeConnection()
exit(-1)
# Attempt To Retrieve Information About All Of The Columns That Have Been Defined For
# A Particular Table
print("Obtaining information about columns that have been defined for the ",
end="")
print(tableName + " table ... ", end="")
try:
resultSet = ibm_db.columns(dbConnection, None, None, tableName, None)
except Exception:
pass
# If The Information Desired Could Not Be Retrieved, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to obtain the information desired\n.")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# As Long As There Are Records (That Were Produced By The ibm_db.columns API), ...
noData = False
def main(self):
args = self.get_option()
dbname = args.dbname # args1
input_file = args.input_file # args2
rows = args.rows # args3
username = connect_conf.username
password = connect_conf.password
schema = username.upper()
l_list = []
g_list = []
col_types = []
col_sizes = []
col_size_fractions = []
col_num = 0
count = 0
COMMIT_ROW = 1000
# connect
try:
conn = ibm_db.pconnect(dbname, username, password)
except Exception as e:
print("Connection failed")
traceback.print_exc()
return
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# get the sql
try:
insert_sql = self.get_sql(input_file)
except Exception as e:
print("Cannot read sqlfile.")
traceback.print_exc()
return
stmt = ibm_db.prepare(conn, insert_sql)
if not stmt:
print("Failed in prepare.")
return
# get the table name from sql, and check
tabname = re.split("[ ]*INSERT INTO[ ]+", insert_sql)[1]
tabname = re.split("[ ]+VALUES", tabname)[0]
if tabname is None:
print("Error in contents of sqlfile.")
return
# verify whether the table exist
rc = self.chk_table(conn, schema, tabname)
if not rc:
print("table name " + tabname + " is not found in " + dbname)
return
# get the number of the columns
result = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(result)
while (col):
col_num = col_num + 1
col = ibm_db.fetch_tuple(result)
# list of column type and column size
col_info = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(col_info)
while (col):
col_types.append(col[5])
col_sizes.append(col[6])
col_size_fractions.append(col[8])
col = ibm_db.fetch_tuple(col_info)
for i in range(rows):
count = 0
random.seed()
for j in range(len(col_types)):
count = count + 1
if self.chk_par_mark(count, insert_sql):
if col_types[j] == "CHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "VARCHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "SMALLINT":
param = self.get_random_smallint()
elif col_types[j] == "INTEGER":
param = self.get_random_integer()
elif col_types[j] == "BIGINT":
param = self.get_random_long()
elif col_types[j] == "REAL":
param = self.get_random_real()
elif col_types[j] == "FLOAT":
param = self.get_random_double()
elif col_types[j] == "DOUBLE":
param = self.get_random_double()
elif col_types[j] == "DECIMAL":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "NUMERIC":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "DATE":
param = self.get_random_date()
elif col_types[j] == "TIME":
param = self.get_random_time()
elif col_types[j] == "TIMESTAMP":
param = self.get_random_timestamp()
elif col_types[j] == "BLOB":
param = self.get_random_byte(col_sizes[j])
elif col_types[j] == "CLOB":
param = self.get_random_char(col_sizes[j])
else:
param = ''
# set the parameter to the list
self.set_param_list(param, l_list)
# end of the columns
if count == col_num:
self.concat_list(g_list, l_list)
l_list = []
if ((i + 1) % COMMIT_ROW == 0):
#print g_list
rc = ibm_db.execute_many(
stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
g_list = []
if len(g_list) != 0:
print g_list
rc = ibm_db.execute_many(stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
ibm_db.close(conn)