[SOLVED] SQLite3Dataset - ATTACH and INNER JOIN
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-12-26 19:41

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
Sqlite3DS, db, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
DBGrids, Clipbrd; type   { TForm1 }   TForm1 = class(TForm)   
Button1: TButton;    DataSource1: TDataSource;    DBGrid1: TDBGrid;  
Sqlite3Dataset1: TSqlite3Dataset;    procedure Button1Click(Sender:
TObject);  private    { private declarations }  public    { public
declarations }  end; var  Form1: TForm1; implementation {$R *.lfm} {
TForm1 } procedure TForm1.Button1Click(Sender: TObject);begin  with
Sqlite3Dataset1 do  begin    FileName:='db1.db';    
TableName:='table1';    PrimaryKey:='Num';     ExecSQL('ATTACH
"db2.db" AS "db_test";');     SQL:='SELECT * FROM '+       
 'main.'+TableName+' a '+         'inner join '+       
 'db_test.'+TableName+' b '+         'on b.'+PrimaryKey+' =
a.'+PrimaryKey+' LIMIT 20;';     ShowMessage(SQL);   
Clipboard.AsText:=SQL;     Open;  end;end; end. 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i .open
db1.dbATTACH "db2.db" AS "db_test";.database.tableSELECT * FROM
main.table1 a inner join db_test.table1 b on b.Num = a.Num LIMIT
20; sqlite> .tabledb_test.table1  table1sqlite> SELECT * FROM
main.table1 a inner join db_test.table1 b on b.Num = a.Num LIMIT
20;1|test 1|1|test 102|test 2|2|test 113|test 3|3|test 12sqlite> 
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 · 2015-12-27 08:50

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 · 2015-12-27 10:41

Fixed in svn

----------------------------------------------------------------------
totya · 2015-12-27 11:23

Quote from: LuizAmérico on December 27, 2015, 09:50:04 amThe 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 · 2015-12-27 16:32

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 · 2015-12-30 16:30

Quote from: mangakissa on December 27, 2015, 05:32:03 pmSELECT * 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 · 2015-12-31 08:50

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.