def run_test_061(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 conn:
server = ifx_db.server_info( conn )
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
result = ifx_db.tables(conn, None, 't');
i = 0
row = ifx_db.fetch_both(result)
while ( row ):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print str
i = i + 1
row = ifx_db.fetch_both(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')
print "done!"
else:
print "no connection: %s" % ifx_db.conn_errormsg()
def run_test_155(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 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')"
)
i = 0
row = ifx_db.fetch_assoc(result)
while (row):
i += 1
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
if (row['midinit'] == None):
row['midinit'] = ''
print "%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['empno'], row['firstnme'], row['midinit'], row['lastname'], row['workdept'], \
row['phoneno'], row['hiredate'], row['job'], row['edlevel'], row['sex'], \
row['birthdate'], row['salary'], row['bonus'], row['comm'])
row = ifx_db.fetch_assoc(result)
else:
if (row['MIDINIT'] == None):
row['MIDINIT'] = ''
print "%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['EMPNO'], row['FIRSTNME'], row['MIDINIT'], row['LASTNAME'], row['WORKDEPT'], \
row['PHONENO'], row['HIREDATE'], row['JOB'], row['EDLEVEL'], row['SEX'], \
row['BIRTHDATE'], row['SALARY'], row['BONUS'], row['COMM'])
row = ifx_db.fetch_assoc(result)
print "%d record(s) selected." % i
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_191(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] == 'IDS'):
result = ifx_db.columns(conn,None,config.user,"emp_photo");
else:
result = ifx_db.columns(conn,None,None,"EMP_PHOTO");
i = 0
row = ifx_db.fetch_both(result)
while ( row ):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ( (row['column_name'] != 'emp_rowid') and (i < 3) ):
print "%s,%s,%s,%s\n" % (row['table_schem'],
row['table_name'], row['column_name'], row['is_nullable'])
else :
if ( (row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3) ):
print"%s,%s,%s,%s\n" % (row['TABLE_SCHEM'],
row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE'])
i = i + 1
row = ifx_db.fetch_both(result)
print "done!"
else:
print "no connection: ", ifx_db.conn_errormsg()
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_054(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
serverinfo = ifx_db.server_info(conn)
stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals")
val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0)
print val
op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY}
stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0)
print val
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN}
else:
op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC}
stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0)
print val
op = {ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC}
stmt = ifx_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ifx_db.get_option(stmt, ifx_db.SQL_ATTR_CURSOR_TYPE, 0)
print val
def run_test_150(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)
result = ifx_db.exec_immediate(conn, "select * from staff")
row = ifx_db.fetch_assoc(result)
while (row):
#print "%5d " % row['ID']
#print "%-10s " % row['NAME']
#print "%5d " % row['DEPT']
#print "%-7s " % row['JOB']
#print "%5d " % row['YEARS']
#print "%15s " % row['SALARY']
#print "%10s " % row['COMM']
if (row['YEARS'] == None):
row['YEARS'] = 0
if (row['COMM'] == None):
row['COMM'] = ''
print "%5d %-10s %5d %-7s %5s %15s %10s " % (
row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'],
row['SALARY'], row['COMM'])
row = ifx_db.fetch_assoc(result)
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_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_161(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)
result = ifx_db.exec_immediate(
conn, "select * from emp_act order by projno desc")
row = ifx_db.fetch_both(result)
count = 1
while (row):
print "Record", count, ": %6s %-6s %3d %9s %10s %10s %6s " % (
row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'],
row['EMENDATE'], row[0])
result2 = ifx_db.exec_immediate(
conn, "select * from employee where employee.empno='" +
row['EMPNO'] + "'")
row2 = ifx_db.fetch_both(result2)
if row2:
print ">>%s,%s,%s,%s,%s,%s,%s" % (
row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'], row2[3],
row2[3], row2[5], row2[6])
count = count + 1
if (count > 10):
break
row = ifx_db.fetch_both(result)
def run_test_152(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 project")
row = ifx_db.fetch_assoc(result)
while (row):
#printf("%6s ",row['PROJNO'])
#printf("%-24s ",row['PROJNAME'])
#printf("%3s ",row['DEPTNO'])
#printf("%6s",row['RESPEMP'])
#printf("%7s ",row['PRSTAFF'])
#printf("%10s ",row['PRSTDATE'])
#printf("%10s ",row['PRENDATE'])
#printf("%6s",row['MAJPROJ'])
#puts ""
if (row['MAJPROJ'] == None):
row['MAJPROJ'] = ''
print "%6s %-24s %3s %6s%7s %10s %10s %6s" % (
row['PROJNO'], row['PROJNAME'], row['DEPTNO'], row['RESPEMP'],
row['PRSTAFF'], row['PRSTDATE'], row['PRENDATE'],
row['MAJPROJ'])
row = ifx_db.fetch_assoc(result)
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_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_300(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER)
print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
ifx_db.close(conn)
else:
print "Error."
def run_test_spinout_timestamp(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 PROCEDURE PROC_TIMESTAMP"
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
# Create the SP with timestamp parameters
create = "CREATE PROCEDURE PROC_TIMESTAMP ( INOUT PAR1 DATETIME YEAR TO FRACTION(5), OUT PAR2 DATETIME YEAR TO FRACTION(5)) LET PAR2 = PAR1; END PROCEDURE"
result = ifx_db.exec_immediate(conn, create)
# call the SP. Expect PAR2 to contain value passed to PAR1
par1 = "2017-05-13 22:47:29.82688"
par2 = ""
print "Values of bound parameters _before_ CALL:"
print " 1: %s 2: %s\n" % (par1, par2)
stmt, par1, par2 = ifx_db.callproc(conn, 'proc_timestamp',
(par1, par2))
if stmt is not None:
print "Values of bound parameters _after_ CALL:"
print " 1: %s 2: %s\n" % (par1, par2)
ifx_db.close(conn)
else:
print("Connection failed.")
def run_test_157a(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
print "Starting..."
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
result = ifx_db.exec_immediate(conn, sql)
try:
i = 2
row = ifx_db.fetch_assoc(result, i)
while (row):
if (server.DBMS_NAME[0:3] == 'IDS'):
print "%-5d %-16s %-32s %10s" % (
row['id'], row['name'], row['breed'],
row['weight'])
else:
print "%-5d %-16s %-32s %10s" % (
row['ID'], row['NAME'], row['BREED'],
row['WEIGHT'])
i = i + 2
row = ifx_db.fetch_assoc(result, i)
except:
print "SQLSTATE: %s" % ifx_db.stmt_error(result)
print "Message: %s" % ifx_db.stmt_errormsg(result)
print "DONE"
def run_test_120(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 * FROM animals")
name1 = ifx_db.field_name(stmt, 1)
name2 = ifx_db.field_name(stmt, 2)
name3 = ifx_db.field_name(stmt, 3)
name4 = ifx_db.field_name(stmt, 4)
name6 = ifx_db.field_name(stmt, 8)
name7 = ifx_db.field_name(stmt, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ifx_db.field_name(stmt, "id")
name8 = ifx_db.field_name(stmt, "WEIGHT")
else:
name5 = ifx_db.field_name(stmt, "ID")
name8 = ifx_db.field_name(stmt, "weight")
print "string(%d) \"%s\"" % (len(name1), name1)
print "string(%d) \"%s\"" % (len(name2), name2)
print "string(%d) \"%s\"" % (len(name3), name3)
print "%s" % name4
print "string(%d) \"%s\"" % (len(name5), name5)
print "%s" % name6
print "string(%d) \"%s\"" % (len(name7), name7)
print "%s" % name8
else:
print "Connection failed."
def run_test_157(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'):
result = ifx_db.exec_immediate(conn, sql, {
ifx_db.SQL_ATTR_CURSOR_TYPE:
ifx_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result = ifx_db.exec_immediate(
conn, sql,
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_STATIC})
i = 2
row = ifx_db.fetch_assoc(result, i)
while (row):
if (server.DBMS_NAME[0:3] == 'IDS'):
print "%-5d %-16s %-32s %10s\n" % (
row['id'], row['name'], row['breed'], row['weight'])
else:
print "%-5d %-16s %-32s %10s\n" % (
row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
i = i + 2
row = ifx_db.fetch_assoc(result, i)
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_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_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_062(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 conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
schema = 't'
else:
schema = 'T'
result = ifx_db.tables(conn, None, schema)
i = 0
row = ifx_db.fetch_both(result)
while (row):
str = row[1] + "/" + row[2] + "/" + row[3]
if (i < 4):
print str
i = i + 1
row = ifx_db.fetch_both(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')
print "done!"
else:
print "no connection: #{ifx_db.conn_errormsg}"
def run_test_060(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 conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.tables(conn, None, 't')
else:
result = ifx_db.tables(conn, None, 'T')
i = 0
row = ifx_db.fetch_both(result)
while ( row ):
if (i < 4):
print "/%s/%s" % (row[1], row[2])
i = i + 1
row = ifx_db.fetch_both(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')
print "done!"
else:
print "no connection: #{ifx_db.conn_errormsg}";
def run_test_148(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
##### Set up #####
serverinfo = ifx_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
try:
sql = "DROP TABLE sptb"
ifx_db.exec_immediate(conn, sql)
except:
pass
try:
sql = "DROP PROCEDURE sp"
ifx_db.exec_immediate(conn, sql)
except:
pass
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 VARCHAR(20))"
ifx_db.exec_immediate(conn, sql)
sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'varchar data')"
ifx_db.exec_immediate(conn, sql)
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 VARCHAR(20));
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
ifx_db.exec_immediate(conn, sql)
#############################
##### Run the test #####
out1 = 0
out2 = 0.00
out3 = ""
out4 = 0
out5 = ""
stmt, out1, out2, out3, out4, out5 = ifx_db.callproc(
conn, 'sp', (out1, out2, out3, out4, out5))
print "out 1:"
print out1
print "out 2:"
print out2
print "out 3:"
print out3
print "out 4:"
print out4
print "out 5:"
print out5
#############################
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_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_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_212(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
result = ifx_db.exec_immediate(conn, "select * from sales")
if (server.DBMS_NAME[0:3] == 'Inf'):
i = "sales_person"
else:
i = "SALES_PERSON"
print "%s size %d" % (i, ifx_db.field_display_size(result, i))
i = 2
print "%d size %d" % (i, ifx_db.field_display_size(result, i))
def run_test_159(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)
result = ifx_db.exec_immediate(conn, "select name,job from staff")
i = 1
row = ifx_db.fetch_assoc(result)
while ( row ):
#printf("%3d %10s %10s\n",i, row['NAME'], row['JOB'])
print "%3d %10s %10s" % (i, row['NAME'], row['JOB'])
i += 1
row = ifx_db.fetch_assoc(result)
