Passing variables into SQL query

Ignition
1

I have what would seem to be a simple script that collects a date from popup calendar name 'weekSelect', places the value into a variable named weekStart and then formats the variable with dateFormat. What I'm having an issue with is getting the variable to work with the same scripts SQL query. The error I receive, GatewayException & SQLServerException: Conversion failed when converting date and/or time from character string.

Code (simplified as to not show all tables):
import datetime

reportDate = datetime.datetime.now().strftime("%Y-%m-%d") # Convert to string format
weekStart = event.source.parent.getComponent('weekSelect').date
weekEnd = system.date.addDays(weekStart, 7)

####### Convert weekStart and weekEnd to SQL-compatible format
weekStart = system.db.dateFormat(weekStart, "yyyy-MM-dd")
weekEnd = system.db.dateFormat(weekEnd, "yyyy-MM-dd")

query = """
DECLARE @weekStart DATE = 'weekStart';
DECLARE @weekEnd DATE = 'weekEnd';

SELECT
*
FROM
timesheets t
JOIN projects p ON t.projectId = p.id
JOIN users u ON t.userID = u.id
WHERE
t.startTime >= @weekStart AND t.startTime < @weekEnd
ORDER BY
t.startTime
"""

result = system.db.runQuery(query).

Seems I'm having an issue getting the variable called from the query. Several attempts in different formats have failed.

2

A few things:

1.) It's really hard to decipher what exactly is happening if the code isn't formatted correctly, if you use the preformatted text option (</> button) it will retain all of your codes formatting and make it much more readable.

2.) You should at a minimum be using system.db.runPrepQuery() but really you should create a named query and use system.db.runNamedQuery()

3.) Don't format the dates and then try to concatenate them into the query string (doing this particuarly with user provided data is a very poor practice). Instead, use one of the suggested functions above and provide the date object unchanged and let the JDBC driver hand it off to the DB.

4.) The code doesn't work because you are setting your parameters to the string literals 'weekStart', and 'weekEnd', which aren't valid dates and can not be parsed into dates by your DB. I'm sure if you look at the error you are getting you would see something along those lines.

Here is a link to the documentation for system.db.runNamedQuery(), post back if you need more help after trying this.

https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery

5 Likes