FireDAC SQLite MetaDefCatalog

[Sonjli 5]

Hello,

I am trying to execute this SQL with FireDAC in D12 ent:

create table if not exists mydb.mytable (name varchar(100) unique not null);

With FDConnection.ExecSQL(...);

These are the params I pass to the connection:

DriverID=SQLite
Database=mydatabase.db
MetaDefCatalog=mydb
CreateDatabase=True

As I read in Embarcadero docs:

https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Connect_to_SQLite_database_(FireDAC)

 

Well, the SQL with the "mydb" alias does not work. If I use "MAIN" (look at next sql) then it works, whatever "MetaDefCatalog" value I set!

create table if not exists MAIN.mytable (name varchar(100) unique not null); -- <-- WORKS!

create table if not exists mytable (name varchar(100) unique not null); -- <-- WORKS!

create table if not exists mydb.mytable (name varchar(100) unique not null); -- <-- DOESN'T WORK!

 

Thanks,

Eddy

[weirdo12 16]

schema-name == The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database.

 

https://sqlite.org/lang_attach.html

Quote

The ATTACH DATABASE statement adds another database file to the current database connection. Database files that were previously attached can be removed using the DETACH DATABASE command.

[Sonjli 5]

16 hours ago, weirdo12 said:

schema-name == The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database.

 

https://sqlite.org/lang_attach.html

How do you make it work with FireDAC?

Do you use two connections?

Can you better explain with examples?

Thanks

[weirdo12 16]

12 minutes ago, Sonjli said:

How do you make it work with FireDAC?

Do you use two connections?

Can you better explain with examples?

Thanks

 

Why do you need to use the mydb schema name?

 

 

[Sonjli 5]

48 minutes ago, weirdo12 said:

 

Why do you need to use the mydb schema name?

 

 

I use a multi database framework that need to do its queries using the schema name. It supposes that I can have many dbs and that I can mix tables in SQL, like all schema-based db does (SQLServer, Oracle, etc.)
example:

select dbusers.dbo.users.name, dbauth.dbo.athentications.read, etc.

Edited December 6, 2023 by Sonjli

[weirdo12 16]

1 hour ago, Sonjli said:

Can you better explain with examples?

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.SQLite,
  FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs, FireDAC.VCLUI.Wait,
  FireDAC.Phys.SQLiteWrapper.Stat, Data.DB, FireDAC.Comp.Client;

type
  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;
    procedure FDConnection1AfterConnect(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FDConnection1AfterConnect(Sender: TObject);
begin
  var count : Integer;

  // this will create mydb.sqlite if it does not exist
  FDConnection1.ExecSQL('ATTACH DATABASE ''C:\Users\Public\Documents\mydb.sqlite'' AS mydb');
  FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTs mydb.t1 (c1 TEXT)');
  FDConnection1.ExecSQL('INSERT INTO mydb.t1 (c1) VALUES (lower(hex(randomblob(16))))');

  count := FDConnection1.ExecSQLScalar('SELECT COUNT(*) FROM mydb.t1');
end;


procedure TForm1.FormCreate(Sender: TObject);
begin
  FDConnection1.Params.Database := ':memory:';
  FDConnection1.Connected := true;
end;

end.

 

[Sonjli 5]

Thank you very much. I try.

[weirdo12 16]

3 minutes ago, Sonjli said:

I use a multi database framework that need to do its queries using the schema name. It supposes that I can have many dbs and that I can mix tables in SQL, like all schema-based db does (SQLServer, Oracle, etc.)
example:

select dbusers.dbo.users.name, dbauth.dbo.athentications.read, etc.

 

select {IF MSSQL}dbusers.dbo.{FI}users.name, {IF MSSQL}dbusers.dbo.{FI}athentications.read, etc.

[Sonjli 5]

Just now, weirdo12 said:

 

select {IF MSSQL}dbusers.dbo.{FI}users.name, {IF MSSQL}dbusers.dbo.{FI}athentications.read, etc.

wooo, never used this feature... thank you!

[weirdo12 16]

https://docwiki.embarcadero.com/RADStudio/Athens/en/Preprocessing_Command_Text_(FireDAC)

[Sonjli 5]

FYI everything works like a charm.

Thanks again

[weirdo12 16]

7 minutes ago, Sonjli said:

FYI everything works like a charm.

Thanks again

Excellent!