def run_test_113(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "DROP TABLE datetest"
try:
ifx_db.exec_immediate( conn, drop )
except:
pass
create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )"
ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'03-27-1982')"
ifx_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'07-08-1981')"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.prepare(conn, "SELECT * FROM datetest")
ifx_db.execute(stmt)
result = ifx_db.fetch_row( stmt )
while ( result ):
row0 = ifx_db.result(stmt, 0)
row1 = ifx_db.result(stmt, 1)
print row0
print row1
result = ifx_db.fetch_row( stmt )
else:
print "Connection failed."
def run_test_114(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
ifx_db.exec_immediate(conn, drop)
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
ifx_db.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.prepare(conn, "SELECT * FROM numericliteral")
ifx_db.execute(stmt)
result = ifx_db.fetch_row(stmt)
while (result):
row0 = ifx_db.result(stmt, 0)
row1 = ifx_db.result(stmt, 1)
print row0
print row1
result = ifx_db.fetch_row(stmt)
else:
print "Connection failed."
def run_test_018(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_ON)
if conn:
stmt = ifx_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
ifx_db.free_result(stmt)
else:
print ifx_db.stmt_errormsg()
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
ifx_db.free_result(stmt)
else:
print ifx_db.stmt_errormsg()
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
ifx_db.close(conn)
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_039(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result = ifx_db.prepare(
conn, "SELECT * FROM animals",
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ifx_db.prepare(conn, "SELECT * FROM animals")
ifx_db.execute(result)
row = ifx_db.fetch_row(result)
while (row):
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
result2 = ifx_db.prepare(conn, "SELECT * FROM animals", {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result2 = ifx_db.prepare(conn, "SELECT * FROM animals")
ifx_db.execute(result2)
while (ifx_db.fetch_row(result2)):
print "%s : %s : %s : %s" % (ifx_db.result(result2, 0), \
ifx_db.result(result2, 1), \
ifx_db.result(result2, 2), \
ifx_db.result(result2, 3))
row = ifx_db.fetch_row(result)
def run_test_014(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
query = 'SELECT * FROM animals ORDER BY name'
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
stmt = ifx_db.prepare(
conn, query,
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ifx_db.prepare(conn, query)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print "%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3])
data = ifx_db.fetch_both(stmt)
try:
stmt = ifx_db.prepare(
conn, query,
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN})
ifx_db.execute(stmt)
rc = ifx_db.fetch_row(stmt, -1)
print "Fetch row -1: %s" % str(rc)
except:
print "Requested row number must be a positive value"
ifx_db.close(conn)
def run_test_037(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
result = ifx_db.exec_immediate(conn,
"SELECT * FROM staff WHERE id < 101")
row = ifx_db.fetch_row(result)
while (row):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ifx_db.prepare(conn,
"SELECT * FROM staff WHERE id < 101",
{
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result2 = ifx_db.prepare(conn,
"SELECT * FROM staff WHERE id < 101")
ifx_db.execute(result2)
row2 = ifx_db.fetch_row(result2)
while (row2):
print "%s : %s : %s : %s : %s" % (ifx_db.result(result2, 0), \
ifx_db.result(result2, 1), \
ifx_db.result(result2, 2), \
ifx_db.result(result2, 3), \
ifx_db.result(result2, 5))
row2 = ifx_db.fetch_row(result2)
row = ifx_db.fetch_row(result)
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_warn(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
drop = "DROP TABLE TEST1"
try:
result = ifx_db.exec_immediate(conn,drop)
except:
pass
# Create the table test1
create = "CREATE TABLE TEST1 (COL1 CHAR(5))"
result = ifx_db.exec_immediate(conn, create)
# Insert a string longer than 5 characters to force an error
# ifx_db.stmt_warn() API
query = 'INSERT INTO TEST1 VALUES (?)'
stmt = ifx_db.prepare(conn, query)
try:
ifx_db.execute(stmt, ('ABCDEF',))
except:
pass
print(ifx_db.stmt_warn(stmt))
ifx_db.close(conn)
else:
print ("Connection failed.")
def run_test_115(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ifx_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.prepare(conn, "SELECT data FROM numericliteral")
ifx_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ifx_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ifx_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ifx_db.result(stmt, 'DATA')
else:
row.DATA = ifx_db.result(stmt, 'data')
print row.DATA
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ifx_db.exec_immediate(conn, insert)
stmt = ifx_db.prepare(conn, "SELECT data FROM numericliteral")
ifx_db.execute(stmt)
# row = ifx_db.fetch_object(stmt, 0)
ifx_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ifx_db.result(stmt, 'DATA')
else:
row.DATA = ifx_db.result(stmt, 'data')
print row.DATA
else:
print "Connection failed."
def run_test_264(self):
# Make a connection
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
server = ifx_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if (server.DBMS_NAME[0:3] == 'IDS'):
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 = ifx_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ifx_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ifx_db.num_rows(res)
stmt = ifx_db.prepare(conn, "SELECT * FROM tab_bigint")
ifx_db.execute(stmt)
data = ifx_db.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 = ifx_db.fetch_both(stmt)
# test ifx_db.result for fetch of bigint
stmt1 = ifx_db.prepare(conn, "SELECT col2 FROM tab_bigint")
ifx_db.execute(stmt1)
ifx_db.fetch_row(stmt1, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row1 = ifx_db.result(stmt1, 'COL2')
else:
row1 = ifx_db.result(stmt1, 'col2')
print row1
ifx_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ifx_db.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 = ifx_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ifx_db.prepare(conn, statement)
result = ifx_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ifx_db.prepare(conn, statement)
result = ifx_db.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ifx_db.exec_immediate(conn, statement)
for i in range(0, ifx_db.num_fields(result)):
print str(i) + ":" + ifx_db.field_type(result, i)
statement = "SELECT * FROM tab_datetime"
stmt = ifx_db.prepare(conn, statement)
rc = ifx_db.execute(stmt)
result = ifx_db.fetch_row(stmt)
while (result):
row0 = ifx_db.result(stmt, 0)
row1 = ifx_db.result(stmt, 1)
row2 = ifx_db.result(stmt, 2)
print type(row0), row0
print type(row1), row1
print type(row2), row2
result = ifx_db.fetch_row(stmt)
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_147(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'
try:
ifx_db.bind_param(stmt, 1, id)
ifx_db.bind_param(stmt, 2, breed)
ifx_db.bind_param(stmt, 3, name)
error = ifx_db.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 = 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_144(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ifx_db.exec_immediate(conn, create)
stmt = ifx_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ifx_db.bind_param(stmt, 1, picture, ifx_db.SQL_PARAM_INPUT,
ifx_db.SQL_BINARY)
rc = ifx_db.execute(stmt)
num = ifx_db.num_rows(stmt)
print num
else:
print "Connection failed."
def run_test_157b(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
if (server.DBMS_NAME[0:3] != 'IDS'):
stmt = ifx_db.prepare(conn, sql, {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ifx_db.prepare(
conn, sql,
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC})
result = ifx_db.execute(stmt)
i = 2
row = ifx_db.fetch_assoc(stmt, i)
while (row):
if (server.DBMS_NAME[0:3] == 'IDS'):
#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 = ifx_db.fetch_assoc(stmt, i)
def run_test_142(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.prepare(conn, sql)
weight = 200.05
mass = 2.0
ifx_db.bind_param(stmt, 1, weight, ifx_db.SQL_PARAM_INPUT)
ifx_db.bind_param(stmt, 2, mass, ifx_db.SQL_PARAM_INPUT)
result = ifx_db.execute(stmt)
if ( result ):
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.close(conn)
else:
print "Connection failed."
def run_test_warn(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
# Get the server type
serverinfo = ifx_db.server_info(conn)
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ifx_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ifx_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ifx_db.prepare(conn, query, {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ifx_db.prepare(conn, query)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
if data:
print("Success")
else:
print("No Data")
print(ifx_db.stmt_warn(stmt))
ifx_db.close(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_261(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'Inf'):
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.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 = ifx_db.prepare(conn, sql)
ifx_db.execute(stmt, (0, ))
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# pet = ifx_db.fetch_object(stmt)
# while (pet):
# print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED)
# pet = ifx_db.fetch_object(stmt)
class Pet:
pass
data = ifx_db.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 = ifx_db.fetch_assoc(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_013(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = ifx_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
stmt = ifx_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ifx_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0")
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
data = ifx_db.fetch_both(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_006(self):
options1 = {
ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN
}
options2 = {
ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY
}
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = ifx_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ifx_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
data = ifx_db.fetch_both(stmt)
print ""
stmt = ifx_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print data[0]
data = ifx_db.fetch_both(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_6755(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
drop = 'DROP TABLE table_6755'
result = ''
try:
result = ifx_db.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', 'database')"
else:
create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB(20))'
insert = "INSERT INTO table_6755 VALUES ('database', 'database')"
result = ifx_db.exec_immediate(conn, create)
result = ifx_db.exec_immediate(conn, insert)
statement = "SELECT col1, col2 FROM table_6755"
result = ifx_db.prepare(conn, statement)
ifx_db.execute(result)
row = ifx_db.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 = ifx_db.fetch_tuple(result)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_131(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?" )
if (ifx_db.execute(stmt, (0,))):
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)
else:
print "Connection failed."
def run_test_021(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, "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.commit(conn)
stmt = ifx_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ifx_db.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 = ifx_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ifx_db.execute(stmt, animal)
ifx_db.commit(conn)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_140(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?")
animal = 0
ifx_db.bind_param(stmt, 1, animal)
if ifx_db.execute(stmt):
row = ifx_db.fetch_tuple(stmt)
while ( row ):
#roiw.each { |child| puts child }
for i in row:
print i
row = ifx_db.fetch_tuple(stmt)
else:
print "Connection failed."
def run_test_141(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?"
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.prepare(conn, sql)
animal = 5
mass = 2.0
ifx_db.bind_param(stmt, 1, animal)
ifx_db.bind_param(stmt, 2, mass)
if ifx_db.execute(stmt):
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.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"
#__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_265(self):
# Make a connection
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
cursor_option = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC}
if conn:
server = ifx_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
try:
sql = 'drop table test'
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
except:
pass
if ((server.DBMS_NAME[0:3] == 'IDS') or (server.DBMS_NAME[0:2] == "AS")):
sql = "create table test(id integer, name VARCHAR(10), clob_col CLOB, some_var VARCHAR(100) )"
else:
sql = "create table test(id integer, name VARCHAR(10), clob_col CLOB, some_var XML )"
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
sql = 'select id from test'
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
sql = "insert into test values( 1, 'some', 'here is a clob value', '<?xml version=\"1.0\" encoding=\"UTF-8\" ?><test attribute=\"value\"/>')"
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
sql = "insert into test values(2, 'value', 'clob data', NULL)"
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
sql = "insert into test values(2, 'in varchar', 'data2', NULL)"
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
sql = 'select * from test'
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
row = ifx_db.fetch_tuple(stmt)
while ( row ):
print "%s, %s, %s, %s\n" %(row[0], row[1], row[2], ((row[3] is not None) and row[3].startswith(u'\ufeff')) and row[3][1:] or row[3])
row = ifx_db.fetch_tuple(stmt)
sql = 'select id, name from test where id = ?'
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
ifx_db.execute(stmt, (2,))
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
row = ifx_db.fetch_tuple(stmt)
while ( row ):
print "%s, %s\n" %(row[0], row[1])
row = ifx_db.fetch_tuple(stmt)
if (server.DBMS_NAME[0:3] == 'IDS'):
sql = "select * from test"
else:
sql = 'select * from test fetch first 12 rows only optimize for 12 rows'
stmt = ifx_db.prepare(conn, sql)
ifx_db.set_option(stmt, cursor_option, 0)
#ifx_db.num_fields(stmt)
ifx_db.execute(stmt)
print "Number of affected rows: %d" % ifx_db.get_num_result(stmt)
row = ifx_db.fetch_tuple(stmt)
while ( row ):
print "%s, %s, %s, %s\n" %(row[0], row[1], row[2], ((row[3] is not None) and row[3].startswith(u'\ufeff')) and row[3][1:] or row[3])
row = ifx_db.fetch_tuple(stmt)
ifx_db.close(conn)
def run_test_decimal(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = ifx_db.server_info(conn)
drop = "DROP TABLE STOCKSHARE"
try:
result = ifx_db.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 = ifx_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ifx_db.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 = ifx_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ifx_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ifx_db.bind_param(stmt, 1, id)
ifx_db.bind_param(stmt, 2, company)
ifx_db.bind_param(stmt, 3, stockshare)
error = ifx_db.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 = ifx_db.prepare(conn, query, {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ifx_db.prepare(conn, query)
ifx_db.execute(stmt)
data = ifx_db.fetch_both(stmt)
while (data):
print "%s : %s : %s\n" % (data[0], data[1], data[2])
data = ifx_db.fetch_both(stmt)
try:
stmt = ifx_db.prepare(conn, query, {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
ifx_db.execute(stmt)
rc = ifx_db.fetch_row(stmt, -1)
print "Fetch Row -1:%s " % str(rc)
except:
print "Requested row number must be a positive value"
ifx_db.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
