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_018(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.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2)
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.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
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.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = IfxPy.execute(stmt)
if result:
rows = IfxPy.num_rows(stmt)
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_006(self):
options1 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_FORWARD_ONLY}
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = IfxPy.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] == 'Inf'):
options1 = options2
stmt = IfxPy.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
IfxPy.execute(stmt)
data = IfxPy.fetch_both(stmt)
while (data):
print(data[0])
data = IfxPy.fetch_both(stmt)
print("")
stmt = IfxPy.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
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_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_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_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_039(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result = IfxPy.prepare(
conn, "SELECT * FROM animals",
{IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = IfxPy.prepare(conn, "SELECT * FROM animals")
IfxPy.execute(result)
row = IfxPy.fetch_row(result)
while (row):
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result2 = IfxPy.prepare(conn, "SELECT * FROM animals", {
IfxPy.SQL_ATTR_CURSOR_TYPE:
IfxPy.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result2 = IfxPy.prepare(conn, "SELECT * FROM animals")
IfxPy.execute(result2)
while (IfxPy.fetch_row(result2)):
print("%s : %s : %s : %s" % (IfxPy.result(result2, 0), \
IfxPy.result(result2, 1), \
IfxPy.result(result2, 2), \
IfxPy.result(result2, 3)))
row = IfxPy.fetch_row(result)
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_115(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 conn:
drop = "drop table numericliteral"
try:
IfxPy.exec_immediate(conn, drop)
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
IfxPy.exec_immediate(conn, insert)
stmt = IfxPy.prepare(conn, "SELECT data FROM numericliteral")
IfxPy.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = IfxPy.fetch_object(stmt, 0)
class Row:
pass
row = Row()
IfxPy.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'Inf'):
row.DATA = IfxPy.result(stmt, 'DATA')
else:
row.DATA = IfxPy.result(stmt, 'data')
print(row.DATA)
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
IfxPy.exec_immediate(conn, insert)
stmt = IfxPy.prepare(conn, "SELECT data FROM numericliteral")
IfxPy.execute(stmt)
# row = IfxPy.fetch_object(stmt, 0)
IfxPy.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'Inf'):
row.DATA = IfxPy.result(stmt, 'DATA')
else:
row.DATA = IfxPy.result(stmt, 'data')
print(row.DATA)
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_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_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_133(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = IfxPy.prepare(conn, sql)
res = IfxPy.execute(
stmt, (128, 'hacker of human and technological nature',
'Wez the ruler of all things PECL', 88.3))
stmt = IfxPy.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = IfxPy.execute(stmt, (128, ))
row = IfxPy.fetch_assoc(stmt)
for i in row:
print i
IfxPy.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
try:
stmt = IfxPy.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = IfxPy.execute(stmt, (128, ))
row = IfxPy.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
IfxPy.rollback(conn)
print "Done"
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_157b(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'):
stmt = IfxPy.prepare(conn, sql, {
IfxPy.SQL_ATTR_CURSOR_TYPE:
IfxPy.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = IfxPy.prepare(
conn, sql,
{IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_STATIC})
result = IfxPy.execute(stmt)
i = 2
row = IfxPy.fetch_assoc(stmt, i)
while (row):
if (server.DBMS_NAME[0:3] == 'Inf'):
#printf("%-5d %-16s %-32s %10s\n", row['id'], row['name'], row['breed'], row['weight'])
print(
"%-5d %-16s %-32s %10s" %
(row['id'], row['name'], row['breed'], row['weight']))
else:
#printf("%-5d %-16s %-32s %10s\n", row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
print(
"%-5d %-16s %-32s %10s" %
(row['ID'], row['NAME'], row['BREED'], row['WEIGHT']))
i = i + 2
row = IfxPy.fetch_assoc(stmt, i)
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_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_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_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_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_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_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_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_130(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 = 0")
if IfxPy.execute(stmt):
row = IfxPy.fetch_tuple(stmt)
while ( row ):
for i in row:
print i
row = IfxPy.fetch_tuple(stmt)
else:
print "Connection failed."
def run_test_132(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE id = ? AND name = ?"
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = IfxPy.prepare(conn, sql)
if (IfxPy.execute(stmt, (0, 'Pook'))):
row = IfxPy.fetch_tuple(stmt)
while (row):
#row.each { |child| print child }
for i in row:
print i
row = IfxPy.fetch_tuple(stmt)
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_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_049(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = IfxPy.prepare(conn, insert)
if IfxPy.execute(stmt, (None, 'ghost', None, None)):
stmt = IfxPy.exec_immediate(conn, select)
row = IfxPy.fetch_tuple(stmt)
while (row):
#row.each { |child| puts child }
for child in row:
print(child)
row = IfxPy.fetch_tuple(stmt)
IfxPy.rollback(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."
