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 run_test_180(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = '' result2 = '' try: result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')") except: pass if result: cols = ibm_db.num_fields(result) print("col:", cols,", ") rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) try: result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") except: pass if result: cols = ibm_db.num_fields(result) print("col:", cols,", ") rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) else: print("no connection")
def get_maxID(conn=None): if not conn: # Exit return {"data":{ "error": "Not connected to the database", }, "status": "Fail", "id": -1 } cont = dict() try: # "SELECT USER.ID, USER.NAME, USER.EMAIL, USER.PHONENUMBER, EMPLOYEETYPE.TYPE, SITELOCATION.SITE FROM ((USER INNER JOIN EMPLOYEETYPE ON USER.EMPLOYEETYPE_ID = EMPLOYEETYPE.ID) INNER JOIN SITELOCATION ON USER.SITELOCATION_ID = SITELOCATION.ID)" # sql = "Select user.id, user.name, user.email, user.phonenumber, employeetype.type, sitelocation.site from (( USER INNER JOIN EMPLOYEETYPE ON USER.EMPLOYEETYPE_ID = EMPLOYEETYPE.ID) INNER JOIN SITELOCATION ON USER.SITELOCATION_ID = SITELOCATION.ID) where USER.id = (Select Max(id) from user)" sql = "SELECT * FROM user WHERE user.id = (SELECT MAX(id) FROM user)" stmt = ibm_db.exec_immediate(conn, sql) result = ibm_db.fetch_assoc(stmt) cont = { "user": result, "error": ibm_db.stmt_errormsg(), "status": "ok", "id": result["ID"] } except: cont = {"data": { "error": ibm_db.stmt_errormsg(), }, "status": "bad", "id": -1 } finally: return cont
def run_test_048(self): conn = ibm_db.connect(config.database, config.user, config.password) if (not conn): print "Could not make a connection." return 0 server = ibm_db.server_info(conn) fp = open("tests/spook_out.png", "wb") if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.exec_immediate( conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'") else: result = ibm_db.exec_immediate( conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'" ) if (not result): print "Could not execute SELECT statement." return 0 row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print ibm_db.stmt_errormsg() fp.close() cmp = (open('tests/spook_out.png', "rb").read() == open('tests/spook.png', "rb").read()) print "Are the files the same:", cmp
def run_test_015(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print("col:", cols) rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn, "delete from t_string where a=123") if result: cols = ibm_db.num_fields(result) print("col:", cols) rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) ibm_db.close(conn) else: print("no connection:", ibm_db.conn_errormsg())
def run_test_180(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = '' result2 = '' try: result = ibm_db.exec_immediate( conn, "insert int0 t_string values(123,1.222333,'one to one')") except: pass if result: cols = ibm_db.num_fields(result) print("col:", cols, ", ") rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) try: result = ibm_db.exec_immediate( conn, "delete from t_string where a=123") except: pass if result: cols = ibm_db.num_fields(result) print("col:", cols, ", ") rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) else: print("no connection")
def run_test_017(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY}) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) ibm_db.close(conn) else: print("no connection:", ibm_db.conn_errormsg())
def run_test_045(self): conn = ibm_db.connect(config.database, config.user, config.password) fp = open("tests/pic1_out.jpg", "wb") result = ibm_db.exec_immediate(conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'") row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print ibm_db.stmt_errormsg() fp.close() cmp = (open('tests/pic1_out.jpg', 'rb').read() == open('tests/pic1.jpg', 'rb').read()) print 'Are the files the same:', cmp
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_045(self): conn = ibm_db.connect(config.database, config.user, config.password) fp = open("tests/pic1_out.jpg", "wb") result = ibm_db.exec_immediate( conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'") row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print ibm_db.stmt_errormsg() fp.close() cmp = (open('tests/pic1_out.jpg', 'rb').read() == open('tests/pic1.jpg', 'rb').read()) print 'Are the files the same:', cmp
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_157a(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) print("Starting...") if conn: sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed" result = ibm_db.exec_immediate(conn, sql) try: i = 2 row = ibm_db.fetch_assoc(result, i) while (row): if (server.DBMS_NAME[0:3] == 'IDS'): print("%-5d %-16s %-32s %10s" % (row['id'], row['name'], row['breed'], row['weight'])) else: print("%-5d %-16s %-32s %10s" % (row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])) i = i + 2 row = ibm_db.fetch_assoc(result, i) except: print("SQLSTATE: %s" % ibm_db.stmt_error(result)) print("Message: %s" % ibm_db.stmt_errormsg(result)) print("DONE")
def run_test_048(self): conn = ibm_db.connect(config.database, config.user, config.password) if (not conn): print("Could not make a connection.") return 0 server = ibm_db.server_info(conn) with open("ibm_db_tests/spook_out.png", "wb") as fp: if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.exec_immediate( conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'") else: result = ibm_db.exec_immediate( conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'" ) if (not result): print("Could not execute SELECT statement.") return 0 row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print(ibm_db.stmt_errormsg()) with open('ibm_db_tests/spook_out.png', 'rb') as fp: pic_out = fp.read() with open('ibm_db_tests/spook.png', 'rb') as fp1: pic_in = fp1.read() cmp = pic_in == pic_out print("Are the files the same:", cmp)
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 read_tablespaces(con, mess, dbTableSpaces): sqlQ = 'select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT from SYSIBMADM.DBA_TABLESPACES' stmt = ibm_db.exec_immediate(con, sqlQ) if not stmt: mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt) module.fail_json(msg=mess, changed=False) raise dictionary = ibm_db.fetch_assoc(stmt) while dictionary != False: tablespace_name = dictionary["TABLESPACE_NAME"] block_size = dictionary["BLOCK_SIZE"] status = dictionary["STATUS"] contents = dictionary["CONTENTS"] segment_management = dictionary["SEGMENT_SPACE_MANAGEMENT"] dbTableSpaces.append( dict(tablespace_name=tablespace_name, block_size=block_size, status=status, contents=contents, segment_management=segment_management)) dictionary = ibm_db.fetch_assoc(stmt) return True
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_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_err_executemany(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] if conn: try: ibm_db.exec_immediate(conn, 'DROP TABLE CLI0126E') except: pass create_ddl = "create table CLI0126E \ (\ offer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\ position_id int NOT NULL,\ title VARCHAR(5000) NOT NULL,\ type VARCHAR(5000),\ quantity decimal(10, 3) NOT NULL,\ price_btc decimal(10, 8) NOT NULL,\ city VARCHAR(5000),\ country VARCHAR(500)\ )" try: ibm_db.exec_immediate(conn, create_ddl) except: pass insert_statement = "INSERT INTO CLI0126E (position_id, title, type, quantity, price_btc, city, country)\ VALUES (?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, insert_statement) # deliberately use wrong size decimal values to trigger CLI0111E (hidden by execute_many()). parms1 = (15628, 'correct value in row1 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus1') parms2 = (15629, 'incorrect value in row2 column4', '', 99999999.0, 0.0067762, 'Belarus', 'Belarus2') parms3 = (15630, 'correct value in row3 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus3') parms = (parms1, parms2, parms3) try: ibm_db.execute_many(stmt, parms) print( str(ibm_db.num_rows(stmt)) + " - Rows inserted successfully") except: print( "Failed to insert multiple-rows with ibm_db.execute_many()" ) print(ibm_db.stmt_errormsg()) print("Number of rows inserted: " + str(ibm_db.num_rows(stmt))) ibm_db.close(conn) else: print("Connection failed.")
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 db2_error(quiet): if quiet == True: return html = '<p style="border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;">' errmsg = ibm_db.stmt_errormsg().replace('\r',' ') errmsg = errmsg[errmsg.rfind("]")+1:].strip() pDisplay(pHTML(html+errmsg+"</p>"))
def run_test_048(self): conn = ibm_db.connect(config.database, config.user, config.password) if not conn: print "Could not make a connection." return 0 fp = open("tests/spook_out.png", "wb") result = ibm_db.exec_immediate(conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'") if not result: print "Could not execute SELECT statement." return 0 row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print ibm_db.stmt_errormsg() fp.close() cmp = open("tests/spook_out.png").read() == open("tests/spook.png").read() print "Are the files the same:", cmp
def fetch_statement_data(self, statement): self.connect() try: self.query_result = ibm_db.exec_immediate(self.connection, statement) except Exception: raise DB2Exception(ibm_db.stmt_errormsg()) return self._dump_query(self.query_result)
def fetch_statement_data(self, statement): self.connect() try: self.query_result = ibm_db.exec_immediate( self.connection, statement ) except Exception: logger.error( 'Failed To Execute Query', extra={ 'reason': ibm_db.stmt_errormsg(), 'time': datetime.now() } ) raise DB2Exception(ibm_db.stmt_errormsg()) return self._dump_query(self.query_result)
def insertDB(sql_stmt): connect() stmt = idb.prepare(con, sql_stmt) try: idb.execute(stmt) except: print(idb.stmt_errormsg()) return
def insertRec(conn, sql): ''' Call num_rows ''' try: stmt = db2.exec_immediate(conn, sql) #print 'Number of affected rows:', db2.num_rows(stmt) except: print sql print "Transaction couldn't be completed:" , db2.stmt_errormsg() return False else: return True
def db_insert(db_conn_, insert_query): try: statement = ibm_db.exec_immediate(db_conn_, insert_query) ibm_db.free_stmt(statement) except Exception as e: logging.error("The dbInsert operation error is %s" % (e)) return False except: logging.error("The dbInsert operation error is %s" % (ibm_db.stmt_errormsg())) return False return True
def execute_command(self, command): mylog.debug("Executing command: %s" % command) try: execute_return = ibm_db.exec_immediate(self.connection, command) except: mylog.critical("Unable to execute command: %s" % command) result = ibm_db.stmt_errormsg() mylog.critical("Command return was: %s" % result) print "PROCESS ERROR: Failed to execute command %s; command return was %s" % ( command, result) execute_return = result return execute_return
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 immediateOper(connection, sql, outFile): outFile.write("Performing immediate operation: {} \n".format(sql)) stmt = None try: stmt = ibm_db.exec_immediate(connection, sql) except: outFile.write(ibm_db.stmt_error()) outFile.write(ibm_db.stmt_errormsg()) raise if not stmt: outFile.write("Operation FAILED \n") return stmt
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_016(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) print ("col:", cols) rows = ibm_db.num_rows(result) print ("affected row:", rows) else: print (ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") if result: cols = ibm_db.num_fields(result) print ("col:", cols) rows = ibm_db.num_rows(result) print ("affected row:", rows) else: print (ibm_db.stmt_errormsg()) ibm_db.close(conn) else: print ("no connection:", ibm_db.conn_errormsg())
def exec_sql_file(conn, path): print(" Executing %s ..." % path) # read file with open(path) as sql_file: commands = sql_file.read() # split file into seperate commands using @ as a delimiter commands = commands.split("@") # strip whitespace and remove empty commands commands = [command.strip() for command in commands if command.strip()] # execute each command for command in commands: try: ibm_db.exec_immediate(conn, command) except: if "statement is blank or empty" not in ibm_db.stmt_errormsg(): print(" Failure executing statement: '%s' ..." % command.replace('\n', ' ')[:100]) print(ibm_db.stmt_errormsg()) exit()
def execute_query(self, query: str, bind_vars: Any) -> Tuple[List, List]: """ Execute query at DB2 Database via connection Args: query(str): db2 query string bind_vars(Any): in case there are names and values - a bind_vars dict, in case there are only values - list Returns: Tuple[results(List), headers(List)] """ results = list() headers = list() status = False stmt = self._prepare_statement(query, bind_vars) try: demisto.info("Executing ...") status = ibm_db.execute(stmt) demisto.info("Done !!!") except Exception: demisto.error(clear(ibm_db.stmt_error())) demisto.error(clear(ibm_db.stmt_errormsg())) raise DemistoException(clear(ibm_db.stmt_errormsg())) demisto.info("Collecting results") if status: row = ibm_db.fetch_assoc(stmt) while row: results.append(row) row = ibm_db.fetch_assoc(stmt) if results: headers = [*results[0]] return results, headers
def run_test_015(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print "col:", cols rows = ibm_db.num_rows(result) print "affected row:", rows else: print ibm_db.stmt_errormsg() result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") if result: cols = ibm_db.num_fields(result) print "col:", cols rows = ibm_db.num_rows(result) print "affected row:", rows else: print ibm_db.stmt_errormsg() ibm_db.close(conn) else: print "no connection:", ibm_db.conn_errormsg()
def getResByRow(conn, sql): ''' Call fetch_row ''' n = 0 try: stmt = db2.exec_immediate(conn, sql) res = db2.fetch_row(stmt) while res != False: print 'Result from :', db2.result(stmt, 0) n += db2.result(stmt, 0) res = db2.fetch_row(stmt) except: print "Transaction couldn't be completed:" , db2.stmt_errormsg() else: return n
def execute(sql): try: conn = ibm_db.connect(SSL_DSN, "", "") stmt = ibm_db.exec_immediate(conn, sql) # ibm_db.bind_param(stmt, 1, animal) except: print "Transaction couldn't be completed:", ibm_db.stmt_errormsg() else: print "Transaction complete." return "SUCCESS"
def execute(self, command): """ Execute a command on the database and return the result if it is a query """ result_set = [] try: statement = ibm_db.exec_immediate(self._connection, command) result = ibm_db.fetch_both(statement) while result: result_set.append(result) result = ibm_db.fetch_both(statement) except Exception as e: print("Transaction could not be completed:", e, ibm_db.stmt_errormsg()) finally: pass return result_set
def run_test_016(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) print "col:", cols rows = ibm_db.num_rows(result) print "affected row:", rows else: print ibm_db.stmt_errormsg() result = ibm_db.exec_immediate(conn, "delete from t_string where a=123") if result: cols = ibm_db.num_fields(result) print "col:", cols rows = ibm_db.num_rows(result) print "affected row:", rows else: print ibm_db.stmt_errormsg() ibm_db.close(conn) else: print "no connection:", ibm_db.conn_errormsg()
def run_test_045(self): conn = ibm_db.connect(config.database, config.user, config.password) with open("ibm_db_tests/pic1_out.jpg", "wb") as fp: result = ibm_db.exec_immediate( conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'") row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print(ibm_db.stmt_errormsg()) with open('ibm_db_tests/pic1_out.jpg', 'rb') as fp: pic_out = fp.read() with open('ibm_db_tests/pic1.jpg', 'rb') as fp: pic_in = fp.read() cmp = pic_in == pic_out print('Are the files the same:', cmp)
def get_latest_update(conn, schema, tablename): try: sql = "select max(id) from {}.{}".format(schema, tablename) logger.debug("Executing SQL: {}".format(sql)) stmt = ibm_db.exec_immediate(conn, sql) result = ibm_db.fetch_tuple(stmt) if result: return result[0] else: return 0 except Exception as e: if "SQL0204N" in ibm_db.stmt_errormsg(): logger.debug("{}.{} was missing, returning -1 for max(id)".format(schema, tablename)) return -1 else: raise e
def _check_table(self, tabschema, tabname): """ Checks if a table is defined. Returns True if defined, False otherwise. """ cols = [] rows = [] log.debug("Checking if {tabschema}.{tabname} exist".format(tabschema = tabschema, tabname = tabname)) try: self.query_db("select 1 from {tabschema}.{tabname}".format(tabschema = tabschema, tabname = tabname)) log.debug("Exist.") return True except: if 'is an undefined name' in ibm_db.stmt_errormsg(): return False else: raise
def read_dbInsparams(con, mess,dbInsParams): sqlQ = 'select name,value from SYSIBMADM.DBMCFG' stmt = ibm_db.exec_immediate(con, sqlQ) if not stmt: mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt) module.fail_json(msg=mess, changed=False) raise dictionary = ibm_db.fetch_assoc(stmt) while dictionary != False: name = dictionary["NAME"] value = dictionary["VALUE"] dbInsParams.append(dict(name=name,value=value)) dictionary = ibm_db.fetch_assoc(stmt) return True
def read_schemas(con,mess,dbSchemas): sqlQ = 'select SCHEMANAME,owner,cast(CREATE_TIME AS VARCHAR(35)) from syscat.SCHEMATA' stmt = ibm_db.exec_immediate(con, sqlQ) if not stmt: mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt) module.fail_json(msg=mess, changed=False) raise dictionary = ibm_db.fetch_assoc(stmt) while dictionary != False: schema_name = dictionary["SCHEMANAME"] owner = dictionary["OWNER"] create_time = dictionary["3"] dbSchemas.append(dict(schema_name=schema_name, owner=owner, create_time=create_time)) dictionary = ibm_db.fetch_assoc(stmt) return True
def insert_many_records(self, recordslist, commitnum, tablename=None): conn = self.get_conn() for i in xrange(0, len(recordslist), commitnum): time.sleep(0.05) if tablename is None: import random tbname = 'xxx%d' % (random.randint(1, 5)) else: tbname = tablename insertsql = "insert into %s(...) values(?, ?, ?, ?, ?, ?, \ ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \ ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \ ?, ?, ?, ?)" % tbname try: stmt = ibm_db.prepare(conn, insertsql) ibm_db.execute_many(stmt, tuple(recordslist[i:i + commitnum])) except: print ibm_db.stmt_error(), ibm_db.stmt_errormsg() finally: ibm_db.commit(conn)
def run_test_048(self): conn = ibm_db.connect(config.database, config.user, config.password) if (not conn): print("Could not make a connection.") return 0 server = ibm_db.server_info( conn ) fp = open("tests/spook_out.png", "wb") if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.exec_immediate(conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'") else: result = ibm_db.exec_immediate(conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'") if (not result): print("Could not execute SELECT statement.") return 0 row = ibm_db.fetch_tuple(result) if row: fp.write(row[0]) else: print(ibm_db.stmt_errormsg()) fp.close() cmp = (open('tests/spook_out.png', "rb").read() == open('tests/spook.png', "rb").read()) print("Are the files the same:", cmp)
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 read_tablespaces(con, mess,dbTableSpaces): sqlQ = 'select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT from SYSIBMADM.DBA_TABLESPACES' stmt = ibm_db.exec_immediate(con, sqlQ) if not stmt: mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt) module.fail_json(msg=mess, changed=False) raise dictionary = ibm_db.fetch_assoc(stmt) while dictionary != False: tablespace_name = dictionary["TABLESPACE_NAME"] block_size = dictionary["BLOCK_SIZE"] status = dictionary["STATUS"] contents = dictionary["CONTENTS"] segment_management = dictionary["SEGMENT_SPACE_MANAGEMENT"] dbTableSpaces.append(dict(tablespace_name=tablespace_name, block_size=block_size, status=status, contents=contents, segment_management=segment_management)) dictionary = ibm_db.fetch_assoc(stmt) return True
def run_test_157a(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) print "Starting..." if conn: sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed" result = ibm_db.exec_immediate(conn, sql) try: i = 2 row = ibm_db.fetch_assoc(result, i) while ( row ): if (server.DBMS_NAME[0:3] == 'IDS'): print "%-5d %-16s %-32s %10s" % (row['id'], row['name'], row['breed'], row['weight']) else: print "%-5d %-16s %-32s %10s" % (row['ID'], row['NAME'], row['BREED'], row['WEIGHT']) i = i + 2 row = ibm_db.fetch_assoc(result, i) except: print "SQLSTATE: %s" % ibm_db.stmt_error(result) print "Message: %s" % ibm_db.stmt_errormsg(result) print "DONE"
def main(): inputfile = '' mess = '' module = AnsibleModule( argument_spec = dict( db2server=dict(default="localhost"), db2port=dict(default=50000), db2user=dict(required=True), db2userpwd=dict(required=True), db2db=dict(required=True), sqlFile=dict(required=True), logFile=dict(default=None), ) ) if not db2py_installed: module.fail_json(msg="IBM_DB has not been located", changed=False) raise db2server = module.params["db2server"] db2port = module.params["db2port"] db2user = module.params["db2user"] db2userpwd = module.params["db2userpwd"] db2db = module.params["db2db"] inputfile = module.params["sqlFile"] outputfile = module.params["logFile"] conStr = "DATABASE="+db2db+";" conStr += "HOSTNAME="+db2server+";" conStr += "PORT="+db2port+";" conStr += "PROTOCOL=TCPIP;" conStr += "UID="+db2user+";" conStr += "PWD="+db2userpwd con = ibm_db.connect (conStr,"","") if not con: mess[0] = ibm_db.conn_errormsg() module.fail_json(msg=mess[0], changed=False) raise # If an output file has been specified, then create one if (outputfile) and outputfile != 'STDOUT': ofs = open(outputfile, "w") sqlQuery = '' with open(inputfile, 'r') as inp: lineNo = 0 for line in inp: lineNo = lineNo + 1 line = line.strip() line = line.lstrip() # Skip any blank lines or SQL remark characters if line == '\n' or line.find('--',0,2) != -1 or line.find('REM',0,3) != -1: sqlQuery = '' elif line == '/\n' or line.find(';') != -1: sqlQuery = sqlQuery + line sqlQuery = sqlQuery.strip() sqlQuery = sqlQuery.strip(';') sqlQuery = sqlQuery.strip('/') sqlQuery = sqlQuery.strip('\n') if sqlQuery and not sqlQuery.isspace(): stmt = ibm_db.exec_immediate(con, sqlQuery) if not stmt: mess = "Line: "+str(lineNo)+": "+sqlQuery + "->" + ibm_db.stmt_errormsg(stmt) module.fail_json(msg=mess, changed=False) raise if (outputfile) and outputfile != 'STDOUT': ofs.write(sqlQuery+"\nCommand processed\n") elif (outputfile) and outputfile == 'STDOUT': mess = mess + sqlQuery+"\nCommand processed\n" sqlQuery = '' else: sqlQuery = sqlQuery + line if (outputfile) and outputfile != 'STDOUT': ofs.close() inp.close() ibm_db.close(con) if not outputfile: mess = "SQL file processed" module.exit_json(msg=mess, changed=True)
def run_test_trusted_context_connect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print("Connection failed.") options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } tc_all_options = { ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Makeing normal connection and playing with it. tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_all_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "", tc_all_options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") get_tc_user = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if config.tc_user != get_tc_user: print("But trusted user is not switched.") ibm_db.close(tc_conn) # Making trusted connection and performing normal operations. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Trusted connection failed.") # Making trusted connection and switching to fake user. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") ibm_db.set_option(tc_conn, {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******"}, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_options_reversed = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user} if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options_reversed, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_user_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user} tc_pass_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass} if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) try: ibm_db.set_option(tc_conn, tc_pass_options, 1) except: print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing only user while switching when both user and password are required. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: ibm_db.set_option(tc_conn, tc_user_options, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Dropping the trusted context, in case it exists try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass # Dropping Role. try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass # Creating Role. try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass # Granting permissions to role. try: sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01" result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass # Creating trusted context try: sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION" result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Closing connection ibm_db.close(conn) else: print("Connection failed.") # Making trusted connection tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_user_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)" try: stmt = ibm_db.exec_immediate(tc_conn, sql_insert) except: print(ibm_db.stmt_errormsg()) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20" stmt = ibm_db.exec_immediate(tc_conn, sql_update) ibm_db.close(tc_conn) else: print("Connection failed.") # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print("Connection succeeded.") try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(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") #__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
def run_test_trusted_context_pconnect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print("Connection failed.") options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass} dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Making trusted connection and performing normal operations. tc_conn = ibm_db.pconnect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Trusted connection failed.") # Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections) for i in range(10): tc_conn = ibm_db.pconnect(dsn, "", "") if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("Explicit Trusted Connection succeeded.") # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print("Connection succeeded.") try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(conn) else: print("Connection failed.")
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)
# Query DB2 database sql = "select DISTINCT COMMUNITY_UUID, NAME, LASTMOD from sncomm.community;" stmt = ibm_db.prepare(conn_comm, sql) try: ibm_db.execute(stmt) while ibm_db.fetch_row(stmt) != False: name = ibm_db.result(stmt, "NAME") lastupdate = ibm_db.result(stmt, "LASTMOD") if (datetime.datetime.now() - datetime.timedelta(days=int(args.critical)) > lastupdate): count_critical = count_critical + 1 list_critical = list_critical + name + "\n" elif (datetime.datetime.now() - datetime.timedelta(days=int(args.warning)) > lastupdate): count_warning = count_warning + 1 list_warning = list_warning + name + "\n" except: print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg() sys.exit(3) # Nagios standard feedback if (count_critical!=0): print("Critical: There are communites which were not updates since " + args.critical + " days" + list_critical.encode('utf-8') + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(2) elif (count_warning!=0): print("Warning: There are communites which were not updates since " + args.warning + " days" + list_warning.encode('utf-8') + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(1) elif (count!=0): print("Communites are OK " + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(0) else: print("UKNOWN number of Communities without an owner!")
def run_test_024(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn != 0: drop = 'DROP TABLE test_primary_keys' try: result = ibm_db.exec_immediate(conn, drop) except: pass drop = 'DROP TABLE test_keys' try: result = ibm_db.exec_immediate(conn, drop) except: pass drop = 'DROP TABLE test_foreign_keys' try: result = ibm_db.exec_immediate(conn, drop) except: pass statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))' result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO test_primary_keys VALUES (1)" result = ibm_db.exec_immediate(conn, statement) statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \ PRIMARY KEY(name))' result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO test_keys VALUES ('vince', 1)" result = ibm_db.exec_immediate(conn, statement) statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))' result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)" result = ibm_db.exec_immediate(conn, statement) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_primary_keys') else: stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_PRIMARY_KEYS') row = ibm_db.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.foreign_keys(conn, None, None, None, None, config.user, 'test_keys') else: stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, 'TEST_KEYS') row = ibm_db.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, None, None) else: stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, None) row = ibm_db.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, config.user, 'test_foreign_keys') else: stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, 'TEST_FOREIGN_KEYS') row = ibm_db.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[6]) print(row[7]) try: stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, None) row = ibm_db.fetch_tuple(stmt) except: if (not stmt): print(ibm_db.stmt_errormsg()) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, 'dummy_schema') else: stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, 'dummy_schema') row = ibm_db.fetch_tuple(stmt) if(not row): print("No Data Found") else: print(row) ibm_db.close(conn) else: print(ibm_db.conn_errormsg()) print("Connection failed\n")