def run_test_120(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
name1 = ibm_db.field_name(stmt, 1)
name2 = ibm_db.field_name(stmt, 2)
name3 = ibm_db.field_name(stmt, 3)
name4 = ibm_db.field_name(stmt, 4)
name6 = ibm_db.field_name(stmt, 8)
name7 = ibm_db.field_name(stmt, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "id")
name8 = ibm_db.field_name(stmt, "WEIGHT")
else:
name5 = ibm_db.field_name(stmt, "ID")
name8 = ibm_db.field_name(stmt, "weight")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("string(%d) \"%s\"" % (len(name3), name3))
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("string(%d) \"%s\"" % (len(name7), name7))
print("%s" % name8)
else:
print("Connection failed.")
def run_test_231(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result) + 1):
print(
str(i) + ":" +
str(ibm_db.field_type(result, ibm_db.field_name(result, i))))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(
str(i) + ":" +
ibm_db.field_type(result2, ibm_db.field_name(result2, i)))
print("\n-----")
for i in range(0, 3):
print(
str(i) + ":" +
ibm_db.field_type(result3, ibm_db.field_name(result3, i)))
print("\n-----")
print("region:%s" % ibm_db.field_type(result, 'region'))
print("5:%s" % ibm_db.field_type(result2, 5))
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.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') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.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') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
def run_test_120(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
name1 = ibm_db.field_name(stmt, 1)
name2 = ibm_db.field_name(stmt, 2)
name3 = ibm_db.field_name(stmt, 3)
name4 = ibm_db.field_name(stmt, 4)
name6 = ibm_db.field_name(stmt, 8)
name7 = ibm_db.field_name(stmt, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "id")
name8 = ibm_db.field_name(stmt, "WEIGHT")
else:
name5 = ibm_db.field_name(stmt, "ID")
name8 = ibm_db.field_name(stmt, "weight")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("string(%d) \"%s\"" % (len(name3), name3))
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("string(%d) \"%s\"" % (len(name7), name7))
print("%s" % name8)
else:
print("Connection failed.")
def run_test_240(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + str(ibm_db.field_width(result, i))
print "\n-----"
for i in range(0, ibm_db.num_fields(result2)):
print str(i) + ":" + str(
ibm_db.field_width(result2, ibm_db.field_name(result2, i)))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + str(ibm_db.field_width(
result3, i)) + "," + str(ibm_db.field_display_size(result3, i))
print "\n-----"
print "region:%s" % ibm_db.field_type(result, 'region')
print "5:%s" % ibm_db.field_type(result2, 5)
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
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 (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print "%s, " % ibm_db.field_name(result, i)
print
print
i = 0
row = ibm_db.fetch_tuple(result)
while (row):
ibm_db.num_fields(result)
if (i < 4):
print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n"
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(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')
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
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 (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print("%s, " % ibm_db.field_name(result, i))
print()
print()
i = 0
row = ibm_db.fetch_tuple(result)
while ( row ):
ibm_db.num_fields(result)
if (i < 4):
print(", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n")
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(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')
def run_test_232(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
for i in range(0, ibm_db.num_fields(result) + 1):
field_name = ibm_db.field_name(result,i)
field_type = ibm_db.field_type(result, ibm_db.field_name(result,i))
print(str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i))))
print("-----")
t = ibm_db.field_type(result,99)
print(t)
t1 = ibm_db.field_type(result, "HELMUT")
print(t1)
def run_test_232(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
for i in range(0, ibm_db.num_fields(result) + 1):
field_name = ibm_db.field_name(result,i)
field_type = ibm_db.field_type(result, ibm_db.field_name(result,i))
print str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i)))
print "-----"
t = ibm_db.field_type(result,99)
print t
t1 = ibm_db.field_type(result, "HELMUT")
print t1
def run_test_065(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
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 (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, '%', "t3")
else:
result = ibm_db.tables(conn, None, '%', "T3")
columns = ibm_db.num_fields(result)
for i in range(0, columns):
print("%s, " % ibm_db.field_name(result, i))
print("\n\n")
row = ibm_db.fetch_tuple(result)
while (row):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "
row = ibm_db.fetch_tuple(result)
print(final)
ibm_db.free_result(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')
def run_test_065(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
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 (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, '%', "t3")
else:
result = ibm_db.tables(conn, None, '%', "T3")
columns = ibm_db.num_fields(result)
for i in range(0, columns):
print "%s, " % ibm_db.field_name(result, i)
print "\n\n"
row = ibm_db.fetch_tuple(result)
while ( row ):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , ";
row = ibm_db.fetch_tuple(result)
print final
ibm_db.free_result(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')
def main():
conn = None
stmt = None
if not conf.has_option('passwd'):
conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8'))
try:
conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'),
conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '')
stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8'))
try:
result = ibm_db.fetch_tuple(stmt)
except:
rows = ibm_db.num_rows(stmt)
if rows != -1:
print u'Обработано строк %d' % rows
else:
print u'Команда выполнена'
return
if result:
column_conv = []
head = u''
underline=u''
for i in xrange(len(result)):
if i != 0:
head += u'|'
underline += u'+'
name = ibm_db.field_name(stmt, i)
size = ibm_db.field_display_size(stmt, i)
if len(name) > size:
size = len(name)
if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size:
size = len(u'NULL')
type_field = ibm_db.field_type(stmt, i)
if type_field == 'float' or type_field == 'real' or type_field == 'decimal':
column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float})
elif type_field == 'int' or type_field == 'bigint':
column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int})
else:
column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert})
head += name.center(size)
underline += u'-' * size
print head
print underline
while( result ):
print conv(result, column_conv)
result = ibm_db.fetch_tuple(stmt)
else:
print u'Результата не возвращено'
except Exception as e:
print >> sys.stderr, e
sys.exit(-1)
finally:
if stmt:
ibm_db.free_result(stmt)
if conn:
ibm_db.close(conn)
def getColumns(stmt):
columns = []
types = []
colcount = 0
try:
colname = ibm_db.field_name(stmt, colcount)
coltype = ibm_db.field_type(stmt, colcount)
while (colname != False):
columns.append(colname)
types.append(coltype)
colcount += 1
colname = ibm_db.field_name(stmt, colcount)
coltype = ibm_db.field_type(stmt, colcount)
return columns, types
except Exception as err:
db2_error()
return None
def run_test_125(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
result = ibm_db.exec_immediate(conn, "SELECT * FROM sales")
result2 = ibm_db.exec_immediate(conn, "SELECT * FROM staff")
for i in range(0, ibm_db.num_fields(result)):
print("%d:%s" % (i, ibm_db.field_name(result,i)))
print("-----")
for i in range(0, ibm_db.num_fields(result2)):
print("%d:%s" % (i, ibm_db.field_name(result2,i)))
print("-----")
if (server.DBMS_NAME[0:3] == 'IDS'):
print("Region:%s" % ibm_db.field_name(result, 'region'))
else:
print("Region:%s" % ibm_db.field_name(result, 'REGION'))
print("5:%s" % ibm_db.field_name(result2, 5))
def run_test_125(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
result = ibm_db.exec_immediate(conn, "SELECT * FROM sales")
result2 = ibm_db.exec_immediate(conn, "SELECT * FROM staff")
for i in range(0, ibm_db.num_fields(result)):
print("%d:%s" % (i, ibm_db.field_name(result, i)))
print("-----")
for i in range(0, ibm_db.num_fields(result2)):
print("%d:%s" % (i, ibm_db.field_name(result2, i)))
print("-----")
if (server.DBMS_NAME[0:3] == 'IDS'):
print("Region:%s" % ibm_db.field_name(result, 'region'))
else:
print("Region:%s" % ibm_db.field_name(result, 'REGION'))
print("5:%s" % ibm_db.field_name(result2, 5))
def run_test_241(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print(str(ibm_db.field_width(result,i)))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))))
def run_test_241(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print(str(ibm_db.field_width(result,i)))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))))
def run_test_231(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result) + 1):
print(str(i) + ":" + str(ibm_db.field_type(result,ibm_db.field_name(result,i))))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(str(i) + ":" + ibm_db.field_type(result2,ibm_db.field_name(result2,i)))
print("\n-----")
for i in range(0, 3):
print(str(i) + ":" + ibm_db.field_type(result3,ibm_db.field_name(result3,i)))
print("\n-----")
print("region:%s" % ibm_db.field_type(result,'region'))
print("5:%s" % ibm_db.field_type(result2,5))
def run_test_240(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + str(ibm_db.field_width(result,i))
print "\n-----"
for i in range(0, ibm_db.num_fields(result2)):
print str(i) + ":" + str(ibm_db.field_width(result2,ibm_db.field_name(result2,i)))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + str(ibm_db.field_width(result3,i)) + "," + str(ibm_db.field_display_size(result3,i))
print "\n-----"
print "region:%s" % ibm_db.field_type(result,'region')
print "5:%s" % ibm_db.field_type(result2,5)
def run_test_122(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table ftest"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table ftest ( \"TEST\" integer, \"test\" integer, \"Test\" integer )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO ftest values (1,2,3)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest")
num1 = ibm_db.field_name(stmt, 0)
num2 = ibm_db.field_name(stmt, 1)
num3 = ibm_db.field_name(stmt, 2)
num4 = ibm_db.field_name(stmt, "TEST")
num5 = ibm_db.field_name(stmt, 'test')
num6 = ibm_db.field_name(stmt, 'Test')
print("string(%d) \"%s\"" % (len(num1), num1))
print("string(%d) \"%s\"" % (len(num2), num2))
print("string(%d) \"%s\"" % (len(num3), num3))
print("string(%d) \"%s\"" % (len(num4), num4))
print("string(%d) \"%s\"" % (len(num5), num5))
print("string(%d) \"%s\"" % (len(num6), num6))
else:
print("Connection failed.")
def run_test_122(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table ftest"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table ftest ( \"TEST\" integer, \"test\" integer, \"Test\" integer )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO ftest values (1,2,3)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest")
num1 = ibm_db.field_name(stmt, 0)
num2 = ibm_db.field_name(stmt, 1)
num3 = ibm_db.field_name(stmt, 2)
num4 = ibm_db.field_name(stmt, "TEST")
num5 = ibm_db.field_name(stmt, 'test')
num6 = ibm_db.field_name(stmt, 'Test')
print "string(%d) \"%s\"" % (len(num1), num1)
print "string(%d) \"%s\"" % (len(num2), num2)
print "string(%d) \"%s\"" % (len(num3), num3)
print "string(%d) \"%s\"" % (len(num4), num4)
print "string(%d) \"%s\"" % (len(num5), num5)
print "string(%d) \"%s\"" % (len(num6), num6)
else:
print "Connection failed."
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Result set information:\n")
print("COLUMN NAME MAXIMUM VALUE SIZE (IN BYTES)")
print("_____________ _____________________________")
# As Long As There Is Column Information, ...
for loopCounter in range(0, numColumns):
# Get The Name Of The Current Column
colName = ibm_db.field_name(resultSet, loopCounter)
# Get The Size Of The Data Allowed In The Current Column
colWidth = ibm_db.field_width(resultSet, loopCounter)
# Format And Display The Data Retrieved
if (not colName is False) and (not colWidth is False):
print("{:<13} {:>29}".format(colName, colWidth))
# Add A Blank Line To The End Of The Report
print()
# Close The Database Connection That Was Opened Earlier
conn.closeConnection()
# Return Control To The Operating System
def run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("%s" % name3)
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("%s" % name7)
print("string(%d) \"%s\"" % (len(name8), name8))
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print "string(%d) \"%s\"" % (len(name1), name1)
print "string(%d) \"%s\"" % (len(name2), name2)
print "%s" % name3
print "%s" % name4
print "string(%d) \"%s\"" % (len(name5), name5)
print "%s" % name6
print "%s" % name7
print "string(%d) \"%s\"" % (len(name8), name8)
ibm_db.rollback(conn)
else:
print "Connection failed."
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
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 (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print "%s, " % ibm_db.field_name(result, i)
print
print
i = 0
row = ibm_db.fetch_tuple(result)
while ( row ):
ibm_db.num_fields(result)
if (i < 4):
print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n"
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(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')
#__END__
#__LUW_EXPECTED__
#TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
#
#, T, T1, TABLE, ,
#, T, T2, TABLE, ,
#, T, T3, TABLE, ,
#, T, T4, TABLE, ,
#__ZOS_EXPECTED__
#TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
#
#, T, T1, TABLE, ,
#, T, T2, TABLE, ,
#, T, T3, TABLE, ,
#, T, T4, TABLE, ,
#__SYSTEMI_EXPECTED__
#TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
#
#, T, T1, TABLE, ,
#, T, T2, TABLE, ,
#, T, T3, TABLE, ,
#, T, T4, TABLE, ,
#__IDS_EXPECTED__
#table_cat, table_schem, table_name, table_type, remarks,
#
#, t, t1, TABLE%s, ,
#, t, t2, TABLE%s, ,
#, t, t3, TABLE%s, ,
#, t, t4, TABLE%s, ,
def schedule_tasks(conn, database, in_coll_lvl, env):
print("Scheduling tasks ...")
# enable Admin Task Scheduler
print(" Setting Admin Task Scheduler registry variable ...")
subprocess.check_call("db2set DB2_ATS_ENABLE=YES", shell=True)
# load task_details.json file
print(" Reading from task_details.json file ...")
with open('task_details.json') as json_file:
tasks = json.load(json_file)
# insert tasks into IBMHIST.TAB_TASKS and schedule with Admin Task Scheduler
for task in tasks:
# get paramaters
collection_name = task['collection_name']
collection_freq = task['collection_freq']
collection_class = task['collection_class']
collection_command = task['collection_command']
coll_lvl = int(task['collection_level'])
collection_condition = task['collection_condition']
# check collection_level constraint
if in_coll_lvl and coll_lvl > in_coll_lvl:
continue
# check collection_condition constraint
skip_task = False
for condition, fulfilled in env.items():
if condition in collection_condition and fulfilled is False:
skip_task = True
if skip_task:
continue
print(" Scheduling task: %s" % (collection_name))
# check command and generate header
if collection_class == "SQL":
# try sql command
try:
stmt = ibm_db.exec_immediate(conn, collection_command)
except Exception as e:
print(" Skipping due to error: %s" % e)
continue
# generate header for sql command as comma seperated field names
header = [
ibm_db.field_name(stmt, i)
for i in range(ibm_db.num_fields(stmt))
]
header = ",".join(header) + '\n'
elif collection_class == "SYS":
# try sys command
try:
devnull = open(os.devnull, 'w')
subprocess.check_call(collection_command,
shell=True,
stdout=devnull,
stderr=devnull)
except Exception as e:
print(" Skipping due to error: %s" % e)
continue
# generate header for sys command as collection name
header = collection_name + '\n'
# insert task into IBMHIST.TAB_TASKS
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_TASKS VALUES ('%s', '%s', '%s', '%s') " %
(collection_name, collection_class, collection_command, header))
# schedule IBMHIST.PROC_COLLECT (database, collection_name) task
ibm_db.exec_immediate(
conn,
"CALL SYSPROC.ADMIN_TASK_ADD ( '%s', NULL, NULL, NULL, '%s', 'IBMHIST', 'PROC_COLLECT', 'values (''%s'', ''%s'')', NULL, 'IBMHIST Collection' )"
% (collection_name, collection_freq, database, collection_name))
# schedule archive task
print(" Scheduling task: ARCHIVE")
# schedule IBMHIST.PROC_ARCHIVE (database) task
ibm_db.exec_immediate(
conn,
"CALL SYSPROC.ADMIN_TASK_ADD ( 'ARCHIVE', NULL, NULL, NULL, '*/10 * * * *', 'IBMHIST', 'PROC_ARCHIVE', 'values (''%s'')', NULL, 'IBMHIST Archival' )"
% (database))
# collection will begin in approximately 5 minutes
print(" Collection will begin in approximately 5 minutes ...")
