Recent

Author Topic: [SOLVED] SQLite3Dataset - ATTACH and INNER JOIN  (Read 3712 times)

totya

  • Hero Member
  • *****
  • Posts: 636
[SOLVED] SQLite3Dataset - ATTACH and INNER JOIN
« on: December 26, 2015, 08:41:18 pm »
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!
« Last Edit: December 28, 2015, 06:26:46 am by totya »

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: SQLite3Dataset - ATTACH and INNER JOIN
« Reply #1 on: December 27, 2015, 09:50:04 am »
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

  • Sr. Member
  • ****
  • Posts: 458
Re: SQLite3Dataset - ATTACH and INNER JOIN
« Reply #2 on: December 27, 2015, 11:41:02 am »
Fixed in svn

totya

  • Hero Member
  • *****
  • Posts: 636
Re: SQLite3Dataset - ATTACH and INNER JOIN
« Reply #3 on: December 27, 2015, 12:23:44 pm »
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

  • Hero Member
  • *****
  • Posts: 1096
Re: SQLite3Dataset - ATTACH and INNER JOIN
« Reply #4 on: December 27, 2015, 05:32:03 pm »
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;
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

totya

  • Hero Member
  • *****
  • Posts: 636
Re: SQLite3Dataset - ATTACH and INNER JOIN
« Reply #5 on: December 30, 2015, 05:30:42 pm »
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

  • Hero Member
  • *****
  • Posts: 1096
Re: [SOLVED] SQLite3Dataset - ATTACH and INNER JOIN
« Reply #6 on: December 31, 2015, 09:50:32 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018