Gateway Script

I am trying to call a function in the app module from a timer script in the Event Scripts Gateway.
My function either inserts or updates a record with existing data from the current monthly table.
This function works fine when it is called from a button event but it seems it is not been called from the Gateway script though the gateway script is being invoked.
When I look at Gateway Scripts in the browser the info below is displayed.
9ms is not long enough for my function to complete, it has no System.gui calls.

setDailyReadings @20,000ms
Last Execution: Tue Sep 23 12:01:28 BST 2014
Duration: 9ms

Can you post the script?
Are you passing in the connection parameter?

Here is an article that shows how to debug and test gateway scripts: perfectabstractions.com/blog … nt-scripts

I have pasted the code below. Where do place the connection string?.
I have removed exception handling there are still no error’s shown in the Gateway Scripts I am using version 7.6

def setDailyReadings():
import app
import sys #source.rootContainer source.parent
import system
from java.lang import System
from java.util import Date
sq = “’”

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]
#system.gui.messageBox("utcDT " + str(utcDT) + "\nmsDate " + str(msDate) + "\nYesterday's Date\n" + str(prevDay) )
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"
#system.gui.messageBox('query\n' + query )
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) #system.gui.messageBox("Query result " + str(result))

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] #system.gui.messageBox("Data been added to dailyRows for tag " + str(tagId)  + "\n" + str(nrow))
	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"]) #system.gui.messageBox("Adding " + row["SiteName"] + " to siteNames")
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) #system.gui.messageBox("Adding reading " + str(siteReadings) + " for " + str(curSite))	
	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)
		#system.gui.messageBox("Query " + query +"\nReturn value: " + str(result) + "\nCount: " + str(rowCount))
		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)

def execUpdateQuery(query):
import app
import sys
import system
from java.lang import System
res = system.db.runUpdateQuery(query)
return res

def execInsertQuery(resDict):
import app
import sys
import system
from java.lang import System
res = system.db.runPrepUpdate(resDict[‘query’], resDict[‘args’])
return res

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]

The database connection is used this way:

dbconnection = "ConnectionName" val1 = 50 result = system.db.runPrepQuery("SELECT col FROM table1 WHERE ID = ?", [val1], dbconnection)

Just a tip, you can write your queries this way.

query = """ SELECT c.tagid, c.floatvalue, c.t_stamp, t.ColumnName, t.SiteName " From %s c Join TagMetadata t On c.tagid = t.tagnum Where c.t_stamp = (Select Top(1) c2.t_stamp From %s c2 Where c2.tagid = c.tagid Order By c2.t_stamp Desc ) """%(tname,tname)

I have copied the changes you made to the module. There are no new exceptions showing up either under Gateway Scripts or in the Console is that the right place to check for them. I have also added a database parameter to my insert/Update functions shown below. Is it just the name of the database connection that is needed for this parameter or do we need more if so can you please give an example of a connection string to connect to Ms SQL server 2008/2012

def execInsertQuery(resDict):
import app
import sys
import system
from java.lang import System
logger=system.util.logger(“execInsertQuery”)
dbconnection = “CAVAN_DB”
try:
res = system.db.runPrepUpdate(resDict[‘query’], resDict[‘args’], dbconnection)
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)

My script will work if I place all code in one script and remove all calls to the app module.
This is not a very practical solution as it will result in a lot of code duplication.
I have included the import app statement but still it seems functions in this module are not visible and calls to them cause the script to fail silently.
On the database connection just the name of the connection works for the parameter.

If the problem is with the imports, then put them inside the try: blocks. I’d be willing to bet that it won’t fail silently then!

It seems the problem is with the imports, the only error message I am getting is “global name ‘app’ is not defined” this is regardless of whether the import app statement is inside or outside the try block.
Also it the calls to functions within an app function that causes the error that is the first call to an app function works but calls within that function to another app function results in the “global name ‘app’ is not defined” error.