Friday, February 1, 2008

Accessing MS SQL UID-fields with Qt

When working with a database that relies heavily on uniqueidentifiers, I experienced problems with handling those fields with Qt's built-in SQL-classes.
First, I connect to the database via the QODBC-driver. Then I fetch the results of table 'a' and tried to fetch the corresponding results in table 'b', which are referenced by foreign keys. Here's a code-snippet:

QSqlQuery a(db);
a.exec("select id from a");
a.next();
QSqlQuery b(db);
b.prepare("select id from b where b.id_a=:id");
while(a.isValid())
{
b.bindValue(":id",a.value(0));
b.exec();
// ERROR: Operand type clash: image is incompatible with uniqueidentifier
a.next();
}

So Qt converts the binary data it received from the uniqueidentifier to a binary blob of type image, it seems.
There's a simple way to convert the GUID that is stored in a.value(0) to a formatted UID-string, which in turn can be used to bind the value of the second query.
   QString uuidToString(const QVariant &v)
{
// get pointer to raw data
QByteArray arr(v.toByteArray());
std::string result(arr.constData(),arr.size());
assert(result.size() == 16);
const char *ptr = result.data();
// extract the GUID-parts from the data
uint data1 = *reinterpret_cast<const uint*>(ptr);
ushort data2 = *reinterpret_cast<const ushort*>(ptr+=sizeof(uint));
ushort data3 = *reinterpret_cast<const ushort*>(ptr+=sizeof(ushort));
uchar data4[8] =
{
*reinterpret_cast<const uchar*>(ptr+=sizeof(ushort)),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr),
*reinterpret_cast<const uchar*>(++ptr)
};
// create a uuid from the extracted parts
QUuid uuid(
data1,
data2,
data3,
data4[0],
data4[1],
data4[2],
data4[3],
data4[4],
data4[5],
data4[6],
data4[7]);
// finally return the uuid as a QString
return uuid.toString();
}

Using this function, you can easily bind the values to the second query:
b.bindValue(uuidToString(a.value(0)));

Edit:Starting from Qt 4.4.0 (I used the latest snapshot) QVariant supports GUIDs and hence this function fails AND is unneccessary.

1 comment:

  1. I fixed an issue that let uuidToString to fail when the UID contains 0-chars. I thought that QVariant::toString() worked for strings containing 0-chars, but it does not. I'm using QByteArray now, which is much better anyways.

    ReplyDelete