Reporting MySQL Query Help

Ignition
1

I am having issues using the SQL Query in reporting data source…I was able to get Historic tags to use StartDate & EndDate Parameters by just binding the Component start and end to the StartDate and EndDate, but I cannot get the report to show correct information when I use the SQL Query? Does this have something to do with how MySQL interprets the date or am I missing something? I cannot even get the Query to work from the Database Query Browser. I have tried many different things but am confused if I need to format the date first before the query is done and if I do how it needs to be formatted…Here is Code…

SELECT * FROM report_test WHERE t_stamp >= '{StartDate}' AND t_stamp < '{EndDate}'

All that it does is just pull in the table headers but no data. If i Just use the ‘< {EndDate}’ it will pull in everything. If I just use the >={StartDate}’ it pulls in no data? Any help would be much appreciated as I have already tried everything everyone else has that I could find in forum with same result.

2

Seems like it might be working now that I used ? in place of the Parameter instead of the actual Parameter???

SELECT * FROM report_test WHERE t_stamp >= ? AND t_stamp < ?

Parameter 1 {StartDate}
Parameter 2 (EndDate}

Why will it work with ? but not the actual parameter being referenced???

3

The SQL query gets evaluated as a plain query, and doesn’t know anything about the expression language.

Your parameters to that query are full expressions, and therefore can reference report parameters, results of previous queries, tags, and just about any gateway scoped item that can be evaluated by the expression language. That expression is fully evaluated and then sent to the SQL query as a parameter.

If you really want to include the parameter directly in the query, you can use a Basic SQL Query instead, but you lose the safety of using a prepared statement.

4

[quote=“cire3621”]Why will it work with ? but not the actual parameter being referenced???[/quote]This is an artifact of date conversions to strings. When you use the {} notation in the query, the date object is converted to a string, which you must quote, and then converted back into a date in the database. In Ignition, you have no control of the date’s conversion to a string, and that can vary with platform and locale. Some databases are very strict about what may be in a string for it to be a valid date.
When you use an unquoted question mark in the query, and then supply parameters in the list below the query, the parameters (dates) are passed into the database with no string conversion. There’s no opportunity for locale or platform to interfere.
If you absolutely must use a date formatted into a string for a database, manually convert the date with the dateFormat() expression function (into another property), specifying date format codes that you are sure the database will accept. Then use the string property in your query.

5

Well that makes sense…thanks for taking the time to explain why I was getting undesired results when referencing the parameter directly instead of as a prepared statement. That will clear a lot of future headaches up while doing queries with the new report module now that I understand how it functions :thumb_left: .