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.")
def searchincountryname(latitude1=None,longitude1=None,latitude2=None,longitude2=None):
try:
longitude1 = float(request.form['longitude1'])
latitude1 = float(request.form['latitude1'])
longitude2 = float(request.form['longitude2'])
latitude2 = float(request.form['latitude2'])
Query_1="select NAME from LATLONG where \"LATITUDE\" BETWEEN ? and ? and \"LONGITUDE\" BETWEEN ? and ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(latitude1))
ibm_db.bind_param(select_Stmt, 2, str(latitude2))
ibm_db.bind_param(select_Stmt, 3, str(longitude1))
ibm_db.bind_param(select_Stmt, 4, str(longitude2))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchincountryname.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def searchwithmag(latitude1=None,longitude1=None,latitude2=None,longitude2=None):
try:
longitude1 = float(request.form['longitude1'])
latitude1 = float(request.form['latitude1'])
longitude2 = float(request.form['longitude2'])
latitude2 = float(request.form['latitude2'])
mag = float(request.form['mag'])
Query_1="select PLACE, LATITUDE, LONGITUDE, TIME from QUAKES where \"LATITUDE\" BETWEEN ? and ? and \"LONGITUDE\" BETWEEN ? and ? and \"MAG\" >= ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(latitude1))
ibm_db.bind_param(select_Stmt, 2, str(latitude2))
ibm_db.bind_param(select_Stmt, 3, str(longitude1))
ibm_db.bind_param(select_Stmt, 4, str(longitude2))
ibm_db.bind_param(select_Stmt, 5, str(mag))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchwithmag.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def searchwithradius(latitude=None,longitude=None,radius=None):
try:
longitude = float(request.form['longitude'])
latitude = float(request.form['latitude'])
radius = float(request.form['radius'])
lat1 = latitude - (radius / 69)
lat2 = latitude + (radius / 69)
long1 = longitude - (radius / 69)
long2 = longitude + (radius / 69)
Query_1="select * from QUAKES where \"LATITUDE\" > ? and \"LATITUDE\" < ? and \"LONGITUDE\" > ? and \"LONGITUDE\" < ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(lat1))
ibm_db.bind_param(select_Stmt, 2, str(lat2))
ibm_db.bind_param(select_Stmt, 3, str(long1))
ibm_db.bind_param(select_Stmt, 4, str(long2))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchwithradius.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def getnames(name=None):
try:
if request.method == "POST":
mag = request.form['mag']
#connect to db
conn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")
if conn:
print("in if loop")
sql='select * from RZG77856.ALL_MONTH where "MAG">?'
prep = ibm_db.prepare(conn,sql)
ibm_db.bind_param(prep, 1, mag)
ibm_db.execute(prep)
rows = []
print("conn 2")
# fetching the result
result = ibm_db.fetch_assoc(prep)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(prep)
# close database connection
ibm_db.close(conn)
print("conn 3")
return render_template('cresult.html', rows=rows)
else:
print("no connection established")
return render_template('main.html')
except Exception as e:
print(e)
return "<html><body><p>In Exception</p></body></html>"
def run_test_018(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
if conn:
stmt = ibm_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
def getTeamScoresFromRouteID(routeID):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT t.team_name, s.value"
" FROM score s"
" INNER JOIN team t"
" ON s.team_id = t.team_id"
" WHERE route_id = '" + routeID + "' ORDER BY s.value DESC;")
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print("These are the team scores", rows)
return rows
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 getTutorNameFromStudentID(studentID):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT t.TUTOR_NAME"
" FROM TUTOR AS t, STUDENT AS s"
" WHERE s.STUDENT_ID = " + str(studentID) +
" AND t.TUTOR_ID = s.TUTOR_ID;")
#print(sql)
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print(rows)
return rows
def getStudent(tutor):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT s.name, s.email"
" FROM student s"
" INNER JOIN tutor t"
" ON s.tutor_id = t.tutor_id"
" WHERE tutor_name = '" + tutor + "';")
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print(rows)
return rows
def getTutorPassword(tutorEmail):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT p.password"
" FROM tutor_password p"
" INNER JOIN tutor t"
" ON t.tutor_id = p.tutor_id"
" WHERE email = '" + tutorEmail + "';")
#print(sql)
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print(rows)
return rows
def getTutorID(tutorName, tutorEmail):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT TUTOR_ID"
" FROM TUTOR"
" WHERE tutor_name = '" + tutorName + "'OR email = '" +
tutorEmail + "';")
#print(sql)
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print(rows)
return rows
def getLocation(routeID, progress):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT l.location_id,l.location_image_url"
" FROM location l"
" INNER JOIN route_location_bridge r"
" ON l.location_id = r.location_id"
" WHERE route_id = " + str(routeID) + " AND sequence_order = " +
str(progress) + ";")
#sql1 = ("SELECT l.location_id FROM location AS l, route_location_bridge AS r WHERE "
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print('CHECK!!! __ ',rows)
return rows
def getQuestionLocationID(locationID):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = (
"SELECT question_content,MULTIPLE_CHOICE_A,MULTIPLE_CHOICE_B,MULTIPLE_CHOICE_C,MULTIPLE_CHOICE_D,ANSWER"
" FROM question"
" WHERE location_id = " + str(locationID) + ";")
#print("getQuestion ",sql)
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print("Get Question Result",rows)
return rows
def run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def getOfficeLocation(officeID):
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = ("SELECT office_name, location_id"
" FROM office"
" WHERE office_id = " + str(officeID) + ";")
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
# #print to screen the result
#print(rows)
return rows
def run_test_114(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate(conn, drop)
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
ibm_db.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral")
ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while (result):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
result = ibm_db.fetch_row(stmt)
else:
print("Connection failed.")
def run_test_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 query_db(self, query, args=()):
"""Submits database query.
Examples:
for user in query_db('select * from users'):
print user['username'], 'has the id', user['user_id']
for user in query_db('select * from users where username = ?', [the_username]):
print user['username'], 'has the id', user['user_id']
Returns list
list = list of rows, where each row is represented using tuple
"""
rows = []
if self._conn:
log.debug("Running query\n" + query)
log.debug("Query params: " + pprint.pformat(args))
stmt = ibm_db.prepare(self._conn, query)
for i, param in enumerate(args):
ibm_db.bind_param(stmt, i, param)
ibm_db.execute(stmt)
if re.search('create|insert|update|delete', query, re.I):
return rows
row = ibm_db.fetch_tuple(stmt)
while (row):
rows.append(row)
row = ibm_db.fetch_tuple(stmt)
return rows
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 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_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 largest_n():
mode = request.args.get('mode1') if request.args.get(
'mode1') else request.args.get('mode2')
mode = mode.split(' ')[1]
start = time.time()
number = request.args.get('number') if request.args.get('number') else 5
if mode == 'RDB':
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE ORDER BY MAG DESC FETCH FIRST ? ROWS ONLY;"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, number)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
elif mode == 'Memcache':
tmp = cache_csv.sort_values(by='MAG', ascending=False)
rows = tmp[:int(number)].reset_index().to_dict(orient='records')
end = time.time()
elapse = end - start
return render_template('large_n.html', ci=rows, elapse=elapse)
def delete_model(self, model_name):
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s where ENTITY_TYPE_ID = ? and MODEL_NAME = ?" % (
self.quoted_schema, self.quoted_store_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (self.quoted_schema, self.quoted_store_tablename)
sql_statement += ' where entity_type_id = %s and model_name = %s'
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement, (self.entity_type_id, model_name))
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
logger.info('Model %s has been deleted from table %s.%s' % (
model_name, self.quoted_schema, self.quoted_store_tablename))
def get_db_list(admin_db, user):
global global_db_list
db_list = []
tLock.acquire()
print("db_list has acquired a lock")
# Get password
str = open('C:/Users/sxk11/PycharmProjects/Safari_1/test.txt', 'r').read()
conn = ibm_db.connect('ADM1P', 'tu01945', str)
sql = 'SELECT * FROM DB2ADM1P.UDB_DB_NAMES ' \
'where ' \
'db_all_chk_ind = \'Y\' ' \
'and db_alias in (\'LSS1D\', \'EDT5D\', \'EDT6D\', \'DBI1D\') '
# 'db_status = \'D\' '\
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
print("The ID is : ", tuple[0])
print("The alias name is : ", tuple[3])
db_name = tuple[2]
db_list.append(db_name)
tuple = ibm_db.fetch_tuple(stmt)
print(db_list)
tLock.release()
print("db_list has completed lock released")
global_db_list = db_list
def Employer():
if request.method=='POST':
conn.tables('DPS35835', 'EMPLOYER')
conn_str='database=BLUDB;hostname=dashdb-txn-sbox-yp-dal09-08.services.dal.bluemix.net;port=50000;protocol=tcpip;uid=dps35835;pwd=PleaseGoCovid19@2020'
ibm_db_conn = ibm_db.connect(conn_str,'','')
conn = ibm_db_dbi.Connection(ibm_db_conn)
NAME = str(request.form.get('Name'))
USERNAME = str(request.form.get('Username'))
EMAIL_ID = str(request.form.get('Email'))
SECTOR_TYPE = str(request.form.get('sectType'))
SECTORNAME = str(request.form.get('SectName'))
COMPANY_NAME = str(request.form.get('CompName'))
POSITION = str(request.form.get('Position'))
COMPANY_SIZE = str(request.form.get('CompSize'))
# COMPANY_SIZE = int(COMPANY_SIZE)
PHONE = str(request.form.get('Phone'))
WEBSITE = str(request.form.get('Website'))
PASSWORD = str(request.form.get('Password'))
# entry=EMPLOYER(NAME = NAME, USERNAME = Us,PASSWORD=Password, COMPANY_NAME=CompName,COMPANY_SIZE=CompSize, WEBSITE=Website,POSITION=Position,SECTOR_TYPE=SectType,SECTORNAME=SectName)
insert = "insert into EMPLOYER values("+NAME+","+USERNAME+","+EMAIL_ID+","+PASSWORD+","+COMPANY_NAME+","+COMPANY_SIZE+","+POSITION+","+PHONE+","+SECTORNAME+","+SECTOR_TYPE+","+WEBSITE+")"
# params = (NAME,USERNAME,EMAIL_ID,PASSWORD,COMPANY_NAME,COMPANY_SIZE,POSITION,PHONE,SECTORNAME,SECTOR_TYPE,WEBSITE)
# print(params)
cur = conn.cursor()
cur.execute(insert)
stmt_insert = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt_insert,params)
return render_template('Employer.html')
def update_caption_name():
name = str(request.form["name"])
keyword = str(request.form["keyword"])
if name == "" or keyword == "":
result_dict = {}
result_dict["RESULT"] = 0
return render_template('update_caption.html', result=result_dict)
sql = "UPDATE names SET KEYWORDS = ? WHERE NAME=?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, keyword)
ibm_db.bind_param(stmt, 2, name)
result = ibm_db.execute(stmt)
result = ibm_db.num_rows(stmt)
sql = "SELECT * FROM names WHERE NAME = ?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, name)
result = ibm_db.execute(stmt)
result_dict = ibm_db.fetch_assoc(stmt)
print(result_dict)
result_dict["RESULT"] = result
return render_template('update_caption.html', result=result_dict)
def saveProject(email, context):
projectID = context['id']
insert = "insert into Project values(?,?)"
params = (email, projectID)
stmt = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt, params)
insert = "insert into Target values(?,?)"
params = (projectID, context['user'])
stmt = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt, params)
insert = "insert into ProjectName values(?,?)"
params = (projectID, context['project_name'])
stmt = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt, params)
insertTwo(projectID, "EmpathyThink", context['empathy_think'])
insertTwo(projectID, "EmpathyFeel", context['empathy_feel'])
insertTwo(projectID, "EmpathySay", context['empathy_say'])
insertTwo(projectID, "EmpathyDo", context['empathy_do'])
insert = "insert into AsIs values(?,?,?)"
for element in context['as_is']:
params = (projectID, element['order'], element['text'])
stmt = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt, params)
insert = "insert into Ideas values(?,?,?,?)"
for element in context['new_ideas']:
params = (projectID, element['complexity'], element['expensive'],
element['text'])
stmt = ibm_db.prepare(ibm_db_conn, insert)
ibm_db.execute(stmt, params)
def ritch(magfrom=None, magto=None, datefromritch=None, datetoritch=None):
# connect to DB2
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
# we have a Db2 connection, query the database
sql = 'select count(*) from EARTHQUAKE where ("mag" BETWEEN ? AND ?) AND ("time" between ? AND ?)'
#Note that for security reasons we are preparing the statement first,
# then bind the form input as value to the statement to replace the
# parameter marker.
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, magfrom)
ibm_db.bind_param(stmt, 2, magto)
ibm_db.bind_param(stmt, 3, datefromritch)
ibm_db.bind_param(stmt, 4, datetoritch)
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return render_template('ritcher.html', r=rows)
def new():
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
# we have a Db2 connection, query the database
sql = 'SELECT count(*) FROM EARTHQUAKE WHERE acos(sin(0.0175*32.7767) * sin(0.0175*"latitude") + cos(0.0175*32.7767) * cos(0.0175*"latitude") * cos(0.0175*"longitude" - (0.0175* -97.1081))) * 6371 < 1000'
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
sql1 = 'SELECT count(*) FROM EARTHQUAKE WHERE acos(sin(0.0175*61) * sin(0.0175*"latitude") + cos(0.0175*61) * cos(0.0175*"latitude") * cos(0.0175*"longitude" - (0.0175* -150))) * 6371 < 1000'
stmt = ibm_db.prepare(db2conn, sql1)
ibm_db.execute(stmt)
rows1 = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows1.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return render_template('bet.html', r=rows, f=rows1)
def experiment(query_str,g):
# generate nb of parameters for query
matchList = re.findall('\?', query_str)
nbParams = len(matchList)
if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)")
# Connect to DB
conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','')
if conn is None: raise Usage(ibm_db.conn_errormsg())
# Prepare statement
query_stmt = ibm_db.prepare(conn, query_str)
if (query_stmt == False): raise Usage("Failed to prepare query")
# Execute statement
for i in range(NBQUERIES):
if (nbParams == 0):
if ibm_db.execute(query_stmt) == False:
raise Usage("Failed to execute the query")
else:
t = g.getWrite(i)
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(query_stmt, tuple(u)) == False:
raise Usage("Failed to execute the query")
nbtuples = 0
while (ibm_db.fetch_tuple(query_stmt) != False):
nbtuples += 1
print "Query"+str(i)+": "+str(nbtuples)+" fetched."
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def run_test_014(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
query = 'SELECT * FROM animals ORDER BY name'
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)
def run_test_006(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
print ("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print ("Connection failed.")
def main(dict):
#Para usar datos del objeto JSON "dict" usar dict["message"]
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=tzx97475;PWD=sz6csp4k28+3wrll;",
"", "")
if dict["operation"] == "information":
#Preparando la sentencia SQL
sql = "INSERT INTO INFORMATION VALUES (CURRENT_TIMESTAMP,?);"
stmt = ibm_db.prepare(conn, sql)
#Vincular explÃcitamente los parámetros
ibm_db.bind_param(stmt, 1, dict["message"])
#Ejecución de la sentencia.
ibm_db.execute(stmt)
elif dict["operation"] == "client_opinion":
#Preparando la sentencia SQL
sql = "INSERT INTO CLIENT_OPINION VALUES (CURRENT_TIMESTAMP,?,?,?);"
stmt = ibm_db.prepare(conn, sql)
#Vincular explÃcitamente los parámetros
ibm_db.bind_param(stmt, 1, dict["valoration"])
ibm_db.bind_param(stmt, 2, dict["opinion_1"])
ibm_db.bind_param(stmt, 3, dict["opinion_2"])
#Ejecución de la sentencia.
ibm_db.execute(stmt)
return {'message': dict["message"]}
def drop_IBMHIST(conn):
stmt = ibm_db.exec_immediate(
conn, "SELECT * FROM SYSCAT.SCHEMATA WHERE SCHEMANAME = 'IBMHIST'")
if ibm_db.fetch_assoc(stmt):
print("Dropping IBMHIST schema and its objects ...")
# calling ADMIN_DROP_SCHEMA to drop IBMHIST schema and all its objects
# errors will be outputted to IBMHIST_ERR_SCHEMA.IBMHIST_ERR_TAB
sql = "CALL SYSPROC.ADMIN_DROP_SCHEMA ('IBMHIST', NULL, ?, ?)"
param = "IBMHIST_ERR_SCHEMA", "IBMHIST_ERR_TAB"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt, param)
# check if any errors were produced when using ADMIN_DROP_SCHEMA
# see if IBMHIST_ERR_SCHEMA.IBMHIST_ERR_TAB was created and drop IBMHIST_ERR_SCHEMA, IBMHIST_ERR_TAB
stmt = ibm_db.exec_immediate(
conn,
"SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'IBMHIST_ERR_SCHEMA' AND TABNAME = 'IBMHIST_ERR_TAB'"
)
if ibm_db.fetch_assoc(stmt):
stmt = ibm_db.exec_immediate(
conn, "SELECT * FROM IBMHIST_ERR_SCHEMA.IBMHIST_ERR_TAB")
error = ibm_db.fetch_assoc(stmt)
print("Error: could not drop schema IBMHIST, error: %s" % error)
ibm_db.exec_immediate(
conn, "DROP TABLE IBMHIST_ERR_SCHEMA.IBMHIST_ERR_TAB")
ibm_db.exec_immediate(conn,
"DROP SCHEMA IBMHIST_ERR_SCHEMA RESTRICT")
exit()
def run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i, p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i, p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_007(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
print("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def count_scale():
start = request.args.get('start', default='2020-06-01')
end = request.args.get('end', default='2020-06-01')
start = '2020-06-01' if start == '' else start
end = '2020-06-08' if end == '' else end
start = datetime.datetime.strptime(start, "%Y-%m-%d")
end = datetime.datetime.strptime(end, "%Y-%m-%d")
scale = request.args.get('scale', '3')
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE WHERE MAGTYPE=\'ml\' AND MAG>=?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, scale)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
curr_date = result['TIME'][:10]
curr_date = datetime.datetime.strptime(curr_date, "%Y-%m-%d")
if start <= curr_date <= end:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('count_scale.html', ci=rows)
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 updateDB(user_name, request_content, latitude, longitude, phone, req_type, conn):
user_name = user_name
request_content = request_content
latitude = latitude
longitude = longitude
phone = phone
req_type = req_type
#Construct the query that retrieves all rows from the REQUESTER table
##insert = "insert into requester (USER_NAME, REQ_TEXT, LATITUDE, LONGITUDE, PHONE, REQ_TYPE) VALUES ( 'abanoopb', 'Please provide ambulance service', 21.2212, 23.1234, 8951523243, 'P1')"
##ibm_db.exec_immediate(conn,insert)
insert = "insert into requester (USER_NAME, REQ_TEXT, LATITUDE, LONGITUDE, PHONE, REQ_TYPE) VALUES (?,?,?,?,?,?);"
#params="( 'abanoopb', 'Please provide ambulance service', 21.2212, 23.1234, 8951523243, 'P1')"
stmt = ibm_db.prepare(conn, insert)
ibm_db.bind_param(stmt, 1, user_name)
ibm_db.bind_param(stmt, 2, request_content)
ibm_db.bind_param(stmt, 3, latitude)
ibm_db.bind_param(stmt, 4, longitude)
ibm_db.bind_param(stmt, 5, phone)
ibm_db.bind_param(stmt, 6, req_type)
ibm_db.execute(stmt)
#Construct the query that retrieves unique id generated from the REQUESTER table
stmt = ibm_db.exec_immediate(conn, "SELECT unique SYSIBM.IDENTITY_VAL_LOCAL() AS id FROM requester")
result = ibm_db.fetch_both(stmt)
print("Creating Req_ID ", result[0]);
def insert_record(self, tablename, record):
conn = self.get_conn()
insertsql = "insert into %s(...) values(?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,?,\
?, ?, ?, ?)" % tablename
stmt = ibm_db.prepare(conn, insertsql)
ibm_db.execute(stmt, record)
ibm_db.commit(conn)
def run_test_115(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# row = ibm_db.fetch_object(stmt, 0)
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
else:
print("Connection failed.")
def check_if_user_active(prof_uid_lower):
sql = "select PROF_MAIL from EMPINST.employee where prof_uid_lower = '" + prof_uid_lower + "' and prof_state = '0';"
stmt = ibm_db.prepare(conn_people, sql)
try:
ibm_db.execute(stmt)
if ibm_db.fetch_row(stmt):
return True
else:
return False
except:
print "Transaction couldn't be completed, error getting profiles data: " , ibm_db.stmt_errormsg()
sys.exit(3)
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 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 executeSelect(statement):
connection = ibm_db.pconnect(url, '', '')
statement = ibm_db.prepare(connection, statement)
ibm_db.execute(statement)
data = ibm_db.fetch_tuple(statement)
result = []
while (data):
result.append(data)
data = ibm_db.fetch_tuple(statement)
ibm_db.free_stmt(statement)
ibm_db.close(connection)
return result
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def summation(q, txtBefore, txtAfter):
sum_str = q[2]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if sum_stmt == False:
raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False:
raise Usage("Failed to execute the sum query")
sum = ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
f.write(txtBefore + str(sum) + txtAfter)
f.close()
except IOError, e:
raise Usage("Failed to manipulate output.txt.\n")
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 summation(q):
sum_str = q[2]
# Connect to DB
start=time.time()
#sys.stderr.write("Start sum %s\n"%(start))
for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
#sys.stderr.write("Sum %d"%n)
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if (sum_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
sum= ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH, 'a')
f.write(str(sum)+'\n')
# f.close
except IOError, e:
raise Usage("Failed to manipulate sum.sql.\n")
finally:
def collect(self, conn):
sql = self.sql
try:
stmt = ibm_db.prepare(conn, sql)
if ibm_db.execute(stmt):
result = ibm_db.fetch_assoc(stmt)
while result != False:
if self.add_inst_name:
if ('INST' not in result.keys()):
result['INST'] = self.server_info['inst_name'].upper()
else:
result['INST'] = result['INST'].upper()
if self.add_db_name:
if ('DB' not in result.keys()):
result['DB'] = self.server_info['db_name'].upper()
else:
result['DB'] = result['DB'].upper()
self.publish(result)
result = ibm_db.fetch_assoc(stmt)
except Exception:
self.log.error(traceback.format_exc())
return True
def run_test_142(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, sql)
weight = 200.05
mass = 2.0
ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
if ( result ):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if (server.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)"
else:
create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print("Number of inserted rows:", ibm_db.num_rows(res))
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0])
print(data[1])
print(data[2])
print(data[3])
if sys.version_info >= (3, ):
print(type(data[0]) is int)
print(type(data[1]) is int)
print(type(data[2]) is int)
else:
print(type(data[0]) is long)
print(type(data[1]) is long)
print(type(data[2]) is long)
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
def run_test_133(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = ibm_db.prepare(conn, sql)
res = ibm_db.execute(stmt,(128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3))
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
for i in row:
print i
ibm_db.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
try:
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
ibm_db.rollback(conn)
print "Done"
def check_community_owners(community_id):
active = False
sql = "select DISTINCT LOWER_LOGIN from sncomm.memberlogin where member_uuid in (SELECT DISTINCT MEMBER_UUID from sncomm.member WHERE COMMUNITY_UUID = '" + community_id + "' AND ROLE='1' AND LOWER_LOGIN NOT LIKE '%@%');"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
member_login = ibm_db.result(stmt, 0)
if check_if_user_active(member_login):
active = True
return True
break
else:
return False
except:
print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg()
sys.exit(3)
def run_test_012(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0")
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while (data):
print data[0]
data = ibm_db.fetch_both( stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def write(q,index,mutex):
# initialize vars
write_str = q[0]
matchList = re.findall('\?', write_str)
nbParams = len(matchList)
write_data = q[1]
# 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)
ret2 = None
if TL:
ret2 = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
print "Lock: %s"%ret
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
# Perform insertions/updates
for i in range(NBWRITES/NBTHREADS):
mutex.acquire()
v = index.value
index.value -= 1
mutex.release()
t = write_data.getWrite(v)
if v >= 0 and t != None:
# 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'):
print "commit"
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
print "commit"
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def performPrepStmtBindings(connection, outFile, jLine, preparedStmtList):
currentBindings = jLine.bindingValueTuple
outFile.write("Values to bind to current prepared statement: {} \n".format(currentBindings))
currentSqlOper = preparedStmtList.getStmtIdSql(jLine.statementId)
outFile.write("Executing sql statement: {} \n".format(currentSqlOper))
currentHdl = preparedStmtList.getCurrentHdl(jLine.statementId)
stmt = ibm_db.execute(currentHdl, currentBindings)
if not stmt:
outFile.write("operation FAILED")
def swap(q):
swap1_str = q[0]
swap2_str = q[1]
# 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)
# Prepare Statements
swap1_stmt = ibm_db.prepare(conn, swap1_str)
if swap1_stmt == False:
raise Usage("Failed to prepare swap1 query")
swap2_stmt = ibm_db.prepare(conn, swap2_str)
if swap2_stmt == False:
raise Usage("Failed to prepare swap2 update")
# Execute Statements
nbrep = int(round(NBSWAPS / NBSWAPTHREADS))
for i in range(nbrep):
x = random.randint(RANGE_LOW, RANGE_UP / 2)
y = random.randint(x, RANGE_UP)
if ibm_db.execute(swap1_stmt, (x,)) == False:
raise Usage("Failed to execute the swap1 query (x)")
valX = ibm_db.fetch_tuple(swap1_stmt)
if valX == False:
raise Usage("Failed to iterate over the swap1 result set (x)")
if ibm_db.execute(swap1_stmt, (y,)) == False:
raise Usage("Failed to execute the swap1 query (y)")
valY = ibm_db.fetch_tuple(swap1_stmt)
if valY == False:
raise Usage("Failed to iterate over the swap1 result set (y)")
time.sleep(0.1)
if ibm_db.execute(swap2_stmt, (valY[0], x)) == False:
raise Usage("Failed to execute the swap2 query (x, valY)")
if ibm_db.execute(swap2_stmt, (valX[0], y)) == False:
raise Usage("Failed to execute the swap1 query (y, valX)")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False:
raise Usage("Failed to close db connection.\n")