Posts by Vitaly

Vitaly · mORMot 1

Yes, all works fine (with and without USE_SYNCOMMONS)! Zeos 6392, and I've updated mORMot to the latest, but I don't think that it mattered here. Great!

Concerning UTF8 codepage setting in Zeos properties in my case (for ISO8859_1 FB DB):
All works, but until it tries to insert/update with the value, containing some non-ISO8859_1 character. Then I get such an exception:

Project Upkeep_Admin.exe raised exception class EZIBSQLException with message 'SQL Error: arithmetic exception, numeric overflow, or string truncation; Cannot transliterate character between character sets; At block line: 15, col: 1

With my current Zeos codepage setting transliteration goes fine, unicode symbols are transformed to some corresponding ISO8859_1 symbols smoothly. At least it seems so for me

Vitaly · mORMot 1

Delphi 10.3.3, Win64 target platform.
I'd love to switch to UTF8, but we have such a restriction currently because of the legacy software.

Sure, I'll try to debug it more. But a bit later, have to go now.
I will try with UTF8 codepage property also but, as far as I remember, it caused Firebird error in some particular cases. It was the reason for making my current codepage setting much time ago.
I'll report about results here.

Vitaly · mORMot 1

Nope. I might be wrong (need to debug it more to understand the difference), but so far it seems that with USE_SYNCOMMONS in mORMot.pas

function TSQLTable.FieldIndex(FieldName: PUTF8Char): integer;
begin
  if (self<>nil) and (fResults<>nil) and (FieldName<>nil) and (FieldCount>0) then
  ...

FieldCount = 0 and the function returns -1.
Without this definition FieldCount = 14 and it returns the correct field index.

Hope this information will be anyhow useful

Vitaly · mORMot 1

Great! It all works again! Thank you very much!

Concerning collation: COLLATE NO_NO

Vitaly · mORMot 1

Ok, with Zeos 6386 the AV has gone, it seems that your change was good, thanks! But (sorry, another trouble...) I began getting the exception:

20200329 20540057 trace uNextDB.TNextDB(046fc860) BatchSend {"TSQLRestBatch(046aa430)":{"Count":2,"SizeBytes":1074}}
20200329 20540057  +    uNextDB.TNextDB(046fc860).EngineBatchSend  inlen=1074
20200329 20540057 DB            SynDBZeos.TSQLDBZEOSStatement(028b3000) Prepare 663us update PREG_PERSON set SSN=?,GivenName=?,MiddleName=?,Surname=?,DateAdrFrom=?,DateOfBirth=?,DateStatus=?,RegStatus=?,MaritalStatus=?,MothersNIN=?,FathersNIN=?,OldNIN=?,SpouseNIN=?,PostalAddress=?,PostalCode=?,PostalPlace=?,St=?,Municipality=?,SchoolDistrict=?,Constituency=?,BasicStatisticalUnit=?,CurrentAddress=?,Gender=?,LastUpdate=? where ID=?
20200329 20540833 EXC           EZIBSQLException ("SQL Error: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 46; =") [] at b4129c
20200329 20540837 EXC           EZIBSQLException ("SQL Error: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 46; =") [] at b4129c

After some debugging I found that resulting Zeos sql statement looks like:

EXECUTE BLOCK(P0_0 VARCHAR(11) CHARACTER SET =?,P1_0 VARCHAR(50) CHARACTER SET =?,P2_0 VARCHAR(50) CHARACTER SET =?,P3_0 VARCHAR(50) CHARACTER SET =?,P4_0 DATE=?...

which, I assume, means that CHARACTER SET is lost.

We have to use ISO8859_1 in this legacy DB, earlier I solved it with the setting ZeosURL.Properties.Values['codepage'] := 'ISO8859_1 ' in TSQLDBZEOSConnectionProperties instance and it worked fine.

It seems, that the problem appears only if those fields in FB DB have ISO8859_1 character set. I made such a test: I picked up a new DB and allowed mORMot to create all metadata (it created all fields as UTF8). Then I looked at the result Zeos sql statement:

EXECUTE BLOCK(P0_0 VARCHAR(11) CHARACTER SET ISO8859_1=?,P1_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P2_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P3_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P4_0 TIMESTAMP=?...

and batch sending worked as intended.

Should I use some other approach to get it working with ISO8859_1 FB fields?

Vitaly · mORMot 1

Sorry for making you wait.
I've updated mORMot with the patch, downloaded 6382 Zeos revision, removed use_syncommons definition (suddenly I began getting other errors with it) and the "invalid BLOB ID" problem has gone!
Although it seems that I'm getting now the same first AV problem, with which I started this thread

20200329 13382356 trace uNextDB.TNextDB(0467c860) BatchSend {"TSQLRestBatch(0462a9b0)":{"Count":2,"SizeBytes":1074}}
20200329 13382356  +    SynDBZeos.TSQLDBZEOSConnection(048791a0).Connect
20200329 13382356 trace         Connect to firebird D:\Norjournal\AFK_DEMO.FDB for localhost at 3050:
20200329 13382356  +    uNextDB.TNextDB(0467c860).EngineBatchSend  inlen=1074
20200329 13382360 DB            Connected to localhost using D:\Norjournal\AFK_DEMO.FDB 2005009
20200329 13382360 DB            SynDBZeos.TSQLDBZEOSStatement(04554110) Prepare 816us select current_timestamp from rdb$database
20200329 13382360 SQL           SynDBZeos.TSQLDBZEOSStatement(04554110) ExecutePrepared 1.52ms select current_timestamp from rdb$database
20200329 13382360  -    00.081.573
20200329 13382360 DB    SynDBZeos.TSQLDBZEOSStatement(045542d0) Prepare 674us update UPKEEP_JOURNAL_TASK set Status=?,Started=?,TotalValue=?,CurrentValue=?,Completed=?,Finished=?,Other=?,Results=? where ID=?
20200329 13382361 SQL   SynDBZeos.TSQLDBZEOSStatement(045542d0) ExecutePrepared 4.80ms wr=1 update UPKEEP_JOURNAL_TASK set Status=1,Started='2020-03-29 16:38:19',TotalValue=20,CurrentValue=5,Completed='25%',Finished=null,Other='{   "BaseURL": "https://personregister-web.test.nhn.no",   "User": "#####",   "Password": "#####"  }',Results='{   "PersonsToLoad": 1039,   "PersonsLoaded": 355,   "PersonsProcessed": 3  }' where ID=64
20200329 13382361 DB            SynDBZeos.TSQLDBZEOSStatement(04554490) Prepare 683us update PREG_PERSON set SSN=?,GivenName=?,MiddleName=?,Surname=?,DateAdrFrom=?,DateOfBirth=?,DateStatus=?,RegStatus=?,MaritalStatus=?,MothersNIN=?,FathersNIN=?,OldNIN=?,SpouseNIN=?,PostalAddress=?,PostalCode=?,PostalPlace=?,St=?,Municipality=?,SchoolDistrict=?,Constituency=?,BasicStatisticalUnit=?,CurrentAddress=?,Gender=?,LastUpdate=? where ID=?
20200329 13385059 EXCOS         EAccessViolation (c0000005) [] at b4335a
20200329 13385100 EXC           EAccessViolation ("Access violation at address 0000000000B4335A in module 'Upkeep.exe'. Read of address FFFFFFFFFFFFFFFC") [] at b4335a

Of course I'll try to test it more (and doublecheck all versions and commits) - just informing now about current status.

Concerning transaction handling. Could 'hard_commit=true' influence somehow badly here?

Vitaly · mORMot 1

EgonHugeist wrote:

As a workaround just add the USE_SYNCOMMONS define to your project base conditions.

Ok, I've done that - compilation goes fine now, thanks but select from the table with text blob doesn't want to work, now:

20200329 09424359 DB    SynDBZeos.TSQLDBZEOSStatement(040df640) Prepare 782us select ID,Name,PeriodType,PeriodSettings,OnOff,ActiveStart,ActiveEnd,Status,LastRun,Technical from UPKEEP_SCHEDULE where Technical=? and OnOff=? and Status=?
20200329 09424359 SQL   SynDBZeos.TSQLDBZEOSStatement(040df640) ExecutePrepared 1.26ms select ID,Name,PeriodType,PeriodSettings,OnOff,ActiveStart,ActiveEnd,Status,LastRun,Technical from UPKEEP_SCHEDULE where Technical=0 and OnOff=1 and Status=0
20200329 09424815 EXC   EZIBSQLException ("SQL Error: invalid BLOB ID") [] at b49f5c

PeriodSettings is a text blob field. Please, tell me if I'm losing smth obvious.

Vitaly · mORMot 1

After downloading snapshot 6380 and updating mORMot to the latest commit, I can't compile SynDBZeos unit with the error:

[dcc64 Error] SynDBZeos.pas(1272): E2035 Not enough actual parameters

The error is pointing to WriteIZBlob subroutine of TSQLDBZEOSStatement.ColumnsToJSON:

procedure WriteIZBlob;
var blob: IZBlob;
begin
  blob := fResultSet.GetBlob(col+FirstDbcIndex);
  WR.WrBase64(blob.GetBuffer,blob.Length,true); // withMagic=true             <--- here
end;

It seems, that blob.GetBuffer expects parameters.

Hope, I haven't mixed anything while getting the SVN snapshot.

Vitaly · mORMot 1

I was not able to follow changes in mORMot for a while and now I'm getting Access Violation at sending the Batch. The code of my project wasn't changed in that part.
AV appears in Zeos unit ZDbcInterbase6Utils.pas at line 1969 in Put subroutine of GetExecuteBlockString function:

  procedure Put(const Args: array of RawByteString; var Dest: PAnsiChar);
  var I: Integer;
  begin
    for I := low(Args) to high(Args) do //Move data
    begin
      {$IFDEF FAST_MOVE}ZFastCode{$ELSE}System{$ENDIF}.Move(Pointer(Args[i])^, Dest^, {%H-}PLengthInt(NativeUInt(Args[i]) - StringLenOffSet)^);         <---- here
      Inc(Dest, {%H-}PLengthInt(NativeUInt(Args[i]) - StringLenOffSet)^);
    end;
  end;

I tried to understand what has been changed and began rolling back mORMot versions. After severel steps (or tens of steps) I began getting other errors... As the result the last working (for me) commit is:
(git, master, 2020-03-21) 7108ee23d38d9b12a9cfe01a5642d0e87429a2a3 introduce TAESCTR.ComposeIV() modular method instead of TAESCTRNIST ...
The strange part is that with this 'old' commit Zeos Put subroutine from the above is not used at all in my project.

There were so many changes lately, so I got a bit confused. Therefore I decided to report my troubles here. If somebody gets the same, at least he'll know the working commit.

Delphi 10.3.3, Win64 target platform
Zeos (git, latest master, 2020-02-23) dac64fafcd00576a2c4ebed9c7dafadd8566e6b3
Firebird 2.5.9

Vitaly · mORMot 1

Yes, all seems fine, now. Thanks!

Vitaly · mORMot 1

After today's changes, I faced problems with mORMot.pas compilation:

[dcc64 Error] mORMot.pas(57677): E2107 Operand size mismatch
[dcc64 Error] mORMot.pas(58046): E2066 Missing operator or semicolon
[dcc64 Error] mORMot.pas(58171): E2066 Missing operator or semicolon

Delphi 10.3.3, Win64 target platform

Vitaly · mORMot 1

I also join others, especially in parts of giving up supporting old compilers and splitting huge units. It will be great!

My only tiny wish is to try to name new units in a more obvious way. Like @EMartin suggested as a sample.
The problem is that sometimes I forget which unit contains some particular function/type (or a set of functions/types) and I have to check SynCommons, then mORMot, then SynTable...
But I think, that Arnaud already has a proper idea for splitting and naming, so I believe everything will be fine

Vitaly · mORMot 1

I also doubt that you need mORMot for your task.

But you can look into SynCrtSock unit for various abilities to send an HTTP request. I assume the simplest is HttpPost function (for POST method): https://synopse.info/files/html/api-1.1 … l#HTTPPOST

Vitaly · mORMot 1

urhen wrote:

I was focussing on the client-side and meant something like Indy's TIdTCPClient which uses the platform APIs so that you don't need to include 3rd party libraries.

Could you help me here to understand, please?
I'm using SynCrtSock.TWinHTTP as a universal TCP-client to get rid of using native Delphi and Indy clients (in handier for me way). Almost all my current projects, where I have to communicate with third-party services, are using it already. And these third-party services are often built on very different communication and security schemes.
So far everything works fine, but should I be aware of something for the future?

Vitaly · mORMot 1

The problem is gone, many thanks!

Vitaly · mORMot 1

Yes, it is fixed, now. Thanks!

Although in one of my projects I began getting such a new (for me) exception:

20200219 11555249  +    SynDBZeos.TSQLDBZEOSStatement(0470a560).ExecutePrepared
20200219 11555249 SQL           SynDBZeos.TSQLDBZEOSStatement(0470a560) select ID,Name,PeriodType,PeriodSettings,OnOff,ActiveStart,ActiveEnd,Status,LastRun from UPKEEP_SCHEDULE where OnOff=1 and Status=0
20200219 11555249  -    00.002.479
20200219 11555748 EXC   ESynException {"Message":"TRawUTF8ListHashed.Rehash collisions=1"} [] at 604cc6

I guess, it might be some my mistake, which just didn't show up earlier without the latest changes. Maybe you could give a hint on which direction I should dig to find it out?
It is a multi-threaded windows service, threads are actively using one TSQLRestServerDB instance (Zeos-Firebird), no http-server.

Vitaly · mORMot 1

I'm getting now a compilation error

[dcc64 Error] SynCommons.pas(21145): E2105 Inline assembler syntax error

{$ifdef CPUX64}
function GetBitsCountSSE42(value: PtrInt): PtrInt;
{$ifdef FPC} assembler; nostackframe;
asm
        popcnt  rax, value
{$else}
asm     .noframe
        db      $f3,$48,$0f,$B8,{$ifdef win64}$c1{$else}$c7{$endif}                   <---------------------------- here
{$endif FPC}
end;

As far as I understood, the change appeared in the commit cfa40bb "new optimized asm versions of most-used sorting/comparison functions".
Delphi 10.3.3, Win64 target platform

Vitaly · mORMot 1

It is so sad to read this thread
Why everybody is so aggressive? Why not try to understand or explain without saying smth like: I'm smarter, you're such and such...
Kindness is much more constructive and productive.

Vitaly · mORMot 1

Yes, everything is compiling smoothly again. Thanks!

Vitaly · mORMot 1

I have the same with all my projects with the latest mORMot commit.
Delphi 10.3.3

function TSynDictionary.LoadFromJSON(JSON: PUTF8Char; EnsureNoKeyCollision: boolean{$ifndef NOVARIANTS};
  CustomVariantOptions: PDocVariantOptions{$endif}): boolean;
var k,v: RawUTF8;
begin
  result := false;
  if not JSONObjectAsJSONArrays(JSON,k,v) then
    exit;
  fSafe.Lock;
  try
    if fKeys.LoadFromJSON(pointer(k),nil{$ifndef NOVARIANTS},CustomVariantOptions{$endif})<>nil then                     <------- here
      if fValues.LoadFromJSON(pointer(v),nil{$ifndef NOVARIANTS},CustomVariantOptions{$endif})<>nil then
...

Vitaly · mORMot 1

oz wrote:

So, to come to an conclusion: It depends a lot on your use-case and data types if it is a good idea to store large blobs in db.

Thanks for sharing your experience, thoughts, and details!
I guess, I misunderstood you the first time. I thought you had some experience, which can tell that storing files in db is always a bad idea in any case.
I used storing blobs in db differently in different situations and planning to continue using it in future. That's why I was interested in your strict phrase "Don't do it."

oz wrote:

Do it only if you exactly know what and why you are doing it.

Totally agree

Vitaly · mORMot 1

oz wrote:

My experience with LARGE blob data:
Don't do it.

Could you share details of your experience, please?

Vitaly · mORMot 1

Rads wrote:

Perhaps, it could be useful for others developers…

Definitely! Subscribing to the topic

Vitaly · mORMot 1

It's working smoothly now, thanks!

Vitaly · mORMot 1

I'm using TIntegerDynArray in one class. After pulling fresh mORMot I began to get such an error at the stage of the application initialization:

Project PReg_BatchLoad.exe raised exception class ESynException with message 'Unregistered ptCustom for TJSONRecordTextDefinition.AddItem(Line: TINTEGERDYNARRAY)'.

I tried other mORMot versions and noticed that this abnormal behavior appeared exactly in 0b4f8c6 commit (2019-11-12): 'optimized IdemPropNameUSameLen()'.

Is it a bug or some breaking change or I should look deeper in my code?

Win64, Delphi 10.3.2

Vitaly · mORMot 1

Ran several tests. Everything works well and fast! Thanks!

Vitaly · mORMot 1

Yes, it's working perfectly now! Thank you Very much!

Vitaly · mORMot 1

Arnaud, could you confirm or disprove, please, that there is a problem with TSQLDBConnectionProperties.MultipleValuesInsertFirebird() exist?
I would like to know, which way I should close this issue for my project. Thanks!

Vitaly · mORMot 1

I ran several tests (for about 100K records) and I didn't find that MultipleValuesInsert is anyhow slower than MultipleValuesInsertFirebird for my project.
So if there an idea to purge MultipleValuesInsertFirebird will appear, I'm totally for it

Vitaly · mORMot 1

Using TSQLDBConnectionProperties.MultipleValuesInsert() works fine, btw.
Tested that way:

procedure TSQLDBConnectionProperties.MultipleValuesInsertFirebird(
  Props: TSQLDBConnectionProperties; const TableName: RawUTF8;
  const FieldNames: TRawUTF8DynArray; const FieldTypes: TSQLDBFieldTypeArray;
  RowCount: integer; const FieldValues: TRawUTF8DynArrayDynArray);
var W: TTextWriter;
    maxf,sqllenwitoutvalues,sqllen,r,f: Integer;
begin
  MultipleValuesInsert(Props, TableName, FieldNames, FieldTypes, RowCount, FieldValues);
  Exit;

  maxf := length(FieldNames);     // e.g. 2 fields
  if (Props=nil) or (FieldNames=nil) or (TableName='') or (length(FieldValues)<>maxf) or
...

Result:

20191102 22114918 trace uNextDB.TNextDB(053c2a10) BatchSend {"TSQLRestBatch(04cfcdc0)":{"Count":2,"SizeBytes":1115}}
20191102 22114918  +    uNextDB.TNextDB(053c2a10).EngineBatchSend TSQLPerson inlen=1116
20191102 22114918  +    	SynDBZeos.TSQLDBZEOSStatement(04d02a10).Prepare
20191102 22114918  -    	00.001.669
20191102 22114918  +    	SynDBZeos.TSQLDBZEOSStatement(04d02a10).ExecutePrepared
20191102 22114918 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d02a10) execute block( i1 BIGINT=7039890342,i2 VARCHAR(11) CHARACTER SET UTF8='07039890342',i3 VARCHAR(5) CHARACTER SET UTF8='TOMMY',i4 VARCHAR(1) CHARACTER SET UTF8='',i5 VARCHAR(7) CHARACTER SET UTF8='BERGENE',i6 CHAR(1)=NULL,i7 TIMESTAMP='1998-03-07',i8 TIMESTAMP='1998-08-11',i9 BIGINT=1,i10 BIGINT=1,i11 VARCHAR(1) CHARACTER SET UTF8='',i12 VARCHAR(1) CHARACTER SET UTF8='',i13 VARCHAR(1) CHARACTER SET UTF8='',i14 VARCHAR(1) CHARACTER SET UTF8='',i15 VARCHAR(2) CHARACTER SET UTF8='0 ',i16 CHAR(1)=NULL,i17 VARCHAR(4) CHARACTER SET UTF8='1311',i18 VARCHAR(11) CHARACTER SET UTF8='HØVIKODDEN',i19 VARCHAR(4) CHARACTER SET UTF8='0219',i20 VARCHAR(6) CHARACTER SET UTF8='BÆRUM',i21 VARCHAR(1) CHARACTER SET UTF8='',i22 VARCHAR(1) CHARACTER SET UTF8='',i23 BIGINT=467,i24 BIGINT=1,i25 BIGINT=1,i26 TIMESTAMP='2019-11-03 01:11:49' ,i27 BIGINT=9060097620,i28 VARCHAR(11) CHARACTER SET UTF8='09060097620',i29 VARCHAR(5) CHARACTER SET UTF8='LINDA',i30 VARCHAR(1) CHARACTER SET UTF8='',i31 VARCHAR(7) CHARACTER SET UTF8='ERIKSEN',i32 TIMESTAMP='1999-08-10',i33 TIMESTAMP='2000-06-09',i34 TIMESTAMP='2000-08-15',i35 BIGINT=1,i36 BIGINT=1,i37 VARCHAR(1) CHARACTER SET UTF8='',i38 VARCHAR(1) CHARACTER SET UTF8='',i39 VARCHAR(1) CHARACTER SET UTF8='',i40 VARCHAR(1) CHARACTER SET UTF8='',i41 VARCHAR(7) CHARACTER SET UTF8='VEI 31 ',i42 BIGINT=1311,i43 VARCHAR(4) CHARACTER SET UTF8='1311',i44 VARCHAR(11) CHARACTER SET UTF8='HØVIKODDEN',i45 VARCHAR(4) CHARACTER SET UTF8='0219',i46 VARCHAR(6) CHARACTER SET UTF8='BÆRUM',i47 VARCHAR(4) CHARACTER SET UTF8='0419',i48 VARCHAR(1) CHARACTER SET UTF8='',i49 BIGINT=467,i50 BIGINT=1,i51 BIGINT=0,i52 TIMESTAMP='2019-11-03 01:11:49' ) as begin INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCodeInt,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (:i1,:i2,:i3,:i4,:i5,:i6,:i7,:i8,:i9,:i10,:i11,:i12,:i13... (truncated) length=2568
20191102 22114919  -    	00.003.345
20191102 22114919 trace 	uNextDB.TNextDB(053c2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson

I know that it is not a decision, but the problem became a bit more localized

Vitaly · mORMot 1

Yes, true. That's the point. As I supposing:
Batch is forming in JSON, where TDateTime value is in ISO format (with T). Then SQL should be transformed into external SQL and processed. But somehow it doesn't happen in my case with insert in a batch.
There are different ways of batch processing ways in TSQLRestServer.EngineBatchSend and other following methods and I hardly can understand the magic (so far at least). Will continue trying

Vitaly · mORMot 1

Trying to debug for a rather long time. Will continue doing that, just decided to share some new information. Maybe you'll have some idea and I would be able to stop breaking my mind

TSQLDBConnectionProperties.MultipleValuesInsert() never starts in my project.
Instead of it TSQLRestStorageExternal.InternalBatchStop by running fProperties.OnBatchInsert() goes to TSQLDBConnectionProperties.MultipleValuesInsertFirebird() procedure.
As far as I could see, LastUpdate field type (FieldTypes array) is founded to be correct: ftDate. Although in FieldValues I have the same ISO datetime value (don't know so far, whether it is good or not).

One more thing, which confuses me much. Rarely, during testing I get another error in my batch sending (without any code changing):

20191102 16074830 trace uNextDB.TNextDB(050b2a10) BatchSend {"TSQLRestBatch(04e2dcc0)":{"Count":2,"SizeBytes":1077}}
20191102 16074830  +    uNextDB.TNextDB(050b2a10).EngineBatchSend TSQLPerson inlen=1078
20191102 16074830 EXC   	EORMException {"Message":"TSQLPropInfoList.IndexByNameOrExcept(SSN): unkwnown field in TSQLPerson"} [] at 8b9466 
20191102 16074830 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at dc19e7 
20191102 16074830 trace 	uNextDB.TNextDB(050b2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson
20191102 16074830 warn  	uNextDB.TNextDB(050b2a10) {"EORMException(04eb1260)":{"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"}} -> PARTIAL rollback of latest auto-committed transaction data={"Person~~["automaticTransactionPerRow",10000,"POST~~{"RowID":7039890342,"SSN":"07039890342","GivenName":"TOMMY","MiddleName":"","Surname":"BERGENE","DateAdrFrom":null,"DateOfBirth":"1998-03-07","DateStatus":"1998-08-11","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"0 ","PostalCodeInt":null,"PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":1,"LastUpdate":""},"POST~~{"RowID":9060097620,"SSN":"09060097620","GivenName":"LINDA","MiddleName":"","Surname":"ERIKSEN","DateAdrFrom":"1999-08-10","DateOfBirth":"2000-06-09","DateStatus":"2000-08-15","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"VEI 31 ","PostalCodeInt":1311,"PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"0419","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":0,"LastUpdate":""}]}
20191102 16074830 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at dc19e7

It is completely undetermined and can complain about other fields (Surname f.ex).

I guess I need to create a simpler testing project. Maybe there is some problem with my bigger project...

upd: using latest Zeos from master branch, now (587fa5e 2019-11-01)

Vitaly · mORMot 1

I faced a problem with batch inserting records, which have TDateTime field (Timestamp in Firebird):

20191102 02555149 trace uNextDB.TNextDB(053c2a10) BatchSend {"TSQLRestBatch(04d0d840)":{"Count":2,"SizeBytes":1074}}
20191102 02555149  +    uNextDB.TNextDB(053c2a10).EngineBatchSend TSQLPerson inlen=1075
20191102 02555149  +    	SynDBZeos.TSQLDBZEOSStatement(04d125f0).Prepare
20191102 02555149  -    	00.001.458
20191102 02555149  +    	SynDBZeos.TSQLDBZEOSStatement(04d125f0).ExecutePrepared
20191102 02555149 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d125f0) execute block as begin INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (7039890342,'07039890342','TOMMY','','BERGENE',null,timestamp '1998-03-07',timestamp '1998-08-11',1,1,'','','','','0 ','1311','HØVIKODDEN','0219','BÆRUM','','',467,1,1,timestamp '2019-11-02T05:55:51'); INSERT INTO PREG_PERSON (ID,SSN,GivenName,MiddleName,Surname,DateAdrFrom,DateOfBirth,DateStatus,RegStatus,MaritalStatus,MothersNIN,FathersNIN,OldNIN,SpouseNIN,PostalAddress,PostalCode,PostalPlace,St,Municipality,SchoolDistrict,Constituency,BasicStatisticalUnit,CurrentAddress,Gender,LastUpdate) VALUES (9060097620,'09060097620','LINDA','','ERIKSEN',timestamp '1999-08-10',timestamp '2000-06-09',timestamp '2000-08-15',1,1,'','','','','VEI 31 ','1311','HØVIKODDEN','0219','BÆRUM','0419','',null,1,0,timestamp '2019-11-02T05:55:51'); end
20191102 02555149 EXC   		EZIBSQLException ("SQL Error: Dynamic SQL Error; conversion error from string \"2019-11-02T05:55:51\"") [] at cc4cbc 
20191102 02555149  -    	00.002.038
20191102 02555149 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at db3db7 
20191102 02555149 trace 	uNextDB.TNextDB(053c2a10) EngineBatchSend json=1 KB add=2 update=0 delete=0 TSQLPerson
20191102 02555149 warn  	uNextDB.TNextDB(053c2a10) {"EORMException(052e9af0)":{"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"}} -> PARTIAL rollback of latest auto-committed transaction data={"Person~~["automaticTransactionPerRow",10000,"POST~~{"RowID":7039890342,"SSN":"07039890342","GivenName":"TOMMY","MiddleName":"","Surname":"BERGENE","DateAdrFrom":null,"DateOfBirth":"1998-03-07","DateStatus":"1998-08-11","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"0 ","PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"","Constituency":"","BasicStatisticalUnit":467,"CurrentAddress":true,"Gender":1,"LastUpdate":"2019-11-02T05:55:51"},"POST~~{"RowID":9060097620,"SSN":"09060097620","GivenName":"LINDA","MiddleName":"","Surname":"ERIKSEN","DateAdrFrom":"1999-08-10","DateOfBirth":"2000-06-09","DateStatus":"2000-08-15","RegStatus":1,"MaritalStatus":1,"MothersNIN":"","FathersNIN":"","OldNIN":"","SpouseNIN":"","PostalAddress":"VEI 31 ","PostalCode":"1311","PostalPlace":"HØVIKODDEN","St":"0219","Municipality":"BÆRUM","SchoolDistrict":"0419","Constituency":"","BasicStatisticalUnit":null,"CurrentAddress":true,"Gender":0,"LastUpdate":"2019-11-02T05:55:51"}]}
20191102 02555149 EXC   	EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLPerson).BatchMethod=mNone"} [] at db3db7

Without TDateTime property inserting works fine. Inserting without batch also works.

At the same time, batch updating with the same data also works fine:

20191102 03032817 trace uNextDB.TNextDB(054b2a10) BatchSend {"TSQLRestBatch(04d8d840)":{"Count":2,"SizeBytes":1072}}
20191102 03032817  +    uNextDB.TNextDB(054b2a10).EngineBatchSend TSQLPerson inlen=1073
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).Prepare
20191102 03032817  -    	00.001.419
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).ExecutePrepared
20191102 03032817 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d925f0) update PREG_PERSON set SSN='07039890342',GivenName='TOMMY',MiddleName='',Surname='BERGENE',DateAdrFrom=NULL,DateOfBirth='1998-03-07',DateStatus='1998-08-11',RegStatus=1,MaritalStatus=1,MothersNIN='',FathersNIN='',OldNIN='',SpouseNIN='',PostalAddress='0 ',PostalCode='1311',PostalPlace='HØVIKODDEN',St='0219',Municipality='BÆRUM',SchoolDistrict='',Constituency='',BasicStatisticalUnit=467,CurrentAddress=1,Gender=1,LastUpdate='2019-11-02 06:03:28' where ID=7039890342
20191102 03032817  -    	00.002.038
20191102 03032817  +    	SynDBZeos.TSQLDBZEOSStatement(04d925f0).ExecutePrepared
20191102 03032817 SQL   		SynDBZeos.TSQLDBZEOSStatement(04d925f0) update PREG_PERSON set SSN='09060097620',GivenName='LINDA',MiddleName='',Surname='ERIKSEN',DateAdrFrom='1999-08-10',DateOfBirth='2000-06-09',DateStatus='2000-08-15',RegStatus=1,MaritalStatus=1,MothersNIN='',FathersNIN='',OldNIN='',SpouseNIN='',PostalAddress='VEI 31 ',PostalCode='1311',PostalPlace='HØVIKODDEN',St='0219',Municipality='BÆRUM',SchoolDistrict='0419',Constituency='',BasicStatisticalUnit=NULL,CurrentAddress=1,Gender=0,LastUpdate='2019-11-02 06:03:28' where ID=9060097620
20191102 03032817  -    	00.000.329
20191102 03032817 trace 	uNextDB.TNextDB(054b2a10) EngineBatchSend json=1 KB add=0 update=2 delete=0 TSQLPerson

mORMot (fresh), Zeos (tried several latest versions from master branch), Firebird 2.5

Vitaly · mORMot 1

Did you try to use RawUTF8 instead of string type?

Vitaly · mORMot 1

Nice! Thank you!

Vitaly · mORMot 1

As far as I understand, you could use smth like this (following your naming):

  TSQLNamesRecord = class(TSQLRecord)
  ...
  end;
  TSQLNamesRecordToBeDeletedID = type TID;

  TSQLAddressRecord= class(TSQLRecord)
  ...
  end;
  TSQLAddressRecordToBeDeletedID = type TID;  

  TSQLClientsRecord = class(TSQLRecord)
  private
    FName: TSQLNamesRecordToBeDeletedID;
    FAddress: TSQLAddressRecordToBeDeletedID;
  published
    property ClientName: TSQLNamesRecordToBeDeletedID read FName write FName;
    property ClientAddress: TSQLAddressRecordToBeDeletedID read FAddress write FAddress;
  end;

So, if any Name (or Address) record is deleted, all Clients records, which use this particular Name (or Address) ID, also will be removed.

But again, I afraid, that it is not what were you looking for in previous messages. Hope, I'm wrong

Vitaly · mORMot 1

jlc wrote:

it should also delete TSQLAddressRecord row and TSQLCountryRecord row if it's not used for any other TSQLAddressRecord entry.

It is not a cascade deletion (some kind of opposite deletion), so, unfortunately, you will have to make it by your hands, I guess...

Vitaly · mORMot 1

jlc wrote:

What do you mean by this? And how would it be done? By writing own SQL code? But as I use TSQLRestClientDB I don't have any running server, its just a local database for the program which should not contain any garbage/old/unused data.

I meant making some procedure to run in a separate thread, which will run periodically and service DB.
Btw, TSQLRestClientDB creates TSQLRestServerDB, so technically you have running server But it doesn't matter.

From my point of view, dictionaries shouldn't contain any personal information... And everything really personal, concerning a client (including address, but you will need to change your model slightly), can be deleted cascaded by reference to Client table, using T{ClassName}ToBeDeletedID or TRecordReferenceToBeDeleted. See 5.1.8 - 5.1.10 of docs: https://synopse.info/files/html/Synopse … l#TITLE_71 . It seems rather clear to me, although I do not use these options - customers usually don't like the ability to purge any information irretrievably.

Just try to use it following docs, and if you'll have troubles, tell me - I'll try to use it by myself and provide you the code. Or maybe somebody will provide you a sample

Vitaly · mORMot 1

I'd like to help, but I haven't faced such tasks earlier. Can you explain, please, why do you need to check and clean all dictionaries after every single Client removing? Some kind of storage space economy?
Maybe, in this case, it is more reasonable to make a separate server-side procedure for cleaning dictionaries, which will be scheduled once a week/month/year, for example.
Tell me, please, if I understood you wrong.

btw,

jlc wrote:

  TSQLAddressRecord = class(TSQLRecordNoCase)
  private
    FAddress: RawUTF8;
    FCountry: TSQLCountryRecord;
  published
    property Address: RawUTF8 read FAddress write FAddress;
    property Country: RawUTF8 read FCountry write FCountry;
  end;

I think you have mistyped here a bit. Published Country property should be also TSQLCountryRecord type, I guess

Vitaly · mORMot 1

jlc wrote:

I've read that but as I may extend my record and just need to know if the entry already exists, it's enough to get 'Name' field only. That's why I think leaving it with 'Name' is fine.

Well, afaik aCustomFieldsCSV is responsible only for the list of selected fields (forming TSQLTable). You are using CreateAndFillPrepare only for understanding, whether such a record exists or not. Following this task, you will change only where clause, if your record will be extended with other fields.
In fact retrieving only id values may be even a bit more reasonable in case of many heavy fields in table and many checking operations:

NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'ID');

Hope, somebody will correct me, if I'm wrong

jlc wrote:

Is there any other way than always doing a TSQL*Record.CreateAndFillPrepare() with maybe 'NewName' item and FillOne to check if it already exist?
I have another class now which has a reference via AsTSQLRecord to the Name record. When extending it with another few record classes, I'll end up in many calls to TSQL*Record.CreateAndFillPrepare() and FillOne to get the ID if existing or add the item to get in the end the proper reference via AsTSQLRecord. Is there any smarter way?

I hardly can imagine another way of forming dictionaries with unique values in DB I guess, you will always have to check the existing of value in some layer.
Of course, there may be some thoughts, which can make code shorter, like making a child class of some TSQLRest... with some custom adding functions, f.ex. But it totally depends on the situation.
Maybe somebody can suggest something, I'll wait, too

Vitaly · mORMot 1

Is there any way to form a joined table with custom fields?
Like aCustomFieldsCSV parameter in CreateAndFillPrepare works.

I tried to search, but couldn't find any topic, mentioning CreateAndFillPrepareJoined. I am afraid something is wrong with the search on the forum...

Vitaly · mORMot 1

I reread your messages and found more questions

jlc wrote:

NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name'); // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?

I don't think, that you need to point custom fields here:
- default aCustomFieldsCSV='' will retrieve all simple table fields

jlc wrote:

if NameRec.Name = '' then // <- which one is the correct way to use it?
//if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?

If you want to fill your TSQLRecord object published fields, you have to call FillOne first after CreateAndFillPrepare. And FillOne will tell you, if there is any record retrieved resulting boolean value.
So, I would quickly correct a part of your code like this (if I understood your desires right):

  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName]);
   if not NameRec.FillOne then
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

Vitaly · mORMot 1

ah, sorry - I didn't notice that you are working with TSQLRestClientDB, although you've mentioned it several times
Then I guess the first variant will work for you

Vitaly · mORMot 1

You can just add

 YourTSQLModel.Owner := YourTSQLRest;

So YourTSQLModel will be freed automatically with YourTSQLRest destroying. See: https://synopse.info/files/html/api-1.1 … ODEL_OWNER

Or you can do the same through the constructor: https://synopse.info/files/html/api-1.1 … THOWNMODEL

Vitaly · mORMot 1

No, everything works as it is.
I'm just adding a couple of paths to mORMot to project options.

Vitaly · mORMot 1

I'm using mORMot with 10.3.2 for a couple of weeks. Everything works fine. Didn't meet any trouble while migrating from 10.3.1.
Delphi 10.3.2 Professional with Mobile Version 26.0.34749.6593

Vitaly · mORMot 1

Maybe it happens because of the order of values, so it becomes not traditional enumeration. Try standard numeration, beginning with 0:

 TTest = (ttOne = 0, ttTen = 1, ttHund = 2);

So it will be the same as

 TTest = (ttOne, ttTen, ttHund);

My idea from the above was just to avoid the problem "ftTable = 5 soon becomes ftTable = 10" through coding, but not to change the enumeration type It will be not easy to break the enumeration while adding some new member if all old members already have their directly set numbers. Of course, you can do it simpler, just remember to add values only to the end of the enumeration.

Although, maybe there is some another approach, and maybe somebody will share it here

Vitaly · mORMot 1

jaclas wrote:

- eliminates the problem of extending the type in the future by new values (then e.g. ftTable = 5 soon becomes ftTable = 10 and all client code needs to be corrected)

You can use directly pointed values, so it will stay fixed until you change it. Like:

  TRegionLevel = (rlCountry = 0, rlCounty = 1, rlMunicipality = 2, rlBSU = 3);

jaclas wrote:

@Vitaly for enumerated types, see Spring4D and TEnum type :-)

I will, thanks!

Vitaly · mORMot 1

I don't know details of the task - that's why I wrote: "depending on your expectations".
As well as don't know, why working with strings can be better than enumerated for some task But situations may be different and unexpected.
If jaclas wants string parameter instead of enumerated in some method, I don't see any problem to use it. For example, converting with TEnumType to string on some side (client or server, depending on needs) seems to me to be a rather simple and fastly implemented decision. How do you think?

I'm asking because I'm interested in everything concerning enumerated types in mORMot (or Delphi/FPC) - there are more and more of them in my projects, lately. And I want to use all options

Vitaly · mORMot 1

I suppose that something (depending on your expectations) can be done with TEnumType (mORMot unit). There is a batch of pretty functions for working with string values of enumerated types.
As a sample of using you can look at TSQLTable.ExpandAsString function f.ex.

I'm using ExpandAsString in my projects and I didn't see any additional visible reduce of performance with enumerated types, even with big data tables. I admit that working with integer values is much faster, but I think that for most everyday tasks such an economy doesn't worth efforts.
Besides, jaclas asked the question "Is it possible ... ?", and not about performance

mORMot Open Source

#101 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-31 01:23:17

#102 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-30 08:43:02

#103 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-30 07:39:06

#104 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-30 06:54:32

#105 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-29 21:18:59

#106 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-29 13:54:40

#107 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-29 09:52:39

#108 Re: mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-29 08:59:16

#109 mORMot 1 » AV at BatchSend with Zeos/FB » 2020-03-28 20:48:32

#110 Re: mORMot 1 » mORMot.pas compilation error (10.3.3, win64) » 2020-03-04 18:35:13

#111 mORMot 1 » mORMot.pas compilation error (10.3.3, win64) » 2020-03-04 16:33:55

#112 Re: mORMot 1 » Revision 2.x of the framework » 2020-03-04 16:13:12

#113 Re: mORMot 1 » Need help for post a json in a Body » 2020-02-27 10:09:28

#114 Re: mORMot 1 » OpenSSL 1.1 wrapper » 2020-02-24 04:32:36

#115 Re: mORMot 1 » Inline assembler syntax error » 2020-02-21 20:19:52

#116 Re: mORMot 1 » Inline assembler syntax error » 2020-02-19 12:13:23

#117 mORMot 1 » Inline assembler syntax error » 2020-02-19 01:53:02

#118 Re: mORMot 1 » open source project- really? » 2020-02-15 15:03:40

#119 Re: mORMot 1 » Problem with serializing TSynDictionary and TDynArray » 2020-01-14 12:09:12

#120 Re: mORMot 1 » Problem with serializing TSynDictionary and TDynArray » 2020-01-14 11:12:17

#121 Re: mORMot 1 » Store PDF as Blobs in SQlite - performance issues? » 2019-12-17 20:54:06

#122 Re: mORMot 1 » Store PDF as Blobs in SQlite - performance issues? » 2019-12-09 20:25:46

#123 Re: mORMot 1 » DDD project sample and some questions related » 2019-11-29 01:41:26

#124 Re: mORMot 1 » A problem with new 'optimized IdemPropNameUSameLen()' » 2019-11-13 14:08:14

#125 mORMot 1 » A problem with new 'optimized IdemPropNameUSameLen()' » 2019-11-12 20:38:01

#126 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-06 15:02:14

#127 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-05 16:08:48

#128 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-05 13:32:29

#129 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-02 22:43:08

#130 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-02 22:15:00

#131 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-02 21:32:22

#132 Re: mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-02 16:36:44

#133 mORMot 1 » Batch insert with TDateTime property, ORM, Zeos-Firebird » 2019-11-02 03:12:53

#134 Re: mORMot 1 » Bug in param matching in remote interface invokation » 2019-09-22 13:37:14

#135 Re: mORMot 1 » Delphi 64bit sqlite compilation » 2019-09-21 11:12:34

#136 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-28 19:03:11

#137 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 21:48:26

#138 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 20:24:00

#139 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 17:05:36

#140 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 20:25:21

#141 mORMot 1 » CreateAndFillPrepareJoined custom fields » 2019-08-16 13:47:24

#142 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 13:14:39

#143 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 12:42:13

#144 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 09:39:11

#145 Re: mORMot 1 » cant compile with RIO 10.3.2 » 2019-08-10 19:08:40

#146 Re: mORMot 1 » cant compile with RIO 10.3.2 » 2019-08-10 17:58:38

#147 Re: mORMot 1 » Enumerated types in method parameter » 2019-08-10 13:31:31

#148 Re: mORMot 1 » Enumerated types in method parameter » 2019-08-10 12:09:11

#149 Re: mORMot 1 » Enumerated types in method parameter » 2019-08-10 11:39:45

#150 Re: mORMot 1 » Enumerated types in method parameter » 2019-08-10 10:56:34

Board footer