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_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_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_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_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_03a(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if conn:
stmt = ifx_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while ( ifx_db.fetch_row(stmt) ):
breed = ifx_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ifx_db.result(stmt, "name")
else:
name = ifx_db.result(stmt, "NAME")
print "string(%d) \"%s\"" % (len(name), name)
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ifx_db.result(stmt, "passport")
else:
name = ifx_db.result(stmt, "PASSPORT")
print name
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_032(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
stmt = ifx_db.exec_immediate(
conn,
"SELECT id, breed, name, weight FROM animals WHERE id = 6")
while (ifx_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ifx_db.result(stmt, "id")
breed = ifx_db.result(stmt, "breed")
name = ifx_db.result(stmt, "name")
weight = ifx_db.result(stmt, "weight")
else:
id = ifx_db.result(stmt, "ID")
breed = ifx_db.result(stmt, "BREED")
name = ifx_db.result(stmt, "NAME")
weight = ifx_db.result(stmt, "WEIGHT")
print "int(%d)" % id
print "string(%d) \"%s\"" % (len(breed), breed)
print "string(%d) \"%s\"" % (len(name), name)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_036(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
result = ifx_db.exec_immediate(conn, "select * from staff")
i=0
row = ifx_db.fetch_row(result)
while ( row ):
result2 = ifx_db.exec_immediate(conn, "select * from staff")
j=0
row2 = ifx_db.fetch_row(result2)
while ( row2 ):
print "%d)%d," % (i, j)
j+=1
row2 = ifx_db.fetch_row(result2)
print "%d, " % i
i+=1
row = ifx_db.fetch_row(result)
ifx_db.close(conn)
def run_test_030(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
stmt = ifx_db.exec_immediate(
conn,
"SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ifx_db.fetch_row(stmt)):
breed = ifx_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
name = ifx_db.result(stmt, "name")
print "string(%d) \"%s\"" % (len(name), name)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_031(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = ifx_db.exec_immediate(
conn,
"SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ifx_db.fetch_row(stmt)):
id = ifx_db.result(stmt, 0)
print "int(%d)" % id
breed = ifx_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
name = ifx_db.result(stmt, 2)
print "string(%d) \"%s\"" % (len(name), name)
weight = ifx_db.result(stmt, 3)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ifx_db.close(conn)
else:
print "Connection failed."
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
def run_test_6792(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = ifx_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (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 table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = ifx_db.prepare(conn, statement)
result = ifx_db.execute(stmt,
(t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
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 table_6792"
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)
row3 = ifx_db.result(stmt, 3)
print row0
print row1
print row2
print row3
result = ifx_db.fetch_row(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__ZOS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__SYSTEMI_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__IDS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
