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_040(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF)
# Drop the test table, in case it exists
drop = 'DROP TABLE animals'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
IfxPy.exec_immediate(conn, insert)
stmt = IfxPy.exec_immediate(conn, "select * from animals")
onerow = IfxPy.fetch_tuple(stmt)
for element in onerow:
print(element)
IfxPy.rollback(conn)
def run_test_020(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, IfxPy.SQL_AUTOCOMMIT_OFF)
ac = IfxPy.autocommit(conn)
if ac != 0:
print("Cannot set IfxPy.SQL_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.rollback(conn)
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print(rows)
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_warn(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "DROP TABLE TEST1"
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
# Create the table test1
create = "CREATE TABLE TEST1 (COL1 CHAR(5))"
result = IfxPy.exec_immediate(conn, create)
# Insert a string longer than 5 characters to force an error
# IfxPy.stmt_warn() API
query = 'INSERT INTO TEST1 VALUES (?)'
stmt = IfxPy.prepare(conn, query)
try:
IfxPy.execute(stmt, ('ABCDEF', ))
except:
pass
print((IfxPy.stmt_warn(stmt)))
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_161(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)
result = IfxPy.exec_immediate(
conn, "select * from emp_act order by projno desc")
row = IfxPy.fetch_both(result)
count = 1
while (row):
print(
"Record", count, ": %6s %-6s %3d %9s %10s %10s %6s " %
(row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'],
row['EMENDATE'], row[0]))
result2 = IfxPy.exec_immediate(
conn, "select * from employee where employee.empno='" +
row['EMPNO'] + "'")
row2 = IfxPy.fetch_both(result2)
if row2:
print(">>%s,%s,%s,%s,%s,%s,%s" %
(row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'],
row2[3], row2[3], row2[5], row2[6]))
count = count + 1
if (count > 10):
break
row = IfxPy.fetch_both(result)
def run_test_180(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = IfxPy.exec_immediate(
conn,
"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
if result:
cols = IfxPy.num_fields(result)
print("col:", cols, ", ")
rows = IfxPy.num_rows(result)
print("affected row:", rows)
else:
print(IfxPy.stmt_errormsg())
try:
result = IfxPy.exec_immediate(
conn, "delete from t_string where a=123")
except:
pass
if result:
cols = IfxPy.num_fields(result)
print("col:", cols, ", ")
rows = IfxPy.num_rows(result)
print("affected row:", rows)
else:
print(IfxPy.stmt_errormsg())
else:
print("no connection")
def run_test_157(self):
conn = IfxPy.connect(config.ConnStr + 'ENABLESCROLLABLECURSORS=1',
config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
if (server.DBMS_NAME[0:3] != 'Inf'):
result = IfxPy.exec_immediate(conn, sql, {
IfxPy.SQL_ATTR_CURSOR_TYPE:
IfxPy.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result = IfxPy.exec_immediate(
conn, sql,
{IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_STATIC})
i = 2
row = IfxPy.fetch_assoc(result, i)
while (row):
if (server.DBMS_NAME[0:3] == 'Inf'):
print "%-5d %-16s %-32s %10s\n" % (
row['id'], row['name'], row['breed'], row['weight'])
else:
print "%-5d %-16s %-32s %10s\n" % (
row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
i = i + 2
row = IfxPy.fetch_assoc(result, i)
def run_test_313(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 collection_tab;"
stmt = IfxPy.exec_immediate(conn, sql)
except:
pass
SetupSqlSet = [
"create table collection_tab ( col1 int, s1 SET(float not null), m1 MULTISET(char(20) not null), l1 LIST(bigint not null)) ;",
"insert into collection_tab values( 1, SET{11.10, 12.11}, MULTISET{'Hey', 'Hell'}, LIST{120, -120});",
"insert into collection_tab values( 2, SET{13.11, 14.20}, MULTISET{'Jhon', 'Nick'}, LIST{130, -130});",
"insert into collection_tab values( 3, SET{14.21, 14.00}, MULTISET{'Dave', 'Mill'}, LIST{140, -141});",
"insert into collection_tab values( 4, SET{15.0, 16}, MULTISET{'Mon', 'Phebee'}, LIST{150, -150});"
]
i = 0
for sql in SetupSqlSet:
i += 1
stmt = IfxPy.exec_immediate(conn, sql)
sql = "select * from collection_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 Data Access complete")
def run_test_231(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
result = IfxPy.exec_immediate(conn, "select * from sales")
result2 = IfxPy.exec_immediate(conn, "select * from staff")
result3 = IfxPy.exec_immediate(conn, "select * from emp_photo")
for i in range(0, IfxPy.num_fields(result) + 1):
print str(i) + ":" + str(
IfxPy.field_type(result, IfxPy.field_name(result, i)))
print "\n-----"
for i in range(0, IfxPy.num_fields(result2)):
print str(i) + ":" + IfxPy.field_type(result2,
IfxPy.field_name(result2, i))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + IfxPy.field_type(result3,
IfxPy.field_name(result3, i))
print "\n-----"
print "region:%s" % IfxPy.field_type(result, 'region')
print "5:%s" % IfxPy.field_type(result2, 5)
def run_test_022(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,
"INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print rows
IfxPy.rollback(conn)
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print rows
IfxPy.close(conn)
else:
print "Connection failed."
def run_test_158(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)
result = IfxPy.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = IfxPy.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = IfxPy.fetch_assoc(result)
result2 = IfxPy.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = IfxPy.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = IfxPy.fetch_assoc(result2)
result3 = IfxPy.exec_immediate(conn,"SELECT * FROM employee WHERE 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')")
row3 = IfxPy.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=IfxPy.fetch_tuple(result3)
print output
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_038(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result = IfxPy.exec_immediate(
conn, "SELECT * FROM staff WHERE id < 101",
{IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = IfxPy.exec_immediate(
conn, "SELECT * FROM staff WHERE id < 101")
row = IfxPy.fetch_row(result)
while (row):
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result2 = IfxPy.prepare(conn,
"SELECT * FROM staff WHERE id < 101", {
IfxPy.SQL_ATTR_CURSOR_TYPE:
IfxPy.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result2 = IfxPy.prepare(conn,
"SELECT * FROM staff WHERE id < 101")
IfxPy.execute(result2)
row2 = IfxPy.fetch_row(result2)
while (row2):
print "%s : %s : %s : %s : %s\n" % (IfxPy.result(result2, 0), \
IfxPy.result(result2, 1), \
IfxPy.result(result2, 2), \
IfxPy.result(result2, 3), \
IfxPy.result(result2, 5))
row2 = IfxPy.fetch_row(result2)
row = IfxPy.fetch_row(result)
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_spinout_timestamp(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
# Get the server type
serverinfo = IfxPy.server_info(conn)
if conn:
drop = "DROP PROCEDURE PROC_TIMESTAMP"
try:
result = IfxPy.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 = IfxPy.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 = IfxPy.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)
IfxPy.close(conn)
else:
print("Connection failed.")
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)
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_148(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
##### Set up #####
serverinfo = IfxPy.server_info( conn )
server = serverinfo.DBMS_NAME[0:3]
try:
sql = "DROP TABLE sptb"
IfxPy.exec_immediate(conn, sql)
except:
pass
try:
sql = "DROP PROCEDURE sp"
IfxPy.exec_immediate(conn, sql)
except:
pass
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 VARCHAR(20))"
IfxPy.exec_immediate(conn, sql)
sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'varchar data')"
IfxPy.exec_immediate(conn, sql)
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 VARCHAR(20));
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
IfxPy.exec_immediate(conn, sql)
#############################
##### Run the test #####
out1 = 0
out2 = 0.00
out3 = ""
out4 = 0
out5 = ""
stmt, out1, out2, out3, out4, out5 = IfxPy.callproc(conn, 'sp', (out1, out2, out3, out4, out5))
print("out 1:")
print(out1)
print("out 2:")
print(out2)
print("out 3:")
print(out3)
print("out 4:")
print(out4)
print("out 5:")
print(out5)
#############################
else:
print("Connection failed.")
def run_test_264(self):
# Make a connection
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
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)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if (server.DBMS_NAME[0:3] == 'Inf'):
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 = IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = IfxPy.exec_immediate(conn, insert)
print "Number of inserted rows:", IfxPy.num_rows(res)
stmt = IfxPy.prepare(conn, "SELECT * FROM tab_bigint")
IfxPy.execute(stmt)
data = IfxPy.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 = IfxPy.fetch_both(stmt)
# test IfxPy.result for fetch of bigint
stmt1 = IfxPy.prepare(conn, "SELECT col2 FROM tab_bigint")
IfxPy.execute(stmt1)
IfxPy.fetch_row(stmt1, 0)
if (server.DBMS_NAME[0:3] != 'Inf'):
row1 = IfxPy.result(stmt1, 'COL2')
else:
row1 = IfxPy.result(stmt1, 'col2')
print row1
IfxPy.close(conn)
def run_test_113(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "DROP TABLE datetest"
try:
IfxPy.exec_immediate(conn, drop)
except:
pass
create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )"
IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'03-27-1982')"
IfxPy.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'07-08-1981')"
IfxPy.exec_immediate(conn, insert)
stmt = IfxPy.prepare(conn, "SELECT * FROM datetest")
IfxPy.execute(stmt)
result = IfxPy.fetch_row(stmt)
while (result):
row0 = IfxPy.result(stmt, 0)
row1 = IfxPy.result(stmt, 1)
print row0
print row1
result = IfxPy.fetch_row(stmt)
else:
print "Connection failed."
def run_test_241(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
result = IfxPy.exec_immediate(conn, "select * from sales")
result2 = IfxPy.exec_immediate(conn, "select * from staff")
result3 = IfxPy.exec_immediate(conn, "select * from emp_photo")
for i in range(0, IfxPy.num_fields(result)):
print str(IfxPy.field_width(result,i))
print "\n-----"
for i in range(0, IfxPy.num_fields(result2)):
print str(IfxPy.field_width(result2,IfxPy.field_name(result2,i)))
def run_test_250(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
result = IfxPy.exec_immediate(conn, "select * from sales")
result2 = IfxPy.exec_immediate(conn, "select * from staff")
result3 = IfxPy.exec_immediate(conn, "select * from emp_photo")
r1 = IfxPy.free_result(result)
r2 = IfxPy.free_result(result2)
r3 = IfxPy.free_result(result3)
print r1
print r2
print r3
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_114(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
IfxPy.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
IfxPy.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
IfxPy.exec_immediate(conn, insert)
stmt = IfxPy.prepare(conn, "SELECT * FROM numericliteral")
IfxPy.execute(stmt)
result = IfxPy.fetch_row( stmt )
while ( result ):
row0 = IfxPy.result(stmt, 0)
row1 = IfxPy.result(stmt, 1)
print row0
print row1
result = IfxPy.fetch_row( stmt )
else:
print "Connection failed."
def run_test_152(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)
result = IfxPy.exec_immediate(conn, "select * from project")
row = IfxPy.fetch_assoc(result)
while (row):
#printf("%6s ",row['PROJNO'])
#printf("%-24s ",row['PROJNAME'])
#printf("%3s ",row['DEPTNO'])
#printf("%6s",row['RESPEMP'])
#printf("%7s ",row['PRSTAFF'])
#printf("%10s ",row['PRSTDATE'])
#printf("%10s ",row['PRENDATE'])
#printf("%6s",row['MAJPROJ'])
#puts ""
if (row['MAJPROJ'] == None):
row['MAJPROJ'] = ''
print("%6s %-24s %3s %6s%7s %10s %10s %6s" %
(row['PROJNO'], row['PROJNAME'], row['DEPTNO'],
row['RESPEMP'], row['PRSTAFF'], row['PRSTDATE'],
row['PRENDATE'], row['MAJPROJ']))
row = IfxPy.fetch_assoc(result)
def execute_sql(_self, sql_name):
if sql_name not in _self.sql_matrix[_self.version].keys():
raise Exception(
'{0} not in SQL Matrix for version {1} - bailing out.\n Please comment out the call using the sql statement in the collect() function.'
.format(sql_name, _self.version))
sql = _self.sql_matrix[_self.version][sql_name]
try:
stat = IfxPy.exec_immediate(_self.connection, sql)
except Exception, e:
_self.print_error(
"Could not execute SQL statement - are we connected? {0}".
format(e))
_self.disconnect()
_self.connect()
if not _self.connection is None:
stat = IfxPy.exec_immediate(_self.connection, sql)
def run_test_150(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)
result = IfxPy.exec_immediate(conn, "select * from staff")
row = IfxPy.fetch_assoc(result)
while ( row ):
#print "%5d " % row['ID']
#print "%-10s " % row['NAME']
#print "%5d " % row['DEPT']
#print "%-7s " % row['JOB']
#print "%5d " % row['YEARS']
#print "%15s " % row['SALARY']
#print "%10s " % row['COMM']
if (row['YEARS'] == None):
row['YEARS'] = 0
if (row['COMM'] == None):
row['COMM'] = ''
print("%5d %-10s %5d %-7s %5s %15s %10s " % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM']))
row = IfxPy.fetch_assoc(result)
def run_test_110(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
stmt = IfxPy.exec_immediate(
conn, "SELECT * FROM animals ORDER BY breed")
num1 = IfxPy.field_num(stmt, "id")
num2 = IfxPy.field_num(stmt, "breed")
num3 = IfxPy.field_num(stmt, "name")
num4 = IfxPy.field_num(stmt, "weight")
num5 = IfxPy.field_num(stmt, "test")
num6 = IfxPy.field_num(stmt, 8)
num7 = IfxPy.field_num(stmt, 1)
num8 = IfxPy.field_num(stmt, "WEIGHT")
print("int(%d)" % num1)
print("int(%d)" % num2)
print("int(%d)" % num3)
print("int(%d)" % num4)
print("%s" % num5)
print("%s" % num6)
print("int(%d)" % num7)
print("%s" % num8)
else:
print("Connection failed.")
def run_test_124(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = IfxPy.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 = IfxPy.num_fields(result)
j = 0
row = IfxPy.fetch_both(result)
while (row):
for i in range(0, cols):
field = IfxPy.field_name(result, i)
value = row[IfxPy.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = IfxPy.fetch_both(result)
IfxPy.close(conn)
print("done")
else:
print(IfxPy.conn_errormsg())
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.")
