def run_test_113(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: drop = "DROP TABLE datetest" try: ifx_db.exec_immediate( conn, drop ) except: pass create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )" ifx_db.exec_immediate(conn, create) insert = "INSERT INTO datetest (id, mydate) VALUES (1,'03-27-1982')" ifx_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'07-08-1981')" ifx_db.exec_immediate(conn, insert) stmt = ifx_db.prepare(conn, "SELECT * FROM datetest") ifx_db.execute(stmt) result = ifx_db.fetch_row( stmt ) while ( result ): row0 = ifx_db.result(stmt, 0) row1 = ifx_db.result(stmt, 1) print row0 print row1 result = ifx_db.fetch_row( stmt ) else: print "Connection failed."
def run_test_114(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: drop = "drop table numericliteral" try: ifx_db.exec_immediate(conn, drop) except: pass create = "create table numericliteral ( id INTEGER, num INTEGER )" ifx_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, num) values (1,5)" ifx_db.exec_immediate(conn, insert) insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'" ifx_db.exec_immediate(conn, insert) stmt = ifx_db.prepare(conn, "SELECT * FROM numericliteral") ifx_db.execute(stmt) result = ifx_db.fetch_row(stmt) while (result): row0 = ifx_db.result(stmt, 0) row1 = ifx_db.result(stmt, 1) print row0 print row1 result = ifx_db.fetch_row(stmt) else: print "Connection failed."
def run_test_018(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_ON) if conn: stmt = ifx_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" ) ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = ifx_db.execute(stmt) if result: rows = ifx_db.num_rows(stmt) print "affected row:", rows ifx_db.free_result(stmt) else: print ifx_db.stmt_errormsg() ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2) result = ifx_db.execute(stmt) if result: rows = ifx_db.num_rows(stmt) print "affected row:", rows ifx_db.free_result(stmt) else: print ifx_db.stmt_errormsg() ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = ifx_db.execute(stmt) if result: rows = ifx_db.num_rows(stmt) print "affected row:", rows else: print ifx_db.stmt_errormsg() ifx_db.close(conn) else: print "no connection:", ifx_db.conn_errormsg()
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_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_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_312(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)" if conn: stmt = ifx_db.prepare(conn, query) params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji'] print("Binding parameters") for i, p in enumerate(params, 1): ifx_db.bind_param(stmt, i, Wrapper(p)) if ifx_db.execute(stmt): print("Executing statement") ifx_db.execute(stmt) # force the cache to be unbound for i, p in enumerate(params, 1): ifx_db.bind_param(stmt, i, p) ifx_db.rollback(conn) else: print("Connection failed.")
def run_test_warn(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: drop = "DROP TABLE TEST1" try: result = ifx_db.exec_immediate(conn,drop) except: pass # Create the table test1 create = "CREATE TABLE TEST1 (COL1 CHAR(5))" result = ifx_db.exec_immediate(conn, create) # Insert a string longer than 5 characters to force an error # ifx_db.stmt_warn() API query = 'INSERT INTO TEST1 VALUES (?)' stmt = ifx_db.prepare(conn, query) try: ifx_db.execute(stmt, ('ABCDEF',)) except: pass print(ifx_db.stmt_warn(stmt)) ifx_db.close(conn) 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_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_133(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if (not conn): print "Connection failed." return 0 ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) print "Starting test ..." res = '' sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" try: stmt = ifx_db.prepare(conn, sql) res = ifx_db.execute( stmt, (128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3)) stmt = ifx_db.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = ifx_db.execute(stmt, (128, )) row = ifx_db.fetch_assoc(stmt) for i in row: print i ifx_db.rollback(conn) print "Done" except: print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) try: stmt = ifx_db.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = ifx_db.execute(stmt, (128, )) row = ifx_db.fetch_assoc(stmt) if (row): for i in row: print i print res print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) except: print "An Exception is not expected" print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) ifx_db.rollback(conn) print "Done"
def run_test_147(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) stmt = ifx_db.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = "\"999\"" breed = None name = 'PythonDS' try: ifx_db.bind_param(stmt, 1, id) ifx_db.bind_param(stmt, 2, breed) ifx_db.bind_param(stmt, 3, name) error = ifx_db.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 = ifx_db.connect(config.ConnStr, config.user, config.password) ifx_db.autocommit(conn, ifx_db.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 = ifx_db.prepare(conn, insert1) animal = None ifx_db.bind_param(stmt, 1, animal) if ifx_db.execute(stmt): stmt = ifx_db.exec_immediate(conn, select) row = ifx_db.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print i row = ifx_db.fetch_tuple(stmt) ifx_db.rollback(conn) else: print "Connection failed."
def run_test_144(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: # Drop the test table, in case it exists drop = 'DROP TABLE pictures' try: result = ifx_db.exec_immediate(conn, drop) except: pass # Create the test table create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)' result = ifx_db.exec_immediate(conn, create) stmt = ifx_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)") picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg" rc = ifx_db.bind_param(stmt, 1, picture, ifx_db.SQL_PARAM_INPUT, ifx_db.SQL_BINARY) rc = ifx_db.execute(stmt) num = ifx_db.num_rows(stmt) print num else: print "Connection failed."
def run_test_157b(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'): stmt = ifx_db.prepare(conn, sql, { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN }) else: stmt = ifx_db.prepare( conn, sql, {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC}) result = ifx_db.execute(stmt) i = 2 row = ifx_db.fetch_assoc(stmt, i) while (row): if (server.DBMS_NAME[0:3] == 'IDS'): #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 = ifx_db.fetch_assoc(stmt, i)
def run_test_142(self): sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?" conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.prepare(conn, sql) weight = 200.05 mass = 2.0 ifx_db.bind_param(stmt, 1, weight, ifx_db.SQL_PARAM_INPUT) ifx_db.bind_param(stmt, 2, mass, ifx_db.SQL_PARAM_INPUT) result = ifx_db.execute(stmt) if ( result ): row = ifx_db.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print i row = ifx_db.fetch_tuple(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_warn(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 TABLE WITH_CLOB" try: result = ifx_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 = ifx_db.exec_immediate(conn, create) # Select the result from the table. This is just to verify we get appropriate warning using # ifx_db.stmt_warn() API query = 'SELECT * FROM WITH_CLOB' if (serverinfo.DBMS_NAME[0:3] != 'IDS'): 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) if data: print("Success") else: print("No Data") print(ifx_db.stmt_warn(stmt)) ifx_db.close(conn) else: print("Connection failed.")
def run_test_145(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) stmt = ifx_db.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = 999 breed = None name = 'PythonDS' ifx_db.bind_param(stmt, 1, id) ifx_db.bind_param(stmt, 2, breed) ifx_db.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 ifx_db.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 ifx_db.bind_param and ifx_db.execute treat Python Nones and empty # strings the right way. ifx_db.execute(stmt, (1000, None, 'PythonDS')) result = ifx_db.exec_immediate( conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL") row = ifx_db.fetch_tuple(result) while (row): for i in row: print i row = ifx_db.fetch_tuple(result) ifx_db.rollback(conn) else: print "Connection failed."
def run_test_261(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) 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 = ifx_db.prepare(conn, sql) ifx_db.execute(stmt, (0, )) # NOTE: This is a workaround # function fetch_object() to be implemented... # pet = ifx_db.fetch_object(stmt) # while (pet): # print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED) # pet = ifx_db.fetch_object(stmt) class Pet: pass data = ifx_db.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 = ifx_db.fetch_assoc(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_013(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = ifx_db.server_info(conn) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = ifx_db.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN }) else: stmt = ifx_db.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0") ifx_db.execute(stmt) data = ifx_db.fetch_both(stmt) while (data): print data[0] data = ifx_db.fetch_both(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_006(self): options1 = { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN } options2 = { ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY } conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = ifx_db.server_info(conn) if (serverinfo.DBMS_NAME[0:3] == 'IDS'): options1 = options2 stmt = ifx_db.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options2) ifx_db.execute(stmt) data = ifx_db.fetch_both(stmt) while (data): print data[0] data = ifx_db.fetch_both(stmt) print "" stmt = ifx_db.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options1) ifx_db.execute(stmt) data = ifx_db.fetch_both(stmt) while (data): print data[0] data = ifx_db.fetch_both(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_6755(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) server = ifx_db.server_info(conn) if conn: drop = 'DROP TABLE table_6755' result = '' try: result = ifx_db.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', 'database')" else: create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB(20))' insert = "INSERT INTO table_6755 VALUES ('database', 'database')" result = ifx_db.exec_immediate(conn, create) result = ifx_db.exec_immediate(conn, insert) statement = "SELECT col1, col2 FROM table_6755" result = ifx_db.prepare(conn, statement) ifx_db.execute(result) row = ifx_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 = ifx_db.fetch_tuple(result) ifx_db.close(conn) else: print "Connection failed."
def run_test_131(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?" ) if (ifx_db.execute(stmt, (0,))): row = ifx_db.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print i row = ifx_db.fetch_tuple(stmt) else: print "Connection failed."
def run_test_021(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.fetch_tuple(stmt) rows = res[0] print rows ifx_db.autocommit(conn, 0) ac = ifx_db.autocommit(conn) if ac != 0: print "Cannot set ifx_db.AUTOCOMMIT_OFF\nCannot run test" #continue ifx_db.exec_immediate(conn, "DELETE FROM animals") stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.fetch_tuple(stmt) rows = res[0] print rows ifx_db.commit(conn) stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.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 = ifx_db.prepare(conn, insert) if stmt: for animal in animals: result = ifx_db.execute(stmt, animal) ifx_db.commit(conn) ifx_db.close(conn) else: print "Connection failed."
def run_test_140(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?") animal = 0 ifx_db.bind_param(stmt, 1, animal) if ifx_db.execute(stmt): row = ifx_db.fetch_tuple(stmt) while ( row ): #roiw.each { |child| puts child } for i in row: print i row = ifx_db.fetch_tuple(stmt) else: print "Connection failed."
def run_test_141(self): sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?" conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.prepare(conn, sql) animal = 5 mass = 2.0 ifx_db.bind_param(stmt, 1, animal) ifx_db.bind_param(stmt, 2, mass) if ifx_db.execute(stmt): row = ifx_db.fetch_tuple(stmt) while (row): #row.each { |child| print child } for i in row: print i row = ifx_db.fetch_tuple(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_133(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if (not conn): print "Connection failed." return 0 ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) print "Starting test ..." res = '' sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" try: stmt = ifx_db.prepare(conn, sql) res = ifx_db.execute( stmt, (128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3)) stmt = ifx_db.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = ifx_db.execute(stmt, (128, )) row = ifx_db.fetch_assoc(stmt) for i in row: print i ifx_db.rollback(conn) print "Done" except: print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) try: stmt = ifx_db.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = ifx_db.execute(stmt, (128, )) row = ifx_db.fetch_assoc(stmt) if (row): for i in row: print i print res print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) except: print "An Exception is not expected" print "SQLSTATE: %s" % ifx_db.stmt_error(stmt) print "Message: %s" % ifx_db.stmt_errormsg(stmt) ifx_db.rollback(conn) print "Done" #__END__ #__LUW_EXPECTED__ #Starting test ... # #SQLSTATE: 22001 #Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999 #True #SQLSTATE: 02000 #Message: [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 #Done #__ZOS_EXPECTED__ #Starting test ... # #SQLSTATE: 22001 #Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999 #True #SQLSTATE: 02000 #Message: [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 #Done #__SYSTEMI_EXPECTED__ #Starting test ... # #SQLSTATE: 22001 #Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999 #True #SQLSTATE: 02000 #Message: [IBM][CLI Driver][AS] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100 #Done #__IDS_EXPECTED__ #Starting test ... # #SQLSTATE: 22001 #Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length. SQLCODE=-1279 #True #SQLSTATE: 02000 #Message: [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 #Done
def run_test_265(self): # Make a connection conn = ifx_db.connect(config.ConnStr, config.user, config.password) cursor_option = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC} 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) try: sql = 'drop table test' stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(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 = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) sql = 'select id from test' stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_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 = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) sql = "insert into test values(2, 'value', 'clob data', NULL)" stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) sql = "insert into test values(2, 'in varchar', 'data2', NULL)" stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) sql = 'select * from test' stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) row = ifx_db.fetch_tuple(stmt) while ( row ): print "%s, %s, %s, %s\n" %(row[0], row[1], row[2], ((row[3] is not None) and row[3].startswith(u'\ufeff')) and row[3][1:] or row[3]) row = ifx_db.fetch_tuple(stmt) sql = 'select id, name from test where id = ?' stmt = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) ifx_db.execute(stmt, (2,)) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) row = ifx_db.fetch_tuple(stmt) while ( row ): print "%s, %s\n" %(row[0], row[1]) row = ifx_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 = ifx_db.prepare(conn, sql) ifx_db.set_option(stmt, cursor_option, 0) #ifx_db.num_fields(stmt) ifx_db.execute(stmt) print "Number of affected rows: %d" % ifx_db.get_num_result(stmt) row = ifx_db.fetch_tuple(stmt) while ( row ): print "%s, %s, %s, %s\n" %(row[0], row[1], row[2], ((row[3] is not None) and row[3].startswith(u'\ufeff')) and row[3][1:] or row[3]) row = ifx_db.fetch_tuple(stmt) ifx_db.close(conn)
def run_test_decimal(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = ifx_db.server_info(conn) drop = "DROP TABLE STOCKSHARE" try: result = ifx_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 = ifx_db.exec_immediate(conn, create) # Insert Directly insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)" result = ifx_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 = ifx_db.prepare(conn, insert) if stmt: for company in stockprice: result = ifx_db.execute(stmt, company) id = 70 company = 'Nirvana' stockshare = Decimal("100.1234") try: ifx_db.bind_param(stmt, 1, id) ifx_db.bind_param(stmt, 2, company) ifx_db.bind_param(stmt, 3, stockshare) error = ifx_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] != '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\n" % (data[0], data[1], data[2]) 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) else: print "Connection failed." #__END__ #__LUW_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__ZOS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__IDS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.21 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.98 #70 : Nirvana : 100.12 #Requested row number must be a positive value