Results 1 to 3 of 3

Thread: Issues with QSqlQuery and inserting Timestamp data

  1. #1
    Join Date
    Oct 2009
    Location
    Vancouver, Canada
    Posts
    2
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Windows

    Default 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
    Qt Code:
    1. while(!fCallLog.atEnd()) {
    2. nLineCount++;
    3. sString = fCallLog.readLine();
    4. sString.remove("\r\n");
    5. myDebug.Output(DEBUG_MAXIMUM, "VDG3C_CA - Raw Call Record Data: " + sString);
    6. sString.replace(",", ";");
    7. sString.replace("\\;", ",");
    8. sString.replace("\\\\", "\\");
    9. slCallRecord = sString.split(";");
    10.  
    11. // Convert Unix time to Date Time string
    12. sString = slCallRecord.at(13);
    13. QDateTime RecordTime;
    14. RecordTime.setTime_t(sString.toInt());
    15. slCallRecord.replace(13, RecordTime.toString("dd-MM-yyyy h:mm:ss AP"));
    16.  
    17. if(!myDatabase.insertIntoTable(CALLRECORDSTABLE, SMDR_Fields, SMDR_Field_Types, slCallRecord))
    18. myDebug.Output(DEBUG_MINIMUM, "VDG3C_CA - Insert Record Into Database - FAIL!");
    19. else
    20. myDebug.Output(DEBUG_MEDIUM, "VDG3C_CA - Insert Record Into Database - SUCCESS!");
    To copy to clipboard, switch view to plain text mode 

    The insertIntoTable finction:
    Qt Code:
    1. bool VDG_Databsse::insertIntoTable(QString sTable, QStringList slFields, QStringList slFieldTypes, QStringList slFieldValues)
    2. {
    3. bool bReturn = false;
    4.  
    5. int nTemp = 14;
    6.  
    7. QSqlQuery thisQuery(myDB);
    8.  
    9. QString sQuery;
    10.  
    11. debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - Start");
    12. if((slFields.count() == slFieldValues.count()) && (slFields.count() == slFieldTypes.count())) {
    13. sQuery = "INSERT INTO ";
    14. sQuery.append(sTable);
    15. sQuery.append(" ( ");
    16. sQuery.append(slFields.at(0));
    17. // for(int nI = 1 ; nI < slFields.count() ; nI++) {
    18. for(int nI = 1 ; nI < nTemp ; nI++) {
    19. sQuery.append(",");
    20. sQuery.append(slFields.at(nI));
    21. }
    22. sQuery.append(" ) VALUES ( ");
    23. // for(int nI = 0 ; nI < slFields.count() ; nI++) {
    24. for(int nI = 0 ; nI < nTemp ; nI++) {
    25. if(nI != 0)
    26. sQuery.append(",");
    27. if(slFieldTypes.at(nI) == FIELD_TYPE_TIME) {
    28. sQuery.append("#'");
    29. sQuery.append(slFieldValues.at(nI));
    30. sQuery.append("'#");
    31. }
    32. else {
    33. sQuery.append("'");
    34. sQuery.append(slFieldValues.at(nI));
    35. sQuery.append("'");
    36. }
    37. }
    38. sQuery.append(" );");
    39. debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Query\r\n"
    40. " Query String: " + sQuery);
    41. if(thisQuery.prepare(sQuery)) {
    42. QSqlError thisError = thisQuery.lastError();
    43. debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Prepare Query - !!!FAILED!!!\r\n"
    44. " Reason: " + thisError.text());
    45. }
    46. else
    47. debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Prepare Query - SUCCESS");
    48.  
    49. if(!(bReturn = thisQuery.exec(sQuery))) {
    50. QSqlError thisError = thisQuery.lastError();
    51. debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Insert into the Database - !!!FAILED!!!\r\n"
    52. " Reason: " + thisError.text());
    53. }
    54. else
    55. debugOutput(DEBUG_MAXIMUM, "VDG_Database - insertIntoTable - Insert into the Database - SUCCESS");
    56. }
    57. else
    58. debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - slFields and slFieldValues counts do not match");
    59.  
    60. debugOutput(DEBUG_MEDIUM, "VDG_Database - insertIntoTable - Stop");
    61. return bReturn;
    62. }
    To copy to clipboard, switch view to plain text mode 

    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
    Qt Code:
    1. slCallRecord.replace(13, RecordTime.toString("dd-MM-yyyy h:mm:ss AP"));
    To copy to clipboard, switch view to plain text mode 
    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
    Last edited by VDG; 26th July 2016 at 00:21.

  2. #2
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default 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.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!

  3. #3
    Join Date
    Oct 2009
    Location
    Vancouver, Canada
    Posts
    2
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Windows

    Default 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

Similar Threads

  1. Replies: 5
    Last Post: 26th April 2016, 19:59
  2. Replies: 1
    Last Post: 18th July 2011, 13:12
  3. Strange issues with QSqlQuery
    By SIFE in forum Qt Programming
    Replies: 5
    Last Post: 17th May 2011, 11:51
  4. Replies: 2
    Last Post: 25th January 2011, 11:17
  5. QTableWidget - inserting data
    By Tomasz in forum Newbie
    Replies: 1
    Last Post: 29th September 2010, 15:32

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.