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
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)
