MSSQL Server in mORMot2

sakura · 2021-04-29 08:47:05

Okay, all I have done so far was easy access using TRestServerDB and SQLite3. I know, that plugging in MSSQL is supposed to be just as easy, but I am stumped at this moment and can't see how to do it, preferably w/o ZEOS. Which RestServer-Class would I use and how would a connect it to the SQL Server?

Thanks for the quick hint,
Daniel

wloochacz · 2021-04-29 09:54:02

sakura wrote:

Okay, all I have done so far was easy access using TRestServerDB and SQLite3. I know, that plugging in MSSQL is supposed to be just as easy, but I am stumped at this moment and can't see how to do it, preferably w/o ZEOS.

It seems that ZEOS is not the best solution to connect to MSSQL, especially since AB has provided an excellent implementation for OLE DB.
In contrast, OLE DB itself is the most recommended and future-proof way to connect to MSSQL at this time.

sakura wrote:

Which RestServer-Class would I use and how would a connect it to the SQL Server?

Of course, the TOleDBMSSQL2012ConnectionProperties class from the SynOleDB unit.

However, you can try my trivial solution which uses Microsoft OLE DB Driver for SQL Server, which is dedicated for MSSQL 2012 R2 and later.
mORMot uses an older solution, namely SQL Server Native Client for OLEDB.
Of course, you must first download and install this driver on your computer:
https://www.microsoft.com/en-us/downloa … x?id=56730

And here's the whole unit for the connection using Microsoft OLE DB Driver for SQL Server, which I'm using at the moment developing a brand new server based on mORMot:

unit dFLEX.SynOLEDB.MSOLEDBSQL;

interface

uses
  SynOleDB;

type
  /// OleDB connection properties to Microsoft SQL Server 2012 R2 and later, via
  // SQL Server OLE DB driver (Microsoft OLE DB SQL / MSOLEDBSQL)
  TOleDBMSSQL2018ConnectionProperties = class(TOleDBMSSQL2012ConnectionProperties)
  protected
    /// will set the appropriate provider name, i.e. 'MSOLEDBSQL'
    // - will leave older 'SQLNCLI11' or 'SQLNCLI10' on Windows XP
    procedure SetInternalProperties; override;
  end;

implementation

{ TOleDBMSSQL2018ConnectionProperties }

procedure TOleDBMSSQL2018ConnectionProperties.SetInternalProperties;
begin
  inherited SetInternalProperties;
  fProviderName := 'MSOLEDBSQL';
end;

end.

PS.
Sorry, what I wrote applies to mORMot 1.18
But for 2.0 it will be identical, except that it will no longer be a SynOleDB unit.

Last edited by wloochacz (2021-04-29 10:00:45)

sakura · 2021-04-29 10:07:35

Oh yes, in mORMot2 we already have TSqlDBOleDBMSSQL2012ConnectionProperties, however, I am stumped right now, how to connect that with which TRestServer implementation. As we say in German, I ain't seeing that forest for all the trees here...

Last edited by sakura (2021-04-29 10:09:50)

ab · 2021-04-29 10:23:21

I have just committed TSqlDBOleDBMSSQL2018ConnectionProperties for mORMot 2.

Hope it helps.

sakura · 2021-04-29 11:00:57

I guess I had the wrong idea about how to do that. Am I right, that I still have to use the TRestServerDB implementation, which internally uses SQLite3, but simply register the model table for MSSQL using VirtualTableExternalRegister/VirtualTableExternalRegisterAll?

ab · 2021-04-29 12:43:15

Yes you need to use TRestServerDB to have the ORM SQL kernel based on SQLite3.
Then you register external tables using VirtualTableExternalRegister().
It will use direct SQL to MSSQL if possible, and fallback to SQlite3 virtual tables if the request is too complex - slower but more complete.

You can refer to the mORMot 1 documentation for this: it didn't change with mORMot 2.

sakura · 2021-04-29 12:48:36

Thanks, yeah, I saw this quite early, but my mind was set to doing it without any SQLite3 support, and this blocked me from seeing the way.

ab · 2021-04-30 10:41:56

If you have an external-only set of tables in your TOrmModel, you can just use an in-memory SQLite3 name for the main TRestServerDB.
And forget about SQLite3.

EgonHugeist · 2021-05-06 04:40:11

Hi sakura,

that's an example how you can connect to SQLServer with Zeos8:

OleDB:

TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI('OleDB[mssql]', '',''),
              Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=zeoslib;Data Source=K95VM-Egon\SQLEXPRESS2012;MarsConn=Yes;Initial File Name="";Server SPN="", DBUser, DBPassword)

or
ODBC_W:

TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI('odbc_w[mssql]', '',''),
              'DRIVER=SQL Server Native Client 11.0;Server=(localdb)\ProjectsV13;DataBase=zeoslib;Trusted_Connection=Yes;MARS_Connection=yes', '', '')

Note the protcol-name prefix. I'll change that later, propably -> users can specify the server and choose the protocol. Like 'mssql[FreeTDS]' or mssql[OleDB] etc.
We've also been testing the SynOLEDB implementation. AB's implementation has no native Batch bindings and no (IIRC) server side prepared statements. Thus we've noticed the Zeoslib implementation is loads faster in practice. Btw. Both (Syn/Zeos) ODBC implementation are almost much slower than OleDB in practice. OleDB should be first choice if you use a windows service using SQLServer.

Make you own tests and share your findings..

Last edited by EgonHugeist (2021-05-06 04:50:20)

mORMot Open Source

#1 2021-04-29 08:47:05

MSSQL Server in mORMot2

#2 2021-04-29 09:54:02

Re: MSSQL Server in mORMot2

#3 2021-04-29 10:07:35

Re: MSSQL Server in mORMot2

#4 2021-04-29 10:23:21

Re: MSSQL Server in mORMot2

#5 2021-04-29 11:00:57

Re: MSSQL Server in mORMot2

#6 2021-04-29 12:43:15

Re: MSSQL Server in mORMot2

#7 2021-04-29 12:48:36

Re: MSSQL Server in mORMot2

#8 2021-04-30 10:41:56

Re: MSSQL Server in mORMot2

#9 2021-05-06 04:40:11

Re: MSSQL Server in mORMot2

Board footer