QSQLITE driver behaves strange with QByteArray Data

Wurgl

I have here a very simple example to reproduce this strange behaviour.

#include <QtCore/QtCore>
#include <QtSql/QtSql>

int main(int argc, char *argv[])
{
  QCoreApplication a(argc, argv);

  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName("abc.sqlite");
  bool ok = db.open();
  qDebug() << "open: status = " << ok;

  QSqlQuery q;
  ok = q.exec("CREATE TABLE tbl (i INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, t TEXT)");
  qDebug() << "create table: status = " << ok;

  ok = q.prepare("INSERT INTO tbl (t) VALUES(:t)");
  qDebug() << "prepare: status = " << ok;

  q.bindValue(":t", QString("string 1"));
  ok = q.exec();
  qDebug() << "insert 'string 1': status = " << ok;

  q.bindValue(":t", QByteArray("bytes 1"));
  ok = q.exec();
  qDebug() << "insert 'bytes 1': status = " << ok;

  db.close();

  return 0;
}

Just compile it and run it once.

Then I did the following.

sqlite3 --header abc.sqlite 
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> select * from tbl;
i|t
1|string 1
2|bytes 1
sqlite> select * from tbl where t = "string 1";
i|t
1|string 1
sqlite> select * from tbl where t = "bytes 1";
sqlite>

As you can see, I can retrieve the column with the text-value "string 1", but I cannot do it with "bytes 1".

However, this here works?

sqlite> select * from tbl where t like "bytes 1";
i|t
2|bytes 1

So somehow a QByteArray seems to handle the data in some magic different way.

I did not find anything in the documentation, so I do not know if this is wanted behaviour or if it tends to be a bug.

What do you think?

Yes: Workaround is to use QString and avoid QByteArray …

VRonin

I think the trick is in how SQL driver treats QVariant types. QByteArray is used to save raw binary data (an image for example) and I think in SQLite it gets converted to a type BLOB hence the misunderstanding

Wurgl

@VRonin I agree. And Binary data works fine.

The problem is some magic invisible whatever which I not even see on the command line interface.

The other problem which I did not mention, is that the same query as on the command line does not work from within the program (as far as I use QString in the query, did not test with QByteArray).

And when one has a large amount of records, (for historical reasons) some are entered with QByteArray and some with QString I do not really know a way to identify such a row, my eyes do not see a difference, a hex-editor does not show a difference in the command line output too. So one needs to inspect every single row of every table ...

VRonin

I think the big difference between the two is \0

from http://doc.qt.io/qt-5/qbytearray.html#QByteArray-1:

The terminating nul-character is not considered part of the byte array.

So checking if QString is used the null char is appended while for QByteArray it is not

kshegunov

@Wurgl
Does

q.bindValue(":t", QVariant::fromValue<QString>(QByteArray("bytes 1")));

work as expected?

kshegunov

@VRonin said in QSQLITE driver behaves strange with QByteArray Data:

So checking if QString is used the null char is appended while for QByteArray it is not

It is. The byte array will keep a zero at the end. That's why QLatin1String is practically equivalent to QByteArray.
Here's the constructor:

class QLatin1String
{
public:
    // ...
    inline explicit QLatin1String(const QByteArray &s) Q_DECL_NOTHROW
        : m_size(int(qstrnlen(s.constData(), s.size()))), m_data(s.constData())
    {
    }
    // ...
};

Wurgl

@VRonin

I added the following lines at the end …

  ok = q.exec("SELECT t from tbl");
  qDebug() << "select: status = " << ok;

  while(q.next()) {
    qDebug() << "data: " << q.record().value(0);
  }

and got this output:

select: status =  true
data:  QVariant(QString, "string 1")
data:  QVariant(QByteArray, "bytes 1")

So the "magic" is recognized somehow.

@kshegunov
Even q.bindValue(":t", QString(QByteArray("bytes 1"))); works fine. Thats what I used to fix the problem … after starring at the code for a few hours

VRonin

@kshegunov But it probably doesn't serialise the null char. If you QDataStream to a QByteArray some binary data (not a string) and then save it to the DB, will it have the 0 at the end?

kshegunov

@VRonin said in QSQLITE driver behaves strange with QByteArray Data:

But it probably doesn't serialise the null char.

Neither serializes the null char. The only real difference is that QString keeps the data in utf16, while QByteArray is latin1 (ascii).

If you QDataStream to a QByteArray some binary data (not a string) and then save it to the DB, will it have the 0 at the end?

Nope.

kshegunov

@Wurgl
You shouldn't use QByteArray for strings to begin with. At least use QLatin1String instead of the byte array. Probably (speculating here) the driver doesn't do the appropriate conversion because you pass it "binary" data.