I am attempting to pull some temp and humidity data from a MySQL database. Right now the database table only contains data from 2 sensors, but we have more sensor though out our facility. I created a multi-selection dropdown list to select different sensors, that way the user can choose to view only the sensors they want to see. I also have 2 check boxes for selecting temp and humidity. Lastly I have 2 datetime pickers so the user can select a start and and end date. I created a stored procedure in MySQL workbench so that I could basically send it a string of column names and build the query in there. I can get everything to work except passing in the start and end datetime data types. The datetime picker values are bound to session custom properties that I created, and that is what I am trying to pass to a named query that is simply calling my stored procedure. I can't figure out what is the proper way to format the datetime data type from the datetime picker to a datetime format that MySQL is happy with.
You shouldn't be formatting (i.e. converting them to strings) your datetimes from the datetime picker components at all, and simply passing through the datetimes as native objects. The named query parameters should be set to DateTime types. Is that not working? (I haven't used SPs much before, and never with Named Queries)
The only time you should ever convert / format datetimes (into strings) is when displaying them for the user, and only ever at the last possible step. E.g. if displayed in a table, datetimes should never be converted in the SQL query, they should be converted by the table component itself, unless it's imperative for the query (you might want to group all date parts together for example which would require stripping the time - and even then you would still be converting it to a DATE type, not a string - or using a calculated field... I digress 
)
Also, you should be using the Wide DB Table Historian tool to query this history for you in Perspective.
Thanks for the responses, I appreciate any help here. I am not actually trying to convert anything. I am just trying to bind the datetime picker values to session custom properties, then sending the custom properties to the named query. I did try the DB Table Historian a little bit tonight, but I couldn't get it to pull any data into a power chart. It might have something to do with the fact that my time column name is datetime potentially. I don't really have any experience with the DB Table Historian, but I'd imagine there is a way to specify which column is the time stamp. I did find my issue tonight though. When this was not working I was able to copy the value from the date time pickers into the named query test and that worked so I didn't think the issue was with my stored procedure. Since I tried everything else though, tonight I changed the line of code that builds the SELECT statement I am using from:
SET @query = CONCAT('SELECT datetime,', InputColumns, ' FROM MEB_TempHumidity WHERE datetime BETWEEN ', StartDate, ' AND ', EndDate);
to the following:
SET @query = CONCAT('SELECT datetime,', InputColumns, ' FROM MEB_TempHumidity WHERE datetime BETWEEN ', '\'', StartDate, '\'', ' AND ', '\'', EndDate, '\'');
and it is working correctly by adding the single quotes before and after the start and end dates. I am still going to mess with the DB Table Historian to make that work too though.
- See Wiki - how to post code on this forum.
- Don't create query strings using CONCAT(). You are leaving your system open to SQL injection attack. Ideally you would use system.db.runNamedQuery | Ignition User Manual or system.db.runPrepQuery | Ignition User Manual.
My named query is just a call to MySQL stored procedure, but I could use system.db.runPrepQuery to do the same thing I am doing in my stored procedure, except I used the sp because I also wanted to check to make sure that all columns actually exist in the table before I query for something that doesn't exist. It makes sense then that I should be using the DB Table Historian then.
