#1 2017-01-17 16:59:00

razielucas
Member
Registered: 2017-01-17
Posts: 3

Using Database View's instead of Database Table's

Good afternoon.

I have a question, in the mORMot framework can I use database view's instead of database table's for select my data?

If yes how I can do it?

Thank's a lot.

Offline

#2 2017-01-18 12:20:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

You can define a TSQLRecord class matching the database view.
But of course, it won't create the view, but generate the SQL as expected (SELECT is the same as for a regular table).
Of course, Add/Delete/Update won't work, unless the database supports INSERT/UPDATE/DELETE on views (which is very unlikely, but working on some engines, to some extent).

Online

#3 2017-01-18 12:48:21

razielucas
Member
Registered: 2017-01-17
Posts: 3

Re: Using Database View's instead of Database Table's

Hi.

Return for some help again.
My view name (on MSSQL SERVER) is Clientes.
My class name on APP is defined as TClientes.

When I query from client to server the error above is logged.
The app ignores the view. Any ideas?

20170118 12452356  B EXC   ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT codigo, nome, loja FROM Clientes] using 3.15.0 - no such table: Clientes, extended_errcode=1"} at 0063D4FE  stack trace API 005634BC 005634E4 0040ACF4

Offline

#4 2017-01-18 14:51:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

Did you define your table as external?

Online

#5 2017-01-18 14:58:36

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: Using Database View's instead of Database Table's

ab wrote:

You can define a TSQLRecord class matching the database view.
But of course, it won't create the view, but generate the SQL as expected (SELECT is the same as for a regular table).
Of course, Add/Delete/Update won't work, unless the database supports INSERT/UPDATE/DELETE on views (which is very unlikely, but working on some engines, to some extent).

Oh, it didn't occurred to me that this would work, is this officially supported by the framework? I mean, since it's not officially documented, AFAIK.

Currently, I derive from TSQLTableJSON to define my "view objects".


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#6 2017-01-18 15:29:14

razielucas
Member
Registered: 2017-01-17
Posts: 3

Re: Using Database View's instead of Database Table's

How do I define table as External?

Offline

#7 2017-01-18 16:13:01

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Online

#8 2017-02-05 15:47:49

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Hi ab and all,

I'm trying to define a model for a SQLite3 database (using Sample 04 as a starting point).
My database contains views which I want to access from ORM.

I'm following the instructions from https://synopse.info/files/html/Synopse … #TITLE_162
but
1- I'm not sure if I should inherit my class from TSQLRecord or TSQLRecordVirtual ?
2- On Model.CreateMissingTables I get the following error:
Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) [CREATE VIRTUAL TABLE match_detail USING Binary(match_id INTEGER, competition_name TEXT COLLATE SYSTEMNOCASE, area1_name TEXT COLLATE SYSTEMNOCASE, team1_name TEXT COLLATE SYSTEMNOCASE, area2_name TEXT COLLATE SYSTEMNOCASE, team2_name TEXT COLLATE SYSTEMNOCASE, date TEXT COLLATE ISO8601);] using 3.16.2 - table match_detail already exists, extended_errcode=1'.

Simplified code is like this:

Model := TSQLModel.Create([TSQLRecordClass1, TSQLRecordClass2], ROOT_NAME);
Model.VirtualTableRegister(TSQLRecordClass2, TSQLVirtualTableBinary); // TSQLRecordClass2 models the view
Server := TSQLRestServerDB.Create(Model, 'mydb.sqlite', TRUE);
Server.CreateMissingTables;
...

What am I dong wrong ?

Thanks.

Offline

#9 2017-02-05 16:26:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

You are mixing things up here.
Virtual tables are only used internally for external databases (e.g. MSSQL or Oracle), or when working with in-memory tables.
No need to define them. The framework will define them from you internally, when needed.

In your case, you are asking to create an in-memory table stored in binary format, and your actual SQLite3 local database has already a real SQLite3 table with the same name.
So it fails creating the virtual table associated with the in-memory table.
Drop the existing SQlite3 database before (or just drop the SQlite3 file).

If your tables are stored in the SQLite3 database, just use TSQLRecord.
If the database has already a view with the TSQLRecord table name, it should work.

There may be a problem in SynSQLite3, so that the views are not seens as existing...
Try to change the code to the following:

  SQL_GET_TABLE_NAMES =
    'SELECT name FROM sqlite_master WHERE type in (''table'',''view'') AND name NOT LIKE ''sqlite_%'';';

Online

#10 2017-02-05 17:14:26

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

After code review, I guess the easiest and safest if you want to work with SQLite3 views is to define the new NoCreateMissingTable property for the table in the TSQLModel.
Use

  Model.Props[TSQLRecordName].NoCreateMissingTable  := true;

See https://synopse.info/fossil/info/45427d2406293d16

Online

#11 2017-02-05 19:01:14

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Thank you ab, that solves the "table already exists" problem.

Now I can't seem to be able to retrieve data from my view:
- I don't think TSQLRecordName.Create(Client, someID) is a possibility as, from my limited knowledge, views don't have a ID column ? Well, I tried anyway, but it will return an empty record (with id=0).
- queries like TSQLRecordName.CreateAndFillPrepare don't return anything.

Offline

#12 2017-02-07 09:29:44

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Can someone please confirm that accessing SQLite3 Views works from the ORM, and that it is just me doing something wrong ? I'm stuck :'-(

Thanks.

Offline

#13 2017-02-07 13:04:36

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

If the view has an ID (or RowID) field, it should work as expected.

Online

#14 2017-02-07 14:55:06

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

I'm working on a legacy DB of about 500MB so it's not easy to produce a sample that I could post here.
So I'll try to explain what I'm doing with a couple screenshots:

- the DB relational structure (partial):
mini_375103capt01.png
- and the view I want to query (as you can see , the view has an ID column):
mini_355775capt02.png

This is the class mapping to that view:

TSQLmatch_detail = class(TSQLRecord)
  private
    // fMatchId: TID;
    fCompetitionName: RawUTF8;
    fArea1Name: RawUTF8;
    fTeam1Name: RawUTF8;
    fArea2Name: RawUTF8;
    fTeam2Name: RawUTF8;
    fDate: TDateTime;
  published
    // property match_id: TID read fMatchId write fMatchId;
    property competition_name: RawUTF8 read fCompetitionName write fCompetitionName;
    property area1_name: RawUTF8 read fArea1Name write fArea1Name;
    property team1_name: RawUTF8 read fTeam1Name write fTeam1Name;
    property area2_name: RawUTF8 read fArea2Name write fArea2Name;
    property team2_name: RawUTF8 read fTeam2Name write fTeam2Name;
    property date: TDateTime read fDate write fDate;
  end;

But queries to the view don't return anything (obviously it's working fine on regular tables).

For ex...

  aMatchDetail := TSQLmatch_detail.Create(Database, 1010055);

...will return an empty object:
{"ID":0,"competition_name":"","area1_name":"","team1_name":"","area2_name":"","team2_name":"","date":""}

1- So, how do I access SQLite3 Views from mORMot ?

...and...

2- How do you map SQLRecord properties to statically linked SQLite3 database column names ?

Thanks

Last edited by jbroussia (2017-02-09 10:27:59)

Offline

#15 2017-02-10 09:31:48

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

OK, to better illustrate my question, I created a really really simple project (still based on sample 04). Here is the archive containing the code and db file:
https://drive.google.com/file/d/0B1Ycio … sp=sharing

In my sqlite3 db I created a very simple view with an id column. I can query tables, but not the view.
Is it a limitation of the mORMot framework?

Offline

#16 2017-02-12 19:41:05

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

For anyone interested in the topic, I finally solved my problem by aliasing the ID column name to"RowID".
It wouldn't work  with "ID", "id" or anything else.

So a view may be defined like this for example:

SELECT
albums.AlbumId AS RowID, /* <--- IMPORTANT !!! Doesn't work if named or aliased to ID */
albums.Title,
artists.Name
FROM
artists
INNER JOIN albums ON albums.ArtistId = artists.Artistid

Offline

#17 2017-02-13 01:07:18

sonadorje
Member
Registered: 2016-12-20
Posts: 13

Re: Using Database View's instead of Database Table's

@jbroussia Good work!

Offline

#18 2017-02-13 07:38:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

Or define the record as external and do proper mapping.

Online

#19 2017-02-13 10:15:33

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

I'm really confused ab, how do you define a record as external ? The only reference I could find in the documentation is this: https://synopse.info/files/html/Synopse … l#TITL_145
And the only difference I see with internal records is the use of VirtualTableExternalRegister ? I used VirtualTableExternalRegisterAll in my code and it still wouldn't work without aliasing the ID column to RowId (SQLite3).

Also, proper mapping ? This View:

CREATE VIEW "album_artist" AS 
SELECT
albums.AlbumId AS RowID,
albums.Title,
artists.Name
FROM
artists
INNER JOIN albums ON albums.ArtistId = artists.Artistid

...is mapped to:

TSQLalbum_artist = class(TSQLRecord)
private
  fTitle: RawUTF8;
  fName: RawUTF8;
published
  property Title: RawUTF8 read fTitle write fTitle;
  property Name: RawUTF8 read fName write fName;
end;

Is that wrong ?

Offline

#20 2017-02-15 19:00:46

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Update on queries with mORMot on SQLite3 Views...

The above workaround only works with a simple DB.Retrieve(ID, aSQLRecord).
It doesn't work with more advanced queries (CreateAndFillPrepare...)

:-\

I don't know what to do, is it better to use SQL queries after all ?

Offline

#21 2017-02-15 19:40:33

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

To define the record as external, use a TSQLDBSQLite3Connection instance, as defined in SynDBSQLite3.

Then follow the documentation, like any other SynDB external table in the ORM.
See https://synopse.info/files/html/Synopse … l#TITL_146

Online

#22 2017-02-16 09:38:01

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

That's what I'm doing (I think ?)

Here is my code (I hope it's not too long and won't break the forum)...

Server:

program Server;

{$APPTYPE CONSOLE}

uses
  {$I SynDprUses.inc} // use FastMM4 on older Delphi, or set FPC threads
  mORMot, mORMotHttpServer, mORMotSQLite3,
  SynCommons,
  SynDB,
  SynDBSQLite3, SynSQLite3, SynSQLite3Static, mORMotDB,
  chinook;

var
  DBConnProps: TSQLDBSQLite3ConnectionProperties;
  Model: TSQLModel;
  DBServer: TSQLRestServerDB;
  HttpServer: TSQLHttpServer;
begin
  DBConnProps := TSQLDBSQLite3ConnectionProperties.Create('chinook.db', '', '', '');
  try
    Model := CreateModel;
    VirtualTableExternalRegisterAll(Model, DBConnProps);
    Model.Props[TSQLalbum_artist].NoCreateMissingTable := TRUE;
    Model.Props[TSQLartists].ExternalDB.MapField('ID', 'ArtistId');
    Model.Props[TSQLalbums].ExternalDB.MapField('ID', 'AlbumId');
    try
      DBServer := TSQLRestServerDB.Create(Model, 'chinook.db', TRUE);
      try
        DBServer.CreateMissingTables;
        HttpServer := TSQLHttpServer.Create('8080', [DBServer], '+', HTTP_DEFAULT_MODE);
        try
          HttpServer.AccessControlAllowOrigin := '*';
          Writeln(#10'Background HTTP server is running.'#10);
          Writeln('Press [Enter] to close the server.'#10);
          readln;
        finally
          HttpServer.Free;
        end;
      finally
        DBServer.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    DBConnProps.Free;
  end;
end.

Model:

unit chinook;

interface

uses
  SynCommons, mORMot;

type
  TSQLartists = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
  end;

  TSQLalbums = class(TSQLRecord)
  private
    fTitle: RawUTF8;
    fArtistId: TSQLartists;
  published
    property Title: RawUTF8 read fTitle write fTitle;
    property ArtistId: TSQLartists read fArtistId write fArtistId;
  end;

  // VIEW
  TSQLalbum_artist = class(TSQLRecord)
  private
    fTitle: RawUTF8;
    fName: RawUTF8;
  published
    property Title: RawUTF8 read fTitle write fTitle;
    property Name: RawUTF8 read fName write fName;
  end;

const
  ROOT_NAME = 'root';

function CreateModel: TSQLModel;
  
implementation

function CreateModel: TSQLModel;
begin
  Result := TSQLModel.Create([TSQLartists,
                              TSQLalbums,
                              TSQLalbum_artist],
                              ROOT_NAME);
end;

end.

And on the client side:

procedure TfrmClient.btnTestClick(Sender: TObject);
var
  aAlbumArtist: TSQLalbum_artist;
begin
  // Test 1: OK
  aAlbumArtist := TSQLalbum_artist.Create(Database, 1);
  // aAlbumArtist := TSQLalbum_artist.Create;
  // Database.Retrieve(1, aAlbumArtist);
  try
    mmoResult.Lines.Add(UTF8ToString(ObjectToJSON(aAlbumArtist)));
  finally
    aAlbumArtist.Free;
  end;

  // Test 2: NOT OK
  aAlbumArtist := TSQLalbum_artist.CreateAndFillPrepare(Database, 'RowID < ?', [10]);
  // aAlbumArtist := TSQLalbum_artist.CreateAndFillPrepare(Database, 'ID < ?', [10]);
  try
    with mmoResult.Lines do begin
      BeginUpdate;
      while aAlbumArtist.FillOne do
        Add(UTF8ToString(ObjectToJSON(aAlbumArtist)));
      EndUpdate;
    end;
  finally
    aAlbumArtist.Free;
  end;
end;

Offline

#23 2017-02-17 09:22:19

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Abstract:

I want to query some SQLite3 View from ORM:
- I defined a TSQLRecord class matching the View, the first column of the View is an ID column
- I defined my DB as external by using TSQLDBSQLite3ConnectionProperties
- I registered my tables with VirtualTableExternalRegisterAll
- and I used NoCreateMissingTable for my View

Now I still can't query my View (only "SELECT" statements obviously):
- If the ID column is aliased to RowID:
  * I can use  TSQLRest.Retrieve(aClass, aID)
  * But "complex" queries don't work (CreateAndFillPrepare...) -> no error, but empty result
- Else (no alias or different than RowID):
  * Queries don't work at all -> no error, empty result

Does anybody have any experience with SQLite3 Views and accessing them with mORMot ? I'd like to know if I'm doing something wrong, or if it's a limitation of SQLite3, a limitation of the framework, or even a bug ? :-\
(@razielucas: did you have any success with your Views on MSSQL ?)

Thanks !

Offline

#24 2017-02-19 08:57:57

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Sorry to insist but I've been stuck on this for 2 weeks :'-(

ab: is it a limitation of the framework ?

Thanks.

Offline

#25 2017-02-19 09:09:29

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

It is an unsupported/untested feature, as I wrote.

Did you map your external field ID?

Sounds like if the SQL uses the virtual table mechanism - which is not intended here.
See https://synopse.info/files/html/Synopse … ml#TITL_20
and https://synopse.info/files/html/Synopse … ml#TITL_30

Try to find out why your CreateAndFillPrepare request SQL is not recognized in TSQLRestStorageExternal.AdaptSQLForEngineList, or what is the actual SQL executed by the SynDB layer.

You also may try to map the primary key to two fields, one named ID and the other RowID...

Online

#26 2017-02-19 10:58:34

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Hi ab,

Yes I tried mapping the ID of the view with...

Model.Props[TSQLalbum_artist].ExternalDB.MapField('ID', 'AlbumId');

Could it be that the way I define my DB as external, or map the field names, is incorrect (see code a couple messages above) ?
When accessing an external DB with mapped field names, even querying "normal" tables (not Views) doesn't work for me:

aAlbum := TSQLalbums.Create(Database, 1);
// dosomethingwith(aAlbum) // OK

aAlbum := TSQLalbums.CreateAndFillPrepare(Database, 'ID<?', [10]); // AlbumID field is mapped to ID
// while aAlbum.FillOne do
  // dosomethingwith(aAlbum)  // EMPTY

Very probable that I'm doing something wrong but can't spot what !

Last edited by jbroussia (2017-02-19 13:51:31)

Offline

#27 2017-02-19 15:48:39

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

As I proposed above:

Try to find out why your CreateAndFillPrepare request SQL is not recognized in TSQLRestStorageExternal.AdaptSQLForEngineList, or what is the actual SQL executed by the SynDB layer.

Online

#28 2017-02-21 10:32:29

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Well, that's the reason why I think I'm not doing things as intended: the debugger never reaches the breakpoint I set in TSQLRestStorageExternal.AdaptSQLForEngineList hmm

(I get an exception in SynSQLite3.sqlite3_check where the resulting SQL is: 'SELECT ID,Title,Name FROM album_artist WHERE ID < ?')

Offline

#29 2017-02-21 10:35:33

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

So your Database instance may not have the table TSQLalbum_artist declared as external in the SQLite3 DB, and the fields mapped as expected.

Online

#30 2017-02-21 12:05:24

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

Is that pseudo code correct to declare my sqlite3 db as external, and map the "AlbumId" field of the album_artist view to "ID" ?

Props := TSQLDBSQLite3ConnectionProperties.Create('mydb.db', '', '', '');
Model := TSQLModel.Create([TSQLalbum_artist], ROOT_NAME);
VirtualTableExternalRegister(Model, TSQLalbum_artist, Props);
Model.Props[TSQLalbum_artist].NoCreateMissingTable := TRUE;         // album_artist is a View
Model.Props[TSQLalbum_artist].ExternalDB.MapField('ID', 'AlbumId'); // Map field AlbumId to ID
DB := TSQLRestServerDB.Create(Model, 'mydb.db', TRUE);

Offline

#31 2017-02-21 15:40:03

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

Perhaps you may reuse the SQlite3 instance of Props for the DB (i.e. use Props.MainSQLite3DB instead of 'mydb.db' for TSQLRestServerDB.Create).
There should be a single exclusive low-level TSQLDataBase instance to access the shared mydb.db file.

And call DB.CreateMissingTables.

Online

#32 2017-02-21 17:23:20

afarias
Member
Registered: 2016-04-13
Posts: 14

Re: Using Database View's instead of Database Table's

I have the same issue. The generated SQL is fine (and works as expected returnig 1 row if run against the DB) but a 404 error "not found" is written to log.

20170221 17103709  B SQL       SynDBFireDAC.TSQLDBFireDACStatement(03744180) select ID,EmployeeName,EmployeeRole,AdmissionDate from vw_Employee where ID=1
20170221 17103711  A debug     mORMotSQLite3.TSQLRestServerDB(0372D470) TSQLRestRoutingREST.Error: {  "errorCode":404,  "errorText":"Not Found"  }
20170221 17103711  A srvr      mORMotSQLite3.TSQLRestServerDB(0372D470)   GET root/Employee ORM-Get -> 404 with outlen=47 in 173806 us
20170221 17103711  ! clnt      mORMotHttpClient.TSQLHttpClientWinHTTP(036D30D0) GET root/Employee/1 status=404 len=47 state=5
20170221 17103711  ! ERROR mORMotHttpClient.TSQLHttpClientWinHTTP(036D30D0) GET root/Employee/1 returned 404 (Not Found) with message  {  "errorCode":404,  "errorText":"Not Found"  }

Here's the code I'm using:

  fModel := CreateModel;

  fDriver := TFDPhysIBDriverLink.Create(nil);
  fDriver.VendorLib := 'fbclient.dll';

  URI := FIREDAC_PROVIDER[dFirebird];
  fProperties := TSQLDBFireDACConnectionProperties.Create(
      URI, DB_PATH, DB_USER, DB_PASS);

  VirtualTableExternalRegisterAll(fModel, fProperties);
  VirtualTableExternalMap(fModel, TEmployee, fProperties, 'VW_EMPLOYEE');

  fRestServer := TSQLRestServerDB.Create(fModel, SQLITE_MEMORY_DATABASE_NAME, False);
  fRestServer.AcquireExecutionMode[execORMGet] := amBackgroundThread;
  fRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;
  fRestServer.CreateMissingTables;

  (..)

If I just change the view name 'VW_EMPLOYEE' with the table name 'EMPLOYEE' than it works.

Regards,

Last edited by afarias (2017-02-21 19:21:17)

Offline

#33 2017-02-21 18:12:11

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Using Database View's instead of Database Table's

There is no mapping for your external view...

Online

#34 2017-02-21 19:19:46

afarias
Member
Registered: 2016-04-13
Posts: 14

Re: Using Database View's instead of Database Table's

ab wrote:

There is no mapping for your external view...

Yes, just for the view name:

VirtualTableExternalMap(fModel, TEmployee, fProperties, 'VW_EMPLOYEE');

Field names match the TEmployee class property names.

Last edited by afarias (2017-02-21 19:21:33)

Offline

#35 2017-02-22 10:57:19

jbroussia
Member
From: France
Registered: 2011-04-09
Posts: 74

Re: Using Database View's instead of Database Table's

ab wrote:

Perhaps you may reuse the SQlite3 instance of Props for the DB (i.e. use Props.MainSQLite3DB instead of 'mydb.db' for TSQLRestServerDB.Create).
There should be a single exclusive low-level TSQLDataBase instance to access the shared mydb.db file.

Yes, thanks, I was wondering why I had two references to the DB file...

And call DB.CreateMissingTables.

Forgot to put it in the pseudo-code, but indeed I'm calling it in my actual code.

After trying many many things, I think the main problem I have is with the way I declare/use external tables. The field name mapping doesn't work, most probably as a result of me doing something wrong with external table declaration hmm Truth be told, for now I don't need this feature (mapping), and I can use an internal DB, but as I'm learning how to use the mORMot framework, I like to understand what I'm doing incorrectly.
Then there is another little problem in accessing SQLite Views: DB.Retrieve and DB.Create(TSQLRecord, ID) don't work, but this is easily circumvented by using CreateAndFillPrepare instead. I think this is because DB.Retrieve/Create internally use the "RowID" column but my View only has an ID field, whicjh I can query with CreateAndFillPrepare.

Last edited by jbroussia (2017-02-23 07:57:41)

Offline

#36 2017-03-22 16:03:22

luscen
Member
Registered: 2014-04-21
Posts: 8

Re: Using Database View's instead of Database Table's

me too, i have the same problem,i can not find the solution now.

Last edited by luscen (2017-03-23 02:30:54)

Offline

Board footer

Powered by FluxBB