def run_test_195(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")): drop = 'DROP TABLE test_195' try: result = ibm_db.exec_immediate(conn, drop) except: pass create = 'CREATE TABLE test_195 (id INTEGER, data XML)' result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')" ibm_db.exec_immediate(conn, insert) sql = "SELECT data FROM test_195" stmt = ibm_db.prepare(conn, sql) ibm_db.execute(stmt) result = ibm_db.fetch_assoc(stmt) while( result ): print "Output:", result result = ibm_db.fetch_assoc(stmt) ibm_db.close(conn) else: print "Native XML datatype is not supported."
def run_test_006(self): options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} conn = ibm_db.connect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] == 'IDS'): options1 = options2 stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print (data[0]) data = ibm_db.fetch_both(stmt) print ("") stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print (data[0]) data = ibm_db.fetch_both(stmt) ibm_db.close(conn) else: print ("Connection failed.")
def 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 run_test_300(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if server: print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME) print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER) print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME) print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME) print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS) print "KEYWORDS: int(%d)" % len(server.KEYWORDS) print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION) il = '' for opt in server.ISOLATION_OPTION: il += opt + " " print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il) print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE) print "PROCEDURES:", server.PROCEDURES print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR) print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS ibm_db.close(conn) else: print "Error."
def run_test_022(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) ibm_db.autocommit(conn, 0) ac = ibm_db.autocommit(conn) if ac != 0: print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test") #continue ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)") stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) ibm_db.rollback(conn) stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) ibm_db.close(conn) else: print("Connection failed.")
def run_test_116(self): conn = None is_alive = ibm_db.active(conn) if is_alive: print("Is active") else: print("Is not active") conn = ibm_db.connect(config.database, config.user, config.password) is_alive = ibm_db.active(conn) if is_alive: print("Is active") else: print("Is not active") ibm_db.close(conn) is_alive = ibm_db.active(conn) if is_alive: print("Is active") else: print("Is not active") # Executing active method multiple times to reproduce a customer reported defect print(ibm_db.active(conn)) print(ibm_db.active(conn)) print(ibm_db.active(conn)) conn = ibm_db.connect(config.database, config.user, config.password) print(ibm_db.active(conn)) print(ibm_db.active(conn)) print(ibm_db.active(conn))
def run_test_034(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if server.DBMS_NAME[0:3] == "IDS": op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER} ibm_db.set_option(conn, op, 1) result = ibm_db.exec_immediate(conn, "select * from staff") row = ibm_db.fetch_assoc(result) if row: # printf("%5d ",row['ID']) # printf("%-10s ",row['NAME']) # printf("%5d ",row['DEPT']) # printf("%-7s ",row['JOB']) # printf("%5d ", row['YEARS']) # printf("%15s ", row['SALARY']) # printf("%10s ", row['COMM']) # puts "" print "%5d %-10s %5d %-7s %5d %15s %10s" % ( row["ID"], row["NAME"], row["DEPT"], row["JOB"], row["YEARS"], row["SALARY"], row["COMM"], ) ibm_db.close(conn)
def run_test_032(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6") while (ibm_db.fetch_row(stmt)): if (server.DBMS_NAME[0:3] == 'IDS'): id = ibm_db.result(stmt, "id") breed = ibm_db.result(stmt, "breed") name = ibm_db.result(stmt, "name") weight = ibm_db.result(stmt, "weight") else: id = ibm_db.result(stmt, "ID") breed = ibm_db.result(stmt, "BREED") name = ibm_db.result(stmt, "NAME") weight = ibm_db.result(stmt, "WEIGHT") print "int(%d)" % id print "string(%d) \"%s\"" % (len(breed), breed) print "string(%d) \"%s\"" % (len(name), name) print "string(%d) \"%s\"" % (len(str(weight)), weight) ibm_db.close(conn) else: print "Connection failed."
def run_test_020(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print rows ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) ac = ibm_db.autocommit(conn) if ac != 0: print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test" #continue ibm_db.exec_immediate(conn, "DELETE FROM animals") stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print rows ibm_db.rollback(conn) stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print rows ibm_db.close(conn) else: print "Connection failed."
def run_test_03a(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while ( ibm_db.fetch_row(stmt) ): breed = ibm_db.result(stmt, 1) print ("string(%d) \"%s\"" % (len(breed), breed)) if (server.DBMS_NAME[0:3] == 'IDS'): name = ibm_db.result(stmt, "name") else: name = ibm_db.result(stmt, "NAME") print ("string(%d) \"%s\"" % (len(name), name)) # following field does not exist in result set if (server.DBMS_NAME[0:3] == 'IDS'): name = ibm_db.result(stmt, "passport") else: name = ibm_db.result(stmt, "PASSPORT") print (name) ibm_db.close(conn) else: print ("Connection failed.")
def run_test_142(self): sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?" conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.prepare(conn, sql) weight = 200.05 mass = 2.0 ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT) ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT) result = ibm_db.execute(stmt) if ( result ): row = ibm_db.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print i row = ibm_db.fetch_tuple(stmt) ibm_db.close(conn) else: print "Connection failed."
def appTransaction(p_vehicleNum): if(vehicleDetails.has_key(p_vehicleNum)): l_connection = dB_init() if(l_connection == None): print("Database Connection Failed on Database Query") return dateorder_query = "SELECT * FROM DASH6461.TOLL_DATA WHERE VEHICLE_NUMBER = \'"+str(p_vehicleNum)+"\' ORDER BY DATES DESC,TIME DESC LIMIT 5" try: l_db_statement = ibm_db.exec_immediate(l_connection, dateorder_query) l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("appHistoricalGraph_twodatequery exec/fetch_ERROR : " + str(e)) l_count = 0 while l_temp_dict: l_new_date = l_temp_dict["DATES"].strftime("%d-%m-%Y") l_new_time = l_temp_dict["TIME"].strftime("%H:%M:%S") l_final_date = l_new_date + " " + l_new_time vehicleTransaction[l_count] = [l_final_date,l_temp_dict["TOLL_NAME"],l_temp_dict["AMOUNT_DEDUCT"],l_temp_dict["AMOUNT_ADDED"],l_temp_dict["AVAI_BAL"]] l_count+=1 try: l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("appHistoricalGraph_twodatequery fetch_ERROR : " + str(e)) transVehicle[p_vehicleNum] = vehicleTransaction ibm_db.free_stmt(l_db_statement) ibm_db.close(l_connection) pubnub.publish(channel=p_vehicleNum+p_vehicleNum,message=transVehicle[p_vehicleNum]) else: pass
def assert_expectf(self, testFuncName): callstack = inspect.stack(0) try: prepconn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(prepconn) ibm_db.close(prepconn) if (server.DBMS_NAME[0:2] == "AS"): pattern = self.expected_AS(callstack[1][1]) elif (server.DBMS_NAME == "DB2"): pattern = self.expected_ZOS(callstack[1][1]) elif (server.DBMS_NAME[0:3] == "IDS"): pattern = self.expected_IDS(callstack[1][1]) else: pattern = self.expected_LUW(callstack[1][1]) sym = ['\[','\]','\(','\)'] for chr in sym: pattern = re.sub(chr, '\\' + chr, pattern) pattern = re.sub('%s', '.*?', pattern) pattern = re.sub('%d', '\\d+', pattern) result = re.match(pattern, self.capture(testFuncName)) self.assertNotEqual(result, None) finally: del callstack
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_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 generalSetting(p_vehicleNumber): if(p_vehicleNumber != None): if(vehicleDetails.has_key(p_vehicleNumber)): if(vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS] == 0): pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\ "vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]}) l_connection = dB_init() if(l_connection == None): print("Database Connection Failed on Database Query") return l_database_query = "SELECT * FROM "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_1+" WHERE VEHICLE_NUMBER = '"+str(p_vehicleNumber)+"'" try: l_db_statement = ibm_db.exec_immediate(l_connection, l_database_query) l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("rfid Register exec/fetch_ERROR : " + str(e)) while l_temp_dict: if(l_temp_dict["VEHICLE_NUMBER"] == p_vehicleNumber): vehicleDetails[p_vehicleNumber][DETAILS_BALANCE] = l_temp_dict["WALLET_BAL"] try: l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("rfid Register fetch_ERROR : " + str(e)) ibm_db.free_stmt(l_db_statement) ibm_db.close(l_connection) pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\ "vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]}) else: appSetting(p_vehicleNumber,vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS]) else: pubnub.publish(channel=p_vehicleNumber, message={"warning":"Vehicle Not Registered with the Automated System"}) else: pass
def run_test_311(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON ) # Drop the tab_num_literals table, in case it exists drop = 'DROP TABLE tab_num_literals' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the animal table create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')" res = ibm_db.exec_immediate(conn, insert) print "Number of inserted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44" res = ibm_db.exec_immediate(conn, sql) print "Number of updated rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'" res = ibm_db.exec_immediate(conn, sql) print "Number of deleted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) ibm_db.rollback(conn) ibm_db.close(conn)
def run_test_001(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: print ("Connection succeeded.") ibm_db.close(conn) else: print ("Connection failed.")
def run_test_002(self): conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.user, config.password) conn = ibm_db.connect(conn_str, '', '') if conn: print ("Connection succeeded.") ibm_db.close(conn) else: print ("Connection failed.")
def main(): conn = ibm_db.connect("cs421","","") taxes = taxable_cash_dolla(conn) for i in taxes: print "Month: " + i + ", Taxes paid: " + taxes[i] ibm_db.close(conn)
def main(): conn = None stmt = None if not conf.has_option('passwd'): conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8')) try: conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'), conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '') stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8')) try: result = ibm_db.fetch_tuple(stmt) except: rows = ibm_db.num_rows(stmt) if rows != -1: print u'Обработано строк %d' % rows else: print u'Команда выполнена' return if result: column_conv = [] head = u'' underline=u'' for i in xrange(len(result)): if i != 0: head += u'|' underline += u'+' name = ibm_db.field_name(stmt, i) size = ibm_db.field_display_size(stmt, i) if len(name) > size: size = len(name) if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size: size = len(u'NULL') type_field = ibm_db.field_type(stmt, i) if type_field == 'float' or type_field == 'real' or type_field == 'decimal': column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float}) elif type_field == 'int' or type_field == 'bigint': column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int}) else: column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert}) head += name.center(size) underline += u'-' * size print head print underline while( result ): print conv(result, column_conv) result = ibm_db.fetch_tuple(stmt) else: print u'Результата не возвращено' except Exception as e: print >> sys.stderr, e sys.exit(-1) finally: if stmt: ibm_db.free_result(stmt) if conn: ibm_db.close(conn)
def run_test_005(self): baduser = "******" badpass = "******" dsn = "DATABASE=" + config.database + ";UID=" + baduser + ";PWD=" + badpass + ";" try: conn = ibm_db.connect(dsn, "", "") print ("odd, ibm_db.connect succeeded with an invalid user / password") ibm_db.close(conn) except: print ("Ooops")
def appHistoricalGraph(p_containerid,p_timeSpan): global DATABASE_TABLE_NAME #Connecting to the database l_connection = dB_init() if(l_connection == None): logging.error("Database Connection Failed on Database Query") return #Evaluvating the number of days to query the db p_timeSpan = p_timeSpan - 1 l_refill_history = dict() l_consumption_history = dict() l_temp_dict = dict() l_sdat = datetime.datetime.now().date() l_edat = l_sdat - datetime.timedelta(days=p_timeSpan) l_sdate = l_sdat.strftime('%Y-%m-%d') l_edate = l_edat.strftime('%Y-%m-%d') #Parsing the data from the database and update the dictionary with respective time span for i in range(p_timeSpan,-1,-1): l_edat_loop = l_sdat - datetime.timedelta(days=i) l_edate_loop = l_edat_loop.strftime('%Y-%m-%d') l_refill_history[l_edate_loop] = [p_containerid,0,0,0] l_consumption_history[l_edate_loop] = [p_containerid,0,0,0] l_twodate_query = "SELECT * FROM "+DB_SCHEMA+"."+DATABASE_TABLE_NAME +" WHERE DATES BETWEEN DATE(\'" + l_edate + "\') AND DATE(\'" + l_sdate + "\') AND SCALE_ID =" + p_containerid try: l_db_statement = ibm_db.exec_immediate(l_connection, l_twodate_query) l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("appHistoricalGraph_twodatequery exec/fetch_ERROR : " + str(e)) while l_temp_dict: if(l_temp_dict["SCALE_ID"] == p_containerid): l_date = l_temp_dict["DATES"].strftime('%Y-%m-%d') if(l_temp_dict["STATUS"] == 0): l_refill_history[l_date] = [l_temp_dict["SCALE_ID"],l_temp_dict["TIME"],"%.2f"%l_temp_dict["QUANTITY"],l_temp_dict["STATUS"]] else: l_consumption_history[l_date] = [l_temp_dict["SCALE_ID"],l_temp_dict["TIME"],"%.2f"%l_temp_dict["QUANTITY"],l_temp_dict["STATUS"]] try: l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("appHistoricalGraph_twodatequery fetch_ERROR : " + str(e)) pubnub.publish(channel="kitchenApp-refillHistory", message=l_refill_history) pubnub.publish(channel="kitchenApp-consumptionHistory", message=l_consumption_history) #deleting the history del l_refill_history,l_consumption_history #Closing the Database Connection ibm_db.free_stmt(l_db_statement) ibm_db.close(l_connection)
def run_test_220(self): conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0") print("Number of affected rows:", ibm_db.num_rows( stmt )) ibm_db.rollback(conn) ibm_db.close(conn) else: print("Connection failed.")
def run_test_011(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals WHERE weight > 10.0") print ("Number of affected rows: %d" % ibm_db.num_rows( stmt )) ibm_db.rollback(conn) ibm_db.close(conn) else: print ("Connection failed.")
def run_test_210(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from staff") cols = ibm_db.num_fields(result) for i in range(0, cols): size = ibm_db.field_display_size(result,i) print "col:%d and size: %d" % (i, size) ibm_db.close(conn)
def run_test_035(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from staff") i=0 row = ibm_db.fetch_row(result) while ( row ): print "%d, " % i i+=1 row = ibm_db.fetch_row(result) ibm_db.close(conn)
def run_test_003(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) sql = 'UPDATE animals SET id = 9' res = ibm_db.exec_immediate(conn, sql) print ("Number of affected rows: %d" % ibm_db.num_rows(res)) ibm_db.rollback(conn) ibm_db.close(conn) else: print ("Connection failed.")
def run_test_warn(self): conn = ibm_db.connect(config.database, config.user, config.password) # Get the server type serverinfo = ibm_db.server_info( conn ) if conn: drop = "DROP TABLE WITH_CLOB" try: result = ibm_db.exec_immediate(conn,drop) except: pass # Create the table with_clob if (serverinfo.DBMS_NAME[0:3] != 'IDS'): create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))" else: create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))" result = ibm_db.exec_immediate(conn, create) # Select the result from the table. This is just to verify we get appropriate warning using # ibm_db.stmt_warn() API query = 'SELECT * FROM WITH_CLOB' if (serverinfo.DBMS_NAME[0:3] != 'IDS'): stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = ibm_db.prepare(conn, query) ibm_db.execute(stmt) data = ibm_db.fetch_both( stmt ) if data: print("Success") else: print("No Data") print(ibm_db.stmt_warn(stmt)) ibm_db.close(conn) else: print ("Connection failed.") #__END__ #__LUW_EXPECTED__ #No Data[IBM][CLI Driver][DB2/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100 #__ZOS_EXPECTED__ #No Data[IBM][CLI Driver][DB2] # SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100 #__SYSTEMI_EXPECTED__ #No Data #__IDS_EXPECTED__ #No Data #[IBM][CLI Driver][IDS/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
def run_test_264(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: server = ibm_db.server_info(conn) if server.DBMS_NAME[0:3] == "IDS": op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER} ibm_db.set_option(conn, op, 1) # Drop the tab_bigint table, in case it exists drop = "DROP TABLE tab_bigint" result = "" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if server.DBMS_NAME[0:3] == "IDS": create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)" else: create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = ibm_db.exec_immediate(conn, insert) print "Number of inserted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while data: print data[0] print data[1] print data[2] print data[3] print type(data[0]) is long print type(data[1]) is long print type(data[2]) is long data = ibm_db.fetch_both(stmt) # test ibm_db.result for fetch of bigint stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint") ibm_db.execute(stmt1) ibm_db.fetch_row(stmt1, 0) if server.DBMS_NAME[0:3] != "IDS": row1 = ibm_db.result(stmt1, "COL2") else: row1 = ibm_db.result(stmt1, "col2") print row1 ibm_db.close(conn)
def getrange(): try: if request.method == "POST": uppermag = request.form['uppermag'] lowermag = request.form['lowermag'] startdate = request.form['startdate'] enddate = request.form['enddate'] #connect to db conn = ibm_db.connect( "DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "") if conn: print("in if loop") sql = 'select * from RZG77856.ALL_MONTH where mag between ? and ? and date >=? and date <=?' prep = ibm_db.prepare(conn, sql) ibm_db.bind_param(prep, 1, lowermag) ibm_db.bind_param(prep, 2, uppermag) ibm_db.bind_param(prep, 3, startdate) ibm_db.bind_param(prep, 4, enddate) ibm_db.execute(prep) rows = [] count = 0 print("conn 2") # fetching the result result = ibm_db.fetch_assoc(prep) print(result) while result != False: count = count + 1 rows.append(result.copy()) result = ibm_db.fetch_assoc(prep) # close database connection ibm_db.close(conn) print(len(rows)) return render_template('rangeresult.html', rows=rows, count=count) else: print("no connection established") return render_template('main.html') except Exception as e: print(e) return "<html><body><p>In Exception</p></body></html>"
def getJobReq(id): try: ibm_db_conn = ibm_db.connect(dsn, '', '') conn = ibm_db_dbi.Connection(ibm_db_conn) cursor = conn.cursor() print("Connected to {0}".format(DB2_DB)) except: print("Couldn't Connect to Database") return False try: q1 = "SELECT GITID, GIT_MUL, BIG5_MUL, VALUES_MUL, SELF_DESC, SELF_DESC_MUL, JOB_WANT_WHY, JOB_WANT_WHY_MUL, JOB_REQ_WHAT, JOB_REQ_WHAT_MUL, PASSION, PASSION_MUL, JOBSKILLS, JOBSKILLS_MUL, JOBYOE, JOBYOE_MUL, APT_MUL, DATE_JOIN, DATE_JOIN_MUL" q1 = q1 + " FROM JOBS_RAW WHERE JOBID = " + str(id) + ";" cursor.execute(q1) jobReq = cursor.fetchall()[0] except: print('Error Querying JOB Requirement') ibm_db.close(ibm_db_conn) print("connection closed") return False print('fetched job requirement') ibm_db.close(ibm_db_conn) print("connection closed") return { 'gitId': jobReq[0], 'gitId_mul': jobReq[1], 'big5_mul': jobReq[2], 'values_mul': jobReq[3], 'self_desc': jobReq[4], 'self_desc_mul': jobReq[5], 'job_want_why': jobReq[6], 'job_want_why_mul': jobReq[7], 'job_req_what': jobReq[8], 'job_req_what_mul': jobReq[9], 'passion': jobReq[10], 'passion_mul': jobReq[11], 'jobskills': jobReq[12], 'jobskills_mul': jobReq[13], 'yoe': jobReq[14], 'yoe_mul': jobReq[15], 'apt_mul': jobReq[16], 'date_join': jobReq[17], 'date_join_mul': jobReq[18] }
def Welcome(): db2conn = ibm_db.connect( "DATABASE=" + ibmdb2cred['db'] + ";HOSTNAME=" + ibmdb2cred['hostname'] + ";PORT=" + str(ibmdb2cred['port']) + ";UID=" + ibmdb2cred['username'] + ";PWD=" + ibmdb2cred['password'] + ";", "", "") if db2conn: sql = "select * from people;" stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) rows = [] # fetch the result result = ibm_db.fetch_assoc(stmt) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) # close database connection print(rows) page = "" stmt = ibm_db.exec_immediate( db2conn, "select host_name,os_name,os_version,total_cpus,configured_cpus, total_memory," "os_kernel_version,os_arch_type, os_release,os_full_version from sysibmadm.env_sys_info" ) # fetch the result result = ibm_db.fetch_assoc(stmt) page += "OS Name: " + result["OS_NAME"] + "<br/>OS Version: " + result[ "OS_VERSION"] page += "<br/>Hostname: " + result[ "HOST_NAME"] + "<br/> Total CPUs: " + str(result["TOTAL_CPUS"]) page += "<br/>Configured CPUs: " + str( result["CONFIGURED_CPUS"]) + "<br/>Total memory: " + str( result["TOTAL_MEMORY"]) + " MB" page += "<br/>OS Kernel Version: " + result[ "OS_KERNEL_VERSION"] + "<br/>OS Architecture Tpye: " + result[ "OS_ARCH_TYPE"] page += "<br/>OS Release: " + result[ "OS_RELEASE"] + "<br/>OS full version: " + result["OS_FULL_VERSION"] ibm_db.close(db2conn) return render_template('index.html', data=rows, system_info=page, script_root=request.script_root)
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_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, 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)
def run_test_021(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) ibm_db.autocommit(conn, 0) ac = ibm_db.autocommit(conn) if ac != 0: print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test") #continue ibm_db.exec_immediate(conn, "DELETE FROM animals") stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) ibm_db.commit(conn) stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals") res = ibm_db.fetch_tuple(stmt) rows = res[0] print(rows) # Populate the animal table animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3), (2, 'horse', 'Smarty', 350.0), (3, 'gold fish', 'Bubbles', 0.1), (4, 'budgerigar', 'Gizmo', 0.2), (5, 'goat', 'Rickety Ride', 9.7), (6, 'llama', 'Sweater', 150)) insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)' stmt = ibm_db.prepare(conn, insert) if stmt: for animal in animals: result = ibm_db.execute(stmt, animal) ibm_db.commit(conn) ibm_db.close(conn) else: print("Connection failed.")
def verificar_status(self): erro = '' self.decriptar() if self.tipo == 2: try: conection = psycopg2.connect( f'host={self.ip} user={self.usuario} dbname={self.nomebanco} port={self.porta} password={self.senhasemcripto}' ) conection.close() erro = '' except Exception as e: print(e) erro = str(e) else: try: conection = ibm_db.connect( f'DATABASE={self.nomebanco};HOSTNAME={self.ip};PORT={self.porta};PROTOCOL={self.protocolo};UID={self.usuario};PWD={self.senhasemcripto};', '', '') ibm_db.close(conection) erro = '' except Exception as e: print(ibm_db.conn_errormsg()) erro = str(e) if erro == '': status = 1 else: status = 2 monitoramento = db.session.query(Monitoramento).filter( Monitoramento.idbanco == self.idbanco).order_by( Monitoramento.idmonitoramento.desc()).first() if monitoramento and monitoramento.idstatus == status: monitoramento.dtmonitoramento = datetime.now() if status == 2: monitoramento.observacao = erro monitoramento.atualizar() elif monitoramento and monitoramento.idstatus != status: monitoramento.dhfinal = datetime.now() monitoramento.atualizar() monitoramento_novo = Monitoramento(self.idbanco, datetime.now(), status, datetime.now(), erro) monitoramento_novo.atualizar() else: monitoramento_novo = Monitoramento(self.idbanco, datetime.now(), status, datetime.now(), erro) monitoramento_novo.atualizar()
def largest(): # connect to DB2 db2conn = ibm_db.connect( "DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "") if db2conn: # we have a Db2 connection, query the database sql = 'Select "mag","time","place" from EARTHQ where "mag" is not NULL order by "mag" DESC limit 5' stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) rows = [] result = ibm_db.fetch_assoc(stmt) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) ibm_db.close(db2conn) return render_template('largest.html', r=rows)
def getQuestions(): db2conn = createConnection() if db2conn: # if we have a Db2 connection, query the database sql = "SELECT question_content FROM question;" # Prepare the statement stmt = ibm_db.prepare(db2conn, sql) # Execute the sql ibm_db.execute(stmt) rows = [] # fetch the result result = ibm_db.fetch_assoc(stmt) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) # close database connection ibm_db.close(db2conn) return rows
def index(): con = ibm_db.connect( "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=lbx88746;PWD=s05d49+z1hv98prc;", "", "") query = "SELECT * FROM people" stmt = ibm_db.prepare(con, query) ibm_db.execute(stmt) row = [] result = ibm_db.fetch_assoc(stmt) while result != False: row.append(result.copy()) result = ibm_db.fetch_assoc(stmt) ibm_db.close(con) return render_template('assign1.html', value=row)
def run_test_221(self): pconn = list(range(100)) for i in range(100): pconn[i] = ibm_db.pconnect(config.database, config.user, config.password) if pconn[33]: conn = pconn[22] ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) stmt = ibm_db.exec_immediate( pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0") print("Number of affected rows:", ibm_db.num_rows(stmt)) ibm_db.rollback(conn) ibm_db.close(pconn[33]) else: print("Connection failed.")
def run_test_031(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ibm_db.fetch_row(stmt)): id = ibm_db.result(stmt, 0) print "int(%d)" % id breed = ibm_db.result(stmt, 1) print "string(%d) \"%s\"" % (len(breed), breed) name = ibm_db.result(stmt, 2) print "string(%d) \"%s\"" % (len(name), name) weight = ibm_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) ibm_db.close(conn) else: print "Connection failed."
def register(self, testClient, name, email, password, confirm, tutorname): """ Deletes the test users and then re registers them :return: the response of the insert """ db2conn = createConnection() if db2conn: sql = ("DELETE from student where email = '" + email + "'") # Prepare the statement stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) sql = ("DELETE from tutor where email = '" + email + "'") stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) ibm_db.close(db2conn) return self.registerIndividual(testClient, name, email, password, confirm, tutorname)
def run_test_260(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate( conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") row = ibm_db.fetch_tuple(stmt) while (row): for i in row: print(i) row = ibm_db.fetch_tuple(stmt) ibm_db.close(conn) else: print("Connection failed.")
def query_search(q): try: db2conn = ibm_db.connect(conn_str, "", "") if db2conn: statement = ibm_db.prepare(db2conn, q) ibm_db.execute(statement) rows = [] result = ibm_db.fetch_assoc(statement) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(statement) ibm_db.close(db2conn) return rows else: return False except: print("Connection to Database failed") exit(1)
def updateGrades(): student = request.args.get('student') grades = request.args.get('grades') sql = "update people set grade=%s where name=%s" % ('\'' + grades + '\'', '\'' + student + '\'') db2conn = ibm_db.connect( "DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "") stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) sql = "select name, grade from people where name=%s" % ('\'' + student + '\'') stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) result = ibm_db.fetch_assoc(stmt) ibm_db.close(db2conn) return render_template('city.html', results=result)
def run_test_310(self): conn = ibm_db.connect(config.database, config.user, config.password) client = ibm_db.client_info(conn) if client: print "DRIVER_NAME: string(%d) \"%s\"" % (len(client.DRIVER_NAME), client.DRIVER_NAME) print "DRIVER_VER: string(%d) \"%s\"" % (len(client.DRIVER_VER), client.DRIVER_VER) print "DATA_SOURCE_NAME: string(%d) \"%s\"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME) print "DRIVER_ODBC_VER: string(%d) \"%s\"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER) print "ODBC_VER: string(%d) \"%s\"" % (len(client.ODBC_VER), client.ODBC_VER) print "ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE) print "APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE print "CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE ibm_db.close(conn) else: print "Error."
def db_operation(sql): db2conn = ibm_db.connect( "DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "") if db2conn: stmt = ibm_db.prepare(db2conn, sql) ibm_db.execute(stmt) rows = [] # fetch the result header = ibm_db.fetch_assoc(stmt) result = ibm_db.fetch_assoc(stmt) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) # close database connection ibm_db.close(db2conn) return header, rows
def fd_main(sys_code, tab_code, etl_date, date_offset, alg, sample_size, start_date_str): etl_dates = date_trans(etl_date, date_offset) conf = Config() output_conn = None if conf.output_db == "db2": output_conn = get_db2_connect(conf.output_db_url) else: logging.error("输出配置数据库未适配 :{}".format(conf.output_db)) exit(-1) # 检查输出,已分析的表跳过分析步骤 # 函数依赖分析 fd_sche = get_analysis_schedule_single(output_conn, conf.output_schema, sys_code, tab_code)['FD_SCHE'] ibm_db.close(output_conn) if fd_sche == "1": logging.warning("该表已完成函数依赖分析:{}".format(tab_code)) exit(-1) else: analyse_table_fds(conf, sys_code, tab_code, alg, etl_dates, start_date_str, sample_size, status=fd_sche)
def updatekeyword(nameToUpdateKeyword=None, keyword=None): # connect to DB2 db2conn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","") if db2conn: # we have a Db2 connection, query the database sql='Update PEOPLE set "Keywords"=? where "Name"=?' # Note that for security reasons we are preparing the statement first, # then bind the form input as value to the statement to replace the # parameter marker. stmt = ibm_db.prepare(db2conn,sql) ibm_db.bind_param(stmt, 1, keyword) ibm_db.bind_param(stmt, 2, nameToUpdateKeyword) ibm_db.execute(stmt) rows=[] # close database connection ibm_db.close(db2conn) return render_template('updateppl.html')
def largest_n(number=5): number = request.args.get('number', '') # connect to DB2 db2conn = ibm_db.connect(db2cred['ssldsn'], "","") if db2conn: sql = "SELECT * FROM EARTHQUAKE ORDER BY MAG DESC FETCH FIRST ? ROWS ONLY;" stmt = ibm_db.prepare(db2conn, sql) ibm_db.bind_param(stmt, 1, number) ibm_db.execute(stmt) rows=[] result = ibm_db.fetch_assoc(stmt) while result != False: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) ibm_db.close(db2conn) return render_template('large_n.html', ci=rows)
def updateVerififcationStatus(userType, email, stringBool): """ :type stringBool: String ("TRUE") or String("FALSE") """ db2conn = createConnection() if db2conn: sql = ("UPDATE " + str(userType) + " SET verified = " + str(stringBool) + " WHERE email = '" + str(email).lower() + "';") #print(sql) stmt = ibm_db.prepare(db2conn, sql) # Execute the sql ibm_db.execute(stmt) # close database connection ibm_db.close(db2conn)
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") and (server.DBMS_NAME[0:3] != "IDS") and (server.DBMS_NAME[0:3] != "DSN")): 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 location_in_range(): magX = request.args.get('magX', 5, type=float) magY = request.args.get('magY', 6, type=float) db2conn = ibm_db.connect(db2cred['ssldsn'], "", "") if db2conn: sql = "SELECT * FROM EARTHQUAKE WHERE MAGTYPE=\'ml\'" stmt = ibm_db.exec_immediate(db2conn, sql) rows = [] result = ibm_db.fetch_assoc(stmt) while result != False: mag = float(result['MAG']) if mag >= magX and mag <= magY: rows.append(result.copy()) result = ibm_db.fetch_assoc(stmt) ibm_db.close(db2conn) return render_template('location_in_range.html', rows=rows)
def generalSetting(p_vehicleNumber): if (p_vehicleNumber != None): if (vehicleDetails.has_key(p_vehicleNumber)): if (vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS] == 0): pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\ "vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]}) l_connection = dB_init() if (l_connection == None): print("Database Connection Failed on Database Query") return l_database_query = "SELECT * FROM " + DB_SCHEMA + "." + DATABASE_TABLE_NAME_1 + " WHERE VEHICLE_NUMBER = '" + str( p_vehicleNumber) + "'" try: l_db_statement = ibm_db.exec_immediate( l_connection, l_database_query) l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("rfid Register exec/fetch_ERROR : " + str(e)) while l_temp_dict: if (l_temp_dict["VEHICLE_NUMBER"] == p_vehicleNumber): vehicleDetails[p_vehicleNumber][ DETAILS_BALANCE] = l_temp_dict["WALLET_BAL"] try: l_temp_dict = ibm_db.fetch_assoc(l_db_statement) except Exception as e: logging.error("rfid Register fetch_ERROR : " + str(e)) ibm_db.free_stmt(l_db_statement) ibm_db.close(l_connection) pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\ "vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]}) else: appSetting( p_vehicleNumber, vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS]) else: pubnub.publish( channel=p_vehicleNumber, message={ "warning": "Vehicle Not Registered with the Automated System" }) else: pass
def login(): try: if (session["login"]): pass except: session["login"] = False session["user_id"] = -1 session["username"] = "" session["cat"] = "" if (request.method == "POST"): try: name = int(request.form["id"]) except: flash("User ID is numeric !", category="error") return redirect(url_for('login')) password = request.form["password"] conn = ibm_db.connect(dsn, "", "") sql = "select * from USERS where user_id = " + str( name) + " and password = '******';" res = ibm_db.exec_immediate(conn, sql) k = ibm_db.fetch_assoc(res) ibm_db.close(conn) if (k == False): flash("Invalid Credentials !!!", category="error") return render_template("login.html") else: session["user_id"] = k["USER_ID"] session["username"] = k["USERNAME"] session["cat"] = k["CATEGORY"] session["login"] = True cat = k["CATEGORY"] if (cat == "p"): session["cat"] = "p" return redirect(url_for('p_home')) elif (cat == "d"): session["cat"] = "d" return redirect(url_for('d_home')) elif (cat == "h"): session["cat"] = "h" return redirect(url_for('h_home')) return render_template("login.html")
def run_test_warn(self): conn = ibm_db.connect(config.database, config.user, config.password) # Get the server type serverinfo = ibm_db.server_info(conn) if conn: drop = "DROP TABLE WITH_CLOB" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the table with_clob if (serverinfo.DBMS_NAME[0:3] != 'IDS'): create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))" else: create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))" result = ibm_db.exec_immediate(conn, create) # Select the result from the table. This is just to verify we get appropriate warning using # ibm_db.stmt_warn() API query = 'SELECT * FROM WITH_CLOB' if (serverinfo.DBMS_NAME[0:3] != 'IDS'): stmt = ibm_db.prepare(conn, query, { ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN }) else: stmt = ibm_db.prepare(conn, query) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) if data: print("Success") else: print("No Data") print(ibm_db.stmt_warn(stmt)) ibm_db.close(conn) else: print("Connection failed.")
def run_test_300(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info(conn) if server: print "DBMS_NAME: string(%d) \"%s\"" % (len( server.DBMS_NAME), server.DBMS_NAME) print "DBMS_VER: string(%d) \"%s\"" % (len( server.DBMS_VER), server.DBMS_VER) print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE print "DB_NAME: string(%d) \"%s\"" % (len( server.DB_NAME), server.DB_NAME) print "INST_NAME: string(%d) \"%s\"" % (len( server.INST_NAME), server.INST_NAME) print "SPECIAL_CHARS: string(%d) \"%s\"" % (len( server.SPECIAL_CHARS), server.SPECIAL_CHARS) print "KEYWORDS: int(%d)" % len(server.KEYWORDS) print "DFT_ISOLATION: string(%d) \"%s\"" % (len( server.DFT_ISOLATION), server.DFT_ISOLATION) il = '' for opt in server.ISOLATION_OPTION: il += opt + " " print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il) print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len( server.SQL_CONFORMANCE), server.SQL_CONFORMANCE) print "PROCEDURES:", server.PROCEDURES print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len( server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR) print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS ibm_db.close(conn) else: print "Error."
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 } if sys.platform == 'zos': conn = ibm_db.pconnect(config.database, '', '') else: conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: try: 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) finally: ibm_db.close(conn) else: print("Connection failed.")
def run_test_030(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ibm_db.fetch_row(stmt)): breed = ibm_db.result(stmt, 1) print "string(%d) \"%s\"" % (len(breed), breed) if (server.DBMS_NAME[0:3] == 'IDS'): name = ibm_db.result(stmt, "name") else: name = ibm_db.result(stmt, "NAME") print "string(%d) \"%s\"" % (len(name), name) ibm_db.close(conn) else: print "Connection failed."