System.db.runPrepUpdate() and difference in seconds between what I want to be saved and what is saved?

Full context just in case it matters:
I have a Form class that I am working on to cover all the forms in my application to simplify my code which themselves are all pretty simple.

Part of this is using a query builder I wrote that inserts the data into the appropriate database table. After insert, then the information is checked by selecting the newly inserted record from the database and comparing it to the dictionary I just used. Herein lies the problem - for a datetime field, what is inserted into the database is always 1 second higher than what I had in the dictionary I fed to system.db.runPrepUpdate. My checker function gives me notice that not everything inserted correctly and I manually verified what is in the database is not what was in the dictionary.

Quick example: Note - nothing on this window has any bindings or scripts

So datetime here is 2:43:48 PM. I click submit and in my console I have this printed

q: INSERT INTO testForm(amount, taxable, dateOfExecution, description, userId, name, dateTimeOfEx) VALUES (?,?,?,?,?,?,?)
v: [123.44999694824219, True, Tue Feb 09 00:00:00 EST 2021, u'lorem ipsim', 1112, u'test', Tue Feb 09 14:43:48 EST 2021]

Generated from this function

	columnInfo = db.util.getDBColumnInfo(tableName)
	dictKeys = dictionary.keys()
	uPrint("in insert function")
	if db.util.dictonaryTableCheckForInsert(columnInfo, dictKeys):
		columnNames = ', '.join([x for x in dictKeys])
		values = [x for x in dictionary.values()]
		questionMarks = ','.join(['?' for x in range(len(dictKeys))])
		realQuery = "INSERT INTO %s(%s) VALUES (%s)"%(tableName, columnNames, questionMarks)
		uPrint("q: " + str(realQuery))
		uPrint("v: " + str(values))
		newIdx = system.db.runPrepUpdate(realQuery, values, getKey = 1)
		result = check(dictionary, tableName, newIdx, sleep=sleep)
		
		if getKey == 1:
			return result,newIdx
		else:
			return result

As you can see I don’t modify any values, this is really just for auto creating queries for system.db.runPrepUpdate. But then my check() function runs and I see this in my console -

mismatched values for dateTimeOfEx
dbValue: Tue Feb 09 14:43:49 EST 2021
dictValue: Tue Feb 09 14:43:48 EST 2021
dbType: <type 'java.util.Date'>
dictType: <type 'java.util.Date'>

Where dbValue is what was just selected after insert. dictValue was the value grabbed from the dictionary and added to values list.

And in my Database, MySQL 5.6.50, I see
image

So it was saved at 1 second ahead of what I input into the system.db.runPrepUpdate function. Any ideas how this could possibly happen? I never had this issue before with these database functions. I am doing all this through a Class now however the class does not manipulate any data. I have attached my tiny project so you can try and see for yourself. I tried recreating this in script console but it seems to work properly there.

Table definition used by project:

CREATE TABLE testForm(
	idx int not null primary key auto_increment,
    name varchar(30) not null,
    userId int not null,
    description text,
    amount decimal(11,2) not null,
    taxable bool not null,
    dateOfExecution date not null,
    dateTimeOfEx datetime not null
);
CREATE UNIQUE INDEX name ON testForm(name);

FormsTest.zip (32.0 KB)

MySQL defaults to 1-second resolution on datetime columns, IIRC. Your dateTimeOfEx source data probably has milliseconds, which don’t print by default with java.util.Date. MySQL is probably rounding up on store.

Check you column precision.

Also, you should seriously consider upgrading to a modern version of MariaDB (the superior fork of MySQL).

2 Likes

I suspect you are right. i just made a custom value with the expression dateFormat({Root Container.cal_out_dateOfExecution_custDate.date},'yyyy-MM-dd HH:mm:ss') to truncate milliseconds, and that worked completely as expected.

A little odd though that the calendar component Date property doesn’t show milliseconds
image
nor can you select milliseconds on the calendar component (at least from a brand new one without modification), but it must have it.

You are quite the optimist, aren’t you? /:

1 Like

Well idk why else it wasn’t working before and is now! In my str() represetation of the java.util.Date I did not see any milliseconds listed as seen here v: [123.44999694824219, True, Tue Feb 09 00:00:00 EST 2021, u'lorem ipsim', 1112, u'test', Tue Feb 09 14:43:48 EST 2021], but obviously it MUST have right? RIGHT!?

I thought you were referring to the date input object. I suspect it initializes with milliseconds and they are along for the ride from that point. By optimist, I was referring to having any way to edit the milliseconds in the input object.

Perhaps my explanation wasn’t clear but all the items in the v list gathered from properties on components, so the java.util.Date object that this is all revolving around I grabbed off the Date property from the calendar component. I suspect you’re right though, when I first made the component, that’s the time that’s there as the default which I never switched or messed with, I’d guess it has 500 or more milliseconds (not shown) and that’s why every single insert was being rounded up until I did some formatting to the output value.