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 defaultLoader_settings():
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
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:
vehicleRfid.setdefault(l_temp_dict["RFID"],
l_temp_dict["VEHICLE_NUMBER"])
vehicleDetails.setdefault(vehicleRfid[l_temp_dict["RFID"]], [
l_temp_dict["WALLET_BAL"], l_temp_dict["USER_NAME"],
l_temp_dict["VEHICLE_TYPE"], l_temp_dict["BLOCK_STATUS"]
])
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)
print "Server Started"
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 dBop_Insert(message):
global connection
connectioncheck_handler()
try:
upload_query = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME+" VALUES (DEFAULT,\'"+str(message["Current_ToGrid"])+"\',\'"+str(message["Current_SolarSupply"])+"\',\'"+str(message["Current_GridSupply"])+"\',\'"+str(message["Energy_ToGrid"])+"\',\'"+str(message["Energy_SolarSupply"])+"\',\'"+str(message["Energy_GridSupply"])+"\',\'"+str(message["TotalEnergy"])+"\',\'"+str(message["Time"])+"\')"
stmt = ibm_db.exec_immediate(connection, upload_query)
ibm_db.free_stmt(stmt)
except Exception as e:
logging.error("The error in dBop_Insert is %s,%s"%(e,type(e)))
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 db_insert(db_conn_, insert_query):
try:
statement = ibm_db.exec_immediate(db_conn_, insert_query)
ibm_db.free_stmt(statement)
except Exception as e:
logging.error("The dbInsert operation error is %s" % (e))
return False
except:
logging.error("The dbInsert operation error is %s" % (ibm_db.stmt_errormsg()))
return False
return True
def updateBlockStatus(p_vehicleNum,p_blockStatus):
l_connection = dB_init()
if(l_connection == None):
print("Database Connection Failed on Database Query")
return
update_data = "UPDATE "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_1+" SET BLOCK_STATUS = "+str(p_blockStatus)+" WHERE VEHICLE_NUMBER = '"+str(p_vehicleNum)+"'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, update_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("Update Wallet : " + str(e))
ibm_db.close(l_connection)
def updateWallet(p_vehicleNum):
l_connection = dB_init()
if(l_connection == None):
print("Database Connection Failed on Database Query")
return
update_data = "UPDATE "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_1+" SET WALLET_BAL = "+str(vehicleDetails[p_vehicleNum][DETAILS_BALANCE])+" WHERE VEHICLE_NUMBER = '"+str(p_vehicleNum)+"'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, update_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("Update Wallet : " + str(e))
ibm_db.close(l_connection)
def updateBlockStatus(p_vehicleNum, p_blockStatus):
l_connection = dB_init()
if (l_connection == None):
print("Database Connection Failed on Database Query")
return
update_data = "UPDATE " + DB_SCHEMA + "." + DATABASE_TABLE_NAME_1 + " SET BLOCK_STATUS = " + str(
p_blockStatus) + " WHERE VEHICLE_NUMBER = '" + str(p_vehicleNum) + "'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, update_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("Update Wallet : " + str(e))
ibm_db.close(l_connection)
def executeSelect(statement):
connection = ibm_db.pconnect(url, '', '')
statement = ibm_db.prepare(connection, statement)
ibm_db.execute(statement)
data = ibm_db.fetch_tuple(statement)
result = []
while (data):
result.append(data)
data = ibm_db.fetch_tuple(statement)
ibm_db.free_stmt(statement)
ibm_db.close(connection)
return result
def insert_data(sql, params):
#Insert crypto currencies data to database.
try:
con = connect_to_db()
logging.info('Connected to database.')
stmt = ibm_db.prepare(con, sql)
o = ibm_db.execute_many(stmt, tuple(params))
logging.info(f'{o} records inserted into database.')
ibm_db.free_stmt(stmt)
ibm_db.close(con)
logging.info('Close connection.')
except Exception as e:
print('Error occured!')
logging
logging.error(f'{e} {ibm_db.stmt_error()} {ibm_db.stmt_errormsg()}')
sys.exit(1)
def updateWallet(p_vehicleNum):
l_connection = dB_init()
if (l_connection == None):
print("Database Connection Failed on Database Query")
return
update_data = "UPDATE " + DB_SCHEMA + "." + DATABASE_TABLE_NAME_1 + " SET WALLET_BAL = " + str(
vehicleDetails[p_vehicleNum][DETAILS_BALANCE]
) + " WHERE VEHICLE_NUMBER = '" + str(p_vehicleNum) + "'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, update_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("Update Wallet : " + str(e))
ibm_db.close(l_connection)
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 hello():
if hasVcap == False:
return "No VCAP_SERVICES variable available."
else:
if hasdashDB == False:
return "No dashDB Service instance bound. Please bind a dashDB Service instance to the app"
connection = ibm_db.connect(url, '', '')
statement = ibm_db.prepare(connection, 'SELECT TABNAME, TABSCHEMA from SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY')
ibm_db.execute(statement)
out = "<html><h2>10 rows from SYSCAT.TABLES table</h2><table border=\"1\"><tr><td>Table Name</td><td>Table Schema</td>"
data = ibm_db.fetch_tuple(statement)
while (data):
out = out + "<tr><td>"+data[0]+"</td><td>"+data[1]+"</td></tr>"
data = ibm_db.fetch_tuple(statement)
ibm_db.free_stmt(statement)
ibm_db.close(connection)
out = out + "</table></html>"
print out
return out
def dataBaseUpload(p_todayDate,p_containerid,p_status,p_quantity):
global DATABASE_TABLE_NAME
l_checkData_length = dict()
#Connecting to the database
l_connection = dB_init()
if(l_connection == None):
logging.error("Database Connection Failed on Database Upload")
return
#Current Time upload on the database
l_time = datetime.datetime.now().strftime('%H:%M:%S')
p_todayDate = p_todayDate.strftime('%Y-%m-%d')
l_date_query = "SELECT COUNT(*) FROM "+DB_SCHEMA+"."+DATABASE_TABLE_NAME+" WHERE DATES = '"+str(p_todayDate)+"'AND STATUS = '"+str(p_status)+"' AND SCALE_ID = '"+p_containerid+"'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, l_date_query)
l_checkData_length = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("dataBaseUpload_datequery_ERROR : " + str(e))
if(l_checkData_length.has_key('1') and (int(l_checkData_length['1'])) == 0):
instert_data = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME +" VALUES "+"('"+p_containerid+"','"+p_todayDate+"','"+str(l_time)+"','"+str(p_quantity)+"','"+str(p_status)+"')"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, instert_data)
except Exception as e:
logging.error("dataBaseUpload_insertdata_ERROR : " + str(e))
else:
update_query = "UPDATE "+DB_SCHEMA+"."+DATABASE_TABLE_NAME +" SET TIME = '"+str(l_time)+"', QUANTITY = '"+str(p_quantity)+"' WHERE DATES='" + str(p_todayDate) +"' AND STATUS ='"+str(p_status)+"' AND SCALE_ID = '"+p_containerid+"'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, update_query)
except Exception as e:
logging.error("dataBaseUpload_updatequery_ERROR : " + str(e))
#Closing the Database Connection
ibm_db.free_stmt(l_db_statement)
ibm_db.close(l_connection)
def reset_Prog():
global connection
date = datetime.datetime.now()
print date
print '\n \tNOTE : THIS PROGRAM WILL HELP YOU WHEN YOUR SESSION EXPIRED AND YOU COULD NOT LOGIN \n\t AND WHEN YOU TERMINATE THE CLIENT.PY PROGRAM WITH LOGGING OUT(RESPECTIVE CLIENT)'
print '\n\tSelect the client of your choice from the list below'
passd = ['peter123','eric123','sam123','mark123']
login = ['*****@*****.**','*****@*****.**','*****@*****.**','*****@*****.**']
print login
select = int(raw_input('\nenter the number between 0 to 3:\t'))
if (select == 0):
i = 0
print 'you have selected the client %s'%(login[i])
elif (select == 1):
i = 1
print 'you have selected the client %s'%(login[i])
elif (select == 2):
i = 2
print 'you have selected the client %s'%(login[i])
elif (select == 3):
i = 3
print 'you have selected the client %s'%(login[i])
else:
print 'wrong selection'
pass
tablename = 'DASH7581.USERTABLE'
# for i in range(len(login)):
update_state = "UPDATE "+tablename+" SET LAST_ACTIVITY_TIME = \'"+str(date)+"\' WHERE EMAIL = \'"+login[i]+"\' AND PASSWORD = \'"+passd[i]+"\'"
stmt = ibm_db.exec_immediate(connection, update_state)
ibm_db.free_stmt(stmt)
# for i in range(0,len(login)):
update_state = "UPDATE "+tablename+" SET USER_STATE = 0 WHERE EMAIL = \'"+login[i]+"\' AND PASSWORD = \'"+passd[i]+"\'"
stmt = ibm_db.exec_immediate(connection, update_state)
ibm_db.free_stmt(stmt)
print 'done'
def defaultLoader_settings():
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
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:
vehicleRfid.setdefault(l_temp_dict["RFID"],l_temp_dict["VEHICLE_NUMBER"])
vehicleDetails.setdefault(vehicleRfid[l_temp_dict["RFID"]],[l_temp_dict["WALLET_BAL"],l_temp_dict["USER_NAME"],l_temp_dict["VEHICLE_TYPE"],l_temp_dict["BLOCK_STATUS"]])
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)
print "Server Started"
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 DASH103289.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 updateDatabase(p_vehicleNum, p_amount, p_flag):
l_date = datetime.datetime.now()
l_time = l_date.strftime('%H:%M:%S')
l_connection = dB_init()
if (l_connection == None):
print("Database Connection Failed on Database Query")
return
if (p_flag == 0):
insert_data = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_2+" VALUES "+"('"+str(p_vehicleNum)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_OWNER_NAME])+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_VEHICLE_TYPE]) \
+"','"+str(TOLL_CROSSED)+"','"+str(l_date.date())+"','"+str(l_time)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_BALANCE])+"','"+str("0")+"','"+str(p_amount)+"')"
else:
insert_data = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_2+" VALUES "+"('"+str(p_vehicleNum)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_OWNER_NAME])+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_VEHICLE_TYPE]) \
+"','"+str(TOLL_CROSSED)+"','"+str(l_date.date())+"','"+str(l_time)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_BALANCE])+"','"+str(p_amount)+"','"+str("0")+"')"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, insert_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("dataBaseUpload_insertdata_ERROR : " + str(e))
ibm_db.close(l_connection)
updateWallet(p_vehicleNum)
def updateDatabase(p_vehicleNum,p_amount,p_flag):
l_date = datetime.datetime.now()
l_time = l_date.strftime('%H:%M:%S')
l_connection = dB_init()
if(l_connection == None):
print("Database Connection Failed on Database Query")
return
if(p_flag == 0):
insert_data = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_2+" VALUES "+"('"+str(p_vehicleNum)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_OWNER_NAME])+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_VEHICLE_TYPE]) \
+"','"+str(TOLL_CROSSED)+"','"+str(l_date.date())+"','"+str(l_time)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_BALANCE])+"','"+str("0")+"','"+str(p_amount)+"')"
else:
insert_data = "INSERT INTO "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_2+" VALUES "+"('"+str(p_vehicleNum)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_OWNER_NAME])+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_VEHICLE_TYPE]) \
+"','"+str(TOLL_CROSSED)+"','"+str(l_date.date())+"','"+str(l_time)+"','"+str(vehicleDetails[p_vehicleNum][DETAILS_BALANCE])+"','"+str(p_amount)+"','"+str("0")+"')"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, insert_data)
ibm_db.free_stmt(l_db_statement)
except Exception as e:
logging.error("dataBaseUpload_insertdata_ERROR : " + str(e))
ibm_db.close(l_connection)
updateWallet(p_vehicleNum)
import ibm_db
#conn = ibm_db.connect("DATABASE=ONETEAMP;HOSTNAME=b03acirdb051.ahe.boulder.ibm.com;PORT=60015;PROTOCOL=TCPIP;UID=*****;PWD=*****;SECURITY=SSL;SSLCLIENTKEYSTOREDB=C:\Users\FuLiQu\Documents\SSL\db2_ssl_keydb.kdb;SSLCLIENTKEYSTASH=C:\Users\FuLiQu\Documents\SSL\db2_ssl_keydb.sth;", "", "")
conn = ibm_db.connect(
"DATABASE=ONETEAMP;HOSTNAME=b03acirdb051.ahe.boulder.ibm.com;PORT=60027;PROTOCOL=TCPIP;UID=*****;PWD=*****;",
"", "")
if conn:
sql = "SELECT app_code,name from appl.application FETCH FIRST 10 rows ONLY "
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_both(stmt)
while (result):
#print(result[0],result[1]) CV,,,,,,,,,,,,,,,;[;[ MMMM GB C
print(result)
result = ibm_db.fetch_both(stmt)
ibm_db.free_stmt(stmt)
ibm_db.close(conn)
def removePreparedStmtFromList(jLineStmtID, preparedStmtList):
for preparedStmt in preparedStmtList:
if preparedStmt.stmtId == jLineStmtID:
ibm_db.free_stmt(preparedStmt.stmtHdl)
preparedStmtList.remove(preparedStmt)
