[SOLVED] SQLite3Dataset - ATTACH and INNER JOIN

[totya]

Hi!

I want to use tables from multiple database.
Idea from here: link

These databases table structure is all the same.

See this code:

Code: Pascal  [Select][+][-]

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, Sqlite3DS, db, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, DBGrids, Clipbrd;
9.  
10. type
11.  
12.   { TForm1 }
13.  
14.   TForm1 = class(TForm)
15.     Button1: TButton;
16.     DataSource1: TDataSource;
17.     DBGrid1: TDBGrid;
18.     Sqlite3Dataset1: TSqlite3Dataset;
19.     procedure Button1Click(Sender: TObject);
20.   private
21.     { private declarations }
22.   public
23.     { public declarations }
24.   end;
25.  
26. var
27.   Form1: TForm1;
28.  
29. implementation
30.  
31. {$R *.lfm}
32.  
33. { TForm1 }
34.  
35. procedure TForm1.Button1Click(Sender: TObject);
36. begin
37.   with Sqlite3Dataset1 do
38.   begin
39.     FileName:='db1.db';
40.  
41.     TableName:='table1';
42.     PrimaryKey:='Num';
43.  
44.     ExecSQL('ATTACH "db2.db" AS "db_test";');
45.  
46.     SQL:='SELECT * FROM '+
47.          'main.'+TableName+' a '+
48.          'inner join '+
49.          'db_test.'+TableName+' b '+
50.          'on b.'+PrimaryKey+' = a.'+PrimaryKey+' LIMIT 20;';
51.  
52.     ShowMessage(SQL);
53.     Clipboard.AsText:=SQL;
54.  
55.     Open;
56.   end;
57. end;
58.  
59. end.
60.  

I get error message (about):
EDatabase error:
duplicate name 'Num' in TFields

But, If I run these commands from commend shell (sqlite3.exe) see Clipboard.AsText:=SQL line,
then run perfectly, without error!
See:

Code: Pascal  [Select][+][-]

1. .open db1.db
2. ATTACH "db2.db" AS "db_test";
3. .database
4. .table
5. SELECT * FROM main.table1 a inner join db_test.table1 b on b.Num = a.Num LIMIT 20;
6.  
7. sqlite> .table
8. db_test.table1  table1
9. sqlite> SELECT * FROM main.table1 a inner join db_test.table1 b on b.Num = a.Num LIMIT 20;
10. 1|test 1|1|test 10
11. 2|test 2|2|test 11
12. 3|test 3|3|test 12
13. sqlite>
14.  

and run perfectly in sqlite3 shell...

Full demo project is attached with sample db files.

Lazarus is 1.4.4
TSQLite3Dataset is the latest SVN

Thanks!

[LuizAmérico]

The issue is not related to attach.

The problem is the duplicate name.

This query also triggers the exception:

SELECT * FROM main.table1 a inner join main.table1 b on b.Num = a.Num LIMIT 20;

I will test with other components to see their behavior

[LuizAmérico]

Fixed in svn

[totya]

The issue is not related to attach.

The problem is the duplicate name.

Hi!

Thanks, you are right! But thanks for the fix (with the MakeNameUnique function). It's works now!

Thanks again!

[mangakissa]

SELECT * FROM main.table1 a inner join main.table1 b on b.Num = a.Num LIMIT 20;

should be

SELECT a.* FROM main.table1 a inner join main.table1 b on b.Num = a.Num LIMIT 20;

[totya]

SELECT * FROM main.table1 a inner join main.table1 b on b.Num = a.Num LIMIT 20;

should be

SELECT a.* FROM main.table1 a inner join main.table1 b on b.Num = a.Num LIMIT 20;

Hi!

I think your ide this:
SELECT a.* FROM main.table1 a inner join db_test.table1 b on b.Num = a.Num LIMIT 20;

But I want to see data from two tables, if the key fields is equally.
For example result field names:
num field / text_from_table1 / text_from_table2

Filed names now is automatic, thanks to LuizAmérico, but I want own fields names in the query result (in progress...).

[mangakissa]

Code: [Select]

select cast('',varchar(10)) AS ownfield1, cast ('', varchar(5)) AS ownfield2, cast(0,integer) as ownfield3 from table
Remember that ownfields will not be saved if you're not using INSERTSQL and UPDATESQL

Or create calculated fields as persisted fields.