mORMot Open Source

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.

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.

Hmmm... Just so we understand each other correctly, you are referring to the table count limitation for the TSQLRecord class and not TSQLRecordHistory?

OK, I understand.
When I remove this limitation, in what cases can this mechanism cause problems?

If I want to have change history tracking for more than 64 tables then how can I solve it differently?

I'll just add that I'm not keeping it all in one History table, but in several.
Also, I used what you wrote AB in the documentation: 7.3.6 Redirect to an external TSQLRest
And I delegated the history to a separate instance of TSQLRestServerDB which works with a SQLite database.
Perhaps the target will be more instances and other beeps, we'll see; my data model is large and complex...
I also have a lot of data changes (much, much more than a classic ERP system), but I MUST keep track of change history for all data.

OK, I see.
Since such a call is invalid, don't you think an exception should be raised there?

procedure TSQLRecordPropertiesMapping.Init(Table: TSQLRecordClass;
  const MappedTableName: RawUTF8; MappedConnection: TObject;
  AutoComputeSQL: boolean; MappingOptions: TSQLRecordPropertiesMappingOptions);
begin
  // I added such a private field to protect against re-initialization.
  if fInitialized then
    Exit // raise exception?
  else
    fInitialized := True;

  // and then the appropriate initialization code  
end;

Of course I would prefer it to work as I wrote above
But if the convection is that such a call is invalid, then IMO an exception should definitely be raised there.
Otherwise you can hit some really weird problems, don't you think AB?

Yes, you're right and I noticed that earlier, but I forgot about it while writing the database ORM class generator.
This is easily fixed by moving the IdAtom field declaration to the public section.
Then everything looks good to mORMot, and I achieve the expected effect.

But what about the mapping problem I described above?
Can you confirm if the problem exists, AB?

A property named TypeKW has been declared in the class, which corresponds to a field in the table named Type.
Also, I don't see any typo in my post.

.MapField('TypeKW', 'Type')

This notation maps a class property named Type to a database table field named TypeKW.
That is, exactly the opposite of my example.
Besides, you can't declare a class property named Type because it's a Delphi/Pascal keyword.

vs
.MapField('Type', 'TypeKW')

This is the correct notation for my example and mORMot generates such a SelectSQL:

select 
  IdAtom as ID,IdAtom, 
  Name, 
  Type as TypeKW 
from dbo.tDfRmAtom

So absolutely correct.

If I may AB, I would still like to ask you to answer a question:
How to check if there was any problem while updating the database for REST/ORM layer?

As for the rest, well - it looks like my conclusions are correct....
But that's a medium problem, I can handle it, but I need to understand exactly how it all works.

Well, I have powerful experience with relational databases and hence some background in my understanding.
I need to switch over ;-)
I've already dug through the forum and all the luck that you introduced nullable types

ORM in mORMot is a new thing for me (I still think that relation handling in mORMot ORM is... questionable), and since we are rewriting Server, so I want to do it properly and according to mORMot philosophy.

Well, I'm still researching, testing and learning.
Just learned that for the Cache to work on the server side is not as transparent as you write about it in the manual.
I can't do CreateAndFillXXX for example, and by using Retrieve methods.

There are really a lot of such cases, so thank you very much for your answers.

Let's keep going!

Understood.
So it is also impossible in this case to assign NULL value for IdUser field?

And is there any workaround for the problem?
OnUpdate event or something similar?

And please, tell me how to check if there was any problem while updating the database?
Right now simply the Update method returns False, but I don't know why this command failed....

OK, perhaps we'll come back to this.
But first I need to get competent in ORM, so dear AB I will definitely have questions

Yes I know that, I read the documentation
However, its compatibility with classic TQuery and further with TParam/TField is hmm... debatable.

Personally I think that naming data types identically as in DB module, but which however provide different contract and work differently, introduces more harm than good.

I understand the idea, but...
Besides, TQuery is exactly what e.g. FIreDAC lacks; a lightweight simple class, serving primarily to read data as efficiently as possible without the unnecessary overhead of TDataSet.

I wanted to extend it a bit and maybe also propose some changes for the benefit of the community.
But for that I need to have virtual methods in TQuery.

Exactly.
But not the way it works in every other DAC for Delphi that I know of.
And that's just the point.

Perhaps.
However, it's so different, and I have too little time to discern the topic thoroughly, that I don't dare touch it without (your AB? ) support.

But OK; where should I look to implement something similar for native SynDB classes?

I had a similar opinion when I started doing this, and this is exactly how it was implemented and can still be used.

However, various use cases in a real project showed that such a solution has limitations and is not very convenient to use. That's why SQLParser was aggregated into TQuery class. And that at first it was an independent class, that's why I provided communication with TQuery using dedicated interface (and precisely - if TQuery inherited from TInterfacedObject or even from TSingletonImplmentation it wouldn't be bad, but it's not necessary).

The limitation I came across is that it needs to remember the state of WHERE clauses for an instance of TQuery.
SQLParser has methods like Save/RestorePrimaryWhere so that when you modify the WHERE at runtime, you can simply and quickly return to the original query.

And besides, the syntax I showed is just convenient, and the overhead is practically negligible, also because SQLParser is created by Query only when it is needed. You don't use the parser - it won't be created.

OLEDB is Windows specific only.
For Linux, You must use ODBC, i guess...

Thank You, AB

Yes, indeed - you may use just just FireDAC Phys Layer.
FireDAC Phys Layer Samples

You may execute or fetch data form server without TDataSet.

OK, ISQLDBRows is really fast and really simple.

I'm power user for AnyDAC/FireDAC, and I'm sorry - this is not true.

AnyDAC / FireDAC is very fast. But you have to be able to use it, which is not so obvious.
It has really extensive capabilities (macros and SQL preprocessor with the registration of its own functions? No problem!), Which can cause confusion for beginners and even experienced users.

I use my own version of FireDAC, which I have equipped with many unique features, eg built-in SQL parser/builder in TFDQuery.
Which allows me, for example, to change the WHERE clauses in this way:

    // qReport is a TFDQuery
    // Add to where clause: (DataStart >= :DataStart and DataStart <= :DataStart)
    qReport.SQLParser.Where.Clear.
      .BeginGroup()
        .Add('DateStart', coAnd, opGreaterEqual)
        .Add('DateStart', coAnd, opLessEqual)
      .EndGroup();
    // Commit changes to SQL and prepare Query
    qReport.SQLParser.CommitSQL([RepFrom, RepTo], [ftDateTime, ftDateTime]);

Or this way:

function TSopSalesOfferProdCalcLogic.GetdsTppTechRoutePattern: TboTppTechRoutePattern;
begin
  // TboTppTechRoutePattern is a strong typed TFDQuery
  if not Assigned(FdsTppTechRoutePattern) then
  begin
    FdsTppTechRoutePattern := TboTppTechRoutePattern.Create(Self);
    FdsTppTechRoutePattern.SetFastForward(True, False, 15);
    // modify Select SQL for fetch one row by Primary Key value
    FdsTppTechRoutePattern.SQLParser.Clear.Where.Add(FdsTppTechRoutePattern.PKFields);
    FdsTppTechRoutePattern.SQLParser.CommitSQL();
  end;
  Result := FdsTppTechRoutePattern;
end;

In fact, you are right and I thank you very much for your time.
Sorry for confusion, too much code, too little sleep: /

However, I lost a lot of time, because the exception that mORMot reports is far from the cause of the problem ...
Can we do anything about it in this case?

PS.
In my opinion, if a method returns an object by out, it should create it by the default constructor. Always.
The mORMot is exactly the opposite.
Of course, if you have adopted this convention then there is nothing left to do with it.

OK, how to control it?
How to setup server to any client (Delphi program or real REST Client), get the same JSON content?

This is not true.
Query like this:

select distinct top 10 i.ItemCode from tIvItem i

Work like a charm.

But this query:

select top 10 distinct i.ItemCode from tIvItem i

Has incorrect syntax on MSSQL.

Hmm... just the long shot (WebSockets based on Indy):
https://github.com/andremussche/DelphiWebsockets

Maybe XE bug - i don't know...
But, there is workaround (SynCommons.pas):

class TSynCache
property property Count: integer read fNameValue.Count;

I changed to:

property Count: integer read GetCount;

Where GetCount is:

function TSynCache.GetCount: integer;
begin
  Result := fNameValue.Count;
end;

And now i'ts working!
Arnaud can you confirm this?

I don't think so ;-)
Please check yourself, here you can download the package:
http://www.dflex.pl/dflex-ftp/mORMot_Package.zip

Strange...

Delphi XE Pro

No changes...

Unfortunately, no.
Delphi raise error with SynCommons.pas(1)
What is total bull...t, because the first line of the SynCommons.pas does not have any error...

Unfortunately, then exactly the same error.