QtSql: QSqlRecord doesn't keep the table prefixes

Question

I'm working on a small application in Qt and I'm using the SQL library together with SQLite as the database. So far this has worked out well. The QSqlQuery class only provides a method for getting column values by index instead of field name. However, using the record() function the current row on the query can be turned into a QSqlRecord, which has the value() function for getting values by field name.

So, this works out just fine and allows me to write shorter and cleaner code, but a join query like the following brings problems:

SELECT t1.*, t2.* FROM table1 AS t1, table2 AS t2 WHERE t1.table2_id=t2.id

So we execute this query as normal, and convert a row to a record. But turns out that the column names in the QSqlRecord aren't prefixed with the table name - for example, there are two columns called id to be found in the record object. This is obviously a bit problematic.

What's the best solution to this problem?

(I've found this issue in the Qt bug tracker, but it's not of much help.)

Answer 1

The field name is totally dependent on the driver; so the issue is really with SQLite itself, which might be doing the 'correct' thing after all (correct, as in SQL standards, which I haven't checked).

Anyway, easy solution: just give your fields an alias in the SQL itself, though, you can't, and really shouldn't, use select * from:

SELECT t1.id TI_ID, t1.name TI_NAME, t2.id T2_ID FROM table1 AS t1, table2 AS t2 WHERE t1.table2_id=t2.id