mORMot Open Source

tdanop

Member

Registered: 2015-01-16

Posts: 27

MVC Server sample with MSSQL db

Hi,

I am testing the MVCServer with Delphi XE5 Update 2, MSSQL 2014 as back-end Server and FireDAC 19.0.14356 as a connection driver.

When I start the project (in debug mode) I am getting the following error:

First chance exception at $75F0C42D. Exception class EFDException with message '[FireDAC][Phys][MSSQL]-335. Parameter [EA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call'. Process MVCServerMSQL.exe (2884)

When I skip this error the server starts "normally"  but when I try to connect with http://localhost:8092 I am getting the JSON output
{
"errorCode":500,
"error":
{"EMSSQLNativeException":{"EMSSQLNativeException":"[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'distinct'."}}
}

Can you help?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: MVC Server sample with MSSQL db

What is the SQL executed?
(enable logs for see them)

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: MVC Server sample with MSSQL db

On the first exception the error is coming from the fact that the parameter in a insert query when you execute prepare is of unknown type :

SQL   	SynDBFireDAC.TSQLDBFireDACStatement(0244AD10) insert into dbo.Author (ID,CreatedAt,ModifiedAt,LogonName,FirstName,FamilyName,BirthDate,Email,HashedPassword,Verified,Rights) values (1,135393478062,135393478062,'synopse',NULL,'Synopse','',NULL,'aa4aecd02efeaab94433ff1eae7479a43bcfa5e685e8c97f7c321ffe57d23daf',1,15)
20170911 08224822 EXC   	EFDException ("[FireDAC][Phys][MSSQL]-335. Parameter [EA] data type is unknown. Hint: specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call") at 007E1D22  stack trace API 00592973 0059299C 

The second one is coming from the SQL statement in GetViewInfo:

  "if not fDefaultData.AddExistingProp('archives',result) then
    fDefaultData.AddNewProp('archives',RestModel.RetrieveDocVariantArray(
      TSQLArticle,'','group by PublishedMonth order by PublishedMonth desc limit 100',[],
      'distinct(PublishedMonth),max(RowID)+1 as FirstID'),result)"

I think the limit 100 (TOP in MSSQL) is invalid if you combine it with distinct, may work with Postgress but not in MSSQL.

I remove the "limit 100" from the query and was execute fine in MSSQL.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: MVC Server sample with MSSQL db

So it is a MSSQL limitation.

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: MVC Server sample with MSSQL db

Yes it is a MSSQL limitation .

In the first case I would like to know how can I define the type of parameter according to field types of insert statement?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: MVC Server sample with MSSQL db

I don't understand which parameter is faulty.

tdanop

Member

Registered: 2015-01-16

Posts: 27

Re: MVC Server sample with MSSQL db

I can't find parameter with name [EA] which is specified in log file. There is no such a parameter name.

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MVC Server sample with MSSQL db

SQL Server support of "limit" is not a limitation. MSSQL uses "select top 100 * from..." instead of "select * from ... limit 100"

https://www.w3schools.com/sql/sql_top.asp

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: MVC Server sample with MSSQL db

@leus Not exactly.
In the above query, the ORM changed LIMIT into a TOP, as expected by MSSQL.

The MSSQL limitation is about TOP + DISTINCT.

wloochacz

Member

Registered: 2015-01-03

Posts: 45

Re: MVC Server sample with MSSQL db

@leus Not exactly.
In the above query, the ORM changed LIMIT into a TOP, as expected by MSSQL.

The MSSQL limitation is about TOP + DISTINCT.

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.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: MVC Server sample with MSSQL db

So how should the ORM generate the above query for MS SQL?

wloochacz

Member

Registered: 2015-01-03

Posts: 45

Re: MVC Server sample with MSSQL db

Sorry, I don't see any Select-SQL above, except my query ;-)
I see nonsense part of select (Both group by and distintct over PublishedMonth field? Why?) with incorrect limit (in MSSQL is TOP)  append to the end of select clause, which is incorrect on MSSQL.

Last edited by wloochacz (2017-09-13 14:00:24)

leus

Member

Registered: 2012-09-05

Posts: 79

Re: MVC Server sample with MSSQL db

So how should the ORM generate the above query for MS SQL?

I don't see the full query, but

SELECT DISTINCT TOP 10 ...

should work. It is worth noticing that DISTINCT is applied first and then TOP (as it should.) Also, DISTINCT applies to all columns in the query, after grouping.