Converting a varchar data type to a datetime data type created an out of range value

Ignition
1

I am positive that this issue is related to SQL but still wanted to see if someone experienced something similar.
I am writing very simple queries dynamically from a template.
For example :

image
image910×65 6.6 KB

But when it executes it throws following error:
image
image1200×202 20.2 KB

Sorry its in French which translates to :

UPDATE lab_test_compound_specification SET date_edited = '2020-04-22 19: 31: 48.930', maximum = 1.55 WHERE id = 4 SQL error for "UPDATE lab_test_compound_specification SET date_edited = '2020-04-22 19: 31: 48.930', maximum = 1.55 WHERE id = 4 ": Converting a varchar data type to a datetime data type created an out of range value.

One thing I want to mention : The server is located in France and I am located in North America(Canada in particular)

Any comments will be appreciated.

Thanks

2

This kind of error is common when relying on string construction of queries. You really should be using parameterization so the timestamp is transferred from Ignition to your DB without conversion to a string. This is done with a “Prep” query or a named query. The former would look like:

UPDATE lab_test_compound_specification
  SET date_edited = ?, maximum = ?
  WHERE id = ?

The latter would look like:

UPDATE lab_test_compound_specification
  SET date_edited = :DateParm, maximum = :MaxParm
  WHERE id = :IdParm

In both cases, the values for the query are passed natively in JDBC so that locale isn’t an issue. This also protects you from SQL injection security issues.

3

@pturmel, It is actually done using Prep query ! I showed an example of Database browser query to elaborate it simply.

Here’s an actual Error :

Traceback (most recent call last):
  File "<event:actionPerformed>", line 5, in <module>
  File "<module:shared.common.Client>", line 412, in saveChanges
  File "<module:shared.mixing.LabTesting>", line 30, in saveChangesToDB
  File "<module:shared.common.Client>", line 442, in saveChangesToDB
  File "<module:shared.common.ObjectToDBMapping>", line 489, in updateRow
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate( UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?, [2020-04-22 19:31:48.930, 1.75, 4], sql_lilvsql16, , false, false)

	caused by Exception: Error executing system.db.runPrepUpdate( UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?, [2020-04-22 19:31:48.930, 1.75, 4], sql_lilvsql16, , false, false)
	caused by GatewayException: SQL error for " UPDATE lab_test_compound_specification SET date_edited= ?, maximum= ? WHERE id = ?": La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.
	caused by SQLServerException: La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.

Ignition v8.0.10 (b2020031912)
Java: Azul Systems, Inc. 11.0.6
4

That suggests your timestamp supplied to the query is already a string. You need to supply a java.util.Date or a java.sql.Timestamp. Please show the code that calls the query.

5

@pturmel, this is how it looks like :

 WITH result_set AS
	( SELECT DISTINCT
			c.compound_name as compound,
			c.id as compound_id,
			lton.order_number as Order#,
			RIGHT(lt.batch_number, 3) as batch_number,
			COALESCE(CAST(mbh.start_time as varchar), '-') as 'date_mixed',
			COALESCE(lt.mixer_batch_history_id, -1) as mbh_id,
			ltn.id as test_id,
			DENSE_RANK() OVER(PARTITION BY lt.batch_number, ltn.id ORDER BY lt.date_created) AS 'test_number',
			CASE 
				WHEN ltr.value IS NULL THEN '-'
				WHEN ltr.pass_customer_specs = 0 THEN '*' + cast( ltr.value as nvarchar(10))
				ELSE  cast( ltr.value as nvarchar(10) )
			END as 'value',
			lt.pass_customer_specs,
			lt.id as lab_test_id,
			lt.date_created as date_tested
		FROM 
			measurement m	
			JOIN lab_test_measurement ltn ON ltn.measurement_id = m.id
			JOIN lab_test_result ltr ON ltr.lab_test_measurement_id = ltn.id
			JOIN lab_test lt ON lt.id = ltr.lab_test_id
			JOIN lab_test_order_number lton ON lton.lab_test_id = lt.id
			JOIN compound c ON c.id = (SELECT compound_id from lab_test_compound WHERE id = lt.lab_test_compound_id)
			JOIN compound_process cp ON cp.compound_id = c.id AND cp.mixing_line_id = (SELECT id FROM mixing_line WHERE line_number =  :line_number )
			LEFT JOIN mixer_batch_history mbh ON mbh.id = lt.mixer_batch_history_id
			
		  WHERE lt.date_created between '2020-03-01 00:00:00' AND '2020-03-31 23:59:59'
	)
SELECT DISTINCT
	compound,
	compound_id,
	order#,
	batch_number, 
	date_mixed,
	test_number,
	COALESCE([1], '-') as 'ml', 
	COALESCE([2], '-') as 'mh', 
	COALESCE([3], '-') as 'ts1',
	COALESCE([4], '-') as 'ts2',
	COALESCE([5], '-') as 't10',
	COALESCE([6], '-') as 't50',
	COALESCE([7], '-') as 't90', 
	COALESCE([8], '-') as 'mlmin',  
	COALESCE([10], '-') as 'ml1p4', 
	COALESCE([11], '-') as 't5',
	COALESCE([12], '-') as 't35 (mv)',
	COALESCE([13], '-') as 'Hardness',
	COALESCE([14], '-') as 'SPG',
	CASE 	pass_customer_specs
		WHEN 0 THEN 'Fail' 
		ELSE 'Pass'
	END AS 'Pass?',
	date_tested
FROM	( SELECT * FROM result_set ) p
PIVOT
	( MIN (value)
	  FOR	test_id in ([1], [2], [3], [4], [5], [6], [7], [8], [10], [11], [12], [13], [14])
	) AS pvt
ORDER BY date_tested desc, compound, batch_number, test_number
6

That’s not what I asked for. Please show the jython that calls the query, along with the code leading to it that obtains and packages the parameters.

7

I see.

It is component property change script looks like -

if (event.propertyName == 'dateChanged' and event.newValue == 1) or event.propertyName == 'where_clause':
	event.source.refresh = 1

if event.propertyName == 'refresh' and event.newValue == 1:
	event.source.refresh = 0
	
	start_date = event.source.getComponent('DateTimeMonthSelection').startDate
	end_date = event.source.getComponent('DateTimeMonthSelection').endDate
	where_clause = event.source.where_clause
	
	print where_clause
	line_number = system.tag.read("LineNumber").value
	
	# View by mix date and all test results (pass and fail)
	if event.source.getComponent('PassFail').getComponent('PassedAndFailed').selected == 1:
		
		table = event.source.parent.parent.getComponent('PassedAndFailed')
		
		results = system.db.runNamedQuery('LabTesting/LabTestExtensive_1', {'clause':where_clause, 'line_number':line_number})
		table.data = results

Here, where clause is coming from custom property which has expression binding as below -

// Date filter
Root Container.Filter.FilterContainer.DateType.selected},
	" WHERE mbh.start_time >= '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.startDate} + "' AND mbh.end_time <= '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.endDate} + "'",
	" WHERE lt.date_created between '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.startDate} + "' AND '" + {Root Container.Filter.FilterContainer.DateTimeMonthSelection.endDate} + "'") +
	
// Last test and pass only
if( {Root Container.Filter.FilterContainer.PassFail.LastTestPassedOnly.selected},
	' AND lt.pass_customer_specs != 0',
	'') + 
	
if( {Root Container.Filter.FilterContainer.PassFail.BatchSummary.selected},
	' AND ltr.value != 0',
	'')

Since, we are focused on the DateTimeMonthSelection Component, it is a template which toggles date as selected,

Example -
This is what I would get if I select March -

from java.util import Calendar

# Set the start date 
cal = Calendar.getInstance()

cal.set( Calendar.MONTH, Calendar.MARCH )
cal.set( Calendar.DAY_OF_MONTH, 1 )
cal.set( Calendar.HOUR_OF_DAY, 0 )
cal.set( Calendar.MINUTE, 0 )
cal.set( Calendar.SECOND, 0 )

if cal.getTime() > Calendar.getInstance().getTime() :
	cal.set( Calendar.YEAR, cal.get(Calendar.YEAR) - 1 )
	
event.source.parent.getComponent('startDate').date = cal.getTime()

# Set the end date 
cal.set( Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH) )
cal.set( Calendar.HOUR_OF_DAY, 23 )
cal.set( Calendar.MINUTE, 59 )
cal.set( Calendar.SECOND, 59 )

event.source.parent.getComponent('endDate').date = cal.getTime()
8

As I suspected, you are converting the dates to strings to supply in your where clause. Don’t do that. Use two different named queries if you need to, but pass the dates themselves in their own parameters (datetime parameters). Don’t convert them to strings.

9

I see. One thing I don’t get - Why does this issue happen ? We didn’t had this issue in 7.8 or 7.9
Is it because Ignition 8 ? Since, this is being used at 1000 different places, what is the efficient way of doing it ?

10

Date/time strings are inherently locale-dependent and time zone dependent. Java’s internal format is UTC milliseconds, converted to/from local time wherever needed, and converted to/from strings using the locale. This was also true in earlier versions of Ignition, but less noticeable because Vision clients are also java, and the serialization of the java date/time values between client and gateway preserves UTC milliseconds. Perspective has to deal with JSON and javascript, which doesn’t even have a native date/time datatype. If you encode timestamps as strings for in both directions, with different timezones or locales, you have a nightmare. If you send long integer milliseconds (like the t_stamp column of Ignition’s tag historian), you get time zone independence, but have to post-process into local formats in the browser. Which is solely performed by IA code, or by third-party module code, not by jython.

11

@pturmel, I think that’s beyond my tiny brain. Do you have a quick example where I can understand datetime transfer between Ignition and SQL ?

12

Just don’t convert timestamps to strings. Use a colon-prefixed parameter for your timestamps in your named queries, putting at least the beginning of your WHERE clause in the named query. Supply the raw datetime datatype to your named query. Leave the clause parameter to deliver just the ANDed parts, if any. You’ll need separate named queries for the two different timestamp WHERE clause structures.

{ Don’t use an expression binding for your WHERE clause–just construct it in jython prior to calling system.db.runNamedQuery(). }

13

Phil, I am doing the way you told. Which seems to be working fine except I am getting and error with missing value parameter for the clause.

For example :
Here the clause is nothing.

image
image761×241 7.29 KB

at throws an error as :

image
image1762×189 25.1 KB

For temporary, I am using ’ ’ and concatenating other where clauses to the space but I thought passing nothing was acceptable as of 7.9.x

14

No, starting with an empty string and passing that if neither optional clause is selected is the correct answer.

15

Okay, thanks !

16

Hello, I solved the error in object of this thread directly in SQL query, something like this:CONVERT(DATETIME, ‘2019-08-15’, 102), where in ‘2019-08-15’ you replace the property text of popup calendar.

17

That you can do this for your case sounds good, but is not a generally good solution. It won't be much help when multiple time zones are (or will be) involved, nor can it handle the formatting problems when client and gateway have different locales. Passing data via unambiguous typed binary parameters (JDBC in Ignition) is always best.