mORMot Open Source

houdw2006

Member

Registered: 2015-05-23

Posts: 48

What is the correct way to Create Index for External DB?

I am trying to create an index for table in MSSQL2018.  I use the following code to do it, everything is ok till I try to execute the SQL statement:

    vSuccess := vRestServer.Execute(vSQL);

The SQL Statement is correct, and I can execute it in  the Microsoft SQL Server Management Studio 2017 correctly.

Can anybody give me some instruction to do it in the right way?

Thanks In advance.

procedure TFormDBServer.CreateIndexIDC;   // Create Index for column 'IDC'
var
  vSQL: string;
  vModel: TSQLModel;
  vSuccess: Boolean;
  vRestServer: TSQLRestServer;
  vDialog: TConnectExternalDB;
  vProps: TSQLDBConnectionProperties;
begin
  vModel := CreateDataModel('root');
  vDialog := TConnectExternalDB.Create(Self);   // unit SynDBExplorerClasses;
  try
    if vDialog.Connect(vModel, vProps) then     // vProps.DBMS --> dMSSQL
    begin
      vSuccess := VirtualTableExternalRegisterAll(vModel, vProps);
      vRestServer := TSQLRestServerDB.Create(vModel, vProps.DatabaseName);
      try
        with vRestServer do
        begin
          CreateMissingTables;
          vSQL := vProps.SQLAddIndex('CardAppInfo', ['IDC'], False, False);   // vSQL  --> CREATE INDEX NDXCardAppInfoIDC ON CardAppInfo(IDC)
          if (vSQL <> '') then
            vSuccess := vRestServer.Execute(vSQL);  // Error prompt:  Virtual Table may not be Indexed, extended_error=1
        end;
      finally
        FreeAndNil(vRestServer);
      end;
    end;
  finally
    FreeAndNil(vDialog);
    FreeAndNil(vModel)
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: What is the correct way to Create Index for External DB?

1. Please don't post huge set of code directly in the forum, as stated by the forum rules.
See https://synopse.info/forum/misc.php?action=rules

2. The proper ORM-oriented way to do this is to override the class procedure TSQLRecord.InitializeTable virtual method, and add a call to the index creation there.
See https://synopse.info/files/html/Synopse … #TITLE_515

houdw2006

Member

Registered: 2015-05-23

Posts: 48

Re: What is the correct way to Create Index for External DB?

Thank you for your kindness and prompt answer, AB. really.

By the way , the rule for posting object pascal code, is a little bit hard to follow. How much is some, and how much is huge? It might be not only me, but also for some others, especially for the newcomers, are puzzled by the rule.  I think it will be helpful to make the rules stated more clearly, such as no more than 25 lines or no more than 1KB of code size, or something else.