def run_test_061(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass if conn: server = ifx_db.server_info( conn ) op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.tables(conn, None, 't'); i = 0 row = ifx_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 = ifx_db.fetch_both(result) ifx_db.exec_immediate(conn, 'DROP TABLE t.t1') ifx_db.exec_immediate(conn, 'DROP TABLE t.t2') ifx_db.exec_immediate(conn, 'DROP TABLE t.t3') ifx_db.exec_immediate(conn, 'DROP TABLE t.t4') print "done!" else: print "no connection: %s" % ifx_db.conn_errormsg()
def run_test_155(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) result = ifx_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 = ifx_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 = ifx_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 = ifx_db.fetch_assoc(result) print "%d record(s) selected." % i
def run_test_146(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if conn: name = "Peaches" second_name = "Rickety Ride" weight = 0 print "Values of bound parameters _before_ CALL:" print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight) stmt, name, second_name, weight = ifx_db.callproc( conn, 'match_animal', (name, second_name, weight)) if stmt is not None: print "Values of bound parameters _after_ CALL:" print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight) if (server.DBMS_NAME[0:3] != 'IDS'): print "Results:" row = ifx_db.fetch_tuple(stmt) while (row): print " %s, %s, %s" % (row[0].strip(), row[1].strip(), row[2]) row = ifx_db.fetch_tuple(stmt)
def run_test_191(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if conn: if (server.DBMS_NAME[0:3] == 'IDS'): result = ifx_db.columns(conn,None,config.user,"emp_photo"); else: result = ifx_db.columns(conn,None,None,"EMP_PHOTO"); i = 0 row = ifx_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 = ifx_db.fetch_both(result) print "done!" else: print "no connection: ", ifx_db.conn_errormsg()
def run_test_032(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if conn: stmt = ifx_db.exec_immediate( conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6") while (ifx_db.fetch_row(stmt)): if (server.DBMS_NAME[0:3] == 'IDS'): id = ifx_db.result(stmt, "id") breed = ifx_db.result(stmt, "breed") name = ifx_db.result(stmt, "name") weight = ifx_db.result(stmt, "weight") else: id = ifx_db.result(stmt, "ID") breed = ifx_db.result(stmt, "BREED") name = ifx_db.result(stmt, "NAME") weight = ifx_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) ifx_db.close(conn) else: print "Connection failed."
def run_test_054(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals") val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0) print val op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY} stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0) print val if (serverinfo.DBMS_NAME[0:3] != 'IDS'): op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN} else: op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC} stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0) print val op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC} stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0) print val
def run_test_150(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.exec_immediate(conn, "select * from staff") row = ifx_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 = ifx_db.fetch_assoc(result)
def run_test_158(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'Inf'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50") output = '' row = ifx_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 = ifx_db.fetch_assoc(result) result2 = ifx_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')") row2 = ifx_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 = ifx_db.fetch_assoc(result2) result3 = ifx_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 = ifx_db.fetch_tuple(result3) while ( row3 ): output += row3[0] + ', ' + row3[3] + ', ' + row3[5] row3=ifx_db.fetch_tuple(result3) print output
def run_test_037(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) result = ifx_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101") row = ifx_db.fetch_row(result) while (row): if (serverinfo.DBMS_NAME[0:3] != 'IDS'): result2 = ifx_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN }) else: result2 = ifx_db.prepare(conn, "SELECT * FROM staff WHERE id < 101") ifx_db.execute(result2) row2 = ifx_db.fetch_row(result2) while (row2): print "%s : %s : %s : %s : %s" % (ifx_db.result(result2, 0), \ ifx_db.result(result2, 1), \ ifx_db.result(result2, 2), \ ifx_db.result(result2, 3), \ ifx_db.result(result2, 5)) row2 = ifx_db.fetch_row(result2) row = ifx_db.fetch_row(result)
def run_test_161(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.exec_immediate( conn, "select * from emp_act order by projno desc") row = ifx_db.fetch_both(result) count = 1 while (row): print "Record", count, ": %6s %-6s %3d %9s %10s %10s %6s " % ( row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0]) result2 = ifx_db.exec_immediate( conn, "select * from employee where employee.empno='" + row['EMPNO'] + "'") row2 = ifx_db.fetch_both(result2) if row2: print ">>%s,%s,%s,%s,%s,%s,%s" % ( row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'], row2[3], row2[3], row2[5], row2[6]) count = count + 1 if (count > 10): break row = ifx_db.fetch_both(result)
def run_test_152(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.exec_immediate(conn, "select * from project") row = ifx_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 = ifx_db.fetch_assoc(result)
def run_test_039(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result = ifx_db.prepare( conn, "SELECT * FROM animals", {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN}) else: result = ifx_db.prepare(conn, "SELECT * FROM animals") ifx_db.execute(result) row = ifx_db.fetch_row(result) while (row): if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result2 = ifx_db.prepare(conn, "SELECT * FROM animals", { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN }) else: result2 = ifx_db.prepare(conn, "SELECT * FROM animals") ifx_db.execute(result2) while (ifx_db.fetch_row(result2)): print "%s : %s : %s : %s" % (ifx_db.result(result2, 0), \ ifx_db.result(result2, 1), \ ifx_db.result(result2, 2), \ ifx_db.result(result2, 3)) row = ifx_db.fetch_row(result)
def run_test_03a(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if conn: stmt = ifx_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while ( ifx_db.fetch_row(stmt) ): breed = ifx_db.result(stmt, 1) print "string(%d) \"%s\"" % (len(breed), breed) if (server.DBMS_NAME[0:3] == 'IDS'): name = ifx_db.result(stmt, "name") else: name = ifx_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 = ifx_db.result(stmt, "passport") else: name = ifx_db.result(stmt, "PASSPORT") print name ifx_db.close(conn) else: print "Connection failed."
def run_test_300(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_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_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 ifx_db.close(conn) else: print "Error."
def run_test_spinout_timestamp(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) # Get the server type serverinfo = ifx_db.server_info(conn) if conn: drop = "DROP PROCEDURE PROC_TIMESTAMP" try: result = ifx_db.exec_immediate(conn, drop) except: pass # Create the SP with timestamp parameters create = "CREATE PROCEDURE PROC_TIMESTAMP ( INOUT PAR1 DATETIME YEAR TO FRACTION(5), OUT PAR2 DATETIME YEAR TO FRACTION(5)) LET PAR2 = PAR1; END PROCEDURE" result = ifx_db.exec_immediate(conn, create) # call the SP. Expect PAR2 to contain value passed to PAR1 par1 = "2017-05-13 22:47:29.82688" par2 = "" print "Values of bound parameters _before_ CALL:" print " 1: %s 2: %s\n" % (par1, par2) stmt, par1, par2 = ifx_db.callproc(conn, 'proc_timestamp', (par1, par2)) if stmt is not None: print "Values of bound parameters _after_ CALL:" print " 1: %s 2: %s\n" % (par1, par2) ifx_db.close(conn) else: print("Connection failed.")
def run_test_157a(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) print "Starting..." if conn: sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed" result = ifx_db.exec_immediate(conn, sql) try: i = 2 row = ifx_db.fetch_assoc(result, i) while (row): if (server.DBMS_NAME[0:3] == 'IDS'): print "%-5d %-16s %-32s %10s" % ( row['id'], row['name'], row['breed'], row['weight']) else: print "%-5d %-16s %-32s %10s" % ( row['ID'], row['NAME'], row['BREED'], row['WEIGHT']) i = i + 2 row = ifx_db.fetch_assoc(result, i) except: print "SQLSTATE: %s" % ifx_db.stmt_error(result) print "Message: %s" % ifx_db.stmt_errormsg(result) print "DONE"
def run_test_120(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if conn: stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals") name1 = ifx_db.field_name(stmt, 1) name2 = ifx_db.field_name(stmt, 2) name3 = ifx_db.field_name(stmt, 3) name4 = ifx_db.field_name(stmt, 4) name6 = ifx_db.field_name(stmt, 8) name7 = ifx_db.field_name(stmt, 0) if (server.DBMS_NAME[0:3] == 'IDS'): name5 = ifx_db.field_name(stmt, "id") name8 = ifx_db.field_name(stmt, "WEIGHT") else: name5 = ifx_db.field_name(stmt, "ID") name8 = ifx_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 run_test_157(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if conn: sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed" if (server.DBMS_NAME[0:3] != 'IDS'): result = ifx_db.exec_immediate(conn, sql, { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN }) else: result = ifx_db.exec_immediate( conn, sql, {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC}) i = 2 row = ifx_db.fetch_assoc(result, i) while (row): if (server.DBMS_NAME[0:3] == 'IDS'): print "%-5d %-16s %-32s %10s\n" % ( row['id'], row['name'], row['breed'], row['weight']) else: print "%-5d %-16s %-32s %10s\n" % ( row['ID'], row['NAME'], row['BREED'], row['WEIGHT']) i = i + 2 row = ifx_db.fetch_assoc(result, i)
def run_test_014(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) query = 'SELECT * FROM animals ORDER BY name' if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = ifx_db.prepare( conn, query, {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = ifx_db.prepare(conn, query) ifx_db.execute(stmt) data = ifx_db.fetch_both(stmt) while (data): print "%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]) data = ifx_db.fetch_both(stmt) try: stmt = ifx_db.prepare( conn, query, {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN}) ifx_db.execute(stmt) rc = ifx_db.fetch_row(stmt, -1) print "Fetch row -1: %s" % str(rc) except: print "Requested row number must be a positive value" ifx_db.close(conn)
def run_test_197(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if conn: try: rc = ifx_db.exec_immediate(conn, "DROP TABLE index_test") except: pass rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))") rc = ifx_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)") print "Test first index table:" if (server.DBMS_NAME[0:3] == 'Inf'): result = ifx_db.statistics(conn,None,config.user,"index_test",True) else: result = ifx_db.statistics(conn,None,None,"INDEX_TEST",True) row = ifx_db.fetch_tuple(result) ## skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'Inf'): row = ifx_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 = ifx_db.exec_immediate(conn, "DROP TABLE index_test2") except: pass rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))") rc = ifx_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)") print "Test second index table:" if (server.DBMS_NAME[0:3] == 'Inf'): result = ifx_db.statistics(conn,None,config.user,"index_test2",True) else: result = ifx_db.statistics(conn,None,None,"INDEX_TEST2",True) row = ifx_db.fetch_tuple(result) ### skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'Inf'): row = ifx_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] == 'Inf'): result = ifx_db.statistics(conn,None,config.user,"non_existent_table",True) else: result = ifx_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True) row = ifx_db.fetch_tuple(result) if row: print "Non-Empty" else: print "Empty" else: print 'no connection: ' + ifx_db.conn_errormsg()
def run_test_064(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass if (server.DBMS_NAME[0:3] == 'IDS'): result = ifx_db.tables(conn, None, 't') else: result = ifx_db.tables(conn, None, 'T') for i in range(0, ifx_db.num_fields(result)): print "%s, " % ifx_db.field_name(result, i) print print i = 0 row = ifx_db.fetch_tuple(result) while (row): ifx_db.num_fields(result) if (i < 4): print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n" i = i + 1 row = ifx_db.fetch_tuple(result) ifx_db.free_result(result) ifx_db.exec_immediate(conn, 'DROP TABLE t.t1') ifx_db.exec_immediate(conn, 'DROP TABLE t.t2') ifx_db.exec_immediate(conn, 'DROP TABLE t.t3') ifx_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_062(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass if conn: if (server.DBMS_NAME[0:3] == 'IDS'): schema = 't' else: schema = 'T' result = ifx_db.tables(conn, None, schema) i = 0 row = ifx_db.fetch_both(result) while (row): str = row[1] + "/" + row[2] + "/" + row[3] if (i < 4): print str i = i + 1 row = ifx_db.fetch_both(result) ifx_db.exec_immediate(conn, 'DROP TABLE t.t1') ifx_db.exec_immediate(conn, 'DROP TABLE t.t2') ifx_db.exec_immediate(conn, 'DROP TABLE t.t3') ifx_db.exec_immediate(conn, 'DROP TABLE t.t4') print "done!" else: print "no connection: #{ifx_db.conn_errormsg}"
def run_test_060(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass if conn: if (server.DBMS_NAME[0:3] == 'IDS'): result = ifx_db.tables(conn, None, 't') else: result = ifx_db.tables(conn, None, 'T') i = 0 row = ifx_db.fetch_both(result) while ( row ): if (i < 4): print "/%s/%s" % (row[1], row[2]) i = i + 1 row = ifx_db.fetch_both(result) ifx_db.exec_immediate(conn, 'DROP TABLE t.t1') ifx_db.exec_immediate(conn, 'DROP TABLE t.t2') ifx_db.exec_immediate(conn, 'DROP TABLE t.t3') ifx_db.exec_immediate(conn, 'DROP TABLE t.t4') print "done!" else: print "no connection: #{ifx_db.conn_errormsg}";
def run_test_148(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: ##### Set up ##### serverinfo = ifx_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] try: sql = "DROP TABLE sptb" ifx_db.exec_immediate(conn, sql) except: pass try: sql = "DROP PROCEDURE sp" ifx_db.exec_immediate(conn, sql) except: pass sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 VARCHAR(20))" ifx_db.exec_immediate(conn, sql) sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'varchar data')" ifx_db.exec_immediate(conn, sql) sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 VARCHAR(20)); SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;""" ifx_db.exec_immediate(conn, sql) ############################# ##### Run the test ##### out1 = 0 out2 = 0.00 out3 = "" out4 = 0 out5 = "" stmt, out1, out2, out3, out4, out5 = ifx_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_115(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) if conn: drop = "drop table numericliteral" try: ifx_db.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )" ifx_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')" ifx_db.exec_immediate(conn, insert) stmt = ifx_db.prepare(conn, "SELECT data FROM numericliteral") ifx_db.execute(stmt) # NOTE: This is a workaround # function fetch_object() to be implemented... # row = ifx_db.fetch_object(stmt, 0) class Row: pass row = Row() ifx_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ifx_db.result(stmt, 'DATA') else: row.DATA = ifx_db.result(stmt, 'data') print row.DATA insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'" ifx_db.exec_immediate(conn, insert) stmt = ifx_db.prepare(conn, "SELECT data FROM numericliteral") ifx_db.execute(stmt) # row = ifx_db.fetch_object(stmt, 0) ifx_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ifx_db.result(stmt, 'DATA') else: row.DATA = ifx_db.result(stmt, 'data') print row.DATA else: print "Connection failed."
def run_test_065(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ifx_db.exec_immediate(conn, create) except: pass result = ifx_db.tables(conn, None, '%', "t3") columns = ifx_db.num_fields(result) for i in range(0, columns): print "%s, " % ifx_db.field_name(result, i) print "\n\n" row = ifx_db.fetch_tuple(result) while (row): final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , " row = ifx_db.fetch_tuple(result) print final ifx_db.free_result(result) ifx_db.exec_immediate(conn, 'DROP TABLE t.t1') ifx_db.exec_immediate(conn, 'DROP TABLE t.t2') ifx_db.exec_immediate(conn, 'DROP TABLE t.t3') ifx_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_264(self): # Make a connection conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: server = ifx_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) # Drop the tab_bigint table, in case it exists drop = 'DROP TABLE tab_bigint' result = '' try: result = ifx_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 = ifx_db.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = ifx_db.exec_immediate(conn, insert) print "Number of inserted rows:", ifx_db.num_rows(res) stmt = ifx_db.prepare(conn, "SELECT * FROM tab_bigint") ifx_db.execute(stmt) data = ifx_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 = ifx_db.fetch_both(stmt) # test ifx_db.result for fetch of bigint stmt1 = ifx_db.prepare(conn, "SELECT col2 FROM tab_bigint") ifx_db.execute(stmt1) ifx_db.fetch_row(stmt1, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row1 = ifx_db.result(stmt1, 'COL2') else: row1 = ifx_db.result(stmt1, 'col2') print row1 ifx_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: drop = 'DROP TABLE tab_datetime' result = '' try: result = ifx_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 = ifx_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 = ifx_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ifx_db.prepare(conn, statement) result = ifx_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ifx_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ifx_db.prepare(conn, statement) result = ifx_db.execute(stmt, (t_val, d_val, ts_val)) statement = "SELECT * FROM tab_datetime" result = ifx_db.exec_immediate(conn, statement) for i in range(0, ifx_db.num_fields(result)): print str(i) + ":" + ifx_db.field_type(result, i) statement = "SELECT * FROM tab_datetime" stmt = ifx_db.prepare(conn, statement) rc = ifx_db.execute(stmt) result = ifx_db.fetch_row(stmt) while (result): row0 = ifx_db.result(stmt, 0) row1 = ifx_db.result(stmt, 1) row2 = ifx_db.result(stmt, 2) print type(row0), row0 print type(row1), row1 print type(row2), row2 result = ifx_db.fetch_row(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_212(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) result = ifx_db.exec_immediate(conn, "select * from sales") if (server.DBMS_NAME[0:3] == 'Inf'): i = "sales_person" else: i = "SALES_PERSON" print "%s size %d" % (i, ifx_db.field_display_size(result, i)) i = 2 print "%d size %d" % (i, ifx_db.field_display_size(result, i))
def run_test_159(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER} ifx_db.set_option(conn, op, 1) result = ifx_db.exec_immediate(conn, "select name,job from staff") i = 1 row = ifx_db.fetch_assoc(result) while ( row ): #printf("%3d %10s %10s\n",i, row['NAME'], row['JOB']) print "%3d %10s %10s" % (i, row['NAME'], row['JOB']) i += 1 row = ifx_db.fetch_assoc(result)