mORMot Open Source

larand54

Member

Registered: 2018-12-25

Posts: 123

Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

I get the following Error message:

Project TestExtDBConnection.exe raised exception class EOleDBException with message 'TSqlDBOleDBConnection: OleDB error [] (0x80040E14) -   (line 1): Could not create constraint or index. See previous errors.
  (line 1): Column 'CustomerID' in table 'Customer' is of a type that is invalid for use as a key column in an index.'.

The ORM-table:

  TOrmCustomer = class(TOrm)
  private
    fName: RawUTF8;
    fCustomerID: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
    property CustomerID: RawUTF8 read fCustomerID write fCustomerID stored AS_UNIQUE;
  end;

Some code:

function CreateOSModel: TOrmModel;
begin
  result := TOrmModel.Create([TOrmCustomer]);
end;

var
  model: TOrmModel;
  props: TSQLDBConnectionProperties;
  RestServer: TRestServerDB;
begin
  model := CreateOSModel;
  Props := GetODBCDriverSpec.Create('MyServer', 'MyDB', '', '');

  VirtualTableExternalRegister(Model, TOrmCustomer, props, 'dbo.Customer');
  RestServer :=  TRestServerDB.Create(Model, ':memory:');
  RestServer.CreateMissingTables;             <<<---- Getting error here.

Last edited by larand54 (2025-12-19 23:47:21)

---

Delphi-11, WIN10 and Win11

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,367

Website

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

What is the table definition in the database? Check the CustomerID column type.
Which DB are you using?

larand54

Member

Registered: 2018-12-25

Posts: 123

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

I Use Microsofts sql server  and there is no table in the database it should have been created by "CreateMissingTables".

---

Delphi-11, WIN10 and Win11

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,367

Website

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

On MS SQL Server, you need to specify a length to the RawUtf8 field, so that it creates a VARCHAR field.
Currently, it creates a CBLOB which can't be indexed on MS SQL, by definition.
Look at the SQL generated to create the table and the index, and you will find it.

larand54

Member

Registered: 2018-12-25

Posts: 123

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

Ok, that was what I missed - the length.
Well after that I ran into next problem, It creates the index perfectly but if I close down the server and then start again, it gives the following error in 'CreateMissingTables':

0251225 17140140  ! ERROR mormot.db.raw.oledb.EOleDBException(02af0e90) {"EOleDBException(02af0e90)":{Message:"TSqlDBOleDBConnection: OleDB error [] (0x80040E14) -  
 (line 1): The operation failed because an index or statistics with name 'NDXCustomerCustomerID' already exists on table 'dbo.Customer'.\r\n",Statement:null}}
20251225 17140658  ! EXC   EOleDBException {Message:"TSqlDBOleDBConnection: OleDB error [] (0x80040E14) -  
 (line 1): The operation failed because an index or statistics with name 'NDXCustomerCustomerID' already exists on table 'dbo.Customer'.\r\n",Statement:null} [Main] at 01277edf   {24 65535.15 65535.28 12 15.6GB/31.3GB 23f00f01}

To have something to compare against I tok an example (\martin-doyle\04-InterfacedBasedServices) and made  the 'Name'-field unique. No problems there so I added another new table to the project, no problem there either.

Well then I decided to take my own table 'dbo.Customer' to the project. The two first tables worked as usual but my Customer-table still failed.

It look like this:

  TOrmCustomer = class(TOrm)
  private
    fName: RawUTF8;
    fCustomerID: RawUTF8;
    fCreateTime: TCreateTime;
    fModTime: TModTime;
  published
    property CustomerID: RawUTF8 index 50 read fCustomerID write fCustomerID stored AS_UNIQUE;
    property Name: RawUTF8 read fName write fName ;
    property CreateTime: TCreateTime read fCreateTime write fCreateTime;
    property ModTime: TModTime read fModTime write fModTime;
  end;

I have tried to debug but it is a bit too complex for me I think. At least I found that in the 'function TRestStorageExternal.CreateSqlMultiIndex' it fail's here:

    if not NeedDesc then
    begin
      // we identify just if indexed, not the order
      extfield := fFieldsInternalToExternal[IntFieldIndex[0] + 1];
      if (extfield >= 0) and
         (fFieldsExternal[extfield].ColumnIndexed) then
      begin
        result := true; // column already indexed
        exit;
      end;
    end;

The value of the field: 'ColumnIndexed' is 'false' , and therefore  it tries to create the index again.

I can't see no reason why this table fails where the other doesn't.

Need some help here.

Last edited by larand54 (2025-12-25 17:46:13)

---

Delphi-11, WIN10 and Win11

larand54

Member

Registered: 2018-12-25

Posts: 123

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

Very strange... I removed all tables from the database and then started the server again and all tables where created with index as they where before.
Then I closed the server and restarted again, now all tables gives error telling that: 'the index already exists'.

So... what I have I done wrong here? Without index everything works ok except I don't have any index on the tables.

---

Delphi-11, WIN10 and Win11

larand54

Member

Registered: 2018-12-25

Posts: 123

Re: Can not have a field with "Stored AS_UNIQUE" using the new OLEDB-drive

Problem now solved.
The error messages comes when the tables have no data, if you put data into the tables then there is no error about the "already existing index"

First run goes ok as the index does not exist and they will be created.
If the server are stopped before any data is stored on the table and then restarted again, the server checks if there is any existing index on the table it doesnt recognize that the table already got an an index so it try to create one again. But this time the database server (here MS SQL Server) finds an existing index and returns an error.

I wouldnt mind if this error message could be avoided in the future even if I never will forget it ;-)

---

Delphi-11, WIN10 and Win11