def run_test_142(self): sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?" conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare(conn, sql) weight = 200.05 mass = 2.0 IfxPy.bind_param(stmt, 1, weight, IfxPy.SQL_PARAM_INPUT) IfxPy.bind_param(stmt, 2, mass, IfxPy.SQL_PARAM_INPUT) result = IfxPy.execute(stmt) if (result): row = IfxPy.fetch_tuple(stmt) while (row): #row.each { |child| print child } for i in row: print i row = IfxPy.fetch_tuple(stmt) IfxPy.close(conn) else: print "Connection failed."
def run_test_312(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)" if conn: stmt = IfxPy.prepare(conn, query) params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji'] print("Binding parameters") for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, Wrapper(p)) if IfxPy.execute(stmt): print("Executing statement") IfxPy.execute(stmt) # force the cache to be unbound for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, p) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_143(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)" select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL' if conn: stmt = IfxPy.prepare(conn, insert1) animal = None IfxPy.bind_param(stmt, 1, animal) if IfxPy.execute(stmt): stmt = IfxPy.exec_immediate(conn, select) row = IfxPy.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print(i) row = IfxPy.fetch_tuple(stmt) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_314(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type server = IfxPy.server_info(conn) try: sql = "drop table coll_param_tab;" stmt = IfxPy.exec_immediate(conn, sql) except: pass sql = "create table coll_param_tab (c1 int, c2 SET(VARCHAR(100)NOT NULL), c3 MULTISET(int not null), c4 LIST(int not null), c5 ROW(name varchar(15), addr varchar(15), zip varchar(15) ) );" stmt = IfxPy.exec_immediate(conn, sql) sql = "INSERT INTO coll_param_tab VALUES (?, ?, ?, ?, ?);" stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None c5 = None IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_VARCHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_VARCHAR, IfxPy.SQL_INFX_RC_ROW) i = 0 while i < 3: i += 1 c1 = 100 + i c2 = "SET{'Joe', 'Pheebes'}" c3 = "MULTISET{'1','2','3','4','5'}" c4 = "LIST{'10', '20', '30'}" c5 = "ROW('Pune', 'City', '411061')" IfxPy.execute(stmt, (c1, c2, c3, c4, c5)) sql = "SELECT * FROM coll_param_tab" stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) rc = 0 while tu != False: rc += 1 tu = IfxPy.fetch_tuple(stmt) print("Collection Param data access complete")
def run_test_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_144(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: # Drop the test table, in case it exists drop = 'DROP TABLE pictures' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the test table create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)' result = IfxPy.exec_immediate(conn, create) stmt = IfxPy.prepare(conn, "INSERT INTO pictures VALUES (0, ?)") picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg" rc = IfxPy.bind_param(stmt, 1, picture, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_BINARY) rc = IfxPy.execute(stmt) num = IfxPy.num_rows(stmt) print num else: print "Connection failed."
def run_test_140(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?") animal = 0 IfxPy.bind_param(stmt, 1, animal) if IfxPy.execute(stmt): row = IfxPy.fetch_tuple(stmt) while (row): #roiw.each { |child| puts child } for i in row: print(i) row = IfxPy.fetch_tuple(stmt) else: print("Connection failed.")
def run_test_147(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = "\"999\"" breed = None name = 'PythonDS' try: IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) error = IfxPy.execute(stmt) print "Should not make it this far" except: excp = sys.exc_info() # slot 1 contains error message print excp[1] else: print "Connection failed."
def run_test_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_145(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = 999 breed = None name = 'PythonDS' IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string IfxPy.execute(stmt) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string. # Use the dynamically bound parameters to ensure that the code paths # for both IfxPy.bind_param and IfxPy.execute treat Python Nones and empty # strings the right way. IfxPy.execute(stmt, (1000, None, 'PythonDS')) result = IfxPy.exec_immediate( conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL") row = IfxPy.fetch_tuple(result) while (row): for i in row: print(i) row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_000(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type server = IfxPy.server_info(conn) # Drop the animal table, in case it exists drop = 'DROP TABLE animals' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the animal table create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))' result = IfxPy.exec_immediate(conn, create) # 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) # Drop the test view, in case it exists drop = 'DROP VIEW anime_cat' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create test view IfxPy.exec_immediate( conn, """CREATE VIEW anime_cat AS SELECT name, breed FROM animals WHERE id = 0""") # Drop the animal_pics table drop = 'DROP TABLE animal_pics' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the animal_pics table create = 'CREATE TABLE animal_pics (name VARCHAR(32), picture BLOB)' result = IfxPy.exec_immediate(conn, create) # Populate the view table animals = (\ ('Spook', 'spook.png'),\ ('Helmut', 'pic1.jpg'),\ ) insert = 'INSERT INTO animal_pics (name, picture) VALUES (?, ?)' stmt = IfxPy.prepare(conn, insert) if (not stmt): print("Attempt to prepare statement failed.") return 0 for animal in animals: name = animal[0] fileHandle = open( os.path.dirname(os.path.abspath(__file__)) + '/' + animal[1], 'rb') picture = fileHandle.read() if (not picture): print("Could not retrieve picture '%s'." % animal[1]) return 0 IfxPy.bind_param(stmt, 1, name, IfxPy.SQL_PARAM_INPUT) IfxPy.bind_param(stmt, 2, picture, IfxPy.SQL_PARAM_INPUT) result = IfxPy.execute(stmt) # Drop the department table, in case it exists drop = 'DROP TABLE department' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the department table create = 'CREATE TABLE department (deptno CHAR(3) NOT NULL, deptname VARCHAR(29) NOT NULL, mgrno CHAR(6), admrdept CHAR(3) NOT NULL, location CHAR(16))' result = IfxPy.exec_immediate(conn, create) # Populate the department table department = (\ ('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00', None),\ ('B01', 'PLANNING', '000020', 'A00', None),\ ('C01', 'INFORMATION CENTER', '000030', 'A00', None),\ ('D01', 'DEVELOPMENT CENTER', None, 'A00', None),\ ('D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', None),\ ('D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01', None),\ ('E01', 'SUPPORT SERVICES', '000050', 'A00', None),\ ('E11', 'OPERATIONS', '000090', 'E01', None),\ ('E21', 'SOFTWARE SUPPORT', '000100', 'E01', None)\ ) insert = 'INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for dept in department: result = IfxPy.execute(stmt, dept) # Drop the emp_act table, in case it exists drop = 'DROP TABLE emp_act' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the emp_act table create = 'CREATE TABLE emp_act (empno CHAR(6) NOT NULL, projno CHAR(6) NOT NULL, actno SMALLINT NOT NULL, emptime DECIMAL(5,2), emstdate DATE, emendate DATE)' result = IfxPy.exec_immediate(conn, create) # Populate the emp_act table emp_act = (\ ('000010', 'MA2100', 10, 0.50, '1982-01-01', '1982-11-01'),\ ('000010', 'MA2110', 10, 1.00, '1982-01-01', '1983-02-01'),\ ('000010', 'AD3100', 10, 0.50, '1982-01-01', '1982-07-01'),\ ('000020', 'PL2100', 30, 1.00, '1982-01-01', '1982-09-15'),\ ('000030', 'IF1000', 10, 0.50, '1982-06-01', '1983-01-01'),\ ('000030', 'IF2000', 10, 0.50, '1982-01-01', '1983-01-01'),\ ('000050', 'OP1000', 10, 0.25, '1982-01-01', '1983-02-01'),\ ('000050', 'OP2010', 10, 0.75, '1982-01-01', '1983-02-01'),\ ('000070', 'AD3110', 10, 1.00, '1982-01-01', '1983-02-01'),\ ('000090', 'OP1010', 10, 1.00, '1982-01-01', '1983-02-01'),\ ('000100', 'OP2010', 10, 1.00, '1982-01-01', '1983-02-01'),\ ('000110', 'MA2100', 20, 1.00, '1982-01-01', '1982-03-01'),\ ('000130', 'IF1000', 90, 1.00, '1982-01-01', '1982-10-01'),\ ('000130', 'IF1000', 100, 0.50, '1982-10-01', '1983-01-01'),\ ('000140', 'IF1000', 90, 0.50, '1982-10-01', '1983-01-01'),\ ('000140', 'IF2000', 100, 1.00, '1982-01-01', '1982-03-01'),\ ('000140', 'IF2000', 100, 0.50, '1982-03-01', '1982-07-01'),\ ('000140', 'IF2000', 110, 0.50, '1982-03-01', '1982-07-01'),\ ('000140', 'IF2000', 110, 0.50, '1982-10-01', '1983-01-01'),\ ('000150', 'MA2112', 60, 1.00, '1982-01-01', '1982-07-15'),\ ('000150', 'MA2112', 180, 1.00, '1982-07-15', '1983-02-01'),\ ('000160', 'MA2113', 60, 1.00, '1982-07-15', '1983-02-01'),\ ('000170', 'MA2112', 60, 1.00, '1982-01-01', '1983-06-01'),\ ('000170', 'MA2112', 70, 1.00, '1982-06-01', '1983-02-01'),\ ('000170', 'MA2113', 80, 1.00, '1982-01-01', '1983-02-01'),\ ('000180', 'MA2113', 70, 1.00, '1982-04-01', '1982-06-15'),\ ('000190', 'MA2112', 70, 1.00, '1982-02-01', '1982-10-01'),\ ('000190', 'MA2112', 80, 1.00, '1982-10-01', '1983-10-01'),\ ('000200', 'MA2111', 50, 1.00, '1982-01-01', '1982-06-15'),\ ('000200', 'MA2111', 60, 1.00, '1982-06-15', '1983-02-01'),\ ('000210', 'MA2113', 80, 0.50, '1982-10-01', '1983-02-01'),\ ('000210', 'MA2113', 180, 0.50, '1982-10-01', '1983-02-01'),\ ('000220', 'MA2111', 40, 1.00, '1982-01-01', '1983-02-01'),\ ('000230', 'AD3111', 60, 1.00, '1982-01-01', '1982-03-15'),\ ('000230', 'AD3111', 60, 0.50, '1982-03-15', '1982-04-15'),\ ('000230', 'AD3111', 70, 0.50, '1982-03-15', '1982-10-15'),\ ('000230', 'AD3111', 80, 0.50, '1982-04-15', '1982-10-15'),\ ('000230', 'AD3111', 180, 1.00, '1982-10-15', '1983-01-01'),\ ('000240', 'AD3111', 70, 1.00, '1982-02-15', '1982-09-15'),\ ('000240', 'AD3111', 80, 1.00, '1982-09-15', '1983-01-01'),\ ('000250', 'AD3112', 60, 1.00, '1982-01-01', '1982-02-01'),\ ('000250', 'AD3112', 60, 0.50, '1982-02-01', '1982-03-15'),\ ('000250', 'AD3112', 60, 0.50, '1982-12-01', '1983-01-01'),\ ('000250', 'AD3112', 60, 1.00, '1983-01-01', '1983-02-01'),\ ('000250', 'AD3112', 70, 0.50, '1982-02-01', '1982-03-15'),\ ('000250', 'AD3112', 70, 1.00, '1982-03-15', '1982-08-15'),\ ('000250', 'AD3112', 70, 0.25, '1982-08-15', '1982-10-15'),\ ('000250', 'AD3112', 80, 0.25, '1982-08-15', '1982-10-15'),\ ('000250', 'AD3112', 80, 0.50, '1982-10-15', '1982-12-01'),\ ('000250', 'AD3112', 180, 0.50, '1982-08-15', '1983-01-01'),\ ('000260', 'AD3113', 70, 0.50, '1982-06-15', '1982-07-01'),\ ('000260', 'AD3113', 70, 1.00, '1982-07-01', '1983-02-01'),\ ('000260', 'AD3113', 80, 1.00, '1982-01-01', '1982-03-01'),\ ('000260', 'AD3113', 80, 0.50, '1982-03-01', '1982-04-15'),\ ('000260', 'AD3113', 180, 0.50, '1982-03-01', '1982-04-15'),\ ('000260', 'AD3113', 180, 1.00, '1982-04-15', '1982-06-01'),\ ('000260', 'AD3113', 180, 0.50, '1982-06-01', '1982-07-01'),\ ('000270', 'AD3113', 60, 0.50, '1982-03-01', '1982-04-01'),\ ('000270', 'AD3113', 60, 1.00, '1982-04-01', '1982-09-01'),\ ('000270', 'AD3113', 60, 0.25, '1982-09-01', '1982-10-15'),\ ('000270', 'AD3113', 70, 0.75, '1982-09-01', '1982-10-15'),\ ('000270', 'AD3113', 70, 1.00, '1982-10-15', '1983-02-01'),\ ('000270', 'AD3113', 80, 1.00, '1982-01-01', '1982-03-01'),\ ('000270', 'AD3113', 80, 0.50, '1982-03-01', '1982-04-01'),\ ('000280', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\ ('000290', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\ ('000300', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\ ('000310', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\ ('000320', 'OP2011', 140, 0.75, '1982-01-01', '1983-02-01'),\ ('000320', 'OP2011', 150, 0.25, '1982-01-01', '1983-02-01'),\ ('000330', 'OP2012', 140, 0.25, '1982-01-01', '1983-02-01'),\ ('000330', 'OP2012', 160, 0.75, '1982-01-01', '1983-02-01'),\ ('000340', 'OP2013', 140, 0.50, '1982-01-01', '1983-02-01'),\ ('000340', 'OP2013', 170, 0.50, '1982-01-01', '1983-02-01'),\ ('000020', 'PL2100', 30, 1.00, '1982-01-01', '1982-09-15')\ ) insert = 'INSERT INTO emp_act (empno, projno, actno, emptime, emstdate, emendate) VALUES (?, ?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for emp in emp_act: result = IfxPy.execute(stmt, emp) # Drop the employee table, in case it exists drop = 'DROP TABLE employee' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the employee table create = 'CREATE TABLE employee (empno CHAR(6) NOT NULL, firstnme VARCHAR(12) NOT NULL, midinit CHAR(1) NOT NULL, lastname VARCHAR(15) NOT NULL, workdept CHAR(3), phoneno CHAR(4), hiredate DATE, job CHAR(8), edlevel SMALLINT NOT NULL, sex CHAR(1), birthdate DATE, salary DECIMAL(9,2), bonus DECIMAL(9,2), comm DECIMAL(9,2))' result = IfxPy.exec_immediate(conn, create) # Populate the employee table employee = (('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978', '1965-01-01', 'PRES', 18, 'F', '1933-08-24', 52750.00, 1000, 4220), ('000020', 'MICHAEL', 'L', 'THOMPSON', 'B01', '3476', '1973-10-10', 'MANAGER', 18, 'M', '1948-02-02', 41250.00, 800, 3300), ('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', '1975-04-05', 'MANAGER', 20, 'F', '1941-05-11', 38250.00, 800, 3060), ('000050', 'JOHN', 'B', 'GEYER', 'E01', '6789', '1949-08-17', 'MANAGER', 16, 'M', '1925-09-15', 40175.00, 800, 3214), ('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', '1973-09-14', 'MANAGER', 16, 'M', '1945-07-07', 32250.00, 500, 2580), ('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831', '1980-09-30', 'MANAGER', 16, 'F', '1953-05-26', 36170.00, 700, 2893), ('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498', '1970-08-15', 'MANAGER', 16, 'F', '1941-05-15', 29750.00, 600, 2380), ('000100', 'THEODORE', 'Q', 'SPENSER', 'E21', '0972', '1980-06-19', 'MANAGER', 14, 'M', '1956-12-18', 26150.00, 500, 2092), ('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490', '1958-05-16', 'SALESREP', 19, 'M', '1929-11-05', 46500.00, 900, 3720), ('000120', 'SEAN', '', 'OCONNELL', 'A00', '2167', '1963-12-05', 'CLERK', 14, 'M', '1942-10-18', 29250.00, 600, 2340), ('000130', 'DOLORES', 'M', 'QUINTANA', 'C01', '4578', '1971-07-28', 'ANALYST', 16, 'F', '1925-09-15', 23800.00, 500, 1904), ('000140', 'HEATHER', 'A', 'NICHOLLS', 'C01', '1793', '1976-12-15', 'ANALYST', 18, 'F', '1946-01-19', 28420.00, 600, 2274), ('000150', 'BRUCE', '', 'ADAMSON', 'D11', '4510', '1972-02-12', 'DESIGNER', 16, 'M', '1947-05-17', 25280.00, 500, 2022), ('000160', 'ELIZABETH', 'R', 'PIANKA', 'D11', '3782', '1977-10-11', 'DESIGNER', 17, 'F', '1955-04-12', 22250.00, 400, 1780), ('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', '1978-09-15', 'DESIGNER', 16, 'M', '1951-01-05', 24680.00, 500, 1974), ('000180', 'MARILYN', 'S', 'SCOUTTEN', 'D11', '1682', '1973-07-07', 'DESIGNER', 17, 'F', '1949-02-21', 21340.00, 500, 1707), ('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986', '1974-07-26', 'DESIGNER', 16, 'M', '1952-06-25', 20450.00, 400, 1636), ('000200', 'DAVID', '', 'BROWN', 'D11', '4501', '1966-03-03', 'DESIGNER', 16, 'M', '1941-05-29', 27740.00, 600, 2217), ('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942', '1979-04-11', 'DESIGNER', 17, 'M', '1953-02-23', 18270.00, 400, 1462), ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672', '1968-08-29', 'DESIGNER', 18, 'F', '1948-03-19', 29840.00, 600, 2387), ('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094', '1966-11-21', 'CLERK', 14, 'M', '1935-05-30', 22180.00, 400, 1774), ('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780', '1979-12-05', 'CLERK', 17, 'M', '1954-03-31', 28760.00, 600, 2301), ('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', '1969-10-30', 'CLERK', 15, 'M', '1939-11-12', 19180.00, 400, 1534), ('000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953', '1975-09-11', 'CLERK', 16, 'F', '1936-10-05', 17250.00, 300, 1380), ('000270', 'MARIA', 'L', 'PEREZ', 'D21', '9001', '1980-09-30', 'CLERK', 15, 'F', '1953-05-26', 27380.00, 500, 2190), ('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997', '1967-03-24', 'OPERATOR', 17, 'F', '1936-03-28', 26250.00, 500, 2100), ('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502', '1980-05-30', 'OPERATOR', 12, 'M', '1946-07-09', 15340.00, 300, 1227), ('000300', 'PHILIP', 'X', 'SMITH', 'E11', '2095', '1972-06-19', 'OPERATOR', 14, 'M', '1936-10-27', 17750.00, 400, 1420), ('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332', '1964-09-12', 'OPERATOR', 12, 'F', '1931-04-21', 15900.00, 300, 1272), ('000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990', '1965-07-07', 'FIELDREP', 16, 'M', '1932-08-11', 19950.00, 400, 1596), ('000330', 'WING', '', 'LEE', 'E21', '2103', '1976-02-23', 'FIELDREP', 14, 'M', '1941-07-18', 25370.00, 500, 2030), ('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698', '1947-05-05', 'FIELDREP', 16, 'M', '1926-05-17', 23840.00, 500, 1907)) insert = 'INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for emp in employee: result = IfxPy.execute(stmt, emp) # Drop the emp_photo table, in case it exists drop = 'DROP TABLE emp_photo' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the emp_photo table create = 'CREATE TABLE emp_photo (empno CHAR(6) NOT NULL, photo_format VARCHAR(10) NOT NULL, picture BLOB, PRIMARY KEY(empno, photo_format))' try: result = IfxPy.exec_immediate(conn, create) except: pass # Populate the emp_photo table emp_photo = (\ ('000130', 'jpg', 'pic1.jpg'),\ ('000130', 'png', 'spook.png'),\ ('000140', 'jpg', 'pic1.jpg'),\ ('000140', 'png', 'spook.png'),\ ('000150', 'jpg', 'pic1.jpg'),\ ('000150', 'png', 'spook.png'),\ ('000190', 'jpg', 'pic1.jpg'),\ ('000190', 'png', 'spook.png')\ ) insert = 'INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for photo in emp_photo: empno = photo[0] photo_format = photo[1] fileHandler = open( os.path.dirname(os.path.abspath(__file__)) + '/' + photo[2], 'rb') picture = fileHandler.read() IfxPy.bind_param(stmt, 1, empno, IfxPy.SQL_PARAM_INPUT) IfxPy.bind_param(stmt, 2, photo_format, IfxPy.SQL_PARAM_INPUT) IfxPy.bind_param(stmt, 3, picture, IfxPy.SQL_PARAM_INPUT) # result = IfxPy.execute(stmt) # Drop the org table, in case it exists drop = 'DROP TABLE org' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the org table create = 'CREATE TABLE org (deptnumb SMALLINT NOT NULL, deptname VARCHAR(14), manager SMALLINT, division VARCHAR(10), location VARCHAR(13))' result = IfxPy.exec_immediate(conn, create) # Populate the org table org = (\ (10, 'Head Office', 160, 'Corporate', 'New York'),\ (15, 'New England', 50, 'Eastern', 'Boston'),\ (20, 'Mid Atlantic', 10, 'Eastern', 'Washington'),\ (38, 'South Atlantic', 30, 'Eastern', 'Atlanta'),\ (42, 'Great Lakes', 100, 'Midwest', 'Chicago'),\ (51, 'Plains', 140, 'Midwest', 'Dallas'),\ (66, 'Pacific', 270, 'Western', 'San Francisco'),\ (84, 'Mountain', 290, 'Western', 'Denver')\ ) insert = 'INSERT INTO org (deptnumb, deptname, manager, division, location) VALUES (?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for orgpart in org: result = IfxPy.execute(stmt, orgpart) # Drop the project table, in case it exists drop = 'DROP TABLE project' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the project table create = 'CREATE TABLE project (projno CHAR(6) NOT NULL, projname VARCHAR(24) NOT NULL, deptno CHAR(3) NOT NULL, respemp CHAR(6) NOT NULL, prstaff DECIMAL(5,2), prstdate DATE, prendate DATE, majproj CHAR(6))' result = IfxPy.exec_immediate(conn, create) # Populate the project table project = (\ ('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5, '1982-01-01', '1983-02-01', ''),\ ('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', 6, '1982-01-01', '1983-02-01', 'AD3100'),\ ('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2, '1982-01-01', '1983-02-01', 'AD3110'),\ ('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1, '1982-01-01', '1983-02-01', 'AD3110'),\ ('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2, '1982-01-01', '1983-02-01', 'AD3110'),\ ('IF1000', 'QUERY SERVICES', 'C01', '000030', 2, '1982-01-01', '1983-02-01', None),\ ('IF2000', 'USER EDUCATION', 'C01', '000030', 1, '1982-01-01', '1983-02-01', None),\ ('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010', 12, '1982-01-01', '1983-02-01', None),\ ('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9, '1982-01-01', '1983-02-01', 'MA2100'),\ ('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2, '1982-01-01', '1982-12-01', 'MA2110'),\ ('MA2112', 'W L ROBOT DESIGN', 'D11', '000150', 3, '1982-01-01', '1982-12-01', 'MA2110'),\ ('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3, '1982-02-15', '1982-12-01', 'MA2110'),\ ('OP1000', 'OPERATION SUPPORT', 'E01', '000050', 6, '1982-01-01', '1983-02-01', None),\ ('OP1010', 'OPERATION', 'E11', '000090', 5, '1982-01-01', '1983-02-01', 'OP1000'),\ ('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5, '1982-01-01', '1983-02-01', None),\ ('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4, '1982-01-01', '1983-02-01', 'OP2000'),\ ('OP2011', 'SCP SYSTEMS SUPPORT', 'E21', '000320', 1, '1982-01-01', '1983-02-01', 'OP2010'),\ ('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1, '1982-01-01', '1983-02-01', 'OP2010'),\ ('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1, '1982-01-01', '1983-02-01', 'OP2010'),\ ('PL2100', 'WELD LINE PLANNING', 'B01', '000020', 1, '1982-01-01', '1982-09-15', 'MA2100')\ ) insert = 'INSERT INTO project (projno, projname, deptno, respemp, prstaff, prstdate, prendate, majproj) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for proj in project: result = IfxPy.execute(stmt, proj) # Drop the sales table, in case it exists drop = 'DROP TABLE sales' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the sales table create = 'CREATE TABLE sales (sales_date DATE, sales_person VARCHAR(15), region VARCHAR(15), sales INT)' result = IfxPy.exec_immediate(conn, create) # Populate the sales table sales = (\ ('1995-12-31', 'LUCCHESSI', 'Ontario-South', 1),\ ('1995-12-31', 'LEE', 'Ontario-South', 3),\ ('1995-12-31', 'LEE', 'Quebec', 1),\ ('1995-12-31', 'LEE', 'Manitoba', 2),\ ('1995-12-31', 'GOUNOT', 'Quebec', 1),\ ('1996-03-29', 'LUCCHESSI', 'Ontario-South', 3),\ ('1996-03-29', 'LUCCHESSI', 'Quebec', 1),\ ('1996-03-29', 'LEE', 'Ontario-South', 2),\ ('1996-03-29', 'LEE', 'Ontario-North', 2),\ ('1996-03-29', 'LEE', 'Quebec', 3),\ ('1996-03-29', 'LEE', 'Manitoba', 5),\ ('1996-03-29', 'GOUNOT', 'Ontario-South', 3),\ ('1996-03-29', 'GOUNOT', 'Quebec', 1),\ ('1996-03-29', 'GOUNOT', 'Manitoba', 7),\ ('1996-03-30', 'LUCCHESSI', 'Ontario-South', 1),\ ('1996-03-30', 'LUCCHESSI', 'Quebec', 2),\ ('1996-03-30', 'LUCCHESSI', 'Manitoba', 1),\ ('1996-03-30', 'LEE', 'Ontario-South', 7),\ ('1996-03-30', 'LEE', 'Ontario-North', 3),\ ('1996-03-30', 'LEE', 'Quebec', 7),\ ('1996-03-30', 'LEE', 'Manitoba', 4),\ ('1996-03-30', 'GOUNOT', 'Ontario-South', 2),\ ('1996-03-30', 'GOUNOT', 'Quebec', 18),\ ('1996-03-30', 'GOUNOT', 'Manitoba', 1),\ ('1996-03-31', 'LUCCHESSI', 'Manitoba', 1),\ ('1996-03-31', 'LEE', 'Ontario-South', 14),\ ('1996-03-31', 'LEE', 'Ontario-North', 3),\ ('1996-03-31', 'LEE', 'Quebec', 7),\ ('1996-03-31', 'LEE', 'Manitoba', 3),\ ('1996-03-31', 'GOUNOT', 'Ontario-South', 2),\ ('1996-03-31', 'GOUNOT', 'Quebec', 1),\ ('1996-04-01', 'LUCCHESSI', 'Ontario-South', 3),\ ('1996-04-01', 'LUCCHESSI', 'Manitoba', 1),\ ('1996-04-01', 'LEE', 'Ontario-South', 8),\ ('1996-04-01', 'LEE', 'Ontario-North', None),\ ('1996-04-01', 'LEE', 'Quebec', 8),\ ('1996-04-01', 'LEE', 'Manitoba', 9),\ ('1996-04-01', 'GOUNOT', 'Ontario-South', 3),\ ('1996-04-01', 'GOUNOT', 'Ontario-North', 1),\ ('1996-04-01', 'GOUNOT', 'Quebec', 3),\ ('1996-04-01', 'GOUNOT', 'Manitoba', 7)\ ) insert = 'INSERT INTO sales (sales_date, sales_person, region, sales) VALUES (?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for sale in sales: result = IfxPy.execute(stmt, sale) # Drop the stored procedure, in case it exists drop = 'DROP PROCEDURE match_animal' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the stored procedure if (server.DBMS_NAME[0:3] == 'Inf'): result = IfxPy.exec_immediate( conn, """ CREATE PROCEDURE match_animal(first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE PRECISION ) DEFINE match_name INT; LET match_name = 0; FOREACH c1 FOR SELECT COUNT(*) INTO match_name FROM animals WHERE name IN (second_name) IF (match_name > 0) THEN LET second_name = 'TRUE'; END IF; END FOREACH; FOREACH c2 FOR SELECT SUM(weight) INTO animal_weight FROM animals WHERE name in (first_name, second_name) END FOREACH; END PROCEDURE;""") else: result = IfxPy.exec_immediate( conn, """ CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE match_name INT DEFAULT 0; DECLARE c1 CURSOR FOR SELECT COUNT(*) FROM animals WHERE name IN (second_name); DECLARE c2 CURSOR FOR SELECT SUM(weight) FROM animals WHERE name in (first_name, second_name); DECLARE c3 CURSOR WITH RETURN FOR SELECT name, breed, weight FROM animals WHERE name BETWEEN first_name AND second_name ORDER BY name; OPEN c1; FETCH c1 INTO match_name; IF (match_name > 0) THEN SET second_name = 'TRUE'; END IF; CLOSE c1; OPEN c2; FETCH c2 INTO animal_weight; CLOSE c2; OPEN c3; END""") result = None # Drop the staff table, in case it exists drop = 'DROP TABLE staff' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the staff table create = 'CREATE TABLE staff (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT, job CHAR(5), years SMALLINT, salary DECIMAL(7,2), comm DECIMAL(7,2))' result = IfxPy.exec_immediate(conn, create) # Populate the staff table staff = (\ (10, 'Sanders', 20, 'Mgr', 7, 18357.50, None),\ (20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45),\ (30, 'Marenghi', 38, 'Mgr', 5, 17506.75, None),\ (40, 'OBrien', 38, 'Sales', 6, 18006.00, 846.55),\ (50, 'Hanes', 15, 'Mgr', 10, 20659.80, None),\ (60, 'Quigley', 38, 'Sales', None, 16808.30, 650.25),\ (70, 'Rothman', 15, 'Sales', 7, 16502.83, 1152.00),\ (80, 'James', 20, 'Clerk', None, 13504.60, 128.20),\ (90, 'Koonitz', 42, 'Sales', 6, 18001.75, 1386.70),\ (100, 'Plotz', 42, 'Mgr' , 7, 18352.80, None),\ (110, 'Ngan', 15, 'Clerk', 5, 12508.20, 206.60),\ (120, 'Naughton', 38, 'Clerk', None, 12954.75, 180.00),\ (130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, 75.60),\ (140, 'Fraye', 51, 'Mgr' , 6, 21150.00, None),\ (150, 'Williams', 51, 'Sales', 6, 19456.50, 637.65),\ (160, 'Molinare', 10, 'Mgr' , 7, 22959.20, None),\ (170, 'Kermisch', 15, 'Clerk', 4, 12258.50, 110.10),\ (180, 'Abrahams', 38, 'Clerk', 3, 12009.75, 236.50),\ (190, 'Sneider', 20, 'Clerk', 8, 14252.75, 126.50),\ (200, 'Scoutten', 42, 'Clerk', None, 11508.60, 84.20),\ (210, 'Lu', 10, 'Mgr' , 10, 20010.00, None),\ (220, 'Smith', 51, 'Sales', 7, 17654.50, 992.80),\ (230, 'Lundquist', 51, 'Clerk', 3, 13369.80, 189.65),\ (240, 'Daniels', 10, 'Mgr' , 5, 19260.25, None),\ (250, 'Wheeler', 51, 'Clerk', 6, 14460.00, 513.30),\ (260, 'Jones', 10, 'Mgr' , 12, 21234.00, None),\ (270, 'Lea', 66, 'Mgr' , 9, 18555.50, None),\ (280, 'Wilson', 66, 'Sales', 9, 18674.50, 811.50),\ (290, 'Quill', 84, 'Mgr' , 10, 19818.00, None),\ (300, 'Davis', 84, 'Sales', 5, 15454.50, 806.10),\ (310, 'Graham', 66, 'Sales', 13, 21000.00, 200.30),\ (320, 'Gonzales', 66, 'Sales', 4, 16858.20, 844.00),\ (330, 'Burke', 66, 'Clerk', 1, 10988.00, 55.50),\ (340, 'Edwards', 84, 'Sales', 7, 17844.00, 1285.00),\ (350, 'Gafney', 84, 'Clerk', 5, 13030.50, 188.00)\ ) insert = 'INSERT INTO staff (id, name, dept, job, years, salary, comm) VALUES (?, ?, ?, ?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for emp in staff: result = IfxPy.execute(stmt, emp) try: result = IfxPy.exec_immediate(conn, 'DROP TABLE t_string') except: pass result = IfxPy.exec_immediate( conn, 'CREATE TABLE t_string(a INTEGER, b DOUBLE PRECISION, c VARCHAR(100))' ) print("Preperation complete")
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 LoadCsvSample(csv_file_name, table_name, ConStr): try: # netstat -a | findstr 9088 conn = IfxPy.connect(ConStr, "", "") except Exception as e: print('ERROR: Connect failed') print(e) quit() try: sql = "drop table {};".format(table_name) print(sql) stmt = IfxPy.exec_immediate(conn, sql) except: print('FYI: drop table failed') # head -n 5 sample.csv # "Store","DayOfWeek","Date","Sales","Customers","Open","Promo","StateHoliday","SchoolHoliday" # 1,5,2015-07-31,5263,555,1,1,"0","1" # 2,5,2015-07-31,6064,625,1,1,"0","1" # 3,5,2015-07-31,8314,821,1,1,"0","1" # 4,5,2015-05-31,13995,1498,1,1,"0","1" # The colum and its type for full list is. # Store int64 # DayOfWeek int64 # Date object # Sales int64 # Customers int64 # Open int64 # Promo int64 # StateHoliday object # SchoolHoliday int64 sql = ''' create table {} ( Store int, DayOfWeek int, Date LVARCHAR, Sales int, Customers int, Open int, Promo int, StateHoliday char(6), SchoolHoliday int ); '''.format(table_name) stmt = IfxPy.exec_immediate(conn, sql) sql = ''' INSERT INTO {} ( Store, DayOfWeek, Date, Sales, Customers, Open, Promo, StateHoliday, SchoolHoliday ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ); '''.format(table_name) stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None c5 = None c6 = None c7 = None c8 = None c9 = None IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR) IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 6, c6, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 7, c7, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 8, c8, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR) IfxPy.bind_param(stmt, 9, c9, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) # Read the CSV file and insert into the table with open(csv_file_name, 'r') as csv_file: csv_reader = csv.reader(csv_file, delimiter=',') row_count = 0 for row in csv_reader: row_count += 1 # Convert the List to tuple tup = tuple(row) if row_count == 1: print("Header :", tup) else: print(tup) IfxPy.execute(stmt, tup) print(f'Rows Inserted is {row_count-1} .') # SELECT the inserted rows sql = "SELECT * FROM t1" stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) rc = 0 while dictionary != False: rc = rc + 1 # print ("-- Record {0} --".format(rc)) # print ("c1 is : ", dictionary[0]) # print ("c2 is : ", dictionary[1]) # print ("c3 is : ", dictionary[2]) # print ("c4 is : ", dictionary[3]) # print ("c5 is : ", dictionary[4]) # print ("c6 is : ", dictionary[5]) # print ("c7 is : ", dictionary[6]) # print ("c8 is : ", dictionary[7]) # print ("c9 is : ", dictionary[8]) # print (" ") dictionary = IfxPy.fetch_both(stmt) print(f'Rows Selected is {rc} .') IfxPy.close(conn) print("Done")
def run_test_316(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) # Get the server type server = IfxPy.server_info(conn) try: sql = "drop table rc_create;" stmt = IfxPy.exec_immediate(conn, sql) except: pass sql = "DROP ROW TYPE if exists details RESTRICT;" IfxPy.exec_immediate(conn, sql) sql = "DROP ROW TYPE if exists udt_t1 RESTRICT;" IfxPy.exec_immediate(conn, sql) sql = " create ROW type details(name varchar(15), addr varchar(15), zip varchar(15) );" stmt = IfxPy.exec_immediate(conn, sql) sql = " create ROW type udt_t1(name varchar(20), zip int);" stmt = IfxPy.exec_immediate(conn, sql) sql = "create table rc_create (c1 int, c2 SET(CHAR(10)NOT NULL), c3 MULTISET(int not null), c4 LIST(int not null), c5 details, c6 udt_t1 );" stmt = IfxPy.exec_immediate(conn, sql) #sql = "INSERT INTO rc_create(c1, c2) values(?, ?);" sql = "INSERT INTO rc_create VALUES (?, ?, ?, ?, ?, ?);" stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None c5 = None c6 = None IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_RC_COLLECTION) IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_UDT_FIXED) IfxPy.bind_param(stmt, 6, c6, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR, IfxPy.SQL_INFX_UDT_VARYING) i = 0 while i < 3: i += 1 c1 = 100 + i c2 = "SET{'test', 'test1'}" c3 = "MULTISET{1,2,3}" c4 = "LIST{10, 20}" c5 = "ROW('Pune', 'City', '411061')" c6 = "ROW('Mumbai', 11111)" IfxPy.execute(stmt, (c1, c2, c3, c4, c5, c6)) sql = "SELECT * FROM rc_create" stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) print("UDT Param complete")
def my_Sample(): ConStr = "SERVER=ids0;DATABASE=db1;HOST=127.0.0.1;SERVICE=9088;UID=informix;PWD=xxxxx;" try: # netstat -a | findstr 9088 conn = IfxPy.connect(ConStr, "", "") except Exception as e: print('ERROR: Connect failed') print(e) quit() try: sql = "drop table t1;" print(sql) stmt = IfxPy.exec_immediate(conn, sql) except: print('FYI: drop table failed') sql = "create table t1 ( c1 int, c2 char(20), c3 int, c4 int ) ;" stmt = IfxPy.exec_immediate(conn, sql) sql = "INSERT INTO t1 (c1, c2, c3, c4) VALUES ( ?, ?, ?, ? )" stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None # Create bindings for the parameter IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR) IfxPy.bind_param(stmt, 3, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 4, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) print("Inserting Recors ......") i = 0 while i < 10: i += 1 c1 = 100 + i c2 = "Testing {0}".format(i) c3 = 20000 + i c4 = 50000 + i # supply new values as a tuple IfxPy.execute(stmt, (c1, c2, c3, c4)) # Try select those rows we have just inserted print("Selecting Recors ......") sql = "SELECT * FROM t1" stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) rc = 0 while tu != False: rc += 1 print(tu) tu = IfxPy.fetch_tuple(stmt) print() print("Total Record Inserted {}".format(i)) print("Total Record Selected {}".format(rc)) # Free up memory used by result and then stmt too IfxPy.free_result(stmt) IfxPy.free_stmt(stmt) # close the connection IfxPy.close(conn) print("Done")