def run_test_180(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = ibm_db.exec_immediate(
conn,
"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols, ", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
try:
result = ibm_db.exec_immediate(
conn, "delete from t_string where a=123")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols, ", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
else:
print("no connection")
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_100(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals ORDER BY breed")
fields1 = ibm_db.num_fields(stmt)
print("int(%d)" % fields1)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ibm_db.num_fields(stmt)
print("int(%d)" % fields2)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ibm_db.num_fields(stmt)
print("int(%d)" % fields3)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ibm_db.num_fields(stmt)
print("int(%d)" % fields4)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ibm_db.num_fields(stmt)
print("int(%d)" % fields5)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_180(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols,", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
try:
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols,", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
else:
print("no connection")
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_015(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(
conn, "insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
# NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
print("col:", cols)
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
result = ibm_db.exec_immediate(conn,
"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print("col:", cols)
rows = ibm_db.num_rows(result)
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_100(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals ORDER BY breed")
fields1 = ibm_db.num_fields(stmt)
print("int(%d)" % fields1)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ibm_db.num_fields(stmt)
print("int(%d)" % fields2)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ibm_db.num_fields(stmt)
print("int(%d)" % fields3)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ibm_db.num_fields(stmt)
print("int(%d)" % fields4)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ibm_db.num_fields(stmt)
print("int(%d)" % fields5)
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')
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_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_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_102(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print ibm_db.conn_errormsg()
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2")):
result = ibm_db.exec_immediate(conn, "VALUES(1)")
#throw :unsupported unless result
if (not result):
raise Exception('Unsupported')
print ibm_db.num_fields(result)
else:
print '1'
ibm_db.close(conn)
def run_test_checkBooleanType(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info( conn )
if( not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn,"DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(conn, "CREATE TABLE bool_test(col1 INTEGER, col2 BOOLEAN, col3 BOOLEAN)")
except Exception as e:
print("Error : {}\n".format(str(e)))
exit(-1)
try:
select_sql = 'SELECT * FROM bool_test'
stmt = ibm_db.exec_immediate(conn, select_sql)
for i in range(0,ibm_db.num_fields(stmt)):
print(str(ibm_db.field_type(stmt,i)))
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
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_101(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
else:
print("no connection");
def run_test_101(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
else:
print("no connection");
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 run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
if (server.DBMS_NAME.startswith('DB2/')):
d_val = datetime.date(2019, 10, 16)
ts_val = datetime.datetime(2019, 10, 16, 23, 0, 0, 0)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values ('24:00:00', ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result, i))
statement = "SELECT * FROM tab_datetime"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt,
(t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result, i))
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
row3 = ibm_db.result(stmt, 3)
print(row0)
print(row1)
print(row2)
print(row3)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_210(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from staff")
cols = ibm_db.num_fields(result)
for i in range(0, cols):
size = ibm_db.field_display_size(result, i)
print("col:%d and size: %d" % (i, size))
ibm_db.close(conn)
def run_test_210(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from staff")
cols = ibm_db.num_fields(result)
for i in range(0, cols):
size = ibm_db.field_display_size(result,i)
print "col:%d and size: %d" % (i, size)
ibm_db.close(conn)
def run_test_016(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
print ("affected row:", rows)
else:
print (ibm_db.stmt_errormsg())
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
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_211(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
i = 1
while (i <= ibm_db.num_fields(result)):
#printf("%d size %d\n",i, ibm_db.field_display_size(result,i) || 0)
print "%d size %d" % (i, ibm_db.field_display_size(result, i) or 0)
i += 1
ibm_db.close(conn)
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_211(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
i = 1
while (i <= ibm_db.num_fields(result)):
#printf("%d size %d\n",i, ibm_db.field_display_size(result,i) || 0)
print("%d size %d" % (i, ibm_db.field_display_size(result,i) or 0))
i += 1
ibm_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result,i))
statement = "SELECT * FROM tab_datetime"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_015(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
# NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
print "col:", cols
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print "col:", cols
rows = ibm_db.num_rows(result)
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_230(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,i))
print "\n-----"
for i in range(0, ibm_db.num_fields(result2)):
print str(i) + ":" + str(ibm_db.field_type(result2,i))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + str(ibm_db.field_type(result3,i))
print "\n-----"
print "region:%s" % str(ibm_db.field_type(result,'region'))
print "5:%s" % str(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_016(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(
conn, "insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print "col:", cols
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,
"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print "col:", cols
rows = ibm_db.num_rows(result)
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_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_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10:42:34', '1981-07-08', '1981-07-08 10:42:34')"
result = ibm_db.exec_immediate(conn, statement)
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10.42.34', '1981-07-08', '1981-07-08-10.42.34')"
result = ibm_db.exec_immediate(conn, statement)
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + ibm_db.field_type(result,i)
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print row0
print row1
print row2
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_103(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from org, project order by project.projname")
cols = ibm_db.num_fields(result)
j = 1
row = ibm_db.fetch_tuple(result)
while ( row ):
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if (j > 10):
break
row = ibm_db.fetch_tuple(result)
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
def execute(self, conn):
try:
stmt = ibm_db.exec_immediate(conn, self.sql)
if ibm_db.num_fields(stmt) > 0:
result = []
dictionary = ibm_db.fetch_assoc(stmt)
print("here")
while dictionary != False:
result.append(dictionary)
dictionary = ibm_db.fetch_assoc(stmt)
return {'result': result, 'message': '1 Query executed.'}, 200
else:
return {'result': [], 'message': str(ibm_db.num_rows(stmt)) + 'rows affected.'}, 200
except Exception as e:
app.logger.error(e)
msg = ibm_db.stmt_errormsg()
if not msg:
msg = str(e)
return {'result': [], 'message': 'Query failed: ' + msg}, 500
def run_test_103(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from org, project order by project.projname")
cols = ibm_db.num_fields(result)
j = 1
row = ibm_db.fetch_tuple(result)
while row:
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if j > 10:
break
row = ibm_db.fetch_tuple(result)
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
def main(args):
global conn
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
if globals().get("conn") is None:
print("creating connection")
conn = ibm_db.connect(ssldsn, "", "")
else:
print("reusing connection")
drop = "DROP TABLE CUSTOMER_FEEDBACK"
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
# Check table creation
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i), ":", ibm_db.field_type(result, i))
ts_val = datetime.datetime.today()
print("the time is", ts_val)
# Try to insert sample test row
statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val))
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
print(ibm_db.result(stmt, 0), ibm_db.result(
stmt, 1), ibm_db.result(stmt, 2))
if not result:
return {"err": "error :" + statement}
return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
def main(args):
global conn
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
if globals().get("conn") is None:
print("creating connection")
conn = ibm_db.connect(ssldsn, "", "")
else:
print("reusing connection")
drop = "DROP TABLE CUSTOMER_FEEDBACK"
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
# Check table creation
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i), ":", ibm_db.field_type(result, i))
ts_val = datetime.datetime.today()
print("the time is", ts_val)
# Try to insert sample test row
statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val))
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
print(ibm_db.result(stmt, 0), ibm_db.result(stmt, 1),
ibm_db.result(stmt, 2))
if not result:
return {"err": "error :" + statement}
return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
def run_test_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + ibm_db.field_type(result,i)
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
row3 = ibm_db.result(stmt, 3)
print row0
print row1
print row2
print row3
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__ZOS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__SYSTEMI_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__IDS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
pass
# If The SQL Statement Could Not Be Executed, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to execute the SQL statement specified.")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Find Out How Many Columns Were Returned In The Result Set Produced By The Query Just Executed
print("Examining the columns returned in the result set produced ... ", end="")
try:
numColumns = ibm_db.num_fields(resultSet)
except Exception:
pass
# If Information About The Number Columns Returned Could Not Be Obtained, Display An Error
# Message And Exit
if numColumns is False:
print(
"\nERROR: Unable to obtain information about the result set produced.")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
def sql(sqlstmts=None, **local_ns):
# Before we event get started, check to see if you have connected yet. Without a connection we
# can't do anything. You may have a connection request in the code, so if that is true, we run those,
# otherwise we connect immediately
# If your statement is not a connect, and you haven't connected, we need to do it for you
global _settings
global _hdbc, _hdbi, _connected, _sqlstate, _sqlerror, _sqlcode
flag_output = False
_sqlstate = "0"
_sqlerror = ""
_sqlcode = 0
if (sqlstmts == None): return
setOptions(local_ns)
sqlstmts = sqlstmts.strip()
if (len(sqlstmts) == 0): return # Nothing to do here
sqlType, remainder = sqlParser(
sqlstmts, local_ns) # What type of command do you have?
if (sqlType == "CONNECT"): # A connect request
parseConnect(sqlstmts, local_ns)
return
elif (sqlType == 'COMMIT' or sqlType == 'ROLLBACK'
or sqlType == 'AUTOCOMMIT'):
parseCommit(remainder)
return
elif (sqlType == "PREPARE"):
pstmt = parsePExec(_hdbc, remainder)
return (pstmt)
elif (sqlType == "EXECUTE"):
result = parsePExec(_hdbc, remainder)
return (result)
elif (sqlType == "CALL"):
result = parseCall(_hdbc, remainder, local_ns)
return (result)
else:
pass
if (_connected == False):
if (db2_doConnect() == False):
errormsg(
'A CONNECT statement must be issued before issuing SQL statements.'
)
return
runSQL = re.sub('.*?--.*$', "", sqlstmts, flags=re.M)
remainder = runSQL.replace("\n", " ")
sqlLines = splitSQL(remainder, _settings["delim"])
flag_cell = True
# For each line figure out if you run it as a command (db2) or select (sql)
for sqlin in sqlLines: # Run each command
sqlType, sql = sqlParser(sqlin, local_ns) # Parse the SQL
if (sql.strip() == ""): continue
try: # See if we have an answer set
stmt = ibm_db.prepare(_hdbc, sql)
if (ibm_db.num_fields(stmt) == 0
): # No, so we just execute the code
result = ibm_db.execute(stmt) # Run it
if (result == False): # Error executing the code
db2_error()
continue
rowcount = ibm_db.num_rows(stmt)
if (rowcount == 0):
errormsg("No rows found", 100, "00100")
continue # Continue running
elif (_settings["format"] == "array"
or _settings["format"] == "json"): # raw, json, format json
row_count = 0
resultSet = []
try:
result = ibm_db.execute(stmt) # Run it
if (result == False): # Error executing the code
db2_error()
return
return (fetchResults(stmt))
except Exception as err:
db2_error()
return
else:
try:
df = pandas.read_sql(sql, _hdbi)
except Exception as err:
db2_error()
return
if (len(df) == 0):
errormsg("No rows found", 100, "00100")
continue
flag_output = True
return df # print(df.to_string())
except:
db2_error()
continue # return
def parsePExec(hdbc, inSQL):
import ibm_db
global _stmt, _stmtID, _stmtSQL, _sqlcode
cParms = inSQL.split()
parmCount = len(cParms)
if (parmCount == 0):
return (None) # Nothing to do but this shouldn't happen
keyword = cParms[0].upper() # Upper case the keyword
if (keyword == "PREPARE"): # Prepare the following SQL
uSQL = inSQL.upper()
found = uSQL.find("PREPARE")
sql = inSQL[found + 7:].strip()
try:
pattern = "\?\*[0-9]+"
findparm = re.search(pattern, sql)
while findparm != None:
found = findparm.group(0)
count = int(found[2:])
markers = ('?,' * count)[:-1]
sql = sql.replace(found, markers)
findparm = re.search(pattern, sql)
stmt = ibm_db.prepare(hdbc, sql) # Check error code here
if (stmt == False):
db2_error()
return (False)
stmttext = str(stmt).strip()
stmtID = stmttext[33:48].strip()
if (stmtID in _stmtID) == False:
_stmt.append(stmt) # Prepare and return STMT to caller
_stmtID.append(stmtID)
else:
stmtIX = _stmtID.index(stmtID)
_stmt[stmtIX] = stmt
return (stmtID)
except Exception as err:
db2_error()
return (False)
if (keyword == "EXECUTE"): # Execute the prepare statement
if (parmCount < 2): return (False) # No stmtID available
stmtID = cParms[1].strip()
if (stmtID in _stmtID) == False:
errormsg("Prepared statement not found or invalid.")
return (False)
stmtIX = _stmtID.index(stmtID)
stmt = _stmt[stmtIX]
try:
if (parmCount == 2): # Only the statement handle available
result = ibm_db.execute(stmt) # Run it
elif (parmCount == 3): # Not quite enough arguments
errormsg(
"Missing or invalid USING clause on EXECUTE statement.")
_sqlcode = -99999
return (False)
else:
using = cParms[2].upper()
if (using != "USING"): # Bad syntax again
errormsg("Missing USING clause on EXECUTE statement.")
_sqlcode = -99999
return (False)
uSQL = inSQL.upper()
found = uSQL.find("USING")
parmString = inSQL[found + 5:].strip()
parmset = splitargs(parmString)
if (len(parmset) == 0):
errormsg("Missing parameters after the USING clause.")
_sqlcode = -99999
return (False)
parms = []
parm_count = 0
CONSTANT = 0
VARIABLE = 1
const = [0]
const_cnt = 0
for v in parmset:
parm_count = parm_count + 1
if (v[1] == True or v[2]
== True): # v[1] true if string, v[2] true if num
parm_type = CONSTANT
const_cnt = const_cnt + 1
if (v[2] == True):
if (isinstance(v[0],
int) == True): # Integer value
sql_type = ibm_db.SQL_INTEGER
elif (isinstance(v[0],
float) == True): # Float value
sql_type = ibm_db.SQL_DOUBLE
else:
sql_type = ibm_db.SQL_INTEGER
else:
sql_type = ibm_db.SQL_CHAR
const.append(v[0])
else:
parm_type = VARIABLE
# See if the variable has a type associated with it varname@type
varset = v[0].split("@")
parm_name = varset[0]
parm_datatype = "char"
# Does the variable exist?
if (parm_name not in globals()):
errormsg("SQL Execute parameter " + parm_name +
" not found")
_sqlcode = -99999
return (False)
if (len(varset) > 1): # Type provided
parm_datatype = varset[1]
if (parm_datatype == "dec"
or parm_datatype == "decimal"):
sql_type = ibm_db.SQL_DOUBLE
elif (parm_datatype == "bin"
or parm_datatype == "binary"):
sql_type = ibm_db.SQL_BINARY
elif (parm_datatype == "int"
or parm_datatype == "integer"):
sql_type = ibm_db.SQL_INTEGER
else:
sql_type = ibm_db.SQL_CHAR
try:
if (parm_type == VARIABLE):
result = ibm_db.bind_param(stmt, parm_count,
globals()[parm_name],
ibm_db.SQL_PARAM_INPUT,
sql_type)
else:
result = ibm_db.bind_param(stmt, parm_count,
const[const_cnt],
ibm_db.SQL_PARAM_INPUT,
sql_type)
except:
result = False
if (result == False):
errormsg("SQL Bind on variable " + parm_name +
" failed.")
_sqlcode = -99999
return (False)
result = ibm_db.execute(stmt) # ,tuple(parms))
if (result == False):
errormsg("SQL Execute failed.")
return (False)
if (ibm_db.num_fields(stmt) == 0):
return (True) # Command successfully completed
return (fetchResults(stmt))
except Exception as err:
db2_error()
return (False)
return (False)
return (False)
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, 0)
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
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], 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], row[3])
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
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, 0)
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
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], 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], row[3])
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
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 ...")
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, ,
