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_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_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_146(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
name = "Peaches"
second_name = "Rickety Ride"
weight = 0
print "Values of bound parameters _before_ CALL:"
print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight)
stmt, name, second_name, weight = ifx_db.callproc(
conn, 'match_animal', (name, second_name, weight))
if stmt is not None:
print "Values of bound parameters _after_ CALL:"
print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight)
if (server.DBMS_NAME[0:3] != 'IDS'):
print "Results:"
row = ifx_db.fetch_tuple(stmt)
while (row):
print " %s, %s, %s" % (row[0].strip(), row[1].strip(),
row[2])
row = ifx_db.fetch_tuple(stmt)
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_158(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)
result = ifx_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ifx_db.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 = ifx_db.fetch_assoc(result)
result2 = ifx_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ifx_db.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 = ifx_db.fetch_assoc(result2)
result3 = ifx_db.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 = ifx_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ifx_db.fetch_tuple(result3)
print output
def run_test_064(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.tables(conn, None, 't')
else:
result = ifx_db.tables(conn, None, 'T')
for i in range(0, ifx_db.num_fields(result)):
print "%s, " % ifx_db.field_name(result, i)
print
print
i = 0
row = ifx_db.fetch_tuple(result)
while (row):
ifx_db.num_fields(result)
if (i < 4):
print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n"
i = i + 1
row = ifx_db.fetch_tuple(result)
ifx_db.free_result(result)
ifx_db.exec_immediate(conn, 'DROP TABLE t.t1')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t2')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t3')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_197(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if conn:
try:
rc = ifx_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ifx_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ifx_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ifx_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = ifx_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
try:
rc = ifx_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ifx_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ifx_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ifx_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = ifx_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
print "Test non-existent table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ifx_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ifx_db.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + ifx_db.conn_errormsg()
def run_test_065(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
result = ifx_db.tables(conn, None, '%', "t3")
columns = ifx_db.num_fields(result)
for i in range(0, columns):
print "%s, " % ifx_db.field_name(result, i)
print "\n\n"
row = ifx_db.fetch_tuple(result)
while (row):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "
row = ifx_db.fetch_tuple(result)
print final
ifx_db.free_result(result)
ifx_db.exec_immediate(conn, 'DROP TABLE t.t1')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t2')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t3')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t4')
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_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_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_190(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.columns(conn, None, config.user, "employee")
else:
result = ifx_db.columns(conn, None, None, "EMPLOYEE")
row = ifx_db.fetch_tuple(result)
while (row):
str = row[1] + "/" + row[3]
print str
row = ifx_db.fetch_tuple(result)
print "done!"
else:
print "no connection:", ifx_db.conn_errormsg()
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_201(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
procedure = """CREATE FUNCTION multiResults ()
RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2);
DEFINE p_name CHAR(16);
DEFINE p_id INT;
DEFINE p_breed VARCHAR(32);
DEFINE p_weight NUMERIC(7,2);
FOREACH c1 FOR
SELECT name, id, breed, weight
INTO p_name, p_id, p_breed, p_weight
FROM animals
ORDER BY name DESC
RETURN p_name, p_id, p_breed, p_weight WITH RESUME;
END FOREACH;
END FUNCTION;"""
if conn:
try:
ifx_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ifx_db.exec_immediate(conn, procedure)
stmt = ifx_db.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = ifx_db.fetch_tuple(stmt)
while (row):
for i in row:
print str(i).strip()
row = ifx_db.fetch_tuple(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_103(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn, "select * from org, project order by project.projname")
cols = ifx_db.num_fields(result)
j = 1
row = ifx_db.fetch_tuple(result)
while (row):
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if (j > 10):
break
row = ifx_db.fetch_tuple(result)
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
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_200(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info( conn )
procedure = """
CREATE FUNCTION multiResults()
RETURNING CHAR(16), INT;
DEFINE p_name CHAR(16);
DEFINE p_id INT;
FOREACH c1 FOR
SELECT name, id
INTO p_name, p_id
FROM animals
ORDER BY name
RETURN p_name, p_id WITH RESUME;
END FOREACH;
END FUNCTION;
"""
if conn:
try:
ifx_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ifx_db.exec_immediate(conn, procedure)
stmt = ifx_db.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = ifx_db.fetch_tuple(stmt)
while ( row ):
for i in row:
print i
row = ifx_db.fetch_tuple(stmt)
ifx_db.close(conn)
else:
print "Connection failed."
def run_test_045(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
fp = open("tests/pic1_out.jpg", "wb")
result = ifx_db.exec_immediate(
conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'")
row = ifx_db.fetch_tuple(result)
if row:
fp.write(row[0])
else:
print ifx_db.stmt_errormsg()
fp.close()
cmp = (open('tests/pic1_out.jpg',
'rb').read() == open('tests/pic1.jpg', 'rb').read())
print 'Are the files the same:', cmp
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_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_103(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn,
"select * from org, project order by project.projname,org.deptnumb"
)
cols = ifx_db.num_fields(result)
j = 1
row = ifx_db.fetch_tuple(result)
while (row):
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if (j > 10):
break
row = ifx_db.fetch_tuple(result)
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
#__END__
#__IDS_EXPECTED__
#1) 10 Head Office 160 Corporate New York AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#2) 15 New England 50 Eastern Boston AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#3) 20 Mid Atlantic 10 Eastern Washington AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#4) 38 South Atlantic 30 Eastern Atlanta AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#5) 42 Great Lakes 100 Midwest Chicago AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#6) 51 Plains 140 Midwest Dallas AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#7) 66 Pacific 270 Western San Francisco AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#8) 84 Mountain 290 Western Denver AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#9) 10 Head Office 160 Corporate New York AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01
#10) 15 New England 50 Eastern Boston AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01
def run_test_023(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if (conn != 0):
stmt = ifx_db.column_privileges(conn, None, config.user, 'animals')
row = ifx_db.fetch_tuple(stmt)
if row:
print row[0]
print row[1]
print row[2]
print row[3]
print row[4]
print row[5]
print row[6]
print row[7]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n\n"
def run_test_048(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if (not conn):
print "Could not make a connection."
return 0
server = ifx_db.server_info( conn )
fp = open("tests/spook_out.png", "wb")
result = ifx_db.exec_immediate(conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'")
if (not result):
print "Could not execute SELECT statement."
return 0
row = ifx_db.fetch_tuple(result)
if row:
fp.write(row[0])
else:
print ifx_db.stmt_errormsg()
fp.close()
cmp = (open('tests/spook_out.png', "rb").read() == open('tests/spook.png', "rb").read())
print "Are the files the same:", cmp
def run_test_025(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if (conn != 0):
drop = 'DROP TABLE test_primary_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ifx_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES (1)"
result = ifx_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ifx_db.primary_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = ifx_db.primary_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[4]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n"
def run_test_154(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)
try:
statement = 'DROP TABLE fetch_test'
result = ifx_db.exec_immediate(conn, statement)
except:
pass
server = ifx_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
else:
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
result = ifx_db.exec_immediate(conn, statement)
result = ifx_db.exec_immediate(conn, st0)
result = ifx_db.exec_immediate(conn, st1)
result = ifx_db.exec_immediate(conn, st2)
result = ifx_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
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)
result = ifx_db.prepare(conn, statement)
ifx_db.execute(result)
row = ifx_db.fetch_assoc(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length)
print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2']))
row = ifx_db.fetch_assoc(result)
result = ifx_db.prepare(conn, statement)
ifx_db.execute(result)
row = ifx_db.fetch_both(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row[1], row[1].length)
print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \
(row['COL1'],len(row['COL1']), row[1], len(row[1]))
row = ifx_db.fetch_both(result)
ifx_db.close(conn)
def run_test_024(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ifx_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ifx_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ifx_db.exec_immediate(conn, statement)
stmt = ifx_db.foreign_keys(conn, None, config.user,
'test_primary_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, None, None)
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, config.user, 'test_foreign_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, 'dummy_schema')
row = ifx_db.fetch_tuple(stmt)
if (not row):
print "No Data Found"
else:
print row[2]
print row[3]
print row[6]
print row[7]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n"
def run_test_201(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
if (server == 'IDS'):
procedure = """CREATE FUNCTION multiResults ()
RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2);
DEFINE p_name CHAR(16);
DEFINE p_id INT;
DEFINE p_breed VARCHAR(32);
DEFINE p_weight NUMERIC(7,2);
FOREACH c1 FOR
SELECT name, id, breed, weight
INTO p_name, p_id, p_breed, p_weight
FROM animals
ORDER BY name DESC
RETURN p_name, p_id, p_breed, p_weight WITH RESUME;
END FOREACH;
END FUNCTION;"""
else:
procedure = """CREATE PROCEDURE multiResults ()
RESULT SETS 3
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
ORDER BY name;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, id, breed, weight
FROM animals
ORDER BY name DESC;
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name
FROM animals
ORDER BY name;
OPEN c1;
OPEN c2;
OPEN c3;
END"""
if conn:
try:
ifx_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ifx_db.exec_immediate(conn, procedure)
stmt = ifx_db.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = ifx_db.fetch_tuple(stmt)
while (row):
for i in row:
print str(i).strip()
row = ifx_db.fetch_tuple(stmt)
if (server == 'IDS'):
print "Fetching second result set (should fail -- IDS does not support multiple result sets)"
else:
print "Fetching second result set"
res = ifx_db.next_result(stmt)
if res:
row = ifx_db.fetch_tuple(res)
while (row):
for i in row:
print str(i).strip()
row = ifx_db.fetch_tuple(res)
if (server == 'IDS'):
print "Fetching third result set (should fail -- IDS does not support multiple result sets)"
else:
print "Fetching third result set"
res2 = ifx_db.next_result(stmt)
if res2:
row = ifx_db.fetch_tuple(res2)
while (row):
for i in row:
print str(i).strip()
row = ifx_db.fetch_tuple(res2)
ifx_db.close(conn)
else:
print "Connection failed."
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)
