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_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_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_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_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_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)
def run_test_159a(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 prstdate,prendate from project")
i = 1
row = ifx_db.fetch_assoc(result)
while (row):
#printf("%3d %10s %10s\n",i, row['PRSTDATE'], row['PRENDATE'])
print "%3d %10s %10s" % (i, row['PRSTDATE'], row['PRENDATE'])
i += 1
row = ifx_db.fetch_assoc(result)
def run_test_151(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 sales")
row = ifx_db.fetch_assoc(result)
while ( row ):
#printf("%-10s ",row['SALES_DATE'])
#printf("%-15s ",row['SALES_PERSON'])
#printf("%-15s ",row['REGION'])
#printf("%4s",row['SALES'])
#puts ""
if (row['SALES'] == None):
row['SALES'] = ''
print "%-10s %-15s %-15s %4s" % (row['SALES_DATE'], row['SALES_PERSON'], row['REGION'], row['SALES'])
row = ifx_db.fetch_assoc(result)
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_153(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 org")
row = ifx_db.fetch_assoc(result)
while (row):
#printf("%4d ",row['DEPTNUMB'])
#printf("%-14s ",row['DEPTNAME'])
#printf("%4d ",row['MANAGER'])
#printf("%-10s",row['DIVISION'])
#printf("%-13s ",row['LOCATION'])
#puts ""
print "%4d %-14s %4d %-10s%-13s " % (
row['DEPTNUMB'], row['DEPTNAME'], row['MANAGER'],
row['DIVISION'], row['LOCATION'])
row = ifx_db.fetch_assoc(result)
def run_test_160(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")
row = ifx_db.fetch_both(result)
while ( row ):
#printf("%6s ",row[0])
#printf("%-6s ",row[1])
#printf("%3d ",row[2])
#printf("%9s ",row['EMPTIME'])
#printf("%10s ", row['EMSTDATE'])
#printf("%10s ", row['EMENDATE'])
#printf("%6s ", row[0])
#puts ""
print "%6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])
row = ifx_db.fetch_both(result)
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_034(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
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.exec_immediate(conn, "select * from staff")
row = ifx_db.fetch_assoc(result)
if (row):
#printf("%5d ",row['ID'])
#printf("%-10s ",row['NAME'])
#printf("%5d ",row['DEPT'])
#printf("%-7s ",row['JOB'])
#printf("%5d ", row['YEARS'])
#printf("%15s ", row['SALARY'])
#printf("%10s ", row['COMM'])
#puts ""
print "%5d %-10s %5d %-7s %5d %15s %10s" % (
row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'],
row['SALARY'], row['COMM'])
ifx_db.close(conn)
def run_test_008(self):
op = {ifx_db.ATTR_CASE: ifx_db.CASE_NATURAL}
conn = ifx_db.connect(config.ConnStr, config.user, config.password, op)
server = ifx_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.columns(conn, None, None, "employee")
else:
result = ifx_db.columns(conn, None, None, "EMPLOYEE")
row = ifx_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.columns(conn, None, None, "employee")
else:
result = ifx_db.columns(conn, None, None, "EMPLOYEE")
row = ifx_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ifx_db.ATTR_CASE: ifx_db.CASE_LOWER}
ifx_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.columns(conn, None, None, "employee")
else:
result = ifx_db.columns(conn, None, None, "EMPLOYEE")
row = ifx_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
def run_test_053(self):
print "Client attributes passed through conection string:"
options1 = {ifx_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn1 = ifx_db.connect(config.ConnStr, config.user, config.password,
options1)
val = ifx_db.get_option(conn1, ifx_db.SQL_ATTR_INFO_USERID, 1)
print val
options2 = {ifx_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn2 = ifx_db.connect(config.ConnStr, config.user, config.password,
options2)
val = ifx_db.get_option(conn2, ifx_db.SQL_ATTR_INFO_ACCTSTR, 1)
print val
options3 = {ifx_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn3 = ifx_db.connect(config.ConnStr, config.user, config.password,
options3)
val = ifx_db.get_option(conn3, ifx_db.SQL_ATTR_INFO_APPLNAME, 1)
print val
options4 = {ifx_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn4 = ifx_db.connect(config.ConnStr, config.user, config.password,
options4)
val = ifx_db.get_option(conn4, ifx_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print val
options5 = {
ifx_db.SQL_ATTR_INFO_USERID: 'kfb',
ifx_db.SQL_ATTR_INFO_WRKSTNNAME: 'kfbwork',
ifx_db.SQL_ATTR_INFO_ACCTSTR: 'kfbacc',
ifx_db.SQL_ATTR_INFO_APPLNAME: 'kfbapp'
}
conn5 = ifx_db.connect(config.ConnStr, config.user, config.password,
options5)
val = ifx_db.get_option(conn5, ifx_db.SQL_ATTR_INFO_USERID, 1)
print val
val = ifx_db.get_option(conn5, ifx_db.SQL_ATTR_INFO_ACCTSTR, 1)
print val
val = ifx_db.get_option(conn5, ifx_db.SQL_ATTR_INFO_APPLNAME, 1)
print val
val = ifx_db.get_option(conn5, ifx_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print val
print "Client attributes passed post-conection:"
options5 = {ifx_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn5 = ifx_db.connect(config.ConnStr, config.user, config.password)
rc = ifx_db.set_option(conn5, options5, 1)
val = ifx_db.get_option(conn5, ifx_db.SQL_ATTR_INFO_USERID, 1)
print val
options6 = {ifx_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn6 = ifx_db.connect(config.ConnStr, config.user, config.password)
rc = ifx_db.set_option(conn6, options6, 1)
val = ifx_db.get_option(conn6, ifx_db.SQL_ATTR_INFO_ACCTSTR, 1)
print val
options7 = {ifx_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn7 = ifx_db.connect(config.ConnStr, config.user, config.password)
rc = ifx_db.set_option(conn7, options7, 1)
val = ifx_db.get_option(conn7, ifx_db.SQL_ATTR_INFO_APPLNAME, 1)
print val
options8 = {ifx_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn8 = ifx_db.connect(config.ConnStr, config.user, config.password)
rc = ifx_db.set_option(conn8, options8, 1)
val = ifx_db.get_option(conn8, ifx_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print val
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_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_156(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)
count = 1
while (row):
if (row['YEARS'] == None):
row['YEARS'] = ''
if (row['COMM'] == None):
row['COMM'] = ''
print row['ID'], row['NAME'], row['JOB'], row['YEARS'], row[
'SALARY'], 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'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
'ADMRDEPT'], row2['LOCATION']
row2 = ifx_db.fetch_assoc(result2)
#__END__
#__LUW_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__ZOS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__SYSTEMI_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__IDS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
