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_018(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON) if conn: stmt = IfxPy.prepare(conn, "SELECT * from animals WHERE weight < 10.0" ) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) IfxPy.free_result(stmt) else: print(IfxPy.stmt_errormsg()) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_OFF}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) IfxPy.free_result(stmt) else: print(IfxPy.stmt_errormsg()) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) IfxPy.close(conn) else: print("no connection:", IfxPy.conn_errormsg())
def run_test_014(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.server_info(conn) query = 'SELECT * FROM animals ORDER BY name' if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = IfxPy.prepare( conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = IfxPy.prepare(conn, query) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print "%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]) data = IfxPy.fetch_both(stmt) try: stmt = IfxPy.prepare( conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) IfxPy.execute(stmt) rc = IfxPy.fetch_row(stmt, -1) print "Fetch row -1: %s" % str(rc) except: print "Requested row number must be a positive value" IfxPy.close(conn)
def run_test_006(self): options1 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN} options2 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_FORWARD_ONLY} conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info(conn) if (serverinfo.DBMS_NAME[0:3] == 'Inf'): options1 = options2 stmt = IfxPy.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options2) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print(data[0]) data = IfxPy.fetch_both(stmt) print("") stmt = IfxPy.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options1) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print(data[0]) data = IfxPy.fetch_both(stmt) IfxPy.close(conn) else: print("Connection failed.")
def run_test_312(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)" if conn: stmt = IfxPy.prepare(conn, query) params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji'] print("Binding parameters") for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, Wrapper(p)) if IfxPy.execute(stmt): print("Executing statement") IfxPy.execute(stmt) # force the cache to be unbound for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, p) IfxPy.rollback(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_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_039(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.server_info(conn) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result = IfxPy.prepare( conn, "SELECT * FROM animals", {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: result = IfxPy.prepare(conn, "SELECT * FROM animals") IfxPy.execute(result) row = IfxPy.fetch_row(result) while (row): if (serverinfo.DBMS_NAME[0:3] != 'Inf'): result2 = IfxPy.prepare(conn, "SELECT * FROM animals", { IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN }) else: result2 = IfxPy.prepare(conn, "SELECT * FROM animals") IfxPy.execute(result2) while (IfxPy.fetch_row(result2)): print("%s : %s : %s : %s" % (IfxPy.result(result2, 0), \ IfxPy.result(result2, 1), \ IfxPy.result(result2, 2), \ IfxPy.result(result2, 3))) row = IfxPy.fetch_row(result)
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_115(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) if conn: drop = "drop table numericliteral" try: IfxPy.exec_immediate(conn, drop) except: pass create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )" IfxPy.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.prepare(conn, "SELECT data FROM numericliteral") IfxPy.execute(stmt) # NOTE: This is a workaround # function fetch_object() to be implemented... # row = IfxPy.fetch_object(stmt, 0) class Row: pass row = Row() IfxPy.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'Inf'): row.DATA = IfxPy.result(stmt, 'DATA') else: row.DATA = IfxPy.result(stmt, 'data') print(row.DATA) insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.prepare(conn, "SELECT data FROM numericliteral") IfxPy.execute(stmt) # row = IfxPy.fetch_object(stmt, 0) IfxPy.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'Inf'): row.DATA = IfxPy.result(stmt, 'DATA') else: row.DATA = IfxPy.result(stmt, 'data') print(row.DATA) else: print("Connection failed.")
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_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_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_133(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if (not conn): print "Connection failed." return 0 IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) print "Starting test ..." res = '' sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" try: stmt = IfxPy.prepare(conn, sql) res = IfxPy.execute( stmt, (128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3)) stmt = IfxPy.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = IfxPy.execute(stmt, (128, )) row = IfxPy.fetch_assoc(stmt) for i in row: print i IfxPy.rollback(conn) print "Done" except: print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) try: stmt = IfxPy.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = IfxPy.execute(stmt, (128, )) row = IfxPy.fetch_assoc(stmt) if (row): for i in row: print i print res print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) except: print "An Exception is not expected" print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) IfxPy.rollback(conn) print "Done"
def run_test_142(self): sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?" conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare(conn, sql) weight = 200.05 mass = 2.0 IfxPy.bind_param(stmt, 1, weight, IfxPy.SQL_PARAM_INPUT) IfxPy.bind_param(stmt, 2, mass, IfxPy.SQL_PARAM_INPUT) result = IfxPy.execute(stmt) if (result): row = IfxPy.fetch_tuple(stmt) while (row): #row.each { |child| print child } for i in row: print i row = IfxPy.fetch_tuple(stmt) IfxPy.close(conn) else: print "Connection failed."
def run_test_157b(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'): stmt = IfxPy.prepare(conn, sql, { IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN }) else: stmt = IfxPy.prepare( conn, sql, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_STATIC}) result = IfxPy.execute(stmt) i = 2 row = IfxPy.fetch_assoc(stmt, i) while (row): if (server.DBMS_NAME[0:3] == 'Inf'): #printf("%-5d %-16s %-32s %10s\n", row['id'], row['name'], row['breed'], row['weight']) print( "%-5d %-16s %-32s %10s" % (row['id'], row['name'], row['breed'], row['weight'])) else: #printf("%-5d %-16s %-32s %10s\n", row['ID'], row['NAME'], row['BREED'], row['WEIGHT']) print( "%-5d %-16s %-32s %10s" % (row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])) i = i + 2 row = IfxPy.fetch_assoc(stmt, i)
def run_test_147(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = "\"999\"" breed = None name = 'PythonDS' try: IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) error = IfxPy.execute(stmt) print "Should not make it this far" except: excp = sys.exc_info() # slot 1 contains error message print excp[1] else: print "Connection failed."
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.")
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_013(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0") IfxPy.execute(stmt) data = IfxPy.fetch_both( stmt ) while (data): print data[0] data = IfxPy.fetch_both( stmt ) IfxPy.close(conn) else: print "Connection failed."
def run_test_145(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = 999 breed = None name = 'PythonDS' IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string IfxPy.execute(stmt) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string. # Use the dynamically bound parameters to ensure that the code paths # for both IfxPy.bind_param and IfxPy.execute treat Python Nones and empty # strings the right way. IfxPy.execute(stmt, (1000, None, 'PythonDS')) result = IfxPy.exec_immediate( conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL") row = IfxPy.fetch_tuple(result) while (row): for i in row: print(i) row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_261(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) if (server.DBMS_NAME[0:3] == 'Inf'): sql = "SELECT breed, TRIM(TRAILING FROM name) AS name FROM animals WHERE id = ?" else: sql = "SELECT breed, RTRIM(name) AS name FROM animals WHERE id = ?" if conn: stmt = IfxPy.prepare(conn, sql) IfxPy.execute(stmt, (0, )) # NOTE: This is a workaround # function fetch_object() to be implemented... # pet = IfxPy.fetch_object(stmt) # while (pet): # print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED) # pet = IfxPy.fetch_object(stmt) class Pet: pass data = IfxPy.fetch_assoc(stmt) while (data): pet = Pet() pet.NAME = data['NAME'] pet.BREED = data['BREED'] print("Come here, %s, my little %s!" % (pet.NAME, pet.BREED)) data = IfxPy.fetch_assoc(stmt) 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_6755(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if conn: drop = 'DROP TABLE table_6755' result = '' try: result = IfxPy.exec_immediate(conn, drop) except: pass if (server.DBMS_NAME[0:3] == 'Inf'): create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB)' insert = "INSERT INTO table_6755 VALUES ('database', FILETOCLOB('C:\\work\\IfxPy\\IfxPy\\tests\\data\\test_6755.txt','client'))" else: create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB(20))' insert = "INSERT INTO table_6755 VALUES ('database', FILETOCLOB('C:\\work\\IfxPy\\IfxPy\\tests\\data\\test_6755.txt','client'))" result = IfxPy.exec_immediate(conn, create) result = IfxPy.exec_immediate(conn, insert) statement = "SELECT col1, col2 FROM table_6755" result = IfxPy.prepare(conn, statement) IfxPy.execute(result) row = IfxPy.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 = IfxPy.fetch_tuple(result) IfxPy.close(conn) else: print("Connection failed.")
def run_test_130(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") if IfxPy.execute(stmt): row = IfxPy.fetch_tuple(stmt) while ( row ): for i in row: print i row = IfxPy.fetch_tuple(stmt) else: print "Connection failed."
def run_test_132(self): sql = "SELECT id, breed, name, weight FROM animals WHERE id = ? AND name = ?" conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare(conn, sql) if (IfxPy.execute(stmt, (0, 'Pook'))): row = IfxPy.fetch_tuple(stmt) while (row): #row.each { |child| print child } for i in row: print i row = IfxPy.fetch_tuple(stmt) else: print "Connection failed."
def run_test_021(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, "DELETE FROM animals") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.commit(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows # 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 = IfxPy.prepare(conn, insert) if stmt: for animal in animals: result = IfxPy.execute(stmt, animal) IfxPy.commit(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_140(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?") animal = 0 IfxPy.bind_param(stmt, 1, animal) if IfxPy.execute(stmt): row = IfxPy.fetch_tuple(stmt) while (row): #roiw.each { |child| puts child } for i in row: print(i) row = IfxPy.fetch_tuple(stmt) else: print("Connection failed.")
def run_test_049(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL' if conn: stmt = IfxPy.prepare(conn, insert) if IfxPy.execute(stmt, (None, 'ghost', None, None)): stmt = IfxPy.exec_immediate(conn, select) row = IfxPy.fetch_tuple(stmt) while (row): #row.each { |child| puts child } for child in row: print(child) row = IfxPy.fetch_tuple(stmt) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_141(self): sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?" conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare(conn, sql) animal = 5 mass = 2.0 IfxPy.bind_param(stmt, 1, animal) IfxPy.bind_param(stmt, 2, mass) if IfxPy.execute(stmt): row = IfxPy.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print i row = IfxPy.fetch_tuple(stmt) IfxPy.close(conn) else: print "Connection failed."