def run_test_022(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.autocommit(conn, 0)
ac = ifx_db.autocommit(conn)
if ac != 0:
print "Cannot set ifx_db.AUTOCOMMIT_OFF\nCannot run test"
#continue
ifx_db.exec_immediate(
conn,
"INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.rollback(conn)
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_020(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
ac = ifx_db.autocommit(conn)
if ac != 0:
print "Cannot set ifx_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
#continue
ifx_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.rollback(conn)
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.fetch_tuple(stmt)
rows = res[0]
print rows
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_6561(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
stmt = ifx_db.exec_immediate(
conn,
"INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
)
statement = "SELECT count(id) FROM animals"
result = ifx_db.exec_immediate(conn, statement)
if ((not result) and ifx_db.stmt_error()):
print "ERROR: %s" % (ifx_db.stmt_errormsg(), )
row = ifx_db.fetch_tuple(result)
while (row):
for i in row:
print i
row = ifx_db.fetch_tuple(result)
ifx_db.rollback(conn)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_143(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
ifx_db.autocommit(conn, ifx_db.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 = ifx_db.prepare(conn, insert1)
animal = None
ifx_db.bind_param(stmt, 1, animal)
if ifx_db.execute(stmt):
stmt = ifx_db.exec_immediate(conn, select)
row = ifx_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ifx_db.fetch_tuple(stmt)
ifx_db.rollback(conn)
else:
print "Connection failed."
def run_test_312(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ifx_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i, p in enumerate(params, 1):
ifx_db.bind_param(stmt, i, Wrapper(p))
if ifx_db.execute(stmt):
print("Executing statement")
ifx_db.execute(stmt)
# force the cache to be unbound
for i, p in enumerate(params, 1):
ifx_db.bind_param(stmt, i, p)
ifx_db.rollback(conn)
else:
print("Connection failed.")
def run_test_040(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
# Drop the test table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ifx_db.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 = ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.exec_immediate(conn, "select * from animals")
onerow = ifx_db.fetch_tuple(stmt)
for element in onerow:
print element
ifx_db.rollback(conn)
def run_test_011(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
stmt = ifx_db.exec_immediate(
conn, "DELETE FROM animals WHERE weight > 10.0")
print "Number of affected rows: %d" % ifx_db.num_rows(stmt)
ifx_db.rollback(conn)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_003(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ifx_db.exec_immediate(conn, sql)
print "Number of affected rows: %d" % ifx_db.num_rows(res)
ifx_db.rollback(conn)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_133(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = ifx_db.prepare(conn, sql)
res = ifx_db.execute(
stmt, (128, 'hacker of human and technological nature',
'Wez the ruler of all things PECL', 88.3))
stmt = ifx_db.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ifx_db.execute(stmt, (128, ))
row = ifx_db.fetch_assoc(stmt)
for i in row:
print i
ifx_db.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
try:
stmt = ifx_db.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ifx_db.execute(stmt, (128, ))
row = ifx_db.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
ifx_db.rollback(conn)
print "Done"
def run_test_111(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
if (server.DBMS_NAME[0:3] == 'Inf'):
num1 = ifx_db.field_num(stmt, "id")
num2 = ifx_db.field_num(stmt, "breed")
num3 = ifx_db.field_num(stmt, "number")
num4 = ifx_db.field_num(stmt, "NUMBER")
num5 = ifx_db.field_num(stmt, "bREED")
num6 = ifx_db.field_num(stmt, 8)
num7 = ifx_db.field_num(stmt, 1)
num8 = ifx_db.field_num(stmt, "WEIGHT")
else:
num1 = ifx_db.field_num(stmt, "ID")
num2 = ifx_db.field_num(stmt, "BREED")
num3 = ifx_db.field_num(stmt, "NUMBER")
num4 = ifx_db.field_num(stmt, "number")
num5 = ifx_db.field_num(stmt, "Breed")
num6 = ifx_db.field_num(stmt, 8)
num7 = ifx_db.field_num(stmt, 1)
num8 = ifx_db.field_num(stmt, "weight")
print "%s" % num1
print "int(%d)" % num2
print "int(%d)" % num3
print "%s" % num4
print "%s" % num5
print "%s" % num6
print "int(%d)" % num7
print "%s" % num8
ifx_db.rollback(conn)
else:
print "Connection failed."
def run_test_121(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
name1 = ifx_db.field_name(stmt, 0)
name2 = ifx_db.field_name(stmt, 1)
name3 = ifx_db.field_name(stmt, 2)
name4 = ifx_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'Inf'):
name5 = ifx_db.field_name(stmt, "breed")
name6 = ifx_db.field_name(stmt, 7)
name7 = ifx_db.field_name(stmt, '"nUMBER"')
name8 = ifx_db.field_name(stmt, "number")
else:
name5 = ifx_db.field_name(stmt, "BREED")
name6 = ifx_db.field_name(stmt, 7)
name7 = ifx_db.field_name(stmt, '"Number"')
name8 = ifx_db.field_name(stmt, "NUMBER")
print "string(%d) \"%s\"" % (len(name1), name1)
print "string(%d) \"%s\"" % (len(name2), name2)
print "%s" % name3
print "%s" % name4
print "string(%d) \"%s\"" % (len(name5), name5)
print "%s" % name6
print "%s" % name7
print "string(%d) \"%s\"" % (len(name8), name8)
ifx_db.rollback(conn)
else:
print "Connection failed."
def run_test_145(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
stmt = ifx_db.prepare(
conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ifx_db.bind_param(stmt, 1, id)
ifx_db.bind_param(stmt, 2, breed)
ifx_db.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
ifx_db.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 ifx_db.bind_param and ifx_db.execute treat Python Nones and empty
# strings the right way.
ifx_db.execute(stmt, (1000, None, 'PythonDS'))
result = ifx_db.exec_immediate(
conn,
"SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ifx_db.fetch_tuple(result)
while (row):
for i in row:
print i
row = ifx_db.fetch_tuple(result)
ifx_db.rollback(conn)
else:
print "Connection failed."
def run_test_100(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
stmt = ifx_db.exec_immediate(
conn, "SELECT * FROM animals ORDER BY breed")
fields1 = ifx_db.num_fields(stmt)
print "int(%d)" % fields1
stmt = ifx_db.exec_immediate(
conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ifx_db.num_fields(stmt)
print "int(%d)" % fields2
stmt = ifx_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ifx_db.num_fields(stmt)
print "int(%d)" % fields3
stmt = ifx_db.exec_immediate(
conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ifx_db.num_fields(stmt)
print "int(%d)" % fields4
stmt = ifx_db.exec_immediate(
conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ifx_db.num_fields(stmt)
print "int(%d)" % fields5
ifx_db.rollback(conn)
else:
print "Connection failed."
def run_test_133(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = ifx_db.prepare(conn, sql)
res = ifx_db.execute(
stmt, (128, 'hacker of human and technological nature',
'Wez the ruler of all things PECL', 88.3))
stmt = ifx_db.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ifx_db.execute(stmt, (128, ))
row = ifx_db.fetch_assoc(stmt)
for i in row:
print i
ifx_db.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
try:
stmt = ifx_db.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ifx_db.execute(stmt, (128, ))
row = ifx_db.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % ifx_db.stmt_error(stmt)
print "Message: %s" % ifx_db.stmt_errormsg(stmt)
ifx_db.rollback(conn)
print "Done"
#__END__
#__LUW_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__ZOS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__SYSTEMI_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][AS] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__IDS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length. SQLCODE=-1279
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][IDS%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
def run_test_311(self):
# Make a connection
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_ON)
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ifx_db.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 = ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ifx_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ifx_db.num_rows(res)
stmt = ifx_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'"
)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
print data[1]
print data[2]
data = ifx_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ifx_db.exec_immediate(conn, sql)
print "Number of updated rows:", ifx_db.num_rows(res)
stmt = ifx_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'"
)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
print data[1]
print data[2]
data = ifx_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ifx_db.exec_immediate(conn, sql)
print "Number of deleted rows:", ifx_db.num_rows(res)
stmt = ifx_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'"
)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
print data[1]
print data[2]
data = ifx_db.fetch_both(stmt)
ifx_db.rollback(conn)
ifx_db.close(conn)
