SELECT from attached database (SQLite)
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2014-06-03 00:57

This syntax works with zeos but SQLdb gives message: near ".": syntax
error from this query:

Code: [Select]SELECT * FROM main.cities
UNION
SELECT * FROM norway.cities
The databases are DB1.db3 and DB2.db3. Inside each of them there is a
simple table called cities, with city_id and city_name. 

Can this be done with a JOIN instead of a UNION? The returned dataset
should contain all data from both cities tables. Zeos gave me data
from only one database whatever I tried. My mistake of course.

The compiler doesn't complain here, but is this OK?:

Code: [Select]Conn.DatabaseName:= ExtractFilePath(Application.ExeName)
+'DB1.db3';
Conn.Open;
Conn.ExecuteDirect('END TRANSACTION');
Conn.ExecuteDirect('ATTACH DATABASE' +QuotedStr('db2.db3') +'AS
norway);
Conn.ExecuteDirect('BEGIN TRANSACTION');

----------------------------------------------------------------------
BigChimp · 2014-06-03 10:04

You could try setting ParseSQL to false, perhaps also look into
ParamCheck (spelling, by heart).

Is it an sqldb error or does the error come from sqlite? (Another way
to test: can you run that query in sqlite query tools?)

I wouldn't think you could do this via a join as a join is used to
select columns from multiple sources, not records.

----------------------------------------------------------------------
fpcoder · 2014-06-03 12:50

You found it. Disabling ParseSQL removes the 'near ".": syntax error'.

The query also worked if 'main.' was removed from the first SELECT
line:

Code: [Select]SELECT * FROM cities
UNION
SELECT * FROM norway.cities

ParseSQL could then be true.

Now it all works, but out of curiosity I commented out the two first
lines in the query:

Code: [Select]--SELECT * FROM cities
--UNION
SELECT * FROM norway.cities

Again complaint about 'near ".": syntax error' for 'norway.cities' (If
ParseSQL is true)

Since 'norway.cities' was accepted in the first query above, I dont
completely understand why ParseSQL must be disabled.

----------------------------------------------------------------------
BigChimp · 2014-06-03 13:35

sqldb has a simple parser that is able to generate
insertsql/updatesql/deletesql statements out of the query's .sql sql
statement.

This parser probably stops at the UNION keyword (haven't looked at
that code for a long while).

It is deliberately kept fairly simple because catering for all db
types with corresponding dialects would be a diabolical job.
That's why you can manually set ParseSQL to false and supply your on
insertsql/updatesql/deletesql

Anyway for your query it doesn't matter as UNION queries aren't
updatable (by default anyway, perhaps sqlite has some smart mechanism
that allows this), so insertsql/updatesql/deletesql serve no purpose
anyway.

----------------------------------------------------------------------
fpcoder · 2014-06-03 15:14

Thanks, thats likely how it is, and thats fine.

Joining the tables also works:

Code: [Select]SELECT c1.city_name AS 'Swedish Citys', c2.city_name AS
'Norwegian Citys'
FROM   main.cities AS c1
JOIN   norway.cities AS c2
ON     c1.city_id = c2.city_id

----------------------------------------------------------------------
BigChimp · 2014-06-03 15:47

Yes? Are those Norwegian and Swedish names for the same cities? Or do
you want to list city names regardless of the layout (you're getting 2
column with names now but that isn't a problem??!)
(I might very well be missing something due to a criminal lack of
caffeine, in that case please accept my apologies in advance...  )

Anyway, glad it works!

----------------------------------------------------------------------
fpcoder · 2014-06-03 18:29

This is just part of my journey into database land, the tables and
content was just made up to have something to attach and query. At
some time I will need both UNION and JOIN of attached databases, so
this is spot on. Will write something on the wiki after some more
tinkering.

And thanks again, I dont know how long it would have taken me to find
out about ParseSQL.


Quote from: BigChimp on June 03, 2014, 05:47:17 pmYes? Are those
Norwegian and Swedish names for the same cities? Or do you want to
list city names regardless of the layout (you're getting 2 column with
names now but that isn't a problem??!)
(I might very well be missing something due to a criminal lack of
caffeine, in that case please accept my apologies in advance...  )

Anyway, glad it works!

----------------------------------------------------------------------
LacaK · 2014-06-04 05:23

@kapibara: can you post here sources of your application, which causes
mentioned error: 'near ".": syntax error'
So we can try reproduce it ...

----------------------------------------------------------------------
LacaK · 2014-06-05 06:07

Problem is in it, that SQLiteConnection does detect TableName:
  "main.cities" in 1st SELECT
And then attempts obtain index information for this table.
It is used in background "PRAGMA table_info(main.cities)", but this
pragma statement does not support doted format ... so it raises error.
If you does not use prefix "main" then all works.

----------------------------------------------------------------------
fpcoder · 2014-06-10 01:09

Hi, sorry for the delay.

Here's the example source.

DB1 and DB2 is auto-created at startup.

Attach database, then try to open "dot" query and you see what
happens.

Opening "no dot" query works.


Quote from: LacaK on June 04, 2014, 07:23:52 am@kapibara: can you post
here sources of your application, which causes mentioned error: 'near
".": syntax error'
So we can try reproduce it ...