Updating a table based off a data selection

toddayers · August 13, 2020, 1:12pm

I am trying to update a table I have based off the date selected in a popup calendar component. The table is supposed to be displaying a t_stamp and coordinators name when it is compared to the date property of the pop up calendar. But it is not working… This is what I currently have:

event.source.parent.getComponent(‘CoordinatorTable’).data = system.db.runQuery(“Select t_stamp, Coordinator from SQDCME.dbo.SQDCME where t_stamp = [Root Container.ProductionDate.date]”)

But it does not seem to be working correctly.

lrose · August 13, 2020, 1:22pm

As a note if you use three back quotes ``` before and after your code it will be formated and more easily readable by forum members.

You have just included the string [Root Container.ProductionDate.date] in the query as opposed to actually getting the value for the date.

I am also assuming that ProductionDate is the calendar component, in which case your code should look something like the following:

event.source.parent.getComponent('CoordinatorTable').data = system.db.runQuery("Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = '" + event.source.parent.getComponent('ProductionDate').date + "'"

toddayers · August 13, 2020, 1:28pm

Thank you. I didnt know about the 3 quotes…

toddayers · August 13, 2020, 1:57pm

I tried using that code that you mentioned, but it didn work. It actually wouldn’t even accept it and let me close the window.

‘’‘event.source.parent.getComponent(‘CoordinatorTable’).data = system.db.runQuery("Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = " + event.source.parent.getComponent(‘ProductionDate’).date + "’" ‘’’

lrose · August 13, 2020, 2:03pm

The back quote is on the same key as the tilde, or at least it is in the US.

Are you getting an error message with your code?

Is this Perspective or vision?

toddayers · August 13, 2020, 2:07pm

It is vision. It not getting an error message per se, it won’t allow the window to close. When I hover over it, it says - no viable alternative at input ‘\n’

lrose · August 13, 2020, 2:11pm

Can you post a screen snippet of this?

pturmel · August 13, 2020, 2:19pm

That would be a syntax error. { Also, when using the triple-backquotes to trigger formatting, put them on their own lines above and below. }

zacht · August 13, 2020, 2:24pm

Also look into using system.db.runPrepQuery instead of system.db.runQuery.

toddayers · August 13, 2020, 2:50pm

I figured out the syntax error but it still would not run. The error message that showed up was this:

Traceback (most recent call last):

  File "<event:propertyChange>", line 1, in <module>



java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = event.source.parent.getComponent('ProductionDate').date, , [null], )


	caused by Exception: Error executing system.db.runPrepQuery(Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = event.source.parent.getComponent('ProductionDate').date, , [null], )
	caused by GatewayException: Remote function reference 'event.source.parent.getComponent' is not allowed, and the column name 'event' could not be found or is ambiguous.
	caused by SQLServerException: Remote function reference 'event.source.parent.getComponent' is not allowed, and the column name 'event' could not be found or is ambiguous.

Ignition v7.9.13 (b2019120915)
Java: Azul Systems, Inc. 9.0.7.4

lrose · August 13, 2020, 2:59pm

The syntax for runPrepQuery is different from runQuery.

query = 'Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = ?'
args = [event.source.parent.getComponent('ProductionDate').date]
results = system.db.runPrepQuery(query, args)
event.source.parent.getComponent('CoordinatorTable').data = system.dataset.toDataSet(results)

toddayers · August 13, 2020, 3:13pm

I’m thinking that no matter what I do, I cannot get it to work. I am trying to use the propertychange of the date component but it does not seem to be working…Dailyproduction.proj (657.5 KB)

lrose · August 13, 2020, 3:38pm

This is a working script, assuming that the query is correct and will return values, there is no reason this code should not work.

Something to look out for, if the database which contains the table of interest is not the default database of the project then you must supply the database connection in the call to runQuery or runPrepQuery.

#filter the property change so that code is only executed when the date property changes
if event.propertyName == date:
    #specify the query to run representing parameter values with the '?' character
	query = 'Select t_stamp,Coordinator FROM SQDCME.dbo.SQDCME where t_stamp = ?'
    #use the newValue of the date as the value to substitute into the query
    args = [event.newValue]
    #run the query
	results = system.db.runPrepQuery(query,args)
    #set the table's dataset equal to the results of the query. NOTE: system.db.runPrepQuery
    #returns a pyDataSet and the table.data property is a Dataset type so use
    #system.dataset.toDataSet to convert the results from a pyDataSet to a DataSet type
	event.source.parent.getComponent('CoordinatorTable').data = system.dataset.toDataSet(results)

Also note that if this is the only function your wanting to perform in the propertyChange event, probably the easier (some would argue better) way to accomplish this is to use an SQL Query binding on the data property of the table and reference the popup calendar component’s date property there.

toddayers · August 13, 2020, 4:41pm

So I took your advice and used a SQL Query binding on the data property of the table, but still didnt work. That query is accurate as well. Is it possible that since I am not using the newer version 8.0 that is not working?Dailyproduction.proj (658.0 KB)

zacht · August 13, 2020, 4:54pm

Have you enabled Legacy Database Access in the project properties?

lrose · August 13, 2020, 4:55pm

Okay, so I took a look at the project.

You misunderstood what I was saying, the script I provided would have gone in the propertyChange event of the popup calendar component. The binding should look like this:

toddayers · August 13, 2020, 5:02pm

I had originally put it in the propertyChange event of the popup calendar and it still did not work. Also what you have in that image… does not work.

lrose · August 13, 2020, 5:25pm

I don’t have a connection to your database so it is difficult for me to troubleshoot this particular error.

The script in and of its self should work, it is possible that it isn’t yielding the results that you expect. For instance, we are asking for results where t_stamp is “Equal” to the selection, depending on how the data is stored in t_stamp, this could be unreliable. You may need to insure that the “date” property holds a value which will yield results when compared to the t_stamp column.

In theory there is no syntactic issue that I can see with the SQL, assuming that there really is a table Named SQDCME.

Try placing single quotes around the reference to the date. I threw together a sample to demonstrate, this works:

toddayers · August 13, 2020, 6:04pm

Yeah the submit button takes the information in the form and dumps it into a table called SQDCME. What I am trying to do is show the coordinators that have already entered there information. If I do it off the current date… it works… but choosing that down arrow and selecting a different date is where we have the issue. Here is my actual sql table that is being polled.

The t_stamp column shows the date and a time of 00:00:00.000 for each record. Also the interesting thing is my original sql query worked with no issues.

Select t_stamp, Coordinator
from SQDCME.dbo.SQDCME
where CAST(t_stamp AS date) AND CAST(GETDATE() AS Date)

toddayers · August 13, 2020, 6:05pm

I did try the quotes around the Root.ProductionDate.date, but it just cleared out the table no matter what date I selected.