Qt-interest Archive, January 2007
Multiple databases with sqlite
Message 1 in thread
Hi all,
I am trying to arrange for a query to run across two separate sqlite3
databases using Qt 4.2.1. I've not been able to work out a Qt way of doing
this with the higher level classes because they seem to be tied to a single
database. So, I've been trying to use QSqlQuery and ATTACH DATABASE to work
around this.
MY sql works outside Qt quite happily as long as the databases both have the
same encoding (UTF-16le in this case). It looks like this:
[in sqlite.......after invoking as sqlite3 network.db]
ATTACH DATABASE my.db as iso;
sqlite> .database
0 main path/network.db
2 iso path/my.db
And then..(with sites a table in network.db)
Select sites.ID, sites.country, iso.geoscore.ScoreCode
From sites left join iso.geoscore
On sites.country = iso.geoscore.Country;
In Qt this reports that the attach operation is ok ( via SQLEror(-1, "", "") )
but it fails with "SQlError(1 , Unable to execute statement, no such table:
iso.geoscore)."
Am I trying to do something that the Qt driver doesn't support? Can anyone
recommend alternative strategies?
Regards,
Amit
--
[ signature omitted ]
Message 2 in thread
> Select sites.ID, sites.country, iso.geoscore.ScoreCode
> From sites left join iso.geoscore
> On sites.country = iso.geoscore.Country;
>
> In Qt this reports that the attach operation is ok ( via SQLEror(-1, "", "") )
> but it fails with "SQlError(1 , Unable to execute statement, no such table:
> iso.geoscore)."
Did you try giving iso.geoscore an alias and using that?
select ..., ig.ScoreCode
from ... iso.geoscore ig
...
Martin
--
[ signature omitted ]
Message 3 in thread
On Monday 15 January 2007 12:25, Martin Gebert wrote:
Martin,
thanks for the idea. In fact, it now works with the same SQL as before but
with an accurate pathname for the database (finger trouble). The ATTACH
command appears to succeed when you lie about the database location !
The Select statement seems to me to be more reliable in its error reporting.
Summary:
You can perform queries across multiple databases with sqlite using the ATTACH
DATABASE technique, but don't rely on QSqlError().lastError() to report on
whether the app found the database you meant it to use.
A
> > Select sites.ID, sites.country, iso.geoscore.ScoreCode
> > From sites left join iso.geoscore
> > On sites.country = iso.geoscore.Country;
> >
> > In Qt this reports that the attach operation is ok ( via SQLEror(-1, "",
> > "") ) but it fails with "SQlError(1 , Unable to execute statement, no
> > such table: iso.geoscore)."
>
> Did you try giving iso.geoscore an alias and using that?
>
> select ..., ig.ScoreCode
> from ... iso.geoscore ig
> ...
>
> Martin
--
[ signature omitted ]
