Reading Nick’s post, I’d say making your own logger is the way to go.
Here is your script, modified to use system.util.logger(). The messageBox lines have been changed to make a string varaible, the pushed to the logger. Try/Except have also been added to handle exceptions. Each function has its own logger to further delineate what’s happening.
I didn’t do anything for connection strings, because I don’t know them for your process. The query functions use the default db connection of the project if one isn’t specified. Since it’s running on the gateway, I imagine that the connection always needs to be specified, since it’s not attached to a project.
[code]def setDailyReadings():
import app
import sys #source.rootContainer source.parent
import system
from java.lang import System
from java.util import Date
sq = “’”
logger=system.util.logger(“setDailyReadings”)
try:
monthStartTStamp = app.GV.getFirstOfMonthTimeStamp()
todayST = app.GV.getTodaysTimeStampZeroTime()
tsAsString = str(monthStartTStamp)
utcDT = app.GV.getCurUtcPyDate()
prevDay = app.GV.getCurUtcPyDateWithDaysAdded(-1)
msDate = app.GV.getPythonDateAsMSListFormat(prevDay)
year = msDate[0]
month = msDate[1]
day = msDate[2]
message=("utcDT " + str(utcDT) + "\nmsDate " + str(msDate) + "\nYesterday's Date\n" + str(prevDay) )
logger.info(message)
monthStartTStamp = app.GV.getFirstOfMonthTimeStamp()
tsAsString = str(monthStartTStamp)
query = "SELECT * FROM sqlth_partitions Where start_time = " + tsAsString # + " or end_time = " + tsAsString + " order by start_time Asc"
message=('query\n' + query )
logger.info(message)
result = system.db.runPrepQuery(query)
tableNames = []
for row in result:
tableNames.append(row["pname"])
tName = tableNames[0]
query = "SELECT c.tagid, c.floatvalue, c.t_stamp, t.ColumnName, t.SiteName "
query += "From " + tName + " c "
query += "Join TagMetadata t "
query += "On c.tagid = t.tagnum "
query += "Where "
query += "c.t_stamp = "
query += "( "
query += "Select Top(1) "
query += "c2.t_stamp "
query += "From " + tName + " c2 "
query += "Where "
query += "c2.tagid = c.tagid "
query += "Order By c2.t_stamp Desc "
query += ") "
result = system.db.runPrepQuery(query)
message=("Query result " + str(result))
logger.info(message
sq = "'"
dayValues = []
for row in result:
tagId = row[0]
floatVal = row[1]
tsDate = app.GV.getDateFromTS(row[2])
msDate = app.GV.getPythonDateAsMSFormat(tsDate)
recType = row[3]
site = row[4]
nrow = [tagId, floatVal, msDate, recType, site]
message=("Data been added to dailyRows for tag " + str(tagId) + "\n" + str(nrow))
logger.info(message)
dayValues.append(nrow) #system.db.runUpdateQuery("Insert into TagDailyReadings(RecType, SiteName, dt, floatvalue, tagnum) VALUES (?, ?, ?, ?, ?)", [recType, site, msDate, floatVal, tagId ])
tagMeta = []
query = "SELECT Distinct SiteName From TagMetadata order by SiteName Asc"
sites = system.db.runPrepQuery(query)
siteNames = []
for row in sites:
siteNames.append(row["SiteName"]) #
message=("Adding " + row["SiteName"] + " to siteNames")
logger.info(message)
sq = "'"
rptDate = year + "-" + month + "-" + day
for i in range(len(siteNames)):
curSite = siteNames[i]
siteReadings = []
for j in range(len(dayValues)):
curDayReading = dayValues[j]
if(curDayReading[4] == curSite):
siteReadings.append(curDayReading)
message=("Adding reading " + str(siteReadings) + " for " + str(curSite))
logger.info(message)
if(len(siteReadings) >= 1):
query = "SELECT SiteName From PLANT_WTW Where SiteName = " + sq + curSite + sq + " And DateofReport = " + sq + rptDate + sq
result = system.db.runPrepQuery(query)
rowCount = len(result)
message=("Query " + query +"\nReturn value: " + str(result) + "\nCount: " + str(rowCount))
logger.info(message)
if(rowCount >= 1):
resQuery = app.gateway.setUpdateQuery(siteReadings, "PLANT_WTW", rptDate)
res = app.gateway.execUpdateQuery(resQuery)
else:
resDict = app.gateway.setInsertQuery(siteReadings, "PLANT_WTW", rptDate)
res = app.gateway.execInsertQuery(resDict)
except:
import sys
from java.lang import Exception
exceptionType, exception, stacktrace = sys.exc_info()
if exceptionType == Exception:
exception = exception.getCause()
logger.error(str(exception))
logger.error(exception.getMessage(), exception)
def setUpdateQuery(updateValues, tableName, rptDate):
import app
import sys
import system
from java.lang import System
from java.util import Date
sq = “’”
logger=system.util.logger(“setUpdateQuery”)
try:
site = ""
query = "Update " + tableName+ " Set SavedAuto = 1, "
for i in range(len(updateValues)):
curRec = updateValues[i]
propName = curRec[3]
site = curRec[4]
query += propName + " = " + str(curRec[1]) + ", "
query = query[:-2]
query+= " Where SiteName = " + sq + site + sq + " And DateofReport = " + sq + rptDate + sq
message=("curRec " + str(curRec) + "\nQuery\n" + str(query))
logger.info(message)
return query
except:
import sys
from java.lang import Exception
exceptionType, exception, stacktrace = sys.exc_info()
if exceptionType == Exception:
exception = exception.getCause()
logger.error(str(exception))
logger.error(exception.getMessage(), exception)
def setInsertQuery(updateValues, tableName, rptDate):
import app
import sys
import system
from java.lang import System
from java.util import Date
logger=system.util.logger(“setInsertQuery”)
try:
sq = “’”
curRec = updateValues[0]
site = curRec[4]
query = "Insert Into " + tableName + "(SavedAuto, DateofReport, SiteName, Caretaker, "
queryPlaceHolders = "(?, ?, ?, ?, "
args = []
args.append(1)
args.append(rptDate)
args.append(site)
args.append(“Auto Filed”)
site = “”
resDict = {}
argsCount = 1
for i in range(len(updateValues)):
curRec = updateValues[i]
propName = curRec[3]
value = curRec[1]
if(value != None):
query += propName + ", "
queryPlaceHolders += "?, "
value = float(value)
args.append(value)
query = query[:-2]
queryPlaceHolders = queryPlaceHolders[:-2]
queryPlaceHolders+= ")"
query+= ") VALUES " + queryPlaceHolders
resDict['query'] = query
resDict['args'] = args
message=("curRec " + str(curRec) + "\nQuery\n" + str(resDict['query']) + "\nArgs\n" + str(resDict['args']))
logger.info(message)
return resDict
except:
import sys
from java.lang import Exception
exceptionType, exception, stacktrace = sys.exc_info()
if exceptionType == Exception:
exception = exception.getCause()
logger.error(str(exception))
logger.error(exception.getMessage(), exception)
def execUpdateQuery(query):
import app
import sys
import system
from java.lang import System
logger=system.util.logger(“execUpdateQuery”)
try:
res = system.db.runUpdateQuery(query)
return res
except:
import sys
from java.lang import Exception
exceptionType, exception, stacktrace = sys.exc_info()
if exceptionType == Exception:
exception = exception.getCause()
logger.error(str(exception))
logger.error(exception.getMessage(), exception)
def execInsertQuery(resDict):
import app
import sys
import system
from java.lang import System
logger=system.util.logger(“execInsertQuery”)
try:
res = system.db.runPrepUpdate(resDict[‘query’], resDict[‘args’])
return res
except:
import sys
from java.lang import Exception
exceptionType, exception, stacktrace = sys.exc_info()
if exceptionType == Exception:
exception = exception.getCause()
logger.error(str(exception))
logger.error(exception.getMessage(), exception)[/code]