Use of mapping to external database

neutronwrangler · 2016-02-09 09:23:25

I have a TSQLRestServer which is connected to an SQLServer database. There are some incompatibilities between existing database names and Delphi (several fields are called Type and I also need to reference tables outside the default schema so I need to have dots in the names). On the server I have used VirtualTableExternalMap and VirtualTableExternalRegister to map acceptable names.

When constructing queries on the server I can use TSQLModel.Props[...].ExternalDB.TableName and .ExternalDB.SQL to get the mapping I need.

My problem is when I use TSQLRestClientURI.Retrieve on the client to access a record, the queries on the server do not honour the mappings when talking to the external database. The SQL building calls (TSQLRest.SQLComputeForSelect, etc) use the internal SQLTableName and Props[...].SQL properties.

Is there something I am doing wrong? I want the implementation details of the external mappings hidden from the client.

Many thanks for any help
John Cooper

ab · 2016-02-09 10:59:15

On client side, it indeed uses the ORM fields.

But go one step further in debugging, and I guess you would find out that on the server side, TSQLRestServer.URI would do the mapping using mapped field names.
Take a look at TSQLRestServerURIContext.ExecuteORMGet which would identify the external table, and run Static.EngineRetrieve which would use TSQLRestStorageExternal.AdaptSQLForEngineList and compute the expected SELECT statements using the mapped field names.

For details, see http://synopse.info/files/html/Synopse% … ml#TITL_42
and http://synopse.info/files/html/Synopse% … ml#TITL_30

neutronwrangler · 2016-02-11 10:27:05

Thanks for that. I have redeclared my records as inheriting from TSQLRecordVirtualTableAutoID and I think that should mean that VirtualTableExternalMap for the model makes InternalAdaptSQL pick up the methods from fStaticVirtualTable, but it is nil. I am not sure how TSQLVirtualTable.Create should be called to set it up.

John Cooper

ab · 2016-02-11 10:50:51

If the table is declared as external, it would be implemented as a virtual table, then InternalAdaptSQL is called.

There is something wrong with your code.

neutronwrangler · 2016-02-15 12:52:19

Great, it is working!

I was forgetting to create the virtual tables although I had created the records and registered the tables, for some reason I was expecting registering to do the work for me.

Some of my fields are read only, ie they are computed in the SQL table definition. I want them retrieved when I query the database, but they should be flagged so that when the SQL is assembled for an update they are excluded from the list. I think I should override somewhere to set aCustomFieldsCSV to only those which should be updated. Is it something I should do in the virtual table or the record definition?

Thanks for any guidance

ab · 2016-02-15 13:25:52

AFAIR it is not possible to exclude those fields easily...

The workaround is indeed to use aCustomFieldsCSV for updates and inserts.

mORMot Open Source

#1 2016-02-09 09:23:25

Use of mapping to external database

#2 2016-02-09 10:59:15

Re: Use of mapping to external database

#3 2016-02-11 10:27:05

Re: Use of mapping to external database

#4 2016-02-11 10:50:51

Re: Use of mapping to external database

#5 2016-02-15 12:52:19

Re: Use of mapping to external database

#6 2016-02-15 13:25:52

Re: Use of mapping to external database

Board footer