QtSql: QSqlRecord does not support table prefix

Question

I am working on a small application in Qt, and I use the SQL library along with SQLite as a database. So far, it has worked out well. The QSqlQuery class provides only a method for retrieving the values ​​of an index column instead of a field name . However, using the record() function, the current query string can be turned into QSqlRecord , which has a value() function to get values ​​by the field name.

So this works very well and allows me to write shorter and cleaner code, but a connection request such as the following causes problems:

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

So, we execute this request as usual and convert the string to a record. But it turns out that the column names in QSqlRecord do not have a prefix with the table name - for example, there are two columns named id in the record object. This is obviously a bit problematic.

What is the best solution to this problem?

(I found this problem in the Qt error debugger, but that doesn't help much.)

Answer 1

The field name is completely dependent on the driver; therefore, the problem is really related to SQLite itself, which, perhaps, does the “right” thing (correctly, as in SQL standards that I have not tested).

In any case, a simple solution: just give your fields an alias in SQL itself, although you cannot and should not 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 

Answer 2

I also found this problem, but found that if you use QSqlQueryModel to create a QSqlRecord, it works fine:

 QSqlQueryModel *model = new QSqlQueryModel; model->setQuery("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"); int TI_ID = model->record(0).value("TI_ID").toInt(); int TI_ID = model->record(0).value(0).toInt(); QString TI_NAME = model->record(0).value("TI_NAME").toString(); QString TI_NAME_from_id= model->record(0).value(1).toString(); 

You should get the same values ​​for the name and the name using both styles.