def run_test_220(self): if sys.platform == 'zos': conn = ibm_db.pconnect(config.database,'','') else: 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 summation(q, txtBefore, txtAfter): sum_str = q[2] # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL) # Prepare statement sum_stmt = ibm_db.prepare(conn, sum_str) if sum_stmt == False: raise Usage("Failed to prepare sum query") # Execute statement if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query") sum = ibm_db.fetch_tuple(sum_stmt) ibm_db.commit(conn) # Print result set to output file try: f = open(OUTPUT_FILE_PATH + "/output.txt", "a") f.write(txtBefore + str(sum) + txtAfter) f.close() except IOError, e: raise Usage("Failed to manipulate output.txt.\n")
def summation(q): sum_str = q[2] # Connect to DB start=time.time() #sys.stderr.write("Start sum %s\n"%(start)) for n in range(int(round(NBSWAPS/NBSWAPTHREADS))): #sys.stderr.write("Sum %d"%n) conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL) # Prepare statement sum_stmt = ibm_db.prepare(conn, sum_str) if (sum_stmt == False): raise Usage("Failed to prepare sum query") # Execute statement if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query") sum= ibm_db.fetch_tuple(sum_stmt) ibm_db.commit(conn) # Print result set to output file try: f = open(OUTPUT_FILE_PATH, 'a') f.write(str(sum)+'\n') # f.close except IOError, e: raise Usage("Failed to manipulate sum.sql.\n") finally:
def run_test_007(self): options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] == 'IDS'): options1 = options2 stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0].strip() data = ibm_db.fetch_both(stmt) print "" stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0].strip() data = ibm_db.fetch_both(stmt) ibm_db.close(conn) else: print "Connection failed."
def _connect(self) -> ibm_db.IBM_DBConnection: """ Connecting to the host using required parameters and returning the `Connection` object Returns: a connection object that will be used in order to execute queries """ conn: ibm_db.IBM_DBConnection = None try: if self.use_persistent: demisto.info("Initializing Persistent connection") conn = ibm_db.pconnect(self.create_url, "", "", conn_options=self._options()) else: demisto.info("Initializing Non-Persistent connection") conn = ibm_db.connect(self.create_url, "", "", conn_options=self._options()) except Exception: demisto.error(f"Connection State:\n{ibm_db.conn_error}") demisto.error(f"Connection Error:\n{ibm_db.conn_errormsg()}") raise DemistoException( f"DB2 Connection Failed:\n{ibm_db.conn_errormsg()}") return conn
def experiment(query_str,g): # generate nb of parameters for query matchList = re.findall('\?', query_str) nbParams = len(matchList) if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)") # Connect to DB conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','') if conn is None: raise Usage(ibm_db.conn_errormsg()) # Prepare statement query_stmt = ibm_db.prepare(conn, query_str) if (query_stmt == False): raise Usage("Failed to prepare query") # Execute statement for i in range(NBQUERIES): if (nbParams == 0): if ibm_db.execute(query_stmt) == False: raise Usage("Failed to execute the query") else: t = g.getWrite(i) l = list(t) u = [l[j] for j in range(len(l)) if j in ATTLIST] if ibm_db.execute(query_stmt, tuple(u)) == False: raise Usage("Failed to execute the query") nbtuples = 0 while (ibm_db.fetch_tuple(query_stmt) != False): nbtuples += 1 print "Query"+str(i)+": "+str(nbtuples)+" fetched." # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def run_test_007(self): options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] == 'IDS'): options1 = options2 stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print(data[0].strip()) data = ibm_db.fetch_both(stmt) print("") stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print(data[0].strip()) data = ibm_db.fetch_both(stmt) ibm_db.close(conn) else: print("Connection failed.")
def db2connection(): # connectionBBB = pyodbc.connect('DSN=SOO;UID=db2adminyang;PWD=Alan5799') # connectionBBB = pyodbc.connect('DSN = SO;UID = db2adminyang;PWD = Alan5799') # connectionBBB = pyodbc.connect('Provider=IBMOLEDB.DB2COPY1;Info=TRUE;ID=db2adminyang;Password=Alan5799;Source = SOGDB') # connectionBBB = pyodbc.connect('DRIVER={IBM DB2 ODBC DRIVER - DB2COPY1};SERVER=127.0.0.0;DATABASE=SOGDB;UID=db2adminyang;PWD=Alan5799') # connectionBBB = pyodbc.connect( # DRIVER='{ODBC}', # server='localhost', # DATABASE='SOO', # uid='db2adminyang', # pwd='Alan5799') conn = ibm_db.pconnect( "DATABASE=SOGDB;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2adminyang;PWD=Alan5799", "", "") sql = "SELECT * from DB2ADMINYANG.MST_CUSTOMER" stmt = ibm_db.exec_immediate(conn, sql) c2 = ibm_db.fetch_both(stmt) # while dictionary != False: # print # "The ID is : ", dictionary["EMPNO"] # print # "The Name is : ", dictionary[1] # dictionary = ibm_db.fetch_both(stmt) # c2 = connectionBBB.cursor() return c2
def write(q,data): # initialize vars write_str = q # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL) if TL: ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode") # Prepare Statements write_stmt = ibm_db.prepare(conn, write_str) if (write_stmt == False): raise Usage("Failed to prepare write statement") for t in data: # execute insertN statement if (WRITE_MODE == 'insertN'): if ibm_db.execute(write_stmt, t) == False: raise Usage("Failed to execute insertN statement") elif (WRITE_MODE == 'updateN'): l = list(t) u = [l[j] for j in range(len(l)) if j in ATTLIST] if ibm_db.execute(write_stmt, tuple(u)) == False: raise Usage("Failed to execute updateN statement") if (TRANS_MODE == 'N'): ibm_db.commit(conn) if (TRANS_MODE == '1'): ibm_db.commit(conn) # ibm_db.commit(conn) # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def connection(): try: connect = ibm_db.pconnect( "DATABASE=funder;HOSTNAME=dione.is.inf.uni-due.de;PORT=50008;PROTOCOL=TCPIP;UID=dbp008;PWD=quee3ahm;", "", "") except Exception as e: print(e) return connect
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 get_connection(dbname, hostname, port, userid, password): conn_str = ( "DATABASE={};" "HOSTNAME={};" "PORT={};" "PROTOCOL=TCPIP;" "UID={};" "PWD={};" ).format(dbname, hostname, port, userid, password) logger.debug("DB2 Connection: {}".format(conn_str)) return ibm_db.pconnect(conn_str, "", "")
def __enter__(self): try: self._conn = ibm_db.pconnect(self.app.config['DBNAME'] , self.app.config['DBUSER'] , self.app.config['DBPW']) except: log.error("Database connection failed.") log.error(ibm_db.conn_errormsg()) raise else: log.debug("Connected to {dbname} user {dbuser} using ****".format(dbname = self.app.config['DBNAME'] , dbuser = self.app.config['DBUSER'])) return self
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 executeSelect(statement): connection = ibm_db.pconnect(url, '', '') statement = ibm_db.prepare(connection, statement) ibm_db.execute(statement) data = ibm_db.fetch_tuple(statement) result = [] while (data): result.append(data) data = ibm_db.fetch_tuple(statement) ibm_db.free_stmt(statement) ibm_db.close(connection) return result
def connect(self, config=None): """Returns a new database connection. When `DB2_POOL_CONNECTIONS` is true a connection supporting pooling is used. :param config: configuration object used to establish connections. current_app.config is used by default. """ if config is None: config = current_app.config conn_string = self._connection_string(config) if config['DB2_POOL_CONNECTIONS']: connection = ibm_db.pconnect(conn_string, '', '') else: connection = ibm_db.connect(conn_string, '', '') return ibm_db_dbi.Connection(connection)
def conectaDB2(): try: conn = ibm_db.pconnect( "DATABASE=" + database + "; HOSTNAME=" + hostname + "; PORT=" + port + "; PROTOCOL=" + protocol + "; UID=" + userid + "; PWD=" + password + ";", '', '') except: print('Connection fault with DATABASE!\nError Code: {0}'.format( ibm_db.conn_error())) sys.exit() info = ibm_db.client_info(conn) print( 'CONNECTED! DATA_SOURCE_NAME: {0} CONN_CODEPAGE: {3} DRIVER_NAME: {1} DRIVER_ODBC_VER: {2} ODBC_VER: {4}' .format(info.DATA_SOURCE_NAME, info.DRIVER_NAME, info.DRIVER_ODBC_VER, info.CONN_CODEPAGE, info.ODBC_VER)) return conn
def write(q,index,mutex): # initialize vars write_str = q[0] matchList = re.findall('\?', write_str) nbParams = len(matchList) write_data = q[1] # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL) ret2 = None if TL: ret2 = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode") print "Lock: %s"%ret # Prepare Statements write_stmt = ibm_db.prepare(conn, write_str) if (write_stmt == False): raise Usage("Failed to prepare write statement") # Perform insertions/updates for i in range(NBWRITES/NBTHREADS): mutex.acquire() v = index.value index.value -= 1 mutex.release() t = write_data.getWrite(v) if v >= 0 and t != None: # execute insertN statement if (WRITE_MODE == 'insertN'): if ibm_db.execute(write_stmt, t) == False: raise Usage("Failed to execute insertN statement") elif (WRITE_MODE == 'updateN'): l = list(t) u = [l[j] for j in range(len(l)) if j in ATTLIST] if ibm_db.execute(write_stmt, tuple(u)) == False: raise Usage("Failed to execute updateN statement") if (TRANS_MODE == 'N'): print "commit" ibm_db.commit(conn) if (TRANS_MODE == '1'): print "commit" ibm_db.commit(conn) # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def update1(q): write_str = q[0] # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL) # Prepare statement write_stmt = ibm_db.prepare(conn, write_str) if (write_stmt == False): raise Usage("Failed to prepare sum query") # Execute statement if ibm_db.execute(write_stmt) == False: raise Usage("Failed to execute the sum query") ibm_db.commit(conn) # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def __init__(self, connection, configuration): # connection and configuration are passed in the arguments for transmit # ex: python main.py transmit db2 '{"host":"localhost", "port":"6603"}' '{"auth": {"mysql_username": "******","mysql_password": "******", "mysql_hostname": "localhost", "mysql_database": "sampledb" } }' pin self.auth = configuration.get('auth') try: self.client = ibm_db.pconnect( "DATABASE=" + self.auth.get("mysql_database") + ";HOSTNAME=" + connection.get("host", "") + ";PORT=" + connection.get("port", "31490") + ";UID=" + self.auth.get("mysql_username") + ";PWD=" + self.auth.get("mysql_password"), "", "") self.state = ibm_db.active(self.client) # handle exceptions later TODO except Exception as e: raise e
def swap(q): swap1_str = q[0] swap2_str = q[1] # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL) # Prepare Statements swap1_stmt = ibm_db.prepare(conn, swap1_str) if swap1_stmt == False: raise Usage("Failed to prepare swap1 query") swap2_stmt = ibm_db.prepare(conn, swap2_str) if swap2_stmt == False: raise Usage("Failed to prepare swap2 update") # Execute Statements nbrep = int(round(NBSWAPS / NBSWAPTHREADS)) for i in range(nbrep): x = random.randint(RANGE_LOW, RANGE_UP / 2) y = random.randint(x, RANGE_UP) if ibm_db.execute(swap1_stmt, (x,)) == False: raise Usage("Failed to execute the swap1 query (x)") valX = ibm_db.fetch_tuple(swap1_stmt) if valX == False: raise Usage("Failed to iterate over the swap1 result set (x)") if ibm_db.execute(swap1_stmt, (y,)) == False: raise Usage("Failed to execute the swap1 query (y)") valY = ibm_db.fetch_tuple(swap1_stmt) if valY == False: raise Usage("Failed to iterate over the swap1 result set (y)") time.sleep(0.1) if ibm_db.execute(swap2_stmt, (valY[0], x)) == False: raise Usage("Failed to execute the swap2 query (x, valY)") if ibm_db.execute(swap2_stmt, (valX[0], y)) == False: raise Usage("Failed to execute the swap1 query (y, valX)") ibm_db.commit(conn) # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def connect_db(self, db, pconnect=True, trytimes=3): try: if pconnect: conn = ibm_db.pconnect(db, "", "") else: conn = ibm_db.connect(db, "", "") except Exception: if pconnect: self.log.error('Database persistent connect failted(' + str(trytimes) + '): ' + traceback.format_exc()) else: self.log.error('Database connect failted(' + str(trytimes) + '): ' + traceback.format_exc()) try: if ibm_db.active(conn): self.server_info = self.get_server_info(conn) return conn except Exception: pass if pconnect: self.log.error('Database persistent connect inactive(' + str(trytimes) + ')') else: self.log.error('Database connect inactive(' + str(trytimes) + ')') if trytimes > 0: try: ibm_db.close(conn) except Exception: pass self.log.info('Database connect try again(' + str(trytimes) + ')') trytimes -= 1 return self.connect_db(db, pconnect=False, trytimes=trytimes) return False
else: userid = args.dbname if args.schema: schema = args.schema else: schema = userid conn_str = ( "DATABASE={};" "HOSTNAME={};" "PORT={};" "PROTOCOL=TCPIP;" "UID={};" "PWD={};" ).format(args.dbname, args.hostname, args.port, userid, args.password) logger.debug("DB2 Connection: {}".format(conn_str)) conn = ibm_db.pconnect(conn_str, "", "") if args.output_file: fout = open(args.output_file, 'w') else: fout = sys.stdout outputcsv = bool(distutils.util.strtobool(args.outputcsv)) report_changes_for_contexts(schema, fout, outputcsv) endtime = datetime.utcnow() logger.info("End of Script: {}".format(endtime)) print("Script ran for: {}".format(endtime - starttime)) except Exception as e: conn_error = ibm_db.conn_error() stmt_error = ibm_db.stmt_error() if conn_error != '': print("Error Code: {} Msg: {}".format(conn_error, ibm_db.conn_errormsg())) elif stmt_error != '':
def main(self): args = self.get_option() dbname = args.dbname # args1 input_file = args.input_file # args2 rows = args.rows # args3 username = connect_conf.username password = connect_conf.password schema = username.upper() l_list = [] g_list = [] col_types = [] col_sizes = [] col_size_fractions = [] col_num = 0 count = 0 COMMIT_ROW = 1000 # connect try: conn = ibm_db.pconnect(dbname, username, password) except Exception as e: print("Connection failed") traceback.print_exc() return ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) # get the sql try: insert_sql = self.get_sql(input_file) except Exception as e: print("Cannot read sqlfile.") traceback.print_exc() return stmt = ibm_db.prepare(conn, insert_sql) if not stmt: print("Failed in prepare.") return # get the table name from sql, and check tabname = re.split("[ ]*INSERT INTO[ ]+", insert_sql)[1] tabname = re.split("[ ]+VALUES", tabname)[0] if tabname is None: print("Error in contents of sqlfile.") return # verify whether the table exist rc = self.chk_table(conn, schema, tabname) if not rc: print("table name " + tabname + " is not found in " + dbname) return # get the number of the columns result = ibm_db.columns(conn, None, None, tabname) col = ibm_db.fetch_tuple(result) while (col): col_num = col_num + 1 col = ibm_db.fetch_tuple(result) # list of column type and column size col_info = ibm_db.columns(conn, None, None, tabname) col = ibm_db.fetch_tuple(col_info) while (col): col_types.append(col[5]) col_sizes.append(col[6]) col_size_fractions.append(col[8]) col = ibm_db.fetch_tuple(col_info) for i in range(rows): count = 0 random.seed() for j in range(len(col_types)): count = count + 1 if self.chk_par_mark(count, insert_sql): if col_types[j] == "CHAR": param = self.get_random_char(col_sizes[j]) elif col_types[j] == "VARCHAR": param = self.get_random_char(col_sizes[j]) elif col_types[j] == "SMALLINT": param = self.get_random_smallint() elif col_types[j] == "INTEGER": param = self.get_random_integer() elif col_types[j] == "BIGINT": param = self.get_random_long() elif col_types[j] == "REAL": param = self.get_random_real() elif col_types[j] == "FLOAT": param = self.get_random_double() elif col_types[j] == "DOUBLE": param = self.get_random_double() elif col_types[j] == "DECIMAL": digit = col_sizes[j] - col_size_fractions[j] param = self.get_random_decimal(digit) elif col_types[j] == "NUMERIC": digit = col_sizes[j] - col_size_fractions[j] param = self.get_random_decimal(digit) elif col_types[j] == "DATE": param = self.get_random_date() elif col_types[j] == "TIME": param = self.get_random_time() elif col_types[j] == "TIMESTAMP": param = self.get_random_timestamp() elif col_types[j] == "BLOB": param = self.get_random_byte(col_sizes[j]) elif col_types[j] == "CLOB": param = self.get_random_char(col_sizes[j]) else: param = '' # set the parameter to the list self.set_param_list(param, l_list) # end of the columns if count == col_num: self.concat_list(g_list, l_list) l_list = [] if ((i + 1) % COMMIT_ROW == 0): #print g_list rc = ibm_db.execute_many( stmt, tuple(tuple(x) for x in g_list)) rc = ibm_db.commit(conn) g_list = [] if len(g_list) != 0: print g_list rc = ibm_db.execute_many(stmt, tuple(tuple(x) for x in g_list)) rc = ibm_db.commit(conn) ibm_db.close(conn)
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 xrange(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 connect_db(): return ibm_db.pconnect("DATABASE=ctddev;HOSTNAME=192.168.1.246;PORT=50010;UID=db2dev;PWD=db2dev","","")
def get_connection(): conn = ibm_db.pconnect(st.secrets['dsn'], st.secrets['user'], st.secrets['pws']) return conn
def connectioncheck_handler(): global connection,url if (active(connection) == False): connection = ibm_db.pconnect(url ,'' ,'') return None
connString += ";PORT=" + portNum # Required To Connect To A Server connString += ";PROTOCOL=TCPIP" # Required To Connect To A Server connString += ";UID=" + userID connString += ";PWD=" + passWord # Display A Status Message Indicating An Attempt To Establish Ten Connections To A Remote # Db2 Server Is About To Be Made print("\nEstablishing 10 connections to the \'" + hostName + "\' server ... \n") # Establish Ten Connections To The Db2 Server Specified for loopCounter in range(10): # Attempt To Establish A Db2 Server Connection try: svrConnection[loopCounter] = ibm_db.pconnect(connString, '', '') except Exception: pass # If A Connection Could Not Be Established, Display An Error Message And Continue if svrConnection[loopCounter] is None: print("\nERROR: Unable to connect to the \'" + hostName + "\' server.") continue # Otherwise, Display A "Connection Ready" Status Message else: print(" Connection {:>2} ready!".format(loopCounter + 1)) # Add A Blank Line To The End Of The List Of Connections Created print()
def __init__(self): with open('env/dash_db.json') as config: self.cred = json.load(config) self.conn = ibm_db.pconnect(self.cred["dsn"], self.cred["username"], self.cred["password"])
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.")
import ibm_db import ibm_db_dbi import config import pprint p = pprint.PrettyPrinter(indent=4) ibm_conn = ibm_db.pconnect( 'DATABASE={dbna};HOSTNAME={host};PORT={port};PROTOCOL={prot};UID={user};PWD={pasw}' .format(dbna=config.DATABASE, host=config.HOSTNAME, port=config.PORTNUMB, prot=config.PROTOCOL, user=config.USERNAME, pasw=config.PASSWORD), '', '') conn = ibm_db_dbi.Connection(ibm_conn) cursor = conn.cursor() # Command authority Checker (can be used inside of commands) class AuthorityCheck: def __init__(self, connection, bot_roles_table='bot_roles', actions='actions', permissions='permissions', guild_roles='guild_roles', debug=False, debug_verbose=False): # Database connection self.conn = connection
log.loc[log[prefix+'timestamp'].notna(),prefix+'month'] = \ log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[:2].astype(int) log.loc[log[prefix+'timestamp'].notna(),prefix+'year'] = \ log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[6:10].astype(int) log.loc[log[prefix+'timestamp'].notna(),prefix+'hour'] = \ log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[10:12].astype(int) log.loc[log[prefix+'timestamp'].notna(),prefix+'min'] = \ log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[13:15].astype(int) log.loc[log[prefix+'timestamp'].notna(),prefix+'sec'] = \ log.loc[log[prefix+'timestamp'].notna(),prefix+'timestamp'].str[17:].astype(int) #DB2 database credentials credencial_db2 = {} # Establish conection db2 = ibm_db.pconnect(credencial_db2['dsn'], "", "") # Parameters for different executions # Dropping table drop = False if drop: q_drop = "DROP TABLE gen_info;" # Deleting table delete = True if delete: q_delete = "DELETE * FROM gen_info" ibm_db.exec_immediate(db2, q_drop) # Creating table create = True
f.close() ############################################################################### # Check that two file names were entered if len(sys.argv) is not 3 or sys.argv[2][sys.argv[2].rfind(".")+1:] != "cfg": print "[*] Usage: python main.py [ddl] [config.cfg] - see README for config format" sys.exit() # read in config sections node1 = config('node1', sys.argv[2]) node2 = config('node2', sys.argv[2]) catalog = config('catalog', sys.argv[2]) # make persistant connections to distributed databases db1 = ibm_db.pconnect(node1['hostname'], node1['username'],node1['passwd']) db2 = ibm_db.pconnect(node2['hostname'], node2['username'],node2['passwd']) cat = ibm_db.pconnect(catalog['hostname'], catalog['username'],catalog['passwd']) # create catalog if doesn't exist create_catalog(cat,catalog) # list of nodes and queries to iterate through nodes = [db1,db2] querys = readDDL(sys.argv[1]) # foreach DDL, execute queries on all nodes for query in querys: for node in nodes: Thread(target=exec_query,args=(node,query,)).start() if node is db1:
def pconnDb(dbname, dbuser, dbpasswd, host='182.29.0.1', port=51001): array = { db2.SQL_ATTR_AUTOCOMMIT : db2.SQL_AUTOCOMMIT_OFF } conn = db2.pconnect("HOSTNAME=%s;PROTOCOL=TCPIP;\ PORT=%d;DATABASE=%s;UID=%s;PWD=%s;" \ %(host, port, dbname, dbuser, dbpasswd), '', '',array) return conn
sys.exit() # read in config sections node1 = config_extract(sys.argv[1], 'node1') node2 = config_extract(sys.argv[1], 'node2') catalog = config_extract(sys.argv[1], 'catalog') # Create strings for connections #db1_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (node1["hostname"],node1["ip"],node1["port"],node1["username"],node1["passwd"]) #db2_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (node2["hostname"],node2["ip"],node2["port"],node2["username"],node2["passwd"]) #catalog_info = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s" % (catalog["hostname"],catalog["ip"],catalog["port"],catalog["username"],catalog["passwd"]) #print db1_info # make persistant connections to distributed databases db1 = ibm_db.pconnect(node1["hostname"],node1["username"],node1["passwd"]) db2 = ibm_db.pconnect(node2["hostname"],node2["username"],node2["passwd"]) cat = ibm_db.pconnect(catalog["hostname"],catalog["username"],catalog["passwd"]) # create catalog if doesn't exist create_catalog(cat,catalog) # list of nodes and queries to iterate through nodes = [db1,db2] querys = readDDL(sys.argv[2]) # foreach DDL, execute queries on all nodes for query in querys: query = query.rstrip() if query == ';': continue