Hi All,
Do you know if there is a way to pass an additional parameter to the function called in writeAsync?
Basically I am iterating through a tag folder to store 1000 raw data samples using a for loop. I would like to pass along the index of my for loop as well in order to populate a column in my table called “Sample number”
I’m doing this in a gateway event script.
Is there a way to do this? Is there a better way to store a tag array than iterating through it one at a time?
Thanks!
-Kelly
Are you asking about the callback function?
From the docs, regarding the callback function:
The function definition must provide a single argument, which will hold a List of quality codes when the callback function is invoked.
So no, I don’t believe there is a way.
If you could provide a snippet of the relevant code, I could see about providing a good way to write to the table.
You should be doing your reading and writing using single calls to the functions passing all (1000, in this case) tagpaths - then you just need to loop over them accordingly in your callback as well. Passing 1000 different callback functions to 1000 different writeAsync calls is orders of magnitude more of a performance hit than looping an in-memory list of 1000 items twice.
EDIT: But I’ll second Cody’s suggestion to post some code, otherwise we’re working blind.
Hi All,
Thank you for the suggestions! I actually ended up just doing a blocking call in the for loop since that seemed to work better. Still seems like a inelegant solution so if you have suggestions on a better way to do this I’m all ears!
for i in range (numberOfSamples):
tagName = "[default]ForceData/ForceData_{}_".format(i)
paths = [
"[default]PartNumber",
"[default]JobNumber",
tagName]
tagValues = system.tag.readBlocking(paths)
args = [
Equipment,
currentDateTime,
tagValues[0].value,
tagValues[1].value,
i,
tagValues[2].value
]
query = """
INSERT INTO [dbo].[DATA_RAW]
([EQUIP_NUM]
,[TIMESTAMP]
,[PART_NUMBER]
,[JOB_NUMBER]
,[SAMPLE_NUMBER]
,[SAMPLE_MEASUREMENT])
VALUES
(?,?,?,?,?,?)"""
#logger.info("Query built")
system.db.runPrepUpdate(query, args, "Monitoring")
You really don't want to call readBlocking() in a loop. See this topic for an example of combining many tag reads into a single call, then looping through the grouped results:
I would also construct a SQL INSERT statement with a multi-row VALUES clause, permitting a single DB call as well.
Gotcha! So for the tag read this would be better:
paths = [
"[default]PartNumber",
"[default]JobNumber"]
for i in range (numberOfSamples):
tagName = "[default]ForceData/ForceData_{}_".format(i)
paths.append(tagName)
And then read that all as once.
For the insert statement, you run it as a prep query? How would iterate through the list to add as value clauses? Would you construct it as a fully formed string and then do system.db.runQuery rather than as a prep?
-Kelly
Kinda thinking this might be the best way to go?
query = """
INSERT INTO [dbo].[DATA_RAW]
([EQUIP_NUM]
,[TIMESTAMP]
,[PART_NUMBER]
,[JOB_NUMBER]
,[SAMPLE_NUMBER]
,[SAMPLE_MEASUREMENT])
VALUES"""
for i in range (numberOfSamples):
query += "({0},{1},{2},{3},{4},{5}),".format(equipment, currentDateTime, tagValues[0].value, tagValues[1].value, i, tagValues [2+i])
So I did the above and it all seems to look good but I get this error when trying to execute the updatequery:
Caused by: org.xml.sax.SAXParseException; lineNumber: 9; columnNumber: 53; An invalid XML character (Unicode: 0x0) was found in the CDATA section.
Tried doing the ascii encode then decode method to remove weird characters but that didn’t help.
Thoughts?
Thank you so much for your help!
While you’re getting the benefit of a single insert of multiple rows here, you’re losing the work (and safety) that is being done by the placeholder substitution you originally had (with the prepUpdate() call). You’ll have to do some extra formatting of those () VALUES entries from your for-loop in order to get the syntax right; for example, making sure that the string fields are surrounded by single tick marks ', ensuring that the timestamp is in ODBC canonical form…
Absolutely use a prep query. Simply add , (?, ?, ?, ?, ?, ?) to the end of your SQL for each additional row.
I’d be inclined to use a transaction, use individual prepUpdate() calls, and then commit the transaction. I think the bulk solution (with the single insert) might perform better, but the former would be “safer” and less at risk of formatting issues.
EDIT: well shucks, do what @pturmel said above… The args parameter is a List, so should be able to pass all the bulk row data in there…
Sorry, I was pulled into a meeting as I was typing this, but if it helps, I do have a function to generate question marks for prep inserts.
def prepInsertQmarks(numOfGroups, groupSize):
'''
Generate a string of grouped question marks for prepUpdate inserts
example: Generate three groups of four question marks:
prepInsertQmarks(3, 4) returns (?,?,?,?),(?,?,?,?),(?,?,?,?)
'''
group = '(' + ','.join(['?'] * groupSize) + ')'
return ','.join([group] * numOfGroups)
query = """
INSERT INTO [dbo].[DATA_RAW]
([EQUIP_NUM]
,[TIMESTAMP]
,[PART_NUMBER]
,[JOB_NUMBER]
,[SAMPLE_NUMBER]
,[SAMPLE_MEASUREMENT])
VALUES %s""" % prepInsertQmarks(4,6)
print query
Output:
INSERT INTO [dbo].[DATA_RAW]
([EQUIP_NUM]
,[TIMESTAMP]
,[PART_NUMBER]
,[JOB_NUMBER]
,[SAMPLE_NUMBER]
,[SAMPLE_MEASUREMENT])
VALUES (?,?,?,?,?,?),(?,?,?,?,?,?),(?,?,?,?,?,?),(?,?,?,?,?,?)
I usually stick a newline and a tab into the .join() so if the SQL gets logged anywhere, it looks good. (:
I like the idea of creating a function for this, I could see this exact issue coming up anytime I’m logging raw data so it might be a good candidate for a shared script!
Hmmmm maybe that’s what I’ll work on this afternoon!