I have a simple named query that has two datetime parameters, StartDate and EndDate. The query just calls a stored procedure, passing in the two dates. Testing the named query in the builder returns what I would expect it to. yet, when calling the named query from a script, it returns nothing. Obviously the dates are getting mangled somehow.
This is a snippet of how I call the named query:
# Set default start and end dates
today = system.date.now()
StartDate = system.date.getDate(system.date.getYear(today), system.date.getMonth(today), system.date.getDayOfMonth(today))
EndDate = today
parameters = {"StartDate":StartDate, "EndDate":EndDate}
data = system.db.runNamedQuery("GetAllRowsInRange", parameters)
StartDate and EndDate look fine when I added messagebox statements to look at them.
The query does not error out, and there is a dataset returned, it is just empty.
Is there some issue pushing date objects into datetime objects? The parameters for the named query are datetime, and the variables in the script are dates.
The query works, it was tested. Works both on the SQL server side, as well as through testing the named query through the named query test interface. The query is not the problem.
As far as getting the StartDate, the method I used works. I used a messagebox call to show both the StartDate and EndDate values before the call to the named query, and they were what I expected them to be.
Except that when you type in parameter values in the named query test interface you can also type in things like single quotes which make the query work, however, when that value is not typed in by hand but is supplied by code, then the query breaks. So I have no doubt that the basic query syntax is not the problem, incorporating it with code can be.
I pass date parameters into named queries and have no problems, so I'm fairly sure this isn't some kind of bug. I'm just trying to get all of the information to be able to help you. I can't see what you wont show. It doesn't need to be real column and table names.
You were asked because how you pass StartDate and EndDate into your query matters. Curly braces do string conversions that tend to scramble date/time values.
So, I had to convert the dates to strings to pass into the named query to get it to work. This is kinda silly since there is a date data type. I should be able to just pass in the date directly.
You can pass them directly. Don’t use curly braces. Use colon-prefixed parameters and make sure the parameter type is Value.
And make sure the stored procedures parameters are a compatible type. If using MS SQL Server with DATETIME parameters, you are likely to have time zone problems. If using DATETIME2, make sure your JDBC driver is current.
{ Returning nothing is common when buggered time zones yield future time spans. }
The curly bracket method is what I found in the manual on how to pass parameters to a named query. Could you explain the method that does not use the curly brackets?
In named queries, you can choose what kind of parameter handling is used, per parameter. If you use the default Value parameters, you must prefix the parameter name with a colon in the SQL, not use quotes, and can only supply data to the query (not structure). If you use QueryString parameters, you must surround the parameters with curly braces in the SQL. You can pass structure this way (complete WHERE clauses, column/table/schema names, etc) as the parameter value is stringified (if not already a string) and stuck straight in the SQL. This means string values or anything that needs quoting must have quotes outside the curly braces.
If you drag a parameter into your SQL in the named query editor, it will add the colon or curly braces for you.
I thought you were referring to the way I was building the parameter list to pass to the named query.
I am not using curly brackets in the query inside the named query.
This is the named query (parameters defined as value parameters, both DateTime
EXEC sp_GetAllRowssInRange :StartDate, :EndDate
What I thought you meant by using curly brackets was this: (from the script that calls the stored procedure)
parameters = {“StartDate”:StartDate, “EndDate”:EndDate}
data = system.db.runNamedQuery(“GetAllRowsInRange”, parameters)
Converting the startdate and enddate to strings when adding them to the parameter list was the only way I could get the named query to work correctly.
Sure it can. String conversions apply each ends' understanding of the current time zone. When passed in binary, JDBC uses UTC and the JDBC driver is responsible for the DB end. That is really easy to screw up with MS products. (It is really hard to get this right with MS without running both DB and Ignition GW in UTC.)