def user(user_id):
#Connect To the database
conn = passw.connect()
if not conn:
return jsonify(passw.reg_content(status="bad", error="Not connected to database", code=100))
cont = dict()
#Make SQL queries
sql_delete_user = "******".format(user_id)
sql_delete_user_bio = "DELETE FROM internbio WHERE user_id={}".format(
user_id)
sql_detele_password = "******".format(
user_id)
#Try to execute SQL
#Fails: Return status bad
try:
if request.method == 'DELETE':
cont = passw.reg_content(status="ok", error="none", code=0)
stmt_1 = ibm_db.exec_immediate(conn, sql_delete_user)
stmt_2 = ibm_db.exec_immediate(conn, sql_delete_user_bio)
stmt_3 = ibm_db.exec_immediate(conn, sql_detele_password)
except:
cont = passw.reg_content(status="bad", error=ibm_db.stmt_errormsg(), code= 400)
ibm_db.rollback(conn)
finally:
ibm_db.close(conn)
return jsonify(cont)
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_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_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_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_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_6561(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,
"INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
)
statement = "SELECT count(id) FROM animals"
result = ibm_db.exec_immediate(conn, statement)
if ((not result) and ibm_db.stmt_error()):
print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))
row = ibm_db.fetch_tuple(result)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
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 db_search(self, param):
timestamp = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
level = 'LEV' + '%s' % param['level']
user = g.user.uid
if user == 'admin':
source = 'Atas'
sql = "SELECT LEV10,LEV15,LEV17,LEV20,LEV30 from CMRDC.PRODUCTS_IMAGE WHERE %s = '%s'" % (
level, param['prod_id'])
stmt = ibm_db.exec_immediate(self.conn, sql)
results = ibm_db.fetch_both(stmt)
while results:
level10 = results[0]
level15 = results[1]
level17 = results[2]
level20 = results[3]
level30 = results[4]
results = ibm_db.fetch_both(stmt)
try:
insert_sql = "INSERT INTO CMRDC.LINEITEM(RLI_ID, MPP_NUMBER, VERSION, COUNTRY, DELETED, LEVEL10, LEVEL15, LEVEL17, LEVEL20, LEVEL30, SOURCE, DATE_ENTERED, DATE_MODIFIED) VALUES('%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%s','%s')" % (
param['rli_id'], param['account_mpp'], ESUtil().rule_index,
param['account_country'], param['deleted'], level10, level15,
level17, level20, level30, source, timestamp, timestamp)
ibm_db.exec_immediate(self.conn, insert_sql)
ibm_db.commit(self.conn)
except Exception as ex:
ibm_db.rollback(self.conn)
finally:
ibm_db.close(self.conn)
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 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_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_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_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 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_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_setgetOption(self):
if sys.platform == 'zos':
options = {}
else:
options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'}
conn = ibm_db.connect(config.database, config.user, config.password,
options)
# Get the server type
serverinfo = ibm_db.server_info(conn)
if conn:
if sys.platform != 'zos':
value = ibm_db.get_option(conn,
ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1)
print("Connection options:\nSQL_ATTR_INFO_PROGRAMNAME = ",
end="")
print(value)
else:
print("Connection options:\n", end="")
returncode = ibm_db.set_option(conn,
{ibm_db.SQL_ATTR_AUTOCOMMIT: 0}, 1)
value = ibm_db.get_option(conn, ibm_db.SQL_ATTR_AUTOCOMMIT, 1)
print("SQL_ATTR_AUTOCOMMIT = ", end="")
print(str(value) + "\n")
drop = "DROP TABLE TEMP_TEST"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table temp_test
create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO temp_test values (1, 'cat')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1")
if sys.platform != 'zos':
returnCode = ibm_db.set_option(
stmt, {ibm_db.SQL_ATTR_QUERY_TIMEOUT: 20}, 0)
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_QUERY_TIMEOUT,
0)
print("Statement options:\nSQL_ATTR_QUERY_TIMEOUT = ", end="")
print(str(value) + "\n")
ibm_db.execute(stmt)
if result:
ibm_db.free_result(stmt)
else:
print(ibm_db.stmt_errormsg())
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_220(self):
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_011(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, "DELETE FROM animals WHERE weight > 10.0")
print ("Number of affected rows: %d" % ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_003(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ibm_db.exec_immediate(conn, sql)
print ("Number of affected rows: %d" % ibm_db.num_rows(res))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def __exit__(self, exc_type, exc_val, exc_tb):
if self.boundary:
if exc_type is not None:
ibm_db.rollback(self.db_connection)
logging.error(f'{exc_type.__name__}: {exc_val}')
else:
ibm_db.commit(self.db_connection)
print("commit transaction")
ibm_db.close(self.db_connection)
delattr(t_local, '_conn')
elif exc_type is not None:
raise exc_type(exc_val)
def run_test_003(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ibm_db.exec_immediate(conn, sql)
print("Number of affected rows: %d" % ibm_db.num_rows(res))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def add_user():
#Connect To database
conn = passw.connect()
if not conn:
return passw.reg_content(status="bad", error="Not connected to database", code=100)
#Get JSON Data Parsed
store_data = passw.getJSON()
#Make SQL Queries
sql_add_user = "******"
sql_add_pass = "******"
sql_add_bio = "INSERT INTO internbio (user_id, position) VALUES (?, ?)"
#Prepare sql statement
stmt_user = ibm_db.prepare(conn, sql_add_user)
stmt_pass = ibm_db.prepare(conn, sql_add_pass)
stmt_bio = ibm_db.prepare(conn, sql_add_bio)
cont = dict()
#Try to Execute SQL queries, if so return json with user_id and ibm_db error
#Else Return json with user_id,
try:
if request.method == 'POST':
#ADDING TO USER TABLE
params_user = store_data["name"], store_data["email"], store_data["phone_number"], store_data["employeeType_id"], store_data["siteLocation_id"]
ibm_db.execute(stmt_user, params_user)
#ADDING TO PASSWORD TABLE
last_user = get_maxID(conn)
params = last_user["id"], store_data["password"][1]
ibm_db.execute(stmt_pass, params)
#ADDING TO INTERNBIO TABLE
params_1 = last_user["id"], "Software Engineer"
ibm_db.execute(stmt_bio, params_1)
print("Add User")
print(store_data["phone_number"])
#Send Message
try:
user_s.send_sms(store_data["password"][0], store_data["phone_number"])
except:
pass
cont = passw.content(user=last_user["id"], user_data=last_user, status="ok")
except:
cont = passw.content(user=None, err=ibm_db.stmt_errormsg(), status="bad")
ibm_db.rollback(conn)
finally:
ibm_db.close(conn)
return jsonify(cont)
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 run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def run_test_111(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
if (server.DBMS_NAME[0:3] == 'IDS'):
num1 = ibm_db.field_num(stmt, "id")
num2 = ibm_db.field_num(stmt, "breed")
num3 = ibm_db.field_num(stmt, "number")
num4 = ibm_db.field_num(stmt, "NUMBER")
num5 = ibm_db.field_num(stmt, "bREED")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "WEIGHT")
else:
num1 = ibm_db.field_num(stmt, "ID")
num2 = ibm_db.field_num(stmt, "BREED")
num3 = ibm_db.field_num(stmt, "NUMBER")
num4 = ibm_db.field_num(stmt, "number")
num5 = ibm_db.field_num(stmt, "Breed")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "weight")
print("%s" % num1)
print("int(%d)" % num2)
print("int(%d)" % num3)
print("%s" % num4)
print("%s" % num5)
print("%s" % num6)
print("int(%d)" % num7)
print("%s" % num8)
ibm_db.rollback(conn)
else:
print("Connection failed.")
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 run_test_111(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
if (server.DBMS_NAME[0:3] == 'IDS'):
num1 = ibm_db.field_num(stmt, "id")
num2 = ibm_db.field_num(stmt, "breed")
num3 = ibm_db.field_num(stmt, "number")
num4 = ibm_db.field_num(stmt, "NUMBER")
num5 = ibm_db.field_num(stmt, "bREED")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "WEIGHT")
else:
num1 = ibm_db.field_num(stmt, "ID")
num2 = ibm_db.field_num(stmt, "BREED")
num3 = ibm_db.field_num(stmt, "NUMBER")
num4 = ibm_db.field_num(stmt, "number")
num5 = ibm_db.field_num(stmt, "Breed")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "weight")
print "%s" % num1
print "int(%d)" % num2
print "int(%d)" % num3
print "%s" % num4
print "%s" % num5
print "%s" % num6
print "int(%d)" % num7
print "%s" % num8
ibm_db.rollback(conn)
else:
print "Connection failed."
def run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print "string(%d) \"%s\"" % (len(name1), name1)
print "string(%d) \"%s\"" % (len(name2), name2)
print "%s" % name3
print "%s" % name4
print "string(%d) \"%s\"" % (len(name5), name5)
print "%s" % name6
print "%s" % name7
print "string(%d) \"%s\"" % (len(name8), name8)
ibm_db.rollback(conn)
else:
print "Connection failed."
def parseCommit(sql):
global _hdbc, _hdbi, _connected, _stmt, _stmtID, _stmtSQL
if (_connected == False): return # Nothing to do if we are not connected
cParms = sql.split()
if (len(cParms) == 0): return # Nothing to do but this shouldn't happen
keyword = cParms[0].upper() # Upper case the keyword
if (keyword == "COMMIT"): # Commit the work that was done
try:
result = ibm_db.commit(_hdbc) # Commit the connection
if (len(cParms) > 1):
keyword = cParms[1].upper()
if (keyword == "HOLD"):
return
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "ROLLBACK"): # Rollback the work that was done
try:
result = ibm_db.rollback(_hdbc) # Rollback the connection
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "AUTOCOMMIT"): # Is autocommit on or off
if (len(cParms) > 1):
op = cParms[1].upper() # Need ON or OFF value
else:
return
try:
if (op == "OFF"):
ibm_db.autocommit(_hdbc, False)
elif (op == "ON"):
ibm_db.autocommit(_hdbc, True)
return
except Exception as err:
db2_error()
return
return
def run_test_145(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.prepare(
conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string
ibm_db.execute(stmt)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string.
# Use the dynamically bound parameters to ensure that the code paths
# for both ibm_db.bind_param and ibm_db.execute treat Python Nones and empty
# strings the right way.
ibm_db.execute(stmt, (1000, None, 'PythonDS'))
result = ibm_db.exec_immediate(
conn,
"SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ibm_db.fetch_tuple(result)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("%s" % name3)
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("%s" % name7)
print("string(%d) \"%s\"" % (len(name8), name8))
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_049(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert)
if ibm_db.execute(stmt, (None, 'ghost', None, None)):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| puts child }
for child in row:
print child
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print "Connection failed."
def run_test_145(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.prepare(conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string
ibm_db.execute(stmt)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string.
# Use the dynamically bound parameters to ensure that the code paths
# for both ibm_db.bind_param and ibm_db.execute treat Python Nones and empty
# strings the right way.
ibm_db.execute(stmt, (1000, None, 'PythonDS'))
result = ibm_db.exec_immediate(conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ibm_db.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_6561(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, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)")
statement = "SELECT count(id) FROM animals"
result = ibm_db.exec_immediate(conn, statement)
if ( (not result) and ibm_db.stmt_error() ):
print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))
row = ibm_db.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
for k in range(len(airbnb_data)):
distance = cal_dist(lng_attractions[i], lat_attractions[i], lng_airbnb[k], lat_airbnb[k])
# print(distance)
distances.append(distance)
#print(len(distances))
k = 1
for i in range(len(tourism_attractions_data)):
for j in range(len(airbnb_data)):
this_attractid = attractionid[i]
this_nameid = roomid[j]
this_distance = distances[(i + 1)* j]
sql = r'INSERT INTO DISTANCE(ATTRACTIONID, ROOMID, DISTANCE) VALUES({attractionID}, {nameID}, {distance})'.format(
attractionID=this_attractid, nameID=this_nameid, distance=this_distance
)
print(sql, '>>')
try:
stmt = ibm_db.exec_immediate(conn, sql)
except Exception as e:
print(e)
print("Inserting couldn't be completed.")
ibm_db.rollback(conn)
else:
ibm_db.commit(conn)
print("Inserting complete.")
print('-----' + str(k) + '-----')
k += 1
def doEverything():
commands = []
# connect to database
host = ""
port = "9090"
username = ""
password = "******"
database = "s0523215"
url = "HOSTNAME=" + host + ";PORT=" + port + ";DATABASE="+ database + ";PROTOCOL=TCPIP;UID=" + username +";PWD="+ password + ";"
conn = ibm_db.connect(url, '', '')
commands.append("Connected to " + url)
# set up variables and data
tableName = "pythonDRDAGalaxy"
# 1 Create table
commands.append("\n#1 Create table")
sql = "create table if not exists " + tableName + "(name VARCHAR(255), population INTEGER, longitude DECIMAL(8,4), latitude DECIMAL(8,4),countryCode INTEGER)"
ibm_db.exec_immediate(conn, sql)
commands.append( "\tCreate a table named: " + tableName)
commands.append("\tCreate Table SQL: " + sql)
# 2 Inserts
commands.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
commands.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, kansasCity.name)
ibm_db.bind_param(statement, 2, kansasCity.population)
ibm_db.bind_param(statement, 3, kansasCity.longitude)
ibm_db.bind_param(statement, 4, kansasCity.latitude)
ibm_db.bind_param(statement, 5, kansasCity.countryCode)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + kansasCity.name + " , " + str(kansasCity.population) +
" , " + str(kansasCity.longitude) + " , " + str(kansasCity.latitude) + " , " + str(kansasCity.countryCode))
commands.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, seattle.name)
ibm_db.bind_param(statement, 2, seattle.population)
ibm_db.bind_param(statement, 3, seattle.longitude)
ibm_db.bind_param(statement, 4, seattle.latitude)
ibm_db.bind_param(statement, 5, seattle.countryCode)
ibm_db.execute(statement)
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, newYork.name)
ibm_db.bind_param(statement, 2, newYork.population)
ibm_db.bind_param(statement, 3, newYork.longitude)
ibm_db.bind_param(statement, 4, newYork.latitude)
ibm_db.bind_param(statement, 5, newYork.countryCode)
ibm_db.execute(statement)
#Alternate way to insert without bindings
sql = "insert into " + tableName + " values" + tokyo.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + madrid.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + melbourne.toSQL()
ibm_db.exec_immediate(conn, sql)
# 3 Queries
commands.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
commands.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where population > 8000000 and countryCode = 1"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFirst document matching query -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
commands.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
commands.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where population > 8000000 and longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind all documents with name: " + kansasCity.name)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
commands.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind All Documents SQL: " + sql)
commands.append("#3.4 Count documents in a table")
sql = "select count(*) from " + tableName + " where longitude < 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Documents in table with longitude less than 40.0: " + str(len(dictionary)))
commands.append("#3.5 Order documents in a table")
sql = "select * from " + tableName + " order by population"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " population: " + str(dictionary[1]) + " latitude: " +
str(dictionary[2]) + " longitude: " + str(dictionary[3]) + " countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.6 Joins")
tableJoin = "country";
sql = "create table if not exists " + tableJoin + " (countryCode INTEGER, countryName VARCHAR(255))";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (1,\"United States of America\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (44,\"United Kingdom\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (81,\"Japan\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (34,\"Spain\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (61,\"Australia\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = ("select table1.name, table1.population, table1.longitude, table1.latitude, table1.countryCode, table2.countryName from " +
tableName + " table1 inner join " + tableJoin + " table2 on table1.countryCode=table2.countryCode")
stmt =ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", latitude: " +
str(dictionary[2]) + ", longitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]) + ", countryName: " + str(dictionary[5]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.7 Distinct documents in a table")
sql = "select distinct countryCode from " + tableName + " where longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Distinct countryCodes of documents in table with longitude greater than 40.0: ")
while dictionary != False:
commands.append("\tJoined -> countryCode: " + str(dictionary[0]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.8 Projection Clause")
sql = "select distinct name, countryCode from " + tableName + " where population > 8000000"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Projection of name and countryCode where population is greater than 8000000: ")
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", countryCode: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
# 4 Update documents in a table
commands.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set countryCode = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 999)
ibm_db.bind_param(statement, 2, seattle.name)
ibm_db.execute(statement)
commands.append( "\tDocument to update: " + seattle.name)
commands.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
commands.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + newYork.name + "'"
ibm_db.exec_immediate(conn, sql)
commands.append("\tDelete documents with name: " + newYork.name)
commands.append("\tDelete By name SQL: " + sql)
commands.append("\n#6 Transactions")
ibm_db.autocommit(conn, False)
sql = "insert into " + tableName + " values" + sydney.toSQL()
ibm_db.exec_immediate(conn, sql)
ibm_db.commit(conn)
sql = "update " + tableName + " set countryCode = 998 where name = 'Seattle'"
ibm_db.exec_immediate(conn, sql)
ibm_db.rollback(conn)
ibm_db.autocommit(conn, True)
commands.append( "\tFind all documents in table: " + tableName)
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\n#7 Commands")
commands.append("\n#7.1 Count")
sql = "select count(*) from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Number of documents in table " + str(len(dictionary)))
# 8 Drop a table
commands.append("\n#8 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
sql = "drop table " + tableJoin;
ibm_db.exec_immediate(conn, sql)
commands.append("\tDrop table: " + tableName)
commands.append("\tDrop Table SQL: " + sql)
ibm_db.close(conn)
commands.append("\nConnection closed")
return commands
def rollback(self):
self.isautapp()
global db
return ibm_db.rollback(db)
def rollbackTrans(connection, outFile):
if ibm_db.rollback(connection):
outFile.write("Rollback successful \n")
else:
outFile.write("Rollback FAILED \n")
def main():
dbname = None
tables = None
schema = None
user = None
pwd = None
try:
opts, args = getopt.getopt(sys.argv[1:], "d:t:s:u:p:")
except getopt.GetoptError:
sys.exit(-1)
for o, a in opts:
if (o == "-d"):
dbname = a
if (o == "-t"):
tables = a.split(',')
if (o == "-s"):
schema = a
if (o == "-u"):
user = a
if (o == "-p"):
pwd = a
if dbname == None or tables == None or schema == None or user == None or pwd == None:
print(
"Usage: print_recreate_fk.py -d <db> -u <usr> -p <pwd> -s <schema> -t t1,t2,...tn"
)
sys.exit(1)
ip = socket.gethostbyname(socket.gethostname())
cfg = (dbname, ip, user, pwd)
conn = ibm_db.connect(
"DATABASE=%s;HOSTNAME=%s;PORT=50000;PROTOCOL=TCPIP;UID=%s; PWD=%s" %
cfg, "", "")
create_stmts = []
drop_stmts = []
s1 = ibm_db.prepare(conn, fk_sql)
for t in tables:
ibm_db.execute(s1, (t, schema))
restore_sql = ''
row = ibm_db.fetch_tuple(s1)
while row != False:
tabscema = row[0]
tabname = row[1]
constname = row[2]
fk_colnames = row[3]
reftabschema = row[4]
reftabname = row[5]
pk_colnames = row[6]
deleterule = row[7]
updaterule = row[8]
enforced = row[9]
if enforced == 'N':
enforced = 'NOT '
else:
enforced = ''
queryopt = row[10]
if queryopt == 'N':
queryopt = 'DISABLE '
else:
queryopt = 'ENABLE '
fk = filter((lambda x: x != ''), fk_colnames.split(' '))
cols = ''
for c in fk:
cols = cols + ',' + c
fkcols = cols[1:]
pk = filter((lambda x: x != ''), pk_colnames.split(' '))
cols = ''
for c in pk:
cols = cols + ',' + c
pkcols = cols[1:]
create = create_str % (tabscema, tabname, constname, fkcols,
reftabschema, reftabname, pkcols,
updaterule, deleterule, enforced, queryopt)
drop = drop_str % (tabscema, tabname, constname)
create_stmts.append(create)
drop_stmts.append(drop)
row = ibm_db.fetch_tuple(s1)
ibm_db.rollback(conn)
for x in drop_stmts:
print(x)
for x in create_stmts:
print(x)
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"
#__END__
#__LUW_EXPECTED__
#Starting test ...
#None
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [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
#Done
#__ZOS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [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
#Done
#__SYSTEMI_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][AS] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__IDS_EXPECTED__
#Starting test ...
#None
#SQLSTATE: 22001
#Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length.
#True
#SQLSTATE: 02000
#Message: [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
#Done
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")
#__END__
#__LUW_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [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
#Done
#__ZOS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [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
#Done
#__SYSTEMI_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][AS] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__IDS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length. SQLCODE=-1279
#True
#SQLSTATE: 02000
#Message: [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
#Done
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display The Number Of Rows That Exist In The DEPARTMENT Table Now
# (The Number Returned Should Change)
returnCode = get_row_count(dbConnection, 'DEPARTMENT')
if returnCode is False:
conn.closeConnection()
exit(-1)
# Back Out The Changes Just Made To The Database
print("Backing out changes made to the database ... ", end="")
resultSet = False
try:
resultSet = ibm_db.rollback(dbConnection)
except Exception:
pass
# If The Rollback Operation Could Not Be Performed, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to roll back the previous operation.")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display The Number Of Rows That Exist In The DEPARTMENT Table Now
# (The Number Should Revert Back To The Original Value)
