mORMot Open Source

chula

Member

Registered: 2014-06-18

Posts: 19

Newbie question for MSSQL connection

I have made it to work. Just want to make sure if I was doing right:-

In order to use MSSQL in ORM way, I must create an Embedded Sqlite3 DB as a bridge, and map each Sqlite3 table as virtual table to MSSQL Physical Table by VirtualTableExternalRegister()?

If it is the case, the virtual tables on Sqlite3 can be come from different back-end database servers? Can the Begin/End Transaction  still work?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Newbie question for MSSQL connection

Yes, SQLite3 is used as a "bridge", and by-passed for most queries.

You can let all tables be defined as external by using VirtualTableExternalRegisterAll().
So all tables are virtual, so you can safely use SQLITE_MEMORY_DATABASE_NAME constant as alias to ':memory:' for the SQLite3 main database.

The transaction will still work as expected.
But to be safer and more efficient, if you use remote CRUD operations (from a client), to use a BATCH process with BatchStart() and a AutomaticTransactionPerRow non zero value (e.g. 5000) to create a transaction on the server side.

chula

Member

Registered: 2014-06-18

Posts: 19

Re: Newbie question for MSSQL connection

Very nice! Thanks for your effort

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: Newbie question for MSSQL connection

Which part of sample "13 - StandAlone JSON SQL server" msut I modify in order the same application work for my local MS-SQL server? I just cant understand how this can happen from Documentation.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Newbie question for MSSQL connection

If what you want is remote access, consider using SynDbRemote.pas instead.

For the sample, use the right TSqlDbConnectionProperties as detailed in the doc.

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: Newbie question for MSSQL connection

Thank you very much, I am in the stage where I am trying to unterstand the mormot framework way of think. Well, now I am on the db connection and my main difficulty, except my "transfer" from thinking in relational model  to object model, is to understand db connections. In old-fashion way we have a datamodule a connection object (ADO,DBEXpress or FireDAC) and dozens of datasets, which are all connected toogether. Now they all gone and their place took some methods and objects which I must understand their use and how these will replace the old "dataset-thinking".

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Newbie question for MSSQL connection

Did you read the corresponding chapter of the documentation?
See http://synopse.info/files/html/Synopse% … ml#TITL_27

Please take the time to read the information, related to SynDB.pas and mORMotDB.pas.
To connect to MSSQL, the corresponding TSQLDBConnectionProperties class is TOleDBMSSQLConnectionProperties.
There is a sample of connection string in 8.1.6. ISQLDBRows interface.

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: Newbie question for MSSQL connection

Based on what the article describe I am trying to modify the sample "16. Execute SQL via services"  to connect to an SQL Server using instead of SQL authentication the windows one. Just adding a new property in class TProjectSettings isn't enough (boolean winauth). Where it is supposed to be my next implementation?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Newbie question for MSSQL connection

Consider using SynDBRemote.pas if you need a remote connection to a DB.

But to support Windows authentication, you could use the mORMot SOA services, as with sample 16.
See http://synopse.info/files/html/Synopse% … l#TITL_121

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: Newbie question for MSSQL connection

I have 2 views with identical number and column names. Can I create one object (TSQLRecord) for the 2 views and each time I want data from the views I have to give the name of the view in a variable?

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: Newbie question for MSSQL connection

Hi, sorry I posted here but I can't create a new post, I don't know why. My question is about sample 30 MVC. I was convert db connection in order my connect will be on my local 2008 R2 server. My sample was compile successfully but in runtime 
an error raise on method aServer.CreateMissingTables on MVCFirebird.exe and the message is
'[FireDAC][Phys][IB]Unable to complete network request to host "tdserver:3050".
Failed to establish a connection.
No connection could be made because the target machine actively refused it.'. where tdserver is my instance of SQL server.

I was try to find where is defined that port but I can't. It seems trying creating the tables on SQLlite3 instead of MSSQL, can you help?