Inconsistent date types from MySQL Database in Perspective?

Ignition
1

I’m having a very difficult time figuring this out. In perspective, we have a function that after insert/update, we check that it worked. We do this based on type and values. With dates I like to use the native java.util.date that we get from date columns in MySQL and compare them to the java.util.date objects I get from date components.

I am getting an error from my function anytime I try to update a date column via perspective now though in my chrome console about mismatched types between the db and what I fed it -
image

So in my select statement that’s running to check if the update worked, SELECT lastStartDateSelected FROM listusers WHERE idx = 3047. Here’s the short script I use to get the value/type for my check -

selectQuery = "SELECT %s FROM %s WHERE idx = %i"%(columnNames, tableName, idx)
	system.perspective.print("SELECT QUERY: " + str(selectQuery))
	results = system.dataset.toPyDataSet(system.db.runQuery(selectQuery))
	badResult = False
	for key in dictionary.keys():
		databaseValue = results[0][str(key)]
		dictionaryValue = dictionary[key]
		system.perspective.print("db Value: " + str(databaseValue))
		system.perspective.print("db Type: " + str(type(databaseValue)))
		system.perspective.print("dict value: " + str(dictionaryValue))
		system.perspective.print("dict type: " + str(type(dictionaryValue)))

Now here is the odd thing, running the very same select statement in the scripting console and doing type on it gives me a java.util.date which is what I expect, but for some reason, perspective calling this function and running the same exact query returns a java.sql.timestamp which ruins my comparisons. Any idea why this is and if it can be changed?

2

java.sql.Timestamp is a subclass of java.util.Date - it should work the same for comparison purposes:
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Timestamp.html

If you specifically need a date, you could convert it back pretty easily; check if isinstance(java.sql.Timestamp), then convert it via system.date.fromMillis(timestamp.time)

1 Like
3

OK this does work. I know with Perspective all the scripts run on the gateway - is that what causes it to be a different type? Some different logic in the gateway with handling a date column vs running the same query via a client? This really threw me for a loop for a bit lol.

4

Yeah, we’re probably serializing to a Date directly before sending the results down to the client, but on the gateway you’re getting them directly.

1 Like