mORMot Open Source

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 516

Problem to create Query Params for MS-SQL RetrieveDocVariantArray

Hi Arnaud,

i try to select some SQLRecord from External MS-SQL Database.
we wan't to select all record where the field (Messzeitpunkt : TDateTime) = date

i tried a lot of valid ms-sql statements, but every time the mORMot Parser makes errors:

Version1:
whereStatement = convert(date, GETDATE()) = convert(date, Messzeitpunkt)

20190801 22095955  2 warn    mORMotDB.TSQLRestStorageExternal(02c45990) TSQLRestStorageExternal.AdaptSQLForEngineList: statement too complex -> would use SQLite3 virtual engine [SELECT ID,CreationDate,LastEditDate,AuftragStartEventID,Gruppierung,Ursprung,IPAdresse,Port,Messzeitpunkt,Barcode,Drehmoment,DrehmomentErgebnis,Drehwinkel,DrehwinkelErgebnis,GesamtErgebnis,Daten,SpindelTag,ControllerName,TighteningID,TorqueUnits,BatchCount,BatchSize,BatchStatus,CountOfScrewSteps,Screwprogram,Torquehighlimit,Torquelowlimit,Anglehighlimit,Anglelowlimit,ResultUniqueID,EORTimeStamp FROM EOR_QSYSOpenProtocolRecord WHERE convert(date, GETDATE()) = convert(date, Messzeitpunkt)]

Verstion 2:
whereStatement = CONVERT(nvarchar(max), messzeitpunkt, 23)=?, ['2019-08-01']
Error:
mORMotDB.TSQLRestStorageExternal(02ab5990) TSQLRestStorageExternal.AdaptSQLForEngineList: unsupported function CONVERT() for [SELECT ID,CreationDate,LastEditDate,AuftragStartEventID,Gruppierung,Ursprung,IPAdresse,Port,Messzeitpunkt,Barcode,Drehmoment,DrehmomentErgebnis,Drehwinkel,DrehwinkelErgebnis,GesamtErgebnis,Daten,SpindelTag,ControllerName,TighteningID,TorqueUnits,BatchCount,BatchSize,BatchStatus,CountOfScrewSteps,Screwprogram,Torquehighlimit,Torquelowlimit,Anglehighlimit,Anglelowlimit,ResultUniqueID,EORTimeStamp FROM EOR_QSYSOpenProtocolRecord WHERE CONVERT(nvarchar(max), messzeitpunkt, 23)=:('2019-08-01'):]

do you have an idea how to get the desired records ?

Is it a bug or do i use it wrong ?

---

Rad Studio 12.1 Santorini

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: Problem to create Query Params for MS-SQL RetrieveDocVariantArray

I think that DateTimeToSQL and DateToSQL functions (SynTable module) can help you to build proper where clause.
I don't know, whether it is the best way. I would be pleased to know an easier way