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_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_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_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_116(self): conn = None is_alive = ifx_db.active(conn) if is_alive: print "Is active" else: print "Is not active" conn = ifx_db.connect(config.ConnStr, config.user, config.password) is_alive = ifx_db.active(conn) if is_alive: print "Is active" else: print "Is not active" ifx_db.close(conn) is_alive = ifx_db.active(conn) if is_alive: print "Is active" else: print "Is not active" # Executing active method multiple times to reproduce a customer reported defect print ifx_db.active(conn) print ifx_db.active(conn) print ifx_db.active(conn) conn = ifx_db.connect(config.ConnStr, config.user, config.password) print ifx_db.active(conn) print ifx_db.active(conn) print ifx_db.active(conn)
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_020(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, ifx_db.SQL_AUTOCOMMIT_OFF) ac = ifx_db.autocommit(conn) if ac != 0: print "Cannot set ifx_db.SQL_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.rollback(conn) stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.fetch_tuple(stmt) rows = res[0] print rows ifx_db.close(conn) else: print "Connection failed."
def run_test_124(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: result = ifx_db.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 = ifx_db.num_fields(result) j = 0 row = ifx_db.fetch_both(result) while (row): for i in range(0, cols): field = ifx_db.field_name(result, i) value = row[ifx_db.field_name(result, i)] if (value == None): value = '' print "%s:%s" % (field, value) print "---------" j += 1 if (j == 10): break row = ifx_db.fetch_both(result) ifx_db.close(conn) print "done" else: print ifx_db.conn_errormsg()
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_022(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, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)") stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.fetch_tuple(stmt) rows = res[0] print rows ifx_db.rollback(conn) stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ifx_db.fetch_tuple(stmt) rows = res[0] print rows ifx_db.close(conn) else: print "Connection failed."
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_6561(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.exec_immediate( conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)" ) statement = "SELECT count(id) FROM animals" result = ifx_db.exec_immediate(conn, statement) if ((not result) and ifx_db.stmt_error()): print "ERROR: %s" % (ifx_db.stmt_errormsg(), ) row = ifx_db.fetch_tuple(result) while (row): for i in row: print i row = ifx_db.fetch_tuple(result) ifx_db.rollback(conn) ifx_db.close(conn) else: print "Connection failed."
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_310(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) client = ifx_db.client_info(conn) if client: print "DRIVER_NAME: string(%d) \"%s\"" % (len( client.DRIVER_NAME), client.DRIVER_NAME) print "DRIVER_VER: string(%d) \"%s\"" % (len( client.DRIVER_VER), client.DRIVER_VER) print "DATA_SOURCE_NAME: string(%d) \"%s\"" % (len( client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME) print "DRIVER_ODBC_VER: string(%d) \"%s\"" % (len( client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER) print "ODBC_VER: string(%d) \"%s\"" % (len( client.ODBC_VER), client.ODBC_VER) print "ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len( client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE) ifx_db.close(conn) else: print "Error." #__END__ #__LUW_EXPECTED__ #DRIVER_NAME: string(%d) %s #DRIVER_VER: string(%d) %s #DATA_SOURCE_NAME: string(%d) %s #DRIVER_ODBC_VER: string(%d) %s #ODBC_VER: string(%d) %s #ODBC_SQL_CONFORMANCE: string(%d) %s #APPL_CODEPAGE: int(%d) #CONN_CODEPAGE: int(%d) #__ZOS_EXPECTED__ #DRIVER_NAME: string(%d) %s #DRIVER_VER: string(%d) %s #DATA_SOURCE_NAME: string(%d) %s #DRIVER_ODBC_VER: string(%d) %s #ODBC_VER: string(%d) %s #ODBC_SQL_CONFORMANCE: string(%d) %s #APPL_CODEPAGE: int(%d) #CONN_CODEPAGE: int(%d) #__SYSTEMI_EXPECTED__ #DRIVER_NAME: string(%d) %s #DRIVER_VER: string(%d) %s #DATA_SOURCE_NAME: string(%d) %s #DRIVER_ODBC_VER: string(%d) %s #ODBC_VER: string(%d) %s #ODBC_SQL_CONFORMANCE: string(%d) %s #APPL_CODEPAGE: int(%d) #CONN_CODEPAGE: int(%d) #__IDS_EXPECTED__ #DRIVER_NAME: string(%d) %s #DRIVER_VER: string(%d) %s #DATA_SOURCE_NAME: string(%d) %s #DRIVER_ODBC_VER: string(%d) %s #ODBC_VER: string(%d) %s #ODBC_SQL_CONFORMANCE: string(%d) %s
def run_test_001(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: print "Connection succeeded." ifx_db.close(conn) else: print "Connection failed."
def run_test_005(self): baduser = "******" badpass = "******" dsn = "DATABASE=" + config.ConnStr + ";UID=" + baduser + ";PWD=" + badpass + ";" try: conn = ifx_db.connect(dsn, "", "") print "odd, ifx_db.connect succeeded with an invalid user / password" ifx_db.close(conn) except: print "Ooops"
def run_test_011(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.exec_immediate( conn, "DELETE FROM animals WHERE weight > 10.0") print "Number of affected rows: %d" % ifx_db.num_rows(stmt) ifx_db.rollback(conn) ifx_db.close(conn) else: print "Connection failed."
def run_test_003(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF) sql = 'UPDATE animals SET id = 9' res = ifx_db.exec_immediate(conn, sql) print "Number of affected rows: %d" % ifx_db.num_rows(res) ifx_db.rollback(conn) ifx_db.close(conn) 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_211(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) result = ifx_db.exec_immediate(conn, "select * from sales") i = 1 while (i <= ifx_db.num_fields(result)): #printf("%d size %d\n",i, ifx_db.field_display_size(result,i) || 0) print "%d size %d" % (i, ifx_db.field_display_size(result, i) or 0) i += 1 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_6528(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] == 'Inf'): sql = "SELECT TRIM(TRAILING FROM name) FROM animals WHERE breed = ?" else: sql = "SELECT RTRIM(name) FROM animals WHERE breed = ?" stmt = ifx_db.prepare(conn, sql) var = "cat" ifx_db.bind_param(stmt, 1, var, ifx_db.SQL_PARAM_INPUT) self.checked_db2_execute(stmt) ifx_db.close(conn) else: print "Connection failed."
def run_test_102(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if (not conn): print ifx_db.conn_errormsg() server = ifx_db.server_info(conn) if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2") and (server.DBMS_NAME[0:3] != "Inf")): result = ifx_db.exec_immediate(conn, "VALUES(1)") #throw :unsupported unless result if (not result): raise Exception('Unsupported') print ifx_db.num_fields(result) else: print '1' ifx_db.close(conn)
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_310(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) client = ifx_db.client_info(conn) if client: print "DRIVER_NAME: string(%d) \"%s\"" % (len(client.DRIVER_NAME), client.DRIVER_NAME) print "DRIVER_VER: string(%d) \"%s\"" % (len(client.DRIVER_VER), client.DRIVER_VER) print "DATA_SOURCE_NAME: string(%d) \"%s\"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME) print "DRIVER_ODBC_VER: string(%d) \"%s\"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER) print "ODBC_VER: string(%d) \"%s\"" % (len(client.ODBC_VER), client.ODBC_VER) print "ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE) print "APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE print "CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE ifx_db.close(conn) else: print "Error."
def run_test_030(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) name = ifx_db.result(stmt, "name") print "string(%d) \"%s\"" % (len(name), name) 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_036(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) result = ifx_db.exec_immediate(conn, "select * from staff") i=0 row = ifx_db.fetch_row(result) while ( row ): result2 = ifx_db.exec_immediate(conn, "select * from staff") j=0 row2 = ifx_db.fetch_row(result2) while ( row2 ): print "%d)%d," % (i, j) j+=1 row2 = ifx_db.fetch_row(result2) print "%d, " % i i+=1 row = ifx_db.fetch_row(result) ifx_db.close(conn)
def run_test_017(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: result = ifx_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN}) if result: rows = ifx_db.num_rows(result) print "affected row:", rows else: print ifx_db.stmt_errormsg() result = ifx_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY}) if result: rows = ifx_db.num_rows(result) print "affected row:", rows else: print ifx_db.stmt_errormsg() result = ifx_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH: ifx_db.SQL_ROWCOUNT_PREFETCH_ON }) if result: rows = ifx_db.num_rows(result) print "affected row:", rows else: print ifx_db.stmt_errormsg() result = ifx_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH: ifx_db.SQL_ROWCOUNT_PREFETCH_OFF }) if result: rows = ifx_db.num_rows(result) 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_201(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) serverinfo = ifx_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] procedure = """CREATE FUNCTION multiResults () RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2); DEFINE p_name CHAR(16); DEFINE p_id INT; DEFINE p_breed VARCHAR(32); DEFINE p_weight NUMERIC(7,2); FOREACH c1 FOR SELECT name, id, breed, weight INTO p_name, p_id, p_breed, p_weight FROM animals ORDER BY name DESC RETURN p_name, p_id, p_breed, p_weight WITH RESUME; END FOREACH; END FUNCTION;""" if conn: try: ifx_db.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass ifx_db.exec_immediate(conn, procedure) stmt = ifx_db.exec_immediate(conn, 'CALL multiResults()') print "Fetching first result set" row = ifx_db.fetch_tuple(stmt) while (row): for i in row: print str(i).strip() row = ifx_db.fetch_tuple(stmt) ifx_db.close(conn) else: print "Connection failed."