def run_test_038(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
ibm_db.execute(result2)
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3), \
ibm_db.result(result2, 5)))
row2 = ibm_db.fetch_row(result2)
row = ibm_db.fetch_row(result)
def run_test_03a(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 id, breed, name, weight FROM animals WHERE id = 0")
while ( ibm_db.fetch_row(stmt) ):
breed = ibm_db.result(stmt, 1)
print ("string(%d) \"%s\"" % (len(breed), breed))
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print ("string(%d) \"%s\"" % (len(name), name))
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "passport")
else:
name = ibm_db.result(stmt, "PASSPORT")
print (name)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_03a(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 id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
breed = ibm_db.result(stmt, 1)
print("string(%d) \"%s\"" % (len(breed), breed))
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print("string(%d) \"%s\"" % (len(name), name))
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "passport")
else:
name = ibm_db.result(stmt, "PASSPORT")
print(name)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_039(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.prepare(
conn, "SELECT * FROM animals",
{ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.prepare(conn, "SELECT * FROM animals")
ibm_db.execute(result)
row = ibm_db.fetch_row(result)
while (row):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM animals", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM animals")
ibm_db.execute(result2)
while (ibm_db.fetch_row(result2)):
print("%s : %s : %s : %s" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3)))
row = ibm_db.fetch_row(result)
def run_test_038(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
ibm_db.execute(result2)
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print "%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3), \
ibm_db.result(result2, 5))
row2 = ibm_db.fetch_row(result2)
row = ibm_db.fetch_row(result)
def callback_worker(call):
if call.data=='yes':
try:
i=0
conn = ibm_db.connect(dsn, "", "")
print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
bot.send_message(author_id,"Рассылаю...")
#Select all
selectQuery = "select * from "+TABLENAME
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
while ibm_db.fetch_row(selectStmt) != False:
print ("Sent to: ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME"))
bot.send_message(ibm_db.result(selectStmt, 0),mess)
i+=1
bot.send_message(author_id,"Пользователей, получивших ваше оповещение: {} ".format(i))
log.info('Разослано пользователям: '+str(i))
except:
print ("Unable to connect: ", ibm_db.conn_errormsg())
ibm_db.close(conn)
print ("Connection closed")
else:
bot.send_message(author_id,"Хорошо, не буду")
def run_test_114(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate(conn, drop)
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
ibm_db.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral")
ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
result = ibm_db.fetch_row(stmt)
else:
print("Connection failed.")
def run_test_114(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
ibm_db.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral")
ibm_db.execute(stmt)
result = ibm_db.fetch_row( stmt )
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print row0
print row1
result = ibm_db.fetch_row( stmt )
else:
print "Connection failed."
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_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_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 getResByRow(conn, sql):
''' Call fetch_row '''
n = 0
try:
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_row(stmt)
while res != False:
print 'Result from :', db2.result(stmt, 0)
n += db2.result(stmt, 0)
res = db2.fetch_row(stmt)
except:
print "Transaction couldn't be completed:" , db2.stmt_errormsg()
else:
return n
def run_test_032(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 id, breed, name, weight FROM animals WHERE id = 6")
while (ibm_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ibm_db.result(stmt, "id")
breed = ibm_db.result(stmt, "breed")
name = ibm_db.result(stmt, "name")
weight = ibm_db.result(stmt, "weight")
else:
id = ibm_db.result(stmt, "ID")
breed = ibm_db.result(stmt, "BREED")
name = ibm_db.result(stmt, "NAME")
weight = ibm_db.result(stmt, "WEIGHT")
print("int(%d)" % id)
print("string(%d) \"%s\"" % (len(breed), breed))
print("string(%d) \"%s\"" % (len(name), name))
print("string(%d) \"%s\"" % (len(str(weight)), weight))
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_032(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 id, breed, name, weight FROM animals WHERE id = 6")
while (ibm_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ibm_db.result(stmt, "id")
breed = ibm_db.result(stmt, "breed")
name = ibm_db.result(stmt, "name")
weight = ibm_db.result(stmt, "weight")
else:
id = ibm_db.result(stmt, "ID")
breed = ibm_db.result(stmt, "BREED")
name = ibm_db.result(stmt, "NAME")
weight = ibm_db.result(stmt, "WEIGHT")
print "int(%d)" % id
print "string(%d) \"%s\"" % (len(breed), breed)
print "string(%d) \"%s\"" % (len(name), name)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ibm_db.close(conn)
else:
print "Connection failed."
def main(args):
#Connect to the DB2 database
conn = ibm_db.connect("DATABASE=TEST;HOSTNAME=172.17.0.1;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=db2inst1-pwd;", "", "")
cmd = "DELETE FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
#output of the above can succeed or fail. Either is fine.
# Insert into TestTable
cmd = "INSERT INTO TESTTABLE (NAME, AGE, LOCATION) VALUES ('Angela', 27, 'Texas')"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
# Select from TestTable (the row just inserted)
cmd = "SELECT * FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
else:
ibm_db.fetch_both(result,0)
value = ibm_db.result(result,"NAME")
# Make sure the row was correctly inserted
if value != 'Angela' :
return {"err":"Expected name 'Angela', but instead found: "+ value}
# Delete the row from TestTable
cmd = "DELETE FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
# If no detected errors occurred so far; return Success status
return {"message":"Tested db2 create, select, and delete of a table row."}
def unschedule_tasks(conn):
print("Unscheduling tasks ...")
# unschedule tasks from Admin Task Scheduler
stmt = ibm_db.exec_immediate(
conn,
"SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SYSTOOLS' AND TABNAME = 'ADMIN_TASK_LIST'"
)
if ibm_db.fetch_assoc(stmt):
# get all tasks belonging to schema IBMHIST
stmt = ibm_db.exec_immediate(
conn,
"SELECT NAME FROM SYSTOOLS.ADMIN_TASK_LIST WHERE PROCEDURE_SCHEMA = 'IBMHIST'"
)
while ibm_db.fetch_row(stmt):
# get name
collection_name = ibm_db.result(stmt, "NAME")
# unschedule task
ibm_db.exec_immediate(
conn, "CALL SYSPROC.ADMIN_TASK_REMOVE( '%s', NULL )" %
(collection_name))
# delete tasks from IBMHIST.TAB_TASKS
stmt = ibm_db.exec_immediate(
conn,
"SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'IBMHIST' AND TABNAME = 'TAB_TASKS'"
)
if ibm_db.fetch_assoc(stmt):
ibm_db.exec_immediate(conn, "DELETE FROM IBMHIST.TAB_TASKS")
def main(args):
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
conn = ibm_db.connect(ssldsn, "", "")
if conn:
print("Connection succeeded.")
else:
print("Connection failed.")
return {"error": "Error connecting to db2"}
# Select from TestTable (the row just inserted).
#
# Due to the db2 lite plan we use, the schema name is fixed to be
# the same as the username. We cut the value of the UID field
# in the ssldsn to get it and insert it in the select statement.
# The ssldsn looks like this:
# ssldsn="DATABASE=BLUDB;HOSTNAME=dashdb-xxxx.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=yyyyyyy;PWD=<hidden>;"
#
ssldsndict = dict(x.split("=") for x in ssldsn.rstrip(";").split(";"))
print("user={}".format(ssldsndict["UID"]))
cmd = "SELECT HISP_DESC FROM {}.HISPANIC_ORIGIN WHERE HISP_CODE='03'".format(
ssldsndict["UID"])
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"error": "error :" + cmd}
else:
ibm_db.fetch_both(result, 0)
value = ibm_db.result(result, "HISP_DESC")
ibm_db.close(conn)
return {"HISP_DESC": value}
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_031(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
id = ibm_db.result(stmt, 0)
print("int(%d)" % id)
breed = ibm_db.result(stmt, 1)
print("string(%d) \"%s\"" % (len(breed), breed))
name = ibm_db.result(stmt, 2)
print("string(%d) \"%s\"" % (len(name), name))
weight = ibm_db.result(stmt, 3)
print("string(%d) \"%s\"" % (len(str(weight)), weight))
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_031(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
id = ibm_db.result(stmt, 0)
print "int(%d)" % id
breed = ibm_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
name = ibm_db.result(stmt, 2)
print "string(%d) \"%s\"" % (len(name), name)
weight = ibm_db.result(stmt, 3)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_033(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 id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
weight = ibm_db.result(stmt, 3)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
breed = ibm_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print "string(%d) \"%s\"" % (len(name), name)
ibm_db.close(conn)
else:
print "Connection failed."
def items():
page = ''
if db2conn:
stmt = ibm_db.exec_immediate(db2conn, "SELECT * FROM " + db2cred['username'] + ".ITEMS")
# Look through each row, while there is a row
while ibm_db.fetch_row(stmt) != False:
# Get the rows Item column value
page += ibm_db.result(stmt, "ITEM") + '\n'
return page
def run_test_033(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 id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
weight = ibm_db.result(stmt, 3)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
breed = ibm_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print "string(%d) \"%s\"" % (len(name), name)
ibm_db.close(conn)
else:
print "Connection failed."
def connectBD2():
# 连接数据库
# conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '')
conn = ibm_db.connect(conn_str, '', '')
# 获取数据库服务器信息
server = ibm_db.server_info(conn)
# 查询数据库sql
sql ='select PID, "NAME" from PRODUCT'
arr = []
dc = {}
if conn:
# 执行查询语句
stmt = ibm_db.exec_immediate(conn, sql)
# 处理查询结果
while (ibm_db.fetch_row(stmt)):
acno, dc[acno] = ibm_db.result(stmt, 0), ibm_db.result(stmt, 1)
arr.append(acno)
# 打印服务器名称
print(server.DBMS_NAME)
# 关闭数据库连接
ibm_db.close(conn)
return arr, dc
def run_test_113(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "DROP TABLE datetest"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )"
ibm_db.exec_immediate(conn, create)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
else:
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM datetest")
ibm_db.execute(stmt)
result = ibm_db.fetch_row( stmt )
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print row0
print row1
result = ibm_db.fetch_row( stmt )
else:
print "Connection failed."
def run_test_113(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "DROP TABLE datetest"
try:
ibm_db.exec_immediate(conn, drop)
except:
pass
create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )"
ibm_db.exec_immediate(conn, create)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
else:
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM datetest")
ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
result = ibm_db.fetch_row(stmt)
else:
print("Connection failed.")
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 check_community_owners(community_id):
active = False
sql = "select DISTINCT LOWER_LOGIN from sncomm.memberlogin where member_uuid in (SELECT DISTINCT MEMBER_UUID from sncomm.member WHERE COMMUNITY_UUID = '" + community_id + "' AND ROLE='1' AND LOWER_LOGIN NOT LIKE '%@%');"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
member_login = ibm_db.result(stmt, 0)
if check_if_user_active(member_login):
active = True
return True
break
else:
return False
except:
print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg()
sys.exit(3)
def dexec_db2(conn,schema,table_name,field_name,sql_fld,test_type):
if sql_fld is None:
if test_type == 'COUNT':
sql = """select count(*) from """ + schema + """.""" + table_name + """ with ur"""
stmt = ibm_db.exec_immediate(conn, sql)
elif test_type == 'SUM':
sql = """select sum("""+field_name+""") from """ + schema + """.""" + table_name + """ with ur"""
stmt = ibm_db.exec_immediate(conn, sql)
else:
stmt = ibm_db.exec_immediate(conn, sql_fld)
cnt = []
while ibm_db.fetch_row(stmt) != False:
cnt.append(ibm_db.result(stmt, 0))
return cnt
def main(args):
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
conn = ibm_db.connect(ssldsn, "", "")
if conn:
print("Connection succeeded.")
else:
print("Connection failed.")
return {"error": "Error connecting to db2"}
# Select from TestTable (the row just inserted)
cmd = "SELECT HISP_DESC FROM SAMPLES.HISPANIC_ORIGIN WHERE HISP_CODE='03'"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"error": "error :" + cmd}
else:
ibm_db.fetch_both(result, 0)
value = ibm_db.result(result, "HISP_DESC")
ibm_db.close(conn)
return {"HISP_DESC": value}
def GetListOfComponentsRecentlyModified(modified_since,
xmeta_user='******',
xmeta_password='******'):
"""
modified_since should be a datetime ( utc) ( or None).
What's the best way of providing db credentials to this?
Initial MVP - just doing jobdefns
"""
if modified_since is None:
logMessage.info('Getting list of all DataStage components')
else:
logMessage.info(
'Getting list of DataStage components modified since :' +
str(modified_since))
# https://www.ibm.com/docs/en/db2/9.7?topic=db-fetching-rows-columns-from-result-sets
#https://stackoverflow.com/questions/6044326/how-to-connect-python-to-db2
## might need rh-python36-python-devel.x86_64 to be able to run this - nope that didn't help/
## maybe need some optional repos? yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms
## Yep, that sorted it. yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms
#from ibm_db import connect, exec_immediate
database = 'XMETA'
database_schema = 'XMETA'
## Setup input args
import ibm_db
# Careful with the punctuation here - we have 3 arguments.
# The first is a big string with semicolons in it.
# (Strings separated by only whitespace, newlines included,
# are automatically joined together, in case you didn't know.)
# The last two are emptry strings.
conn = ibm_db.connect(
'DATABASE=' + database + ';'
'HOSTNAME=localhost;' # 127.0.0.1 or localhost works if it's local
'PORT=50000;'
'PROTOCOL=TCPIP;'
'UID=' + xmeta_user + ';'
'PWD=' + xmeta_password + ';',
'',
'')
## Example ..get all tables
#from ibm_db import tables
#t = db2_results(tables(connection))
#print(t)
# This SQL would show you the different types of class and users that modify things.
#db2 "select CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA, count(*) from DATASTAGEX_DSITEM group by CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA "
## Maybe we should not include things last modified by DataStageSystemUser in the backup - I assume they are default components.
#SQL="select NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA \
# from ISMETA01.DATASTAGEX_DSJOBDEF \
# order by XMETA_MODIFICATION_TIMESTAMP_XMETA desc \
# fetch first 6 rows only"
##ob I created has modification date 2020-10-08-08.29.14.000000
#That's shown as 1602142154825 in db2
#
#So, need to divide the number by 1000 and add as seconds to 1970-01-01
# Convert the timestamp input
from datetime import datetime
dt = datetime.today() # Get timezone naive now
seconds = dt.timestamp()
import time
import datetime
where_clause = """
t2.XMETA_MODIFIED_BY_USER_XMETA <> 'DataStageSystemUser'
and t2.XMETA_MODIFIED_BY_USER_XMETA <> 'admin01'
"""
if modified_since is not None:
where_clause += ' and t2.XMETA_MODIFICATION_TIMESTAMP_XMETA >= ' + str(
modified_since.timestamp() * 1000)
#sql="""
# select XMETA_MODIFIED_BY_USER_XMETA, PROJECTNAMESPACE_XMETA, CLASSNAME_XMETA, NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA
# from XMETA.DATASTAGEX_DSITEM
# where """ + where_clause + """
# ;
# """
sql = """
select t1.CATEGORY_XMETA, t2.NAME_XMETA, t2.XMETA_MODIFICATION_TIMESTAMP_XMETA, t2.CLASSNAME_XMETA, t2.PROJECTNAMESPACE_XMETA
from """ + database_schema + """.DATASTAGEX_DSJOBDEF t1
inner join """ + database_schema + """.DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA = t2.REPOSID_XMETA )
where """ + where_clause + """
;
"""
## Need to include component path in here too.
## I guess need to join to these or tables like these
"""" DATASTAGEX_DSIMSVIEWSET CATEGORY_XMETA
DATASTAGEX_DSJCLTEMPLATE CATEGORY_XMETA
DATASTAGEX_DSJOBDEF CATEGORY_XMETA
DATASTAGEX_DSMACHINEPROFILE CATEGORY_XMETA
DATASTAGEX_DSPARAMETERSET CATEGORY_XMETA
DATASTAGEX_DSROUTINE CATEGORY_XMETA
DATASTAGEX_DSSHAREDCONTAINERDEF CATEGORY_XMETA
DATASTAGEX_DSSTAGETYPE CATEGORY_XMETA
DATASTAGEX_DSTABLEDEFINITION CATEGORY_XMETA
DATASTAGEX_DSTRANSFORM CATEGORY_XMETA
VWDATASTAGEX_DSIMSROOTOBJECT CATEGORY_XMETA
VWMMI_ABSTRACTPERSISTENTADMINPROPERTY CATEGORY_XMETA
maybe better to have something liek.
select t1.CATEGORY_XMETA , t2.*
from DATASTAGEX_DSJOBDEF t1
inner join DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA = t2.REPOSID_XMETA )
"""
## build dictionary of
#( engine, project) [ (component_type, component_name, component_last_modified_timestamp) , .. , .. ]
objectsList = {} #
#sql = "SELECT * FROM XMETA.DATASTAGEX_DSITEM "
stmt = ibm_db.exec_immediate(conn, sql)
#counter=0
while ibm_db.fetch_row(stmt) != False:
#counter+=1
#mod_timestamp=datetime.datetime.fromtimestamp(mod_timestamp_unix,tz=datetime.timezone.utc) # Let's just stick with utc.
#formatted_modification_timestamp=mod_timestamp.strftime("%A, %B %d, %Y %I:%M:%S")
project_namespace = ibm_db.result(stmt, "PROJECTNAMESPACE_XMETA")
project_namespace_tuple = tuple(project_namespace.split(':'))
component_type = ibm_db.result(stmt, "CLASSNAME_XMETA")
component_name = ibm_db.result(stmt, "NAME_XMETA")
mod_timestamp_unix = int(
ibm_db.result(stmt, "XMETA_MODIFICATION_TIMESTAMP_XMETA")) / 1000
component_last_modified_ts = datetime.datetime.fromtimestamp(
mod_timestamp_unix, tz=datetime.timezone.utc)
category = ibm_db.result(stmt, "CATEGORY_XMETA")
try:
objectsList[project_namespace_tuple] += [
(component_type, component_name, component_last_modified_ts,
category)
]
except KeyError:
objectsList[project_namespace_tuple] = [
(component_type, component_name, component_last_modified_ts,
category)
]
return objectsList
# 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.\n")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Query results:\n")
print("DEPTNAME")
print("____________________________")
# As Long As There Are Records In The Result Set Produced, ...
while (ibm_db.fetch_row(resultSet) is True):
# Display The Data Retrieved
print(ibm_db.result(resultSet, 0))
# 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
exit()
insertQuery2 = "insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')"
insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)
##########################
#Construct the query that retrieves all rows from the INSTRUCTOR table
selectQuery = "select * from INSTRUCTOR"
#Execute the statement
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
#Fetch the Dictionary (for the first row only)
ibm_db.fetch_both(selectStmt)
#Fetch the rest of the rows and print the ID and FNAME for those rows
while ibm_db.fetch_row(selectStmt) != False:
print(" ID:", ibm_db.result(selectStmt, 0), " FNAME:",
ibm_db.result(selectStmt, "FNAME"))
############################
#Update query
updateQuery = "update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'"
updateStmt = ibm_db.exec_immediate(conn, updateQuery)
###########################
# Retrieve data into pandas
import pandas
import ibm_db_dbi
#connection for pandas
pconn = ibm_db_dbi.Connection(conn)
def chat():
# check if user is logged in
if not session.get('logged_in'):
return render_template('index.html')
else:
user_ip = request.remote_addr
user_agent = request.headers.get('User-Agent')
session['unique_conversation_id'] = str(user_ip) + "__" + str(user_agent)
context["conversation_id"] = str(hashlib.sha256(session['unique_conversation_id'].encode('utf-8')).hexdigest())
if request.method == "POST":
if 'cardNo' in request.form:
sql = "SELECT * FROM CARD WHERE CARDNO = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["cardNo"],
ibm_db.execute(stmt, param)
if ibm_db.fetch_row(stmt) == False:
sql = "SELECT * FROM CARD ORDER BY ID DESC fetch first 1 row only"
stmt = ibm_db.exec_immediate(conn, sql)
card = 1
while ibm_db.fetch_row(stmt) != False:
card = card + int(ibm_db.result(stmt, "ID"))
# Save new customer card info
sql = "INSERT INTO CARD (ID, BVN, ACCNO, CARDNO, CARDVENDOR, CARDTYPE, CVC, EXPIRY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, sql)
param = card, user["bvn"], request.form["cardAccountNo"], request.form["cardNo"], request.form["cardVendor"], request.form["cardType"], request.form["cvc"], request.form["cardExpiry"],
ibm_db.execute(stmt, param)
msg = Message('CASBA: New Card', sender = '*****@*****.**', recipients = [str(user["email"])])
msg.body = "You have added a new card"
mail.send(msg)
flash('You have successfully added a card!')
sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])])
stmt2 = ibm_db.exec_immediate(conn, sql2)
userCardDF = pandas.read_sql(sql2 , pconn)
if userCardDF.empty:
user.update({'#Card': 0})
return redirect(url_for('chat'))
else:
user.update({'#Card': len(userCardDF.index)})
userCard = {}
for i in range(0, user["#Card"]):
userCard[i] = userCardDF.iloc[i].tolist()
user.update({'Card': userCard})
#print user["Account"][0][0]
return redirect(url_for('chat'))
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
else:
flash('You have card already registered!')
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
elif 'accountNo' in request.form:
sql = "SELECT * FROM ACCOUNT WHERE ACCNO = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["accountNo"],
ibm_db.execute(stmt, param)
if ibm_db.fetch_row(stmt) == False:
sql = "SELECT * FROM ACCOUNT ORDER BY ID DESC fetch first 1 row only"
stmt = ibm_db.exec_immediate(conn, sql)
account = 1
while ibm_db.fetch_row(stmt) != False:
account = account + int(ibm_db.result(stmt, "ID"))
# Save new customer info
sql = "INSERT INTO ACCOUNT (ID, BVN, ACCNO, ACCBANK, ACCNAME, ACCTYPE, ACCBALANCE) VALUES (?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, sql)
param = account, user["bvn"], request.form["accountNo"], request.form["accountBank"], request.form["accountName"], request.form["accountType"], request.form["accountBalance"],
ibm_db.execute(stmt, param)
msg = Message('CASBA: New Account', sender = '*****@*****.**', recipients = [str(user["email"])])
msg.body = "You have added a new account"
mail.send(msg)
flash('You were successfully setup an account!')
sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(user['bvn'])])
stmt1 = ibm_db.exec_immediate(conn, sql1)
userAccountDF = pandas.read_sql(sql1 , pconn)
if userAccountDF.empty:
user.update({'accountIn': 0})
return redirect(url_for('chat'))
else:
user.update({'accountIn': len(userAccountDF.index)})
userAccount = {}
for i in range(0, user["accountIn"]):
userAccount[i] = userAccountDF.iloc[i].tolist()
user.update({'Account': userAccount})
#print user["Account"][0][0]
return redirect(url_for('chat'))
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
else:
flash('You have account already registered!')
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
else:
flash('Unsuccessful account setup!')
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
else:
return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
if check_if_user_active(member_login):
active = True
return True
break
else:
return False
except:
print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg()
sys.exit(3)
# Query DB2 database
sql = "select DISTINCT COMMUNITY_UUID, NAME from sncomm.community;"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
community_id = ibm_db.result(stmt, 0)
name = ibm_db.result(stmt, 1)
if not check_community_owners(community_id):
list =list + name + " ID: " + community_id + "\n"
count = count + 1
except:
print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg()
sys.exit(3)
# Nagios standard feedback
if (count < int(args.warning)):
print("Communites are OK, " + str(count) + " Communities without an owner: " + list.encode('utf-8') + "| Number of Communities=" + str(count) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
sys.exit(0)
elif (count < int(args.critical)):
print("Warning: " + str(count) + " Communities without an owner: "+ list.encode('utf-8') + "| Number of Communities=" + str(count) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
sys.exit(1)
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
def index():
if request.method == "POST":
if 'signupBVN' in request.form:
sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["signupBVN"],
ibm_db.execute(stmt, param)
if ibm_db.fetch_row(stmt) == False:
sql = "SELECT * FROM CUSTOMER ORDER BY ID DESC fetch first 1 row only"
stmt = ibm_db.exec_immediate(conn, sql)
customer = 1
while ibm_db.fetch_row(stmt) != False:
customer = customer + int(ibm_db.result(stmt, "ID"))
# Save new customer info
sql = "INSERT INTO CUSTOMER (ID, BVN, FIRSTNAME, LASTNAME, DATEOFBIRTH, PHONENUMBER, EMAIL, PASSWORD, CITY, DOC) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, sql)
param = customer, request.form["signupBVN"], request.form["inputFName"], request.form["inputLName"], request.form["inputDOB"], request.form["inputPhone"], request.form["inputEmail"], request.form["inputPassword"], request.form["inputLocation"], datetime.date.today(),
ibm_db.execute(stmt, param)
# Save security info for new customer
sql = "INSERT INTO SECURITY (ID, BVN, SQ1, SA1, SQ2, SA2, SQ3, SA3) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, sql)
param = customer, request.form["signupBVN"], request.form["inputSQ1"], request.form["inputSA1"], request.form["inputSQ2"], request.form["inputSA2"], request.form["inputSQ3"], request.form["inputSA3"],
ibm_db.execute(stmt, param)
msg = Message('Welcome to CASBA', sender = '*****@*****.**', recipients = [str(request.form["inputEmail"])])
msg.body = "Thank you for registering to use of Cognitive Banking (CogniBank) service. Enjoy the experience of using artifical intelligence to organise your finances."
mail.send(msg)
flash('You were successfully registered!')
return redirect(url_for('index'))
else:
flash('You were already registered!')
return redirect(url_for('index'))
elif 'loginBVN' in request.form:
sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["loginBVN"],
ibm_db.execute(stmt, param)
if ibm_db.fetch_row(stmt) != False:
# check if user credentials match
if request.form["inputPassword"] == ibm_db.result(stmt, "PASSWORD"):
session['logged_in'] = True
user.update({'bvn': str(request.form["loginBVN"]), 'fName': str(ibm_db.result(stmt, "FIRSTNAME")), 'lName': str(ibm_db.result(stmt, "LASTNAME")), 'email': str(ibm_db.result(stmt, "EMAIL")) })
# sql = "SELECT * FROM ACCOUNT WHERE BVN = ?"
# stmt = ibm_db.prepare(conn, sql)
# param = request.form["loginBVN"],
# ibm_db.execute(stmt, param)
sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(request.form["loginBVN"])])
stmt1 = ibm_db.exec_immediate(conn, sql1)
userAccountDF = pandas.read_sql(sql1 , pconn)
sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])])
stmt2 = ibm_db.exec_immediate(conn, sql2)
userCardDF = pandas.read_sql(sql2 , pconn)
if userAccountDF.empty:
user.update({'accountIn': 0})
if userCardDF.empty:
user.update({'#Card': 0})
return redirect(url_for('chat'))
else:
user.update({'accountIn': len(userAccountDF.index)})
userAccount = {}
for i in range(0, user["accountIn"]):
userAccount[i] = userAccountDF.iloc[i].tolist()
user.update({'Account': userAccount})
#print user["Account"][0][0]
if userCardDF.empty:
user.update({'#Card': 0})
return redirect(url_for('chat'))
else:
user.update({'#Card': len(userCardDF.index)})
userCard = {}
for i in range(0, user["#Card"]):
userCard[i] = userCardDF.iloc[i].tolist()
user.update({'Card': userCard})
#print user["Account"][0][0]
return redirect(url_for('chat'))
else:
flash('Wrong credentials, please try again or create an account!')
return redirect(url_for('index'))
else:
flash('Wrong credentials, please try again or create an account!')
return redirect(url_for('index'))
elif 'passwordBVN' in request.form:
sql = "SELECT * FROM SECURITY WHERE BVN = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["passwordBVN"],
ibm_db.execute(stmt, param)
# check if security questions match
if ibm_db.fetch_row(stmt) != False:
if str(ibm_db.result(stmt, "SQ1")) == str(request.form["inputSQ1"]) and str(ibm_db.result(stmt, "SA1")) == str(request.form["inputSA1"]):
if str(ibm_db.result(stmt, "SQ2")) == str(request.form["inputSQ2"]) and str(ibm_db.result(stmt, "SA2")) == str(request.form["inputSA2"]):
if str(ibm_db.result(stmt, "SQ3")) == str(request.form["inputSQ3"]) and str(ibm_db.result(stmt, "SA3")) == str(request.form["inputSA3"]):
new_password = gen_random_string(password_charset, 6)
sql = "UPDATE CUSTOMER SET PASSWORD = ? WHERE BVN = ?"
stmt = ibm_db.prepare(conn, sql)
param = new_password, request.form["passwordBVN"],
ibm_db.execute(stmt, param)
sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
stmt = ibm_db.prepare(conn, sql)
param = request.form["passwordBVN"],
ibm_db.execute(stmt, param)
if ibm_db.fetch_row(stmt) != False:
msg = Message('Password Reset', sender = '*****@*****.**', recipients = [str(ibm_db.result(stmt, "EMAIL"))])
msg.body = ' '.join(["Here is your new password", new_password])
mail.send(msg)
flash('Your password has been reset! Please check your email.')
return redirect(url_for('index'))
else:
flash('You are not registered! Please try again or create an account.')
return redirect(url_for('index'))
else:
flash('Incorrect Security Question & Answer')
return redirect(url_for('index'))
else:
flash('Incorrect Security Question & Answer')
return redirect(url_for('index'))
else:
flash('Incorrect Security Question & Answer')
return redirect(url_for('index'))
else:
flash('You are not a registered user! Please create an account.')
return redirect(url_for('index'))
else:
return render_template('index.html')
dsn_protocol, dsn_uid, dsn_pwd)
#print(dsn)
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected!")
except:
print("Unable to connect to database")
#1 createstmt=ibm_db.exec_immediate(conn,"create table Employee(name varchar(20), id int, dept char(10))")
#2 to insert a record
"""ins="insert into Employee values('Amritsar','18','BCA')"
insertstmt=ibm_db.exec_immediate(conn,ins)"""
#3 to drop a table
#dropstmt=ibm_db.exec_immediate(conn,"drop table Employee")
#4 getting data from table
selectquery = "select * from Employee"
selectstmt = ibm_db.exec_immediate(conn, selectquery)
while ibm_db.fetch_row(selectstmt) != False:
print("Name:", ibm_db.result(selectstmt, 0), " ID: ",
ibm_db.result(selectstmt, 1), " Dept: ",
ibm_db.result(selectstmt, 2))
ibm_db.close(conn)
uid = args.user
pwd = args.pw
# Connect to DB2 database
try:
conn_people = ibm_db.connect("DATABASE=" + peopledb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "")
conn_comm = ibm_db.connect("DATABASE=" + communitydb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "")
except:
print "No connection to DBs:", ibm_db.conn_errormsg()
sys.exit(3)
# Query DB2 database
sql = "select DISTINCT COMMUNITY_UUID, NAME, LASTMOD from sncomm.community;"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
name = ibm_db.result(stmt, "NAME")
lastupdate = ibm_db.result(stmt, "LASTMOD")
if (datetime.datetime.now() - datetime.timedelta(days=int(args.critical)) > lastupdate):
count_critical = count_critical + 1
list_critical = list_critical + name + "\n"
elif (datetime.datetime.now() - datetime.timedelta(days=int(args.warning)) > lastupdate):
count_warning = count_warning + 1
list_warning = list_warning + name + "\n"
except:
print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg()
sys.exit(3)
# Nagios standard feedback
if (count_critical!=0):
print("Critical: There are communites which were not updates since " + args.critical + " days" + list_critical.encode('utf-8') + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
sys.exit(2)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Query results:\n")
print("DEPTNAME")
print("____________________________")
# As Long As There Are Records In The Result Set Produced, ...
while (ibm_db.fetch_row(resultSet) is True):
# Retrieve The Data From The Current Row
try:
dataValue = ibm_db.result(resultSet, 0)
except Exception:
pass
# Display The Data Retrieved
if not dataValue is None:
print(dataValue)
# 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
exit()
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
#Deleting only the rows which meet some criteria
## DeleteQuery = "delete from "+TABLENAME+" WHERE id="+'EXAMPLE'
## DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)
# Or you may just want to delete everything:
DeleteQuery = "delete from " + TABLENAME
DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)
#Checking the result
selectQuery = "select * from " + TABLENAME
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
#Fetch the Dictionary, if the only row is enough for you
#ibm_db.fetch_both(selectStmt)
#Fetch all the rows and print the first([0]) and the second ("USERNAME") columns for those rows
while ibm_db.fetch_row(selectStmt) != False:
print(" ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME"))
except:
print("something went wrong, but I don't know what exactly ",
ibm_db.conn_errormsg())
ibm_db.close(conn)
print("Connection closed")
def config_IBMHIST(conn, coll_path, arch_path, max_size, arch_cmd, arch_ext):
print("Configuring IBMHIST settings ...")
# delete from IBMHIST.TAB_CONFIG
print(" Deleting configurations from IBMHIST.TAB_CONFIG ...")
ibm_db.exec_immediate(conn, "DELETE FROM IBMHIST.TAB_CONFIG")
# rename current collection directory to avoid name conflicts
stmt = ibm_db.exec_immediate(
conn, "SELECT PATH FROM IBMHIST.TAB_DIRS WHERE STATUS = 'COLL'")
while ibm_db.fetch_row(stmt):
# get orig path
orig_path = ibm_db.result(stmt, "PATH")
# get new path by appending current minute and current second
new_path = orig_path + '_old' + str(
datetime.datetime.now().minute) + str(
datetime.datetime.now().second)
# rename
print(" Renaming collection directory '%s' to '%s' ..." %
(orig_path, new_path))
shutil.move(orig_path, new_path)
# update IBMHIST.TAB_DIRS
ibm_db.exec_immediate(
conn, "UPDATE IBMHIST.TAB_DIRS SET PATH = '%s' WHERE PATH = '%s'" %
(new_path, orig_path))
# add data collection path configuration
print(" Setting COLL_PATH to '%s' ..." % (coll_path))
assert os.path.exists(
coll_path), "Data collection path: %s does not exist" % (coll_path)
stmt, ret_coll_path, retcode = ibm_db.callproc(
conn, 'IBMHIST.PATH_READABLE_WRITABLE', (coll_path, 0))
assert retcode is 0 or retcode is None, "Data collection path: %s does not provide fenced external functions read/write access" % (
coll_path)
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'COLL_PATH', '%s', 'DIRECTORY PATH OF DATA COLLECTION') "
% (coll_path))
# add data archival path configuration
print(" Setting ARCH_PATH to '%s' ..." % (arch_path))
assert os.path.exists(
arch_path), "Data archival path: %s does not exist" % (arch_path)
stmt, ret_arch_path, retcode = ibm_db.callproc(
conn, 'IBMHIST.PATH_READABLE_WRITABLE', (arch_path, 0))
assert retcode is 0 or retcode is None, "Data archival path: %s does not provide fenced external functions read/write access" % (
arch_path)
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_PATH', '%s', 'DIRECTORY PATH OF DATA ARCHIVAL') "
% (arch_path))
# add max size configuration
print(" Setting MAX_SIZE to '%s' bytes ..." % (max_size))
assert str.isdigit(max_size), "Max size: %s is not an integer" % (max_size)
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'MAX_SIZE', '%s', 'MAX SIZE IN BYTES OF COLLECTION AND ARCHIVAL') "
% (max_size))
# add archival command and archival extension
print(" Setting ARCH_CMD to '%s' and ARCH_EXT to '%s' ..." %
(arch_cmd, arch_ext))
# test archival functionality by archiving sql folder
src, dest = "sql", "arch_test" + arch_ext
arch_cmd_test = arch_cmd.replace("_src_", src).replace("_dest_", dest)
if os.path.exists(dest):
os.remove(dest)
subprocess.check_call(arch_cmd_test, shell=True)
assert os.path.exists(
dest
), "Data archival command: %s failed, could not find archival %s " % (
arch_cmd, dest)
os.remove(dest)
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_CMD', '%s', 'COMMAND USED TO ARCHIVE HOURLY DIRECTORIES') "
% arch_cmd)
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_EXT', '%s', 'EXTENSION OF ARCHIVE HOURLY DIRECTORIES') "
% arch_ext)
# add task_details.json path configuration
task_details_path = os.path.realpath("task_details.json")
print(" Setting TASK_DETAILS_PATH to '%s' ..." % (task_details_path))
ibm_db.exec_immediate(
conn,
"INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'TASK_DETAILS_PATH', '%s', 'LOCATION OF task_details.json FILE') "
% task_details_path)
def sql(self, line, cell=None):
# 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, hstmt, connected
select = ["SELECT", "WITH", "VALUES"]
noBlock = 0
sqlBlock = 1
db2Block = 2
# If you use %sql (line) we just run the SQL. If you use %%SQL the entire cell is run.
flag_delim = ";"
flag_results = True
flag_sqlType = noBlock
flag_quiet = False
flag_json = False
flag_timer = False
flag_plot = 0
flag_cell = False
flag_output = False
flag_resultset = False
flag_dataframe = False
# The parameters must be in the line, not in the cell i.e. %sql -c
Parms = line.strip()
if len(Parms) == 0:
if cell == None:
sqlhelp()
return
if len(cell.strip()) == 0:
sqlhelp()
return
# Check of you just want help
if Parms == "?":
sqlhelp()
return
if Parms.upper() == "? CONNECT":
connected_help()
return
# If you issue a CONNECT statement in %sql then we run this first before auto-connecting
if findKeyword(Parms,"CONNECT") == True:
parseConnect(Parms)
return
# We need to check to see if we are connected before running any SQL
if connected == False:
db2_doConnect()
if connected == False: return
# Default result set size
if settings["maxrows"] == -1:
pandas.reset_option('max_rows')
else:
pandas.options.display.max_rows = settings["maxrows"]
# Display rows as JSON structure
if Parms.find("-j") >= 0:
flag_json = True
Parms = Parms.replace("-j"," ")
# Load sample tables for scripts
if Parms.find('-sampledata') >= 0:
db2_create_sample()
return
# Execute the SQL so that it behaves like a SELECT statement
if Parms.find("-s") >= 0:
flag_sqlType = sqlBlock
Parms = Parms.replace("-s"," ")
# Execute the SQL but return the results in an array (basically a two-dimensional array)
if Parms.find("-r") >= 0:
flag_resultset = True
Parms = Parms.replace("-r", " ")
# Execute the SQL so that it behaves like an INSERT, DELETE, UPDATE or no result set
if Parms.find("-n") >= 0:
flag_sqlType = db2Block
Parms = Parms.replace("-n"," ")
# Quiet execution (no errors or completed messages)
if Parms.find("-q") >= 0:
flag_quiet = True
Parms = Parms.replace("-q"," ")
# Retrieve all rows (do not use the default limit)
if Parms.find("-a") >= 0:
pandas.reset_option('max_rows')
Parms = Parms.replace("-a"," ")
# Set the delimiter to @ instead of a semi-colon for procedures, triggers, and functions
if Parms.find("-d") >= 0:
flag_delim = "@"
Parms = Parms.replace("-d"," ")
# Timer function (not that useful, but worth a try)
if Parms.find("-t") >= 0:
flag_timer = True
Parms = Parms.replace("-t"," ")
# Plot functions -pb = bar, -pp = pie, -pl = line
if Parms.find("-pb") >= 0:
flag_plot = 1
Parms = Parms.replace("-pb"," ")
if Parms.find("-pp") >= 0:
flag_plot = 2
Parms = Parms.replace("-pp"," ")
if Parms.find("-pl") >= 0:
flag_plot = 3
Parms = Parms.replace("-pl"," ")
if Parms.find("-i") >= 0:
flag_plot = 4
Parms = Parms.replace("-i"," ")
remainder = Parms.strip()
# Split the line according to your delimiter
if cell is None:
sqlLines = [remainder]
flag_cell = False
else:
cell = re.sub('.*?--.*$',"",cell,flags=re.M)
remainder = cell.replace("\n"," ")
sqlLines = remainder.split(flag_delim)
flag_cell = True
# For each line figure out if you run it as a command (db2) or select (sql)
for sql in sqlLines:
# Split the line so we know what the first keyword is. We only look at the first one. There may
# be SQL that returns output that we may not know about
keywords = sql.split()
if len(keywords) == 0: continue
sqlcmd = keywords[0].upper()
if (flag_timer == True):
count = sqlTimer(flag_sqlType, sql)
if flag_quiet == False and count != -1:
print("Total iterations in %s second(s): %s" % (runtime,count))
return(count)
elif (flag_plot != 0):
try:
df = pandas.read_sql(sql,hstmt)
except Exception as err:
db2_error(False)
return
if flag_plot == 4:
ShellAccess.pdf = df
display(pdf)
return
plt.style.use('ggplot')
plt.figure()
col_count = len(df.columns)
if flag_plot == 1:
# Bar Chart
if (col_count >= 2):
xlabel = df.columns.values[0]
ylabel = df.columns.values[1]
_ = df.plot(kind='bar',x=xlabel,y=ylabel);
else:
_ = df.plot(kind='bar');
elif flag_plot == 2:
# Pie
if (col_count >= 2):
xlabel = df.columns.values[0]
xname = df[xlabel].tolist()
yname = df.columns.values[1]
_ = df.plot(kind='pie',y=yname,labels=xname);
else:
yname = df.columns.values[0]
_ = df.plot(kind='pie',y=yname);
elif flag_plot == 3:
# Line Chart
if (col_count >= 2):
xlabel = df.columns.values[0]
ylabel = df.columns.values[1]
_ = df.plot(kind='line',x=xlabel,y=ylabel) ;
else:
_ = df.plot(kind='line') ;
else:
return
plt.show()
return
elif (flag_sqlType == sqlBlock) or (sqlcmd in select and flag_sqlType != db2Block):
if flag_json == True:
try:
stmt = ibm_db.exec_immediate(hdbc,sql);
row_count = 0
while( ibm_db.fetch_row(stmt) ):
row_count = row_count + 1
jsonVal = ibm_db.result(stmt,0)
formatted_JSON = json.dumps(json.loads(jsonVal), indent=4, separators=(',', ': '))
# Print JSON Structure
if row_count > 1: print()
print("Row: %d" % row_count)
print(formatted_JSON)
flag_output = True
except Exception as err:
db2_error(flag_quiet)
else:
if flag_resultset == True:
row_count = 0
resultSet = []
try:
stmt = ibm_db.exec_immediate(hdbc,sql)
result = ibm_db.fetch_tuple(stmt)
while (result):
row = []
for col in result:
row.append(col)
resultSet.append(row)
result = ibm_db.fetch_tuple(stmt)
return(resultSet)
except Exception as err:
db2_error(False)
else:
try:
dp = pandas.read_sql(sql, hstmt)
if flag_dataframe == True:
return(dp)
else:
# pDisplay(dp)
flag_output = True
return(dp)
except Exception as err:
db2_error(flag_quiet)
else:
try:
ibm_db.exec_immediate(hdbc,sql);
if flag_cell == False and flag_quiet == False:
print("Command completed.")
except Exception as err:
db2_error(flag_quiet)
if flag_cell == True and flag_output == False:
print("Command completed.")
def run_test_bool_callproc(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 PROCEDURE bool_procparams")
ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(
conn,
"CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
)
except:
pass
try:
procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
OPEN c1;
set param2 = parm1;
END"""
ibm_db.exec_immediate(conn, procedure)
except Exception as e:
print(str(e))
exit(-1)
try:
insert_sql = "INSERT INTO bool_test values(?, ?)"
stmt = ibm_db.prepare(conn, insert_sql)
rows = ((True, 'bindparam true'), (False, 'bindparam false'),
(None, 'bindparam None'))
for row in rows:
ibm_db.bind_param(stmt, 1, row[0])
ibm_db.bind_param(stmt, 2, row[1])
ibm_db.execute(stmt)
stmt = None
inparam = 11
outparam = -1
stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
(inparam, outparam))
print("Fetching first result set")
row = ibm_db.fetch_row(stmt)
while row:
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
row = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
