def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_db.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
row3 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def populate_role(conn, data):
db2.execute(conn, '''
INSERT INTO SSO_ROLES(ROLE_KEY, NAME, ENABLED, DESC, TENANT_KEY, CREATE_TIME)
VALUES(?, ?, ?, ?, ?, ?)
''', data, commit=False)
ibm_db.exec_immediate(conn, 'ALTER TABLE SSO_ROLES ALTER COLUMN ROLE_KEY RESTART WITH 4000000')
def exec_query(db, query):
try:
ibm_db.exec_immediate(db,query)
except:
print "[*] The transaction could not be completed:", query# ,ibm_db.stmt_errormsg()
else:
print "[*] Transaction complete: ",query
def populate_user(conn, data):
db2.execute(conn, '''
INSERT INTO SSO_USERS(USER_KEY, PASSWORD, USER_NAME, SALT, TENANT_KEY, CREATE_TIME, MODIFY_TIME)
VALUES(?, ?, ?, ?, ?, ?, ?)
''', data, commit=False)
ibm_db.exec_immediate(conn, 'ALTER TABLE SSO_USERS ALTER COLUMN USER_KEY RESTART WITH 1000')
def run_test_195(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
drop = 'DROP TABLE test_195'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
ibm_db.exec_immediate(conn, insert)
sql = "SELECT data FROM test_195"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
while( result ):
print "Output:", result
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print "Native XML datatype is not supported."
def run_test_040(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Drop the test table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "select * from animals")
onerow = ibm_db.fetch_tuple(stmt)
for element in onerow:
print element
ibm_db.rollback(conn)
def run_test_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
if result:
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_144(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BINARY)
rc = ibm_db.execute(stmt)
num = ibm_db.num_rows(stmt)
print(num)
else:
print("Connection failed.")
def run_test_054(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
else:
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(val)
def run_test_020(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
ac = ibm_db.autocommit(conn)
if ac != 0:
print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.close(conn)
else:
print "Connection failed."
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_156(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
count = 1
while (row):
if (row['YEARS'] == None):
row['YEARS'] = ''
if (row['COMM'] == None):
row['COMM'] = ''
print row['ID'], row['NAME'], row['JOB'], row['YEARS'], row[
'SALARY'], row['COMM']
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(
conn,
"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')"
)
row2 = ibm_db.fetch_assoc(result2)
while (row2):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
'ADMRDEPT'], row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
def write(q,data):
# initialize vars
write_str = q
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
if TL:
ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
for t in data:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
ibm_db.commit(conn)
#
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def run_test_022(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.close(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_311(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON )
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ibm_db.exec_immediate(conn, sql)
print "Number of updated rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ibm_db.exec_immediate(conn, sql)
print "Number of deleted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)
def create_catalog(cat, catalog):
try:
ibm_db.exec_immediate(cat,"create table dtables (tname char(32), nodedriver char(64), " \
"nodeurl char(128), nodeuser char(16), nodepasswd char(16), " \
"partmtd int, nodeid int, partcol char(32), partparam1 char(32)," \
" partparam2 char(32))")
except:
print "[*] NOTICE catalog table exists, continuing..."
def deleteItem():
page = ''
if db2conn:
value = request.args.get('item')
ibm_db.exec_immediate(db2conn, "DELETE FROM ITEMS WHERE item='" + value + "'")
page = 'Deleted: ' + value
else:
page = 'Cannot delete item'
return page
def myDeviceEventCallback(event):
s = json.dumps(event.data)
print(s)
s = json.loads(s)
print(s["d"])
if db2conn:
if s["d"]["topic"][:4] == "CSIE":
ins = ibm_db.exec_immediate(db2conn, "INSERT INTO DASH5369.CSIE (TIME_STAMP, SIGNAL_SENT) VALUES ('%s','%s');" %(s['d']['timestamp'], s['d']['signal']))
elif s['d']['topic'][:3] == 'MDV':
ins = ibm_db.exec_immediate(db2conn, "INSERT INTO DASH5369.MDV (ID, TIME_STAMP, SINGAL_RECEIVED, SPEED) VALUES (%s, '%s', '%s', %s);" %(s['d']['id'],s['d']['tiemstamp'],s['d']['signal'], s['d']['speed']))
def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print ("Connection failed.")
#__END__
#__LUW_EXPECTED__
#No Data[IBM][CLI Driver][DB2/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__ZOS_EXPECTED__
#No Data[IBM][CLI Driver][DB2]
# SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__SYSTEMI_EXPECTED__
#No Data
#__IDS_EXPECTED__
#No Data
#[IBM][CLI Driver][IDS/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
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 counter_page():
try:
connection = ibm_db.connect(app.config['dsn'], '', '')
query = "UPDATE COUNTER SET N = N + 1"
ibm_db.exec_immediate(connection, query)
query = "SELECT N FROM COUNTER"
statement = ibm_db.exec_immediate(connection, query)
(count,) = ibm_db.fetch_tuple(statement)
except:
count = -1
return "This page was accessed %d times." % count
def run_test_046(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format FROM emp_photo")
else:
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(picture) FROM emp_photo")
row = ibm_db.fetch_tuple(result)
while ( row ):
if row[1] != 'xwd':
print("<a href='test_046.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a><br>" % (row[0], row[1], row[0], row[1], row[2]))
row = ibm_db.fetch_tuple(result)
def run_test_250(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") r1 = ibm_db.free_result(result) r2 = ibm_db.free_result(result2) r3 = ibm_db.free_result(result3) print(r1) print(r2) print(r3)
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_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_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 addItem():
page = ''
if db2conn:
#Get the value of the key 'item' in the query string
value = request.args.get('item')
#Execute the insert statement
ibm_db.exec_immediate(db2conn, "INSERT INTO ITEMS(ITEM) VALUES ('" + value + "')")
page = 'Added: ' + value
else:
page = 'Cannot add item'
return page
def run_test_047(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format from emp_photo")
else:
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(PICTURE) from emp_photo")
row = ibm_db.fetch_tuple(result)
while ( row ):
if (row[1] == 'gif'):
print "<img src='test_047.php?EMPNO=%s&FORMAT=%s'><br>\n" % (row[0], row[1])
if (row[1] != 'xwd'):
print "<a href='test_047.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a>\n<br>" % (row[0], row[1], row[0], row[1], row[2])
row = ibm_db.fetch_tuple(result)
def insert():
mode = request.args.get('mode1') if request.args.get(
'mode1') else request.args.get('mode2')
mode = mode.split(' ')[-1]
etime = request.args.get('time') if request.args.get('time') else -1
lat = request.args.get('lat') if request.args.get('lat') else -1
lon = request.args.get('lon') if request.args.get('lon') else -1
dep = request.args.get('dep') if request.args.get('dep') else -1
mag = request.args.get('mag') if request.args.get('mag') else -1
magtype = request.args.get('magtype') if request.args.get(
'magtype') else -1
nst = request.args.get('nst') if request.args.get('nst') else -1
gap = request.args.get('gap') if request.args.get('gap') else -1
dmin = request.args.get('dmin') if request.args.get('dmin') else -1
rms = request.args.get('rms') if request.args.get('rms') else -1
net = request.args.get('net') if request.args.get('net') else -1
eid = request.args.get('id') if request.args.get('id') else -1
updated = request.args.get('updated') if request.args.get(
'updated') else -1
place = request.args.get('place') if request.args.get('place') else -1
etype = request.args.get('type') if request.args.get('type') else -1
he = request.args.get('he') if request.args.get('he') else -1
de = request.args.get('de') if request.args.get('de') else -1
me = request.args.get('me') if request.args.get('me') else -1
mn = request.args.get('mn') if request.args.get('mn') else -1
status = request.args.get('status') if request.args.get('status') else -1
locsource = request.args.get('locsource') if request.args.get(
'locsource') else -1
magsource = request.args.get('magsource') if request.args.get(
'magsource') else -1
start = time.time()
# connect to DB2
if mode == 'RDB':
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = """INSERT INTO EARTHQUAKE \
(TIME, LATITUDE, LONGTITUDE, DEPTH, MAG, \
MAGTYPE, NST, GAP, DMIN, RMS, NET, ID, UPDATED, \
PLACE, TYPE, HOTIZONTALERROR, DEPTHERROR, \
MAGERROR, MAGNST, STATUS, LOCATIONSOURCE, MAGSOURCE) \
VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {});""".format(
etime, lat, lon, dep, mag, magtype, nst, gap, dmin, rms, net,
eid, updated, place, etype, he, de, me, mn, status, locsource,
magsource)
ibm_db.exec_immediate(db2conn, sql)
elif mode == 'Memcache':
tmp = pd.DataFrame([[
etime,
float(lat),
float(lon),
float(dep),
float(mag), magtype,
float(nst),
float(gap),
float(dmin),
float(rms), net, eid, updated, place, etype,
float(he),
float(de),
float(me),
float(mn), status, locsource, magsource
]])
tmp.columns = [
'TIME', 'LATITUDE', 'LONGTITUDE', 'DEPTH', 'MAG', 'MAGTYPE', 'NST',
'GAP', 'DMIN', 'RMS', 'NET', 'ID', 'UPDATED', 'PLACE', 'TYPE',
'HOTIZONTALERROR', 'DEPTHERROR', 'MAGERROR', 'MAGNST', 'STATUS',
'LOCATIONSOURCE', 'MAGSOURCE'
]
global cache_csv
cache_csv = cache_csv.append(tmp, ignore_index=True)
end = time.time()
elapse = end - start
return render_template('index.html', app=appenv, insert_elp=elapse)
def main(params):
logging.info('Calling fn_wds_operations.')
try:
cos_everest_submission_bucket = params.get(
"cos_everest_submission_bucket", SUBMISSION_BUCKET)
if cos_everest_submission_bucket is None or "":
raise Exception("Pass location of the bucket")
cos_everest_submission_data_folder = params.get(
"cos_everest_submission_data_folder", SUBMISSION_DATA_FOLDER)
if cos_everest_submission_data_folder is None or "":
raise Exception("Pass cos_everest_submission_data_folder")
doc_type_to_process = params.get("doc_type_to_process",
DOC_TYPE_TO_PROCESS)
if doc_type_to_process is None or "":
raise Exception("Pass doc_type_to_process")
# initialize watson discovery utils
wds = watsondiscoveryutils.inst()
# with DB2DBConnection() as db_conn:
db_conn = db2utils.get_connection()
print("db_conn: {}".format(db_conn))
sql = f'''SELECT ID, DOCUMENT_TYPE, DOCUMENT_NAME
FROM EVERESTSCHEMA.evre_learning_email_attachments where used_for='TRAINING' and DOCUMENT_TYPE IN {tuple(doc_type_to_process)} order by ID'''
print("sql: {}".format(sql))
stmt = ibm_db.exec_immediate(db_conn, sql)
result = ibm_db.fetch_both(stmt)
msg_id = None
msg_encoded_id = None
msg_document_id = None
while result:
print("result::{}".format(result))
msg_id = result["ID"]
doc_type = result["DOCUMENT_TYPE"]
obj_storage_doc_path = result["DOCUMENT_NAME"]
print(f'obj_storage_doc_path: {obj_storage_doc_path}')
# https://blogs.msdn.microsoft.com/vsofficedeveloper/2008/05/08/office-2007-file-format-mime-types-for-http-content-streaming-2/
if doc_type.lower() == ".PDF".lower():
file_content_type = "application/pdf"
elif doc_type.lower() == ".DOCX".lower():
file_content_type = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
elif doc_type.lower() == ".DOC".lower():
file_content_type = "application/msword"
elif doc_type.lower() == ".XLSX".lower():
file_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
elif doc_type.lower() == ".XLS".lower() or doc_type.lower(
) == ".XL".lower():
file_content_type = "application/vnd.ms-excel"
elif doc_type.lower() == ".XLSM".lower():
file_content_type = "application/vnd.ms-excel.sheet.macroEnabled.12"
elif doc_type.lower() == ".XLTM".lower():
file_content_type = "application/vnd.ms-excel.template.macroEnabled.12"
elif doc_type.lower() == ".XLTX".lower():
file_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.template"
elif doc_type.lower() == ".XLSB".lower():
file_content_type = "application/vnd.ms-excel.sheet.binary.macroEnabled.12"
elif doc_type.lower() == ".PPTX".lower():
file_content_type = "application/vnd.openxmlformats-officedocument.presentationml.presentation"
elif doc_type.lower() == ".HTML".lower():
file_content_type = "application/xhtml+xml"
# task = asyncio.create_task(wds_upload_task(wds, cos_everest_submission_bucket, obj_storage_doc_path, doc_bytes, doc_type))
# await task
wds_upload_task(wds, cos_everest_submission_bucket,
obj_storage_doc_path, file_content_type)
# iterate thru the resultset
result = ibm_db.fetch_both(stmt)
except (ibm_db.conn_error, ibm_db.conn_errormsg, Exception) as err:
logging.exception(err)
json_result = json.dumps(err)
return {"result": "Success"}
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 __name__ == '__main__':
"""For usage reference"""
# conn = DB2DBConnection().call ()
# sql = "Select * from GOSALES.BRANCH"
# stmt = ibm_db.exec_immediate(conn, sql)
# data = ibm_db.fetch_both(stmt)
try:
with DB2DBConnection() as db_conn:
# sql = f'''SELECT encoded_id FROM FINAL TABLE (INSERT INTO EVERESTSCHEMA.evre_learning_email_msgs (encoded_id,
# business_segment_id, status, document_name)
# VALUES (GENERATE_UNIQUE(),
# 1,
# 'N',
# 'testkey')
# )
# '''
sql = f'''SELECT ID, HEX(encoded_id) as encoded_id FROM EVERESTSCHEMA.evre_learning_email_msgs'''
stmt = ibm_db.exec_immediate(db_conn, sql)
result = ibm_db.fetch_both(stmt)
msg_document_id = None
if result:
msg_document_id = result["ENCODED_ID"]
print(f'encode_id: {msg_document_id}')
except (ibm_db.conn_error, ibm_db. conn_errormsg, Exception) as err:
logging.exception(err)
def cancel_open_order_query(conn, order_id):
query = "UPDATE trade_history SET STATUS = 'CANCELED' where ORDER_ID_OPEN = " + str(
order_id)
ibm_db.exec_immediate(conn, query)
return None
def insert_data(conn,insert_sql):
'插入数据'
result = ibm_db.exec_immediate(conn,insert_sql)
return result
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205),\
(30, "Megabyte", "98.65"),\
(40, "Visarsoft", Decimal("123.34")),\
(50, "Mailersoft", Decimal("134.222")),\
(60, "Kaerci", Decimal("100.976"))\
)
insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKSHARE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print("%s : %s : %s\n" % (data[0], data[1], data[2]))
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " % str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
#__END__
#__LUW_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__ZOS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__IDS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
def selectDB(params, table, connection):
select = "select " + params + " from " + table
stmt_select = ibm_db.exec_immediate(connection, select)
cols = ibm_db.fetch_tuple(stmt_select)
print("Resultados: ")
print("%s, %s" % (cols[0], cols[1]))
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_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_FORWARD_ONLY
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ibm_db.SQL_ROWCOUNT_PREFETCH_ON
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ibm_db.SQL_ROWCOUNT_PREFETCH_OFF
})
if result:
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 read_table(self):
sql = "SELECT * FROM " + TABLE + ";"
res = self.results(exec_immediate(connection, sql))
return res
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)
def run_test_154(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
try:
statement = 'DROP TABLE fetch_test'
result = ibm_db.exec_immediate(conn, statement)
except:
pass
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
else:
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
result = ibm_db.exec_immediate(conn, statement)
result = ibm_db.exec_immediate(conn, st0)
result = ibm_db.exec_immediate(conn, st1)
result = ibm_db.exec_immediate(conn, st2)
result = ibm_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_tuple(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row[0],row[0].length, row[1],row[1].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row[0], len(row[0]), row[1], len(row[1])))
row = ibm_db.fetch_tuple(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_assoc(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2'])))
row = ibm_db.fetch_assoc(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_both(result)
while (row):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row[1], row[1].length)
print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \
(row['COL1'],len(row['COL1']), row[1], len(row[1])))
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
def run_test_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)))
def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(
conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print("Test first index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST",
True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(
conn,
"CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE INDEX index2 ON index_test2 (data)")
print("Test second index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test2", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST2",
True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
print("Test non-existent table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"non_existent_table", True)
else:
result = ibm_db.statistics(conn, None, None,
"NON_EXISTENT_TABLE", True)
row = ibm_db.fetch_tuple(result)
if row:
print("Non-Empty")
else:
print("Empty")
else:
print('no connection: ' + ibm_db.conn_errormsg())
def main(params):
logging.info('Calling fn_split_pdf.')
try:
cos_everest_submission_bucket = params.get(
"cos_everest_submission_bucket", None)
if cos_everest_submission_bucket is None or "":
raise Exception("Pass location of the bucket")
final_pdf_folder = params.get("final_pdf_folder", None)
if final_pdf_folder is None or "":
raise Exception("Pass pdf folder to split files")
submissions_data_folder = params.get("submissions_data_folder", None)
if submissions_data_folder is None or "":
raise Exception("Pass submissions_data_folder")
submission_id = params.get("submission_id", None)
if submission_id is None or "":
raise Exception("Pass submission_id")
mode = params.get("mode", None)
if mode is None or "":
raise Exception("Pass mode")
object_storage_key = submissions_data_folder + "/" + \
mode + "/" + str(submission_id) + "/"
# + "/" + final_pdf_folder
extensions = ['pdf']
regex = r"^" + object_storage_key + ".*(?i)(pdf).*$"
file_keys = cosutils.get_bucket_contents(cos_everest_submission_bucket,
regex)
print(file_keys)
for key in file_keys:
if key.lower().endswith(tuple(extensions)):
file_name = os.path.basename(key)
file_name_without_ext, file_extension = os.path.splitext(
file_name)
pdf_file_bytes = cosutils.get_item(
cos_everest_submission_bucket, key)
db_conn = db2utils.get_connection()
print("db_conn: {}".format(db_conn))
sql = f'''SELECT ID FROM EVERESTSCHEMA.EVRE_LEARNING_EMAIL_ATTACHMENTS
where EVRE_EMAIL_MSG_ID={submission_id} and DOCUMENT_NAME='{key}' '''
print("sql: {}".format(sql))
stmt = ibm_db.exec_immediate(db_conn, sql)
result = ibm_db.fetch_both(stmt)
pdf_id = -1
if result:
pdf_id = result["ID"]
# read pdf
pdf = PdfFileReader(BytesIO(pdf_file_bytes))
num_of_pages = pdf.getNumPages()
print("num_of_pages:: {} ", num_of_pages)
for page in range(num_of_pages):
pdf_writer = PdfFileWriter()
pdf_writer.addPage(pdf.getPage(page))
split_pdf_dir = "final_pdf_split"
output_filename_key = '{}{}/{}_page_{}.pdf'.format(
object_storage_key, split_pdf_dir,
file_name_without_ext, page + 1)
tmp = BytesIO()
pdf_writer.write(tmp)
tmp.seek(0)
output_page_bytes = tmp.read()
# print("Bytes:: {} ", output_page_bytes)
return_val = cosutils.save_file(
cos_everest_submission_bucket, output_filename_key,
output_page_bytes)
if return_val is "SUCCESS":
print(
"File Uploaded to object storage successfully:: {} ",
output_filename_key)
db_conn = db2utils.get_connection()
print("db_conn: {}".format(db_conn))
sql = f'''SELECT ID FROM FINAL TABLE (INSERT INTO EVERESTSCHEMA.EVRE_LEARNING_SPLIT_CONTENT (EVRE_EMAIL_MSG_ID, EVRE_LEARNING_EMAIL_ATTACHMENTS_ID,
DOCUMENT_NAME, DOCUMENT_TYPE, CLASSIFICATION_TYPE, STATUS, USED_FOR, DESCRIPTION)
VALUES ({submission_id},
{pdf_id},
'{output_filename_key}',
'.pdf',
'N/A',
'N',
'RUNTIME',
'STANDARDIZE_TO_TXT')
)
'''
print("sql: {}".format(sql))
stmt = ibm_db.exec_immediate(db_conn, sql)
result = ibm_db.fetch_both(stmt)
attachment_id = None
if result:
attachment_id = result["ID"]
# end of for loop
db_conn = db2utils.get_connection()
sql = f'''SELECT ID, STATUS, TO_CHAR(FIRST_UPDATED,'YYYY-MM-DD HH.MI.SS') as FIRST_UPDATED,
TO_CHAR(LAST_UPDATED,'YYYY-MM-DD HH.MI.SS') as LAST_UPDATED FROM FINAL TABLE
(UPDATE EVERESTSCHEMA.EVRE_LEARNING_EMAIL_MSGS SET STATUS = 'STANDARDIZE_TO_TXT' where ID = {submission_id})
'''
print("sql: {}".format(sql))
stmt = ibm_db.exec_immediate(db_conn, sql)
result = ibm_db.fetch_assoc(stmt)
result_list = []
if result:
result_list.append(result)
result_dict = {}
result_dict["result"] = result_list
result_dict["status"] = "SUCCESS"
return result_dict
except (ibm_db.conn_error, ibm_db.conn_errormsg, Exception) as err:
logging.exception(err)
result_dict = {}
result_dict["error"] = err
result_dict["status"] = "FAILURE"
return result_dict
return {"result": "Flow should not reach here"}
def run_test_061(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 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)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while (row):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print(str)
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: %s" % ibm_db.conn_errormsg())
dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB" # e.g. "BLUDB"
dsn_port = "50000" # e.g. "50000"
dsn_protocol = "TCPIP" # i.e. "TCPIP"
dsn = ("DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
dsn_protocol, dsn_uid, dsn_pwd)
#print the connection string to check correct values are specified
print(dsn)
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
createQuery = "create table REVIEW(USERNAME VARCHAR(20) PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), REVIEWS VARCHAR(100), SENTIMENT VARCHAR(20))"
#Now fill in the name of the method and execute the statement
createStmt = ibm_db.exec_immediate(conn, createQuery)
ibm_db.close(conn)
def run_test_311(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ibm_db.exec_immediate(conn, insert)
print("Number of inserted rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'"
)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = ibm_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ibm_db.exec_immediate(conn, sql)
print("Number of updated rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'"
)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = ibm_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ibm_db.exec_immediate(conn, sql)
print("Number of deleted rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'"
)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = ibm_db.fetch_both(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)
def run_test_decfloat(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKPRICE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
else:
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205, "100.234"),\
(30, "Megabyte", 98.65, "1002.112"),\
(40, "Visarsoft", 123.34, "1652.345"),\
(50, "Mailersoft", 134.22, "1643.126"),\
(60, "Kaerci", 100.97, "9876.765")\
)
insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = 100.1234
stockprice = "100.567"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
ibm_db.bind_param(stmt, 4, stockprice)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKPRICE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print("%s : %s : %s : %s\n" %
(data[0], data[1], data[2], data[3]))
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " % str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
else:
print("Login successful!\n")
while (user is not None):
print("Welcome to SocialNet, {0}!".format(user[1]))
print_menu()
print(user)
print("\nSocialNet Command Line Interface:\n")
while (True):
command = input("\t-> ")
args = command.split()
if (args[0].lower() in available_commands):
if (args[0].lower() == "view"):
uid_query = "SELECT * FROM User WHERE username = \'{0}\'".format(
' '.join(args[1:]))
uid_stmt = db2.exec_immediate(connection, uid_query)
uid_res = db2.fetch_assoc(uid_stmt)
if (uid_res != False):
friend_id = uid_res['OWNID']
else:
print(
"That user doesn't have an account on SocialNet..."
)
break
owner_query = "SELECT privacy, description FROM PageOwner WHERE ownID = {0}".format(
friend_id)
owner_stmt = db2.exec_immediate(connection, owner_query)
owner_res = db2.fetch_assoc(owner_stmt)
if (owner_res != False):
owner_prv = owner_res['PRIVACY']
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
dsn_protocol, dsn_uid, dsn_pwd)
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 ",
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
def run_test_000(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
server = ibm_db.server_info(conn)
# Drop the animal table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the animal table
animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty',
350.0), (3, 'gold fish', 'Bubbles',
0.1), (4, 'budgerigar', 'Gizmo',
0.2), (5, 'goat', 'Rickety Ride',
9.7), (6, 'llama', 'Sweater', 150))
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
# Drop the test view, in case it exists
drop = 'DROP VIEW anime_cat'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create test view
ibm_db.exec_immediate(
conn, """CREATE VIEW anime_cat AS
SELECT name, breed FROM animals
WHERE id = 0""")
# Drop the animal_pics table
drop = 'DROP TABLE animal_pics'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal_pics table
create = 'CREATE TABLE animal_pics (name VARCHAR(32), picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
# Populate the view table
animals = (
('Spook', 'spook.png'),
('Helmut', 'pic1.jpg'),
)
insert = 'INSERT INTO animal_pics (name, picture) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, insert)
if (not stmt):
print("Attempt to prepare statement failed.")
return 0
for animal in animals:
name = animal[0]
with open(
os.path.dirname(os.path.abspath(__file__)) + '/' +
animal[1], 'rb') as fileHandle:
picture = fileHandle.read()
if (not picture):
print("Could not retrieve picture '%s'" % animal[1])
return 0
ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the department table, in case it exists
drop = 'DROP TABLE department'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the department table
create = 'CREATE TABLE department (deptno CHAR(3) NOT NULL, deptname VARCHAR(29) NOT NULL, mgrno CHAR(6), admrdept CHAR(3) NOT NULL, location CHAR(16))'
result = ibm_db.exec_immediate(conn, create)
# Populate the department table
department = (('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00',
None), ('B01', 'PLANNING', '000020', 'A00', None),
('C01', 'INFORMATION CENTER', '000030', 'A00',
None), ('D01', 'DEVELOPMENT CENTER', None, 'A00', None),
('D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', None),
('D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01',
None), ('E01', 'SUPPORT SERVICES', '000050', 'A00',
None), ('E11', 'OPERATIONS', '000090', 'E01',
None), ('E21', 'SOFTWARE SUPPORT',
'000100', 'E01', None))
insert = 'INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for dept in department:
result = ibm_db.execute(stmt, dept)
# Drop the emp_act table, in case it exists
drop = 'DROP TABLE emp_act'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_act table
create = 'CREATE TABLE emp_act (empno CHAR(6) NOT NULL, projno CHAR(6) NOT NULL, actno SMALLINT NOT NULL, emptime DECIMAL(5,2), emstdate DATE, emendate DATE)'
result = ibm_db.exec_immediate(conn, create)
# Populate the emp_act table
emp_act = (
('000010', 'MA2100', 10, 0.50, '1982-01-01',
'1982-11-01'), ('000010', 'MA2110', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000010', 'AD3100', 10, 0.50,
'1982-01-01', '1982-07-01'),
('000020', 'PL2100', 30, 1.00, '1982-01-01',
'1982-09-15'), ('000030', 'IF1000', 10, 0.50, '1982-06-01',
'1983-01-01'), ('000030', 'IF2000', 10, 0.50,
'1982-01-01', '1983-01-01'),
('000050', 'OP1000', 10, 0.25, '1982-01-01',
'1983-02-01'), ('000050', 'OP2010', 10, 0.75, '1982-01-01',
'1983-02-01'), ('000070', 'AD3110', 10, 1.00,
'1982-01-01', '1983-02-01'),
('000090', 'OP1010', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000100', 'OP2010', 10, 1.00, '1982-01-01',
'1983-02-01'), ('000110', 'MA2100', 20, 1.00,
'1982-01-01', '1982-03-01'),
('000130', 'IF1000', 90, 1.00, '1982-01-01',
'1982-10-01'), ('000130', 'IF1000', 100, 0.50, '1982-10-01',
'1983-01-01'), ('000140', 'IF1000', 90, 0.50,
'1982-10-01', '1983-01-01'),
('000140', 'IF2000', 100, 1.00, '1982-01-01',
'1982-03-01'), ('000140', 'IF2000', 100, 0.50, '1982-03-01',
'1982-07-01'), ('000140', 'IF2000', 110, 0.50,
'1982-03-01', '1982-07-01'),
('000140', 'IF2000', 110, 0.50, '1982-10-01',
'1983-01-01'), ('000150', 'MA2112', 60, 1.00, '1982-01-01',
'1982-07-15'), ('000150', 'MA2112', 180, 1.00,
'1982-07-15', '1983-02-01'),
('000160', 'MA2113', 60, 1.00, '1982-07-15',
'1983-02-01'), ('000170', 'MA2112', 60, 1.00, '1982-01-01',
'1983-06-01'), ('000170', 'MA2112', 70, 1.00,
'1982-06-01', '1983-02-01'),
('000170', 'MA2113', 80, 1.00, '1982-01-01',
'1983-02-01'), ('000180', 'MA2113', 70, 1.00, '1982-04-01',
'1982-06-15'), ('000190', 'MA2112', 70, 1.00,
'1982-02-01', '1982-10-01'),
('000190', 'MA2112', 80, 1.00, '1982-10-01',
'1983-10-01'), ('000200', 'MA2111', 50, 1.00, '1982-01-01',
'1982-06-15'), ('000200', 'MA2111', 60, 1.00,
'1982-06-15', '1983-02-01'),
('000210', 'MA2113', 80, 0.50, '1982-10-01',
'1983-02-01'), ('000210', 'MA2113', 180, 0.50, '1982-10-01',
'1983-02-01'), ('000220', 'MA2111', 40, 1.00,
'1982-01-01', '1983-02-01'),
('000230', 'AD3111', 60, 1.00, '1982-01-01',
'1982-03-15'), ('000230', 'AD3111', 60, 0.50, '1982-03-15',
'1982-04-15'), ('000230', 'AD3111', 70, 0.50,
'1982-03-15', '1982-10-15'),
('000230', 'AD3111', 80, 0.50, '1982-04-15',
'1982-10-15'), ('000230', 'AD3111', 180, 1.00, '1982-10-15',
'1983-01-01'), ('000240', 'AD3111', 70, 1.00,
'1982-02-15', '1982-09-15'),
('000240', 'AD3111', 80, 1.00, '1982-09-15',
'1983-01-01'), ('000250', 'AD3112', 60, 1.00, '1982-01-01',
'1982-02-01'), ('000250', 'AD3112', 60, 0.50,
'1982-02-01', '1982-03-15'),
('000250', 'AD3112', 60, 0.50, '1982-12-01',
'1983-01-01'), ('000250', 'AD3112', 60, 1.00, '1983-01-01',
'1983-02-01'), ('000250', 'AD3112', 70, 0.50,
'1982-02-01', '1982-03-15'),
('000250', 'AD3112', 70, 1.00, '1982-03-15',
'1982-08-15'), ('000250', 'AD3112', 70, 0.25, '1982-08-15',
'1982-10-15'), ('000250', 'AD3112', 80, 0.25,
'1982-08-15', '1982-10-15'),
('000250', 'AD3112', 80, 0.50, '1982-10-15',
'1982-12-01'), ('000250', 'AD3112', 180, 0.50, '1982-08-15',
'1983-01-01'), ('000260', 'AD3113', 70, 0.50,
'1982-06-15', '1982-07-01'),
('000260', 'AD3113', 70, 1.00, '1982-07-01',
'1983-02-01'), ('000260', 'AD3113', 80, 1.00, '1982-01-01',
'1982-03-01'), ('000260', 'AD3113', 80, 0.50,
'1982-03-01', '1982-04-15'),
('000260', 'AD3113', 180, 0.50, '1982-03-01',
'1982-04-15'), ('000260', 'AD3113', 180, 1.00, '1982-04-15',
'1982-06-01'), ('000260', 'AD3113', 180, 0.50,
'1982-06-01', '1982-07-01'),
('000270', 'AD3113', 60, 0.50, '1982-03-01',
'1982-04-01'), ('000270', 'AD3113', 60, 1.00, '1982-04-01',
'1982-09-01'), ('000270', 'AD3113', 60, 0.25,
'1982-09-01', '1982-10-15'),
('000270', 'AD3113', 70, 0.75, '1982-09-01',
'1982-10-15'), ('000270', 'AD3113', 70, 1.00, '1982-10-15',
'1983-02-01'), ('000270', 'AD3113', 80, 1.00,
'1982-01-01', '1982-03-01'),
('000270', 'AD3113', 80, 0.50, '1982-03-01',
'1982-04-01'), ('000280', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000290', 'OP1010', 130, 1.00,
'1982-01-01', '1983-02-01'),
('000300', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000310', 'OP1010', 130, 1.00, '1982-01-01',
'1983-02-01'), ('000320', 'OP2011', 140, 0.75,
'1982-01-01', '1983-02-01'),
('000320', 'OP2011', 150, 0.25, '1982-01-01',
'1983-02-01'), ('000330', 'OP2012', 140, 0.25, '1982-01-01',
'1983-02-01'), ('000330', 'OP2012', 160, 0.75,
'1982-01-01', '1983-02-01'),
('000340', 'OP2013', 140, 0.50, '1982-01-01',
'1983-02-01'), ('000340', 'OP2013', 170, 0.50, '1982-01-01',
'1983-02-01'), ('000020', 'PL2100', 30, 1.00,
'1982-01-01', '1982-09-15'))
insert = 'INSERT INTO emp_act (empno, projno, actno, emptime, emstdate, emendate) VALUES (?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in emp_act:
result = ibm_db.execute(stmt, emp)
# Drop the employee table, in case it exists
drop = 'DROP TABLE employee'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the employee table
create = 'CREATE TABLE employee (empno CHAR(6) NOT NULL, firstnme VARCHAR(12) NOT NULL, midinit CHAR(1) NOT NULL, lastname VARCHAR(15) NOT NULL, workdept CHAR(3), phoneno CHAR(4), hiredate DATE, job CHAR(8), edlevel SMALLINT NOT NULL, sex CHAR(1), birthdate DATE, salary DECIMAL(9,2), bonus DECIMAL(9,2), comm DECIMAL(9,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the employee table
if ('zos' in sys.platform):
employee = (
('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978',
'1965-01-01', 'PRES', 18, 'F', '1933-08-24', 52750.00,
1000, 4220), ('000020', 'MICHAEL', 'L', 'THOMPSON',
'B01', '3476', '1973-10-10', 'MANAGER', 18, 'M',
'1948-02-02', 41250.00, 800, 3300),
('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', '1975-04-05',
'MANAGER', 20, 'F', '1941-05-11', 38250.00,
800, 3060), ('000050', 'JOHN', 'B', 'GEYER',
'E01', '6789', '1949-08-17', 'MANAGER', 16,
'M', '1925-09-15', 40175.00, 800, 3214),
('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', '1973-09-14',
'MANAGER', 16, 'M', '1945-07-07',
32250.00,
500, 2580), ('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831',
'1980-09-30', 'MANAGER', 16, 'F', '1953-05-26',
36170.00, 700, 2893),
('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498',
'1970-08-15', 'MANAGER', 16,
'F', '1941-05-15', 29750.00,
600, 2380), ('000100', 'THEODORE', 'Q', 'SPENSER',
'E21', '0972', '1980-06-19', 'MANAGER',
14, 'M', '1956-12-18', 26150.00, 500, 2092),
('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490',
'1958-05-16', 'SALESREP', 19, 'M', '1929-11-05', 46500.00,
900, 3720), ('000120', 'SEAN', ' ', 'OCONNELL', 'A00', '2167',
'1963-12-05', 'CLERK', 14, 'M', '1942-10-18',
29250.00, 600, 2340),
('000130', 'DOLORES', 'M',
'QUINTANA', 'C01', '4578', '1971-07-28', 'ANALYST', 16, 'F',
'1925-09-15', 23800.00,
500, 1904), ('000140', 'HEATHER', 'A', 'NICHOLLS',
'C01', '1793', '1976-12-15', 'ANALYST', 18,
'F', '1946-01-19', 28420.00, 600, 2274),
('000150', 'BRUCE', ' ', 'ADAMSON', 'D11', '4510',
'1972-02-12', 'DESIGNER', 16, 'M', '1947-05-17', 25280.00,
500, 2022), ('000160', 'ELIZABETH', 'R', 'PIANKA',
'D11', '3782', '1977-10-11', 'DESIGNER', 17,
'F', '1955-04-12', 22250.00, 400, 1780),
('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890',
'1978-09-15', 'DESIGNER', 16, 'M', '1951-01-05', 24680.00,
500, 1974), ('000180', 'MARILYN', 'S', 'SCOUTTEN',
'D11', '1682', '1973-07-07', 'DESIGNER', 17,
'F', '1949-02-21', 21340.00, 500, 1707),
('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986',
'1974-07-26', 'DESIGNER', 16, 'M', '1952-06-25', 20450.00,
400, 1636), ('000200', 'DAVID', ' ', 'BROWN', 'D11', '4501',
'1966-03-03', 'DESIGNER', 16, 'M', '1941-05-29',
27740.00, 600, 2217),
('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942',
'1979-04-11', 'DESIGNER', 17, 'M', '1953-02-23', 18270.00,
400, 1462), ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672',
'1968-08-29', 'DESIGNER', 18, 'F', '1948-03-19',
29840.00, 600, 2387),
('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094',
'1966-11-21', 'CLERK', 14, 'M', '1935-05-30', 22180.00, 400,
1774), ('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780',
'1979-12-05', 'CLERK', 17, 'M', '1954-03-31',
28760.00, 600, 2301),
('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', '1969-10-30',
'CLERK', 15, 'M', '1939-11-12', 19180.00, 400,
1534), ('000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953',
'1975-09-11', 'CLERK', 16, 'F', '1936-10-05',
17250.00, 300,
1380), ('000270', 'MARIA', 'L', 'PEREZ', 'D21',
'9001', '1980-09-30', 'CLERK', 15, 'F',
'1953-05-26', 27380.00, 500, 2190),
('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997',
'1967-03-24', 'OPERATOR', 17, 'F', '1936-03-28', 26250.00,
500, 2100), ('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502',
'1980-05-30', 'OPERATOR', 12, 'M', '1946-07-09',
15340.00, 300,
1227), ('000300', 'PHILIP', 'X', 'SMITH', 'E11',
'2095', '1972-06-19', 'OPERATOR', 14,
'M', '1936-10-27', 17750.00, 400, 1420),
('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332',
'1964-09-12', 'OPERATOR', 12, 'F', '1931-04-21', 15900.00,
300, 1272), ('000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990',
'1965-07-07', 'FIELDREP', 16, 'M', '1932-08-11',
19950.00, 400, 1596),
('000330', 'WING', ' ', 'LEE', 'E21', '2103', '1976-02-23',
'FIELDREP', 14, 'M', '1941-07-18', 25370.00, 500,
2030), ('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698',
'1947-05-05', 'FIELDREP', 16, 'M', '1926-05-17',
23840.00, 500, 1907))
else:
employee = (
('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978',
'1965-01-01', 'PRES', 18, 'F', '1933-08-24', 52750.00,
1000, 4220), ('000020', 'MICHAEL', 'L', 'THOMPSON',
'B01', '3476', '1973-10-10', 'MANAGER', 18, 'M',
'1948-02-02', 41250.00, 800, 3300),
('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', '1975-04-05',
'MANAGER', 20, 'F', '1941-05-11', 38250.00,
800, 3060), ('000050', 'JOHN', 'B', 'GEYER',
'E01', '6789', '1949-08-17', 'MANAGER', 16,
'M', '1925-09-15', 40175.00, 800, 3214),
('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', '1973-09-14',
'MANAGER', 16, 'M', '1945-07-07',
32250.00,
500, 2580), ('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831',
'1980-09-30', 'MANAGER', 16, 'F', '1953-05-26',
36170.00, 700, 2893),
('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498',
'1970-08-15', 'MANAGER', 16,
'F', '1941-05-15', 29750.00,
600, 2380), ('000100', 'THEODORE', 'Q', 'SPENSER',
'E21', '0972', '1980-06-19', 'MANAGER',
14, 'M', '1956-12-18', 26150.00, 500, 2092),
('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490',
'1958-05-16', 'SALESREP', 19, 'M', '1929-11-05', 46500.00,
900, 3720), ('000120', 'SEAN', '', 'OCONNELL', 'A00', '2167',
'1963-12-05', 'CLERK', 14, 'M', '1942-10-18',
29250.00, 600, 2340),
('000130', 'DOLORES', 'M',
'QUINTANA', 'C01', '4578', '1971-07-28', 'ANALYST', 16, 'F',
'1925-09-15', 23800.00,
500, 1904), ('000140', 'HEATHER', 'A', 'NICHOLLS',
'C01', '1793', '1976-12-15', 'ANALYST', 18,
'F', '1946-01-19', 28420.00, 600, 2274),
('000150', 'BRUCE', '', 'ADAMSON', 'D11', '4510',
'1972-02-12', 'DESIGNER', 16, 'M', '1947-05-17', 25280.00,
500, 2022), ('000160', 'ELIZABETH', 'R', 'PIANKA',
'D11', '3782', '1977-10-11', 'DESIGNER', 17,
'F', '1955-04-12', 22250.00, 400, 1780),
('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890',
'1978-09-15', 'DESIGNER', 16, 'M', '1951-01-05', 24680.00,
500, 1974), ('000180', 'MARILYN', 'S', 'SCOUTTEN',
'D11', '1682', '1973-07-07', 'DESIGNER', 17,
'F', '1949-02-21', 21340.00, 500, 1707),
('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986',
'1974-07-26', 'DESIGNER', 16, 'M', '1952-06-25', 20450.00,
400, 1636), ('000200', 'DAVID', '', 'BROWN', 'D11', '4501',
'1966-03-03', 'DESIGNER', 16, 'M', '1941-05-29',
27740.00, 600, 2217),
('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942',
'1979-04-11', 'DESIGNER', 17, 'M', '1953-02-23', 18270.00,
400, 1462), ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672',
'1968-08-29', 'DESIGNER', 18, 'F', '1948-03-19',
29840.00, 600, 2387),
('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094',
'1966-11-21', 'CLERK', 14, 'M', '1935-05-30', 22180.00, 400,
1774), ('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780',
'1979-12-05', 'CLERK', 17, 'M', '1954-03-31',
28760.00, 600, 2301),
('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', '1969-10-30',
'CLERK', 15, 'M', '1939-11-12', 19180.00, 400,
1534), ('000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953',
'1975-09-11', 'CLERK', 16, 'F', '1936-10-05',
17250.00, 300,
1380), ('000270', 'MARIA', 'L', 'PEREZ', 'D21',
'9001', '1980-09-30', 'CLERK', 15, 'F',
'1953-05-26', 27380.00, 500, 2190),
('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997',
'1967-03-24', 'OPERATOR', 17, 'F', '1936-03-28', 26250.00,
500, 2100), ('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502',
'1980-05-30', 'OPERATOR', 12, 'M', '1946-07-09',
15340.00, 300,
1227), ('000300', 'PHILIP', 'X', 'SMITH', 'E11',
'2095', '1972-06-19', 'OPERATOR', 14,
'M', '1936-10-27', 17750.00, 400, 1420),
('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332',
'1964-09-12', 'OPERATOR', 12, 'F', '1931-04-21', 15900.00,
300, 1272), ('000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990',
'1965-07-07', 'FIELDREP', 16, 'M', '1932-08-11',
19950.00, 400, 1596),
('000330', 'WING', '', 'LEE', 'E21', '2103', '1976-02-23',
'FIELDREP', 14, 'M', '1941-07-18', 25370.00, 500,
2030), ('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698',
'1947-05-05', 'FIELDREP', 16, 'M', '1926-05-17',
23840.00, 500, 1907))
insert = 'INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in employee:
result = ibm_db.execute(stmt, emp)
# Drop the emp_photo table, in case it exists
drop = 'DROP TABLE emp_photo'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_photo table
create = 'CREATE TABLE emp_photo (empno CHAR(6) NOT NULL, photo_format VARCHAR(10) NOT NULL, picture BLOB, PRIMARY KEY(empno, photo_format))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
# Populate the emp_photo table
emp_photo = (('000130', 'jpg', 'pic1.jpg'), ('000130', 'png',
'spook.png'),
('000140', 'jpg', 'pic1.jpg'), ('000140', 'png',
'spook.png'),
('000150', 'jpg', 'pic1.jpg'), ('000150', 'png',
'spook.png'),
('000190', 'jpg', 'pic1.jpg'), ('000190', 'png',
'spook.png'))
insert = 'INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for photo in emp_photo:
empno = photo[0]
photo_format = photo[1]
with open(
os.path.dirname(os.path.abspath(__file__)) + '/' +
photo[2], 'rb') as fileHandler:
picture = fileHandler.read()
ibm_db.bind_param(stmt, 1, empno, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, photo_format,
ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 3, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the org table, in case it exists
drop = 'DROP TABLE org'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the org table
create = 'CREATE TABLE org (deptnumb SMALLINT NOT NULL, deptname VARCHAR(14), manager SMALLINT, division VARCHAR(10), location VARCHAR(13))'
result = ibm_db.exec_immediate(conn, create)
# Populate the org table
org = ((10, 'Head Office', 160, 'Corporate',
'New York'), (15, 'New England', 50, 'Eastern', 'Boston'),
(20, 'Mid Atlantic', 10, 'Eastern',
'Washington'), (38, 'South Atlantic', 30, 'Eastern',
'Atlanta'), (42, 'Great Lakes', 100, 'Midwest',
'Chicago'), (51, 'Plains', 140,
'Midwest', 'Dallas'),
(66, 'Pacific', 270, 'Western',
'San Francisco'), (84, 'Mountain', 290, 'Western', 'Denver'))
insert = 'INSERT INTO org (deptnumb, deptname, manager, division, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for orgpart in org:
result = ibm_db.execute(stmt, orgpart)
# Drop the project table, in case it exists
drop = 'DROP TABLE project'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the project table
create = 'CREATE TABLE project (projno CHAR(6) NOT NULL, projname VARCHAR(24) NOT NULL, deptno CHAR(3) NOT NULL, respemp CHAR(6) NOT NULL, prstaff DECIMAL(5,2), prstdate DATE, prendate DATE, majproj CHAR(6))'
result = ibm_db.exec_immediate(conn, create)
# Populate the project table
project = (('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', 6,
'1982-01-01', '1983-02-01', 'AD3100'),
('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2,
'1982-01-01', '1983-02-01', 'AD3110'),
('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1,
'1982-01-01', '1983-02-01', 'AD3110'),
('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2,
'1982-01-01', '1983-02-01',
'AD3110'), ('IF1000', 'QUERY SERVICES', 'C01', '000030', 2,
'1982-01-01', '1983-02-01', None),
('IF2000', 'USER EDUCATION', 'C01', '000030', 1,
'1982-01-01', '1983-02-01',
None), ('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010',
12, '1982-01-01', '1983-02-01', None),
('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9,
'1982-01-01', '1983-02-01', 'MA2100'),
('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2,
'1982-01-01', '1982-12-01',
'MA2110'), ('MA2112', 'W L ROBOT DESIGN', 'D11', '000150',
3, '1982-01-01', '1982-12-01', 'MA2110'),
('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3,
'1982-02-15', '1982-12-01',
'MA2110'), ('OP1000', 'OPERATION SUPPORT', 'E01', '000050',
6, '1982-01-01', '1983-02-01',
None), ('OP1010', 'OPERATION', 'E11', '000090',
5, '1982-01-01', '1983-02-01',
'OP1000'),
('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5,
'1982-01-01', '1983-02-01',
None), ('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4,
'1982-01-01', '1983-02-01',
'OP2000'), ('OP2011', 'SCP SYSTEMS SUPPORT', 'E21',
'000320', 1, '1982-01-01',
'1983-02-01', 'OP2010'),
('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1,
'1982-01-01', '1983-02-01',
'OP2010'), ('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1,
'1982-01-01', '1983-02-01',
'OP2010'), ('PL2100', 'WELD LINE PLANNING',
'B01', '000020', 1, '1982-01-01',
'1982-09-15', 'MA2100'))
if ('zos' in sys.platform):
project = (('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5,
'1982-01-01', '1983-02-01', None), ) + project
else:
project = (('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5,
'1982-01-01', '1983-02-01', ''), ) + project
insert = 'INSERT INTO project (projno, projname, deptno, respemp, prstaff, prstdate, prendate, majproj) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for proj in project:
result = ibm_db.execute(stmt, proj)
# Drop the sales table, in case it exists
drop = 'DROP TABLE sales'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the sales table
create = 'CREATE TABLE sales (sales_date DATE, sales_person VARCHAR(15), region VARCHAR(15), sales INT)'
result = ibm_db.exec_immediate(conn, create)
# Populate the sales table
sales = (('1995-12-31', 'LUCCHESSI', 'Ontario-South',
1), ('1995-12-31', 'LEE', 'Ontario-South',
3), ('1995-12-31', 'LEE', 'Quebec',
1), ('1995-12-31', 'LEE', 'Manitoba',
2), ('1995-12-31', 'GOUNOT', 'Quebec', 1),
('1996-03-29', 'LUCCHESSI', 'Ontario-South',
3), ('1996-03-29', 'LUCCHESSI', 'Quebec',
1), ('1996-03-29', 'LEE', 'Ontario-South',
2), ('1996-03-29', 'LEE', 'Ontario-North',
2), ('1996-03-29', 'LEE', 'Quebec',
3), ('1996-03-29', 'LEE', 'Manitoba', 5),
('1996-03-29', 'GOUNOT', 'Ontario-South',
3), ('1996-03-29', 'GOUNOT', 'Quebec',
1), ('1996-03-29', 'GOUNOT', 'Manitoba',
7), ('1996-03-30', 'LUCCHESSI', 'Ontario-South',
1), ('1996-03-30', 'LUCCHESSI', 'Quebec', 2),
('1996-03-30', 'LUCCHESSI', 'Manitoba',
1), ('1996-03-30', 'LEE', 'Ontario-South',
7), ('1996-03-30', 'LEE', 'Ontario-North',
3), ('1996-03-30', 'LEE', 'Quebec',
7), ('1996-03-30', 'LEE', 'Manitoba', 4),
('1996-03-30', 'GOUNOT', 'Ontario-South',
2), ('1996-03-30', 'GOUNOT', 'Quebec',
18), ('1996-03-30', 'GOUNOT', 'Manitoba',
1), ('1996-03-31', 'LUCCHESSI', 'Manitoba', 1),
('1996-03-31', 'LEE', 'Ontario-South',
14), ('1996-03-31', 'LEE', 'Ontario-North',
3), ('1996-03-31', 'LEE', 'Quebec',
7), ('1996-03-31', 'LEE', 'Manitoba',
3), ('1996-03-31', 'GOUNOT', 'Ontario-South',
2), ('1996-03-31', 'GOUNOT', 'Quebec',
1), ('1996-04-01', 'LUCCHESSI',
'Ontario-South', 3),
('1996-04-01', 'LUCCHESSI', 'Manitoba',
1), ('1996-04-01', 'LEE', 'Ontario-South',
8), ('1996-04-01', 'LEE', 'Ontario-North',
None), ('1996-04-01', 'LEE', 'Quebec',
8), ('1996-04-01', 'LEE', 'Manitoba', 9),
('1996-04-01', 'GOUNOT', 'Ontario-South',
3), ('1996-04-01', 'GOUNOT', 'Ontario-North',
1), ('1996-04-01', 'GOUNOT', 'Quebec',
3), ('1996-04-01', 'GOUNOT', 'Manitoba', 7))
insert = 'INSERT INTO sales (sales_date, sales_person, region, sales) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for sale in sales:
result = ibm_db.execute(stmt, sale)
# Drop the stored procedure, in case it exists
drop = 'DROP PROCEDURE match_animal'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the stored procedure
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(
conn, """
CREATE PROCEDURE match_animal(first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE PRECISION )
DEFINE match_name INT;
LET match_name = 0;
FOREACH c1 FOR
SELECT COUNT(*) INTO match_name FROM animals
WHERE name IN (second_name)
IF (match_name > 0)
THEN LET second_name = 'TRUE';
END IF;
END FOREACH;
FOREACH c2 FOR
SELECT SUM(weight) INTO animal_weight FROM animals WHERE name in (first_name, second_name)
END FOREACH;
END PROCEDURE;""")
else:
result = ibm_db.exec_immediate(
conn, """
CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE match_name INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT COUNT(*) FROM animals
WHERE name IN (second_name);
DECLARE c2 CURSOR FOR
SELECT SUM(weight) FROM animals
WHERE name in (first_name, second_name);
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, breed, weight FROM animals
WHERE name BETWEEN first_name AND second_name
ORDER BY name;
OPEN c1;
FETCH c1 INTO match_name;
IF (match_name > 0)
THEN SET second_name = 'TRUE';
END IF;
CLOSE c1;
OPEN c2;
FETCH c2 INTO animal_weight;
CLOSE c2;
OPEN c3;
END""")
result = None
# Drop the staff table, in case it exists
drop = 'DROP TABLE staff'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the staff table
create = 'CREATE TABLE staff (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT, job CHAR(5), years SMALLINT, salary DECIMAL(7,2), comm DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the staff table
staff = ((10, 'Sanders', 20, 'Mgr', 7, 18357.50,
None), (20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45),
(30, 'Marenghi', 38, 'Mgr', 5, 17506.75,
None), (40, 'OBrien', 38, 'Sales', 6, 18006.00, 846.55),
(50, 'Hanes', 15, 'Mgr', 10, 20659.80,
None), (60, 'Quigley', 38, 'Sales', None, 16808.30, 650.25),
(70, 'Rothman', 15, 'Sales', 7, 16502.83,
1152.00), (80, 'James', 20, 'Clerk', None, 13504.60, 128.20),
(90, 'Koonitz', 42, 'Sales', 6, 18001.75,
1386.70), (100, 'Plotz', 42, 'Mgr', 7, 18352.80,
None), (110, 'Ngan', 15, 'Clerk', 5, 12508.20,
206.60), (120, 'Naughton', 38, 'Clerk',
None, 12954.75, 180.00),
(130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90,
75.60), (140, 'Fraye', 51, 'Mgr', 6, 21150.00,
None), (150, 'Williams', 51, 'Sales', 6, 19456.50,
637.65), (160, 'Molinare', 10, 'Mgr', 7,
22959.20, None),
(170, 'Kermisch', 15, 'Clerk', 4, 12258.50,
110.10), (180, 'Abrahams', 38, 'Clerk', 3, 12009.75,
236.50), (190, 'Sneider', 20, 'Clerk', 8, 14252.75,
126.50), (200, 'Scoutten', 42, 'Clerk',
None, 11508.60, 84.20),
(210, 'Lu', 10, 'Mgr', 10, 20010.00,
None), (220, 'Smith', 51, 'Sales', 7, 17654.50,
992.80), (230, 'Lundquist', 51, 'Clerk', 3, 13369.80,
189.65), (240, 'Daniels', 10, 'Mgr', 5,
19260.25, None),
(250, 'Wheeler', 51, 'Clerk', 6, 14460.00,
513.30), (260, 'Jones', 10, 'Mgr', 12, 21234.00,
None), (270, 'Lea', 66, 'Mgr', 9, 18555.50, None),
(280, 'Wilson', 66, 'Sales', 9, 18674.50,
811.50), (290, 'Quill', 84, 'Mgr', 10, 19818.00,
None), (300, 'Davis', 84, 'Sales', 5, 15454.50,
806.10), (310, 'Graham', 66, 'Sales', 13,
21000.00, 200.30),
(320, 'Gonzales', 66, 'Sales', 4, 16858.20,
844.00), (330, 'Burke', 66, 'Clerk', 1, 10988.00,
55.50), (340, 'Edwards', 84, 'Sales', 7, 17844.00,
1285.00), (350, 'Gafney', 84, 'Clerk', 5,
13030.50, 188.00))
insert = 'INSERT INTO staff (id, name, dept, job, years, salary, comm) VALUES (?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in staff:
result = ibm_db.execute(stmt, emp)
try:
result = ibm_db.exec_immediate(conn, 'DROP TABLE t_string')
except:
pass
result = ibm_db.exec_immediate(
conn,
'CREATE TABLE t_string(a INTEGER, b DOUBLE PRECISION, c VARCHAR(100))'
)
print("Preperation complete")
def run_test_200(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
server = serverinfo.DBMS_NAME[0:3]
if (server == 'IDS'):
procedure = """
CREATE FUNCTION multiResults()
RETURNING CHAR(16), INT;
DEFINE p_name CHAR(16);
DEFINE p_id INT;
FOREACH c1 FOR
SELECT name, id
INTO p_name, p_id
FROM animals
ORDER BY name
RETURN p_name, p_id WITH RESUME;
END FOREACH;
END FUNCTION;
"""
else:
procedure = """
CREATE PROCEDURE multiResults ()
RESULT SETS 3
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
ORDER BY name;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE id < 4
ORDER BY name DESC;
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE weight < 5.0
ORDER BY name;
OPEN c1;
OPEN c2;
OPEN c3;
END
"""
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ibm_db.exec_immediate(conn, procedure)
stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = ibm_db.fetch_tuple(stmt)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
if (server == 'IDS'):
print "Fetching second result set (should fail -- IDS does not support multiple result sets)"
else:
print "Fetching second result set"
res = ibm_db.next_result (stmt)
if res:
row = ibm_db.fetch_tuple(res)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(res)
if (server == 'IDS'):
print "Fetching third result set (should fail -- IDS does not support multiple result sets)"
else:
print "Fetching third result set"
res2 = ibm_db.next_result(stmt)
if res2:
row = ibm_db.fetch_tuple(res2)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(res2)
print "Fetching fourth result set (should fail)"
res3 = ibm_db.next_result(stmt)
if res3:
row = ibm_db.fetch_tuple(res3)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(res3)
ibm_db.close(conn)
else:
print "Connection failed."
if len(ret) > 0:
#We have to think about event order when processing
cVal = ret[len(ret) - 1][ref]
for e in ret:
self.generate(e)
except Exception,e:
logger.error("Error building the event: %s" %(str(e)))
time.sleep(tSleep)
else:
self.error ("Couldn't connect to database, maximum retries exceeded")
return
else:
sql = rules['query']['query']
sql = sql.replace("$1", str(cVal))
logger.debug(sql)
result = ibm_db.exec_immediate(self.__objDBConn, sql)
row = ibm_db.fetch_tuple(result)
ret = []
while row:
logger.info(str(row))
ret.append(row)
row = ibm_db.fetch_tuple(result)
logger.info("len ret %s y ref %s" % (len(ret),ref))
if len(ret) > 0:
cVal = ret[len(ret) - 1][ref]
for e in ret:
logger.info("-.-->", e)
self.generate(e)
time.sleep(tSleep)
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')
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.122.107.243;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;", "", "")
#prodqa
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.108.240.66;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#wdc
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.108.216.4;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#dal
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.142.234.144;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#SYD
conn = ibm_db.connect(
"DATABASE=TLSAAS;HOSTNAME=10.138.55.105;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;",
"", "")
#FRA
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.134.194.133;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#sqlorgs = "select schemaname from syscat.schemata where schemaname like 'ORG%'"
sqlorgs = "select schemaname from syscat.schemata"
stmtorgs = ibm_db.exec_immediate(conn, sqlorgs)
resultorgs = ibm_db.fetch_both(stmtorgs)
while resultorgs != False:
orgname = resultorgs["SCHEMANAME"]
try:
sqlQryEvents = "select sharetype, COUNT(*) from " + orgname + ".workspace group by sharetype order by sharetype"
stmtQryEvents = ibm_db.exec_immediate(conn, sqlQryEvents)
eventCount = ibm_db.fetch_tuple(stmtQryEvents)
count0 = 0
count1 = 0
count2 = 0
while eventCount != False:
if eventCount[0] == 0: count0 = eventCount[1]
if eventCount[0] == 1: count1 = eventCount[1]
if eventCount[0] == 2: count2 = eventCount[1]
def delete():
mode = request.args.get('dmode1') if request.args.get(
'dmode1') else request.args.get('dmode2')
mode = mode.split(' ')[-1]
start = time.time()
etime = request.args.get('time') if request.args.get('time') else 'None'
lat = request.args.get('lat') if request.args.get('lat') else -1
lon = request.args.get('lon') if request.args.get('lon') else -1
dep = request.args.get('dep') if request.args.get('dep') else -1
mag = request.args.get('mag') if request.args.get('mag') else -1
magtype = request.args.get('magtype') if request.args.get(
'magtype') else 'None'
nst = request.args.get('nst') if request.args.get('nst') else -1
gap = request.args.get('gap') if request.args.get('gap') else -1
dmin = request.args.get('dmin') if request.args.get('dmin') else -1
rms = request.args.get('rms') if request.args.get('rms') else -1
net = request.args.get('net') if request.args.get('net') else 'None'
eid = request.args.get('id') if request.args.get('id') else 'None'
updated = request.args.get('updated') if request.args.get(
'updated') else 'None'
place = request.args.get('place') if request.args.get('place') else 'None'
etype = request.args.get('type') if request.args.get('type') else 'None'
he = request.args.get('he') if request.args.get('he') else -1
de = request.args.get('de') if request.args.get('de') else -1
me = request.args.get('me') if request.args.get('me') else -1
mn = request.args.get('mn') if request.args.get('mn') else -1
status = request.args.get('status') if request.args.get(
'status') else 'None'
locsource = request.args.get('locsource') if request.args.get(
'locsource') else 'None'
magsource = request.args.get('magsource') if request.args.get(
'magsource') else 'None'
params = {
"TIME": etime,
"Latitude": lat,
"LONGTITUDE": lon,
"DEPTH": dep,
"MAG": mag,
"MAGTYPE": magtype,
"NST": nst,
"GAP": gap,
"DMIN": dmin,
"RMS": rms,
"NET": net,
"ID": eid,
"UPDATED": updated,
"PLACE": place,
"TYPE": etype,
"HOTIZONTALERROR": he,
"DEPTHERROR": de,
"MAGERROR": me,
"MAGNST": mn,
"STATUS": status,
"LOCATIONSOURCE": locsource,
"MAGSOURCE": magsource,
}
params = [(k, v) for k, v in params.items() if v not in [-1, 'None']]
sub_sql = ', '.join([p[0] + '=' + str(p[1]) for p in params])
if mode == 'RDB':
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = """DELETE FROM EARTHQUAKE WHERE {}""".format(sub_sql)
ibm_db.exec_immediate(db2conn, sql)
elif mode == 'Memcache':
for p in params:
global cache_csv
cache_csv = cache_csv[cache_csv[p[0]] != p[1]]
end = time.time()
elapse = end - start
return render_template('index.html', app=appenv, delete_elp=elapse)
def main():
# Parse the input arguments
parser = argparse.ArgumentParser()
parser.add_argument("-d","-dbname", dest="dbname", required=True, help = "Target database to load the data into")
parser.add_argument("-sourcePath", dest="sourcePath", required=True, help = "Path containing output from Historical Monitorinig. E.g. /home/db2inst1/sqllib/db2dump/IBMHIST_SAMPLE")
parser.add_argument("-dataCollectionName", dest="dataCollectionName", help = "Data collection name, as defined in the task_details.json file")
parser.add_argument("-startDate", dest="startDate", help = "Start timestamp (Default: localtime) (Must be of format YYYY-MM-DD-hh.mm.ss)")
parser.add_argument("-endDate", dest="endDate", help = "End timestamp (Default: localtime) (Must be of format YYYY-MM-DD-hh.mm.ss)")
args = parser.parse_args()
sourcePath = Path(args.sourcePath)
# Check if the raw data path exists
if not sourcePath.exists():
print("Raw data folder is not found: ", sourcePath)
exit(1)
print("Connecting to database:", args.dbname)
conn = ibm_db.connect(args.dbname, '', '')
# Set up preconditions
createDBObjects(conn)
# When -startDate and -endDate are specified, filter the qualified raw files
startDate, endDate = args.startDate, args.endDate
if startDate and endDate:
startTs = time.mktime(time.strptime(startDate, '%Y-%m-%d-%H.%M.%S'))
startHour = time.mktime(time.strptime(startDate[:startDate.find('.')], '%Y-%m-%d-%H'))
endTs = time.mktime(time.strptime(endDate, '%Y-%m-%d-%H.%M.%S'))
endHour = time.mktime(time.strptime(endDate[:endDate.find('.')], '%Y-%m-%d-%H'))
hourDirList = [hourDir for hourDir in sourcePath.glob('*_??????????') if startHour <= time.mktime(time.strptime(hourDir.name[-10:], '%Y%m%d%H')) <= endTs ]
else:
hourDirList = [hourDir for hourDir in sourcePath.glob('*_??????????')]
if not hourDirList:
print("No hourly directory found in {}".format(sourcePath))
exit(1)
# Load task details from json file
searchTaskDetailFile = [file for file in hourDirList[0].glob(taskDetailFileName)]
if searchTaskDetailFile:
taskDetailFile = searchTaskDetailFile.pop()
else:
print("Task details file {} is not found under {}".format(taskDetailFileName, hourDirList[0]))
exit(1)
print("Loading tasks from task_details.json ...")
with open(taskDetailFile) as file:
tasks = json.load(file)
# For each SQL task, import the data and delta data into tables if needed
for task in tasks:
collectionName = task['collection_name']
if args.dataCollectionName and args.dataCollectionName != collectionName:
continue
if task['collection_class'] == "SQL":
# Find raw data files
fileList = []
if startDate and endDate:
files = list(sourcePath.rglob(collectionName + "*.del"))
for file in files:
tsPattern = re.search("[0-9]{12}", file.name)
if tsPattern:
fileTs = time.mktime(time.strptime(tsPattern.group(0), "%Y%m%d%H%M"))
if startTs <= fileTs <= endTs:
fileList.append(file)
if not fileList:
print("No raw data file found for {} with the time range from {} to {}".format(collectionName, startDate, endDate))
continue
else:
fileList = list(sourcePath.rglob(collectionName + "*.del"))
if not fileList:
print("No raw data file found for {}".format(collectionName))
continue
tableName = schemaName + '.' + collectionName
deltaTableName = tableName + delExt
# Create the data table
print("Creating the data table:",tableName)
createTable = "create table {} as ( {} ) WITH NO DATA NOT LOGGED INITIALLY IN {}".format(tableName, task['collection_command'], monTSName)
stmt = ibm_db.exec_immediate(conn, createTable)
# Load data into table
print("Loading data into", tableName)
for file in fileList:
importCmd = "CALL SYSPROC.ADMIN_CMD('import from {} of del insert into {}' )".format(file, tableName)
stmt = ibm_db.exec_immediate(conn, importCmd)
# Check whether we need to create and load the delta table
if task['loader_diff_exempt_columns'] != "ALL":
print("Creating the delta data table:",deltaTableName)
createDeltaTable = "create table {} as ( {} ) WITH NO DATA NOT LOGGED INITIALLY IN {}".format(deltaTableName, task['collection_command'], monTSName)
stmt = ibm_db.exec_immediate(conn, createDeltaTable)
# Read column names and types from the describe command
desCmd = "CALL SYSPROC.ADMIN_CMD('describe table {} show detail' )".format(deltaTableName)
tabDes = ibm_db.exec_immediate(conn, desCmd)
loadDeltaStmt = "insert into {} (".format(deltaTableName)
alterColList, colList = [], ""
exemptionColList = [col.strip() for col in task['loader_diff_exempt_columns'].split(',')]
joinColumns = [col.strip() for col in task['loader_join_columns'].split(",") ]
# The array should never be empty unless somebody forgot to specify
# "loader_join_columns" in the JSON file, but never hurts to check
if joinColumns:
# Despite not empty, the array may still contain only one element, and
# that could be a null string ("loader_join_columns": "")
if joinColumns[0]:
orderByList = ','.join(joinColumns + [collectionTimeColName])
else:
orderByList = collectionTimeColName
else:
orderByList = collectionTimeColName
tuple = ibm_db.fetch_tuple(tabDes)
while tuple != False:
colName, colType = tuple[0], tuple[2]
loadDeltaStmt = "{} {},".format(loadDeltaStmt, colName)
if colType in ["TIMESTAMP", "BIGINT"] and colName not in exemptionColList and colName != collectionTimeColName:
if colType == "TIMESTAMP":
alterColList.append(colName)
colList="{} COALESCE(TIMESTAMPDIFF(2, current.{} - previous.{}), 0),".format(colList, colName, colName)
else:
colList="{} COALESCE(current.{} - previous.{}, 0),".format(colList, colName, colName)
else:
colList = "{} current.{},".format(colList, colName)
tuple = ibm_db.fetch_tuple(tabDes)
# Alter the column data type from TIMESTAMP to BIGINT
for col in alterColList:
stmt = ibm_db.exec_immediate(conn,"alter table {} alter column {} set data type bigint".format(deltaTableName, col))
stmt = ibm_db.exec_immediate(conn, "CALL SYSPROC.ADMIN_CMD('reorg table {}' )".format(deltaTableName))
stmt = ibm_db.exec_immediate(conn, "commit")
# Remove the last comma and append the closing bracket
loadDeltaStmt = loadDeltaStmt.rstrip(',') + ")"
colList = colList.rstrip(',')
loadDeltaStmt = "{} with current as ( SELECT ( row_number() over ( order by {} ) ) rowId, \
{}.* from {} order by {} ) select ".format(loadDeltaStmt, orderByList, tableName, tableName, orderByList)
# Append the column list
loadDeltaStmt += colList
# Append the join clause
loadDeltaStmt = "{} FROM current AS previous RIGHT JOIN current ON previous.rowId + 1 = current.rowId ".format(loadDeltaStmt)
for col in joinColumns:
# col can still be an empty string ("loader_join_columns": "")
if col:
loadDeltaStmt = "{} and previous.{} = current.{} ".format(loadDeltaStmt, col, col)
# Load the delta data into table
print("Loading data into", deltaTableName)
stmt = ibm_db.exec_immediate(conn, loadDeltaStmt)
print("Closing connection ...")
ibm_db.close(conn)
