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

larand54 · 2025-12-19 16:08:03

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)

ab · 2025-12-19 18:06:14

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

larand54 · 2025-12-19 20:34:11

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

ab · 2025-12-20 09:21:46

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 · Yesterday 17:44:22

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 (Yesterday 17:46:13)

mORMot Open Source

#1 2025-12-19 16:08:03

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

#2 2025-12-19 18:06:14

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

#3 2025-12-19 20:34:11

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

#4 2025-12-20 09:21:46

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

#5 Yesterday 17:44:22

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

Board footer