mORMot Open Source

I've searched the forum for a way to deal with SYSTEMNOCASE collation outside the mORMot Framework and haven't had much luck.

I'd like to be able to load/connect to the SQLite file with 3rd-party software for reporting (e.g. connecting through ODBC driver with LibreOffice Base or Microsoft Access).
Connecting works, the problem is I can't generate reports that use collation because SYSTEMNOCASE is not recognized.

Is there a way around this?

I have a TSQLModel with multiple TSQLRecord and TSQLRecordExternal.
The idea is to have the option to switch between a local SQLite3 DB and an external DB on the fly.

I get an access violation when I try to create the client (and it's stand-alone server) when there are multiple TSQLRecordExternal in the TSQLModel.

MySQL := TOLEDBODBCSQLConnectionProperties.Create('', 'Local MySQL', '', 'user', 'password');
...
VirtualTableExternalRegister(Model, TSQLsn_log_ext, MySQL, 'sn_log');
VirtualTableExternalRegister(Model, TSQLfile_ext, MySQL, 'file');
Client := TSQLRestClientDB.Create(Model, Model, 'test.db3', TSQLRestServerDB, false);
Client.Server.CreateMissingTables(0);
...

Everything works if I use only 1 TSQLRecordExternal per TSQLModel.
Is this intended? I dont remember reading anything in the SAD explicitly saying that TSQLModel can only handle 1 TSQLRecordExternal and I assumed the TSQLRecordExternal are handled the same way as TSQLRecord, that is, one can have multiple TSQLRecordExternal same as having multiple TSQLRecord.
Am I using VirtualTableExternalRegister wrong?
Should I move my individual tables to separate TSQLModels or is there another way to do this?

Thanks in advance.

I'm trying to access an external MySQL database through OLE for ODBC and the ODBC Driver for MySQL.
I can connect directly using

MySQL := TOLEDBODBCSQLConnectionProperties.Create('MSDASQL.1','Local MySQL', '', 'login', 'password');
  try
    Conn := MySQL.NewConnection;
    try
      Query := Conn.NewStatement;
      try
        Query.Execute('select * from test.table_name where id > 1 and id < 100', true, []);
        F := TFileStream.Create(ChangeFileExt('result', '.json'), fmCreate);
      try
        Query.FetchAllToJSON(F, true);
      finally
        F.Free;
      end;
      finally
        Query.Free;
      end;
    finally
      Conn.Free;
    end;
  finally
    MySQL.Free;
  end;

and this gives me the proper results in the JSON file.

However, I would like to use the ORM with the external database but I can't seem to get it to work.
I followed the instructions in the SAD and the example in SynSelfTest and have the following:

  MySQL := TOLEDBODBCSQLConnectionProperties.Create('MSDASQL.1', 'Local MySQL', '', 'login', 'password');

  ...

    ModelExt := TSQLModel.Create([TSQLtable_name_ext], 'rootext');
    VirtualTableExternalRegister(ModelExt, TSQLtable_name_ext, MySQL, 'test.table_name');
    Database := TSQLRestServerDB.Create(ModelExt, ':memory:', true);
    TSQLRestServerDB(Database).CreateMissingTables(0);

    Client := TSQLRestClientURI.Create(ModelExt);

  ...

ModelExt contains TSQLtable_name and TSQLtable_name_ext, they are identical except TSQLtable_name_ext is a TSQLRecordExternal
I might be doing something wrong with the TSQLRestClientURI.
I also have another version of the code that runs an HTTP server that serves both to SQLite3 DB and attempts to serve to an external MySQL DB through virtual tables.
The SQLite3 stuff work flawlessly.

The SAD is a little confusing to me and I only had these sections to go with:

Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','AdventureWorks2008R2','',''); 
Model := TSQLModel.Create([TSQLCustomer],'root'); 
VirtualTableExternalRegister(Model,TSQLCustomer,Props,'Sales.Customer');

Snippet from SynSelfTest

var RInt: TSQLRecordPeople; 
RExt: TSQLRecordPeopleExt; 
(...) 
fConnection := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','',''); 
VirtualTableExternalRegister(fModel,TSQLRecordPeopleExt,fConnection,'PeopleExternal'); 
fClient := TSQLRestClientDB.Create(fModel,nil,'test.db3',TSQLRestServerDB); 
fClient.Server.StaticVirtualTableDirect := StaticVirtualTableDirect; 
fClient.Server.CreateMissingTables; 
(...)

I am getting the same error when running SynDBExplorer

OLEDB Error 80040E21 - Multiple-step operation generated errors. Check each status value. Status[9] = Bad ordinal

When using the following settings:

Generic OLEDB
Microsoft OLEDB Driver for ODBC
Local MySQL (this is the data source I created for the MySQL database, same one used for the direct connection above that works)

Am I missing something in terms of how the external database ORM through virtual tables is done?
Or is this an issue with the ODBC Driver for MySQL or my MySQL setup?
Thanks in advance, and great framework.