def run_test_015(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") if result: cols = IfxPy.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print "col:", cols rows = IfxPy.num_rows(result) print "affected row:", rows else: print IfxPy.stmt_errormsg() result = IfxPy.exec_immediate(conn, "delete from t_string where a=123") if result: cols = IfxPy.num_fields(result) print "col:", cols rows = IfxPy.num_rows(result) print "affected row:", rows else: print IfxPy.stmt_errormsg() IfxPy.close(conn) else: print "no connection:", IfxPy.conn_errormsg()
def run_test_040(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) # Drop the test table, in case it exists drop = 'DROP TABLE animals' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the test table create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))' result = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.exec_immediate(conn, "select * from animals") onerow = IfxPy.fetch_tuple(stmt) for element in onerow: print(element) IfxPy.rollback(conn)
def run_test_020(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) ac = IfxPy.autocommit(conn) if ac != 0: print("Cannot set IfxPy.SQL_AUTOCOMMIT_OFF\nCannot run test") #continue IfxPy.exec_immediate(conn, "DELETE FROM animals") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.rollback(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.close(conn) else: print("Connection failed.")
def run_test_warn(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: drop = "DROP TABLE TEST1" try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the table test1 create = "CREATE TABLE TEST1 (COL1 CHAR(5))" result = IfxPy.exec_immediate(conn, create) # Insert a string longer than 5 characters to force an error # IfxPy.stmt_warn() API query = 'INSERT INTO TEST1 VALUES (?)' stmt = IfxPy.prepare(conn, query) try: IfxPy.execute(stmt, ('ABCDEF', )) except: pass print((IfxPy.stmt_warn(stmt))) IfxPy.close(conn) else: print("Connection failed.")
def run_test_161(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate( conn, "select * from emp_act order by projno desc") row = IfxPy.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 = IfxPy.exec_immediate( conn, "select * from employee where employee.empno='" + row['EMPNO'] + "'") row2 = IfxPy.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 = IfxPy.fetch_both(result)
def run_test_180(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = '' result2 = '' try: result = IfxPy.exec_immediate( conn, "insert int0 t_string values(123,1.222333,'one to one')") except: pass if result: cols = IfxPy.num_fields(result) print("col:", cols, ", ") rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) try: result = IfxPy.exec_immediate( conn, "delete from t_string where a=123") except: pass if result: cols = IfxPy.num_fields(result) print("col:", cols, ", ") rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) else: print("no connection")
def run_test_157(self): conn = IfxPy.connect(config.ConnStr + 'ENABLESCROLLABLECURSORS=1', config.user, config.password) server = IfxPy.server_info(conn) if conn: sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed" if (server.DBMS_NAME[0:3] != 'Inf'): result = IfxPy.exec_immediate(conn, sql, { IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN }) else: result = IfxPy.exec_immediate( conn, sql, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_STATIC}) i = 2 row = IfxPy.fetch_assoc(result, i) while (row): if (server.DBMS_NAME[0:3] == 'Inf'): 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 = IfxPy.fetch_assoc(result, i)
def run_test_313(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type server = IfxPy.server_info(conn) try: sql = "drop table collection_tab;" stmt = IfxPy.exec_immediate(conn, sql) except: pass SetupSqlSet = [ "create table collection_tab ( col1 int, s1 SET(float not null), m1 MULTISET(char(20) not null), l1 LIST(bigint not null)) ;", "insert into collection_tab values( 1, SET{11.10, 12.11}, MULTISET{'Hey', 'Hell'}, LIST{120, -120});", "insert into collection_tab values( 2, SET{13.11, 14.20}, MULTISET{'Jhon', 'Nick'}, LIST{130, -130});", "insert into collection_tab values( 3, SET{14.21, 14.00}, MULTISET{'Dave', 'Mill'}, LIST{140, -141});", "insert into collection_tab values( 4, SET{15.0, 16}, MULTISET{'Mon', 'Phebee'}, LIST{150, -150});" ] i = 0 for sql in SetupSqlSet: i += 1 stmt = IfxPy.exec_immediate(conn, sql) sql = "select * from collection_tab;" stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) rc = 0 while tu != False: rc += 1 tu = IfxPy.fetch_tuple(stmt) print("Collection Data Access complete")
def run_test_231(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) result = IfxPy.exec_immediate(conn, "select * from sales") result2 = IfxPy.exec_immediate(conn, "select * from staff") result3 = IfxPy.exec_immediate(conn, "select * from emp_photo") for i in range(0, IfxPy.num_fields(result) + 1): print str(i) + ":" + str( IfxPy.field_type(result, IfxPy.field_name(result, i))) print "\n-----" for i in range(0, IfxPy.num_fields(result2)): print str(i) + ":" + IfxPy.field_type(result2, IfxPy.field_name(result2, i)) print "\n-----" for i in range(0, 3): print str(i) + ":" + IfxPy.field_type(result3, IfxPy.field_name(result3, i)) print "\n-----" print "region:%s" % IfxPy.field_type(result, 'region') print "5:%s" % IfxPy.field_type(result2, 5)
def run_test_022(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.autocommit(conn, 0) ac = IfxPy.autocommit(conn) if ac != 0: print "Cannot set IfxPy.AUTOCOMMIT_OFF\nCannot run test" #continue IfxPy.exec_immediate( conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.rollback(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.close(conn) else: print "Connection failed."
def run_test_158(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info( conn ) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50") output = '' row = IfxPy.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 = IfxPy.fetch_assoc(result) result2 = IfxPy.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')") row2 = IfxPy.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 = IfxPy.fetch_assoc(result2) result3 = IfxPy.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 = IfxPy.fetch_tuple(result3) while ( row3 ): output += row3[0] + ', ' + row3[3] + ', ' + row3[5] row3=IfxPy.fetch_tuple(result3) print output
def run_test_6561(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate( conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)" ) statement = "SELECT count(id) FROM animals" result = IfxPy.exec_immediate(conn, statement) if ((not result) and IfxPy.stmt_error()): print "ERROR: %s" % (IfxPy.stmt_errormsg(), ) row = IfxPy.fetch_tuple(result) while (row): for i in row: print i row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_038(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.server_info(conn) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result = IfxPy.exec_immediate( conn, "SELECT * FROM staff WHERE id < 101", {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: result = IfxPy.exec_immediate( conn, "SELECT * FROM staff WHERE id < 101") row = IfxPy.fetch_row(result) while (row): if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result2 = IfxPy.prepare(conn, "SELECT * FROM staff WHERE id < 101", { IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN }) else: result2 = IfxPy.prepare(conn, "SELECT * FROM staff WHERE id < 101") IfxPy.execute(result2) row2 = IfxPy.fetch_row(result2) while (row2): print "%s : %s : %s : %s : %s\n" % (IfxPy.result(result2, 0), \ IfxPy.result(result2, 1), \ IfxPy.result(result2, 2), \ IfxPy.result(result2, 3), \ IfxPy.result(result2, 5)) row2 = IfxPy.fetch_row(result2) row = IfxPy.fetch_row(result)
def run_test_144(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: # Drop the test table, in case it exists drop = 'DROP TABLE pictures' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the test table create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)' result = IfxPy.exec_immediate(conn, create) stmt = IfxPy.prepare(conn, "INSERT INTO pictures VALUES (0, ?)") picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg" rc = IfxPy.bind_param(stmt, 1, picture, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_BINARY) rc = IfxPy.execute(stmt) num = IfxPy.num_rows(stmt) print num else: print "Connection failed."
def run_test_spinout_timestamp(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type serverinfo = IfxPy.server_info(conn) if conn: drop = "DROP PROCEDURE PROC_TIMESTAMP" try: result = IfxPy.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 = IfxPy.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 = IfxPy.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) IfxPy.close(conn) else: print("Connection failed.")
def run_test_311(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON ) # Drop the tab_num_literals table, in case it exists drop = 'DROP TABLE tab_num_literals' result = '' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the animal table create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))" result = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')" res = IfxPy.exec_immediate(conn, insert) print "Number of inserted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44" res = IfxPy.exec_immediate(conn, sql) print "Number of updated rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'" res = IfxPy.exec_immediate(conn, sql) print "Number of deleted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) IfxPy.rollback(conn) IfxPy.close(conn)
def run_test_314(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type server = IfxPy.server_info(conn) try: sql = "drop table coll_param_tab;" stmt = IfxPy.exec_immediate(conn, sql) except: pass sql = "create table coll_param_tab (c1 int, c2 SET(VARCHAR(100)NOT NULL), c3 MULTISET(int not null), c4 LIST(int not null), c5 ROW(name varchar(15), addr varchar(15), zip varchar(15) ) );" stmt = IfxPy.exec_immediate(conn, sql) sql = "INSERT INTO coll_param_tab VALUES (?, ?, ?, ?, ?);" stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None c5 = None IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_VARCHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_VARCHAR, IfxPy.SQL_INFX_RC_ROW) i = 0 while i < 3: i += 1 c1 = 100 + i c2 = "SET{'Joe', 'Pheebes'}" c3 = "MULTISET{'1','2','3','4','5'}" c4 = "LIST{'10', '20', '30'}" c5 = "ROW('Pune', 'City', '411061')" IfxPy.execute(stmt, (c1, c2, c3, c4, c5)) sql = "SELECT * FROM coll_param_tab" stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) rc = 0 while tu != False: rc += 1 tu = IfxPy.fetch_tuple(stmt) print("Collection Param data access complete")
def run_test_148(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: ##### Set up ##### serverinfo = IfxPy.server_info( conn ) server = serverinfo.DBMS_NAME[0:3] try: sql = "DROP TABLE sptb" IfxPy.exec_immediate(conn, sql) except: pass try: sql = "DROP PROCEDURE sp" IfxPy.exec_immediate(conn, sql) except: pass sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 VARCHAR(20))" IfxPy.exec_immediate(conn, sql) sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'varchar data')" IfxPy.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;""" IfxPy.exec_immediate(conn, sql) ############################# ##### Run the test ##### out1 = 0 out2 = 0.00 out3 = "" out4 = 0 out5 = "" stmt, out1, out2, out3, out4, out5 = IfxPy.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_264(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) # Drop the tab_bigint table, in case it exists drop = 'DROP TABLE tab_bigint' result = '' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if (server.DBMS_NAME[0:3] == 'Inf'): 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 = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = IfxPy.exec_immediate(conn, insert) print "Number of inserted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT * FROM tab_bigint") IfxPy.execute(stmt) data = IfxPy.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 = IfxPy.fetch_both(stmt) # test IfxPy.result for fetch of bigint stmt1 = IfxPy.prepare(conn, "SELECT col2 FROM tab_bigint") IfxPy.execute(stmt1) IfxPy.fetch_row(stmt1, 0) if (server.DBMS_NAME[0:3] != 'Inf'): row1 = IfxPy.result(stmt1, 'COL2') else: row1 = IfxPy.result(stmt1, 'col2') print row1 IfxPy.close(conn)
def run_test_113(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: drop = "DROP TABLE datetest" try: IfxPy.exec_immediate(conn, drop) except: pass create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )" IfxPy.exec_immediate(conn, create) insert = "INSERT INTO datetest (id, mydate) VALUES (1,'03-27-1982')" IfxPy.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'07-08-1981')" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.prepare(conn, "SELECT * FROM datetest") IfxPy.execute(stmt) result = IfxPy.fetch_row(stmt) while (result): row0 = IfxPy.result(stmt, 0) row1 = IfxPy.result(stmt, 1) print row0 print row1 result = IfxPy.fetch_row(stmt) else: print "Connection failed."
def run_test_241(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) result = IfxPy.exec_immediate(conn, "select * from sales") result2 = IfxPy.exec_immediate(conn, "select * from staff") result3 = IfxPy.exec_immediate(conn, "select * from emp_photo") for i in range(0, IfxPy.num_fields(result)): print str(IfxPy.field_width(result,i)) print "\n-----" for i in range(0, IfxPy.num_fields(result2)): print str(IfxPy.field_width(result2,IfxPy.field_name(result2,i)))
def run_test_250(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) result = IfxPy.exec_immediate(conn, "select * from sales") result2 = IfxPy.exec_immediate(conn, "select * from staff") result3 = IfxPy.exec_immediate(conn, "select * from emp_photo") r1 = IfxPy.free_result(result) r2 = IfxPy.free_result(result2) r3 = IfxPy.free_result(result3) print r1 print r2 print r3
def run_test_InsertRetrieveDateTimeTypeColumn(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: drop = 'DROP TABLE tab_datetime' result = '' try: result = IfxPy.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 = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))" result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = IfxPy.prepare(conn, statement) result = IfxPy.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = IfxPy.prepare(conn, statement) result = IfxPy.execute(stmt, (t_val, d_val, ts_val)) statement = "SELECT * FROM tab_datetime" result = IfxPy.exec_immediate(conn, statement) for i in range(0, IfxPy.num_fields(result)): print str(i) + ":" + IfxPy.field_type(result, i) statement = "SELECT * FROM tab_datetime" stmt = IfxPy.prepare(conn, statement) rc = IfxPy.execute(stmt) result = IfxPy.fetch_row(stmt) while (result): row0 = IfxPy.result(stmt, 0) row1 = IfxPy.result(stmt, 1) row2 = IfxPy.result(stmt, 2) print type(row0), row0 print type(row1), row1 print type(row2), row2 result = IfxPy.fetch_row(stmt) IfxPy.close(conn) else: print "Connection failed."
def run_test_114(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: drop = "drop table numericliteral" try: IfxPy.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, num INTEGER )" IfxPy.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, num) values (1,5)" IfxPy.exec_immediate(conn, insert) insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.prepare(conn, "SELECT * FROM numericliteral") IfxPy.execute(stmt) result = IfxPy.fetch_row( stmt ) while ( result ): row0 = IfxPy.result(stmt, 0) row1 = IfxPy.result(stmt, 1) print row0 print row1 result = IfxPy.fetch_row( stmt ) else: print "Connection failed."
def run_test_152(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate(conn, "select * from project") row = IfxPy.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 = IfxPy.fetch_assoc(result)
def execute_sql(_self, sql_name): if sql_name not in _self.sql_matrix[_self.version].keys(): raise Exception( '{0} not in SQL Matrix for version {1} - bailing out.\n Please comment out the call using the sql statement in the collect() function.' .format(sql_name, _self.version)) sql = _self.sql_matrix[_self.version][sql_name] try: stat = IfxPy.exec_immediate(_self.connection, sql) except Exception, e: _self.print_error( "Could not execute SQL statement - are we connected? {0}". format(e)) _self.disconnect() _self.connect() if not _self.connection is None: stat = IfxPy.exec_immediate(_self.connection, sql)
def run_test_150(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info( conn ) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate(conn, "select * from staff") row = IfxPy.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 = IfxPy.fetch_assoc(result)
def run_test_110(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if conn: stmt = IfxPy.exec_immediate( conn, "SELECT * FROM animals ORDER BY breed") num1 = IfxPy.field_num(stmt, "id") num2 = IfxPy.field_num(stmt, "breed") num3 = IfxPy.field_num(stmt, "name") num4 = IfxPy.field_num(stmt, "weight") num5 = IfxPy.field_num(stmt, "test") num6 = IfxPy.field_num(stmt, 8) num7 = IfxPy.field_num(stmt, 1) num8 = IfxPy.field_num(stmt, "WEIGHT") print("int(%d)" % num1) print("int(%d)" % num2) print("int(%d)" % num3) print("int(%d)" % num4) print("%s" % num5) print("%s" % num6) print("int(%d)" % num7) print("%s" % num8) else: print("Connection failed.")
def run_test_124(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate( conn, "select * from staff, employee, org where employee.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') order by org.location,employee.lastname,staff.id" ) cols = IfxPy.num_fields(result) j = 0 row = IfxPy.fetch_both(result) while (row): for i in range(0, cols): field = IfxPy.field_name(result, i) value = row[IfxPy.field_name(result, i)] if (value == None): value = '' print("%s:%s" % (field, value)) print("---------") j += 1 if (j == 10): break row = IfxPy.fetch_both(result) IfxPy.close(conn) print("done") else: print(IfxPy.conn_errormsg())
def run_test_143(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)" select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL' if conn: stmt = IfxPy.prepare(conn, insert1) animal = None IfxPy.bind_param(stmt, 1, animal) if IfxPy.execute(stmt): stmt = IfxPy.exec_immediate(conn, select) row = IfxPy.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print(i) row = IfxPy.fetch_tuple(stmt) IfxPy.rollback(conn) else: print("Connection failed.")