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_6528(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.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 = IfxPy.prepare(conn, sql) var = "cat" IfxPy.bind_param(stmt, 1, var, IfxPy.SQL_PARAM_INPUT) self.checked_ids_execute(stmt) IfxPy.close(conn) else: print "Connection failed."
def run_test_102(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if (not conn): print IfxPy.conn_errormsg() server = IfxPy.server_info( conn ) if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2") and (server.DBMS_NAME[0:3] != "Inf")): result = IfxPy.exec_immediate(conn, "VALUES(1)") #throw :unsupported unless result if (not result): raise Exception('Unsupported') print IfxPy.num_fields(result) else: print '1' IfxPy.close(conn)
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_033(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info( conn ) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (IfxPy.fetch_row(stmt)): weight = IfxPy.result(stmt, 3) print("string(%d) \"%s\"" % (len(str(weight)), weight)) breed = IfxPy.result(stmt, 1) print("string(%d) \"%s\"" % (len(breed), breed)) name = IfxPy.result(stmt, "name") print("string(%d) \"%s\"" % (len(name), name)) IfxPy.close(conn) 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_036(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) result = IfxPy.exec_immediate(conn, "select * from staff") i = 0 row = IfxPy.fetch_row(result) while (row): result2 = IfxPy.exec_immediate(conn, "select * from staff") j = 0 row2 = IfxPy.fetch_row(result2) while (row2): print("%d)%d," % (i, j)) j += 1 row2 = IfxPy.fetch_row(result2) print("%d, " % i) i += 1 row = IfxPy.fetch_row(result) IfxPy.close(conn)
def run_test_300(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.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 IfxPy.close(conn) else: print "Error."
def run_test_201(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.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: IfxPy.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass IfxPy.exec_immediate(conn, procedure) stmt = IfxPy.exec_immediate(conn, 'CALL multiResults()') print "Fetching first result set" row = IfxPy.fetch_tuple(stmt) while (row): for i in row: print str(i).strip() row = IfxPy.fetch_tuple(stmt) IfxPy.close(conn) else: print "Connection failed."
def run_test_023(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (conn != 0): stmt = IfxPy.column_privileges(conn, None, config.user, 'animals') row = IfxPy.fetch_tuple(stmt) if row: print(row[0]) print(row[1]) print(row[2]) print(row[3]) print(row[4]) print(row[5]) print(row[6]) print(row[7]) IfxPy.close(conn) else: print(IfxPy.conn_errormsg()) print("Connection failed\n\n")
def run_test_019(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.SQL_ATTR_ROWCOUNT_PREFETCH: IfxPy.SQL_ROWCOUNT_PREFETCH_ON }) 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.close(conn) else: print "no connection:", IfxPy.conn_errormsg()
def run_test_070(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: if (type(conn) == IfxPy.IFXConnection): print("Resource is a Ifx Connection") rc = IfxPy.close(conn) print(rc) else: print("Connection failed.")
def run_test_200(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.server_info(conn) procedure = """ CREATE FUNCTION multiResults() RETURNING CHAR(16), INT; DEFINE p_name CHAR(16); DEFINE p_id INT; FOREACH c1 FOR SELECT name, id INTO p_name, p_id FROM animals ORDER BY name RETURN p_name, p_id WITH RESUME; END FOREACH; END FUNCTION; """ if conn: try: IfxPy.exec_immediate(conn, 'DROP PROCEDURE multiResults') except: pass IfxPy.exec_immediate(conn, procedure) stmt = IfxPy.exec_immediate(conn, 'CALL multiResults()') print "Fetching first result set" row = IfxPy.fetch_tuple(stmt) while (row): for i in row: print i row = IfxPy.fetch_tuple(stmt) IfxPy.close(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_310(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) client = IfxPy.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)) IfxPy.close(conn) else: print("Error.")
def run_test_025(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (conn != 0): drop = 'DROP TABLE test_primary_keys' try: result = IfxPy.exec_immediate(conn, drop) except: pass drop = 'DROP TABLE test_foreign_keys' try: result = IfxPy.exec_immediate(conn, drop) except: pass statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))' result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO test_primary_keys VALUES (1)" result = IfxPy.exec_immediate(conn, statement) statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))' result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO test_foreign_keys VALUES (1)" result = IfxPy.exec_immediate(conn, statement) if (server.DBMS_NAME[0:3] == 'Inf'): stmt = IfxPy.primary_keys(conn, None, config.user, 'test_primary_keys') else: stmt = IfxPy.primary_keys(conn, None, None, 'TEST_PRIMARY_KEYS') row = IfxPy.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[4]) IfxPy.close(conn) else: print(IfxPy.conn_errormsg()) print("Connection failed\n")
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_034(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate(conn, "select * from staff") row = IfxPy.fetch_assoc(result) if (row): #printf("%5d ",row['ID']) #printf("%-10s ",row['NAME']) #printf("%5d ",row['DEPT']) #printf("%-7s ",row['JOB']) #printf("%5d ", row['YEARS']) #printf("%15s ", row['SALARY']) #printf("%10s ", row['COMM']) #puts "" print("%5d %-10s %5d %-7s %5d %15s %10s" % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM'])) IfxPy.close(conn)
def run_test_015(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") if result: cols = IfxPy.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print("col:", cols) rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) result = IfxPy.exec_immediate(conn,"delete from t_string where a=123") if result: cols = IfxPy.num_fields(result) print("col:", cols) rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) IfxPy.close(conn) else: print("no connection:", IfxPy.conn_errormsg())
def run_test_6561(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate(conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)") statement = "SELECT count(id) FROM animals" result = IfxPy.exec_immediate(conn, statement) if ( (not result) and IfxPy.stmt_error() ): print("ERROR: %s" % (IfxPy.stmt_errormsg(), )) row = IfxPy.fetch_tuple(result) while ( row ): for i in row: print(i) row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) IfxPy.close(conn) else: print("Connection failed.")
def run_test_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."
def run_test_016(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") if result: cols = IfxPy.num_fields(result) print("col:", cols) rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) result = IfxPy.exec_immediate(conn, "delete from t_string where a=123") if result: cols = IfxPy.num_fields(result) print("col:", cols) rows = IfxPy.num_rows(result) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) IfxPy.close(conn) else: print("no connection:", IfxPy.conn_errormsg())
def run_test_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_103(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate( conn, "select * from org, project order by project.projname,org.deptnumb" ) cols = IfxPy.num_fields(result) j = 1 row = IfxPy.fetch_tuple(result) while (row): print "%d) " % j for i in range(0, cols): print "%s " % row[i] j += 1 if (j > 10): break row = IfxPy.fetch_tuple(result) IfxPy.close(conn) else: print IfxPy.conn_errormsg() #__END__ #__IDS_EXPECTED__ #1) 10 Head Office 160 Corporate New York AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #2) 15 New England 50 Eastern Boston AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #3) 20 Mid Atlantic 10 Eastern Washington AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #4) 38 South Atlantic 30 Eastern Atlanta AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #5) 42 Great Lakes 100 Midwest Chicago AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #6) 51 Plains 140 Midwest Dallas AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #7) 66 Pacific 270 Western San Francisco AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #8) 84 Mountain 290 Western Denver AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110 #9) 10 Head Office 160 Corporate New York AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01 #10) 15 New England 50 Eastern Boston AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01
def run_test_071(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: rc = IfxPy.close(conn) if (rc == True): print "IfxPy.close succeeded" else: print "IfxPy.close FAILED\n" else: print "%s" % IfxPy.conn_errormsg() print ",sqlstate=%s" % IfxPy.conn_error() print "%s" % IfxPy.conn_errormsg() print "%s" % IfxPy.conn_errormsg() print "%s" % IfxPy.conn_errormsg() print "%s" % IfxPy.conn_errormsg()
#!/root/anaconda3/bin/ipython # Environment settings for Database import os try: informix_dir=os.environ['INFORMIXDIR'] except KeyError: print ( "INFORMIX PATH is not setup - Exit ") os.exit() try: ld_lib_path=os.environ['LD_LIBRARY_PATH'] LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli:"+ld_lib_path os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH except KeyError: print ( "No environment set for LD_LIBRARY_PATH") LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli" os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH os.environ print(os.environ) import IfxPy conStr = "SERVER=pronto_net;DATABASE=sysmaster;HOST=pronto;SERVICE=9088;UID=informix;PWD=pronto1$;" conn = IfxPy.connect( conStr, "", "") sql = "select fname from syschunks" stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) while dictionary != False: print(dictionary[0]) dictionary = IfxPy.fetch_both(stmt) IfxPy.close(conn)
def run_test_024(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if conn != 0: drop = 'DROP TABLE test_primary_keys' try: result = IfxPy.exec_immediate(conn, drop) except: pass drop = 'DROP TABLE test_keys' try: result = IfxPy.exec_immediate(conn, drop) except: pass drop = 'DROP TABLE test_foreign_keys' try: result = IfxPy.exec_immediate(conn, drop) except: pass statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))' result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO test_primary_keys VALUES (1)" result = IfxPy.exec_immediate(conn, statement) statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \ PRIMARY KEY(name))' result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO test_keys VALUES ('vince', 1)" result = IfxPy.exec_immediate(conn, statement) statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))' result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)" result = IfxPy.exec_immediate(conn, statement) stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_primary_keys') row = IfxPy.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) stmt = IfxPy.foreign_keys(conn, None, None, None, None, config.user, 'test_keys') row = IfxPy.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys', None, None, None) row = IfxPy.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys', None, config.user, 'test_foreign_keys') row = IfxPy.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys', None, 'dummy_schema') row = IfxPy.fetch_tuple(stmt) if (not row): print("No Data Found") else: print(row[2]) print(row[3]) print(row[6]) print(row[7]) IfxPy.close(conn) else: print(IfxPy.conn_errormsg()) print("Connection failed\n")
def run_test_6792(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = IfxPy.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 = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)" result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)" stmt = IfxPy.prepare(conn, statement) result = IfxPy.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)" result = IfxPy.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)" stmt = IfxPy.prepare(conn, statement) result = IfxPy.execute(stmt, (t_val, d_val, ts_val, ts_withT_val)) statement = "SELECT * FROM table_6792" 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 table_6792" 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) row3 = IfxPy.result(stmt, 3) print row0 print row1 print row2 print row3 result = IfxPy.fetch_row(stmt) IfxPy.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 #None
def run_test_decimal(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info( conn ) drop = "DROP TABLE STOCKSHARE" try: result = IfxPy.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 = IfxPy.exec_immediate(conn, create) # Insert Directly insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)" result = IfxPy.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 = IfxPy.prepare(conn,insert) if stmt: for company in stockprice: result = IfxPy.execute(stmt,company) id = 70 company = 'Nirvana' stockshare = Decimal("100.1234") try: IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, company) IfxPy.bind_param(stmt, 3, stockshare) error = IfxPy.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 = 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\n" % (data[0], data[1], data[2]) 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) 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_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)