mORMot Open Source

negativethirteen

Member

Registered: 2012-07-20

Posts: 12

Accessing external MySQL Database using Virtual Table

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.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Accessing external MySQL Database using Virtual Table

You should NOT instantiate TSQLRestClientURI but one of its sub-class, depending on the communication protocol used.

See the "Client-Server" section of the SAD document, and the corresponding samples provided in the source code.

negativethirteen

Member

Registered: 2012-07-20

Posts: 12

Re: Accessing external MySQL Database using Virtual Table

I updated mORMot to the latest leaf and that fixed the problem.
Maybe the older version I was using had some issues with OLEDB provider for ODBC

And with that, the final piece of the puzzle on the DB side of my project falls into place.

Your framework is amazing! Thanks!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Accessing external MySQL Database using Virtual Table

BTW, I would never advice to use OleDB over ODBC for MySQL access...
I would recommend using TSQLDBZEOSConnectionProperties and direct  libmysql.dll access with Zeos/ZDBC 7.2.
Or directly ODBC, which performs well (with the problem of very high latency when freeing the connection, due to a bug in the connector 5.2.6 and 5.3.1 - dead slow in ODBC.FreeHandle - seems not to be tied to SynDB: it is visible on ODBC Data Source tool).
There is no need to use OleDB over ODBC.

See http://blog.synopse.info/post/2014/03/0 … PostgreSQL