Results 1 to 7 of 7

Thread: How to update BLOB field in a SQLite database ?

  1. #1
    Join Date
    Apr 2011
    Posts
    8
    Thanks
    2
    Qt products
    Qt3 Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Question How to update BLOB field in a SQLite database ?

    Hello,

    I would like to update a BLOB field in my database with a QByteArray, but it doesn't work. Can you help me ?

    Ma database structure is :

    CREATE TABLE Settings (
    Version nvarchar(10),
    Geometry BLOB
    );

    This database have 1 row :

    INSERT INTO Settings VALUES (
    '0.1',
    ''
    );

    and I would like to update this row with the following source code :

    // connection :
    < database connection code >

    // update :
    QByteArray a_value;
    a_value = saveGeometry(); // saveGeometry return a QByteArray

    QSqlQuery query;
    query.prepare("UPDATE Settings SET Geometry = ':Geometry'");
    query.bindValue(":Geometry", a_value);
    if (query.exec() == false)
    qDebug() << "Error SQL exec()";


    The result is : Error SQL exec()

    Thanks

  2. #2
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to update BLOB field in a SQLite database ?

    if you pass query.lastError().text() to qDebug() you will get the specific error info

  3. #3
    Join Date
    Sep 2009
    Location
    Wroclaw, Poland
    Posts
    1,394
    Thanked 342 Times in 324 Posts
    Qt products
    Qt4 Qt5
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: How to update BLOB field in a SQLite database ?

    Try without the quotes:
    Qt Code:
    1. query.prepare("UPDATE Settings SET Geometry = :Geometry");
    To copy to clipboard, switch view to plain text mode 

  4. The following user says thank you to stampede for this useful post:

    aircraftstories (8th April 2011)

  5. #4
    Join Date
    Apr 2011
    Posts
    8
    Thanks
    2
    Qt products
    Qt3 Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: How to update BLOB field in a SQLite database ?

    Quote Originally Posted by stampede View Post
    Try without the quotes:
    Perfect, it works now. Thank you.

    The next problem is that the QByteArray returned by the function SaveGeometry() can have the following values :

    QByteArray a_value;
    a_value = saveGeometry();
    a_value[0] = 79
    a_value[1] = 28
    a_value[2] = 41
    a_value[3] = 00
    a_value[4] = 27
    .......
    a_value[X] = YY

    And in my BLOB cell only "79 28 41" is stored. the value "a_value[3] = 00" is considered as a '\0' end of string .... any idea to store the complete QByteArray ?

    Thanks
    Last edited by aircraftstories; 8th April 2011 at 14:36.

  6. #5
    Join Date
    Sep 2009
    Location
    Wroclaw, Poland
    Posts
    1,394
    Thanked 342 Times in 324 Posts
    Qt products
    Qt4 Qt5
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: How to update BLOB field in a SQLite database ?

    Show the code where you save and read from db. This works ok:
    Qt Code:
    1. #include <QApplication>
    2. #include <QtCore>
    3. #include <QtSql>
    4.  
    5. int main(int argc, char *argv[])
    6. {
    7. QApplication a(argc, argv);
    8. QByteArray bytes;
    9. bytes.resize(6);
    10. bytes[0] = 79;
    11. bytes[1] = 28;
    12. bytes[2] = 41;
    13. bytes[3] = 00;
    14. bytes[4] = 27;
    15. bytes[5] = 11;
    16.  
    17. qDebug() << "bytes size: " << bytes.size();
    18.  
    19. QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    20. db.setDatabaseName("testdb");
    21. db.open();
    22. QSqlQuery query(db);
    23. query.exec("DROP TABLE test;");
    24. query.exec("CREATE TABLE test( data BLOB );");
    25. query.prepare("INSERT INTO test VALUES(:data);");
    26. query.bindValue(":data",bytes);
    27. query.exec();
    28.  
    29. query.exec("SELECT data FROM test;");
    30. if( query.next() ){
    31. QByteArray data = query.value(0).toByteArray();
    32. qDebug() << "from db size: " << data.size();
    33. qDebug() << "equal?" << (data==bytes);
    34. }
    35.  
    36. return 0;
    37. }
    To copy to clipboard, switch view to plain text mode 
    Output:
    bytes size: 6
    from db size: 6
    equal? true

  7. The following user says thank you to stampede for this useful post:

    aircraftstories (8th April 2011)

  8. #6
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: How to update BLOB field in a SQLite database ?

    Are you sure? How are you checking that? Maybe the program your are viewing the blob with does interpret it that way. Try reading it back from the db the same way and dump the bytearray to your console.

    Ah: stampede was faster!

    Joh

  9. #7
    Join Date
    Apr 2011
    Posts
    8
    Thanks
    2
    Qt products
    Qt3 Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: How to update BLOB field in a SQLite database ?

    Quote Originally Posted by JohannesMunk View Post
    Are you sure? How are you checking that? Maybe the program your are viewing the blob with does interpret it that way.
    Thank you guys ! it was the problem. Now, all works.

Similar Threads

  1. Help with QT, SQLite, Update Statement
    By chetu1984 in forum Newbie
    Replies: 3
    Last Post: 17th March 2011, 23:24
  2. SQLITE - UPDATE query problem
    By Tomasz in forum Newbie
    Replies: 12
    Last Post: 5th September 2010, 21:27
  3. Replies: 2
    Last Post: 8th April 2010, 17:16
  4. Replies: 2
    Last Post: 17th February 2010, 15:32
  5. Replies: 1
    Last Post: 14th September 2009, 09:48

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.