Issues with QSqlQuery and inserting Timestamp data

[VDG]

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


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);
QDateTime RecordTime;
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:


bool VDG_Databsse::insertIntoTable(QString sTable, QStringList slFields, QStringList slFieldTypes, QStringList slFieldValues)
{
bool bReturn = false;

int nTemp = 14;

QSqlQuery thisQuery(myDB);

QString sQuery;

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)) {
QSqlError thisError = thisQuery.lastError();
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))) {
QSqlError thisError = thisQuery.lastError();
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


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
#' date '# to
# date # and the prepare doesn't like it.

Also I have tried several different date formats

slCallRecord.replace(13, RecordTime.toString("dd-MM-yyyy h:mm:ss AP"));
including:


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

[jefftee]

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.

[VDG]

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