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("_____________________________________________________________________________")