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 main(args): #Connect to the DB2 database conn = ibm_db.connect("DATABASE=TEST;HOSTNAME=172.17.0.1;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=db2inst1-pwd;", "", "") cmd = "DELETE FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) #output of the above can succeed or fail. Either is fine. # Insert into TestTable cmd = "INSERT INTO TESTTABLE (NAME, AGE, LOCATION) VALUES ('Angela', 27, 'Texas')" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} # Select from TestTable (the row just inserted) cmd = "SELECT * FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} else: ibm_db.fetch_both(result,0) value = ibm_db.result(result,"NAME") # Make sure the row was correctly inserted if value != 'Angela' : return {"err":"Expected name 'Angela', but instead found: "+ value} # Delete the row from TestTable cmd = "DELETE FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} # If no detected errors occurred so far; return Success status return {"message":"Tested db2 create, select, and delete of a table row."}
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 load_group_page(self): ContentNavigationDrawer.populateNavDrawerValues(self) self.ids['allorgscroll'].clear_widgets() #To clear list of orgs query = f'''SELECT ORG_ID,NAME,REGISTRATION FROM ORG WHERE ORG_ID IN ( SELECT ORG_ID FROM ORG MINUS SELECT ORG_ID FROM CONTACT_ORG WHERE CONTACT_ID = {globalvariables.var_userid} AND STATUS='Y')''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) orglist = ibm_db.fetch_both(stmt) item = TwoLineAvatarListItem() while (orglist): file_found = download_org_image(str(orglist[0])) if file_found == True: image = ImageLeftWidget(source="resources/org/" + str(orglist[0]) + ".png") else: image = ImageLeftWidget(source="resources/org/default.jpg") item = TwoLineAvatarListItem(text=str(orglist[1]), secondary_text=str(orglist[2])) item.add_widget(image) item.bind(on_release=self.row_press) self.ids['allorgscroll'].add_widget(item) orglist = ibm_db.fetch_both( stmt) #for incrementing rows inside while loop
def run_test_162(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 emp_act order by projno") row = ibm_db.fetch_both(result) # will only retrieve 10 records count = 1 while (row): print( "Record ", count, ": %6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])) result2 = ibm_db.exec_immediate( conn, "select * from employee where employee.empno='" + row['EMPNO'] + "'") row2 = ibm_db.fetch_both(result2) if row2: print(">>%s,%s,%s,%s,%s,%s,%s" % (row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'], row2[3], row2[3], row2[5], row2[6])) count = count + 1 if (count > 10): break row = ibm_db.fetch_both(result)
def getDatabaseContentsJson(): select_statement = 'SELECT * FROM RVB49192.ORDERS ORDER BY ID desc;' try: res = ibm_db.exec_immediate(conn, select_statement) result = ibm_db.fetch_both(res) resultDict = [] while (result): returnDictBuffer = { 'ID': result['ID'], 'NAME': result['NAME'], 'PHONE': result['PHONE'], 'ORDERS': result['ORDERS'], 'ADDRESS': result['ADDRESS'] } resultDict.append(returnDictBuffer) result = ibm_db.fetch_both(res) return jsonify(resultDict) except: resultDict = [] returnDictBuffer = { 'ID': 0, 'NAME': 'db2 not connected!', 'PHONE': '', 'ORDERS': '', 'ADDRESS': '' } resultDict.append(returnDictBuffer) return jsonify(resultDict)
def load_page(self, greg): ContentNavigationDrawer.populateNavDrawerValues(self) query = "SELECT ORG_ID,NAME,REGISTRATION,DESC FROM ORG WHERE REGISTRATION='" + greg + "'" # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) userrow = ibm_db.fetch_tuple(stmt) org_id = "" org_name = "" org_reg = "" org_desc = "" if userrow != False: org_id = userrow[0] org_name = userrow[1] org_reg = userrow[2] org_desc = userrow[3] globalvariables.var_org_id = org_id self.ids['gname'].text = org_name self.ids['greg'].text = org_reg self.ids['gdesc'].text = org_desc #USED TO POPULATE DROP DOWN OF ACTIVITY FOR PAYMENTS FOR NON JOINERS query = "SELECT ACTIVITY_ID,NAME FROM ACTIVITY WHERE ORG_ID=" + str( globalvariables.var_org_id) stmt = ibm_db.exec_immediate(connection.conn, query) act = ibm_db.fetch_both(stmt) actlist = [] while (act): actlist.append(str(act[1])) act = ibm_db.fetch_both(stmt) self.menu = MDDropdownMenu(caller=self.ids['activity_item'], position="center", width_mult=5, callback=self.set_item) for i in actlist: self.menu.items.append({"text": str(i)})
def within_distance(): value = [] lat = request.form.get("lat") long = request.form.get("long") dist = request.form.get("dist") query = f"SELECT * FROM(\ SELECT *,\ (\ (\ (\ acos(\ sin(( {lat} * 0.01745329251 ))\ *\ sin(( latitude * 0.01745329251 )) + cos(( {lat} *0.01745329251 ))\ *\ cos(( latitude * 0.01745329251)) * cos((( {long} - longitude) * 0.01745329251)))\ ) * 57.2957795131\ ) * 60 * 1.1515 * 1.609344\ )\ as distance FROM earthquake)\ where distance <= {dist} order by distance asc" stmt1 = ibm_db.exec_immediate(conn, query) result = ibm_db.fetch_both(stmt1) while result: value.append(result) result = ibm_db.fetch_both(stmt1) return render_template('within-distance.html', search=value, lat=lat, long=long, dist=dist)
def magnitude(): value = [] lat = request.form.get("lat") long = request.form.get("long") query = f"SELECT *,\ (\ (\ (\ acos(\ sin(( {lat} * 0.01745329251 ))\ *\ sin(( latitude * 0.01745329251 )) + cos(( {lat} *0.01745329251 ))\ *\ cos(( latitude * 0.01745329251)) * cos((( {long} - longitude) * 0.01745329251)))\ ) * 57.2957795131\ ) * 60 * 1.1515\ )\ as distance FROM earthquake where mag>6 order by distance asc limit 1" stmt1 = ibm_db.exec_immediate(conn, query) result = ibm_db.fetch_both(stmt1) while result: value.append(result) result = ibm_db.fetch_both(stmt1) return render_template('magnitude.html', search=value, lat=lat, long=long)
def latlong(): value = [] lat1 = float(request.form.get("lat1")) long1 = float(request.form.get("long1")) lat2 = float(request.form.get("lat2")) long2 = float(request.form.get("long2")) #Set min. latitude if lat1 < lat2: min_lat = lat1 max_lat = lat2 else: min_lat = lat2 max_lat = lat1 #Set min. longitude if long1 < long2: min_long = long1 max_long = long2 else: min_long = long2 max_long = long1 query = f"SELECT * FROM LATLONG WHERE LATITUDE BETWEEN {min_lat} and {max_lat} AND LONGITUDE BETWEEN {min_long} and {max_long}" stmt1 = ibm_db.exec_immediate(conn, query) result = ibm_db.fetch_both(stmt1) while result: value.append(result) result = ibm_db.fetch_both(stmt1) return render_template('latlong-results.html', search=value)
def main(args): ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] conn = ibm_db.connect(ssldsn, "", "") if conn: print("Connection succeeded.") else: print("Connection failed.") return {"error": "Error connecting to db2"} # Select from TestTable (the row just inserted). # # Due to the db2 lite plan we use, the schema name is fixed to be # the same as the username. We cut the value of the UID field # in the ssldsn to get it and insert it in the select statement. # The ssldsn looks like this: # ssldsn="DATABASE=BLUDB;HOSTNAME=dashdb-xxxx.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=yyyyyyy;PWD=<hidden>;" # ssldsndict = dict(x.split("=") for x in ssldsn.rstrip(";").split(";")) print("user={}".format(ssldsndict["UID"])) cmd = "SELECT HISP_DESC FROM {}.HISPANIC_ORIGIN WHERE HISP_CODE='03'".format( ssldsndict["UID"]) result = ibm_db.exec_immediate(conn, cmd) if not result: return {"error": "error :" + cmd} else: ibm_db.fetch_both(result, 0) value = ibm_db.result(result, "HISP_DESC") ibm_db.close(conn) return {"HISP_DESC": value}
def run_test_014(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) query = 'SELECT * FROM animals ORDER BY name' 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) while (data): print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3])) data = ibm_db.fetch_both(stmt) try: stmt = ibm_db.prepare( conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) ibm_db.execute(stmt) rc = ibm_db.fetch_row(stmt, -1) print("Fetch row -1: %s" % str(rc)) except: print("Requested row number must be a positive value") ibm_db.close(conn)
def tasktwo(): lati1 = str(request.form['number1']) longi1 = str(request.form['number2']) lati2 = str(request.form['number3']) longi2 = str(request.form['number4']) value = str(request.form['number5']) nir_dsn_hostname = "dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net" nir_dsn_uid = "mfm86030" nir_dsn_pwd = "s17-fnd9c6p060xm" nir_dsn_driver = "{IBM DB2 ODBC DRIVER}" nir_dsn_database = "BLUDB" nir_dsn_protocol = "TCPIP" nir_dsn_port = "50000" dsn_nir = ("DRIVER={0};" "DATABASE={1};" "HOSTNAME={2};" "PORT={3};" "PROTOCOL={4};" "UID={5};" "PWD={6};").format(nir_dsn_driver, nir_dsn_database, nir_dsn_hostname, nir_dsn_port, nir_dsn_protocol, nir_dsn_uid, nir_dsn_pwd) try: conn = ibm_db.connect(dsn_nir, "", "") except: print("Unable to connect: ", ibm_db.conn_errormsg()) server = ibm_db.server_info(conn) sql = "SELECT COUNT(*) AS ct FROM EARTHQUAKE_TABLEQ WHERE (LATITUDE>=" + lati1 + " AND LATITUDE<=" + lati2 + ") AND (LONGITUDE>=" + longi1 + " AND LONGITUDE<= " + longi2 + ")" stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) demo2 = [] while dictionary != False: demo2.append(dictionary[0]) dictionary = ibm_db.fetch_both(stmt) return render_template('tasktwo.html', demo2=demo2, value=value)
def load_org_list(self): self.ids['scroll'].clear_widgets() #To clear list of orgs query = f'''SELECT ORG_ID,NAME,REGISTRATION FROM ORG WHERE ORG_ID IN (SELECT ORG_ID FROM CONTACT_ORG WHERE CONTACT_ID={globalvariables.var_userid} AND MEMBER_FLAG='Y' AND STATUS='Y')''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) orglist = ibm_db.fetch_both(stmt) item = TwoLineAvatarListItem() if orglist is False: self.ids['nogroupwarning'].pos_hint = {"x": 0.2, "y": 0.3} self.ids['nogroupwarning'].font_size = '20sp' self.ids[ 'nogroupwarning'].text = """You have not joined any groups. Your joined groups will show up here""" else: while (orglist): file_found = download_org_image(str(orglist[0])) if file_found == True: image = ImageLeftWidget(source="resources/org/" + str(orglist[0]) + ".png") else: image = ImageLeftWidget(source="resources/org/default.jpg") item = TwoLineAvatarListItem(text=str(orglist[1]), secondary_text=str(orglist[2])) item.add_widget(image) item.bind(on_release=self.row_press) self.ids['scroll'].add_widget(item) orglist = ibm_db.fetch_both( stmt) #for incrementing rows inside while loop
def getDatabaseContentsJson(): try: conn = ibm_db.connect(dsn, "", "") except: pass select_statement = 'SELECT * FROM {0}.ORDERS ORDER BY ID desc;'.format( dsn_uid) res = ibm_db.exec_immediate(conn, select_statement) result = ibm_db.fetch_both(res) resultDict = [] while (result): returnDictBuffer = { 'ID': result['ID'], 'COGNOME': result['COGNOME'], 'MODELLO': result['MODELLO'], 'VIN': result['VIN'], 'KMTOTALI': result['KMTOTALI'], 'TIPODISTRADA': result['TIPODISTRADA'], 'RIFORNIMENTO': result['RIFORNIMENTO'], 'LUOGO': result['LUOGO'], 'LITRI': result['LITRI'], 'SEGNALAZIONE': result['SEGNALAZIONE'] } resultDict.append(returnDictBuffer) result = ibm_db.fetch_both(res) return jsonify(resultDict)
def run_test_007(self): options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] == 'IDS'): options1 = options2 stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print(data[0].strip()) data = ibm_db.fetch_both(stmt) print("") stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1) ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print(data[0].strip()) data = ibm_db.fetch_both(stmt) ibm_db.close(conn) else: print("Connection failed.")
def apply_on_row(query, f): stmt = ibm_db.exec_immediate(conn, query) result = ibm_db.fetch_both(stmt) while (result): f(result) result = ibm_db.fetch_both(stmt)
def jobInfo(request): conn = ibm_db.connect( "DATABASE=metadata;HOSTNAME=172.16.11.170;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;", "", "") if conn: sql = "select JOB_SEQ_ID,JOB_STS,JOB_NM,DATA_PRD,ACT_ST_DT,ACT_ST_TM,ACT_ED_DT,ACT_ED_TM,JOB_LG,RW_RD,RW_IST,RW_UPT from dwmm.job_log" statement = ibm_db.exec_immediate(conn, sql) #print(type(stmt)) #print(stmt) result = ibm_db.fetch_both(statement) print(type(result)) print(type(ibm_db.fetch_both(statement))) while (result): # print(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7], result[8], result[9], result[10], result[11]) #result = ibm_db.fetch_both(stmt) #for i in range(len(result)): #recordDir[stmt[i][2]]=stmt[i][4] #print(recordDir[stmt[i][2]]) result = ibm_db.fetch_both(statement) print(result) ibm_db.close(conn) return render(request, '/jobInfo/', {'data': result})
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_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 print_contents(conn,sql): print "SQL: ",sql stmt = ibm_db.exec_immediate(conn,sql) dictionary = ibm_db.fetch_both(stmt) while dictionary != False: print "ITEM: ",dictionary[0] dictionary = ibm_db.fetch_both(stmt)
def injuryAmong(Longtitude, Latitude, Range=0.05): # key:[value1, value2, ...] conn = ibm_db.connect( "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=tpj29337;PWD=8kzn@v6p7wlb4r75;Security=SSL;", "", "") try: sql_find = "SELECT * from Injury WHERE " \ "(POWER(longtitude - %f,2) + POWER(Latitude - %f,2)) <= POWER(%f, 2) LIMIT 100" \ %(Longtitude, Latitude, Range) res = ibm_db.exec_immediate(conn, sql_find) dict = {} result = ibm_db.fetch_both(res) for key in result.keys(): if type(key) != int: dict[key] = [result[key]] while result != False: for key in result.keys(): if type(key) != int: dict[key].append(result[key]) result = ibm_db.fetch_both(res) ibm_db.close(conn) return dict except: ibm_db.close(conn) return -1
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 db_search(self, param): timestamp = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) level = 'LEV' + '%s' % param['level'] user = g.user.uid if user == 'admin': source = 'Atas' sql = "SELECT LEV10,LEV15,LEV17,LEV20,LEV30 from CMRDC.PRODUCTS_IMAGE WHERE %s = '%s'" % ( level, param['prod_id']) stmt = ibm_db.exec_immediate(self.conn, sql) results = ibm_db.fetch_both(stmt) while results: level10 = results[0] level15 = results[1] level17 = results[2] level20 = results[3] level30 = results[4] results = ibm_db.fetch_both(stmt) try: insert_sql = "INSERT INTO CMRDC.LINEITEM(RLI_ID, MPP_NUMBER, VERSION, COUNTRY, DELETED, LEVEL10, LEVEL15, LEVEL17, LEVEL20, LEVEL30, SOURCE, DATE_ENTERED, DATE_MODIFIED) VALUES('%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%s','%s')" % ( param['rli_id'], param['account_mpp'], ESUtil().rule_index, param['account_country'], param['deleted'], level10, level15, level17, level20, level30, source, timestamp, timestamp) ibm_db.exec_immediate(self.conn, insert_sql) ibm_db.commit(self.conn) except Exception as ex: ibm_db.rollback(self.conn) finally: ibm_db.close(self.conn)
def process(self): tSleep = self._plugin.get("config", "sleep") if not self._canrun: logger.info("We can't start the process,needed modules") return logger.info("Starting Database plugin") rules = self._plugin.rules() run_process = False # logger.info(rules['start_query']['query']) # if rules['start_query']['query'] if not self.tryConnectDB(): self.stop() return cVal = 0 plugin_source_type = self._plugin.get("config", "source_type") while cVal == 0 and not self.stop_processing: cVal = self.runStartQuery(plugin_source_type, rules) if cVal <= 0: logger.info("Waiting for next pooling...no data") time.sleep(10) else: run_process = True ref = int(rules["query"]["ref"]) while run_process and not self.stop_processing: if self._plugin.get("config", "source_type") != "db2": sql = rules["query"]["query"] sql = sql.replace("$1", str(cVal)) logger.debug(sql) self.__myDataBaseCursor.execute(sql) ret = self.__myDataBaseCursor.fetchall() if len(ret) > 0: # We have to think about event order when processing cVal = ret[len(ret) - 1][ref] for e in ret: # pdb.set_trace() self.generate(e) # cursor.close() time.sleep(int(tSleep)) else: sql = rules["query"]["query"] sql = sql.replace("$1", str(cVal)) logger.debug(sql) result = ibm_db.exec_immediate(self.__myDataBaseCursor, sql) dictionary = ibm_db.fetch_both(result) # print dictionary ret = [] while dictionary != False: ret1 = [] for i in dictionary.keys(): ret1.append(dictionary[i]) ret.append(ret1) dictionary = ibm_db.fetch_both(result) cVal = ret[len(ret) - 1][ref] for e in ret: logger.info("-.-->", e) self.generate(e) time.sleep(int(tSleep))
def process(self): tSleep = self._plugin.get("config", "sleep") if not self._canrun: logger.info("We can't start the process,needed modules") return logger.info("Starting Database plugin") rules = self._plugin.rules() run_process = False #logger.info(rules['start_query']['query']) #if rules['start_query']['query'] if not self.tryConnectDB(): self.stop() return cVal = 0 plugin_source_type = self._plugin.get("config", "source_type") while cVal == 0 and not self.stop_processing: cVal = self.runStartQuery(plugin_source_type, rules) if cVal <= 0: logger.info("Waiting for next pooling...no data") time.sleep(10) else: run_process = True ref = int(rules['query']['ref']) while run_process and not self.stop_processing: if self._plugin.get("config", "source_type") != "db2": sql = rules['query']['query'] sql = sql.replace("$1", str(cVal)) logger.debug(sql) self.__myDataBaseCursor.execute(sql) ret = self.__myDataBaseCursor.fetchall() if len(ret) > 0: #We have to think about event order when processing cVal = ret[len(ret) - 1][ref] for e in ret: #pdb.set_trace() self.generate(e) #cursor.close() time.sleep(int(tSleep)) else: sql = rules['query']['query'] sql = sql.replace("$1", str(cVal)) logger.debug(sql) result = ibm_db.exec_immediate(self.__myDataBaseCursor, sql) dictionary = ibm_db.fetch_both(result) #print dictionary ret = [] while dictionary != False: ret1 = [] for i in dictionary.keys(): ret1.append(dictionary[i]) ret.append(ret1) dictionary = ibm_db.fetch_both(result) cVal = ret[len(ret) - 1][ref] for e in ret: logger.info("-.-->", e) self.generate(e) time.sleep(int(tSleep))
def getResByDict(conn, sql): ''' Call fetch_both: ''' print sql stmt = db2.exec_immediate(conn, sql) res = db2.fetch_both(stmt) while(res): print 'Result from :', res[1], res res = db2.fetch_both(stmt)
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_062(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: if (server.DBMS_NAME[0:3] == 'IDS'): schema = 't' else: schema = 'T' result = ibm_db.tables(conn,None,schema); i = 0 row = ibm_db.fetch_both(result) while ( row ): str = row[1] + "/" + row[2] + "/" + row[3] 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: #{ibm_db.conn_errormsg}";
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_062(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: if (server.DBMS_NAME[0:3] == 'IDS'): schema = 't' else: schema = 'T' result = ibm_db.tables(conn,None,schema); i = 0 row = ibm_db.fetch_both(result) while ( row ): str = row[1] + "/" + row[2] + "/" + row[3] 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: #{ibm_db.conn_errormsg}");
def create_org(self, groupname, regnum, desc): ContentNavigationDrawer.populateNavDrawerValues(self) groupname = groupname.text regnum = regnum.text desc = desc.text valid_str = "" if groupname == "": valid_str = "Name is blank" elif regnum == "": valid_str = "Registration is blank" elif desc == "": valid_str = "Description is blank" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: status = "Y" query1 = f'''INSERT INTO ORG(NAME, REGISTRATION, DESC, OWNER_ID, STATUS) VALUES (UPPER('{groupname}'),UPPER('{regnum}'),'{desc}',{globalvariables.var_userid}, '{status}')''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query1) if ibm_db.num_rows(stmt) > 0: query2 = f'''SELECT ORG_ID FROM ORG WHERE REGISTRATION=UPPER('{regnum}') ''' stmt = ibm_db.exec_immediate(connection.conn, query2) orglist = ibm_db.fetch_both(stmt) orgid = "" while (orglist): orgid = orglist[0] query3 = f'''INSERT INTO CONTACT_ORG(ORG_ID, CONTACT_ID, MEMBER_FLAG, STATUS) VALUES ({orgid},{globalvariables.var_userid},'Y', '{status}')''' stmt1 = ibm_db.exec_immediate(connection.conn, query3) orglist = ibm_db.fetch_both(stmt) self.ids[ 'groupname'].text = "" #Setting the values to NULL after sucessfull registration self.ids['regnum'].text = "" self.ids['desc'].text = "" #To upload Org Logo if globalvariables.var_img_path != "": logo_path = globalvariables.var_img_path tgt_logo_path = "org_" + str(orgid) + ".png" upload_org_logo(logo_path, tgt_logo_path) globalvariables.var_img_path = "" ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Successfully Registered', text="Lets start helping!", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'left' self.manager.current = 'home_window' self.manager.get_screen('home_window').load_home_page()
def getdata(): listofdata = [] query_1 = "SELECT * FROM PEOPLE1" stmt_1 = ibm_db.exec_immediate(conn, query_1) result1 = ibm_db.fetch_both(stmt_1) while result1: listofdata.append(result1) result1 = ibm_db.fetch_both(stmt_1) return render_template('main.html', table=listofdata)
def getdata(): listofdata = [] query1 = "SELECT * FROM DATA" stmt = ibm_db.exec_immediate(conn, query1) result = ibm_db.fetch_both(stmt) while result: listofdata.append(result) result = ibm_db.fetch_both(stmt) return render_template('main.html', table=listofdata)
def run_test_060(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: 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): if (i < 4): print("/%s/%s" % (row[1], row[2])) 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: #{ibm_db.conn_errormsg}")
def run_test_060(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: 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: if i < 4: print "/%s/%s" % (row[1], row[2]) 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: #{ibm_db.conn_errormsg}"
def chk_table(self, conn, schema, tabname): tables = [] tables_set = ibm_db.tables(conn, None, schema) result = ibm_db.fetch_both(tables_set) while (result): tables.append(result[2]) result = ibm_db.fetch_both(tables_set) if tabname in tables: return True return False
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: print(dictionary["SOURCE_OWNER"] + "." + dictionary["SOURCE_TABLE"] + " " + dictionary["STATE"]) dictionary = ibm_db.fetch_both(stmt)
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 int) print(type(data[1]) is int) print(type(data[2]) is int) 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 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 run_test_060(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: result = ibm_db.tables(conn, None, 'T') i = 0 row = ibm_db.fetch_both(result) while ( row ): if (i < 4): print "/%s/%s" % (row[1], row[2]) 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: #{ibm_db.conn_errormsg}";
def connectBD2(): # 连接数据库 # conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '') conn = ibm_db.connect(conn_str, '', '') # 获取数据库服务器信息 server = ibm_db.server_info(conn) # 查询数据库sql sql ="select * from emp" if conn: result = ibm_db.exec_immediate(conn, sql) row = ibm_db.fetch_both(result) print ('%s | %s | %s | %s | %s | %s | %s ' % ('EMPNO', 'FIRSTNME', 'MIDINIT', 'LASTNAME', 'HIREDATE', 'JOB', 'SALARY')) while ( row ): print ('%s | %s | %s | %s | %s | %s | %s ' % (row[0], row[1], row[2], row['LASTNAME'], row['HIREDATE'], row['JOB'], row['SALARY'])) row = ibm_db.fetch_both(result)
def runStartQuery(self, plugin_source_type, rules): cVal = "NA" if self.__myDataBaseCursor is None: return cVal try: if plugin_source_type != "db2": sql = rules['start_query']['query'] logger.debug("Running Start query: %s" % sql) self.__myDataBaseCursor.execute(sql) rows = self.__myDataBaseCursor.fetchone() if not rows: logger.warning("Initial query empty, please double-check") return cVal cVal = str((rows[0])) sql = rules['query']['query'] elif plugin_source_type == "db2": sql = rules['start_query']['query'] logger.debug("Start query: %s" % sql) result = ibm_db.exec_immediate(self.__objDBConn, sql) dictionary = ibm_db.fetch_both(result) if not dictionary: logger.warning("Initial query empty, please double-check") return cVal cVal = str((dictionary[0])) logger.info("Connection closed") if cVal==None or cVal =="None" or len(cVal)<=0: cVal="NA" except Exception, e: cVal ="NA" logger.error("Error running the start query: %s" % str(e))
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]) if sys.version_info >= (3, ): print(type(data[0]) is int) print(type(data[1]) is int) print(type(data[2]) is int) else: print(type(data[0]) is long) print(type(data[1]) is long) print(type(data[2]) is long) data = ibm_db.fetch_both(stmt) ibm_db.close(conn)
def executeQuery(self, con, query): try: stmt = ibm_db.exec_immediate(con, query) result = ibm_db.fetch_both(stmt) return result except Exception as message: pass
def run_test_012(self): 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'): stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0") 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_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_161(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 emp_act order by projno desc") row = ibm_db.fetch_both(result) count = 1 while ( row ): print "Record",count,": %6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0]) result2 = ibm_db.exec_immediate(conn,"select * from employee where employee.empno='" + row['EMPNO'] + "'") row2 = ibm_db.fetch_both(result2) if row2: print ">>%s,%s,%s,%s,%s,%s,%s" % (row2['EMPNO'], row2['FIRSTNME'],row2['MIDINIT'], row2[3], row2[3], row2[5], row2[6]) count = count + 1 if (count > 10): break row = ibm_db.fetch_both(result)
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_160(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, 0) result = ibm_db.exec_immediate(conn, "select * from emp_act") row = ibm_db.fetch_both(result) while ( row ): #printf("%6s ",row[0]) #printf("%-6s ",row[1]) #printf("%3d ",row[2]) #printf("%9s ",row['EMPTIME']) #printf("%10s ", row['EMSTDATE']) #printf("%10s ", row['EMENDATE']) #printf("%6s ", row[0]) #puts "" print "%6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0]) row = ibm_db.fetch_both(result)
def run_test_014(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info( conn ) query = 'SELECT * FROM animals ORDER BY name' 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 ) while ( data ): print ("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3])) data = ibm_db.fetch_both( stmt ) try: stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) ibm_db.execute(stmt) rc = ibm_db.fetch_row(stmt, -1) print ("Fetch row -1: %s" % str(rc)) except: print ("Requested row number must be a positive value") ibm_db.close(conn)
def run_test_196(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")): try: rc = ibm_db.exec_immediate(conn, "DROP TABLE xml_test") except: pass rc = ibm_db.exec_immediate(conn, "CREATE TABLE xml_test (id INTEGER, data VARCHAR(50), xmlcol XML)") rc = ibm_db.exec_immediate(conn, "INSERT INTO xml_test (id, data, xmlcol) values (1, 'xml test 1', '<address><street>12485 S Pine St.</street><city>Olathe</city><state>KS</state><zip>66061</zip></address>')") sql = "SELECT * FROM xml_test" stmt = ibm_db.prepare(conn, sql) ibm_db.execute(stmt) result = ibm_db.fetch_both(stmt) while( result ): print("Result ID:", result[0]) print("Result DATA:", result[1]) print("Result XMLCOL:", result[2]) result = ibm_db.fetch_both(stmt) sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") AS CLOB(32k)) FROM xml_test c WHERE id = 1" stmt = ibm_db.prepare(conn, sql) ibm_db.execute(stmt) result = ibm_db.fetch_both(stmt) while( result ): print("Result from XMLSerialize and XMLQuery:", result[0]) result = ibm_db.fetch_both(stmt) sql = "select xmlquery('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") from xml_test c where id = 1" stmt = ibm_db.prepare(conn, sql) ibm_db.execute(stmt) result = ibm_db.fetch_both(stmt) while( result ): print("Result from only XMLQuery:", result[0]) result = ibm_db.fetch_both(stmt) else: print('Native XML datatype is not supported.')
dct = ibm_db.fetch_assoc(stmt_hist) routes[route] = { 'tot_resp': 1, 'cong_resp': -1, 'percentage': 0, 'ratings_sum': post['CONGESTION'], 'est_time': est_time, 'density': density, '0': 0, '1': 0, '2': 0, '3': 0, '4': 0} routes[route][str(int(post['CONGESTION']))] = 1 else: routes[route]['tot_resp']+=1 routes[route]['ratings_sum']+=post['CONGESTION'] routes[route][str(int(post['CONGESTION']))]+=1 post = ibm_db.fetch_both(res) print "GOT ALL POSTS IN LAST HOUR" print routes print print "CALCULATING AND UPDATING LIVE TRAFFIC CONGESTION INFO..." # calculate averages for all routes and update live table (and maybe some other count table) for route in routes: congestion_av = routes[route]['ratings_sum']/float(routes[route]['tot_resp']) print "AVERAGE FOR", (route), ":" print congestion_av cong = int(round(congestion_av)) cong_resp, tot_resp = routes[route][str(cong)], routes[route]['tot_resp'] p = float(cong_resp)/float(tot_resp)*100. percentage = int(round(p))
def run_test_008(self): op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL} conn = ibm_db.connect(config.database, config.user, config.password, op) server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.columns(conn,None,None,"employee") else: result = ibm_db.columns(conn,None,None,"EMPLOYEE") row = ibm_db.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4 op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER} ibm_db.set_option(conn, op, 0) if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.columns(conn,None,None,"employee") else: result = ibm_db.columns(conn,None,None,"EMPLOYEE") row = ibm_db.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4 op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER} ibm_db.set_option(conn, op, 0) if (server.DBMS_NAME[0:3] == 'IDS'): result = ibm_db.columns(conn,None,None,"employee") else: result = ibm_db.columns(conn,None,None,"EMPLOYEE") row = ibm_db.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4
def run_test_decimal(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) drop = "DROP TABLE STOCKSHARE" try: result = ibm_db.exec_immediate(conn,drop) except: pass # Create the table stockprice create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))" result = ibm_db.exec_immediate(conn, create) # Insert Directly insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)" result = ibm_db.exec_immediate(conn, insert) # Prepare and Insert in the stockprice table stockprice = (\ (20, "Zaral", 102.205),\ (30, "Megabyte", "98.65"),\ (40, "Visarsoft", Decimal("123.34")),\ (50, "Mailersoft", Decimal("134.222")),\ (60, "Kaerci", Decimal("100.976"))\ ) insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)' stmt = ibm_db.prepare(conn,insert) if stmt: for company in stockprice: result = ibm_db.execute(stmt,company) id = 70 company = 'Nirvana' stockshare = Decimal("100.1234") try: ibm_db.bind_param(stmt, 1, id) ibm_db.bind_param(stmt, 2, company) ibm_db.bind_param(stmt, 3, stockshare) error = ibm_db.execute(stmt); except: excp = sys.exc_info() # slot 1 contains error message print(excp[1]) # Select the result from the table and query = 'SELECT * FROM STOCKSHARE ORDER BY id' 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 ) while ( data ): print("%s : %s : %s\n" % (data[0], data[1], data[2])) data = ibm_db.fetch_both( stmt ) try: stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) ibm_db.execute(stmt) rc = ibm_db.fetch_row(stmt, -1) print("Fetch Row -1:%s " %str(rc)) except: print("Requested row number must be a positive value") ibm_db.close(conn) else: print("Connection failed.") #__END__ #__LUW_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__ZOS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__IDS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value
dct = ibm_db.fetch_assoc(stmt_hist) routes[route] = { 'tot_resp': 1, 'cong_resp': -1, 'percentage': 0, 'ratings_sum': dictionary['CONGESTION'], 'est_time': est_time, 'density': density, '0': 0, '1': 0, '2': 0, '3': 0, '4': 0} routes[route][str(int(dictionary['CONGESTION']))] = 1 else: routes[route]['tot_resp']+=1 routes[route]['ratings_sum']+=dictionary['CONGESTION'] routes[route][str(int(dictionary['CONGESTION']))]+=1 dictionary = ibm_db.fetch_both(res) print "GOT ALL POSTS IN LAST HOUR" print routes print print "CALCULATING AND UPDATING LIVE TRAFFIC CONGESTION INFO..." # calculate averages for all routes and update live table (and maybe some other count table) for route in routes: congestion_av = routes[route]['ratings_sum']/float(routes[route]['tot_resp']) print "AVERAGE FOR", (route), ":" print congestion_av cong = int(round(congestion_av)) cong_resp, tot_resp = routes[route][str(cong)], routes[route]['tot_resp'] p = float(cong_resp)/float(tot_resp)*100. percentage = int(round(p))
def run_test_decfloat(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: serverinfo = ibm_db.server_info( conn ) drop = "DROP TABLE STOCKPRICE" try: result = ibm_db.exec_immediate(conn,drop) except: pass # Create the table stockprice if (serverinfo.DBMS_NAME[0:3] == 'IDS'): create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))" else: create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))" result = ibm_db.exec_immediate(conn, create) # Insert Directly insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)" result = ibm_db.exec_immediate(conn, insert) # Prepare and Insert in the stockprice table stockprice = (\ (20, "Zaral", 102.205, "100.234"),\ (30, "Megabyte", 98.65, "1002.112"),\ (40, "Visarsoft", 123.34, "1652.345"),\ (50, "Mailersoft", 134.22, "1643.126"),\ (60, "Kaerci", 100.97, "9876.765")\ ) insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)' stmt = ibm_db.prepare(conn,insert) if stmt: for company in stockprice: result = ibm_db.execute(stmt,company) id = 70 company = 'Nirvana' stockshare = 100.1234 stockprice = "100.567" try: ibm_db.bind_param(stmt, 1, id) ibm_db.bind_param(stmt, 2, company) ibm_db.bind_param(stmt, 3, stockshare) ibm_db.bind_param(stmt, 4, stockprice) error = ibm_db.execute(stmt); except: excp = sys.exc_info() # slot 1 contains error message print(excp[1]) # Select the result from the table and query = 'SELECT * FROM STOCKPRICE ORDER BY id' 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 ) while ( data ): print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3])) data = ibm_db.fetch_both( stmt ) try: stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) ibm_db.execute(stmt) rc = ibm_db.fetch_row(stmt, -1) print("Fetch Row -1:%s " %str(rc)) except: print("Requested row number must be a positive value") ibm_db.close(conn) else: print("Connection failed.")
def doEverything(): commands = [] # connect to database conn = ibm_db.connect(url, '', '') commands.append("Connected to " + url) # set up variables and data tableName = "pythonDRDATest" user1 = DataFormat("test1", 1) user2 = DataFormat("test2", 2) user3 = DataFormat("test3", 3) # 1 Create table commands.append("\n#1 Create table") sql = "create table " + tableName + "(name varchar(255), value integer)" ibm_db.exec_immediate(conn, sql) commands.append( "\tCreate a table named: " + tableName) commands.append("\tCreate Table SQL: " + sql) # 2 Inserts commands.append("\n#2 Inserts") # 2.1 Insert a single document into a table commands.append("#2.1 Insert a single document into a table") sql = "insert into " + tableName + " values(?,?)" statement = ibm_db.prepare(conn, sql) ibm_db.bind_param(statement, 1, user1.name) ibm_db.bind_param(statement, 2, user1.value) ibm_db.execute(statement) commands.append("\tCreate Document -> " + user1.name + " : " + str(user1.value)) commands.append("\tSingle Insert SQL: " + sql) sql = "insert into " + tableName + " values(?,?)" statement = ibm_db.prepare(conn, sql) ibm_db.bind_param(statement, 1, user2.name) ibm_db.bind_param(statement, 2, user2.value) ibm_db.execute(statement) commands.append("\tCreate Document -> " + user2.name + " : " + str(user2.value)) commands.append("\tSingle Insert SQL: " + sql) sql = "insert into " + tableName + " values(?,?)" statement = ibm_db.prepare(conn, sql) ibm_db.bind_param(statement, 1, user3.name) ibm_db.bind_param(statement, 2, user3.value) ibm_db.execute(statement) commands.append("\tCreate Document -> " + user3.name + " : " + str(user3.value)) commands.append("\tSingle Insert SQL: " + sql) # 2.2 Insert multiple documents into a table # Currently there is no support for batch inserts with ibm_db commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts") # 3 Queries commands.append("\n#3 Queries") # 3.1 Find one document in a table that matches a query condition commands.append("#3.1 Find one document in a table that matches a query condition") sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'" stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) commands.append("\tFind document with name: " + user1.name) commands.append("\tFirst document with name -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1])) commands.append("\tQuery By name SQL: " + sql) # 3.2 Find documents in a table that match a query condition commands.append("#3.2 Find documents in a table that match a query condition") sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'" stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) commands.append("\tFind all documents with name: " + user1.name) while dictionary != False: commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1])) dictionary = ibm_db.fetch_both(stmt) commands.append( "\tQuery All By name SQL: " + sql) # 3.3 Find all documents in a table commands.append("#3.3 Find all documents in a table") sql = "select * from " + tableName stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) commands.append( "\tFind all documents in table: " + tableName) while dictionary != False: commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1])) dictionary = ibm_db.fetch_both(stmt) commands.append("\tFind All Documents SQL: " + sql) # 4 Update documents in a table commands.append("\n#4 Update documents in a table") sql = "update " + tableName + " set value = ? where name = ?" statement = ibm_db.prepare(conn, sql) ibm_db.bind_param(statement, 1, 4) ibm_db.bind_param(statement, 2, user2.name) ibm_db.execute(statement) commands.append( "\tDocument to update: " + user2.name) commands.append("\tUpdate By name SQL: " + sql) # 5 Delete documents in a table commands.append("\n#5 Delete documents in a table") sql = "delete from " + tableName + " where name like '" + user1.name + "'" ibm_db.exec_immediate(conn, sql) commands.append("\tDelete documents with name: " + user1.name) commands.append("\tDelete By name SQL: " + sql) # 6 Drop a table commands.append("\n#6 Drop a table") sql = "drop table " + tableName; ibm_db.exec_immediate(conn, sql) commands.append("\tDrop table: " + tableName) commands.append("\tDrop Table SQL: " + sql) ibm_db.close(conn) commands.append("\nConnection closed") return commands
def run_test_154(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, 0) try: statement = 'DROP TABLE fetch_test' result = ibm_db.exec_immediate(conn, statement) except: pass server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)' st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)" st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)" st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)" st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)" else: statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)' st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)" st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)" st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)" st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)" result = ibm_db.exec_immediate(conn, statement) result = ibm_db.exec_immediate(conn, st0) result = ibm_db.exec_immediate(conn, st1) result = ibm_db.exec_immediate(conn, st2) result = ibm_db.exec_immediate(conn, st3) statement = "SELECT col1, col2 FROM fetch_test" result = ibm_db.prepare(conn, statement) ibm_db.execute(result) row = ibm_db.fetch_tuple(result) while ( row ): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row[0],row[0].length, row[1],row[1].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\ (row[0], len(row[0]), row[1], len(row[1])) row = ibm_db.fetch_tuple(result) result = ibm_db.prepare(conn, statement) ibm_db.execute(result) row = ibm_db.fetch_assoc(result) while ( row ): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\ (row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2'])) row = ibm_db.fetch_assoc(result) result = ibm_db.prepare(conn, statement) ibm_db.execute(result) row = ibm_db.fetch_both(result) while ( row ): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row['COL1'], row['COL1'].length, row[1], row[1].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \ (row['COL1'],len(row['COL1']), row[1], len(row[1])) row = ibm_db.fetch_both(result) ibm_db.close(conn)