Pages: 1 2

#1 mORMot 1 » Db Oracle - error while calling stored procedure » 2022-11-11 19:37:45

ASiwon
Replies: 0

Hello,

I found small problem in my code which calls stored procedure in the Oracle database. I know that direct calling stored procedures is not the recommended approach, but for now I have no other choice. The procedure has varchar output parameter. When this procedure is called I receive Oracle error: ORA-06502: character string buffer too small. For output param in my code there is a binding like in the documentation:

stmt.BindTextU(2, '', paramOut);

and then I found in the documentation information about exception for the TSQLDBOracleStatement class. The param should be prealocated with code like:

stmt.BindTextU(2, StringOfChar(3000),paramOut);

I'm a little bit confused because it would be nice to have the same calling code which is independent from used database. I wrote some correction to the TSQLDBOracleStatement which allows automatic prealocation for varchar type output parameters. Could you consider to add these changes to the mORMot source code? I could add my corrections as pull request on GitHub.

#2 mORMot 1 » Problem with saving date values to the Oracle in batch mode. » 2020-06-01 13:22:18

ASiwon
Replies: 1

Hi,

I'm using mORMot with Oracle database connected via SynDbOracle. Few days ago I decided to change my process to use batch mode to improve the performance.

After changing the code to use batch mode the program has started to reports databases constraints rules violation for columns with date values. But they occurs only when the Oracle database is used. For SQLite database everything works perfectly.

After long investigation I found the reason. Here is a little long explanation.
In SynDBOracle unit to the execution of queries the procedure ExecutePrepared is used. Here is the piece of code which is used to set date values to the query params:

          for j := 0 to fParamsArrayCount-1 do
            if VArray[j]='null' then // bind null (ftUTF8 should be '"null"')
              aIndicator[i][j] := -1 else begin
              if VDBType=SQLT_INT then
                SetInt64(pointer(Varray[j]),oDataINT^[j]) else
              case VType of
              ftUTF8,ftDate: begin
                L := length(VArray[j])-2; // -2 since quotes will be removed
                if VType=ftDate then
                  if L<=0 then
                    oDataDAT^[j].From(0) else
                    oDataDAT^[j].From(PUTF8Char(pointer(VArray[j]))+1,L) else      <--- Here is the date conversion command
                  if L>oLength then
                    if L*fParamsArrayCount>MAX_INLINED_PARAM_SIZE then
                      raise ESQLDBOracle.CreateUTF8(
                        '%.ExecutePrepared: Array parameter #% STR too big',[self,i+1]) else
                      oLength := L;
              end;

this code runs procedure TOracleDate.From to read date value from the JSON string to the TOracleDate record. In procedure From is used function: Iso8601ToTimeLogPUTF8Char which not always works correctly in this place.

procedure TOracleDate.From(aIso8601: PUTF8Char; Length: integer);
var Value: QWord;
    Value32: cardinal absolute Value;
    Y: cardinal;
    NoTime: boolean;
begin
  PInteger(PtrUInt(@self)+3)^ := 0; // set Day=Hour=Min=Sec to 0
  Value := Iso8601ToTimeLogPUTF8Char(aIso8601,Length,@NoTime);
  if Value=0 then begin
    PInteger(@self)^ := 0;
    exit; // invalid ISO-8601 text -> store as null date
  end;
...

It is because the Iso8601ToTimeLogPUTF8Char function expects date value in format: 2000-01-01 but in this case to the function is passed string from JSON format and it has single quote char
on the end of the string. The string with date looks like: 2000-01-01'. The function expects that after date part will be started time part or the end of the string.
When the function finds single quote char it stops the execution and returns 0. This causes the function for any date string returns value 0 and all these wrong values are saved to the database.

For my purposes I have change the Iso8601ToTimeLogPUTF8Char function code:

function Iso8601ToTimeLogPUTF8Char(P: PUTF8Char; L: integer; ContainsNoTime: PBoolean): TTimeLog;
// bits: S=0..5 M=6..11 H=12..16 D=17..21 M=22..25 Y=26..40
// i.e. S<64 M<64 H<32 D<32 M<16 Y<9999: power of 2 -> use fast shl/shr
var V,B: PtrUInt;
    tab: {$ifdef CPUX86NOTPIC}TNormTableByte absolute ConvertHexToBin{$else}PNormTableByte{$endif};
begin
  result := 0;
  if P=nil then
    exit;
  if L=0 then
    L := StrLen(P);
  if L<4 then
    exit; // we need 'YYYY' at least
  if P[0]='T' then
    dec(P,8) else begin // 'YYYY' -> year decode
    {$ifndef CPUX86NOTPIC}tab := @ConvertHexToBin;{$endif} // faster on PIC/x86_64
    V := tab[ord(P[0])];
    if V>9 then exit;
    B := tab[ord(P[1])];
    if B>9 then exit else V := V*10+B;
    B := tab[ord(P[2])];
    if B>9 then exit else V := V*10+B;
    B := tab[ord(P[3])];
    if B>9 then exit else V := V*10+B;
    result := Int64(V) shl 26;  // store YYYY
    if P[4] in ['-','/'] then begin inc(P); dec(L); end; // allow YYYY-MM-DD
    if L>=6 then begin // YYYYMM
      V := ord(P[4])*10+ord(P[5])-(48+480+1); // Month 1..12 -> 0..11
      if V<=11 then
        inc(result,V shl 22) else begin
        result := 0;
        exit;
      end;
      if P[6] in ['-','/'] then begin inc(P); dec(L); end; // allow YYYY-MM-DD
      if L>=8 then begin  // YYYYMMDD
        V := ord(P[6])*10+ord(P[7])-(48+480+1); // Day 1..31 -> 0..30
        if (V<=30) and(P[8] in [#0,' ','T', '''']) then  <-- here is single quote exception added.

after this change date values are correctly saved to the database. I could add this correction to the pull request, but I'm not sure is it the best place to make correction for this problem.

#3 mORMot 1 » Problem with deleting record in Oracle » 2018-09-27 09:55:41

ASiwon
Replies: 1

Hello,

today I found problem with using mORMot's Delete function for object which is mapped to the table in Oracle database. I have model class which looks like:

  TBkLogZmiana = class(TSQLRecord)
  strict private
    FRodzajId: TBkLogZmianaRodzajKey;
    FZmianaId: TBkLogTabelaKey;
  published
      /// <summary>
      /// Identyfikator rodzaju zmiany.
      /// </summary>
    property RodzajId: TBkLogZmianaRodzajKey read FRodzajId write FRodzajId;
      /// <summary>
      /// Identyfikator informacji o zmianie w tabeli bazy danych.
      /// </summary>
    property ZmianaId: TBkLogTabelaKey read FZmianaId write FZmianaId;
  end;

this class is mapped to the table:

create table U_BTK.T_LOG_ZMIANY
(
  lz_id     INTEGER not null,   -- mapped to ID
  lz_lt_id  INTEGER not null,  -- mapped to ZmianaId
  lz_lzr_id INTEGER not null,  -- mapped to RodzajId
);

When I try to execute the code:

  Client.Delete(TBkLogZmiana, FormatUTF8('%<? and %=?', [ZmianaId, RodzajId], [SomeValue, 1]));

in log file is generated error:

20180927 08495920  - SQL   				SynDBOracle.TSQLDBOracleStatement(01BD2780) delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<32569 and RodzajId=1
20180927 08495920  - EXC   				ESQLDBOracle {"Statement":{"SQL":"delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<? and RodzajId=?","SQLWithInlinedParams":"delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<32569 and RodzajId=1","CurrentRow":0,"TotalRowsRetrieved":0,"Connection":{"Connected":true,"ServerTimeStampAtConnection":"2018-09-27T09:59:45","TotalConnectionCount":1,"TransactionCount":1,"InTransaction":true,"RollbackOnDisconnect":true,"LastErrorMessage":"","LastErrorWasAboutConnection":false,"Properties":{"ClientVersion":"oci.dll rev. 11.2.0.1","EnvironmentInitializationMode":7,"InternalBufferSize":131072,"RowsPrefetchSize":131072,"BlobPrefetchSize":4096,"StatementCacheSize":30,"UseWallet":false,"IgnoreORA01453OnStartTransaction":false,"Engine":"Oracle","ServerName":"GIDP2","DatabaseNameSafe":"","UserID":"bk_app_server","DBMS":"dOracle","DBMSEngineName":"Oracle","BatchSendingAbilities":["cCreate","cUpdate","cDelete"],"BatchMaxSentAtOnce":10000,"LoggedSQLMaxSize":0,"LogSQLStatementOnException":false,"ForcedSchemaName":"","UseCache":true,"RollbackOnDisconnect":true,"StoreVoidStringAsNull":false}},"StripSemicolon":true},"Message":"TSQLDBOracleStatement error: ORA-00904: \"RODZAJID\": niepoprawny identyfikator"} at 00FBEF99  stack trace API 00DBF05F 5005FF40

it looks like one part of the SQL query was translated to SQL and second part (where clause section) was not translated and this made error. I tried to execute the code in the following way:

  Client.Delete(TBkLogZmiana, FormatUTF8('%<? and %=?', ['lz_lt_id', 'lz_lzr_id'], [SomeValue, 1]));

but this change doesn't  works. mORMot can't to read IDs of deleted record to notification mechanism and reports following error:

08:38:25	Warning	11	             mORMotDB.TSQLRestStorageExternal(78B68410) TSQLRestStorageExternal.AdaptSQLForEngineList: statement too complex -> would use SQLite3 virtual engine [SELECT RowID FROM BkLogZmiana WHERE LZ_LT_ID<:(83446693): and LZ_LZR_ID=:(1):]
08:38:25	Exception	11	             ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT RowID FROM BkLogZmiana WHERE LZ_LT_ID<? and LZ_LZR_ID=?] using 3.19.2 - no such column: LZ_LT_ID, extended_errcode=1"} at 01400CEA  stack trace API 0130395B 01303984 5005FF40 
08:38:25	Result	11	             {"TSQLDatabase(78FEB920)":{"FileName":":memory:","IsMemory":true,"UseCache":false,"TransactionActive":true,"BusyTimeout":0,"CacheSize":-2000,"PageSize":4096,"PageCount":19,"FileSize":77824,"WALMode":false,"Synchronous":"smFull","LockingMode":"lmNormal","MemoryMappedMB":0,"user_version":0,"OpenV2Flags":6,"BackupBackgroundInProcess":false,"BackupBackgroundLastTime":"","BackupBackgroundLastFileName":"","SQLite3Library":{"TSQLite3LibraryStatic(7F951850)":{"Version":"3.19.2 with internal MM"}}}}
08:38:25	Debug	11	             mORMotSQLite3.TSQLRestServerDB(78F41E90) TSQLRestRoutingREST.Error: {  "errorCode":400,  "errorText":"Bad Request"  }

I'm using mORMot revision 4235 (from SVN repository) from 5.September.2017 10:24:13.

#4 Re: mORMot 1 » TableRowCount overloaded version » 2018-04-28 20:43:55

ASiwon

Hello,

could you explain it better? Maybe I'm working with older version of mORMot but for me this function work with TSQLRestServerFullMemory correctly. Of course it highly depends on condition in the where query section which is processed in TSQLRestStorageInMemory.EngineList function. If EngineList could correctly processes SQL query then function returns correct values. If not function returns as expected value -1. For simple conditions like PropertyName=Value it returns expected values.

#5 mORMot 1 » TableRowCount overloaded version » 2018-04-26 11:47:14

ASiwon
Replies: 3

Hello,

is it possible to add to TSQLRest class new, overloaded version of TableRowCount function? In some cases I want to get count of rows in table which meets additional conditions. For my needs I made new version of TableRowCount function:

function TSQLRest.TableRowCount(Table: TSQLRecordClass; WhereClause: RawUTF8): Int64;
var T: TSQLTableJSON;
begin
  if (self=nil) or (Table=nil) then
    T := nil else
    T := ExecuteList([Table], Model.Props[Table].SQLFromSelectWhere('Count(*)', WhereClause));
  if T<>nil then
  try
    Result := T.GetAsInt64(1,0);
  finally
    T.Free;
  end else
    Result := -1;
end;

standard TableRowCount function could be changed to:

function TSQLRest.TableRowCount(Table: TSQLRecordClass): Int64;
begin
  Result := TableRowCount(Table, '');
end;

declarations for both functions could be:

    /// get the row count of a specified table
    // - returns -1 on error
    // - returns the row count of the table on success
    // - calls internaly the "SELECT Count(*) FROM TableName;" SQL statement
    function TableRowCount(Table: TSQLRecordClass): Int64; overload; virtual;
    /// get the row count of a specified table with additional where condition clause
    // - returns -1 on error
    // - returns the row count of the table matching where clause on success
    // - calls internaly the "SELECT Count(*) FROM TableName WHERE WhereClause;" SQL statement
    function TableRowCount(Table: TSQLRecordClass; WhereClause: RawUTF8): Int64; overload;

If this is possible please add this change to the mORMot source.

#6 Re: mORMot 1 » Compilation Error in Current Nightly Build » 2017-06-22 16:21:32

ASiwon

Hi,

I have tried to compile project with fix. The problem still occured. So I have to start to check what is going wrong. After small investigation I found old version of the Synopse.inc file. This file was used during compilation and that is why the problem occured in my environment. My apologies for reporting non existing problem.

#7 Re: mORMot 1 » Compilation Error in Current Nightly Build » 2017-06-22 13:10:44

ASiwon

Hi,

I think I have the same problem with compilation on mORMot build 4101 in svn repository. Date of commit is: 9:21:51 22 June. I'm using Delphi XE4 Pro, platform target is Win32. Exactly compiler occurs an error:

E2441 Inline function declared in interface section must not use local symbol Deref.

When I move declaration of the Deref function to the interface section in mORMot unit compilation is done correctly. If you wish I could to prepare pull request on github.

#8 Re: mORMot 1 » TSQLRest.Retrieve and the InternalState » 2017-06-21 12:52:02

ASiwon

Hi,

I have add a pull request for this issue: https://github.com/synopse/mORMot/pull/43
Please add this correction to the mORMot source code.

#9 Re: mORMot 1 » TSQLRest.Retrieve and the InternalState » 2017-06-19 13:42:09

ASiwon

Hi,

is it possible to add filling InternalState property for RetrieveList* methods? Especially in generics version it would be very usefull for me. It could be done in TSQLTable.ToObjectList<T>

function TSQLTable.ToObjectList<T>: TObjectList<T>;
var R,Item: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
begin
  result := TObjectList<T>.Create; // TObjectList<T> will free each T instance
  if (self=nil) or (fRowCount=0) then
    exit;
  R := TSQLRecordClass(T).Create;
  try
    R.FillPrepare(self);
    Row := @fResults[FieldCount];     // Row^ points to first row of data
    {$ifdef ISDELPHIXE3}
    result.Count := fRowCount;         // faster than manual Add()
    for i := 0 to fRowCount-1 do begin
      Item := TSQLRecordClass(T).Create;
      PPointerArray(result.List)[i] := Item;
    {$else}
    for i := 0 to fRowCount-1 do begin
      Item := TSQLRecordClass(T).Create;
      Result.Add(Item);
    {$endif}
      R.fFill.Fill(pointer(Row),Item);
      Item.fInternalState := Self.InternalState;   // Filling InternalState property
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;

If it is impossible to change TSQLTable.ToObjectList function it could be done in TSQLRest.RetrieveList<T>. In this case function should be changed to:

function TSQLRest.RetrieveList<T>(const FormatSQLWhere: RawUTF8;
  const BoundsSQLWhere: array of const; const aCustomFieldsCSV: RawUTF8): TObjectList<T>;
var Table: TSQLTable;
  i: Integer;
begin
  result := nil;
  if self=nil then
    exit;
  Table := MultiFieldValues(TSQLRecordClass(T),aCustomFieldsCSV,FormatSQLWhere,BoundsSQLWhere);
  if Table<>nil then
  try
    result := Table.ToObjectList<T>;
      // filling InternalState property
    for i := 0 to result.Count - 1 do
      result[i].fInternalState := Table.InternalState;
  finally
    Table.Free;
  end;
end;

#10 Re: mORMot 1 » Unexpected Access Violation exception on service call » 2017-05-04 06:09:59

ASiwon

Hi,

indeed. The problem occurs in older version of my application, which was linked to the older version of mORMot. I think now everything will works correctly. Thank you very much for answer.

#11 mORMot 1 » Unexpected Access Violation exception on service call » 2017-05-03 21:13:02

ASiwon
Replies: 2

Hi,

i found in log of my application an unexpected Access Violation exception. Here is fragment of log file:

10:42:43	Enter	16	                         mORMotSQLite3.TSQLRestServerDB(0126E7D0).URI(POST root/BkKomunikatyOperator.LogujWyswietlenie/6648 inlen=13)
10:42:43	Service call	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) BkKomunikatyOperator.LogujWyswietlenie[98754,10561]
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6684) after 1917563 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D340) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D340)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6996) after 1917547 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C140) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C140)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6264) after 3078891 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0CFE0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0CFE0)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6796) after 1984094 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D580) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D580)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=1492) after 3008329 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0CEC0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0CEC0)":{"LastErrorCode":201,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6800) after 3008313 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D8E0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D8E0)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6464) after 1983813 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0CC80) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0CC80)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875307}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6224) after 2519141 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C020) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C020)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6648) after 1877672 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0CB60) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0CB60)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6976) after 1982860 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D460) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D460)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=5044) after 1917579 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C380) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C380)":{"LastErrorCode":201,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6228) after 3359766 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D7C0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D7C0)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6812) after 3359766 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C260) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C260)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6236) after 1982579 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C6E0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C6E0)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875307}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6232) after 1917579 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0CDA0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0CDA0)":{"LastErrorCode":201,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Enter	17	                   mORMotSQLite3.TSQLRestServerDB(0126E7D0).URI(POST root/BkKomunikatyOperator.LogujWyswietlenie/6996 inlen=13)
10:42:43	Service call	17	                      mORMotSQLite3.TSQLRestServerDB(0126E7D0) BkKomunikatyOperator.LogujWyswietlenie[98754,10533]

<Here is a problem>

10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6804) after 1983110 ms timeout (max 1800000 ms)
10:42:43	Debug	17	                      mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6804) after 1983110 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0C4A0) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0C4A0)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Deleted BkKomunikatyOperator instance (id=6992) after 1983344 ms timeout (max 1800000 ms)
10:42:43	Info	16	                            mORMotSQLite3.TSQLRestClientDB(01D0D100) TSQLRestClientDB.Destroy -> {"TSQLRestClientDB(01D0D100)":{"LastErrorCode":200,"LastErrorMessage":"","MaximumAuthentificationRetry":0,"RetryOnceOnTimeout":false,"SessionID":1,"SessionServer":"","SessionVersion":"","ServerTimeStamp":135374875306}}
10:42:43	Debug	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) TServiceFactoryServer.InternalInstanceRetrieve: Adding BkKomunikatyOperator instance (id=1492)
10:42:43	Enter	16	                            uBkKomunikatyOperatorzy.TBkSerwisKomunikatyOperator(045D8740).LogujWyswietlenie
10:42:43	Debug	16	                               Wykonano preparację struktury.
10:42:43	Enter	16	                               mORMotSQLite3.TSQLRestClientDB(01D0D100).CallBackGet root/TimeStamp
10:42:43	Enter	16	                                  mORMotSQLite3.TSQLRestServerDB(0126E7D0).URI(GET root/TimeStamp inlen=0)
10:42:43	Service call	16	                                     mORMotSQLite3.TSQLRestServerDB(0126E7D0) TimeStamp 
10:42:43	Server	16	                                     mORMotSQLite3.TSQLRestServerDB(0126E7D0)   GET root/TimeStamp SOA-Method -> 200 with outlen=12 in 16 us
10:42:43	Service return	16	                                     mORMotSQLite3.TSQLRestServerDB(0126E7D0) 135374875307
10:42:43	Leave	16	                                  00.000.027
10:42:43	Service return	16	                                  mORMotSQLite3.TSQLRestClientDB(01D0D100) 135374875307
10:42:43	Leave	16	                               00.000.036
10:42:43	Enter	16	                               mORMotSQLite3.TSQLRestServerDB(0126E7D0).URI(POST root/BkKomunikatLog inlen=126)
10:42:43	Exception OS	17	                      EAccessViolation (C0000005) at 006FC205  stack trace 5006119C 007993F8 007B1142 011D0BC8 0079D39F 007A1DED 5016C3B9 50060BA6 751F7C04 770CAB8F 770CAB5A 
10:42:43	Debug	16	                                  Statement cache HIT
10:42:43	SQL	16	                                  SynDBOracle.TSQLDBOracleStatement(01D0C4A0) insert into U_BTK.T_BTK_KOMUNIKATY_LOGI (kml_id,kml_czas_sprawdzenia,kml_czas_wyswietl,kml_czas_zamkniecia,kml_km_id,kml_uz_id) values (315479,NULL,'2017-04-29 12:42:43',NULL,98754,10561)
10:42:43	Debug	17	                      mORMotSQLite3.TSQLRestServerDB(0126E7D0) TSQLRestRoutingREST.Error: {  "errorCode":500,  "error":  {"EAccessViolation":{"EAccessViolation":"Access violation at address 006FC205 in module 'MORMotCore18.bpl'. Read of address 00000000"}}  }
10:42:43	Server	17	                      mORMotSQLite3.TSQLRestServerDB(0126E7D0)   POST root/BkKomunikatyOperator.LogujWyswietlenie SOA-Interface -> 500 with outlen=169 in 1325 us
10:42:43	Leave	17	                   00.001.338
10:42:43	DB	16	                                  SynDBOracle.TSQLDBOracleStatement(01D0C4A0) 0 row(s) in 2.25ms
10:42:43	Server	16	                                  mORMotSQLite3.TSQLRestServerDB(0126E7D0)   POST root/BkKomunikatLog ORM-Write -> 201 with outlen=0 in 2339 us
10:42:43	Leave	16	                               00.002.352
10:42:43	Debug	16	                               Dodano zapis o wyświetleniu komunikatu. Id: 315479
10:42:43	Leave	16	                            00.002.435
10:42:43	Server	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0)   POST root/BkKomunikatyOperator.LogujWyswietlenie SOA-Interface -> 200 with outlen=29 in 4004 us
10:42:43	Service return	16	                            mORMotSQLite3.TSQLRestServerDB(0126E7D0) {"result":[315479],"id":1492}
10:42:43	Leave	16	                         00.004.018

As you can see at some point thread 16 deletes BkKomunikatyOperator instance (id=6804) in the next step thread 17 deletes the same BkKomunikatyOperator instance. Instance is destroyed in thread 16 and in thread 17 Access Violation exception is thrown. I'm very suprised with this results. I have check source code and I see that InternalInstanceRetrieve is protected using critical section so two thread shouldn't execute this method in the same time. Could anybody help me to explain or resolve this problem? Any help will be very appreciated.

I'm using mORMot revision (3815 from SVN repository, 2017 February 28, 11:42:24) and Delphi XE4 Pro. Service is registered as sicPerThread.

#12 mORMot 1 » Documentation - example for section 5.1.12. Record fields » 2017-03-09 18:38:11

ASiwon
Replies: 0

Hello,

I think in examle in section 5.1.12. Record fields exists mistake:

class procedure TSQLMyRecord.InternalRegisterCustomProperties(
  Props: TSQLRecordProperties);
begin
  Props.RegisterCustomPropertyFromTypeName(self,'TGUID','GUID',
    @TSQLRecordCustomProps(nil).fGUID,[aIsUnique],38);  <----------- Here is a problem
end;

TSQLRecordCustomProps class does not contain field fGUID and compiler stops with error. I think this code should looks like:

class procedure TSQLMyRecord.InternalRegisterCustomProperties(
  Props: TSQLRecordProperties);
begin
  Props.RegisterCustomPropertyFromTypeName(self,'TGUID','GUID',
    @TSQLMyRecord(nil).fGUID,[aIsUnique],38);

#13 mORMot 1 » Access Violation while preparing SQL query » 2017-03-03 10:33:45

ASiwon
Replies: 1

Hello,

few days ago I have migrate to the current version of mORMot. Today an Access Violation exception occured in my code:

  lWagonyPobyty := Client.RetrieveList<TBkWagonPobyt>('% in (%)', [cGxPropId, lValues],
    [], RawUTF8ArrayToCSV([cGxPropId, cBkWagPobytPrzybycieCzas]));

I'm not sure but probably this problem occurs only when I'm working with external database (Oracle) Unfortunatelly in my case lValues was empty string. So where clause was generated as: 'id in ()'. It is not correct of course but I expect to occur HTTP_BADREQUEST error in this situation rather than Access Violation. I think the problem exists in function GetWhereValues in SynCommons.pas unit. Now the first condition in function is:

  if Where.ValueSQLLen<2 then
    exit;

I think the first condition in function should looks like:

  if Where.ValueSQLLen<=2 then
    exit;

#14 mORMot 1 » Using SQL JOIN to limit retrieved data » 2017-03-01 14:03:25

ASiwon
Replies: 0

Hello,

Is it possible in mORMot to limiting retrieved data with conditions based on SQL JOIN clause. For example I have following model:

  TDocument = class(TSQLRecord)
  strict private
    FDate: TDateTime;
  published
    property Date: TDateTime read FDate write FDate;
  end;

  TPosition = class(TSQLRecord)
  strict private
    FDocumentId: TRecordReference;
    FNumber: Integer;
  published
    property DocumentId: TRecordReference read FDocumentId write FDocumentId;
    property Number: Integer read FNumber write FNumber;
  end;

Now I would like to get positions lists for documents which have for example Date value is from specified range. I would like to achieve SQL query which looks like:

select position.* from Positions join Documents on Documents.Id = Positions.DocumentId and (where clause for Documents table) where (optional where clause for Positions table)

I don't want to get any data from Documents table. I just want to build condition based on Documents to limit data from Positions. I could of course to read identifiers from Documents and then generate long expression list and use it with 'in' operator to get Positions from requested Documents, but I think query which use join clause will be better optimized on SQL server.

best regards
Adam Siwon

#15 Re: mORMot 1 » Oracle - problem with limiting ordered data » 2016-04-19 20:16:34

ASiwon

Hi ab,

As you wish I added pull request on github. (https://github.com/synopse/mORMot/pull/14)

#16 mORMot 1 » Oracle - problem with limiting ordered data » 2016-04-18 08:32:54

ASiwon
Replies: 3

Hello,

I found the problem with reading objects from the Oracle database if for query are used limit and order by clauses. For example for entity class:

TSQLBaby = class(TSQLRecord)
  private
    fName: RawUTF8;
    fAddress: RawUTF8;
    fBirthDate: TDateTime;
    fSex: TSex;
  published
    property Name: RawUTF8 read fName write fName;
    property Address: RawUTF8 read fAddress write fAddress;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
    property Sex: TSex read fSex write fSex;
end;

query:

lBaby := TSQLBaby.Create(Client, 'Name = ? order by BirthDate desc limit 1', ['SomeName']);

will be executed in Oracle as:

select Id, Name, Address, BirthDate, Sex from Baby where rownum <=1 and Name = 'SomeName'  order by BirthDate

The problem is using rownum and order by in Oracle SQL. RowNum is called before executing "order by" clause and from time to time query returns data not for the youngest baby. It is known Oracle behaviour: rownum conditions should not be used with queries with order by clause. In Oracle SQL this query should looks like:

select * from (
  select Id, Name, Address, BirthDate, Sex from Baby where Name = 'SomeName'  order by BirthDate) where rownum <=1

To correct this problem I made some changes in mORMot sources. In SynDb.pas file to TSQLDBDefinitionLimitPosition type I added new item: posOuter

TSQLDBDefinitionLimitPosition = (posNone, posWhere, posSelect, posAfter, posOuter);

for class TSQLDBConnectionProperties I changed function SQLLimitClause to:

function SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; virtual;

function TSQLDBConnectionProperties.SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause;
begin
  result := DB_SQLLIMITCLAUSE[DBMS];
end;

only function header was changed. In the body of the function nothing was changed. In SynDbOracle unit for class TSQLDBOracleConnectionProperties I added function SQLLimitClause overrides:

function SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause; override;

function TSQLDBOracleConnectionProperties.SQLLimitClause(const AStmt: TSynTableStatement): TSQLDBDefinitionLimitClause;
begin
  if AStmt.OrderByField <> nil then
  begin
    Result.Position := posOuter;
    Result.InsertFmt := 'select * from (%) where rownum <=%';
  end
  else
    Result := inherited SQLLimitClause(AStmt);
end;

In mORMotDB unit I made some changes in AdaptSQLForEngineList function. Block

    if Stmt.Limit=0 then
      limit.Position := posNone else begin
      limit := fProperties.SQLLimitClause;
      if limit.Position=posNone then begin
        InternalLog('%.AdaptSQLForEngineList: unknown "%" LIMIT syntax for [%]',
          [ClassType,ToText(fProperties.DBMS)^,SQL],sllWarning);
        exit;
      end;
      limitSQL := FormatUTF8(limit.InsertFmt,[Stmt.Limit]);
    end;

was changed to

    if Stmt.Limit=0 then
      limit.Position := posNone else begin
      limit := fProperties.SQLLimitClause(Stmt);
      if limit.Position=posNone then begin
        InternalLog('%.AdaptSQLForEngineList: unknown "%" LIMIT syntax for [%]',
          [ClassType,ToText(fProperties.DBMS)^,SQL],sllWarning);
        exit;
      end;
      if  limit.Position = posOuter then
        limitSQL := FormatUTF8(limit.InsertFmt,['%', Stmt.Limit])
      else
        limitSQL := FormatUTF8(limit.InsertFmt,[Stmt.Limit]);
    end;

and block:

      W.SetText(SQL);
      result := true;

was changed to:

      W.SetText(SQL);
      if limit.Position = posOuter then
        SQL := FormatUTF8(limitSQL, [SQL]);
      result := true;

After those changes SQL queries with ORDER BY clause in Oracle looks like:

select * from (
  select Id, Name, Address, BirthDate, Sex from Baby where Name = 'SomeName'  order by BirthDate) where rownum <=1

and SQL queries without ORDER BY clause in the Oracle looks like:

select Id, Name, Address, BirthDate, Sex from Baby where rownum <=1 and Name = 'SomeName'

I hope it will be possible to add those changes to the mORMot source.

#17 Re: mORMot 1 » Problem with reading data from Oracle database. » 2015-12-02 18:28:09

ASiwon

Hi ab!

On the client side LastError properties works perfectly. Now after few months I found the same problem but on the server side. Here I have only pointer to TSQLRestServer class instance. In this class there is no information about errors which occurs while model class instance creation. How can I check information about errors for TSQLRestServer instance on server side?

#18 Re: mORMot 1 » Growing amount of the memory used on server » 2015-10-06 20:46:26

ASiwon

Do you mean executing function OCIStmtPrepare2 in OCI_PREP2_CACHE_SEARCHONLY mode? I have experimentally changed TSQLDBOracleStatement.Prepare procedure to the following form:

procedure TSQLDBOracleStatement.Prepare(const aSQL: RawUTF8;
  ExpectResults: Boolean);
var oSQL: RawUTF8;
    prepare: Boolean;
    Env: POCIEnv;
begin
  fTimeElapsed.ProfileCurrentMethod;
  try
    if (fStatement<>nil) or (fColumnCount>0) then
      raise ESQLDBOracle.CreateUTF8('%.Prepare should be called only once',[self]);
    // 1. process SQL
    inherited Prepare(aSQL,ExpectResults); // set fSQL + Connect if necessary
    fPreparedParamsCount := ReplaceParamsByNames(aSQL,oSQL);
    // 2. prepare statement
    Env := (Connection as TSQLDBOracleConnection).fEnv;
    with OCI do begin
      HandleAlloc(Env,fError,OCI_HTYPE_ERROR);
      if fUseServerSideStatementCache then
      begin
        prepare := StmtPrepare2(TSQLDBOracleConnection(Connection).fContext,fStatement,
          fError,pointer(oSQL),length(oSQL),nil,0,OCI_NTV_SYNTAX,OCI_PREP2_CACHE_SEARCHONLY) <> OCI_SUCCESS;

        if prepare then
          Check(nil,self,StmtPrepare2(TSQLDBOracleConnection(Connection).fContext,fStatement,
            fError,pointer(oSQL),length(oSQL),nil,0,OCI_NTV_SYNTAX,OCI_DEFAULT),fError);
      end
      else begin
        HandleAlloc(Env,fStatement,OCI_HTYPE_STMT);
        Check(nil,self,StmtPrepare(fStatement,fError,pointer(oSQL),length(oSQL),
          OCI_NTV_SYNTAX,OCI_DEFAULT),fError);
      end;
    end;
    // 3. retrieve column information and dispatch data in row buffer
    SetColumnsForPreparedStatement;
  except
    on E: Exception do begin
      SynDBLog.Add.Log(sllError,E);
      FreeHandles(True);
      raise;
    end;
  end;
end;

This version works correctly but change does not helps. Amount of the memory used by server still grows.

#19 Re: mORMot 1 » Growing amount of the memory used on server » 2015-10-06 13:38:38

ASiwon

After turn off of the caching, service is executed slower about 5-7%. Its not a problem. Maybe caching should be turned off as default for TSQLDBOracleConnectionProperties class?

#20 Re: mORMot 1 » Growing amount of the memory used on server » 2015-10-06 11:05:00

ASiwon

No. I have only one query, which returns huge amount of data and a huge amount of queries which returns small pieces of data. I must to check how much it slows down.

#21 Re: mORMot 1 » Growing amount of the memory used on server » 2015-10-06 10:31:43

ASiwon
ab wrote:

First of all, ensure you use the latest revision of SynDBOracle, and the framework.

Ok. Today I make tests on the current version (SHA-1: 430ac9e7dab570 in GIT repository) Yesterday tests were made on version SHA-1: 33cd1280368d355.

ab wrote:

I guess this is not a ORM memory consumption.
The ORM cache has a property high level of memory size, which is a few dozen of MB.

Yes. I agree.

ab wrote:

I guess this has something to do with how SynDBOracle is used.
Are you using the ORM?
Or plain SQL?

I'm using pure ORM. Not SQL.

ab wrote:

How are you doing your queries?
I guess this is the ORM.

Here is a sample code:

      wagon := TTCWagon.Create(FTCClient, '% = ?', ['Id'], [APosition.Key]);

I have one query which selects 100.000 records on begin of the every logical session and then a lot of queries which selects one or few records from REST server.

ab wrote:

Try to change the threading settings.
Try with

aServer.AcquireExecutionMode[execORMGet] := amBackgroundThread;
 aServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

See http://synopse.info/files/html/Synopse% … #TITLE_269

After this correction there are no changes. Amount of used memory still grows.

ab wrote:

Also try to set 

TSQLDBOracleConnectionProperties.UseCache := false;

This is a cache at SynDBOracle level.

It works. Amount of used memory not grows, but execution is slowed down.

#22 mORMot 1 » Growing amount of the memory used on server » 2015-10-05 20:52:32

ASiwon
Replies: 16

Hello,

I have problem with my mORMot powered serwer. I have two applications: server and client using mORMot written in Delphi XE4. Server use Oracle 11 SEO database server (with TSQLDBOracleConnectionProperties from SynDbOracle unit) Client reads a lot of data (2,5 milion rows during few hours) from server. There is no any services on server - just reading data via JSON RestFull Client-Server mechanism. Data reading is divided on portions about 100.000 reads per one logical session. Each session starts every 5 minutes. After each session amount of memory used by the server process grows. In extremal situation process is using 2GB memory and for every data request returns exception: HTTP/1.1 500 Internal Server Error  (application/json)

{
"errorCode":500,
"error":
{"EOutOfMemory":{"EOutOfMemory":"Out of memory"}}
}

At first I was thinking that is simple memory leaks. I have changed memory manager (FastMM) to FullDebug mode. But after server process closing it was nothing reported. No memory leak detected. I have turned off caching using code:

Server.Db.UseCache := False;

It nothing changed. So I was thinking maybe some object allocates memory, accumulates and frees it just before server closing. I have tried to monitor memory usage while server is working. I didn't found anything special while this process. Amount of the used memory still grows but it was no new memory allocations in the process reported by FastMM. Maybe the memory is allocated outside of FastMM - in some dll library? The only external library which I'm directly using is oci.dll (Oracle client) via SynDbOracle unit. So I have changed database layer. Instead of class TSQLDBOracleConnectionProperties from SynDbOracle I have used class TOleDBOracleConnectionProperties from SynOleDb unit. After this change everything works correctly - amount of the used memory using grows up to the one value and it stays on this level during few hours (tomorrow I will make longer test)

Is it any chance to find what exactly causes this problem and fix it?

#23 Re: mORMot 1 » Differences in ORM code for SQLIte and Oracle. » 2015-09-10 10:22:03

ASiwon

For me it was also strange behaviour in Oracle. Empty string is empty string not a null. For this particular case I have changed my code and I'm retrieving all records from table and I'm testing values after retrieving them. In this case I can do that because there are not much objects in the table. But in other cases it could be not so easy.

The similar problem exists with using functions in where clauses in mORMot. Some time ago I have tried to use following condition: upper(fieldName) = ?. For sqlite it is possible and it's working, but for Oracle it's not working - an exception is raised while retrieving objects. But in this case I'm afraid it is mORMot related problem because in Oracle SQL exists function upper.

#24 mORMot 1 » Differences in ORM code for SQLIte and Oracle. » 2015-09-10 08:07:44

ASiwon
Replies: 4

Hello,

I have problem with using in mORMot one code for two databases (sqlite and Oracle) My model looks like:

  TBkTowar = class(TSQLRecord)
  private
    FTextId: RawUTF8;
  published
    property TextId: RawUTF8 index 18 read FTextId write FTextId;
  end;

I want to retrieve list of the objects where TextId has not empty value. So I wrote following code:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% <> ?', ['TextId'], ['']);

For sqlite (memory file) it works correctly but not for Oracle. To retrieve objects with not empty values from Oracle database I must to change code to the following:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% is not null', ['TextId'], []);

Its because Oracle treats empty string as null. Now it works correctly for Oracle. But now objects from sqlite are not retrieved correctly. I could to change code to the following way:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% is not null or % <> ?', ['TextId', 'TextId'], ['']);

Last time I wrote: It will works correctly for both databases but it is not really clean code. But I made mistake and this will also not works correctly for both databases.

Is it any way in mORMot to use one condition which will be correctly works for both or even for other databases?

#25 mORMot 1 » TSQLRest.Update question » 2015-08-05 21:41:35

ASiwon
Replies: 1

Hello,

While creating some units test for my classes I found mORMot behaviour which is unexpected to me. When I try to call Update method to update record which does not exists in the database (database in memory for testing purposes) then Update function returns True value. I have expected that it should to return False value in this case. In the documentation exists information: function return true on success but what function should return in the case of update non existing record?

#26 mORMot 1 » Problem with serializing RawByteString properties » 2015-08-05 21:26:44

ASiwon
Replies: 1

Hello,

I have problem with the following code:

  TUserCredentials = class(TPersistent)
...
  published
    property PasswordEncoded: RawByteString read FPasswordEncoded write FPasswordEncoded;
    property PasswordPlain: RawUTF8 read FPasswordPlain write SetPasswordPlain;
    property UserName: RawUTF8 read FUserName write FUserName;
  end;

procedure Test;
var
  tmp: RawUTF8;
  uc: TUserCredentials;
  valid: Boolean;
begin
  uc := TUserCredentials.Create;
  uc.UserName := 'abc';
  uc.PasswordEncoded := #$f7#$17#$f0#$2f#$4b#$5c#$6e#$e4#$7d#$35#$14#$5b#$e4#$19#$14#$1B#$29;
  tmp := ObjectToJSON(uc);
  JSONToObject(uc, @tmp[1], valid);
end;

On the end of the procedure Test uc structure should be deserialized but in PasswordEncoded property is always empty string. When I  change line where value is assigned to property to the following way:

  uc.PasswordEncoded := BinToBase64(#$f7#$17#$f0#$2f#$4b#$5c#$6e#$e4#$7d#$35#$14#$5b#$e4#$19#$14#$1B#$29);

then after deserialization in property is exactly the same value like before serialization. Is it required to make conversion to base64 format manually or there is a bug in serialization or deserialization procedure?

Btw I think I found small mistake in documentation. In the last line of the section 5.1.11 exists information:

Or via TSQLRestClientURI.TSQLRestClientURI.ForceBlobTransfertTable[] property,

I think it should looks like:

Or via TSQLRestClientURI.ForceBlobTransfertTable[] property,

or even

Or via TSQLRestClient.ForceBlobTransfertTable[] property,

because now property ForceBlobTransfertTable is defined in TSQLRestClient class.

#27 Re: mORMot 1 » Two Oracle and mORMot question » 2015-07-25 16:58:52

ASiwon

Hello,

I have checked code and I found where the problem exists. ORA-28001 exception has status OCI_ERROR not OCI_SUCCESS_WITH_INFO. Thats why exception is raised and OnStatementInfo event is never fired. So I think ORA-28001 error should be supported separately. I wrote some changes to the SynDBOracle unit which could to handle password changing when it is expired. Modified version of file is available at: http://gidex.com.pl/download/SynDBOracle.pas. You could use those changes if they are valuable for you.

Those changes was made only for Oracle so I made changes only in SynDbOracle unit. I think some changes could be moved to the base classes to better integration with mORMot library.

#28 Re: mORMot 1 » Two Oracle and mORMot question » 2015-07-23 20:08:02

ASiwon

Hello,

I have checked changes you made. I'm sorry for the delay. OnProcess event works as expected. I can set correct roles for user.

Second change does not works. When I'm trying to login into the Oracle server using account which has status expired then regular exception ORA-28001 with message: the password has expired@' extended_errorcode = 1 is raised and logging process is interrupted. OnStatementInfo event is never fired. When I'm trying to login to the Oracle using this same account in other tools then they wants to get new password for account.

Is it possible to add to the OnStatementInfo event additional parameter with error code? Testing code value will be easier to get information about event calling reason than parse message text.

#29 mORMot 1 » Unexpected TInterfaceStub behaviour » 2015-07-18 15:19:18

ASiwon
Replies: 1

Hello,

today I have upgraded mormot from version from 29th April to the newest. After change I found unexpected change in creation of stubs for interfaces with RawByteString properties. Earlier stub was prepared using following code:

  cPassword: RawUTF8 = 'hasloPlain';
  cPasswordEncoded: RawByteString = 'hasloEncoded';

  TInterfaceStub.Create(TypeInfo(IGxPasswordCoder), coder).
    Returns('Encode', [cPassword], [cPasswordEncoded]);

and stub works correctly. After upgrading, stub after calling Encode function returns wrong value. Only when preparation code is changed to:

  TInterfaceStub.Create(TypeInfo(IGxPasswordCoder), coder).
    Returns('Encode', [cPassword], [BinToBase64(cPasswordEncoded)]);

then everything works correctly. I just want to know: Is it intentional change in mORMot and must I change all my stubs definitions?

#30 Re: mORMot 1 » Overflow exception when calling SetMaxLengthFilterForTextFields » 2015-05-08 21:07:33

ASiwon
ab wrote:

So you would have to change your "forked" version to support overflow checking.
Your own code is to be modified, not mORMot's, I suppose.

But there is no problem in mORMot's code, which is always compiled without overflow checks, therefore not to be changed.

Yes.

ab wrote:

Compiler generated overflow checking slow down the process a lot, this is why we rely on manual or algorithm-level checks, and sometimes the overflows are expected to happen (e.g. when we cast an integer into a cardinal, so that a single comparison is able to check for both >=0 and <count conditions).

I know that overflow slows down the process. I'm using it only when I'm compiling application in debug mode. Thank you very much for assistance.

#31 Re: mORMot 1 » Overflow exception when calling SetMaxLengthFilterForTextFields » 2015-05-08 06:40:51

ASiwon

Hello,

I have not changed original source code of mORMot. But I made copy of this procedure to my own code. Its because I have my own version of truncate filter. My version of filter saves to the log file informations about truncation. Thats why in my case exception occurs. When Overflow checking is disabled then for original mORMot code problem not exists. smile

#32 mORMot 1 » Overflow exception when calling SetMaxLengthFilterForTextFields » 2015-05-07 19:29:09

ASiwon
Replies: 4

Hello,

I found small problem. In some cases when I call SetMaxLengthFilterForTextFields procedure then EOverflow exception is raised. Problem exist for the properties which does not have index parameter defined. For example:

  TTest1Item = class(TSQLRecord)
  private
    FTestProp: TDateTime;
  published
    property TestProp: TDateTime read FTestProp write FTestProp;
  end;

  TTest2Item = class(TSQLRecord)
  private
    FTestProp: RawUTF8;
  published
    property TestProp: RawUTF8 read FTestProp write FTestProp;
  end;

  TTest3Item = class(TSQLRecord)
  private
    FTestProp: string;
  published
    property TestProp: string read FTestProp write FTestProp;
  end;

and of course when overflow control is on in compiler. This is not a problem with production code but in testing code it makes some troubles. I have change condition:

if (SQLFieldType in TEXT_FIELDS) and (cardinal(FieldWidth-1)<262144) then

to:

if (SQLFieldType in TEXT_FIELDS) and (cardinal(FieldWidth)<262144) and (FieldWidth <> 0) then

After this correction everything works correctly.

#33 Re: mORMot 1 » Some logging related questions » 2015-04-30 16:08:48

ASiwon
ab wrote:

I've fixed the TTextWriter.EchoAdd() issue with http://synopse.info/fossil/info/52573ec04a
Thanks for the feedback.

Thank you very much. Now it works as expected. :-)

#34 Re: mORMot 1 » Some logging related questions » 2015-04-29 09:34:09

ASiwon
ab wrote:

IMHO this is not possible, due to how the methods are defined.
Just try it: the compiler complains about 'array of const' parameters.

But we may mock the Writer class, or something in-between ISynLog methods and the Writer instance.

Thank you for your answer. This is better idea than creating mock or stub possibility for ISynLog interface! This allow me to test log messages even if ISynLog instance is created inside of the procedure (using TSynLog.Enter method)

For now I'm trying to use EchoCustom event to test messages which are saved in log. I have some problem with this. I wrote small example to explain the problem:

function TForm1.MemoLog(Sender: TTextWriter; Level: TSynLogInfo; const Text: RawUTF8): boolean;
begin
  Memo1.Lines.Add('MemoLog call: ' + IntToStr(Integer(Level)));
  Memo1.Lines.Add(Text);
  Result := True;
end;

procedure TForm1.btn1Click(Sender: TObject);
var
  log: ISynLog;
begin
  log := TSynLog.Add;
  log.Instance.Family.EchoCustom := MemoLog;
  if log.Instance.Writer <> nil then
    log.Instance.Writer.EchoAdd(MemoLog);
  log.Instance.Family.Level := LOG_VERBOSE;
  log.Log(sllWarning, 'Some warning');
  log.Instance.Writer.EchoRemove(MemoLog);
  log := nil;

  log := TSynLog.Add;
  log.Instance.Family.EchoCustom := MemoLog;
  log.Instance.Writer.EchoAdd(MemoLog);
  log.Instance.Family.Level := LOG_VERBOSE - [sllWarning];
  log.Log(sllWarning, 'Some warning');
  log.Instance.Writer.EchoRemove(MemoLog);
  log := nil;

  raise EORMException.Create('Error Message');
end;

This shows how from view of logging mechanism works my test procedure. I hope this is correct code which should works correctly. If I call btn1Click once then everything is Ok. In memo are added lines:

MemoLog call: 4
20150429 11190807 warn  Some warning

But when I call btn1Click second and next times then in memo are added lines:

MemoLog call: 4

in Text param of function MemoLog is empty string. After some times of debugging I have changed procedure TTextWriter.EchoAdd to: 

procedure TTextWriter.EchoAdd(const aEcho: TOnTextWriterEcho);
begin
  if self<>nil then
    if MultiEventAdd(fEchos,TMethod(aEcho)) then
      if fEchos<>nil then
        fEchoStart := B-fTempBuf+1; // ignore any previous buffer
end;

in original source condition is: if fEchos=nil then. After this correction proper warning message is added to memo every time I call procedure: btn1Click. Could you help me and check where exactly exists a problem? I'm using DXE4 and mORMot from 27 April 2015 18:57:25

And there are overloaded methods, so mocking/stubbing may be impossible (at least with mORMot.pas, which expect unique method names AFAIR).

Is it any chance to change it? Sometimes I'm writing unit test for class which use interfaces with overloaded procedures. Possibility of creating stub or mock for such interfaces would be very usefull.

#35 mORMot 1 » Some logging related questions » 2015-04-28 14:18:29

ASiwon
Replies: 4

Hello,

I have some problem with using TSynLog.Family.EchoCustom event. For following simple code:

var
  log: ISynLog;
  echoObject: TSomeObjectWichSupportsEvent;


log := TSynLog.Add;
log.Instance.Family.EchoCustom := echoObject.Write;
log.Log(sllInfo, 'Info logging');
log.Instance.Family.EchoCustom := nil;
echoObject.Free;

After executing this sequence all log calls trying to call event in freed object instance. It is because fWriter is still storing and using for event reference to freed objects. If I call

log.Instance.Writer.EchoRemove(echoObject.Write);

then everything works correctly. Is it correct way or just calling log.Instance.Family.EchoCustom := nil; should be enough? I have similar problem while adding event. Before first log operation EchoCustom should be assigned to the Familly.EchoCustom event and after first log operation it should be added to the writer using Writer.EchoAdd method. Is it possible to add one way to set and remove EchoCustom event?

I have one question yet. Is it possible to change ISynLog base interface to IInvokable? In some cases I want to check in unit tests my code is logging properly. With mocking possibility it would be very easy.

#36 Re: mORMot 1 » Help for stubbing needed » 2015-04-16 20:22:16

ASiwon

I have changed code to the following kind:

TInterfaceStub.Create(IBkSchenckWazenie1Rozpoznanie, FWazenieRozpoznanie).
      Returns('Rozpoznaj', [wazenie], [cArticleWeight, False, False]);

and now is working as expected. :-)

#37 mORMot 1 » Help for stubbing needed » 2015-04-16 10:56:52

ASiwon
Replies: 1

Hello,

I have problem with create stub for following code:

  IBkSchenckWazenie1Rozpoznanie = interface(IInvokable)
    ['{F6D047F9-9B3E-4C24-BE1D-5010979318E1}']
    procedure Rozpoznaj(const AWazenie: TSchenckWMWazenie1; out ARodzaj: Integer; out APotwierdz, APosrednie: Boolean);
  end;

  TSchenckWMWazenie1 = class(TSQLRecord)
  private
    FSklad: String;
  published
    property Sklad: String index 30 read FSklad write FSklad;
       // Other properties ...
    ...
  end;

procedure Test_Something;
var
  wazenie: TSchenckWMWazenie1;
  FWazenieRozpoznanie: IBkSchenckWazenie1Rozpoznanie;
  rodzaj: Integer;
  potwierdz, posrednie: Boolean;
begin
  wazenie := TSchenckWMWazenie1.Create;
  try
    wazenie.Sklad := cSklad;
      // Some other assignment...
    TInterfaceStub.Create(IBkSchenckWazenie1Rozpoznanie, FWazenieRozpoznanie);
      Returns('Rozpoznaj', [ObjectToJSON(wazenie, []), rodzaj, potwierdz, posrednie], [cArticleWeight, False, False]);
    FWazenieRozpoznanie.Rozpoznaj(wazenie, rodzaj, potwierdz, posrednie);
      // Here value in rodzaj is always 0
    ...
  finally
    wazenie.Free;
  end;
end;

Value returned in parameter rodzaj is always 0, but cArticleWeight has value 1.

I have tried to create stub in this way:

    TInterfaceStub.Create(IBkSchenckWazenie1Rozpoznanie, FWazenieRozpoznanie);
      Returns('Rozpoznaj', [ObjectToJSON(wazenie, [])], [cArticleWeight, False, False]);

But nothing is changed. What I'm doing wrong?

#38 Re: mORMot 1 » How to handle EInterfaceFactoryException properly » 2015-04-12 22:09:58

ASiwon

Maybe I'm wrong (my english is not good) but I think in documentation to the mORMotDDD unit code, for class TDDDRepositoryRestFactory, for method AddFilterOrValidate exists incomplete sentence:

- filters and validators will be applied to the

#39 Re: mORMot 1 » How to handle EInterfaceFactoryException properly » 2015-04-12 15:07:18

ASiwon

Thank you very much for answer.

Exceptions are not value objects, but true class instances, with some methods, and a specific behavior within the Delphi language.
A Delphi exception is something very specific, and would not be easily converted into e.g. a JavaScript, Java or C# exception.
As such, they are not good candidate on serialization, and transmission per value, from the server side to the client side.
So we would NOT be in favor of propagating exceptions to the client side.
And just one exception message (i.e. a string) may be not versatile enough.
Sometimes, you need additional structured information.

Of course I need additional structured information. But for now when I'm calling methods of remote service all I have is Message property in EInterfaceFactoryException...

See how ICQRSQuery is defined in mORMotDDD.pas.
I've just updated the documentation, and written a blog article about this point.

Any example of using TDDD* classes would be also very valuable and helpfull.

#40 Re: mORMot 1 » How to handle EInterfaceFactoryException properly » 2015-04-12 13:00:12

ASiwon

Ok - I will use try except in interface implementation because propagate Oracle's or any other database exception it is a bad idea. Even if I will change Oracle exception to my own exception it will be still problem with reading exception details on client side. But mORMot's client-server exception handling is great and I still want to use it.

Now exception stucture is serialized to the JSON format but after that some prefixes are added for example ErrorCode or Error information. Now it is impossible to directly parse exception message and get detailed information about occured exception. Now I'm removing some parts of message and then I'm deserializing structure to exception on client side. It works but it is hand made method.

I think it would be better if exception message could be in pure JSON format. Then it would be much more simpler to deserialize detailed information about exception end use it on client side. What do you think about it?

#41 Re: mORMot 1 » Access Violation while creating client instance » 2015-04-12 07:58:00

ASiwon

I don't know why FTableMax has value 0. Probably after initialization. I just see if I execute following code:

  model := TSQLModel.Create;
  client := TSQLRestClientDB.Create(model, nil, SQLITE_MEMORY_DATABASE_NAME, TSQLRestServerDB, False);

it raises AV exception. Here is call stack

mORMot.TSQLModel.Create($13F6C78)
mORMotSQLite3.TSQLRestClientDB.Create($13F6C78,nil,$13FDDE0,TSQLRestServerDB,False)
mORMotSQLite3.TSQLRestClientDB.Create($13F6C78,nil,':memory:',TSQLRestServerDB,False,'')

I hope this helps.

#42 Re: mORMot 1 » Access Violation while creating client instance » 2015-04-12 06:56:04

ASiwon

Use TSQLRestServer.CreateWithOwnModel() model if you want a "minimal" model.

Thank you very much for information. I will try this.

I've enhanced TSQLRestServer.Create to intercept aModel=nil parameter as an explicit exception.
See http://synopse.info/fossil/info/91959008ae

As you can see in attached code AModel parameter has not nil value. The Access Violation exception is raised in the following lines of the TSQLModel constructor:

constructor TSQLModel.Create(CloneFrom: TSQLModel);
var i: integer;
begin
  if CloneFrom=nil then
    raise EModelException.CreateUTF8('%.Create(CloneFrom=nil)',[self]);
  fTables := CloneFrom.fTables;
  fTablesMax := CloneFrom.fTablesMax;
  fRoot := CloneFrom.fRoot;
  fActions := CloneFrom.fActions;
  fEvents := CloneFrom.fEvents;
  fRestOwner := CloneFrom.fRestOwner;
  fSortedTablesName := CloneFrom.fSortedTablesName;
  fSortedTablesNameIndex := CloneFrom.fSortedTablesNameIndex;
  fRecordReferences := CloneFrom.fRecordReferences;
  fVirtualTableModule := CloneFrom.fVirtualTableModule;
  fCustomCollationForAllRawUTF8 := CloneFrom.fCustomCollationForAllRawUTF8;
  SetLength(fTableProps,fTablesMax+1);
  for i := 0 to fTablesMax do
    fTableProps[i] := TSQLModelRecordProperties.CreateFrom(
      self,CloneFrom.fTableProps[i]);
end;

TSQLModel is called from TSQLRestClientDB.Create constructor when AServerModel parameters has value nil. In the last instruction (for loop) is raised exception. Its because fTableMax variable has value 0 and for loop is executed one iteration. In this iteration CloneFrom.fTableProps[index] tries to get element of array which not exists. ANd here is AV raised.

#43 mORMot 1 » Access Violation while creating client instance » 2015-04-11 13:25:08

ASiwon
Replies: 5

Hello,

I have AV exception on the following code:

  FModel := TSQLModel.Create;
  FClient := TSQLRestClientDB.Create(FModel, nil, SQLITE_MEMORY_DATABASE_NAME, TSQLRestServerDB, False);

I know - this is stupid code because empty model is unusable, but I just need a client instance to create some unit tests. Even if model cannot be empty then it would be nice to get more comprehensible message than: Access Violation.

#44 Re: mORMot 1 » Problem with TInterfaceMock » 2015-04-10 20:47:33

ASiwon

And you need to pass the same exception instance class as defined.

I had problem with registering custom serializer. Only read method was defined. Thats why it doesn't worked correctly while creating mock.

In my case only for testing purposes it would be enough to use simple pointer values for such parameters - without defining custom serializer. I know that for service using pointers have no sense but for testing it really would be enough. Is it possible to add this behaviour?

#45 mORMot 1 » Problem with TInterfaceMock » 2015-04-10 18:58:30

ASiwon
Replies: 2

Hello,

I'm trying to use TInterfaceMock to create unit tests. I'm trying to create mock for interface:

  IExceptionHandler = interface(IInvokable)
    ['{E869A018-624E-429D-8245-428FE9620FCE}']
    function Handle(const AException: Exception): Boolean;
  end;

But if I'm trying to create mock with code:

  localExc := Exception.Create('');
  TInterfaceMock.Create(TypeInfo(IGxExceptionHandler), mckHandler).
      ExpectsCount('Handle', [localExc], qoEqualTo, 1);

it raises exception with message: TInterfaceFactoryRTTI.Create: IGxExceptionHandler.Handle "AException" parameter has unexpected type Exception - use TJSONSerializer.RegisterCustomSerializer() It is raised even if reader for Exception class is registered. Is it possible to use mORMot mocks to create mocks for interfaces like my?

#46 mORMot 1 » How to handle EInterfaceFactoryException properly » 2015-04-09 10:06:08

ASiwon
Replies: 5

Hello,

from time to time during remote function call I have received an exception:

TInterfacedObjectFakeClient.FakeCall(IBkWazeniaImportyPodstaw.Podstaw) failed: '{
"errorCode":500,
"error":
{"ESQLDBOracle":{
    "ClassName":"ESQLDBOracle",
    "Address":"7FA52510",
    "Statement": {
        "ClassName":"TFreedObject",
        "Address":"7FADBE10"
    },
    "Message": "TSQLDBOracleStatement error: ORA-00001: unique constraint (U_EKS.I_WP_NR_WAGONU) violated\nORA-06512: at \"U_EKS.P_WAGONY_OBECNE_DODAJ\", line 585\nORA-06512: at \"U_EKS.P_WAGONY_OBECNE_DODAJ\", line 322\nORA-06512: at \"U_EKS.SP_WAGONY_OBECNE_INSERT\", line 139\nORA-06512: at line 2"
}}
}'

And thats right. It is database index violation and it could sometimes happens. But how to handle this exception properly? I don't want to show whole exception message to the end user. Is it possible to easilly parse exception message in any way and get detailed information about raised exception? Or maybe this exception should be handled on server side and propagated in other way than exception EInterfaceFactoryException?

#47 Re: mORMot 1 » Problem with opening compressed log files. » 2015-03-26 14:58:39

ASiwon

I have download the current version from github. If I will find any problem then I will let you know. Thank you very much for great support. As always.

#48 Re: mORMot 1 » Problem with opening compressed log files. » 2015-03-26 09:40:39

ASiwon

Wrong values it is my fault. I have problems with two files. Size from header was from second file and size from stream was from first file. Thats why values was different. So sizes are equals but still are differences on hash values.
You can download compressed files from:
http://gidex.com.pl/download/BkSerwerService.1.synlz
http://gidex.com.pl/download/BkSerwerService.2.synlz

#49 Re: mORMot 1 » Problem with opening compressed log files. » 2015-03-26 09:05:45

ASiwon

For testing purposes I have changed end of function StreamUnSynLZ to:

  if (SynLZdecompress1(P,Head.CompressedSize,result.Memory)<>Head.UnCompressedSize) or
     (Hash32(result.Memory,Head.UnCompressedSize)<>Head.HashUncompressed) then
  begin
    Result.Seek(0, soFromBeginning);
    Result.SaveToFile('Test.log');
    FreeAndNil(result);
  end;

The file after saving has: 102438644 bytes and Head.UnCompressedSize has value: 102404552. Saved file is readable and I don't see any damaged data inside.

#50 mORMot 1 » Problem with opening compressed log files. » 2015-03-25 21:45:46

ASiwon
Replies: 9

Hello,

I have tried to read log records from compressed files using LogView tool. But files are not opened correctly. In LogView just nothing happens after selecting synlz log file - empty list is shown. I have checked where is the problem in source code and I found that in function: StreamUnSynLZ hash value is different in uncompressed data and header:

  if (SynLZdecompress1(P,Head.CompressedSize,result.Memory)<>Head.UnCompressedSize) or
     (Hash32(result.Memory,Head.UnCompressedSize)<>Head.HashUncompressed) then
    FreeAndNil(result);

For now I have only two synlz files but for both it is impossible to read their content. Those two files are not important to me but in the future this may make me some problems. Is it possible to check where exist a problem?

Pages: 1 2

Board footer

Powered by FluxBB