mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

RowID is an invalid identifier for Oracle Tables

I've finally managed to get a very simple ORM application to connect to my Oracle database with the following code:

  Props := TSQLDBOracleConnectionProperties.Create('OPFTEST','OPF_MORMOT','xxxx',GetACP);
  Model := CreateSampleModel; // from SampleData unit
  VirtualTableExternalRegister(Model,TSQLSampleRecord,Props,'SampleRecord');
  Client := TSQLRestClientDB.Create(Model, nil, 'test.db3', TSQLRestServerDB);
  Client.Server.CreateMissingTables;

Then when the CreateMIssingTable is executed, an "Invalid Identifier" error returns from the Oracle 10g server.  have found out that this is caused by the field ROWID that mORMot adds to the table definition ... obviously ROWID is a reserved word in Oracle world.

It also struck me that this fieldname, which is used extensively throughout the whole framework, is not defined as a constant ...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: RowID is an invalid identifier for Oracle Tables

Ensure that you get the latest versions from http://synopse.info
I've just fixed several issues about Oracle connection and parameter handling.

About RowID, I did not notice it.
Reference is http://docs.oracle.com/cd/E11882_01/ser … SQLRF00254
Code modification needed here!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: RowID is an invalid identifier for Oracle Tables

Some fixes made:
  *  added ReplaceRowIDWithID optional parameter to GetJSONObjectAsSQL(), in order to allow working with external DB not allowing RowID (e.g. Oracle);
  *  changed column named 'RowID' into 'ID' since it is reserved e.g. in Oracle.

See http://synopse.info/fossil/info/ffe496a829

Certainly not the end of all fixes related to external ORM and Oracle, but a start!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: RowID is an invalid identifier for Oracle Tables

See also the just committed huge code refactoring and set of fixes, mostly related to:
- Oracle handling of BLOB / CLOB fields and Unicode columns (NCLOB/NVARCHAR2);
- mORMot access of external Oracle tables.

http://synopse.info/fossil/info/7bf4073f38