Pages: 1

#1 Re: mORMot 1 » Missing quotes in JSON service call » 2016-04-02 22:54:54

ncook

Thanks ab.

Your latest modification fixed it.

#2 mORMot 1 » Missing quotes in JSON service call » 2016-04-02 12:43:21

ncook
Replies: 2

I have a project that uses an interfaced based service.

Until recently the service was running smoothly, using a version of the mORMot libraries from several months ago. However when I upgraded to a recent version of the mORMot libraries (I've tried a few versions) I started getting the following error:
    TInterfacedObjectFakeClient.FakeCall(IMM_MonitorService_Log.Log) failed: '{ - "errorCode":406, - "errorText":"(sicClientDriven) execution failed (probably due to bad input parameters) for IMM_MonitorService_XXX.Log" - }'

I investigated and found that the new version of the libraries is sending JSON from the client to the server with no quotes around the keys. For example:
    ClassName : "TMonitorService_XXX"
instead of
    "ClassName" : "TMonitorService_XXX"
and it seems the server is rejecting this, producing the error above.

The JSON being sent from the client was generated by the framework as part of the automatic serialisation of a TObjectList decedant, which is first argument of the Log method on the IMM_MonitorService_XXX service.

My first and most important question is obviously "How do I fix this please?".

I have dug deeply into the code, and cant find any option on the client side that gives me a choice about how the JSON is generated. It seems that for sicClientDriven style services, twoForceJSONExtended is hard wired, and short of modifying the library code (which will break at the next update) I cant find a way to override that back to twoForceJSONStandard (which I think would put quotes around the keys).

My second question is almost as obvious: "Why is the client generating JSON that the server cant parse?"

I have followed the execution path in the server as follows:

TServiceFactoryServer.ExecuteMethod
  TServiceMethodExecute.ExecuteJson
    JSONToObject
      JSONObject
        TJSONSerializerRegisteredClass.Find
          JSONRetrieveStringField - Which ONLY works for "quoted" JSON strings (e.g. line: if P^<>'"' then exit;)

My third question is "Why is no one else finding the same problem?

From this I must conclude that I am stupid, and that I'm missing something really obvious. I wish I knew what it was.
Could you help me please?

#3 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-12 02:14:42

ncook

Yes!

Thank you Arnaud.

This has fixed the original problem of storing NULL's in place of empty string into Text & Memo fields, and now both of the additional techniques I detailed above (for storing NULL's into other field types when required) also work correctly with Jet.

Thank you again for your amazing work.

#4 Re: mORMot 1 » JSONToObject example - nested objects » 2013-12-11 22:17:48

ncook

Hi Arnaud.
I really like the new serialisation option for records. It seems to be simple and much less error prone that the older technique.

You said in the blog post that the supported Delphi data types were (boolean byte word integer cardinal Int64 single double TDateTime TTimeLog string RawUTF8 SynUnicode WideString).
Is that list of supported data types extendible by registering additional types in some way?

If it is then I guess we can add anything else we need. That's great. But if not, there is one more type that would make this 1000% more useful: TGUID.
So if we can't register additional types, is there any chance that TGUID could be added to the list of supported types please?

Regards
Neville

#5 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-09 22:58:36

ncook

Hi Arnaud.
Thanks for all the work you've done trying to fix this.

Unfortunately I have to report that it still has not fixed the error while trying to save NULLs in Jet.

The error message is the same. I'll include the whole log file from a single test run, in case you find something useful.

D:\Development\Projects\MM_Monitor\mORMot_Dev\mORMot_Dev.exe 0.0.0.0 (2013-12-10 07:44:09)
Host=NEVILLE-WIN7 User=Neville CPU=4*9-6-5898 OS=13.1=6.1.7601 Wow64=1 Freq=2760019
Environment variables=ALLUSERSPROFILE=C:\ProgramData	APPDATA=C:\Users\Neville\AppData\Roaming	asl.log=Destination=file	BDS=c:\program files (x86)\codegear\rad studio\5.0	BDSAppDataBaseDir=BDS	BDSCOMMONDIR=C:\Users\Public\Documents\RAD Studio\5.0	BDSPROJECTSDIR=D:\Users\Neville\Documents\RAD Studio\Projects	BDSUSERDIR=D:\Users\Neville\Documents\RAD Studio\5.0	CASSANDRA_HOME=D:\Development\Cassandra	CLASSPATH=.;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip	CommonProgramFiles=C:\Program Files (x86)\Common Files	CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files	CommonProgramW6432=C:\Program Files\Common Files	COMPUTERNAME=NEVILLE-WIN7	ComSpec=C:\Windows\system32\cmd.exe	Data Abstract=C:\Program Files (x86)\RemObjects Software\Data Abstract for Delphi	DELPHI=c:\program files (x86)\codegear\rad studio\5.0	EL_PATHS=;C:\Program Files (x86)\EurekaLog 6\Delphi11;C:\Program Files (x86)\EurekaLog 6\CBuilder11	Everwood=C:\Program Files (x86)\RemObjects Software\Everwood	FP_NO_HOST_CHECK=NO	HOMEDRIVE=C:	HOMEPATH=\Users\Neville	JAVA_HOME=C:\Program Files (x86)\Java\jre6	LOCALAPPDATA=C:\Users\Neville\AppData\Local	LOGONSERVER=\\NEVILLE-WIN7	NUMBER_OF_PROCESSORS=4	OS=Windows_NT	Pascal Script=C:\Program Files (x86)\RemObjects Software\Pascal Script for Delphi	Path=C:\ProgramData\Embarcadero\RAD Studio\9.0\bin;C:\Users\Public\Documents\RAD Studio\9.0\Bpl;C:\ProgramData\Embarcadero\RAD Studio\9.0\bin64;C:\Users\Public\Documents\RAD Studio\9.0\Bpl\Win64;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\CodeGear\RAD Studio\5.0\bin;C:\Users\Public\Documents\RAD Studio\5.0\Bpl;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\;C:\Program Files (x86)\Windows Live\Shared;C:\Program Files (x86)\RemObjects Software\Everwood\Bin;C:\Program Files (x86)\RemObjects Software\RemObjects SDK for Delphi\Dcu\D11;C:\Program Files (x86)\RemObjects Software\Pascal Script for Delphi\Dcu\D11;C:\Program Files (x86)\RemObjects Software\Data Abstract (Common)\Bin;C:\Program Files (x86)\RemObjects Software\Data Abstract for Delphi\Dcu\D11;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files (x86)\RemObjects Software\Oxygene\bin;C:\Program Files (x86)\Common Files\Acronis\SnapAPI\;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files (x86)\Git\cmd;;C:\Program Files (x86)\EurekaLog 6\Delphi11;C:\Program Files (x86)\EurekaLog 6\CBuilder11;C:\Users\Public\Documents\RAD Studio\5.0\Bpl	PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC	PROCESSOR_ARCHITECTURE=x86	PROCESSOR_ARCHITEW6432=AMD64	PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 23 Stepping 10, GenuineIntel	PROCESSOR_LEVEL=6	PROCESSOR_REVISION=170a	ProgramData=C:\ProgramData	ProgramFiles=C:\Program Files (x86)	ProgramFiles(x86)=C:\Program Files (x86)	ProgramW6432=C:\Program Files	PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\	PUBLIC=C:\Users\Public	QTJAVA=C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip	RemObjects SDK=C:\Program Files (x86)\RemObjects Software\RemObjects SDK for Delphi	RWDCP=C:\Users\Public\Documents\RAD Studio\5.0\Dcp	RWDCU=D:\Users\Neville\Documents\RAPWare\dcu	RWDSG=C:\Users\Public\Documents\RAD Studio\5.0\Bpl	RWLIB=D:\Users\Neville\Documents\RAPWare\Libraries	RWSRC=D:\Users\Neville\Documents\RAPWare\Source	SESSIONNAME=Console	SSH_AGENT_PID=8320	SSH_AUTH_SOCK=/tmp/ssh-TVvBjg8332/agent.8332	SystemDrive=C:	SystemRoot=C:\Windows	TEMP=C:\Users\Neville\AppData\Local\Temp	TMP=C:\Users\Neville\AppData\Local\Temp	USERDOMAIN=Neville-Win7	USERNAME=Neville	USERPROFILE=C:\Users\Neville	VS100COMNTOOLS=C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\Tools\	windir=C:\Windows
TSQLLog 1.18 2013-12-10T07:51:25

20131210 07512547  +    0011E3EF SynSQLite3.TSQLDatabase.DBOpen (3259) 
20131210 07512547  +    	TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   		PRAGMA cache_size=10000
20131210 07512547  -    	TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.173
20131210 07512547  -    0011E85B SynSQLite3.TSQLDatabase.DBOpen (3322)  00.000.298
20131210 07512547  +    TSQLDatabase(009669F0).0011DC58 SynSQLite3.TSQLDatabase.Execute (3031) 
20131210 07512547 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20131210 07512547  -    TSQLDatabase(009669F0).0011DCCF SynSQLite3.TSQLDatabase.Execute (3040)  00.000.042
20131210 07512547 debug TSQLDatabase(009669F0) TableNames=null
20131210 07512547  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   	BEGIN TRANSACTION;
20131210 07512547  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.007
20131210 07512547  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512547 SQL   	CREATE VIRTUAL TABLE NullTest1 USING External(fldText TEXT COLLATE SYSTEMNOCASE, fldMemo TEXT COLLATE SYSTEMNOCASE);
20131210 07512547  +    	TOleDBConnection(008D8A28).0011460C SynOleDB.TOleDBConnection.Create (1964) 
20131210 07512547  -    	TOleDBConnection(008D8A28).001146A1 SynOleDB.TOleDBConnection.Create (1971)  00.000.010
20131210 07512547  +    	TOleDBConnection(008D8A28).00114374 SynOleDB.TOleDBConnection.Connect (1927) 
20131210 07512607  -    	TOleDBConnection(008D8A28).00114551 SynOleDB.TOleDBConnection.Connect (1959)  00.363.041
20131210 07512607  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512607  -    	TOleDBStatement(00860370).CreateAccessor 00.000.123
20131210 07512607  +    	TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512607 DB    		TOleDBStatement(00860370) Total rows = 3
20131210 07512607  -    	TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.007
20131210 07512607  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512607  -    	TOleDBStatement(00860370).CreateAccessor 00.000.027
20131210 07512607  +    	TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512607 DB    		TOleDBStatement(00860370) Total rows = 1
20131210 07512607  -    	TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.004
20131210 07512607  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.369.387
20131210 07512607 SQL   TOleDBStatement(00860100) SELECT top 1 ID FROM tblNullTest1
20131210 07512607  +    TOleDBStatement(00860100).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512612  -    TOleDBStatement(00860100).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  00.073.808
20131210 07512612  +    TOleDBStatement(00860100).CreateAccessor
20131210 07512612  -    TOleDBStatement(00860100).CreateAccessor 00.000.015
20131210 07512612  +    TSQLDatabase(009669F0).0011DB46 SynSQLite3.TSQLDatabase.Execute (2898) 
20131210 07512612 SQL   	COMMIT TRANSACTION;
20131210 07512612  -    TSQLDatabase(009669F0).0011DBB6 SynSQLite3.TSQLDatabase.Execute (2907)  00.000.810
20131210 07512612  +    TSQLRestClientDB(00897DB0).TimeStamp
20131210 07512612  +    	TSQLRestServerDB(008C8840).root/TimeStamp
20131210 07512612 call  		TSQLRestServerDB(008C8840) TimeStamp
20131210 07512612 srvr  		TSQLRestServerDB(008C8840) GET TimeStamp -> 200
20131210 07512612  -    	TSQLRestServerDB(008C8840). 00.000.014
20131210 07512612 ret   	135137492185
20131210 07512612  -    TSQLRestClientDB(00897DB0).TimeStamp 00.000.073
20131210 07512612  +    TSQLRestServerDB(008C8840).root/NullTest1
20131210 07512612 SQL   	TOleDBStatement(00860370) select max(ID) from tblNullTest1
20131210 07512612  +    	TOleDBStatement(00860370).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512613  -    	TOleDBStatement(00860370).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  00.004.273
20131210 07512613  +    	TOleDBStatement(00860370).CreateAccessor
20131210 07512613  -    	TOleDBStatement(00860370).CreateAccessor 00.000.011
20131210 07512613 SQL   	TOleDBStatement(00860510) insert into tblNullTest1 (ID,fldText,fldMemo) VALUES (?OOR?,?OOR?,?OOR?)
20131210 07512613  +    	TOleDBStatement(00860510).00112E78 SynOleDB.TOleDBStatement.ExecutePrepared (1543) 
20131210 07512613 ERROR 		"EOleDBException(0096E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 00114BCB SynOleDB.EnhancedTest (2061) 00114CD3 SynOleDB.TOleDBConnection.OleDBCheck (2067) 0011320A SynOleDB.TOleDBStatement.ExecutePrepared (1606) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512749 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 00114BD3 SynOleDB.EnhancedTest (2062)  stack trace 00001007 System.SysFreeMem 00002211 System.@ReallocMem 00005C61 System.DynArraySetLength 000F6C9C SynCommons.TDynArrayHashed.HashFind (23504) 000F6C9C SynCommons.TDynArrayHashed.HashFind (23504) 000FCA2A SynCommons.TRawUTF8ListHashed.Changed (27752) 0000658B System.TInterfacedObject._AddRef 00002211 System.@ReallocMem 000F5F11 SynCommons.TDynArray.InternalSetLength (22907) 000F601F SynCommons.TDynArray.SetCount (22944) 00111844 SynOleDB.TOleDBStatement.CheckParam (1146) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 00172945 SynSQLite3Static.sqlite3_create_collation (1355) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 
20131210 07512750 ERROR 		"EOleDBException(0096E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 0011331D SynOleDB.TOleDBStatement.ExecutePrepared (1626) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512750 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 00114BD3 SynOleDB.EnhancedTest (2062)  stack trace API 00102EB8 SynCommons.SynRtlUnwind (32572) 00003B58 System.@HandleAnyException 0007A58B ExceptionLog.Call_HookedRaise (15678) 00114BD3 SynOleDB.EnhancedTest (2062) 00114CD3 SynOleDB.TOleDBConnection.OleDBCheck (2067) 0011320A SynOleDB.TOleDBStatement.ExecutePrepared (1606) 001BB27D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9427 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A1284 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24404) 001A26CC mORMot.TSQLRestServer.URI (24865) 001BE965 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CE56 mORMot.TSQLRestClientURI.URI (22688) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 
20131210 07512750  -    	TOleDBStatement(00860510).00113342 SynOleDB.TOleDBStatement.ExecutePrepared (1629)  01.598.803
20131210 07512750 srvr  	POST root/NullTest1 ERROR=400 (Bad Request)
20131210 07512750  -    TSQLRestServerDB(008C8840). 01.603.834
20131210 07512750 ERROR TSQLRestClientDB(00897DB0) POST root/NullTest1 returned 400 Bad Request with message  {  "ErrorCode":400,  "ErrorText":"Bad Request"  } stack trace API 0019CFDF mORMot.TSQLRestClientURI.URI (22718) 0019D2D6 mORMot.TSQLRestClientURI.EngineAdd (23018) 001A7730 mORMot.TSQLRestClient.Add (27483) 001C3015 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131210 07512750 info  TSQLRestServerDB(008C8840) {   "ClientsCurrent": 0,   "ClientsMax": 0,   "Invalid": 1,   "Responses": 1,   "Modified": 0,   "IncomingBytes": 172,   "OutcomingBytes": 132,   "OutcomingFiles": 0,   "ServiceCalls": 1,   "ProcessTime": "1.60s"  }
20131210 07512750  +    0011E31A SynSQLite3.TSQLDatabase.DBClose (3246) 
20131210 07512751  -    0011E374 SynSQLite3.TSQLDatabase.DBClose (3252)  00.019.212
20131210 07512751  +    TOleDBConnection(008D8A28).00114725 SynOleDB.TOleDBConnection.Destroy (1976) 
20131210 07512751  +    	TOleDBConnection(008D8A28).001147E7 SynOleDB.TOleDBConnection.Disconnect (1989) 
20131210 07512751  +    		TOleDBStatement(00860100).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860100) Total rows = 1
20131210 07512751  -    		TOleDBStatement(00860100).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.209
20131210 07512751  +    		TOleDBStatement(00860370).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860370) Total rows = 1
20131210 07512751  -    		TOleDBStatement(00860370).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.580
20131210 07512751  +    		TOleDBStatement(00860510).00113933 SynOleDB.TOleDBStatement.Destroy (1711) 
20131210 07512751 DB    			TOleDBStatement(00860510) Total rows = 0
20131210 07512751  -    		TOleDBStatement(00860510).001139B4 SynOleDB.TOleDBStatement.Destroy (1721)  00.000.036
20131210 07512761  -    	TOleDBConnection(008D8A28).0011486C SynOleDB.TOleDBConnection.Disconnect (2000)  00.155.461
20131210 07512761  -    TOleDBConnection(008D8A28).00114796 SynOleDB.TOleDBConnection.Destroy (1985)  00.155.469

I'm compiling and testing this using Delphi 2007 (obviously Win32) on a Win7x64 system. But I've also tried compiling & running the same code on a 32 bit WinXP VM and it still has exactly the same error.

I'll freely admit that I'm really a bit out of my depth down in the OleDB binding code, and while I'm slowly making sense of what it is doing, I'm not sure that I'm up to the level to spot a problem yet. I'm usually quite happy deep in technical code (much better than dropping components on forms) but having never been any deeper into database code than ADO before, OleDB is a steep learning curve for an old guy.
Still, if there's anything I can do to help (especially tests to run, etc.) then please just ask.
I wish I could work on this more today, but I'm committed to some other work for a client. So I'll get back to this in the evening.

Regards.

#6 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-09 13:32:15

ncook

Hi Arnaud.
I've been working through the code and trying to interpret what is happening.

I've found my way to this code in SynOleDB so far...

procedure TOleDBStatement.ExecutePrepared;
...
        case P^.VType of
        ftNull: begin
          // bind a NULL parameter
          B^.dwPart := DBPART_STATUS;
          B^.obStatus := PAnsiChar(@P^.VInt64)-pointer(fParams);
          P^.VInt64 := Ord(stIsNull);
        end;
...

which I gather may be relevant, and I am attempting to understand what it is doing.
I'm still working on that. I hope I'm looking in the right direction.

However during this process I found these two enumerations in SynOleDB...

type
  /// indicates whether the data value or some other value, such as a NULL,
  // is to be used as the value of the column or parameter
  // - see http://msdn.microsoft.com/en-us/library/ms722617(VS.85).aspx
  TOleDBStatus = (
    stOK, stBadAccessor, stCanNotConvertValue, stIsNull, stTruncated,
    stSignMismatch, stDataoverFlow, stCanNotCreateValue, stUnavailable,
    stPermissionDenied, stIntegrityViolation, stBadStatus, stDefault);
  /// binding status of a given column
  // - see http://msdn.microsoft.com/en-us/library/windows/desktop/ms720969
  TOleDBBindStatus = (
    bsOK, bsBadOrdinal, bsUnsupportedConversion, bsBadBindInfo,
    bsBadStorageFlags, bsNoInterface, bsMultipleStorage);

which do not appear to be complete when compared to the OLE DB Reference here: http://msdn.microsoft.com/en-us/library/ms716934.aspx

TOleDBStatus appears to be missing several values, including the entry stSchemaViolation between stIntegrityViolation & stBadStatus (which causes stBadStatus & stDefault to have the wrong values).
TOleDBBindStatus appears to have an additional value bsMultipleStorage on the end, which is not in the reference.

I know these are not related to the problem we are looking at now, but I thought it may be worth raising anyway, in case they make a difference one day somewhere else.

I'll keep working to try to understand the OLE DB binding code for a while, but it's just after midnight here, and my mind is starting to get foggy, so I may have to stop soon. (Not to mention that the OLE DB API is seriously twisted - I've never seen anything quite like it before.)

#7 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-09 00:02:07

ncook

Arnaud.
The modification has been only partly successful.
It works perfectly for MS SQL, but unfortunately not for Jet.

Now when using Jet, attempting to set an empty string value raises an exception from deep within the TOleDBConnection class.
The exception is "EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.")"

This is being raised in TOleDBConnection.OleDBCheck.EnhancedTest at the "raise E;" line near the bottom.
However the problem is coming from TOleDBStatement.ExecutePrepared at his line near the bottom "OleDBConnection.OleDBCheck(fCommand.Execute(nil,DB_NULLGUID,fDBParams,@fUpdateCount,nil));"

I'll include an extract from the log file in case it is useful. In this case I was attempting to set Null into 2 text fields in the same record...

20131209 09402152  +    TSQLRestServerDB(01FB8A40).root/NullTest1
20131209 09402152 SQL   	TOleDBStatement(01F50370) select max(ID) from tblNullTest1
20131209 09402152  +    	TOleDBStatement(01F50370).00112B9C SynOleDB.TOleDBStatement.ExecutePrepared (1522) 
20131209 09402152  -    	TOleDBStatement(01F50370).00113077 SynOleDB.TOleDBStatement.ExecutePrepared (1612)  00.004.230
20131209 09402152  +    	TOleDBStatement(01F50370).CreateAccessor
20131209 09402152  -    	TOleDBStatement(01F50370).CreateAccessor 00.000.010
20131209 09402152 SQL   	TOleDBStatement(01F50510) insert into tblNullTest1 (ID,fldText,fldMemo) VALUES (?OOR?,?OOR?,?OOR?)
20131209 09402152  +    	TOleDBStatement(01F50510).00112B9C SynOleDB.TOleDBStatement.ExecutePrepared (1522) 
20131209 09402152 ERROR 		"EOleDBException(0205E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 001148A7 SynOleDB.EnhancedTest (2038) 001149AB SynOleDB.TOleDBConnection.OleDBCheck (2044) 00113027 SynOleDB.TOleDBStatement.ExecutePrepared (1605) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402351 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 001148AF SynOleDB.EnhancedTest (2039)  stack trace 00001007 System.SysFreeMem 00002211 System.@ReallocMem 00005C61 System.DynArraySetLength 000F6BE4 SynCommons.TDynArrayHashed.HashFind (23430) 000F6BE4 SynCommons.TDynArrayHashed.HashFind (23430) 000FC836 SynCommons.TRawUTF8ListHashed.Changed (27652) 00002211 System.@ReallocMem 000F5E59 SynCommons.TDynArray.InternalSetLength (22833) 000F5F67 SynCommons.TDynArray.SetCount (22870) 00111568 SynOleDB.TOleDBStatement.CheckParam (1125) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 0017261D SynSQLite3Static.sqlite3_create_collation (1355) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 0000DD9B SysUtils.EncodeTime 
20131209 09402353 ERROR 		"EOleDBException(0205E340)":"OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." stack trace API 00113052 SynOleDB.TOleDBStatement.ExecutePrepared (1609) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402353 EXC   		EOleDBException ("OLEDB Error 80040E21 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.") at 001148AF SynOleDB.EnhancedTest (2039)  stack trace API 00102CC4 SynCommons.SynRtlUnwind (32472) 00003B58 System.@HandleAnyException 0007A58B ExceptionLog.Call_HookedRaise (15678) 001148AF SynOleDB.EnhancedTest (2039) 001149AB SynOleDB.TOleDBConnection.OleDBCheck (2044) 00113027 SynOleDB.TOleDBStatement.ExecutePrepared (1605) 001BAF9D mORMotDB.TSQLRestServerStaticExternal.ExecuteFromJSON (1529) 001B9147 mORMotDB.TSQLRestServerStaticExternal.EngineAdd (1025) 001A0F58 mORMot.TSQLRestServerURIContext.ExecuteORMWrite (24405) 001A23A0 mORMot.TSQLRestServer.URI (24866) 001BE685 mORMotSQLite3.TSQLRestClientDB.InternalURI (1479) 0019CB2A mORMot.TSQLRestClientURI.URI (22689) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 
20131209 09402353  -    	TOleDBStatement(01F50510).00113077 SynOleDB.TOleDBStatement.ExecutePrepared (1612)  02.008.380
20131209 09402353 srvr  	POST root/NullTest1 ERROR=400 (Bad Request)
20131209 09402353  -    TSQLRestServerDB(01FB8A40). 02.013.517
20131209 09402353 ERROR TSQLRestClientDB(01F87FB0) POST root/NullTest1 returned 400 Bad Request with message  {  "ErrorCode":400,  "ErrorText":"Bad Request"  } stack trace API 0019CCB3 mORMot.TSQLRestClientURI.URI (22719) 0019CFAA mORMot.TSQLRestClientURI.EngineAdd (23019) 001A7404 mORMot.TSQLRestClient.Add (27484) 001C2CF9 mORMot_Dev_TestGroup3.TfrmMORMot_Dev_TestGroup3.btnNullTest1Click (121) 000A8422 Controls.TControl.Click 000E4EAE RzButton.TRzCustomButton.MouseUp 000A8825 Controls.TControl.DoMouseUp 000ABF14 Controls.TWinControl.WndProc 000AB63B Controls.TWinControl.MainWndProc 00027F9A Classes.StdWndProc 
20131209 09402353 info  TSQLRestServerDB(01FB8A40) {   "ClientsCurrent": 0,   "ClientsMax": 0,   "Invalid": 1,   "Responses": 1,   "Modified": 0,   "IncomingBytes": 172,   "OutcomingBytes": 132,   "OutcomingFiles": 0,   "ServiceCalls": 1,   "ProcessTime": "2.01s"  }

I've tried Jet V3 and V4 databases with the same results.
This is not dependant on the value of the "Allow Zero Length" property of the text field. Having that set True or False makes no difference.

Actually in some ways this is a move forward, because there is now consistency between this and another new issue I was going to raise today. Previously this would have been a different problem. So fixing one problem will probably fix both of them.

I have been getting exactly the same error when I have been attempting to use other techniques to write NULL values into fields of other data types (not text fields). I have been trying 2 techniques.

The first technique I have tried is by using a variant in Delphi, like this...

type
  TSQLNullTest2 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fDouble: Variant; // This is a Double field in the database table.
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldDouble: Variant read fDouble write fDouble;
  end;

  // Use this class when creating the table.
  TSQLNullTest2_Create = class(TSQLRecord)
  private
    fText: RawUTF8;
    fDouble: Double;
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldDouble: Double read fDouble write fDouble;
  end;

procedure TfrmMORMot_Dev_TestGroup3.btnNullTest2_AppendClick(Sender: TObject);
var
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest2;
begin
  Conn := CreateTestDbConn;
  try
    Model := TSQLModel.Create([TSQLNullTest2]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest2, Conn, 'tblNullTest2');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables; // The table will already exist in the database.

        Obj := TSQLNullTest2.Create;
        try
          Obj.fldText := 'xx';
          Obj.fDouble := Null

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

This technique seems to work perfectly when reading the data, and works fine when writing a normal (non-Null) value into the fldDouble field.
It only seems to fail when attempting to write a Null value into the fldDouble field in a Jet database. (MS SQL works fine)
And it fails with exactly the same exception, in exactly the same way.

The second technique I have tried uses the InternalRegisterCustomProperties feature, like this...

type
  TExactHandicap = Double;

  TSQLNullTest3 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fExactHandicap: Double;

    function getExactHandicap: TExactHandicap;
    procedure setExactHandicap(aNewValue: TExactHandicap);

  protected
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;

  public
    property fldExactHandicap: TExactHandicap read getExactHandicap write setExactHandicap;

  published
    property fldText: RawUTF8 index 50 read fText write fText;
  end;

  // Use this class when creating the table.
  TSQLNullTest3_Create = class(TSQLRecord)
  private
    fText: RawUTF8;
    fExactHandicap: Double;
  published
    property fldText: RawUTF8 index 50 read fText write fText;
    property fldExactHandicap: Double read fExactHandicap write fExactHandicap;
  end;

{ TSQLNullTest3 }

function TSQLNullTest3.getExactHandicap: TExactHandicap;
begin
  Result := fExactHandicap;
end;

procedure TSQLNullTest3.setExactHandicap(aNewValue: TExactHandicap);
begin
  fExactHandicap := aNewValue;
end;

procedure ExactHandicap_RawData2Text(aRawDataIn: Pointer; aRawDataLength: Integer; var arefTextOut: RawUTF8); // TOnSQLPropInfoRecord2Text signature
const
  c_ExactHandicap_Null = 'null';
  c_ExactHandicap_Invalid = -99;
var
  ExactHandicap: Double;
begin
  arefTextOut := c_ExactHandicap_Null;

  if (aRawDataLength = SizeOf(Double)) then begin
    ExactHandicap := PDouble(aRawDataIn)^;

    if (ExactHandicap <> c_ExactHandicap_Invalid) then arefTextOut := StringToUTF8(FloatToStr(ExactHandicap));
  end;
end;

procedure ExactHandicap_Text2RawData(aTextIn: PUTF8Char; var arefRawDataOut: RawByteString); // TOnSQLPropInfoRecord2Data signature
const
  c_ExactHandicap_Null = 'null';
  c_ExactHandicap_Invalid = -99;
var
  ExactHandicap: Double;
begin
  if (aTextIn = c_ExactHandicap_Null) then ExactHandicap := c_ExactHandicap_Invalid
  else if (aTextIn = '') then              ExactHandicap := c_ExactHandicap_Invalid
  else                                     ExactHandicap := StrToFloat(aTextIn);

  SetString(arefRawDataOut, PAnsiChar(@ExactHandicap), SizeOf(Double));
end;

class procedure TSQLNullTest3.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  inherited;

  Props.RegisterCustomFixedSizeRecordProperty(Self, SizeOf(Double), 'fldExactHandicap', @TSQLNullTest3(nil).fExactHandicap, [], 0, ExactHandicap_RawData2Text, ExactHandicap_Text2RawData);
end;

procedure TfrmMORMot_Dev_TestGroup3.btnNullTest3_AppendClick(Sender: TObject);
const
  c_ExactHandicap_Invalid = -99;
var
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest3;
begin
  Conn := CreateTestDbConn;
  try
    Model := TSQLModel.Create([TSQLNullTest3]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest3, Conn, 'tblNullTest3');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables; // The table will already exist in the database.

        Obj := TSQLNullTest3.Create;
        try
          Obj.fldText := 'xx';
          Obj.fExactHandicap := c_ExactHandicap_Invalid;

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

The concept here is that occasionally for some fields, the database concept of NULL (meaning "Not specified" or "Invalid") does not map well to the value Zero. This happens when Zero is a valid value for the field, and it must be distinguishable from "Not specified" or "Invalid".
Internally in Delphi, we define a different value for "Invalid" which is -99 in this case.

The custom property is attempting to map between -99 in Delphi and NULL in the database field.

This technique seems to work perfectly when reading the data, and works fine when writing a normal (non-Null) value into the fldExactHandicap field.
It only seems to fail when attempting to write a Null value into the fldExactHandicap field in a Jet database. (MS SQL works fine)
And it fails with exactly the same exception, in exactly the same way.

So hopefully, if we can fix the first problem, these other two techniques for storing NULL fields in the database will also be fixed.

PS: As a side issue, I would appreciate any feedback you may have on the two techniques I am trying to use, either on the concepts, or on the code itself.

#8 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-08 22:10:40

ncook

Thanks Arnaud.
Especially for doing this on a Sunday.  Wow, that really is great support!

I'll be testing this today (Australian time, overnight your time) and I'll let you know the outcome.

#9 Re: mORMot 1 » Assertion failure in mORMot.pas with an empty string field » 2013-12-08 22:07:17

ncook

Thanks Arnaud.
That has fixed the problem.

#10 Re: mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-07 22:05:36

ncook

Arnaud.
Thanks for responding.
With so many details in my post, I must have given you a slightly wrong impression. Sorry about that.

Jet can store '' in it's text fields, but Not by default. You must first manually change the properties of the text & memo field definitions in the table, to enable the storing of zero length strings. By default this property is set to False, including the Text & Memo fields created by mORMot itself.

But beyond that, we need mORMot to store empty strings as NULL instead of '', for backward compatibility with other applications that need to read the same database, for all the reasons listed in my original post. I expect that other people needing to build mORMot based applications that will work well with other applications sharing the same data may also have similar issues. Especially when it is mORMot that is generating the data, not just being the consumer of data that has been generated externally.

So Yes, an internal parameter to store NULL instead of '' for an empty string would be a great solution.

But not only for Jet databases. At the minimum we will also need this for MS SQL databases also.
The best solution (IMHO) would be to make this parameter available across the whole system for all databases, but I understand that each databases behaves differently and so this may not be needed or wanted in all of them (e.g. not for Sqlite3).

Because of backwards compatibility with other applications, the accurate and controllable storage of NULL's in the database is very important to us.
As the use of mORMot becomes more widespread I expect you will find other people with the same requirements, as sharing a database between applications is very common, and several applications that we work with expect empty strings to be stored as NULL rather than ''.

#11 mORMot 1 » We need to store NULL's in the database instead of empty strings » 2013-12-07 04:25:36

ncook
Replies: 12

Hi Arnaud.

I have a second problem related to the minimal sample code I posted on the previous topic "Assertion failure in mORMot.pas with an empty string field"...

type
  TSQLNullTest1 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fMemo: RawUTF8;
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldMemo: RawUTF8 read fMemo write fMemo;
  end;

procedure TfrmMORMmot_Dev_Main.btnNullTest1Click(Sender: TObject);
const
  c_DbFileExtension = '.mdb';
var
  DbFileSpec: string;
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest1;
begin
  DbFileSpec := ChangeFileExt(application.ExeName, c_DbFileExtension);
  Conn := TOleDBJetConnectionProperties.Create(DbFileSpec, '', '', '');
  //Conn := TOleDBMSSQL2008ConnectionProperties.Create(c_SQLTest_ServerName, c_SQLTest_DatabaseName, c_SQLTest_UserName, c_SQLTest_Password);
  try
    Model := TSQLModel.Create([TSQLNullTest1]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest1, Conn, 'tblNullTest1');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables;

        Obj := TSQLNullTest1.Create;
        try
          Obj.fldText := '';
          Obj.fldMemo := '';

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

We have been using this sample code (with a work around in place for the bug from the previous topic) to experiment with the way mORMot stores empty string values in the fields in the tables, and we have found that rather than storing NULL values in the Text fields as we expected, it actually stores the empty string values instead. That is causing us all sorts of problems...

a) If the target database is a Jet 3 file (e.g. from Access 97, which unfortunately we must still support for many clients, for backwards compatibility with other applications that cannot be upgraded or modified) the code fails with an error, because that version of the Jet database does not allow an empty string value to be stored in a Text field. At least not without modifying the table definition manually (in the MS-Access IDE), by changing the "Allow zero length" property from it's default "No" to "Yes" for every text field, which we cannot do, even if we wanted to.

NOTE: The text fields that are created in the table by mORMot are set to NOT allow zero length strings (the Jet default), so in this case mORMot cannot even write an empty string value to the tables & fields that it has created itself.

b) Even if we could change the table definitions to allow empty strings to be stored, or if we use a Jet 4 database file (which seems to allow empty strings to be stored even when the field definition indicates they should not be allowed), or an MS SQL database, then storing empty strings instead of NULL's still causes backwards compatibility problems with other applications (that cannot be upgraded or modified). These applications are using "IS NULL" tests in their SQL WHERE clauses, which do not match the records with the empty string fields. This results in records that were written by mORMot not appearing in several queries or reports.

c) In a Jet database (any version we've tested) storing an NULL in a MEMO field uses almost no space, whereas storing an empty string in a MEMO field causes a whole database page to be allocated (to store the empty string), thus causing significant bloat in the database.

d) For an field which normally contains NULL values most of the time, but for which we need to be able to very quickly select (in SQL) the few records with non-Null values, we can add an index which excludes nulls, and that index stays very short and fast, as it only contains entries for the records not containing nulls. However when the field is populated with empty strings instead of Nulls, these are not ignored by the index, so the index size bloats and it becomes much slower.

etc, etc...

We are completely happy with the way mORMot handles NULL field values when it is reading data, but we need to find a way for it to be able to write NULL's instead of empty strings to the Text and Memo fields in the tables.

We have been trying to follow through the code (both statically and by tracing the execution) but so far we have not been able to find exactly how and where this decision is made.

Is there anything that you can think of that we can do as a work around for this problem, so that we can get mORMot to store NULL values instead of empty strings?

#12 mORMot 1 » Assertion failure in mORMot.pas with an empty string field » 2013-12-07 02:58:54

ncook
Replies: 2

Hi Arnaud.
Sorry, but I have to report that I'm getting an error "Assertion failure in mORMot.pas line 16841".

Here's my minimal code to reproduce the error...

type
  TSQLNullTest1 = class(TSQLRecord)
  private
    fText: RawUTF8;
    fMemo: RawUTF8;
  published
    property fldText: RawUTF8 index 255 read fText write fText;
    property fldMemo: RawUTF8 read fMemo write fMemo;
  end;

procedure TfrmMORMmot_Dev_Main.btnNullTest1Click(Sender: TObject);
const
  c_DbFileExtension = '.mdb';
var
  DbFileSpec: string;
  Conn: TOleDBConnectionProperties;
  Model: TSQLModel;
  Client: TSQLRestClientDB;
  Obj: TSQLNullTest1;
begin
  DbFileSpec := ChangeFileExt(application.ExeName, c_DbFileExtension);
  Conn := TOleDBJetConnectionProperties.Create(DbFileSpec, '', '', '');
  //Conn := TOleDBMSSQL2008ConnectionProperties.Create(c_SQLTest_ServerName, c_SQLTest_DatabaseName, c_SQLTest_UserName, c_SQLTest_Password);
  try
    Model := TSQLModel.Create([TSQLNullTest1]);
    try
      VirtualTableExternalRegister(Model, TSQLNullTest1, Conn, 'tblNullTest1');

      Client := TSQLRestClientDB.Create(Model, nil, '', TSQLRestServerDB);
      try
        Client.Server.CreateMissingTables;

        Obj := TSQLNullTest1.Create;
        try
          Obj.fldText := '';
          Obj.fldMemo := '';

          Client.Add(Obj, True);
        finally
          Obj.Free;
        end;
      finally
        Client.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Conn.Free;
  end;
end;

The line causing the error is in mORMot.pas, near the bottom of routine TJSONObjectDecoder.EncodeAsSQLPrepared

assert(P-pointer(tmp)<length(tmp));

My first thought as that the test was had an edge condition bug, and maybe it should be using "<=" rather than "<", but I was wrong.
I found this was not the problem once I added the second RawUTF8 to the TSQLNullTest1 type (originally I only had a single text field in the sample code). With two empty string fields, P is now pointing beyond the end of tmp, so using "<=" does not fix the problem.

Rather it appears that the problem is actually related to the following line near the top of the routine

SetLength(tmp,FieldLen+2*FieldCount+12); // max length

where FieldLen seems to be too low, and so it is making tmp slightly too short.

I guess there are a few possible ways to fix this (e.g. changing the calculation of FieldLen, or changing the calculation of the length of tmp), but I expect there could be side effects if it is not fixed properly, so I'll have to leave it to you to work out what the "correct" fix is.

If it makes any difference, I am using a Jet database (TOleDBJetConnectionProperties), but as you can see in the sample code, I have also tried this with an MS SQL database (TOleDBMSSQL2008ConnectionProperties), and I got the same result.

I have a another problem with the same test code, but I'll put that on a separate topic shortly, as it is unrelated to this error.

#13 Re: mORMot 1 » Incorrect results when reading Boolean fields from a Jet table » 2013-12-05 23:21:05

ncook

Thanks ab.

That works well.

#14 mORMot 1 » Incorrect results when reading Boolean fields from a Jet table » 2013-12-05 08:21:53

ncook
Replies: 2

Hi,
I'm having a problem reading Boolean fields from a Jet table. They are always ending up with the value False, even when the value recorded in the table is True.

It seems to work ok when using ISQLDBRows with .Step, and it seems to work ok when using FetchAllAsJSON, both of which give a value of -1 for True, and 0 for False, as expected.

But when I try to access the data using the ORM with .CreateAndFillPrepare and .FillOne, the Boolean fields in my TSQLRecord descendant type always get the value False.

I think that I've tracked it down to the following routine, but I'm unsure how to fix it without breaking other things....

procedure TSQLPropInfoRTTIEnum.SetValue(Instance: TObject; Value: PUTF8Char; wasString: boolean);
var i,err: integer;
begin
  if Value=nil then
    i := 0 else begin
    i := GetInteger(Value,err);
    if err<>0 then // we allow a value stated as text
      i := fEnumType^.GetEnumNameValue(Value); // -> convert into integer
    if cardinal(i)>cardinal(fEnumType^.MaxValue) then
      i := 0;  // only set a valid value
  end;
  SetOrdProp(Instance,pointer(fPropInfo),i);
end;

In particular the following line...

if cardinal(i)>cardinal(fEnumType^.MaxValue) then
  i := 0;  // only set a valid value

When the Boolean is True, the value of i is -1, which passes the test (because of the typecast to cardinal, and fEnumType^.MaxValue = 1) and thus sets the value of i to 0.

I hope someone else knows enough about this area of the code to suggest a safe fix.

#15 Re: mORMot 1 » How to safely terminate the TSQLRestClientURI OnIdle background thread » 2013-08-15 02:06:32

ncook

Sorry, I got a bit lost in your third paragraph. I'm only new to the mORMot framework, and have only been experimenting with it for a month or so. I'm still at the "let's try this and see if I can make it work" stage, and so some of the details are a bit beyond my current level of understanding, at least without more time to digest them. (I'm also fighting a two week head cold, so my concentration level is not a good as it should be at the moment.)

Having said that, I would be quite happy to do some testing of any proposed technique that you decide to try. My testing would not be deep, as I am very new to the concepts of multi-threading, especially in Delphi (I have never tried multi-threaded programming in Delphi before my first mORMot server application, only about a month ago), but I could certainly test how well a given implementation works in variations of our application environment, as well as checking for memory leaks, and giving feedback on practical implementation issues (once I understand them properly).

You mentioned needing to recreate the TSQLRestClientURI to avoid a GPF from the background thread. As I said, I'm not entirely clear at this stage about why that would be required, so I'll take your word on it.
However our client application architecture has been built to easily drop and recreate the TSQLModel, TSQLRestClientURI and the service interfaces, regularly or on demand, as this seemed like the best way to handle server drop-outs and/or the fail-over of servers to new addresses. So while I can see that recreating the TSQLRestClientURI may be a problem for a really simple application, it shouldn't be much of a problem for a production ready architecture, which is where the issue would arise in the first place.

#16 Re: mORMot 1 » How to safely terminate the TSQLRestClientURI OnIdle background thread » 2013-08-13 14:10:14

ncook

Would it be possible to add a third argument to TOnIdleSQLRestClient, a Boolean which if set True would terminate the waiting thread.
Something like TOnIdleSQLRestClient = procedure(Sender: TSQLRestClientURI; ElapsedMS: Integer; out Terminate: Boolean) of object;

This would have the advantage of:
(a) only terminating while still in a wait state, and
(b) leaving the termination up to the framework itself, which could ensure it was done as safely as possible.

As far as the UI, I'm envisaging something like a "Cancel" button on the "Please wait..." form displayed by the OnIdle event. (e.g. the user has decided they don't want to wait, and would rather cancel the operation).
It would then be up to the application to capture the intent to terminate the wait, and feed that back to something the OnIdle event handler could use to achieve that end (e.g. by setting the Terminate argument).

I'm not sure if that is practical or not, but I'm thinking about the frustration caused by similar extended waits that occur sometimes in other applications (e.g. Windows attempting to copy a file over a bad network connection) that seem to go on forever, with no way for the user to say "Give up! There's obviously something wrong. If it's going to take that long I don't care any more." For half my users, such extended waits usually end up with them killing the application in Task Manager (or worse: pulling the power cord).

Anything we can do to prevent that level of frustration is good. The OnIdle event helps because it keeps the application responsive. But at present it still does not give the user any alternative but to wait (or out of frustration, to take more drastic action).

#17 Re: mORMot 1 » Remainder of the REST URI » 2013-08-13 09:43:55

ncook

Thank you. Especially for the quick response.

That was exactly what I needed.

#18 mORMot 1 » Remainder of the REST URI » 2013-08-13 09:27:37

ncook
Replies: 2

I'm building a REST service based application using mORMot.

For most of the application I am using interfaced based methods very successfully with a Delphi client, JSON encoded arguments and results, using the techniques as described in chapter 17 of the SAD 1.18 documentation.
(By the way, congratulations on having such good documentation. It really is a breath of fresh air.)

For another part of the application I am returning dynamically built HTML pages to a web browser. I have set up a test service using the techniques as described in chapter 15, and I now have HTML pages being built and returned. I was very pleased how simple that was to set up.

However the technique requires any arguments to the method to be passed as parameters with a URI syntax like "/root/Sum?a=3.12&b=4.2" (to use an example from the SAD).
I would like to be able to pass additional information to my method as part of the URI using a syntax more like "/root/exampleMethod/moreDetails/aboutWhat/iAmTrying/toDo". I have seen this technique used in several other frameworks (especially ASP.Net MVC) and it has great appeal.

Taking this example, I would have no trouble calling the method "exampleMethod". I would be quite happy within that method, to decode the remainder of the URI myself (the "/moreDetails/aboutWhat/iAmTrying/toDo" part). I am not expecting the framework to do anything with that information automatically.
But I have not yet been able to find any way to get access to anything that will tell me what the remainder of the URI was.  For example: TSQLRestServerCallBackParams.URI only returns "exampleMethod".

Is there some way to get access the the remainder of the URI from within my method (hopefully from something in TSQLRestServerCallBackParams)?

If there currently is not, could this information be made available? (For example: as TSQLRestServerCallBackParams.FullURI, or TSQLRestServerCallBackParams.ExtendedURI, or TSQLRestServerCallBackParams.URIAfterMethod, or something like that.)

#19 mORMot 1 » How to safely terminate the TSQLRestClientURI OnIdle background thread » 2013-08-13 01:19:16

ncook
Replies: 5

I've been experimenting with the TSQLRestClientURI.OnIdle background communications and it's working really well. Thanks for that feature, as it helps to make the client applications much more responsive.

However there are some situations where a long running wait may need to be prematurely terminated (for example: when the user wants to cancel the pending operation, of even wants to close the client).

Is there a safe mechanism for the client application to be able to terminate the background thread (in TSQLRestClientURI) that is waiting for the server to respond? (rather than just waiting for it to time out)

ncook

Pages: 1

Board footer

Powered by FluxBB