def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_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 = ibm_db.fetch_assoc(result2)
result3 = ibm_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 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(
conn,
"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')"
)
row2 = ibm_db.fetch_assoc(result2)
while (row2):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
'ADMRDEPT'], row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
def run_test_150(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_150(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_162(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(
conn, "select * from emp_act order by projno")
row = ibm_db.fetch_both(result)
# will only retrieve 10 records
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 = ibm_db.exec_immediate(
conn, "select * from employee where employee.empno='" +
row['EMPNO'] + "'")
row2 = ibm_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 = ibm_db.fetch_both(result)
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print(row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2['ADMRDEPT'], row2['LOCATION'])
row2 = ibm_db.fetch_assoc(result2)
def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_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 = ibm_db.fetch_assoc(result2)
result3 = ibm_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 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def run_test_153(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from org")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_152(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from project")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_152(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from project")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_034(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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"],
)
ibm_db.close(conn)
def run_test_setgetOption(self):
if sys.platform == 'zos':
options = {}
else:
options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'}
conn = ibm_db.connect(config.database, config.user, config.password,
options)
# Get the server type
serverinfo = ibm_db.server_info(conn)
if conn:
if sys.platform != 'zos':
value = ibm_db.get_option(conn,
ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1)
print("Connection options:\nSQL_ATTR_INFO_PROGRAMNAME = ",
end="")
print(value)
else:
print("Connection options:\n", end="")
returncode = ibm_db.set_option(conn,
{ibm_db.SQL_ATTR_AUTOCOMMIT: 0}, 1)
value = ibm_db.get_option(conn, ibm_db.SQL_ATTR_AUTOCOMMIT, 1)
print("SQL_ATTR_AUTOCOMMIT = ", end="")
print(str(value) + "\n")
drop = "DROP TABLE TEMP_TEST"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table temp_test
create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO temp_test values (1, 'cat')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1")
if sys.platform != 'zos':
returnCode = ibm_db.set_option(
stmt, {ibm_db.SQL_ATTR_QUERY_TIMEOUT: 20}, 0)
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_QUERY_TIMEOUT,
0)
print("Statement options:\nSQL_ATTR_QUERY_TIMEOUT = ", end="")
print(str(value) + "\n")
ibm_db.execute(stmt)
if result:
ibm_db.free_result(stmt)
else:
print(ibm_db.stmt_errormsg())
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.tables(conn, None, 'T');
i = 0
row = ibm_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 = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_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 = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_115(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# row = ibm_db.fetch_object(stmt, 0)
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
else:
print("Connection failed.")
def run_test_115(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate(conn, drop)
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print row.DATA
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# row = ibm_db.fetch_object(stmt, 0)
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print row.DATA
else:
print "Connection failed."
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = "DROP TABLE tab_bigint"
result = ""
try:
result = ibm_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 = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_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 = ibm_db.fetch_both(stmt)
# test ibm_db.result for fetch of bigint
stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint")
ibm_db.execute(stmt1)
ibm_db.fetch_row(stmt1, 0)
if server.DBMS_NAME[0:3] != "IDS":
row1 = ibm_db.result(stmt1, "COL2")
else:
row1 = ibm_db.result(stmt1, "col2")
print row1
ibm_db.close(conn)
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = ibm_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 = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print("Number of inserted rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0])
print(data[1])
print(data[2])
print(data[3])
print(type(data[0]) is int)
print(type(data[1]) is int)
print(type(data[2]) is int)
data = ibm_db.fetch_both(stmt)
# test ibm_db.result for fetch of bigint
stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint")
ibm_db.execute(stmt1)
ibm_db.fetch_row(stmt1, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row1 = ibm_db.result(stmt1, 'COL2')
else:
row1 = ibm_db.result(stmt1, 'col2')
print(row1)
ibm_db.close(conn)
def run_test_159(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select name,job from staff")
i = 1
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = ibm_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 = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print("Number of inserted rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0])
print(data[1])
print(data[2])
print(data[3])
if sys.version_info >= (3, ):
print(type(data[0]) is int)
print(type(data[1]) is int)
print(type(data[2]) is int)
else:
print(type(data[0]) is long)
print(type(data[1]) is long)
print(type(data[2]) is long)
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
def run_test_159a(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select prstdate,prendate from project")
i = 1
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_159a(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select prstdate,prendate from project")
i = 1
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_153(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from org")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_151(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from sales")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_151(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from sales")
row = ibm_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 = ibm_db.fetch_assoc(result)
def run_test_018(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
if conn:
stmt = ibm_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
def run_test_261(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
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 = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt, (0, ))
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# pet = ibm_db.fetch_object(stmt)
# while (pet):
# print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED)
# pet = ibm_db.fetch_object(stmt)
class Pet:
pass
data = ibm_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 = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_261(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
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 = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt, (0,))
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# pet = ibm_db.fetch_object(stmt)
# while (pet):
# print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED)
# pet = ibm_db.fetch_object(stmt)
class Pet:
pass
data = ibm_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 = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_160(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from emp_act")
row = ibm_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 = ibm_db.fetch_both(result)
def run_test_161(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from emp_act order by projno desc")
row = ibm_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 = ibm_db.exec_immediate(conn,"select * from employee where employee.empno='" + row['EMPNO'] + "'")
row2 = ibm_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 = ibm_db.fetch_both(result)
def run_test_160(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from emp_act")
row = ibm_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 = ibm_db.fetch_both(result)
def run_test_034(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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'])
ibm_db.close(conn)
def run_test_trusted_context_pconnect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.pconnect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections)
for i in range(10):
tc_conn = ibm_db.pconnect(dsn, "", "")
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("Explicit Trusted Connection succeeded.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password, op)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if ('TABLE_NAME' in row):
value1 = row['TABLE_NAME']
if ('COLUMN_NAME' in row):
value2 = row['COLUMN_NAME']
if ('table_name' in row):
value3 = row['table_name']
if ('column_name' in row):
value4 = row['column_name']
print(value1)
print(value2)
print(value3)
print(value4)
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password, op)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_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 = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_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 = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_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_trusted_context_pconnect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print "Connection failed."
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.database, config.hostname, config.port, config.auth_user,
config.auth_pass)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.pconnect(dsn, "", "", options)
if tc_conn:
print "Trusted connection succeeded."
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print "User has been switched."
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print ibm_db.stmt_errormsg()
ibm_db.close(tc_conn)
else:
print "Trusted connection failed."
# Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections)
for i in xrange(10):
tc_conn = ibm_db.pconnect(dsn, "", "")
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print "Explicit Trusted Connection succeeded."
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print "Connection succeeded."
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2['ADMRDEPT'], row2['LOCATION']
row2 = ibm_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
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Create A Dictionary That Contains The Value Needed To Turn Row Prefetch Behavior On;
# This Enables Db2 To Determine The Number Of Rows That Are Returned By A Query (So The
# Entire Result Set Can Be Prefetched Into Memory, When Possible)
stmtOption = {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH: ibm_db.SQL_ROWCOUNT_PREFETCH_ON
}
# Attempt To Set The Statement Option Specified
print("Turning SQL_ATTR_ROWCOUNT_PREFETCH behavior ON ... ", end="")
try:
returnCode = ibm_db.set_option(preparedStmt, stmtOption, 0)
except Exception:
pass
# If The Statement Option Could Not Be Set, Display An Error Message And Exit
if returnCode is False:
print("\nERROR: Unable to set the statement option specified.\n")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Execute The Prepared SQL Statement Again
print("Executing the prepared SQL statement again ... ", end="")
else:
print("Done!\n")
# Create A Dictionary That Contains Values For The Connection Options That Can Only Be Set
# After A Database Connection Is Established
connectOptions = {
ibm_db.SQL_ATTR_INFO_ACCTSTR: 'RESANDERS@IBM',
ibm_db.SQL_ATTR_INFO_APPLNAME: 'ibm_db-set_option.py',
ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1',
ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'ibm_UDOO_X86'
}
# Attempt To Set The Connection Options Specified
print("Assigning values to four different connection options ... ", end="")
try:
returnCode = ibm_db.set_option(dbConnection, connectOptions, 1)
except Exception:
pass
# If The Connection Options Could Not Be Set, Display An Error Message And Exit
if returnCode is False:
print("\nERROR: Unable to set the connection options specified.\n")
if not dbConnection is None:
ibm_db.close(dbConnection)
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
def run_test_053(self):
print("Client attributes passed through conection string:")
options1 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn1 = ibm_db.connect(config.database, config.user, config.password, options1)
val = ibm_db.get_option(conn1, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
options2 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn2 = ibm_db.connect(config.database, config.user, config.password, options2)
val = ibm_db.get_option(conn2, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
options3 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn3 = ibm_db.connect(config.database, config.user, config.password, options3)
val = ibm_db.get_option(conn3, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
options4 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn4 = ibm_db.connect(config.database, config.user, config.password, options4)
val = ibm_db.get_option(conn4, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'kfb',
ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'kfbwork',
ibm_db.SQL_ATTR_INFO_ACCTSTR: 'kfbacc',
ibm_db.SQL_ATTR_INFO_APPLNAME: 'kfbapp'}
conn5 = ibm_db.connect(config.database, config.user, config.password, options5)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
print("Client attributes passed post-conection:")
options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn5 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn5, options5, 1)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
options6 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn6 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn6, options6, 1)
val = ibm_db.get_option(conn6, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
options7 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn7 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn7, options7, 1)
val = ibm_db.get_option(conn7, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
options8 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn8 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn8, options8, 1)
val = ibm_db.get_option(conn8, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
def run_test_265(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
cursor_option = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
try:
sql = 'drop table test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(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 = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = 'select id from test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_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 = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = "insert into test values(2, 'value', 'clob data', NULL)"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = "insert into test values(2, 'in varchar', 'data2', NULL)"
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
sql = 'select * from test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s, %s, %s\n" %(row[0], row[1], row[2], strip_bom(row[3])))
row = ibm_db.fetch_tuple(stmt)
sql = 'select id, name from test where id = ?'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt, (2,))
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s\n" %(row[0], row[1]))
row = ibm_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 = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
#ibm_db.num_fields(stmt)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
row = ibm_db.fetch_tuple(stmt)
while ( row ):
print("%s, %s, %s, %s\n" % (row[0], row[1], row[2], strip_bom(row[3])))
row = ibm_db.fetch_tuple(stmt)
try:
sql = 'drop table test'
stmt = ibm_db.prepare(conn, sql)
ibm_db.set_option(stmt, cursor_option, 0)
ibm_db.execute(stmt)
print("Number of affected rows: %d" % ibm_db.get_num_result(stmt))
except:
pass
ibm_db.close(conn)
def run_test_trusted_context_connect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
tc_all_options = {
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.database, config.hostname, config.port, config.auth_user,
config.auth_pass)
# Makeing normal connection and playing with it.
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_all_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "", tc_all_options)
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
get_tc_user = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if config.tc_user != get_tc_user:
print("But trusted user is not switched.")
ibm_db.close(tc_conn)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Making trusted connection and switching to fake user.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
ibm_db.set_option(
tc_conn, {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser",
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD:
"******"
}, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_options_reversed = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user
}
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options_reversed, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_user_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user
}
tc_pass_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
try:
ibm_db.set_option(tc_conn, tc_pass_options, 1)
except:
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing only user while switching when both user and password are required.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
ibm_db.set_option(tc_conn, tc_user_options, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Dropping the trusted context, in case it exists
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
# Dropping Role.
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
# Creating Role.
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
# Granting permissions to role.
try:
sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01"
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
# Creating trusted context
try:
sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION"
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Closing connection
ibm_db.close(conn)
else:
print("Connection failed.")
# Making trusted connection
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_user_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_insert)
except:
print(ibm_db.stmt_errormsg())
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20"
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_trusted_context_connect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
tc_all_options = {
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)
# Makeing normal connection and playing with it.
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_all_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "", tc_all_options)
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
get_tc_user = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if config.tc_user != get_tc_user:
print("But trusted user is not switched.")
ibm_db.close(tc_conn)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Making trusted connection and switching to fake user.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
ibm_db.set_option(tc_conn, {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******"}, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_options_reversed = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options_reversed, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_user_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}
tc_pass_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
try:
ibm_db.set_option(tc_conn, tc_pass_options, 1)
except:
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing only user while switching when both user and password are required.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
ibm_db.set_option(tc_conn, tc_user_options, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Dropping the trusted context, in case it exists
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
# Dropping Role.
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
# Creating Role.
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
# Granting permissions to role.
try:
sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01"
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
# Creating trusted context
try:
sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION"
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Closing connection
ibm_db.close(conn)
else:
print("Connection failed.")
# Making trusted connection
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_user_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_insert)
except:
print(ibm_db.stmt_errormsg())
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20"
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_154(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
try:
statement = 'DROP TABLE fetch_test'
result = ibm_db.exec_immediate(conn, statement)
except:
pass
server = ibm_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 = ibm_db.exec_immediate(conn, statement)
result = ibm_db.exec_immediate(conn, st0)
result = ibm_db.exec_immediate(conn, st1)
result = ibm_db.exec_immediate(conn, st2)
result = ibm_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_tuple(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_assoc(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_both(result)
ibm_db.close(conn)
def run_test_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_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 = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2['ADMRDEPT'], row2['LOCATION']
row2 = ibm_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
def run_test_154(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
try:
statement = 'DROP TABLE fetch_test'
result = ibm_db.exec_immediate(conn, statement)
except:
pass
server = ibm_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 = ibm_db.exec_immediate(conn, statement)
result = ibm_db.exec_immediate(conn, st0)
result = ibm_db.exec_immediate(conn, st1)
result = ibm_db.exec_immediate(conn, st2)
result = ibm_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_tuple(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_assoc(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_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 = ibm_db.fetch_both(result)
ibm_db.close(conn)
def run_test_053(self):
print("Client attributes passed through conection string:")
options1 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn1 = ibm_db.connect(config.database, config.user, config.password, options1)
val = ibm_db.get_option(conn1, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
options2 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn2 = ibm_db.connect(config.database, config.user, config.password, options2)
val = ibm_db.get_option(conn2, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
options3 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn3 = ibm_db.connect(config.database, config.user, config.password, options3)
val = ibm_db.get_option(conn3, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
options4 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn4 = ibm_db.connect(config.database, config.user, config.password, options4)
val = ibm_db.get_option(conn4, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'kfb',
ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'kfbwork',
ibm_db.SQL_ATTR_INFO_ACCTSTR: 'kfbacc',
ibm_db.SQL_ATTR_INFO_APPLNAME: 'kfbapp'}
conn5 = ibm_db.connect(config.database, config.user, config.password, options5)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
print("Client attributes passed post-conection:")
options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'}
conn5 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn5, options5, 1)
val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1)
print(val)
options6 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'}
conn6 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn6, options6, 1)
val = ibm_db.get_option(conn6, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1)
print(val)
options7 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'}
conn7 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn7, options7, 1)
val = ibm_db.get_option(conn7, ibm_db.SQL_ATTR_INFO_APPLNAME, 1)
print(val)
options8 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'}
conn8 = ibm_db.connect(config.database, config.user, config.password)
rc = ibm_db.set_option(conn8, options8, 1)
val = ibm_db.get_option(conn8, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1)
print(val)
