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_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_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_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_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_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_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_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_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_031(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: stmt = ifx_db.exec_immediate( conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ifx_db.fetch_row(stmt)): id = ifx_db.result(stmt, 0) print "int(%d)" % id breed = ifx_db.result(stmt, 1) print "string(%d) \"%s\"" % (len(breed), breed) name = ifx_db.result(stmt, 2) print "string(%d) \"%s\"" % (len(name), name) weight = ifx_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) ifx_db.close(conn) else: print "Connection failed."
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
def run_test_6792(self): conn = ifx_db.connect(config.ConnStr, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ifx_db.exec_immediate(conn, drop) except: pass t_val = '10:42:34' d_val = '1981-07-08' ts_val = '1981-07-08 10:42:34' ts_withT_val = '2013-06-06T15:30:39' server = ifx_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)" result = ifx_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (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 table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)" result = ifx_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)" stmt = ifx_db.prepare(conn, statement) result = ifx_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val)) statement = "SELECT * FROM table_6792" 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 table_6792" 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) row3 = ifx_db.result(stmt, 3) print row0 print row1 print row2 print row3 result = ifx_db.fetch_row(stmt) ifx_db.close(conn) else: print "Connection failed." #__END__ #__LUW_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__ZOS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__SYSTEMI_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__IDS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39