def run_test_124(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id") cols = ibm_db.num_fields(result) j = 0 row = ibm_db.fetch_both(result) while ( row ): for i in range(0, cols): field = ibm_db.field_name(result, i) value = row[ibm_db.field_name(result, i)] if (value == None): value = '' print "%s:%s" % (field, value) print "---------" j += 1 if (j == 10): break row = ibm_db.fetch_both(result) ibm_db.close(conn) print "done" else: print ibm_db.conn_errormsg()
def summation(q, txtBefore, txtAfter): sum_str = q[2] # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL) # Prepare statement sum_stmt = ibm_db.prepare(conn, sum_str) if sum_stmt == False: raise Usage("Failed to prepare sum query") # Execute statement if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query") sum = ibm_db.fetch_tuple(sum_stmt) ibm_db.commit(conn) # Print result set to output file try: f = open(OUTPUT_FILE_PATH + "/output.txt", "a") f.write(txtBefore + str(sum) + txtAfter) f.close() except IOError, e: raise Usage("Failed to manipulate output.txt.\n")
def run_test_071(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: rc = ibm_db.close(conn) if (rc == True): print("ibm_db.close succeeded") else: print("ibm_db.close FAILED\n") else: print("%s" % ibm_db.conn_errormsg()) print(",sqlstate=%s" % ibm_db.conn_error()) print("%s" % ibm_db.conn_errormsg()) print("%s" % ibm_db.conn_errormsg()) print("%s" % ibm_db.conn_errormsg()) print("%s" % ibm_db.conn_errormsg())
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_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_102(self): conn = ibm_db.connect(config.database, config.user, config.password) if (not conn): print ibm_db.conn_errormsg() server = ibm_db.server_info( conn ) if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2")): result = ibm_db.exec_immediate(conn, "VALUES(1)") #throw :unsupported unless result if (not result): raise Exception('Unsupported') print ibm_db.num_fields(result) else: print '1' ibm_db.close(conn)
def run_test_091(self): try: conn = ibm_db.connect(config.database, "y", config.password) print "??? No way." except: err = ibm_db.conn_errormsg() print err
def write(q,data): # initialize vars write_str = q # Connect to DB conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD) if conn is None: raise Usage(ibm_db.conn_errormsg()) ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF) # Set isolation level ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL) if TL: ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode") # Prepare Statements write_stmt = ibm_db.prepare(conn, write_str) if (write_stmt == False): raise Usage("Failed to prepare write statement") for t in data: # execute insertN statement if (WRITE_MODE == 'insertN'): if ibm_db.execute(write_stmt, t) == False: raise Usage("Failed to execute insertN statement") elif (WRITE_MODE == 'updateN'): l = list(t) u = [l[j] for j in range(len(l)) if j in ATTLIST] if ibm_db.execute(write_stmt, tuple(u)) == False: raise Usage("Failed to execute updateN statement") if (TRANS_MODE == 'N'): ibm_db.commit(conn) if (TRANS_MODE == '1'): ibm_db.commit(conn) # ibm_db.commit(conn) # Disconnect from DB status = ibm_db.close(conn) if status == False: raise Usage("Failed to close db connection.\n")
def run_test_090(self): try: conn = ibm_db.connect("x", config.user, config.password) print("??? No way.") except: err = ibm_db.conn_errormsg() print(err)
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_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_197(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: try: rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test") except: pass rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))") rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)") print "Test first index table:" if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn,None,config.user,"index_test",True) else: result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True) row = ibm_db.fetch_tuple(result) ## skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'IDS'): row = ibm_db.fetch_tuple(result) print row[2] # TABLE_NAME print row[3] # NON_UNIQUE print row[5] # INDEX_NAME print row[8] # COLUMN_NAME try: rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2") except: pass rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))") rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)") print "Test second index table:" if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn,None,config.user,"index_test2",True) else: result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True) row = ibm_db.fetch_tuple(result) ### skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'IDS'): row = ibm_db.fetch_tuple(result) print row[2] # TABLE_NAME print row[3] # NON_UNIQUE print row[5] # INDEX_NAME print row[8] # COLUMN_NAME print "Test non-existent table:" if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True) else: result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True) row = ibm_db.fetch_tuple(result) if row: print "Non-Empty" else: print "Empty" else: print 'no connection: ' + ibm_db.conn_errormsg()
def run_test_191(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.columns(conn,None,config.user,"emp_photo"); else: result = ibm_db.columns(conn,None,None,"EMP_PHOTO"); i = 0 row = ibm_db.fetch_both(result) while ( row ): if (server.DBMS_NAME[0:3] == 'IDS'): if ( (row['column_name'] != 'emp_rowid') and (i < 3) ): print("%s,%s,%s,%s\n" % (row['table_schem'], row['table_name'], row['column_name'], row['is_nullable'])) else : if ( (row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3) ): print("%s,%s,%s,%s\n" % (row['TABLE_SCHEM'], row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE'])) i = i + 1 row = ibm_db.fetch_both(result) print("done!") else: print("no connection: ", ibm_db.conn_errormsg())
def run_test_092(self): try: conn = ibm_db.connect(config.database, config.user, "z") print "??? No way." except: err = ibm_db.conn_errormsg() print err
def run_test_061(self): conn = ibm_db.connect(config.database, config.user, config.password) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if conn: server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER} ibm_db.set_option(conn, op, 0) result = ibm_db.tables(conn, None, 'T'); i = 0 row = ibm_db.fetch_both(result) while ( row ): str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE'] if (i < 4): print str i = i + 1 row = ibm_db.fetch_both(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4') print "done!" else: print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_103(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn, "select * from org, project order by project.projname") cols = ibm_db.num_fields(result) j = 1 row = ibm_db.fetch_tuple(result) while row: print "%d) " % j for i in range(0, cols): print "%s " % row[i] j += 1 if j > 10: break row = ibm_db.fetch_tuple(result) ibm_db.close(conn) else: print ibm_db.conn_errormsg()
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_023(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if (conn != 0): if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.column_privileges(conn, None, config.user, 'animals') else: stmt = ibm_db.column_privileges(conn, None, None, 'ANIMALS') row = ibm_db.fetch_tuple(stmt) if row: print row[0] print row[1] print row[2] print row[3] print row[4] print row[5] print row[6] print row[7] ibm_db.close(conn) else: print ibm_db.conn_errormsg() print "Connection failed\n\n"
def run_test_025(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_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_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))' result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO test_foreign_keys VALUES (1)" result = ibm_db.exec_immediate(conn, statement) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.primary_keys(conn, None, config.user, 'test_primary_keys') else: stmt = ibm_db.primary_keys(conn, None, None, 'TEST_PRIMARY_KEYS') row = ibm_db.fetch_tuple(stmt) print row[2] print row[3] print row[4] ibm_db.close(conn) else: print ibm_db.conn_errormsg() print "Connection failed\n"
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 run_test_190(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if conn: if server.DBMS_NAME[0:3] == "IDS": result = ibm_db.columns(conn, None, config.user, "employee") else: result = ibm_db.columns(conn, None, None, "EMPLOYEE") row = ibm_db.fetch_tuple(result) while row: str = row[1] + "/" + row[3] print str row = ibm_db.fetch_tuple(result) print "done!" else: print "no connection:", ibm_db.conn_errormsg()
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 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 run_test_103(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate( conn, "select * from org, project order by project.projname") cols = ibm_db.num_fields(result) j = 1 row = ibm_db.fetch_tuple(result) while (row): print("%d) " % j) for i in range(0, cols): print("%s " % row[i]) j += 1 if (j > 10): break row = ibm_db.fetch_tuple(result) ibm_db.close(conn) else: print(ibm_db.conn_errormsg())
def write_to_db(): # connect to DB2 try: db2conn = ibm_db.connect( "DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "") except: connerr = "Connection to database failed" print(connerr + ": " + str(ibm_db.conn_errormsg())) return {"error": connerr} delete_stmt = ibm_db.exec_immediate(db2conn, "DELETE from for_app") filesAll = get_bucket_contents() for file in filesAll: sql = "INSERT INTO for_app VALUES (?)" stmt = ibm_db.prepare(db2conn, sql) ibm_db.bind_param(stmt, 1, file[0]) ibm_db.execute(stmt) ibm_db.close(db2conn) return render_template("success.html")
def run_test_025(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_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_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))' result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO test_foreign_keys VALUES (1)" result = ibm_db.exec_immediate(conn, statement) if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.primary_keys(conn, None, config.user, 'test_primary_keys') elif ('zos' in sys.platform): stmt = ibm_db.primary_keys(conn, None, config.user, 'TEST_PRIMARY_KEYS') else: stmt = ibm_db.primary_keys(conn, None, None, 'TEST_PRIMARY_KEYS') row = ibm_db.fetch_tuple(stmt) print(row[2]) print(row[3]) print(row[4]) ibm_db.close(conn) else: print(ibm_db.conn_errormsg()) print("Connection failed\n")
def run_test_019(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.SQL_ATTR_ROWCOUNT_PREFETCH: ibm_db.SQL_ROWCOUNT_PREFETCH_ON }) 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.close(conn) else: print("no connection:", ibm_db.conn_errormsg())
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 db2conn(): try: if conType == 'LOCAL': conn = ibm_db_dbi.connect('DATABASE=*LOCAL') elif conType == 'REMOTE': conn = pyodbc.connect(DRIVER='IBM i Access ODBC Driver', SYSTEM='your IBMi IP', UID='IBM I USERNAME', PWD='IBM I PASSWORD') else: return None, None c1 = conn.cursor() return c1, conn except: if conType == 'LOCAL': print("Connection Error:", ibm_db.conn_errormsg()) else: print("Connection Error:", conn.conn_errormsg()) return None, None
def Submit(self): response = natural_language_understanding.analyze( text=r1,features=Features(sentiment=SentimentOptions())).get_result() print(json.dumps(response, indent=2)) response = response["sentiment"]["document"]["label"] print(response) text1 = self.textEdit_3.toPlainText() text2 = self.textEdit_4.toPlainText() text3 = self.textEdit_5.toPlainText() try: conn = ibm_db.connect(dsn, "", "") print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) except: print ("Unable to connect: ", ibm_db.conn_errormsg() ) insertQuery = "insert into REVIEW values (?, ?, ?, ?, ?)" params=(text3,text1,text2,r1,response) stmt = ibm_db.prepare(conn, insertQuery) ibm_db.execute(stmt,params) ibm_db.close(conn)
def run_test_023(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if (conn != 0): if (server.DBMS_NAME[0:3] == 'IDS'): stmt = ibm_db.column_privileges(conn, None, config.user, 'animals') else: stmt = ibm_db.column_privileges(conn, None, None, 'ANIMALS') row = ibm_db.fetch_tuple(stmt) if row: print(row[0]) print(row[1]) print(row[2]) print(row[3]) print(row[4]) print(row[5]) print(row[6]) print(row[7]) ibm_db.close(conn) else: print(ibm_db.conn_errormsg()) print("Connection failed\n\n")
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()
try: conn = ibm_db.connect(dsn, "", "") print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) #Deleting only the rows which meet some criteria ## DeleteQuery = "delete from "+TABLENAME+" WHERE id="+'EXAMPLE' ## DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery) # Or you may just want to delete everything: DeleteQuery = "delete from " + TABLENAME DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery) #Checking the result selectQuery = "select * from " + TABLENAME selectStmt = ibm_db.exec_immediate(conn, selectQuery) #Fetch the Dictionary, if the only row is enough for you #ibm_db.fetch_both(selectStmt) #Fetch all the rows and print the first([0]) and the second ("USERNAME") columns for those rows while ibm_db.fetch_row(selectStmt) != False: print(" ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME")) except: print("something went wrong, but I don't know what exactly ", ibm_db.conn_errormsg()) ibm_db.close(conn) print("Connection closed")
#populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None)) try: ibm_db.execute_many(stmt_insert, params) except Exception, inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print inst print row_count ibm_db.close(conn) else: print ibm_db.conn_errormsg() #__END__ #__LUW_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__ZOS_EXPECTED__ #4 #10, Sanders
def run_test_createDropDB(self): database = "test001" conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % ( database, config.hostname, config.port, config.user, config.password, ) conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % ( config.hostname, config.port, config.user, config.password, ) # for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server conn_attach = ibm_db.connect(conn_str_attach, "", "") if conn_attach: conn = False try: conn = ibm_db.connect(conn_str, "", "") except: pass if conn: ibm_db.close(conn) conn = False try: ibm_db.dropdb(conn_attach, database) except: print "Errors occurred during drop database" try: # create databse rc = ibm_db.createdb(conn_attach, database) if rc: conn = ibm_db.connect(conn_str, "", "") if conn: print "database created sucessfully" ibm_db.close(conn) conn = False else: print "database is not created" else: print "Errors occurred during create database" # drop databse rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, "", "") except: print "datbase droped sucessfully" if conn: print "Errors occurred during drop database" ibm_db.close(conn) conn = False else: print "Errors occurred during delete database" # create database with codeset option rc = ibm_db.createdb(conn_attach, database, "iso88591") if rc: conn = ibm_db.connect(conn_str, "", "") server_info = ibm_db.server_info(conn) if conn and (server_info.DB_CODEPAGE == 819): print "database with codeset created sucessfully" ibm_db.close(conn) conn = False else: print "database is not created" else: print "Errors occurred during create database" # drop database rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, "", "") except: print "datbase droped sucessfully" if conn: print "Errors occurred during drop database" ibm_db.close(conn) conn = False else: print "Errors occurred during drop database" except: print ibm_db.conn_errormsg() pass ibm_db.close(conn_attach) else: print ibm_db.conn_errormsg()
def run_test_197(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if conn: try: rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test") except: pass rc = ibm_db.exec_immediate( conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))") rc = ibm_db.exec_immediate( conn, "CREATE UNIQUE INDEX index1 ON index_test (id)") print("Test first index table:") if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn, None, config.user, "index_test", True) else: result = ibm_db.statistics(conn, None, None, "INDEX_TEST", True) row = ibm_db.fetch_tuple(result) ## skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'IDS'): row = ibm_db.fetch_tuple(result) print(row[2]) # TABLE_NAME print(row[3]) # NON_UNIQUE print(row[5]) # INDEX_NAME print(row[8]) # COLUMN_NAME try: rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2") except: pass rc = ibm_db.exec_immediate( conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))") rc = ibm_db.exec_immediate( conn, "CREATE INDEX index2 ON index_test2 (data)") print("Test second index table:") if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn, None, config.user, "index_test2", True) else: result = ibm_db.statistics(conn, None, None, "INDEX_TEST2", True) row = ibm_db.fetch_tuple(result) ### skipping table info row. statistics returns informtation about table itself for informix ### if (server.DBMS_NAME[0:3] == 'IDS'): row = ibm_db.fetch_tuple(result) print(row[2]) # TABLE_NAME print(row[3]) # NON_UNIQUE print(row[5]) # INDEX_NAME print(row[8]) # COLUMN_NAME print("Test non-existent table:") if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.statistics(conn, None, config.user, "non_existent_table", True) else: result = ibm_db.statistics(conn, None, None, "NON_EXISTENT_TABLE", True) row = ibm_db.fetch_tuple(result) if row: print("Non-Empty") else: print("Empty") else: print('no connection: ' + ibm_db.conn_errormsg())
connString = "ATTACH=FALSE" # Attach To A Database; Not A Server connString += ";DATABASE=" + dbName # Required To Connect To A Database connString += ";PROTOCOL=TCPIP" connString += ";UID=" + userID connString += ";PWD=" + passWord # Attempt To Establish A Connection To The Database Specified try: dbConnection = ibm_db.connect(connString, '', '') except Exception: pass # If A Db2 Database Connection Could Not Be Established, Display An Error Message And Exit if dbConnection is None: print("\nERROR: Unable to connect to the \'" + dbName + "\' database.") errorMsg = ibm_db.conn_errormsg() print("\n" + errorMsg + "\n") exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Add Additional Db2 Database-Related Processing Here ... # Attempt To Close The Db2 Database Connection That Was Just Opened if not dbConnection is None: print("Disconnecting from the \'" + dbName + "\' database ... ", end="") try: returnCode = ibm_db.close(dbConnection) except Exception:
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_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 openConnection(self): """Attempt to establish a Db2 server or database connection.""" # Define And Initialize The Appropriate Local Variables connString = "DRIVER={IBM DB2 ODBC DRIVER}" msgString = "" # If A Host Name Was Not Specified, Use The Name That Is Assigned To The Current Server if self.hostName is '': self.hostName = socket.gethostname() # If A Port Number Was Not Specified, Use Port Number 50000 if self.portNum is '': self.portNum = '50000' # Display An Appropriate Status Message And Add The Correct "ATTACH" Value To The # Connection String Variable (connString) msgString = "\nConnecting to the " if self.dsType is 'LOCAL_SVR': print(msgString + "local server ... ", end="") if get_ipython(): # If Running With IPython, ... connString += ";ATTACH=TRUE" # Attach To A Server; Not A Database else: connString = "ATTACH=TRUE" # Attach To A Server; Not A Database elif self.dsType is 'SERVER': print(msgString + self.hostName + " server ... ", end="") connString += ";ATTACH=TRUE" # Attach To A Server; Not A Database else: print(msgString + self.dbName + " database ... ", end="") connString += ";ATTACH=FALSE" # Attach To A Database; Not A Server # If Appropriate, Construct The Portion Of The Connection String That Will Be Used To # Establish A Connection To The Local Db2 Server if self.dsType is 'LOCAL_SVR': if get_ipython(): # If Running With IPython, ... connString += ";HOSTNAME=" + socket.gethostname() connString += ";PORT=" + self.portNum connString += ";PROTOCOL=TCPIP" connString += ";UID=" + self.userID connString += ";PWD=" + self.passWord # Otherwise, Construct The Portion Of The Connection String That Will Be Used To # Establish A Connection To A Remote Db2 Server Or A Db2 Database else: connString += ";DATABASE=" + self.dbName # Only Used To Connect To A Database connString += ";HOSTNAME=" + self.hostName # Only Used To Connect To A Server connString += ";PORT=" + self.portNum # Only Used To Connect To A Server connString += ";PROTOCOL=TCPIP" connString += ";UID=" + self.userID connString += ";PWD=" + self.passWord # Attempt To Establish A Connection To The Appropriate Db2 Server Or Database # If Running With IPython (i.e., Jupyter Notebook), ... if get_ipython(): try: self.connectionID = ibm_db.connect(connString, '', '') except Exception: pass # If Running With Python, ... else: try: self.connectionID = ibm_db.connect(connString, self.userID, self.passWord) except Exception: pass # If A Connection Could Not Be Established, Display An Appropriate Error Message # And Set The Function Return Code Attribute To "False" if self.connectionID is None: msgString = "\nERROR: Unable to connect to the " if self.dsType is 'LOCAL_SVR': print(msgString + "local server ... ", end="") elif self.dsType is 'SERVER': print(msgString + self.hostName + " server.") else: print(msgString + self.dbName + " database.") msgString = ibm_db.conn_errormsg() print(msgString + "\n") print("Connection string used: " + connString + "\n") self.returnCode = False # If A Connection Could Be Established, Complete The Status Message And Set The # Return Code Attribute To "True" else: print("Done!\n") self.returnCode = True
def main(): 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) ) ) 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"] 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 dbParams = list() if not read_dbparams(con,mess,dbParams): module.fail_json(msg=mess[0], changed=False) raise dbInsParams = list() if not read_dbInsparams(con, mess,dbInsParams): module.fail_json(msg=mess[0], changed=False) raise dbTableSpaces = list() if not read_tablespaces(con, mess,dbTableSpaces): module.fail_json(msg=mess[0], changed=False) raise dbSchemas = list() if not read_schemas(con,mess,dbSchemas): module.fail_json(msg=mess[0], changed=False) raise module.exit_json( ansible_facts = dict( dbParams = dbParams, dbInsParams = dbInsParams, dbTableSpaces = dbTableSpaces, dbSchemas = dbSchemas ), changed = False )
def __init__(self, id, nome, compra, preco): self.id = id self.nome = nome self.compra = compra self.preco = preco #Replace the placeholder values with your actual Db2 hostname, username, and password: dsn_hostname = "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net" # e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net" dsn_uid = "mxb40007" # e.g. "abc12345" dsn_pwd = "z887bnjptc6sd^p0" # e.g. "7dBZ3wWt9XN6$o0J" dsn_driver = "{IBM DB2 ODBC DRIVER}" dsn_database = "BLUDB" # e.g. "BLUDB" dsn_port = "50000" # e.g. "50000" dsn_protocol = "TCPIP" # i.e. "TCPIP" dsn = ("DRIVER={0};" "DATABASE={1};" "HOSTNAME={2};" "PORT={3};" "PROTOCOL={4};" "UID={5};" "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd) #print the connection string to check correct values are specified print(dsn) #DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter #Create database connection try: conn = ibm_db.connect(dsn, "", "") print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) except: print("Unable to connect: ", ibm_db.conn_errormsg()) #Apaga a tabela PI_7sem #dropQuery = "drop table PI_7sem" #Executa o statement #ibm_db.exec_immediate(conn, dropQuery) #Construindo a tabela. Caso haja mudanças na tabela é aqui que que deve-se alterar #createQuery = "create table PI_7sem(ID VARCHAR(40) NOT NULL, Nome VARCHAR(20),Compra VARCHAR(20), Valor DOUBLE)" #Não está como PRIMARY KEY #ibm_db.exec_immediate(conn,createQuery) data = (self.id, self.nome, self.compra, self.preco) format_str = "insert into PI_7sem values ('{id_pi}', '{name}', '{comp}', '{value}')" insertQuery = format_str.format(id_pi=data[0], name=data[1], comp=data[2], value=data[3]) ibm_db.exec_immediate(conn, insertQuery) selectStmt = ibm_db.exec_immediate(conn, "SELECT * FROM PI_7sem") slct = ibm_db.fetch_tuple(selectStmt) n = 0 selecao = [] while (slct): selecao.append(slct) slct = ibm_db.fetch_tuple(selectStmt) n = n + 1 self.selecao = selecao ibm_db.close(conn)
def run_test_execute_many(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Drop the tabmany table, in case it exists drop = "DROP TABLE TABMANY" try: result = ibm_db.exec_immediate(conn, drop) except: pass #create table tabmany create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))" ibm_db.exec_immediate(conn, create) #Populate the tabmany table with execute_many insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)" params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'), (40, 'OBrien')) stmt_insert = ibm_db.prepare(conn, insert) ibm_db.execute_many(stmt_insert, params) #check the number of rows inserted row_count = ibm_db.num_rows(stmt_insert) print(row_count) # chaeck the inserted columns select = "SELECT * FROM TABMANY" stmt_select = ibm_db.exec_immediate(conn, select) cols = ibm_db.fetch_tuple(stmt_select) while (cols): print("%s, %s" % (cols[0], cols[1])) cols = ibm_db.fetch_tuple(stmt_select) #populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None)) try: ibm_db.execute_many(stmt_insert, params) except Exception as inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print(inst) print(row_count) ibm_db.close(conn) else: print(ibm_db.conn_errormsg()) #__END__ #__LUW_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__ZOS_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__IDS_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3
if args.output_file: fout = open(args.output_file, 'w') else: fout = sys.stdout print_legend(args.hostname1, hostname2) # Compare DNs/ModifyTimestamp from first and second LDAP servers compare_all_entry_modify_timestamps(args.hostname1, conn1, schema1, hostname2, conn2, schema2, fout) 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())) if conn1: print("Connection issue with server#2 most probably.") else: print("Connection issue with server#1 most probably.") elif stmt_error != '': print("Error Code: {} Msg: {}".format(stmt_error, ibm_db.stmt_errormsg())) raise e finally: if fout and fout is not sys.stdout: fout.close() if conn1: ibm_db.close(conn1) if conn2: ibm_db.close(conn2)
print() # Attempt To Create A New Database At The Remote Server Using Connection Number Five if not svrConnection[5] is None: print("Creating a database named " + dbName + " using Connection 5. Please wait.") currentConnection = svrConnection[5] try: returnCode = ibm_db.createdbNX(currentConnection, dbName) except Exception: pass # If The Database Could Not Be Created, Display An Error Message And Exit if returnCode is None: print("ERROR: Unable to create the " + dbName + " database.\n") errorMsg = ibm_db.conn_errormsg(svrConnection) print(errorMsg + "\n") # Otherwise, Display A Status Message Indicating The Database Was Created else: print("\nThe database \"" + dbName + "\" has been created!\n") # Attempt To Close The Db2 Server Connection (Connection 5) print("Closing Db2 server Connection 5 ... ", end="") try: returnCode = ibm_db.close(currentConnection) except Exception: pass # If The Connection Was Not Closed, Display An Error Message if returnCode is False:
#DB2数据与写csv数据 import ibm_db import sys import os import time import datetime import csv try: conn = ibm_db.connect( "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=valen;PWD=kingling819@;", "", "") except: print "no connection:", ibm_db.conn_errormsg() else: print "The connection was successful" file_object = open('call_result.txt', 'a') str_sql = "select region_code, call_time from valen.tbl_my_test b " cresult = "" file_csv = open('my.csv', 'wb') writer = csv.writer(file_csv) writer.writerow(['Column1', 'Column2']) stmt = ibm_db.exec_immediate(conn, str_sql) r = ibm_db.fetch_both(stmt) while (r): #print str(r[0]), str(r[1]) cresult = cresult + str(r[0]) + ',' + str(r[1]) + '\r\n'
def run_test_createdbNX(self): database = 'test001' conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password) conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server conn_attach = ibm_db.connect(conn_str_attach, '', '') if conn_attach: conn = False try: conn = ibm_db.connect(conn_str, '', '') except: pass if conn: ibm_db.close(conn) conn = False try: ibm_db.dropdb(conn_attach, database) except: print 'Errors occurred during drop database' try: # call createdbNX without codeset argument when specified database not exeist rc = ibm_db.createdbNX(conn_attach, database) if rc: conn = ibm_db.connect(conn_str, '', '') if conn: print 'database created sucessfully' ibm_db.close(conn) conn = False else: print 'database is not created' else: print 'Error occurred during create db if not exist' conn = ibm_db.connect(conn_str, '', '') if conn: ibm_db.close(conn) conn = False # call recreate db with codeset argument when specified database exist rc = ibm_db.createdbNX(conn_attach, database, 'iso88591') if rc: conn = ibm_db.connect(conn_str, '', '') server_info = ibm_db.server_info( conn ) if conn and (server_info.DB_CODEPAGE != 819): print 'database with codeset created sucessfully' ibm_db.close(conn) conn = False else: print 'Database not created' else: print 'Error occurred during create db if not exist with codeset' #drop database rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, '', '') except: print 'datbase droped sucessfully' if conn: print 'Errors occurred during drop database' ibm_db.close(conn) conn = False else: print 'Errors occurred during drop database' except: print ibm_db.conn_errormsg() pass ibm_db.close(conn_attach) else: print ibm_db.conn_errormsg()
def run_test_createDropDB(self): database = 'test001' conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password) conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server conn_attach = ibm_db.connect(conn_str_attach, '', '') if conn_attach: conn = False try: conn = ibm_db.connect(conn_str, '', '') except: pass if conn: ibm_db.close(conn) conn = False try: ibm_db.dropdb(conn_attach, database) except: print('Errors occurred during drop database') try: #create databse rc = ibm_db.createdb(conn_attach, database) if rc: conn = ibm_db.connect(conn_str, '', '') if conn: print('database created sucessfully') ibm_db.close(conn) conn = False else: print('database is not created') else: print('Errors occurred during create database') #drop databse rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, '', '') except: print('datbase droped sucessfully') if conn: print('Errors occurred during drop database') ibm_db.close(conn) conn = False else: print('Errors occurred during delete database') #create database with codeset option rc = ibm_db.createdb(conn_attach, database, 'iso88591') if rc: conn = ibm_db.connect(conn_str, '', '') server_info = ibm_db.server_info( conn ) if conn and (server_info.DB_CODEPAGE == 819): print('database with codeset created sucessfully') ibm_db.close(conn) conn = False else: print('database is not created') else: print('Errors occurred during create database') #drop database rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, '', '') except: print('datbase droped sucessfully') if conn: print('Errors occurred during drop database') ibm_db.close(conn) conn = False else: print('Errors occurred during drop database') except: print(ibm_db.conn_errormsg()) pass ibm_db.close(conn_attach) else: print(ibm_db.conn_errormsg())
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') elif ('zos' in sys.platform): 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') elif ( 'zos' in sys.platform): 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) elif ( 'zos' in sys.platform): 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') elif ( 'zos' in sys.platform): 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') elif ( 'zos' in sys.platform): 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") #__END__ #__LUW_EXPECTED__ #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999 #No Data Found #__ZOS_EXPECTED__ #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999 #No Data Found #__SYSTEMI_EXPECTED__ #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999 #__IDS_EXPECTED__ #test_primary_keys #id #test_keys #idf #test_primary_keys #id #test_keys #idf #test_keys #name #test_foreign_keys #namef #test_keys #name #test_foreign_keys #namef #[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999 #No Data Found #__ZOS_ODBC_EXPECTED__ #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_PRIMARY_KEYS #ID #TEST_KEYS #IDF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #TEST_KEYS #NAME #TEST_FOREIGN_KEYS #NAMEF #{DB2 FOR OS/390}{ODBC DRIVER} SQLSTATE=HY009 ERRLOC=10:17:6 SQLCODE=-99999 #No Data Found
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")
cols = ibm_db.fetch_tuple( stmt_select ) #populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None) ) try: ibm_db.execute_many(stmt_insert, params) except Exception, inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print inst print row_count ibm_db.close(conn) else: print ibm_db.conn_errormsg() #__END__ #__LUW_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__ZOS_EXPECTED__ #4 #10, Sanders #20, Pernal
def run_test_createdbNX(self): database = 'test001' conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password) conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server conn_attach = ibm_db.connect(conn_str_attach, '', '') if conn_attach: conn = False try: conn = ibm_db.connect(conn_str, '', '') except: pass if conn: ibm_db.close(conn) conn = False try: ibm_db.dropdb(conn_attach, database) except: print('Errors occurred during drop database') try: # call createdbNX without codeset argument when specified database not exeist rc = ibm_db.createdbNX(conn_attach, database) if rc: conn = ibm_db.connect(conn_str, '', '') if conn: print('database created sucessfully') ibm_db.close(conn) conn = False else: print('database is not created') else: print('Error occurred during create db if not exist') conn = ibm_db.connect(conn_str, '', '') if conn: ibm_db.close(conn) conn = False # call recreate db with codeset argument when specified database exist rc = ibm_db.createdbNX(conn_attach, database, 'iso88591') if rc: conn = ibm_db.connect(conn_str, '', '') server_info = ibm_db.server_info( conn ) if conn and (server_info.DB_CODEPAGE != 819): print('database with codeset created sucessfully') ibm_db.close(conn) conn = False else: print('Database not created') else: print('Error occurred during create db if not exist with codeset') #drop database rc = ibm_db.dropdb(conn_attach, database) if rc: try: conn = ibm_db.connect(conn_str, '', '') except: print('datbase droped sucessfully') if conn: print('Errors occurred during drop database') ibm_db.close(conn) conn = False else: print('Errors occurred during drop database') except: print(ibm_db.conn_errormsg()) pass ibm_db.close(conn_attach) else: print(ibm_db.conn_errormsg())
def db2_error(): global _sqlerror, _sqlcode, _sqlstate, _connected _sqlerror = "No error text available" _sqlcode = -99999 _sqlstate = "-99999" try: if (_connected == True): errmsg = ibm_db.stmt_errormsg().replace('\r', ' ') errmsg = errmsg[errmsg.rfind("]") + 1:].strip() else: errmsg = ibm_db.conn_errormsg().replace('\r', ' ') errmsg = errmsg[errmsg.rfind("]") + 1:].strip() _sqlerror = errmsg msg_start = errmsg.find("SQLSTATE=") if (msg_start != -1): msg_end = errmsg.find(" ", msg_start) if (msg_end == -1): msg_end = len(errmsg) _sqlstate = errmsg[msg_start + 9:msg_end] else: _sqlstate = "0" msg_start = errmsg.find("SQLCODE=") if (msg_start != -1): msg_end = errmsg.find(" ", msg_start) if (msg_end == -1): msg_end = len(errmsg) _sqlcode = errmsg[msg_start + 8:msg_end] try: _sqlcode = int(_sqlcode) except: pass else: _sqlcode = 0 except: errmsg = "Unknown error." _sqlcode = -99999 _sqlstate = "-99999" _sqlerror = errmsg return msg_start = errmsg.find("SQLSTATE=") if (msg_start != -1): msg_end = errmsg.find(" ", msg_start) if (msg_end == -1): msg_end = len(errmsg) _sqlstate = errmsg[msg_start + 9:msg_end] else: _sqlstate = "0" msg_start = errmsg.find("SQLCODE=") if (msg_start != -1): msg_end = errmsg.find(" ", msg_start) if (msg_end == -1): msg_end = len(errmsg) _sqlcode = errmsg[msg_start + 8:msg_end] try: _sqlcode = int(_sqlcode) except: pass else: _sqlcode = 0
"UID={5};" "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd) #print the connection string to check correct values are specified print(dsn) #DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter #Create database connection try: conn = ibm_db.connect(dsn, "", "") print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) except: print("Unable to connect: ", ibm_db.conn_errormsg()) ################################# #BASIC SQL ###################### #Lets first drop the table INSTRUCTOR in case it exists from a previous attempt dropQuery = "drop table INSTRUCTOR" #Now execute the drop statment dropStmt = ibm_db.exec_immediate(conn, dropQuery) #################### #Construct the Create Table DDL statement - replace the ... with rest of the statement createQuery = "create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))" #Now fill in the name of the method and execute the statement createStmt = ibm_db.exec_immediate(conn, createQuery)
def run_test_061(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = ibm_db.exec_immediate(conn, create) except: pass if conn: server = ibm_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER} ibm_db.set_option(conn, op, 1) if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.tables(conn, None, 't') else: result = ibm_db.tables(conn, None, 'T') i = 0 row = ibm_db.fetch_both(result) while (row): str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE'] if (i < 4): print(str) i = i + 1 row = ibm_db.fetch_both(result) ibm_db.exec_immediate(conn, 'DROP TABLE t.t1') ibm_db.exec_immediate(conn, 'DROP TABLE t.t2') ibm_db.exec_immediate(conn, 'DROP TABLE t.t3') ibm_db.exec_immediate(conn, 'DROP TABLE t.t4') print("done!") else: print("no connection: %s" % ibm_db.conn_errormsg())
#DB credentials, stored in config file dsn = ( "DRIVER={0};" "DATABASE={1};" "HOSTNAME={2};" "PORT={3};" "PROTOCOL={4};" "UID={5};" "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd) try: conn = ibm_db.connect(dsn, "", "") print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) #Table structure from the create_table file #'''(id varchar(10) PRIMARY KEY NOT NULL, username VARCHAR (30), name varchar(30), Col3 varchar(30), Col4 varchar(30))"''' #Insert sigle row of data: insertQuery = "insert into "+TABLENAME+" values ('0000000','Fiction','Kathy','','')" insertStmt = ibm_db.exec_immediate(conn, insertQuery) #Or insert few rows: ##insertQuery2 = "insert into "+TABLENAME+" values ('0000000','Fiction','Kathy','',''), ('0000001','SAMPLE','DANA','','') ##insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2) except: print ("something gone wrong, but I don't know what exactly ", ibm_db.conn_errormsg() ) ibm_db.close(conn) print ("Connection closed")
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_017(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: if ('zos' in sys.platform): result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0") else: result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN }) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) if ('zos' in sys.platform): result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0") else: result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY }) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) if ('zos' in sys.platform): result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0") else: result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH: ibm_db.SQL_ROWCOUNT_PREFETCH_ON }) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) if ('zos' in sys.platform): result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0") else: result = ibm_db.exec_immediate( conn, "SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH: ibm_db.SQL_ROWCOUNT_PREFETCH_OFF }) if result: rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) ibm_db.close(conn) else: print("no connection:", ibm_db.conn_errormsg())
connectionStr = '' security = '' if (connections['db2']['host'].lower() == 'y'): security = ';SECURITY=SSL;SSLClientKeystoredb=/opt/ibm/db2/GSK8Store/keyclient.kdb;SSLClientKeystash=/opt/ibm/db2/GSK8Store/keyclient.sth; ' connectionStr = "DATABASE=" + connections['db2']['database'] connectionStr = connectionStr + ";HOSTNAME=" + connections['db2']['host'] connectionStr = connectionStr + ";PORT=" + connections['db2']['port'] connectionStr = connectionStr + ";PROTOCOL=TCPIP;UID=" + connections['db2']['user'] connectionStr = connectionStr + ";PWD=" + connections['db2']['pwd'] connectionStr = connectionStr + security try: conn = ibm_db.connect(connectionStr, "", "") except: print(ibm_db.conn_errormsg()) else: print("db2 connected ...") def cdctable(cdcdo): if cdcdo == 'archive' : print('set tables to archive') sql = "UPDATE " + ASN_SCHEMA + ".IBMSNAP_REGISTER SET STATE = 'A' WHERE SOURCE_OWNER <> '' " stmt = ibm_db.exec_immediate(conn, sql) else: sql = "SELECT SOURCE_OWNER,SOURCE_TABLE, STATE FROM " + ASN_SCHEMA + ".IBMSNAP_REGISTER WHERE SOURCE_OWNER <> '' " stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) while dictionary != False: