def run_test_032(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6")
while (ibm_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ibm_db.result(stmt, "id")
breed = ibm_db.result(stmt, "breed")
name = ibm_db.result(stmt, "name")
weight = ibm_db.result(stmt, "weight")
else:
id = ibm_db.result(stmt, "ID")
breed = ibm_db.result(stmt, "BREED")
name = ibm_db.result(stmt, "NAME")
weight = ibm_db.result(stmt, "WEIGHT")
print "int(%d)" % id
print "string(%d) \"%s\"" % (len(breed), breed)
print "string(%d) \"%s\"" % (len(name), name)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_300(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER)
print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE
print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
ibm_db.close(conn)
else:
print "Error."
def run_test_195(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
drop = 'DROP TABLE test_195'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
ibm_db.exec_immediate(conn, insert)
sql = "SELECT data FROM test_195"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
while( result ):
print "Output:", result
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print "Native XML datatype is not supported."
def run_test_150(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
while ( row ):
#print "%5d " % row['ID']
#print "%-10s " % row['NAME']
#print "%5d " % row['DEPT']
#print "%-7s " % row['JOB']
#print "%5d " % row['YEARS']
#print "%15s " % row['SALARY']
#print "%10s " % row['COMM']
if (row['YEARS'] == None):
row['YEARS'] = 0
if (row['COMM'] == None):
row['COMM'] = ''
print "%5d %-10s %5d %-7s %5s %15s %10s " % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM'])
row = ibm_db.fetch_assoc(result)
def run_test_006(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
print ("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
print "Test non-existent table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ibm_db.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + ibm_db.conn_errormsg()
def run_test_155(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
result = ibm_db.exec_immediate(conn, "select * from employee where lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
i=0
row = ibm_db.fetch_assoc(result)
while ( row ):
i += 1
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
if (row['midinit'] == None):
row['midinit'] = ''
print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['empno'], row['firstnme'], row['midinit'], row['lastname'], row['workdept'], \
row['phoneno'], row['hiredate'], row['job'], row['edlevel'], row['sex'], \
row['birthdate'], row['salary'], row['bonus'], row['comm']))
row = ibm_db.fetch_assoc(result)
else:
if (row['MIDINIT'] == None):
row['MIDINIT'] = ''
print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['EMPNO'], row['FIRSTNME'], row['MIDINIT'], row['LASTNAME'], row['WORKDEPT'], \
row['PHONENO'], row['HIREDATE'], row['JOB'], row['EDLEVEL'], row['SEX'], \
row['BIRTHDATE'], row['SALARY'], row['BONUS'], row['COMM']))
row = ibm_db.fetch_assoc(result)
print("%d record(s) selected." % i)
def run_test_038(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
ibm_db.execute(result2)
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3), \
ibm_db.result(result2, 5)))
row2 = ibm_db.fetch_row(result2)
row = ibm_db.fetch_row(result)
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_153(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from org")
row = ibm_db.fetch_assoc(result)
while row:
# printf("%4d ",row['DEPTNUMB'])
# printf("%-14s ",row['DEPTNAME'])
# printf("%4d ",row['MANAGER'])
# printf("%-10s",row['DIVISION'])
# printf("%-13s ",row['LOCATION'])
# puts ""
print "%4d %-14s %4d %-10s%-13s " % (
row["DEPTNUMB"],
row["DEPTNAME"],
row["MANAGER"],
row["DIVISION"],
row["LOCATION"],
)
row = ibm_db.fetch_assoc(result)
def run_test_120(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
name1 = ibm_db.field_name(stmt, 1)
name2 = ibm_db.field_name(stmt, 2)
name3 = ibm_db.field_name(stmt, 3)
name4 = ibm_db.field_name(stmt, 4)
name6 = ibm_db.field_name(stmt, 8)
name7 = ibm_db.field_name(stmt, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "id")
name8 = ibm_db.field_name(stmt, "WEIGHT")
else:
name5 = ibm_db.field_name(stmt, "ID")
name8 = ibm_db.field_name(stmt, "weight")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("string(%d) \"%s\"" % (len(name3), name3))
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("string(%d) \"%s\"" % (len(name7), name7))
print("%s" % name8)
else:
print("Connection failed.")
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
if (server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (server.DBMS_NAME == "DB2"):
pattern = self.expected_ZOS(callstack[1][1])
elif (server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[','\]','\(','\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
def run_test_152(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from project")
row = ibm_db.fetch_assoc(result)
while row:
# printf("%6s ",row['PROJNO'])
# printf("%-24s ",row['PROJNAME'])
# printf("%3s ",row['DEPTNO'])
# printf("%6s",row['RESPEMP'])
# printf("%7s ",row['PRSTAFF'])
# printf("%10s ",row['PRSTDATE'])
# printf("%10s ",row['PRENDATE'])
# printf("%6s",row['MAJPROJ'])
# puts ""
if row["MAJPROJ"] == None:
row["MAJPROJ"] = ""
print "%6s %-24s %3s %6s%7s %10s %10s %6s" % (
row["PROJNO"],
row["PROJNAME"],
row["DEPTNO"],
row["RESPEMP"],
row["PRSTAFF"],
row["PRSTDATE"],
row["PRENDATE"],
row["MAJPROJ"],
)
row = ibm_db.fetch_assoc(result)
def run_test_034(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
if row:
# printf("%5d ",row['ID'])
# printf("%-10s ",row['NAME'])
# printf("%5d ",row['DEPT'])
# printf("%-7s ",row['JOB'])
# printf("%5d ", row['YEARS'])
# printf("%15s ", row['SALARY'])
# printf("%10s ", row['COMM'])
# puts ""
print "%5d %-10s %5d %-7s %5d %15s %10s" % (
row["ID"],
row["NAME"],
row["DEPT"],
row["JOB"],
row["YEARS"],
row["SALARY"],
row["COMM"],
)
ibm_db.close(conn)
def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_db.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
row3 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def run_test_03a(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while ( ibm_db.fetch_row(stmt) ):
breed = ibm_db.result(stmt, 1)
print ("string(%d) \"%s\"" % (len(breed), breed))
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print ("string(%d) \"%s\"" % (len(name), name))
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "passport")
else:
name = ibm_db.result(stmt, "PASSPORT")
print (name)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_054(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
else:
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
count = 1
while (row):
if (row['YEARS'] == None):
row['YEARS'] = ''
if (row['COMM'] == None):
row['COMM'] = ''
print row['ID'], row['NAME'], row['JOB'], row['YEARS'], row[
'SALARY'], row['COMM']
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(
conn,
"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')"
)
row2 = ibm_db.fetch_assoc(result2)
while (row2):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
'ADMRDEPT'], row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print("%s, " % ibm_db.field_name(result, i))
print()
print()
i = 0
row = ibm_db.fetch_tuple(result)
while ( row ):
ibm_db.num_fields(result)
if (i < 4):
print(", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n")
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_065(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, '%', "t3")
else:
result = ibm_db.tables(conn, None, '%', "T3")
columns = ibm_db.num_fields(result)
for i in range(0, columns):
print "%s, " % ibm_db.field_name(result, i)
print "\n\n"
row = ibm_db.fetch_tuple(result)
while ( row ):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , ";
row = ibm_db.fetch_tuple(result)
print final
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.tables(conn, None, 'T');
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print str
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_062(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
schema = 't'
else:
schema = 'T'
result = ibm_db.tables(conn,None,schema);
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row[1] + "/" + row[2] + "/" + row[3]
if (i < 4):
print(str)
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: #{ibm_db.conn_errormsg}");
def run_test_060(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = "CREATE SCHEMA AUTHORIZATION t"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if server.DBMS_NAME[0:3] == "IDS":
result = ibm_db.tables(conn, None, "t")
else:
result = ibm_db.tables(conn, None, "T")
i = 0
row = ibm_db.fetch_both(result)
while row:
if i < 4:
print "/%s/%s" % (row[1], row[2])
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, "DROP TABLE t.t1")
ibm_db.exec_immediate(conn, "DROP TABLE t.t2")
ibm_db.exec_immediate(conn, "DROP TABLE t.t3")
ibm_db.exec_immediate(conn, "DROP TABLE t.t4")
print "done!"
else:
print "no connection: #{ibm_db.conn_errormsg}"
def run_test_115(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# row = ibm_db.fetch_object(stmt, 0)
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
else:
print("Connection failed.")
def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print ("Connection failed.")
#__END__
#__LUW_EXPECTED__
#No Data[IBM][CLI Driver][DB2/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__ZOS_EXPECTED__
#No Data[IBM][CLI Driver][DB2]
# SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__SYSTEMI_EXPECTED__
#No Data
#__IDS_EXPECTED__
#No Data
#[IBM][CLI Driver][IDS/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = "DROP TABLE tab_bigint"
result = ""
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if server.DBMS_NAME[0:3] == "IDS":
create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)"
else:
create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while data:
print data[0]
print data[1]
print data[2]
print data[3]
print type(data[0]) is long
print type(data[1]) is long
print type(data[2]) is long
data = ibm_db.fetch_both(stmt)
# test ibm_db.result for fetch of bigint
stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint")
ibm_db.execute(stmt1)
ibm_db.fetch_row(stmt1, 0)
if server.DBMS_NAME[0:3] != "IDS":
row1 = ibm_db.result(stmt1, "COL2")
else:
row1 = ibm_db.result(stmt1, "col2")
print row1
ibm_db.close(conn)
def get_server_info(self, conn):
server_info = ibm_db.server_info(conn)
dbms_name = server_info.DBMS_NAME
dbms_ver = server_info.DBMS_VER
inst_name = server_info.INST_NAME.upper()
db_name = server_info.DB_NAME.upper()
return dict(dbms_name=dbms_name
, dbms_ver=dbms_ver
, inst_name=inst_name
, db_name=db_name
)
def run_test_046(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format FROM emp_photo")
else:
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(picture) FROM emp_photo")
row = ibm_db.fetch_tuple(result)
while ( row ):
if row[1] != 'xwd':
print("<a href='test_046.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a><br>" % (row[0], row[1], row[0], row[1], row[2]))
row = ibm_db.fetch_tuple(result)
def connectBD2():
# 连接数据库
# conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '')
conn = ibm_db.connect(conn_str, '', '')
# 获取数据库服务器信息
server = ibm_db.server_info(conn)
# 查询数据库sql
sql ="select * from emp"
if conn:
result = ibm_db.exec_immediate(conn, sql)
onerow = ibm_db.fetch_tuple(result)
while ( onerow ):
print(onerow)
onerow = ibm_db.fetch_tuple(result)
def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result,i))
statement = "SELECT * FROM tab_datetime"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_007(self):
options1 = {
ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN
}
options2 = {
ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY
}
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
print("")
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_160(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from emp_act")
row = ibm_db.fetch_both(result)
while (row):
#printf("%6s ",row[0])
#printf("%-6s ",row[1])
#printf("%3d ",row[2])
#printf("%9s ",row['EMPTIME'])
#printf("%10s ", row['EMSTDATE'])
#printf("%10s ", row['EMENDATE'])
#printf("%6s ", row[0])
#puts ""
print("%6s %-6s %3d %9s %10s %10s %6s " %
(row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'],
row['EMENDATE'], row[0]))
row = ibm_db.fetch_both(result)
def run_test_000(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
server = ibm_db.server_info(conn)
# Drop the animal table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the animal table
animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty',
350.0), (3, 'gold fish', 'Bubbles',
0.1), (4, 'budgerigar', 'Gizmo',
0.2), (5, 'goat', 'Rickety Ride',
9.7), (6, 'llama', 'Sweater', 150))
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
# Drop the test view, in case it exists
drop = 'DROP VIEW anime_cat'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create test view
ibm_db.exec_immediate(
conn, """CREATE VIEW anime_cat AS
SELECT name, breed FROM animals
WHERE id = 0""")
# Drop the animal_pics table
drop = 'DROP TABLE animal_pics'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal_pics table
create = 'CREATE TABLE animal_pics (name VARCHAR(32), picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
# Populate the view table
animals = (
('Spook', 'spook.png'),
('Helmut', 'pic1.jpg'),
)
insert = 'INSERT INTO animal_pics (name, picture) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, insert)
if (not stmt):
print("Attempt to prepare statement failed.")
return 0
for animal in animals:
name = animal[0]
with open(
os.path.dirname(os.path.abspath(__file__)) + '/' +
animal[1], 'rb') as fileHandle:
picture = fileHandle.read()
if (not picture):
print("Could not retrieve picture '%s'" % animal[1])
return 0
ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the department table, in case it exists
drop = 'DROP TABLE department'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the department table
create = 'CREATE TABLE department (deptno CHAR(3) NOT NULL, deptname VARCHAR(29) NOT NULL, mgrno CHAR(6), admrdept CHAR(3) NOT NULL, location CHAR(16))'
result = ibm_db.exec_immediate(conn, create)
# Populate the department table
department = (('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00',
None), ('B01', 'PLANNING', '000020', 'A00', None),
('C01', 'INFORMATION CENTER', '000030', 'A00',
None), ('D01', 'DEVELOPMENT CENTER', None, 'A00', None),
('D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', None),
('D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01',
None), ('E01', 'SUPPORT SERVICES', '000050', 'A00',
None), ('E11', 'OPERATIONS', '000090', 'E01',
None), ('E21', 'SOFTWARE SUPPORT',
'000100', 'E01', None))
insert = 'INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for dept in department:
result = ibm_db.execute(stmt, dept)
# Drop the emp_act table, in case it exists
drop = 'DROP TABLE emp_act'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_act table
create = 'CREATE TABLE emp_act (empno CHAR(6) NOT NULL, projno CHAR(6) NOT NULL, actno SMALLINT NOT NULL, emptime DECIMAL(5,2), emstdate DATE, emendate DATE)'
result = ibm_db.exec_immediate(conn, create)
# Populate the emp_act table
emp_act = (
('000010', 'MA2100', 10, 0.50, '1982-01-01',
'1982-11-01'), ('000010', 'MA2110', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000010', 'AD3100', 10, 0.50,
'1982-01-01', '1982-07-01'),
('000020', 'PL2100', 30, 1.00, '1982-01-01',
'1982-09-15'), ('000030', 'IF1000', 10, 0.50, '1982-06-01',
'1983-01-01'), ('000030', 'IF2000', 10, 0.50,
'1982-01-01', '1983-01-01'),
('000050', 'OP1000', 10, 0.25, '1982-01-01',
'1983-02-01'), ('000050', 'OP2010', 10, 0.75, '1982-01-01',
'1983-02-01'), ('000070', 'AD3110', 10, 1.00,
'1982-01-01', '1983-02-01'),
('000090', 'OP1010', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000100', 'OP2010', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000110', 'MA2100', 20, 1.00,
'1982-01-01', '1982-03-01'),
('000130', 'IF1000', 90, 1.00, '1982-01-01',
'1982-10-01'), ('000130', 'IF1000', 100, 0.50, '1982-10-01',
'1983-01-01'), ('000140', 'IF1000', 90, 0.50,
'1982-10-01', '1983-01-01'),
('000140', 'IF2000', 100, 1.00, '1982-01-01',
'1982-03-01'), ('000140', 'IF2000', 100, 0.50, '1982-03-01',
'1982-07-01'), ('000140', 'IF2000', 110, 0.50,
'1982-03-01', '1982-07-01'),
('000140', 'IF2000', 110, 0.50, '1982-10-01',
'1983-01-01'), ('000150', 'MA2112', 60, 1.00, '1982-01-01',
'1982-07-15'), ('000150', 'MA2112', 180, 1.00,
'1982-07-15', '1983-02-01'),
('000160', 'MA2113', 60, 1.00, '1982-07-15',
'1983-02-01'), ('000170', 'MA2112', 60, 1.00, '1982-01-01',
'1983-06-01'), ('000170', 'MA2112', 70, 1.00,
'1982-06-01', '1983-02-01'),
('000170', 'MA2113', 80, 1.00, '1982-01-01',
'1983-02-01'), ('000180', 'MA2113', 70, 1.00, '1982-04-01',
'1982-06-15'), ('000190', 'MA2112', 70, 1.00,
'1982-02-01', '1982-10-01'),
('000190', 'MA2112', 80, 1.00, '1982-10-01',
'1983-10-01'), ('000200', 'MA2111', 50, 1.00, '1982-01-01',
'1982-06-15'), ('000200', 'MA2111', 60, 1.00,
'1982-06-15', '1983-02-01'),
('000210', 'MA2113', 80, 0.50, '1982-10-01',
'1983-02-01'), ('000210', 'MA2113', 180, 0.50, '1982-10-01',
'1983-02-01'), ('000220', 'MA2111', 40, 1.00,
'1982-01-01', '1983-02-01'),
('000230', 'AD3111', 60, 1.00, '1982-01-01',
'1982-03-15'), ('000230', 'AD3111', 60, 0.50, '1982-03-15',
'1982-04-15'), ('000230', 'AD3111', 70, 0.50,
'1982-03-15', '1982-10-15'),
('000230', 'AD3111', 80, 0.50, '1982-04-15',
'1982-10-15'), ('000230', 'AD3111', 180, 1.00, '1982-10-15',
'1983-01-01'), ('000240', 'AD3111', 70, 1.00,
'1982-02-15', '1982-09-15'),
('000240', 'AD3111', 80, 1.00, '1982-09-15',
'1983-01-01'), ('000250', 'AD3112', 60, 1.00, '1982-01-01',
'1982-02-01'), ('000250', 'AD3112', 60, 0.50,
'1982-02-01', '1982-03-15'),
('000250', 'AD3112', 60, 0.50, '1982-12-01',
'1983-01-01'), ('000250', 'AD3112', 60, 1.00, '1983-01-01',
'1983-02-01'), ('000250', 'AD3112', 70, 0.50,
'1982-02-01', '1982-03-15'),
('000250', 'AD3112', 70, 1.00, '1982-03-15',
'1982-08-15'), ('000250', 'AD3112', 70, 0.25, '1982-08-15',
'1982-10-15'), ('000250', 'AD3112', 80, 0.25,
'1982-08-15', '1982-10-15'),
('000250', 'AD3112', 80, 0.50, '1982-10-15',
'1982-12-01'), ('000250', 'AD3112', 180, 0.50, '1982-08-15',
'1983-01-01'), ('000260', 'AD3113', 70, 0.50,
'1982-06-15', '1982-07-01'),
('000260', 'AD3113', 70, 1.00, '1982-07-01',
'1983-02-01'), ('000260', 'AD3113', 80, 1.00, '1982-01-01',
'1982-03-01'), ('000260', 'AD3113', 80, 0.50,
'1982-03-01', '1982-04-15'),
('000260', 'AD3113', 180, 0.50, '1982-03-01',
'1982-04-15'), ('000260', 'AD3113', 180, 1.00, '1982-04-15',
'1982-06-01'), ('000260', 'AD3113', 180, 0.50,
'1982-06-01', '1982-07-01'),
('000270', 'AD3113', 60, 0.50, '1982-03-01',
'1982-04-01'), ('000270', 'AD3113', 60, 1.00, '1982-04-01',
'1982-09-01'), ('000270', 'AD3113', 60, 0.25,
'1982-09-01', '1982-10-15'),
('000270', 'AD3113', 70, 0.75, '1982-09-01',
'1982-10-15'), ('000270', 'AD3113', 70, 1.00, '1982-10-15',
'1983-02-01'), ('000270', 'AD3113', 80, 1.00,
'1982-01-01', '1982-03-01'),
('000270', 'AD3113', 80, 0.50, '1982-03-01',
'1982-04-01'), ('000280', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000290', 'OP1010', 130, 1.00,
'1982-01-01', '1983-02-01'),
('000300', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000310', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000320', 'OP2011', 140, 0.75,
'1982-01-01', '1983-02-01'),
('000320', 'OP2011', 150, 0.25, '1982-01-01',
'1983-02-01'), ('000330', 'OP2012', 140, 0.25, '1982-01-01',
'1983-02-01'), ('000330', 'OP2012', 160, 0.75,
'1982-01-01', '1983-02-01'),
('000340', 'OP2013', 140, 0.50, '1982-01-01',
'1983-02-01'), ('000340', 'OP2013', 170, 0.50, '1982-01-01',
'1983-02-01'), ('000020', 'PL2100', 30, 1.00,
'1982-01-01', '1982-09-15'))
insert = 'INSERT INTO emp_act (empno, projno, actno, emptime, emstdate, emendate) VALUES (?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in emp_act:
result = ibm_db.execute(stmt, emp)
# Drop the employee table, in case it exists
drop = 'DROP TABLE employee'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the employee table
create = 'CREATE TABLE employee (empno CHAR(6) NOT NULL, firstnme VARCHAR(12) NOT NULL, midinit CHAR(1) NOT NULL, lastname VARCHAR(15) NOT NULL, workdept CHAR(3), phoneno CHAR(4), hiredate DATE, job CHAR(8), edlevel SMALLINT NOT NULL, sex CHAR(1), birthdate DATE, salary DECIMAL(9,2), bonus DECIMAL(9,2), comm DECIMAL(9,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the employee table
employee = (('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978',
'1965-01-01', 'PRES', 18, 'F', '1933-08-24', 52750.00,
1000, 4220), ('000020', 'MICHAEL', 'L', 'THOMPSON', 'B01',
'3476', '1973-10-10', 'MANAGER', 18, 'M',
'1948-02-02', 41250.00, 800, 3300),
('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738',
'1975-04-05', 'MANAGER', 20, 'F', '1941-05-11', 38250.00,
800, 3060), ('000050', 'JOHN', 'B', 'GEYER', 'E01',
'6789', '1949-08-17', 'MANAGER', 16, 'M',
'1925-09-15', 40175.00, 800, 3214),
('000060', 'IRVING', 'F', 'STERN', 'D11', '6423',
'1973-09-14', 'MANAGER', 16, 'M', '1945-07-07', 32250.00,
500, 2580), ('000070', 'EVA', 'D', 'PULASKI', 'D21',
'7831', '1980-09-30', 'MANAGER', 16, 'F',
'1953-05-26', 36170.00, 700, 2893),
('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498',
'1970-08-15', 'MANAGER', 16, 'F', '1941-05-15', 29750.00,
600, 2380), ('000100', 'THEODORE', 'Q', 'SPENSER', 'E21',
'0972', '1980-06-19', 'MANAGER', 14, 'M',
'1956-12-18', 26150.00, 500, 2092),
('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490',
'1958-05-16', 'SALESREP', 19, 'M', '1929-11-05', 46500.00,
900, 3720), ('000120', 'SEAN', '', 'OCONNELL', 'A00',
'2167', '1963-12-05', 'CLERK', 14, 'M',
'1942-10-18', 29250.00, 600, 2340),
('000130', 'DOLORES', 'M', 'QUINTANA', 'C01', '4578',
'1971-07-28', 'ANALYST', 16, 'F', '1925-09-15', 23800.00,
500, 1904), ('000140', 'HEATHER', 'A', 'NICHOLLS', 'C01',
'1793', '1976-12-15', 'ANALYST', 18, 'F',
'1946-01-19', 28420.00, 600, 2274),
('000150', 'BRUCE', '', 'ADAMSON', 'D11', '4510',
'1972-02-12', 'DESIGNER', 16, 'M', '1947-05-17', 25280.00,
500, 2022), ('000160', 'ELIZABETH', 'R', 'PIANKA', 'D11',
'3782', '1977-10-11', 'DESIGNER', 17, 'F',
'1955-04-12', 22250.00, 400, 1780),
('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890',
'1978-09-15', 'DESIGNER', 16, 'M', '1951-01-05', 24680.00,
500, 1974), ('000180', 'MARILYN', 'S', 'SCOUTTEN', 'D11',
'1682', '1973-07-07', 'DESIGNER', 17, 'F',
'1949-02-21', 21340.00, 500, 1707),
('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986',
'1974-07-26', 'DESIGNER', 16, 'M', '1952-06-25', 20450.00,
400, 1636), ('000200', 'DAVID', '', 'BROWN', 'D11',
'4501', '1966-03-03', 'DESIGNER', 16, 'M',
'1941-05-29', 27740.00, 600, 2217),
('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942',
'1979-04-11', 'DESIGNER', 17, 'M', '1953-02-23', 18270.00,
400, 1462), ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11',
'0672', '1968-08-29', 'DESIGNER', 18, 'F',
'1948-03-19', 29840.00, 600, 2387),
('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094',
'1966-11-21', 'CLERK', 14, 'M', '1935-05-30', 22180.00,
400, 1774), ('000240', 'SALVATORE', 'M', 'MARINO', 'D21',
'3780', '1979-12-05', 'CLERK', 17, 'M',
'1954-03-31', 28760.00, 600, 2301),
('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961',
'1969-10-30', 'CLERK', 15, 'M', '1939-11-12', 19180.00,
400, 1534), ('000260', 'SYBIL', 'P', 'JOHNSON', 'D21',
'8953', '1975-09-11', 'CLERK', 16, 'F',
'1936-10-05', 17250.00, 300, 1380),
('000270', 'MARIA', 'L', 'PEREZ', 'D21', '9001',
'1980-09-30', 'CLERK', 15, 'F', '1953-05-26', 27380.00,
500, 2190), ('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11',
'8997', '1967-03-24', 'OPERATOR', 17, 'F',
'1936-03-28', 26250.00, 500, 2100),
('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502',
'1980-05-30', 'OPERATOR', 12, 'M', '1946-07-09', 15340.00,
300, 1227), ('000300', 'PHILIP', 'X', 'SMITH', 'E11',
'2095', '1972-06-19', 'OPERATOR', 14, 'M',
'1936-10-27', 17750.00, 400, 1420),
('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332',
'1964-09-12', 'OPERATOR', 12, 'F', '1931-04-21', 15900.00,
300, 1272), ('000320', 'RAMLAL', 'V', 'MEHTA', 'E21',
'9990', '1965-07-07', 'FIELDREP', 16, 'M',
'1932-08-11', 19950.00, 400, 1596),
('000330', 'WING', '', 'LEE', 'E21', '2103', '1976-02-23',
'FIELDREP', 14, 'M', '1941-07-18', 25370.00, 500,
2030), ('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698',
'1947-05-05', 'FIELDREP', 16, 'M', '1926-05-17',
23840.00, 500, 1907))
insert = 'INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in employee:
result = ibm_db.execute(stmt, emp)
# Drop the emp_photo table, in case it exists
drop = 'DROP TABLE emp_photo'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_photo table
create = 'CREATE TABLE emp_photo (empno CHAR(6) NOT NULL, photo_format VARCHAR(10) NOT NULL, picture BLOB, PRIMARY KEY(empno, photo_format))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
# Populate the emp_photo table
emp_photo = (('000130', 'jpg', 'pic1.jpg'), ('000130', 'png',
'spook.png'),
('000140', 'jpg', 'pic1.jpg'), ('000140', 'png',
'spook.png'),
('000150', 'jpg', 'pic1.jpg'), ('000150', 'png',
'spook.png'),
('000190', 'jpg', 'pic1.jpg'), ('000190', 'png',
'spook.png'))
insert = 'INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for photo in emp_photo:
empno = photo[0]
photo_format = photo[1]
with open(
os.path.dirname(os.path.abspath(__file__)) + '/' +
photo[2], 'rb') as fileHandler:
picture = fileHandler.read()
ibm_db.bind_param(stmt, 1, empno, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, photo_format,
ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 3, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the org table, in case it exists
drop = 'DROP TABLE org'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the org table
create = 'CREATE TABLE org (deptnumb SMALLINT NOT NULL, deptname VARCHAR(14), manager SMALLINT, division VARCHAR(10), location VARCHAR(13))'
result = ibm_db.exec_immediate(conn, create)
# Populate the org table
org = ((10, 'Head Office', 160, 'Corporate',
'New York'), (15, 'New England', 50, 'Eastern', 'Boston'),
(20, 'Mid Atlantic', 10, 'Eastern',
'Washington'), (38, 'South Atlantic', 30, 'Eastern',
'Atlanta'), (42, 'Great Lakes', 100, 'Midwest',
'Chicago'), (51, 'Plains', 140,
'Midwest', 'Dallas'),
(66, 'Pacific', 270, 'Western',
'San Francisco'), (84, 'Mountain', 290, 'Western', 'Denver'))
insert = 'INSERT INTO org (deptnumb, deptname, manager, division, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for orgpart in org:
result = ibm_db.execute(stmt, orgpart)
# Drop the project table, in case it exists
drop = 'DROP TABLE project'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the project table
create = 'CREATE TABLE project (projno CHAR(6) NOT NULL, projname VARCHAR(24) NOT NULL, deptno CHAR(3) NOT NULL, respemp CHAR(6) NOT NULL, prstaff DECIMAL(5,2), prstdate DATE, prendate DATE, majproj CHAR(6))'
result = ibm_db.exec_immediate(conn, create)
# Populate the project table
project = (('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5,
'1982-01-01', '1983-02-01',
''), ('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070',
6, '1982-01-01', '1983-02-01', 'AD3100'),
('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2,
'1982-01-01', '1983-02-01', 'AD3110'),
('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1,
'1982-01-01', '1983-02-01', 'AD3110'),
('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2,
'1982-01-01', '1983-02-01',
'AD3110'), ('IF1000', 'QUERY SERVICES', 'C01', '000030', 2,
'1982-01-01', '1983-02-01', None),
('IF2000', 'USER EDUCATION', 'C01', '000030', 1,
'1982-01-01', '1983-02-01',
None), ('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010',
12, '1982-01-01', '1983-02-01', None),
('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9,
'1982-01-01', '1983-02-01', 'MA2100'),
('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2,
'1982-01-01', '1982-12-01',
'MA2110'), ('MA2112', 'W L ROBOT DESIGN', 'D11', '000150',
3, '1982-01-01', '1982-12-01', 'MA2110'),
('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3,
'1982-02-15', '1982-12-01',
'MA2110'), ('OP1000', 'OPERATION SUPPORT', 'E01', '000050',
6, '1982-01-01', '1983-02-01',
None), ('OP1010', 'OPERATION', 'E11', '000090',
5, '1982-01-01', '1983-02-01',
'OP1000'),
('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5,
'1982-01-01', '1983-02-01',
None), ('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4,
'1982-01-01', '1983-02-01',
'OP2000'), ('OP2011', 'SCP SYSTEMS SUPPORT', 'E21',
'000320', 1, '1982-01-01',
'1983-02-01', 'OP2010'),
('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1,
'1982-01-01', '1983-02-01',
'OP2010'), ('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1,
'1982-01-01', '1983-02-01',
'OP2010'), ('PL2100', 'WELD LINE PLANNING',
'B01', '000020', 1, '1982-01-01',
'1982-09-15', 'MA2100'))
insert = 'INSERT INTO project (projno, projname, deptno, respemp, prstaff, prstdate, prendate, majproj) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for proj in project:
result = ibm_db.execute(stmt, proj)
# Drop the sales table, in case it exists
drop = 'DROP TABLE sales'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the sales table
create = 'CREATE TABLE sales (sales_date DATE, sales_person VARCHAR(15), region VARCHAR(15), sales INT)'
result = ibm_db.exec_immediate(conn, create)
# Populate the sales table
sales = (('1995-12-31', 'LUCCHESSI', 'Ontario-South',
1), ('1995-12-31', 'LEE', 'Ontario-South',
3), ('1995-12-31', 'LEE', 'Quebec',
1), ('1995-12-31', 'LEE', 'Manitoba',
2), ('1995-12-31', 'GOUNOT', 'Quebec', 1),
('1996-03-29', 'LUCCHESSI', 'Ontario-South',
3), ('1996-03-29', 'LUCCHESSI', 'Quebec',
1), ('1996-03-29', 'LEE', 'Ontario-South',
2), ('1996-03-29', 'LEE', 'Ontario-North',
2), ('1996-03-29', 'LEE', 'Quebec',
3), ('1996-03-29', 'LEE', 'Manitoba', 5),
('1996-03-29', 'GOUNOT', 'Ontario-South',
3), ('1996-03-29', 'GOUNOT', 'Quebec',
1), ('1996-03-29', 'GOUNOT', 'Manitoba',
7), ('1996-03-30', 'LUCCHESSI', 'Ontario-South',
1), ('1996-03-30', 'LUCCHESSI', 'Quebec', 2),
('1996-03-30', 'LUCCHESSI', 'Manitoba',
1), ('1996-03-30', 'LEE', 'Ontario-South',
7), ('1996-03-30', 'LEE', 'Ontario-North',
3), ('1996-03-30', 'LEE', 'Quebec',
7), ('1996-03-30', 'LEE', 'Manitoba', 4),
('1996-03-30', 'GOUNOT', 'Ontario-South',
2), ('1996-03-30', 'GOUNOT', 'Quebec',
18), ('1996-03-30', 'GOUNOT', 'Manitoba',
1), ('1996-03-31', 'LUCCHESSI', 'Manitoba', 1),
('1996-03-31', 'LEE', 'Ontario-South',
14), ('1996-03-31', 'LEE', 'Ontario-North',
3), ('1996-03-31', 'LEE', 'Quebec',
7), ('1996-03-31', 'LEE', 'Manitoba',
3), ('1996-03-31', 'GOUNOT', 'Ontario-South',
2), ('1996-03-31', 'GOUNOT', 'Quebec',
1), ('1996-04-01', 'LUCCHESSI',
'Ontario-South', 3),
('1996-04-01', 'LUCCHESSI', 'Manitoba',
1), ('1996-04-01', 'LEE', 'Ontario-South',
8), ('1996-04-01', 'LEE', 'Ontario-North',
None), ('1996-04-01', 'LEE', 'Quebec',
8), ('1996-04-01', 'LEE', 'Manitoba', 9),
('1996-04-01', 'GOUNOT', 'Ontario-South',
3), ('1996-04-01', 'GOUNOT', 'Ontario-North',
1), ('1996-04-01', 'GOUNOT', 'Quebec',
3), ('1996-04-01', 'GOUNOT', 'Manitoba', 7))
insert = 'INSERT INTO sales (sales_date, sales_person, region, sales) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for sale in sales:
result = ibm_db.execute(stmt, sale)
# Drop the stored procedure, in case it exists
drop = 'DROP PROCEDURE match_animal'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the stored procedure
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(
conn, """
CREATE PROCEDURE match_animal(first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE PRECISION )
DEFINE match_name INT;
LET match_name = 0;
FOREACH c1 FOR
SELECT COUNT(*) INTO match_name FROM animals
WHERE name IN (second_name)
IF (match_name > 0)
THEN LET second_name = 'TRUE';
END IF;
END FOREACH;
FOREACH c2 FOR
SELECT SUM(weight) INTO animal_weight FROM animals WHERE name in (first_name, second_name)
END FOREACH;
END PROCEDURE;""")
else:
result = ibm_db.exec_immediate(
conn, """
CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE match_name INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT COUNT(*) FROM animals
WHERE name IN (second_name);
DECLARE c2 CURSOR FOR
SELECT SUM(weight) FROM animals
WHERE name in (first_name, second_name);
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, breed, weight FROM animals
WHERE name BETWEEN first_name AND second_name
ORDER BY name;
OPEN c1;
FETCH c1 INTO match_name;
IF (match_name > 0)
THEN SET second_name = 'TRUE';
END IF;
CLOSE c1;
OPEN c2;
FETCH c2 INTO animal_weight;
CLOSE c2;
OPEN c3;
END""")
result = None
# Drop the staff table, in case it exists
drop = 'DROP TABLE staff'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the staff table
create = 'CREATE TABLE staff (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT, job CHAR(5), years SMALLINT, salary DECIMAL(7,2), comm DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the staff table
staff = ((10, 'Sanders', 20, 'Mgr', 7, 18357.50,
None), (20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45),
(30, 'Marenghi', 38, 'Mgr', 5, 17506.75,
None), (40, 'OBrien', 38, 'Sales', 6, 18006.00, 846.55),
(50, 'Hanes', 15, 'Mgr', 10, 20659.80,
None), (60, 'Quigley', 38, 'Sales', None, 16808.30, 650.25),
(70, 'Rothman', 15, 'Sales', 7, 16502.83,
1152.00), (80, 'James', 20, 'Clerk', None, 13504.60, 128.20),
(90, 'Koonitz', 42, 'Sales', 6, 18001.75,
1386.70), (100, 'Plotz', 42, 'Mgr', 7, 18352.80,
None), (110, 'Ngan', 15, 'Clerk', 5, 12508.20,
206.60), (120, 'Naughton', 38, 'Clerk',
None, 12954.75, 180.00),
(130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90,
75.60), (140, 'Fraye', 51, 'Mgr', 6, 21150.00,
None), (150, 'Williams', 51, 'Sales', 6, 19456.50,
637.65), (160, 'Molinare', 10, 'Mgr', 7,
22959.20, None),
(170, 'Kermisch', 15, 'Clerk', 4, 12258.50,
110.10), (180, 'Abrahams', 38, 'Clerk', 3, 12009.75,
236.50), (190, 'Sneider', 20, 'Clerk', 8, 14252.75,
126.50), (200, 'Scoutten', 42, 'Clerk',
None, 11508.60, 84.20),
(210, 'Lu', 10, 'Mgr', 10, 20010.00,
None), (220, 'Smith', 51, 'Sales', 7, 17654.50,
992.80), (230, 'Lundquist', 51, 'Clerk', 3, 13369.80,
189.65), (240, 'Daniels', 10, 'Mgr', 5,
19260.25, None),
(250, 'Wheeler', 51, 'Clerk', 6, 14460.00,
513.30), (260, 'Jones', 10, 'Mgr', 12, 21234.00,
None), (270, 'Lea', 66, 'Mgr', 9, 18555.50, None),
(280, 'Wilson', 66, 'Sales', 9, 18674.50,
811.50), (290, 'Quill', 84, 'Mgr', 10, 19818.00,
None), (300, 'Davis', 84, 'Sales', 5, 15454.50,
806.10), (310, 'Graham', 66, 'Sales', 13,
21000.00, 200.30),
(320, 'Gonzales', 66, 'Sales', 4, 16858.20,
844.00), (330, 'Burke', 66, 'Clerk', 1, 10988.00,
55.50), (340, 'Edwards', 84, 'Sales', 7, 17844.00,
1285.00), (350, 'Gafney', 84, 'Clerk', 5,
13030.50, 188.00))
insert = 'INSERT INTO staff (id, name, dept, job, years, salary, comm) VALUES (?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in staff:
result = ibm_db.execute(stmt, emp)
try:
result = ibm_db.exec_immediate(conn, 'DROP TABLE t_string')
except:
pass
result = ibm_db.exec_immediate(
conn,
'CREATE TABLE t_string(a INTEGER, b DOUBLE PRECISION, c VARCHAR(100))'
)
print("Preperation complete")
def run_test_201(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
if (server == 'IDS'):
procedure = """CREATE FUNCTION multiResults ()
RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2);
DEFINE p_name CHAR(16);
DEFINE p_id INT;
DEFINE p_breed VARCHAR(32);
DEFINE p_weight NUMERIC(7,2);
FOREACH c1 FOR
SELECT name, id, breed, weight
INTO p_name, p_id, p_breed, p_weight
FROM animals
ORDER BY name DESC
RETURN p_name, p_id, p_breed, p_weight WITH RESUME;
END FOREACH;
END FUNCTION;"""
else:
procedure = """CREATE PROCEDURE multiResults ()
RESULT SETS 3
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
ORDER BY name;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, id, breed, weight
FROM animals
ORDER BY name DESC;
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name
FROM animals
ORDER BY name;
OPEN c1;
OPEN c2;
OPEN c3;
END"""
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ibm_db.exec_immediate(conn, procedure)
if sys.platform == 'zos':
stmt = ibm_db.exec_immediate(conn, 'CALL MULTIRESULTS()')
else:
stmt = ibm_db.exec_immediate(conn, 'CALL multiresults()')
print("Fetching first result set")
row = ibm_db.fetch_tuple(stmt)
while (row):
for i in row:
print(str(i).strip())
row = ibm_db.fetch_tuple(stmt)
if (server == 'IDS'):
print(
"Fetching second result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching second result set")
res = ibm_db.next_result(stmt)
if res:
row = ibm_db.fetch_tuple(res)
while (row):
for i in row:
print(str(i).strip())
row = ibm_db.fetch_tuple(res)
if (server == 'IDS'):
print(
"Fetching third result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching third result set")
res2 = ibm_db.next_result(stmt)
if res2:
row = ibm_db.fetch_tuple(res2)
while (row):
for i in row:
print(str(i).strip())
row = ibm_db.fetch_tuple(res2)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_024(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
'TEST_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, None, None)
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, None)
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, config.user,
'test_foreign_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, 'TEST_FOREIGN_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
try:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
None)
row = ibm_db.fetch_tuple(stmt)
except:
if (not stmt):
print(ibm_db.stmt_errormsg())
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, 'dummy_schema')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
'dummy_schema')
row = ibm_db.fetch_tuple(stmt)
if (not row):
print("No Data Found")
else:
print(row)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
def run_test_148(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
##### Set up #####
serverinfo = ibm_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
try:
sql = "DROP TABLE sptb"
ibm_db.exec_immediate(conn, sql)
except:
pass
try:
sql = "DROP PROCEDURE sp"
ibm_db.exec_immediate(conn, sql)
except:
pass
if (server == 'IDS'):
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 CLOB)"
else:
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 BIGINT, c5 CLOB)"
ibm_db.exec_immediate(conn, sql)
sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'clob data clob data')"
ibm_db.exec_immediate(conn, sql)
if (server == 'IDS'):
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 CLOB);
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
else:
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 BIGINT, OUT out5 CLOB)
DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END"""
ibm_db.exec_immediate(conn, sql)
#############################
##### Run the test #####
out1 = 0
out2 = 0.00
out3 = ""
out4 = 0
out5 = ""
stmt, out1, out2, out3, out4, out5 = ibm_db.callproc(
conn, 'sp', (out1, out2, out3, out4, out5))
print("out 1:")
print(out1)
print("out 2:")
print(out2)
print("out 3:")
print(out3)
print("out 4:")
print(out4)
print("out 5:")
print(out5)
#############################
else:
print("Connection failed.")
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
count = 1
while ( row ):
if (row['YEARS'] == None):
row['YEARS'] = ''
if (row['COMM'] == None):
row['COMM'] = ''
print row['ID'],row['NAME'],row['JOB'],row['YEARS'], row['SALARY'], row['COMM']
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2['ADMRDEPT'], row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
#__END__
#__LUW_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__ZOS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__SYSTEMI_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__IDS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
def run_test_createdbNX(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print 'Errors occurred during drop database'
try:
# call createdbNX without codeset argument when specified database not exeist
rc = ibm_db.createdbNX(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print 'database created sucessfully'
ibm_db.close(conn)
conn = False
else:
print 'database is not created'
else:
print 'Error occurred during create db if not exist'
conn = ibm_db.connect(conn_str, '', '')
if conn:
ibm_db.close(conn)
conn = False
# call recreate db with codeset argument when specified database exist
rc = ibm_db.createdbNX(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info( conn )
if conn and (server_info.DB_CODEPAGE != 819):
print 'database with codeset created sucessfully'
ibm_db.close(conn)
conn = False
else:
print 'Database not created'
else:
print 'Error occurred during create db if not exist with codeset'
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print 'datbase droped sucessfully'
if conn:
print 'Errors occurred during drop database'
ibm_db.close(conn)
conn = False
else:
print 'Errors occurred during drop database'
except:
print ibm_db.conn_errormsg()
pass
ibm_db.close(conn_attach)
else:
print ibm_db.conn_errormsg()
def run_test_createDropDB(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print('Errors occurred during drop database')
try:
#create databse
rc = ibm_db.createdb(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print('database created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Errors occurred during create database')
#drop databse
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during delete database')
#create database with codeset option
rc = ibm_db.createdb(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info( conn )
if conn and (server_info.DB_CODEPAGE == 819):
print('database with codeset created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Errors occurred during create database')
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during drop database')
except:
print(ibm_db.conn_errormsg())
pass
ibm_db.close(conn_attach)
else:
print(ibm_db.conn_errormsg())
def run_test_265(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
cursor_option = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
try:
sql = 'drop table test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
except:
pass
if ((server.DBMS_NAME[0:3] == 'IDS') or (server.DBMS_NAME[0:2] == "AS")):
sql = "create table test(id integer, name VARCHAR(10), clob_col CLOB, some_var VARCHAR(100) )"
else:
sql = "create table test(id integer, name VARCHAR(10), clob_col CLOB, some_var XML )"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = 'select id from test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = "insert into test values( 1, 'some', 'here is a clob value', '<?xml version=\"1.0\" encoding=\"UTF-8\" ?><test attribute=\"value\"/>')"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = "insert into test values(2, 'value', 'clob data', NULL)"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = "insert into test values(2, 'in varchar', 'data2', NULL)"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = 'select * from test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s, %s, %s\n" %(row[0], row[1], row[2], strip_bom(row[3])))
row = ibm_db.fetch_tuple(stmt)
sql = 'select id, name from test where id = ?'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt, (2,))
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s\n" %(row[0], row[1]))
row = ibm_db.fetch_tuple(stmt)
if (server.DBMS_NAME[0:3] == 'IDS'):
sql = "select * from test"
else:
sql = 'select * from test fetch first 12 rows only optimize for 12 rows'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
#ibm_db.num_fields(stmt)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s, %s, %s\n" % (row[0], row[1], row[2], strip_bom(row[3])))
row = ibm_db.fetch_tuple(stmt)
try:
sql = 'drop table test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
except:
pass
ibm_db.close(conn)
def run_test_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt,
(t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result, i))
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
row3 = ibm_db.result(stmt, 3)
print(row0)
print(row1)
print(row2)
print(row3)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
#__END__
#__LUW_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__ZOS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__SYSTEMI_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__IDS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
def run_test_200(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
if (server == 'IDS'):
procedure = """
CREATE FUNCTION multiResults()
RETURNING CHAR(16), INT;
DEFINE p_name CHAR(16);
DEFINE p_id INT;
FOREACH c1 FOR
SELECT name, id
INTO p_name, p_id
FROM animals
ORDER BY name
RETURN p_name, p_id WITH RESUME;
END FOREACH;
END FUNCTION;
"""
else:
procedure = """
CREATE PROCEDURE multiResults ()
RESULT SETS 3
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
ORDER BY name;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE id < 4
ORDER BY name DESC;
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE weight < 5.0
ORDER BY name;
OPEN c1;
OPEN c2;
OPEN c3;
END
"""
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ibm_db.exec_immediate(conn, procedure)
stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()')
#print(stmt)
print("Fetching first result set")
row = ibm_db.fetch_tuple(stmt)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
if (server == 'IDS'):
print(
"Fetching second result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching second result set")
#print(stmt)
res = ibm_db.next_result(stmt)
if res:
row = ibm_db.fetch_tuple(res)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res)
if (server == 'IDS'):
print(
"Fetching third result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching third result set")
res2 = ibm_db.next_result(stmt)
if res2:
row = ibm_db.fetch_tuple(res2)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res2)
print("Fetching fourth result set (should fail)")
res3 = ibm_db.next_result(stmt)
if res3:
row = ibm_db.fetch_tuple(res3)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res3)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_154(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
try:
statement = 'DROP TABLE fetch_test'
result = ibm_db.exec_immediate(conn, statement)
except:
pass
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
else:
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
result = ibm_db.exec_immediate(conn, statement)
result = ibm_db.exec_immediate(conn, st0)
result = ibm_db.exec_immediate(conn, st1)
result = ibm_db.exec_immediate(conn, st2)
result = ibm_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_tuple(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row[0],row[0].length, row[1],row[1].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row[0], len(row[0]), row[1], len(row[1])))
row = ibm_db.fetch_tuple(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_assoc(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2'])))
row = ibm_db.fetch_assoc(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_both(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row[1], row[1].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \
(row['COL1'],len(row['COL1']), row[1], len(row[1])))
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205),\
(30, "Megabyte", "98.65"),\
(40, "Visarsoft", Decimal("123.34")),\
(50, "Mailersoft", Decimal("134.222")),\
(60, "Kaerci", Decimal("100.976"))\
)
insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKSHARE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print("%s : %s : %s\n" % (data[0], data[1], data[2]))
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " % str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(
conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print("Test first index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST",
True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(
conn,
"CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE INDEX index2 ON index_test2 (data)")
print("Test second index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test2", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST2",
True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
print("Test non-existent table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"non_existent_table", True)
else:
result = ibm_db.statistics(conn, None, None,
"NON_EXISTENT_TABLE", True)
row = ibm_db.fetch_tuple(result)
if row:
print("Non-Empty")
else:
print("Empty")
else:
print('no connection: ' + ibm_db.conn_errormsg())
def run_test_bool_callproc(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info(conn)
if (not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn, "DROP PROCEDURE bool_procparams")
ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(
conn,
"CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
)
except:
pass
try:
procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
OPEN c1;
set param2 = parm1;
END"""
ibm_db.exec_immediate(conn, procedure)
except Exception as e:
print(str(e))
exit(-1)
try:
insert_sql = "INSERT INTO bool_test values(?, ?)"
stmt = ibm_db.prepare(conn, insert_sql)
rows = ((True, 'bindparam true'), (False, 'bindparam false'),
(None, 'bindparam None'))
for row in rows:
ibm_db.bind_param(stmt, 1, row[0])
ibm_db.bind_param(stmt, 2, row[1])
ibm_db.execute(stmt)
stmt = None
inparam = 11
outparam = -1
stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
(inparam, outparam))
print("Fetching first result set")
row = ibm_db.fetch_row(stmt)
while row:
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
row = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
def run_test_024(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS' ):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_primary_keys')
elif ('zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_PRIMARY_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, None, None, None, config.user, 'test_keys')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, None, None, None, config.user,'TEST_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, 'TEST_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, None, None)
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, None, None)
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, None)
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, config.user, 'test_foreign_keys')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, config.user, 'TEST_FOREIGN_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, 'TEST_FOREIGN_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
try:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, None)
row = ibm_db.fetch_tuple(stmt)
except:
if (not stmt):
print(ibm_db.stmt_errormsg())
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, 'dummy_schema')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, 'dummy_schema')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, 'dummy_schema')
row = ibm_db.fetch_tuple(stmt)
if(not row):
print("No Data Found")
else:
print(row)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
#__END__
#__LUW_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__ZOS_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__SYSTEMI_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#__IDS_EXPECTED__
#test_primary_keys
#id
#test_keys
#idf
#test_primary_keys
#id
#test_keys
#idf
#test_keys
#name
#test_foreign_keys
#namef
#test_keys
#name
#test_foreign_keys
#namef
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__ZOS_ODBC_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#{DB2 FOR OS/390}{ODBC DRIVER} SQLSTATE=HY009 ERRLOC=10:17:6 SQLCODE=-99999
#No Data Found
class IbmDbTestFunctions(unittest.TestCase):
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
# See the tests.py comments for this function.
def setUp(self):
pass
# This function captures the output of the current test file.
def capture(self, func):
buffer = StringIO()
sys.stdout = buffer
func()
sys.stdout = sys.__stdout__
# str() ensures not Unicode object on Python 2
var = str(buffer.getvalue())
var = var.replace('\n', '').replace('\r', '')
return var
# This function grabs the expected output of the current test function for LUW,
# located at the bottom of the current test file.
def expected_LUW(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__LUW_EXPECTED__')[-1].split(
'#__ZOS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for IDS,
# located at the bottom of the current test file.
def expected_IDS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__IDS_EXPECTED__')[-1].replace(
'\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_ZOS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__ZOS_EXPECTED__')[-1].split(
'#__SYSTEMI_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_AS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__SYSTEMI_EXPECTED__')[-1].split(
'#__IDS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function compares the captured outout with the expected out of
# the current test file.
def assert_expect(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
self.assertEqual(self.capture(testFuncName),
self.expected_AS(callstack[1][1]))
elif (self.server.DBMS_NAME == "DB2"):
self.assertEqual(self.capture(testFuncName),
self.expected_ZOS(callstack[1][1]))
elif (self.server.DBMS_NAME[0:3] == "IDS"):
self.assertEqual(self.capture(testFuncName),
self.expected_IDS(callstack[1][1]))
else:
self.assertEqual(self.capture(testFuncName),
self.expected_LUW(callstack[1][1]))
finally:
del callstack
# This function will compare using Regular Expressions
# based on the servre
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (self.server.DBMS_NAME == "DB2"):
pattern = self.expected_ZOS(callstack[1][1])
elif (self.server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[', '\]', '\(', '\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
if sys.version_info >= (3, 7):
pattern = re.sub('%d', r'\\d+', pattern)
else:
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
#def assert_throw_blocks(self, testFuncName):
# callstack = inspect.stack(0)
# try:
# This function needs to be declared here, regardless of if there
# is any body to this function
def runTest(self):
pass
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_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
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.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while (row):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print(str)
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: %s" % ibm_db.conn_errormsg())
class IbmDbTestFunctions(unittest.TestCase):
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
# See the tests.py comments for this function.
def setUp(self):
pass
# This function captures the output of the current test file.
def capture(self, func):
buffer = StringIO()
sys.stdout = buffer
func()
sys.stdout = sys.__stdout__
# str() ensures not Unicode object on Python 2
var = str(buffer.getvalue())
var = var.replace('\n', '').replace('\r', '')
return var
def testCasesIn(self, fileName):
if (fileName.startswith('ibm_db_tests/test_017') or \
fileName.startswith('ibm_db_tests/test_005') or \
fileName.startswith('ibm_db_tests/test_018') or \
fileName.startswith('ibm_db_tests/test_019') or \
fileName.startswith('ibm_db_tests/test_024') or \
fileName.startswith('ibm_db_tests/test_053') or \
fileName.startswith('ibm_db_tests/test_054') or \
fileName.startswith('ibm_db_tests/test_080') or \
fileName.startswith('ibm_db_tests/test_081') or \
fileName.startswith('ibm_db_tests/test_082') or \
fileName.startswith('ibm_db_tests/test_090') or \
fileName.startswith('ibm_db_tests/test_091') or \
fileName.startswith('ibm_db_tests/test_092') or \
fileName.startswith('ibm_db_tests/test_103') or \
fileName.startswith('ibm_db_tests/test_116') or \
fileName.startswith('ibm_db_tests/test_133') or \
fileName.startswith('ibm_db_tests/test_147') or \
fileName.startswith('ibm_db_tests/test_157a') or \
fileName.startswith('ibm_db_tests/test_240') or \
fileName.startswith('ibm_db_tests/test_241') or \
fileName.startswith('ibm_db_tests/test_cursortype') or \
fileName.startswith('ibm_db_tests/test_decfloat') or \
fileName.startswith('ibm_db_tests/test_setgetOption') or \
fileName.startswith('ibm_db_tests/test_warn') \
):
return True
else:
return False
# This function grabs the expected output of the current test function for LUW,
# located at the bottom of the current test file.
def expected_LUW(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__LUW_EXPECTED__')[-1].split(
'#__ZOS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for IDS,
# located at the bottom of the current test file.
def expected_IDS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__IDS_EXPECTED__')[-1].split(
'#__ZOS_ODBC_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_ZOS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__ZOS_EXPECTED__')[-1].split(
'#__SYSTEMI_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_AS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__SYSTEMI_EXPECTED__')[-1].split(
'#__IDS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for z/OS ODBC driver,
# located at the bottom of the current test file.
def expected_ZOS_ODBC(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split(
'#__ZOS_ODBC_EXPECTED__')[-1].replace('\n', "").replace('#', '')
fileHandle.close()
return fileInput
# This function compares the captured outout with the expected out of
# the current test file.
def assert_expect(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
self.assertEqual(self.capture(testFuncName),
self.expected_AS(callstack[1][1]))
elif (platform.system() == 'z/OS' or platform.system() == 'OS/390'
and self.testCasesIn(callstack[1][1])):
self.assertEqual(self.capture(testFuncName),
self.expected_ZOS_ODBC(callstack[1][1]))
elif (self.server.DBMS_NAME == "DB2"
or "DSN" in self.server.DBMS_NAME):
self.assertEqual(self.capture(testFuncName),
self.expected_ZOS(callstack[1][1]))
elif (self.server.DBMS_NAME[0:3] == "IDS"):
self.assertEqual(self.capture(testFuncName),
self.expected_IDS(callstack[1][1]))
else:
self.assertEqual(self.capture(testFuncName),
self.expected_LUW(callstack[1][1]))
finally:
del callstack
# This function will compare using Regular Expressions
# based on the server
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (platform.system() == 'z/OS' or platform.system() == 'OS/390'
and self.testCasesIn(callstack[1][1])):
pattern = self.expected_ZOS_ODBC(callstack[1][1])
elif (self.server.DBMS_NAME == "DB2"
or "DSN" in self.server.DBMS_NAME):
pattern = self.expected_ZOS(callstack[1][1])
elif (self.server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[', '\]', '\(', '\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
if sys.version_info >= (3, 7):
pattern = re.sub('%d', r'\\d+', pattern)
else:
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
#def assert_throw_blocks(self, testFuncName):
# callstack = inspect.stack(0)
# try:
# This function needs to be declared here, regardless of if there
# is any body to this function
def runTest(self):
pass
def run_test_066(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, config.user.lower(), 'animals')
else:
result = ibm_db.tables(conn, None, config.user.upper(), 'ANIMALS')
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(result)
class Row:
pass
data = ibm_db.fetch_assoc(result)
while (data):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print("Schema: %s" % row.table_schem)
print("Name: %s" % row.table_name)
print("Type: %s" % row.table_type)
print("Remarks: %s\n" % row.remarks)
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print("Schema: %s" % row.TABLE_SCHEM)
print("Name: %s" % row.TABLE_NAME)
print("Type: %s" % row.TABLE_TYPE)
print("Remarks: %s\n" % row.REMARKS)
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, config.user.lower(),
'animal_pics')
else:
result = ibm_db.tables(conn, None, config.user.upper(),
'ANIMAL_PICS')
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
while (data):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print("Schema: %s" % row.table_schem)
print("Name: %s" % row.table_name)
print("Type: %s" % row.table_type)
print("Remarks: %s\n" % row.remarks)
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print("Schema: %s" % row.TABLE_SCHEM)
print("Name: %s" % row.TABLE_NAME)
print("Type: %s" % row.TABLE_TYPE)
print("Remarks: %s\n" % row.REMARKS)
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, config.user.lower(),
'anime_cat')
else:
result = ibm_db.tables(conn, None, config.user.upper(),
'ANIME_CAT')
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
while (data):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print("Schema: %s" % row.table_schem)
print("Name: %s" % row.table_name)
print("Type: %s" % row.table_type)
print("Remarks: %s\n" % row.remarks)
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print("Schema: %s" % row.TABLE_SCHEM)
print("Name: %s" % row.TABLE_NAME)
print("Type: %s" % row.TABLE_TYPE)
print("Remarks: %s\n" % row.REMARKS)
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
ibm_db.free_result(result)
ibm_db.close(conn)
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
dsn_protocol, dsn_uid, dsn_pwd)
print(dsn)
#%%
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
#%%
server = ibm_db.server_info(conn)
print("DBMS_NAME: ", server.DBMS_NAME)
print("DBMS_VER: ", server.DBMS_VER)
print("DB_NAME: ", server.DB_NAME)
#%%
client = ibm_db.client_info(conn)
print("DRIVER_NAME: ", client.DRIVER_NAME)
print("DRIVER_VER: ", client.DRIVER_VER)
print("DATA_SOURCE_NAME: ", client.DATA_SOURCE_NAME)
print("DRIVER_ODBC_VER: ", client.DRIVER_ODBC_VER)
print("ODBC_VER: ", client.ODBC_VER)
print("ODBC_SQL_CONFORMANCE: ", client.ODBC_SQL_CONFORMANCE)
print("APPL_CODEPAGE: ", client.APPL_CODEPAGE)
svrConnection = None
serverInfo = False
# Create An Instance Of The Db2ConnectionMgr Class And Use It To Connect To A Db2 Server
conn = Db2ConnectionMgr('SERVER', '', '', '', userID, passWord)
conn.openConnection()
if conn.returnCode is True:
svrConnection = conn.connectionID
else:
conn.closeConnection()
exit(-1)
# Attempt To Obtain Information About The Db2 Server Being Used
print("Obtaining information about the server ... ", end="")
try:
serverInfo = ibm_db.server_info(svrConnection)
except Exception:
pass
# If Information About The Server Could Not Be Obtained, Display An Error Message
if serverInfo is False:
print("\nERROR: Unable to obtain server information.\n")
# Otherwise, Complete The Status Message; Then Format And Display The Data Retrieved
else:
print("Done!\n")
# Display A Report Header
print("Server details:")
print("_____________________________________________________________________________")
