I have tested the the named query using 00000000005008675309 and it returns the correct records
Here is the popup screen and value being passed into the pop-up. It's just a label on the screen, however the data being returned is a different record (I expect 3 records so that's ok).
The ID field is defined as char(20) in the SQL database. I'm not sure exactly what's going on but it seems like there's data conversion happening behind the scenes.
It might be that the props.selection data has not populated with the correct data by the time the event is called.
What happens if you try double clicking on one line, then closing the popup and double clicking on a different line? Do you get the previously selected line's data?
Try adding a label to the popup that has its text bound to {view.params.key} so you can sanity check what is actually being passed.
Also, for what it is worth, I can never seem to get the popup action to do what I want, so I use a script action instead and use system.perspective.openPopup to script the opening of my popups. It gives you a lot more control.
I didn't think about it being a 'late data arrival' issue. But, it doesn't give me the previously selected one. What's interesting is that I have another pop-up that does a query to a different table using a different value and that works fine. I'll work with our DBA to see if we can capture the query on the SQL side. I changed the action to a script and added some debugging.
def runAction(self, event):
try:
dict =event.value
vID = dict['ID']
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
system.perspective.openPopup ('DogLog',"Templates/Table",params)
except Exception as e:
vID = str(e)
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
My SQL debug table has the correct value being passed
The pop-up shows the query runs and I get the wrong data.
I even went so far as to change the query to like '%' + ID + '%'
Python's Exception class doesn't include the panoply of java exceptions that can occur, particularly any SQLException that JDBC can throw. You should include another except Throwable, t: clause for those, where you include a
from java.lang import Throwable
above your try:. (Preferably in a project library script where you delegate your event and action scripts, so it can be imported as a persistent global.)
Also, note that stringifying an exception loses the traceback, which is a real problem for java exceptions, since the traceback has any chained "caused by" inner exceptions. You should log your exceptions, using the logger methods that take a Throwable.
Thanks. I was going to fix that but right now it's just a quick catch while I'm debugging.
I changed my Named Query and added
INSERT INTO debug ([QueryText])
VALUES (:vID)
Select .... from ...where ...
My debug table has the correct value and I can use that value in SMMS to do a select query and get the correct records. This is just weird.
Going to try some things on the pop-up screen to troubleshoot.
I set the binding on the table to a property view.params.vID
Then I added the following transform script
from java.lang import Throwable
try:
vID = value
params = {"vID":vID}
dbdata = system.db.runNamedQuery("IDLookup", params)
return dbdata
except java.lang.Throwable, e:
vID = str(e.cause)
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
except Exception as e:
vID = str(e)
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
Dataset has wrong data.
Got rid of the named query and tried running with and without debug.
Debug table shows the correct value.
Table shows wrong data.
from java.lang import Throwable
try:
query = "Select * FROM [myDB] where [vID]=" + "'"+ value + "'"
dbdata = system.db.runQuery(query,'myDB')
params = {"vID":query}
system.db.runNamedQuery("INSERT_DEBUG", params)
return dbdata
except java.lang.Throwable, e:
vID = str(e.cause)
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
except Exception as e:
vID = str(e)
params = {"vID":vID}
system.db.runNamedQuery("INSERT_DEBUG", params)
My debug table has the correct record
Select * FROM [myDB] where [vID]='00000000005008675309'
This "ID" is part of a much larger system that I have not given you information about so while your point is valid it doesn't work in the "big picture". But given the choice I would have made it a number and did post-processing also.
At this point all the logic is in the popup. The scripting is all in the table object. The data is bound to the incoming parameter which is then transformed in the script.
For ease typing the input string is a 20 character number (prepended with 0). In my examples I'll use 'generic' 7 digit numbers as a representation.
All indications show that when I search for record 8675309 I'm returned a completely different record. I'm trying to figure out how 8675309 is getting 'translated' to something completely different in the query either by Ignition or MSSQL. This mistranslation, as I'll call it occurs for any value that is queried. If I were to query 4894608 I'll get records for 5552303 returned.
The recordset that is returned is 'correct' for that ID it's just the wrong ID. I.E. if I manually query 5552303 in SMMS I get the same recordset that was returned by Ignition querying 4894608
I have not been able to manually cause this mistranslation so I don't know if something is converting a string to number and hitting a boundary or what.
If I run the following in the script console I get the correct records
try:
value = '00000000008508675309'
query = "Select * FROM [Table] where [ID]=" + "'"+ value + "'"
dbdata = system.db.runQuery(query,'AutoLabel')
for row in range(dbdata.getRowCount()):
for col in range(dbdata.getColumnCount()):
print dbdata.getValueAt(row, col)
except Exception as e:
print str(e)
At this point in the story we see where the user made a terrible mistake in interpreting the resultant data as displayed.
I clicked icon to see the raw table data
and realized that the Table object is not spacing the columns and the ID and the next field are overlapping. I was only seeing the the first ## characters of the ID and the next 7 characters of the next field all bunched together in the popup table.
I was only seeing the the first ## characters of the ID and the next 7 characters of the next field all bunched together in the popup table.