Passing calendar date into Python script

Ignition
1

I finally wanted to use one of the calendar components, and am having trouple passing in the startDate into Python string formatting. I’ve tried al kinds of things, but the end result is an arithmetic overflow error. All I want to do is something like this:

"SELECT Machine FROM MyTable WHERE timeStamp>=%d"%(event.source.parent.getComponent('Date Range 1').startDate)

I’ve tried using ‘%s’, various date formats, etc, but I’m obviously missing something simple.

2

For the snippet of code you posted, %s or ‘%s’ should both work, it just depends on if you want the inserted string to have single quotes surrounding it or not. The arithmetic overflow error is odd, do you mind posting more of the code so I can get a better context of what’s going on? Thanks.

3

This is what I had at the beginning, and I get a type conversion error, most likely because of the Java date format. I then tried converting the Java date to other formats, which resulted in the overflow errors. To add to the confusion, when I do a ‘print startDate’ from the Calendar control, the format is different from that in the designer.

Anyway, let’s see if we can get something like this to work:

d=event.source.parent.getComponent('Date Range').startDate
q="SELECT Machine FROM DataMachines WHERE ts >= '%s'"%(d)
print q
system.db.runQuery(q)
4

Have you tried using runPrepQuery? It should properly handle the string substitution for you:

system.db.runPrepQuery("SELECT Machine FROM DataMachines WHERE ts>=?", [event.source.parent.getComponent('Date Range').startDate])
5

No, actually I was writing ‘q’ to a string dynamic variable, and then binding this parameter to a dataset through a SQL binding (I construct the query dynamically based on a multi-selection button). Is there a way to run a PrepQuery through a property binding?

6

Ah, I think I see the issue now. You’re trying to build the query string yourself and you need to know how to convert the date string. You can use java.sql.Timestamp which wraps the Java Date so that the JDBC API sees it as a SQL TIMESTAMP. Try this:

[code]from java.sql import Timestamp

s = event.source.parent.getComponent(‘Date Range’).startDate
new_s = Timestamp(s.getTime())
q = “SELECT Machine FROM DataMachines WHERE ts>=’%s’” %(new_s)[/code]

7

Yep, that did the trick. Thanks!