Re: Issues with QSqlQuery and inserting Timestamp data
I have been stuck on this issue fro the last two days. It is a simple syntax issue but all of my search haven't pointed me in a direction that makes since. Most of the examples and post talking about inserting the current time and the time I need to insert is in the past.
Qt Creator 3.6.0
Qt 5.5.1 (MSVC 2013, 32 bit)
using QSqlDatabase and QSqlQuery
I have a system that gathers data and time stamps it and writes it to a file. Later I pickup the file and put the data in to a database for others to process.
So here is an excerpt of the code. This is the main read loop
Code:
while(!fCallLog.atEnd()) {
nLineCount++;
sString = fCallLog.readLine();
sString.remove("\r\n");
myDebug.Output(DEBUG_MAXIMUM, "VDG3C_CA - Raw Call Record Data: " + sString);
sString.replace(",", ";");
sString.replace("\\;", ",");
sString.replace("\\\\", "\\");
slCallRecord = sString.split(";");
// Convert Unix time to Date Time string
sString = slCallRecord.at(13);
RecordTime.setTime_t(sString.toInt());
slCallRecord.replace(13, RecordTime.toString("dd-MM-yyyy h:mm:ss AP"));
if(!myDatabase.insertIntoTable(CALLRECORDSTABLE, SMDR_Fields, SMDR_Field_Types, slCallRecord))
myDebug.Output(DEBUG_MINIMUM, "VDG3C_CA - Insert Record Into Database - FAIL!");
else
myDebug.Output(DEBUG_MEDIUM, "VDG3C_CA - Insert Record Into Database - SUCCESS!");
The insertIntoTable finction:
Code:
{
bool bReturn = false;
int nTemp = 14;
debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - Start");
if((slFields.count() == slFieldValues.count()) && (slFields.count() == slFieldTypes.count())) {
sQuery = "INSERT INTO ";
sQuery.append(sTable);
sQuery.append(" ( ");
sQuery.append(slFields.at(0));
// for(int nI = 1 ; nI < slFields.count() ; nI++) {
for(int nI = 1 ; nI < nTemp ; nI++) {
sQuery.append(",");
sQuery.append(slFields.at(nI));
}
sQuery.append(" ) VALUES ( ");
// for(int nI = 0 ; nI < slFields.count() ; nI++) {
for(int nI = 0 ; nI < nTemp ; nI++) {
if(nI != 0)
sQuery.append(",");
if(slFieldTypes.at(nI) == FIELD_TYPE_TIME) {
sQuery.append("#'");
sQuery.append(slFieldValues.at(nI));
sQuery.append("'#");
}
else {
sQuery.append("'");
sQuery.append(slFieldValues.at(nI));
sQuery.append("'");
}
}
sQuery.append(" );");
debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Query\r\n"
" Query String: " + sQuery);
if(thisQuery.prepare(sQuery)) {
debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Prepare Query - !!!FAILED!!!\r\n"
" Reason: " + thisError.text());
}
else
debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Prepare Query - SUCCESS");
if(!(bReturn = thisQuery.exec(sQuery))) {
debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Insert into the Database - !!!FAILED!!!\r\n"
" Reason: " + thisError.text());
}
else
debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Insert into the Database - SUCCESS");
}
else
debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - slFields and slFieldValues counts do not match");
debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - Stop");
return bReturn;
}
Results of a test run
Quote:
2016-07-25 11:04:41.062 - VDG_Database - insertIntoTable - Start
2016-07-25 11:04:41.062 - VDG_Database - insertIntoTable - Query
Query String: INSERT INTO SMDR_Data ( Version,Call_ID,Orig_Name,Orig_Addr_Type,Orig_Addr _Value,Rec_Name,Rec_Addr_Type,Rec_Addr_Value,Inten d_Rec_Name,Intend_Rec_Addr_Type,Intend_Rec_Addr_Va lue,Outcome,Reason,Time ) VALUES ( '2','16777417','Gilbert, Victor 1','ext','1028','PSTN Trunk','pstn','16042792115','','','','0','1',#'05-10-2015 4:00:40 PM'# );
2016-07-25 11:04:41.065 - VDG_Database - insertIntoTable - Prepare Query - SUCCESS
2016-07-25 11:04:41.066 - VDG_Database - insertIntoTable - Insert into the Database - !!!FAILED!!!
Reason: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. QODBC3: Unable to execute statement
2016-07-25 11:04:41.067 - VDG_Database - insertIntoTable - Stop
I tried changing the to and the prepare doesn't like it.
Also I have tried several different date formats
Code:
slCallRecord.replace(13, RecordTime.toString("dd-MM-yyyy h:mm:ss AP"));
including:
Quote:
dd-MM-yyyy
MM-hh-yyyy
yyyy-MM-dd
dd/MM/yyyy
mm/dd/yyyy
yyyy/MM/dd
Any help would be appreciated.
Victor
Added after 4 minutes:
Sorry I meant to add that this is using the ODBC connector in the an MS Access Database.
Victor
Re: Issues with QSqlQuery and inserting Timestamp data
I don't use MS Access, but I see the last column name you're trying to insert is named "Time". My guess is that this may be a reserved keyword to access. Can you try again square brackets around the column name, i.e. [Time]?
While I can't vouch for it personally, I googled that people recommend that the DateTime values inserted into an Access Db are of the following format: YYYY-MM-DD HH:NN:SS (4-digit year, 2-digit month, 2-digit day, 2-digit hour, 2-digit minute, 2-digit second).
Good luck.
Re: Issues with QSqlQuery and inserting Timestamp data
Thank you so much.
That must have been it (the field time).
Since I am creating the Access DB I changed the field name and put the time format the the suggested format and it now works.:)
Victor
