VirtualTableExternalRegister: the column name "ID" is invalid

corchi72 · 2014-12-18 14:42:38

I'm trying to connect to a MSSQL table, without the ID column, but I can not! I'm forced to add a column named ID (Integer) in all tables of an existing database?
I can not modify the tables of the customer!!

Deletefile('testExternal.db3');

var
   aClient: TSQLRestClientDB;
   aServer : TSQLRestServerDB;
   Model : TSQLModel;
   ARec_UTEN : TSQLSYS_UTEN;
begin
  Props := TOleDBConnectionProperties.Create('..\SQLEXPRESS','TestDB','sa',xxx');

  Props.ConnectionString := UTF8ToWideString(Local_Connection);
  //Props.ConnectionStringDialogExecute;

 

  Model := TSQLModel.Create([TSQLSYS_UTEN]);  
 // VirtualTableExternalRegisterAll(Model, Props, false);

  VirtualTableExternalRegister(Model,TSQLSYS_UTEN,Props,'SYS_UTEN');
  Model.Props[TSQLSYS_UTEN].ExternalDB.MapAutoKeywordFields;

  aClient := TSQLRestClientDB.Create(Model,nil,'testExternal.db3',TSQLRestServerDB);
  aClient.Server.StaticVirtualTableDirect := false;
  aClient.Server.CreateMissingTables;                                                                             //here is the error ID not exist!!
  aClient.Server.CreateSQLIndex(TSQLSYS_UTEN,['SYSUTE'],false);

   if aClient.Server.StaticVirtualTable[TSQLSYS_UTEN]=nil then
    Showmessage('error');

  ARec_UTEN := TSQLSYS_UTEN.CreateAndFillPrepare(aClient,'');

while ARec_UTEN.FillOne do
  begin
    showmessage(format('ID=%d - %s',[ARec_UTEN.ID, ARec_UTEN.SYSUTE]) );


  end;

Thanks Corchi

Last edited by corchi72 (2014-12-18 14:43:35)

ab · 2014-12-18 15:40:15

You need an INTEGER primary key column for any TSQLRecord table.

The mORMot Documentation wrote:

Due to the design of SQLite3 virtual tables, and mORMot internals in its current state, the database primary key must be an INTEGER field to be mapped as expected by the ORM. But you can specify any secondary key, e.g. a TEXT field, via stored AS_UNIQUE definition in code.

But you can rename the name from "ID" to whatever you expect, using column mapping.
See http://synopse.info/files/html/Synopse% … l#TITL_120

corchi72 · 2014-12-19 08:17:51

I understood but in my case the key is composed of two text fields, in that case, what should I write to map the ID field?

  TSQLSYS_UTEN = class(TSQLRecord)
  protected
    fSYSUTE: RawUTF8;
    fSYSSAP: RawUTF8;
    fSYSPWD: RawUTF8;
    fSYSTYPE: Int64;
    fSYSUDES: RawUTF8;
  published
    /// match SYS_UTEN.SYSUTE [nvarchar 20 0 0] *
    property SYSUTE: RawUTF8 index 20 read fSYSUTE write fSYSUTE stored AS_UNIQUE;;
    /// match SYS_UTEN.SYSSAP [nvarchar 3 0 0]
    property SYSSAP: RawUTF8 index 3 read fSYSSAP write fSYSSAP stored AS_UNIQUE;;
    /// match SYS_UTEN.SYSPWD [nvarchar 20 0 0]
    property SYSPWD: RawUTF8 index 20 read fSYSPWD write fSYSPWD;
    /// match SYS_UTEN.SYSTYPE [int 0 10 0]
    property SYSTYPE: Int64 read fSYSTYPE write fSYSTYPE;
    /// match SYS_UTEN.SYSUDES [nvarchar 100 0 0]
    property SYSUDES: RawUTF8 index 100 read fSYSUDES write fSYSUDES;
  end;

I should write an array of fields for ID. This is the example:

VirtualTableExternalRegister(Model,TSQLSYS_UTEN,Props,'SYS_UTEN');
Model.Props[TSQLSYS_UTEN].ExternalDB. // custom field mapping
MapField('ID',['SYSUTE','SYSSAP'] ). // array of ID in the original MSSQL Table
MapAutoKeywordFields;

thanks corchi

ab · 2014-12-19 08:55:52

The primary key should be a single INTEGER column, by design.
You can not define a composite primary key.

corchi72 · 2014-12-19 10:39:27

Then I have to modify the tables of my client to use the mORMot ... this is impossible!!! ... I can't think that it is a common practice to modify tables in a management of a customer, for them to be questioned by an external application.

I thought that the tables of mORMot childeren in memory the column "ID" and then when you had to do the INSERT / UPDATE/ DELETE the outside tables "MSSQL" were simply removed the ID from the SQL string and adding the External table key .

This is just my suggestion!

Thanks Corchi

ab · 2014-12-19 11:24:04

As an alternative, you can use raw SQL and the optimized SynDB* units to access the data, and define some SOA services, via interface-based services.
Ensure you did read and understand http://synopse.info/files/html/Synopse% … l#TITL_106

SQlite3 virtual table mechanism expects the RowID to be an INTEGER.
Maintaining a ID/keys pairing in memory (or even better in an external SQLite3 file) is possible, we had this in mind since years, but maintaining the IDs in a separate table is not perfect either, and may lead to awfully slow performance, and potential synchronization breaking issue if the data is not persistent in synch between the main table and the ID correspondance table.
We may add a ID: integer column to the main table. Or create a new dedicated table for mapping (allowing a JOINed query on the MSSQL DB directly). But I suppose you would not like to change the data layout.

corchi72 · 2014-12-22 11:55:01

"Maintaining a ID/keys pairing in memory (or even better in an external SQLite3 file) is possible, we had this in mind since years"

Table SQLite / Key external table (array of fields)
ID - COD,MAT,ECC...

is undoubtedly this would be the best (I can imagine other possible solutions) would be enough to run the post

(saving data referring to the key ID to the server mORMot and then from the server to external database would be enough to use as a key to the actual key of the outer table )

I would create a separate table mapping but when reading data in addition to reading the columns of the query "SELECT * FROM EXTERNALTABLE" add as request the list of fields that are key and add a property to the field type (KEYOrigin) then in mORMot would turn around as framework and only in the case of saving data I would write UPDATE EXTERNALTABLE fields () values () WHERE KEY is (array of fields That type is KEYOrigin)

anyway thanks for your work I think by all...
merry christmas
corchi

ab · 2014-12-22 21:54:59

My point is that a JOINed query or a query including a new "ID" column may be faster than an in-memory separated pairing.

mORMot Open Source

#1 2014-12-18 14:42:38

VirtualTableExternalRegister: the column name "ID" is invalid

#2 2014-12-18 15:40:15

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#3 2014-12-19 08:17:51

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#4 2014-12-19 08:55:52

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#5 2014-12-19 10:39:27

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#6 2014-12-19 11:24:04

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#7 2014-12-22 11:55:01

Re: VirtualTableExternalRegister: the column name "ID" is invalid

#8 2014-12-22 21:54:59

Re: VirtualTableExternalRegister: the column name "ID" is invalid

Board footer