Posts by zed

zed · mORMot 1

  /// low-level libcurl library file name, depending on the running OS
  LIBCURL_DLL = {$ifdef Darwin} 'libcurl.dylib' {$else}
    {$ifdef Linux} 'libcurl.so' {$else} 'libcurl-x64.dll' {$endif}{$endif};

What about Win32 platform? Is it no longer supported?

zed · mORMot 1

Thank you.

zed · mORMot 1

This test case fails on version 1.18.5015:

uses
  mORMot,
  SynCommons;

procedure Test_ArraySorted;
var
  VCount: Integer;
  VArr, VIntArr: TIntegerDynArray;
  VDynArr: TDynArray;
begin
  VDynArr.InitSpecific(TypeInfo(TIntegerDynArray), VIntArr, djInteger, @VCount);

  SetLength(VArr, 1);
  VArr[0] := 1;

  VDynArr.AddArray(VArr);
  VDynArr.Sort;

  Assert(VDynArr.Sorted, 'TDynArray.Sort failed!');
end;

Possible fix (SynCommons.pas):

procedure TDynArray.Sort(aCompare: TDynArraySortCompare);
begin
  if Count <= 1 then
    fSorted := true // array with 0 or 1 element is always sorted
  else
    SortRange(0,Count-1,aCompare);  
end;

zed · mORMot 1

Some example may be found here: Delphi System.Zip patch with ZIP64 and LZMA supports

zed · mORMot 1

I make some tests and here is 4 Gb file size limitation, not 2. When mORMot needed to write more then 4 Gb data to output file, LongWord type overflow occurs and file truncated to zero.

It would be better if there was an exception with message that Zip file cannot be more than 4 Gb and Zip64 format is not supported.

zed · mORMot 1

Ah, I think it's because resulting file size is > 2Gb. In this case Zip64 format should be used (instead simple Zip format) but mORMot doesn't support it.

zed · mORMot 1

What type of errors shows 7zip?

What shows unzip with -t switch?

zed · mORMot 1

Calculation EngineLastID (when EngineAddCompute = eacMaxIDEachTime) stopped work in mORMot with MongoDB 3.6 because now mongo require cursor in aggregate requests:

Changed in version 3.6: MongoDB 3.6 removes the use of aggregate command without the cursor option unless the command includes the explain option. Unless you include the explain option, you must specify the cursor option.
To indicate a cursor with the default batch size, specify cursor: {}.
To indicate a cursor with a non-default batch size, use cursor: { batchSize: <num> }.

https://docs.mongodb.com/manual/referen … aggregate/

My pull request with fix: https://github.com/synopse/mORMot/pull/120

zed · mORMot 1

Did you test it with 500 or 5000 clients?

I'm not sure exactly, but I think that mORMot can use some Thread poll and the dependence of memory usage on the number of clients will not be linear.

zed · mORMot 1

Each Thread use 1 Mb of RAM to its stack, so if mORMot use one Thread per client there is no surprise.
https://msdn.microsoft.com/en-us/librar … s.85).aspx

zed · mORMot 1

Ok, now it's work. Thank you!

zed · mORMot 1

Yes, it helps.

But here is a new fail:

Assert(not IsMatch('ab[cd]e', 'abdde', False)); // ok
Assert(not IsMatch('ab[cd]ex', 'abddex', False)); // ok

Assert(not IsMatch('ab*.[cd]e', 'ab.dde', False)); // ok
Assert(not IsMatch('ab*.[cd]ex', 'ab.ddex', False)); // fail

zed · mORMot 1

Test code:

IsMatch('ab*.[ef]xyz', 'ab.exyz', True);
IsMatch('ab*.[ef]xyz', 'abcd.exyz', True);
IsMatch('ab*.[ef]xyz', 'ab.fxyz', True);
IsMatch('ab*.[ef]xyz', 'abcd.fxyz', True);

all failed!

mORMot version: 1.18.4332.
Compiler: Delphi 2007.

zed · mORMot 1

ertank
Bug in this expression in your code: string(KeyPC)

To get Key as a string, use this code:

function GetKey: string;
const
  KeyPC: array of Char = [
    #00,  #101, #02,  #03,  #40,  #05,  #106, #07,
    #08,  #09,  #07,  #100, #110, #201, #117, #45,
    #10,  #11,  #12,  #13,  #140, #150, #160, #170,
    #13,  #1,   #17,  #130, #40,  #16,  #106, #70,
    #180, #190, #174, #200, #0,   #1,   #11,  #15,
    #80,  #91,  #72,  #130, #101, #111, #107, #45
  ];
var
  I: Integer;
begin
  SetLength(Result, Length(KeyPC));
  for I := 0 to Length(KeyPC) - 1 do begin
    Result[I+1] := KeyPC[I];
  end;
end;

zed · mORMot 1

Fix:

function FastFindInt64Sorted(P: PInt64Array; R: PtrInt; const Value: Int64): PtrInt; overload;
var L: PtrInt;
    cmp: Integer;
begin
  L := 0;
  if 0<=R then
  repeat
    result := (L + R) shr 1;
    {$ifdef CPUX86} // circumvent Int64 comparison slowness
    cmp := SortDynArrayInt64(P^[result],Value);
    if cmp=0 then
      exit else
    if cmp<0 then
    {$else}
    if P^[result]=Value then
      exit else
    if P^[result]<Value then
    {$endif}
      L := result + 1 else
      R := result - 1;
  until (L > R);
  result := -1
end;

zed · mORMot 1

Test code:

uses
  SysUtils,
  mORMot,
  SynCommons;

procedure DoTest;
var
  I, J: Integer;
  VLen: Integer;
  VArr: TIDDynArray;
begin
  VLen := 3;
  SetLength(VArr, VLen);
  for I := 0 to VLen - 1 do begin
    VArr[I] := I;
  end;
 J := FastFindInt64Sorted(PInt64Array(VArr), VLen-1, 2); // <-- hangs
end;

Previous worked version FastFindInt64Sorted:

function FastFindInt64Sorted(P: PInt64Array; R: PtrInt; const Value: Int64): PtrInt; overload;
var L: PtrInt;
    cmp: Int64;
begin
  L := 0;
  if 0<=R then
  repeat
    result := (L + R) shr 1;
    cmp := P^[result]-Value;
    if cmp=0 then
      exit;
    if cmp<0 then
      L := result + 1 else
      R := result - 1;
  until (L > R);
  result := -1
end;

Current, bugged version (infinite loop):

function FastFindInt64Sorted(P: PInt64Array; R: PtrInt; const Value: Int64): PtrInt; overload;
var L: PtrInt;
begin
  L := 0;
  if 0<=R then
  repeat
    result := (L + R) shr 1;
    {$ifdef CPUX86} // circumvent Int64 comparison slowness
    result := SortDynArrayInt64(P^[result],Value);
    if result=0 then
      exit else
    if result <0 then
    {$else}
    if P^[result]=Value then
      exit else
    if P^[result]<Value then
    {$endif}
      L := result + 1 else
      R := result - 1;
  until (L > R);
  result := -1
end;

I use Delphi 10.2 and x86 build for test.

zed · mORMot 1

mpv wrote:

EMartin wrote:
Just for curiosity, why not just to use a function for readonly access in https://synopse.info/fossil/info/1ff1e5b5c276dcb5 ?
Indeed: see [6b4cc8c]. Thanks!

And function with 1 line of code should be marked as inline if it's possible.

zed · mORMot 1

Option WINHTTP_ACCESS_TYPE_AUTOMATIC_PROXY replases WINHTTP_ACCESS_TYPE_DEFAULT_PROXY, but not WINHTTP_ACCESS_TYPE_NO_PROXY!

So, if you want to change behavor when fProxyName is empty, you should use WINHTTP_ACCESS_TYPE_DEFAULT_PROXY for windows version < 8.1:

if (OSVersionInfo.dwMajorVersion>=6) and (OSVersionInfo.dwMinorVersion>=3) then
  OpenType := WINHTTP_ACCESS_TYPE_AUTOMATIC_PROXY else // Windows 8.1 and newer
    OpenType := WINHTTP_ACCESS_TYPE_DEFAULT_PROXY else // Windows 8 and older
OpenType := WINHTTP_ACCESS_TYPE_NAMED_PROXY;

zed · mORMot 1

Use this option:

TSynLog.Family.HighResolutionTimeStamp := False;

zed · mORMot 1

Yes, this commit fix my issue with MongoDB. Thanks!

zed · mORMot 1

Oh, I found this breaking change commit: https://github.com/synopse/mORMot/commi … 3aec5db617 is it really so necessary to make this change? Have you some bugs without it? Because with it we definitely have a bugs. I hope you will revert it and set param AllowVarDouble to True by default.

And I think that is not obvious behavior, processing numbers with 4 digits after dot as a doubles, but with 5 digits as a strings. By default, behavior must be consistent.

zed · mORMot 1

I propose return old behavior (process double values by default) and revert option dvoAllowDoubleValue to dvoDontAllowDoubleValue and use it in all places where you need strings.

zed · mORMot 1

ab
You change old behavior and this broke all old code. Include your own code in TSQLRestStorageMongoDB.DocFromJSON(). You tell me to use dvoAllowDoubleValue option, but this doesn't work, I tried!

My main problem is that now I can't insert double values into MongoDB, because your code in TSQLRestStorageMongoDB.DocFromJSON() is broken and have a bug. Please, fix it.

zed · mORMot 1

Test, to reproduce a bug:

uses
  mORMot,
  SynCommons,
  SysUtils;

procedure DoTest;
const
  // some random geographic coordinates
  VJSON_In: array [0..1] of RawUTF8 = (
    '{"ArrayOfDouble":[45.2,55.9]}',
    '{"ArrayOfDouble":[45.264823,55.914998]}'
  );
var
  I: Integer;
  VDoc: TDocVariantData;
  VJSON_Out: RawUTF8;
begin
  for I := 0 to Length(VJSON_In) - 1 do begin
    VDoc.InitJSON(VJSON_In[I],[dvoValueCopiedByReference]);
    VJSON_Out := VDoc.ToJSON();
    if VJSON_Out <> VJSON_In[I] then begin
      raise Exception.CreateFmt('Test %d failed!', [I + 1]);
    end;
  end;
end;

First test is Ok, second - Failed, because double values became a strings:

'{"ArrayOfDouble":["45.264823","55.914998"]}'

TSQLRestStorageMongoDB.DocFromJSON() uses method doc.InitJSON() and it corrupt MongoDB json request, so Doubles inserts to DB as Strings.

It seems, that this bug exists at least 3 month, but discovered only now.

zed · Fast JPEG decoder

@MarkTG
Did you know about libjpeg-turbo? I think, that this is the fastest library to work with jpeg.

You can find Delphi wrapper and helper classes (feel free to optimize it!) here: https://bitbucket.org/sas_team/sas.plan … s/LibJpeg/

It would be great to see performance statistics for it

zed · mORMot 1

edwinsn wrote:

Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

Why?

zed · mORMot 1

RObyDP
I think, if you compile snappy as *.dll with MS Visual Studio and use it from Delphi, you may found that performance is better.

I have some experience with CityHash lib from Google. I compile it as *.obj with bcc32 and as *.dll with MSVS and compare speed for both versions. As you can guess, dll version was faster. I also test pure Pascal version, but it was even slower than *.obj version.

zed · mORMot 1

I ran into next problem related to Blob parameters - whenever I try to store a picture into varbinary(max) field ( using BindBlob - see code above ), in MSSQL database is stored only first 8 bytes.

This is old bug: http://synopse.info/forum/viewtopic.php?id=3009

zed · mORMot 1

MM wrote:

I just bumped into it and found that changing line 1929 in SynOLEDB (latest nightly build) to
 B^.cbMaxLen := length(P^.VBlob); //sizeof(Pointer); 
seems to solve the truncation problem.

Can somebody make a pull-request with this fix?

zed · mORMot 1

I know about ZeosLib and this is my last hope. I will try it later, thanks for the advice.

zed · mORMot 1

Yes, I install and trying all versions of Native Client with this classes. The result is the same - data is truncated.

zed · mORMot 1

>Did you try more tuned class than TOleDBMSSQLConnectionProperties ?
What do you mean? Which class I should try to use?

>Which compiler version are you using?
Tested on Delphi 2007 and XE2.

> try to use StringFromFile() and FileFromString()
> Take a look also at VirtualTableExternalRegisterAll
Yes, I know about this functions, but in test I don't use them to make test code more closer to my real application.
In my code it's looks like:

...
  for I := 0 to High(FModel.Tables) do begin
    VTable := FModel.Tables[i];
    if VTable.InheritsFrom(TSQLMark) then begin
      VTableName := 'Mark'
    end else begin
      VTableName := '';
    end;
    
    if FDBMSConnection.Properties.DBMS = dMSSQL then begin
      if VTable.SQLTableName = 'User' then begin
        VTableName := 'UserInfo';
      end;
    end;

    if not VirtualTableExternalRegister(FModel, VTable, FDBMSConnection.Properties, VTableName) then begin
      raise EMarkSystemORMError.Create('VirtualTableExternalRegister failed');
    end;
  end;
....
function TMarkDbImplORMHelper._GeomertryFromBlob(
  const ABlob: TSQLRawBlob
): IGeometryLonLat;
var
  VStream: TRawByteStringStream;
begin
  Assert(ABlob <> '');
  VStream := TRawByteStringStream.Create(ABlob);
  try
    Result := FGeometryReader.Parse(VStream);
  finally
    VStream.Free;
  end;
end;

function TMarkDbImplORMHelper._GeomertryToBlob(
  const AGeometry: IGeometryLonLat
): TSQLRawBlob;
var
  VStream: TRawByteStringStream;
begin
  Assert(AGeometry <> nil);
  VStream := TRawByteStringStream.Create;
  try
    FGeometryWriter.Save(AGeometry, VStream);
    Result := VStream.DataString;
  finally
    VStream.Free;
  end;
end;

zed · mORMot 1

If I'm manually change fields definition to nvarchar(2000), then all works fine:

CREATE TABLE "TestRec" (
	"ID" BIGINT NOT NULL,
	"CustomID" BIGINT NULL DEFAULT NULL,
	"TestField1" NVARCHAR(2000) NULL DEFAULT NULL,
	"TestField2" NVARCHAR(2000) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

So, I think that here is a problem with nvarchar(max) type in SynDBODBC...

zed · mORMot 1

Because of errors with ODBC driver and MS SQL, I'm trying to use an OleDB driver (TOleDBMSSQLConnectionProperties). It's save and retrieve string fields without any problems, but it's not working with blobs for me

I have no any errors in log during save/read blobs from DB, but I got a truncated data.

I try to save a data with size of 21 byte, but after RetrieveBlob I got only 4 bytes of data:

uses
  SysUtils,
  Classes,
  mORMot,
  mORMotSQLite3,
  mORMotDB,
  SynDB,
  SynOleDB,
  SynDBODBC,
  SynSQLite3Static,
  SynCommons;

type
  TSQLBlobTest = class(TSQLRecord)
  public
    FSize: Int64;
    FData: TSQLRawBlob;
  published
    property xSize: Int64 read FSize write FSize;
    property xData: TSQLRawBlob read FData write FData;
  end;

const
  cTestFileName = '..\test_data\mGeoWKB';
  cOutFileName = '..\test_data\mGeoWKB_out';

procedure FillTestRec(const ARec: TSQLBlobTest);
var
  VStream: TRawByteStringStream;
  VMemStream: TMemoryStream;
begin
  VMemStream := TMemoryStream.Create;
  VStream := TRawByteStringStream.Create;
  try
    VMemStream.LoadFromFile(cTestFileName);
    VMemStream.Position := 0;

    VStream.CopyFrom(VMemStream, VMemStream.Size);

    ARec.FSize := VStream.Size;;

    ARec.FData := VStream.DataString;
  finally
    VStream.Free;
    VMemStream.Free;
  end;
end;

procedure CheckTestRec(const ARec: TSQLBlobTest; const ABlob: TSQLRawBlob);
var  
  VNewSize: Int64;
  VStream: TRawByteStringStream;
  VMemStream: TMemoryStream;
begin
  VMemStream := TMemoryStream.Create;
  VStream := TRawByteStringStream.Create(ABlob);
  try
    VStream.Position := 0;

    VMemStream.LoadFromStream(VStream);
    VMemStream.Position := 0;

    VNewSize := VMemStream.Size;
    
    VMemStream.SaveToFile(cOutFileName);

    Assert(ARec.FSize = VNewSize);    
  finally
    VStream.Free;
    VMemStream.Free;
  end;
end;

procedure DoTest;
const
  cDSN = 'TEST_MSSQL';
var
  I: Integer;
  VId: TID;
  VBool: Boolean;
  FModel: TSQLModel;
  FClientDB: TSQLRestClientDB;
  FDBMSProps: TSQLDBConnectionProperties;
  VRec: TSQLBlobTest;
  VBlob: TSQLRawBlob;
begin
  FModel := TSQLModel.Create([TSQLBlobTest]);

  //FDBMSProps := TODBCConnectionProperties.Create(cDSN, '', '', '');
  FDBMSProps := TOleDBMSSQLConnectionProperties.Create('HOME-PC\SQLEXPRESS', 'testdb', '', '');

  for I := 0 to High(FModel.Tables) do begin
    if not VirtualTableExternalRegister(FModel, FModel.Tables[i], FDBMSProps, '') then begin
      raise Exception.Create('VirtualTableExternalRegister failed');
    end;
  end;

  FClientDB := TSQLRestClientDB.Create(FModel, nil, ':memory:', TSQLRestServerDB);
  FClientDB.Server.CreateMissingTables;

  VRec := TSQLBlobTest.Create;
  try
    FillTestRec(VRec);

    FClientDB.TransactionBegin(TSQLBlobTest, 1000);
    try
      VId := FClientDB.Add(VRec, True);
      Assert(VId > 0);

      VBool := FClientDB.UpdateBlob(TSQLBlobTest, VId, 'xData', VRec.FData);
      Assert(VBool);

      FClientDB.Commit(1000, True);
    except
      FClientDB.RollBack(1000);
      raise;
    end;

    VBlob := '';
    VBool := FClientDB.RetrieveBlob(TSQLBlobTest, VId, 'xData', VBlob);
    Assert(VBool);

    CheckTestRec(VRec, VBlob);
  finally
    VRec.Free;
  end;
end;

My test data (mGeoWKB content as byte array):

  data: array[0..20] of byte = (
	$01, $01, $00, $00, $00, $8D, $0A, $9C, $6C, $03, $5D, $44, $40, $A7, $25, $56, 
	$46, $23, $51, $46, $40
  );

The same code works fine with ODBC.

It's terrible: with ODBC strings not working, blobs - ok; with OleDB blobs not working, but strings - ok. Is it my mistake trying to use MS SQL with mORMot???

zed · mORMot 1

Error occurred when I'm trying to read an existing record from DB, not to insert it!
If DB already has records with strings length more than 40 chars, you can make test without transaction and Add operation. It will be failure in any case.

zed · mORMot 1

Yes, I can try it, but, if it's possible, I would like to use TODBCConnectionProperties.

zed · mORMot 1

Text fields is nvarchar(max):

CREATE TABLE "TestRec" (
	"ID" BIGINT NOT NULL,
	"CustomID" BIGINT NULL DEFAULT NULL,
	"TestField1" NVARCHAR(max) NULL DEFAULT NULL,
	"TestField2" NVARCHAR(max) NULL DEFAULT NULL,
	PRIMARY KEY ("ID")
)
;

zed · mORMot 1

I don't know what is wrong, but if I set a string len to 40 I can't retrieve it back from DB.

Test code:

uses
  SysUtils,
  Classes,
  mORMot,
  mORMotSQLite3,
  mORMotDB,
  SynDB,
  SynDBODBC,
  SynSQLite3Static,
  SynCommons;

type
  TSQLTestRec = class(TSQLRecord)
  public
    FCustomID: TID;
    FTestField1: RawUTF8;
    FTestField2: RawUTF8;
  published
    property CustomID: TID read FCustomID write FCustomID;
    property TestField1: RawUTF8 read FTestField1 write FTestField1;
    property TestField2: RawUTF8 read FTestField2 write FTestField2;
  end;

procedure DoTest;
const
  cDNS = 'TEST_MSSQL';
var
  I: Integer;
  VId: TID;
  FModel: TSQLModel;
  FClientDB: TSQLRestClientDB;
  FProps: TSQLDBConnectionProperties;
  VRec: TSQLTestRec;
  VTestStr: string;
begin
  FModel := TSQLModel.Create([TSQLTestRec]);
  try
    FProps := TODBCConnectionProperties.Create(cDNS, '', '', '');
    try

      for I := 0 to High(FModel.Tables) do begin
        if not VirtualTableExternalRegister(FModel, FModel.Tables[i], FProps, '') then begin
          raise Exception.Create('VirtualTableExternalRegister failed');
        end;
      end;

      FClientDB := TSQLRestClientDB.Create(FModel, nil, ':memory:', TSQLRestServerDB);
      try
        FClientDB.Server.AcquireExecutionMode[execORMWrite] := amBackgroundThread;
        FClientDB.Server.AcquireExecutionMode[execORMGet] := amBackgroundThread;

        FClientDB.Server.CreateMissingTables;

        SetLength(VTestStr, 40); // <-- 40 is FAIL, but 30 is OK
        for I := 1 to Length(VTestStr) do begin
          VTestStr[i] := 'z';
        end;

        VRec := TSQLTestRec.Create;
        try
          VRec.CustomID := 2;
          VRec.FTestField1 := StringToUTF8(VTestStr);
          VRec.FTestField2 := StringToUTF8(VTestStr);

          for I := 0 to 1 do begin
            FClientDB.TransactionBegin(TSQLTestRec, 1000);
            try
              VId := FClientDB.Add(VRec, True);
              Assert(VId > 0);
              FClientDB.Commit(1000, True);
            except
              FClientDB.RollBack(1000);
              raise;
            end;

            if FClientDB.Retrieve(VId, VRec) then begin
              if not SameText(UTF8ToString(VRec.FTestField2), VTestStr) then begin
                Assert(False, 'SameText failed');
              end;
            end else begin
              Assert(False, 'Retrieve failed');
            end;
          end;
        finally
          VRec.Free;
        end;

      finally
        FClientDB.Free;
      end;
    finally
      FProps.Free;
    end;
  finally
    FModel.Free;
  end;
end;

Function FClientDB.Retrieve(VId, VRec) returns False, and this is wrong.

I found this error in log:

TODBCStatement(00B28C50).00586274 SynDBODBC.TODBCStatement.ExecutePrepared (1494) 
00000000CA203659  # SQL   	TODBCStatement(00B28C50) select ID,CustomID,TestField1,TestField2 from dbo.TestRec where ID=3
00000000D89BEDBE  #  -    00.081.010
00000000D8A3553A  # info  [01004] [Microsoft][SQL Server Native Client 10.0]String data, right truncation (0)  
00000000D8A3C689  # info  [01004] [Microsoft][SQL Server Native Client 10.0]String data, right truncation (0)
00000000DC5192A8  # EXC   EODBCException .... "TODBCStatement - TODBCLib error: [07009] [Microsoft][SQL Server Native Client 10.0]Invalid Descriptor Index (0)\r\n"...

If I set string len to 30 then all works fine.

WTF?

zed · mORMot 1

When I try to update a BLOB field in MS SQL Server 2008 with data more then 8000 bytes, it raises an error about "Invalid precision value".

In mORMot model field defined as TSQLRawBlob and it translated to varbinary(max) type in MS SQL table.

I found the same problem on StackOverflow: http://stackoverflow.com/questions/1486 … sion-value where recommend set len1 to 0:

SQLLEN len1 = 0;
SQLLEN nThisLen = (SQLLEN)sData.size();
SQLBindParameter( handle, (SQLUSMALLINT)4, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, len1, 0, (SQLCHAR*)&sData.c_str(), nThisLen, &nThisLen );

so, I tried to fix this in mORMot and this is work:

procedure TODBCStatement.ExecutePrepared;
...
status := ODBC.BindParameter(fStatement, p+1, InputOutputType, CValueType,
  CType2SQL(CValueType), {ColumnSize}0, 0, ParameterValue, ColumnSize, StrLen_or_Ind[p]);
...

Also, this change tested on MySQL and PostgreSQL.

Is it correct fix?

zed · mORMot 1

zed wrote:

[offtop]
Can you, please, add option EngineAddUseSelectMaxID to MongoDB Engine too?
[/offtop]

My pull request: https://github.com/synopse/mORMot/pull/8/files

zed · mORMot 1

[offtop]
Can you, please, add option EngineAddUseSelectMaxID to MongoDB Engine too?
[/offtop]

zed · mORMot 1

Yes, this helped.

FProps.UseCache := False;

zed · mORMot 1

ab wrote:

Are you using latest source from nightly zip?

Yes, from git repository: 1.18.2003

zed · mORMot 1

Hi, Ab. I try to use MS SQL Server 2008 R2 with mORMot. Simple test:

unit u_ODBC_AutoIDTest;

interface

procedure DoTest;

implementation

uses
  SysUtils,
  Classes,
  mORMot,
  mORMotSQLite3,
  mORMotDB,
  SynDB,
  SynDBODBC,
  SynSQLite3Static,
  SynCommons;

type
  TSQLTestRec = class(TSQLRecord)
  public
    FTestField: Int64;
  published
    property TestField: Int64 read FTestField write FTestField;
  end;

procedure DoTest;
const
  cDNS = 'TEST_MSSQL';
var
  I: Integer;
  VId: TID;
  FModel: TSQLModel;
  FClientDB: TSQLRestClientDB;
  FProps: TSQLDBConnectionProperties;
  VStorage: TSQLRestStorageExternal;
  VRec: TSQLTestRec;
begin
  FModel := TSQLModel.Create([TSQLTestRec]);
  try
    FProps := TODBCConnectionProperties.Create(cDNS, '', '', '');
    try

      for I := 0 to High(FModel.Tables) do begin
        if not VirtualTableExternalRegister(FModel, FModel.Tables[i], FProps, '') then begin
          raise Exception.Create('VirtualTableExternalRegister failed');
        end;
      end;

      FClientDB := TSQLRestClientDB.Create(FModel, nil, ':memory:', TSQLRestServerDB);
      try
        FClientDB.Server.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

        FClientDB.Server.CreateMissingTables;

        for I := 0 to High(FModel.Tables) do begin
          VStorage := TSQLRestStorageExternal.Instance(FModel.Tables[i], FClientDB.Server);
          if Assigned(VStorage) then begin
            VStorage.EngineAddUseSelectMaxID := True;
          end;
        end;

        VRec := TSQLTestRec.Create;
        try
          VRec.FTestField := GetTickCount64;

          for I := 0 to 1 do begin
            FClientDB.TransactionBegin(TSQLTestRec, 1000);
            try
              VId := FClientDB.Add(VRec, True);
              Assert(VId > 0);
              FClientDB.Commit(1000, True);
            except
              FClientDB.RollBack(1000);
              raise;
            end;
          end;
        finally
          VRec.Free;
        end;

      finally
        FClientDB.Free;
      end;
    finally
      FProps.Free;
    end;
  finally
    FModel.Free;
  end;
end;

end.

First insertion is OK, but second is FAIL. If I set VStorage.EngineAddUseSelectMaxID := False; then both insertions is OK.

ODBC driver report error on second insertion: "TODBCStatement - TODBCLib error: [24000] [Microsoft][SQL Server Native Client 10.0]Invalid cursor state (0)\r\n". Full log is here: http://pastebin.com/RaBf1NkU

Can you tell me what is wrong?

P.S. The same test code work fine with MySQL.

zed · mORMot 1

I don't use cascading deletes too and with your last fixes my code works fine, again

zed · mORMot 1

My original declarations:

...
  TSQLMark = class(TSQLRecord)
  public
    FCategory: TSQLCategory;
    FImage: TSQLMarkImage;
    FAppearance: TSQLMarkAppearance;
    FName: RawUTF8;
    FDesc: RawUTF8;
    FGeoType: TSQLGeoType;
    FGeoCount: Cardinal;
    FGeoLonSize: Cardinal;
    FGeoLatSize: Cardinal;
    FGeoWKB: TSQLRawBlob;
  published
    property mCategory: TSQLCategory read FCategory write FCategory;
    property mImage: TSQLMarkImage read FImage write FImage;
    property mAppearance: TSQLMarkAppearance read FAppearance write FAppearance;
    property mName: RawUTF8 read FName write FName;
    property mDesc: RawUTF8 read FDesc write FDesc;
    property mGeoType: TSQLGeoType read FGeoType write FGeoType;
    property mGeoCount: Cardinal read FGeoCount write FGeoCount;
    property mGeoLonSize: Cardinal read FGeoLonSize write FGeoLonSize;
    property mGeoLatSize: Cardinal read FGeoLatSize write FGeoLatSize;
    property mGeoWKB: TSQLRawBlob read FGeoWKB write FGeoWKB;
  end;

  TSQLMarkClass = class of TSQLMark;

  TSQLMarkDBMS = class(TSQLMark)
  public
    FLeft, FRight, FBottom, FTop: Cardinal;
  published
    property mLeft: Cardinal read FLeft write FLeft;
    property mRight: Cardinal read FRight write FRight;
    property mBottom: Cardinal read FBottom write FBottom;
    property mTop: Cardinal read FTop write FTop;
  end;

  TSQLMarkMongoDB = class(TSQLMark)
  public
    FGeoJsonIdx: Variant;
  published
    property mGeoJsonIdx: Variant read FGeoJsonIdx write FGeoJsonIdx;
  end;

  TSQLMarkView = class(TSQLRecord)
  public
    FUser: TSQLUser;
    FMark: TSQLMark;
    FCategory: TSQLCategory;
    FVisible: Boolean;
  published
    property mvUser: TSQLUser read FUser write FUser;
    property mvMark: TSQLMark read FMark write FMark;
    property mvCategory: TSQLCategory read FCategory write FCategory;
    property mvVisible: Boolean read FVisible write FVisible;
  end;
...

And yes, in my TSQLMarkView I have field that pointing to TSQLMark. What can I do in this case? Change field type declaration in TSQLMarkView form TSQLMark to TID?

zed · mORMot 1

I have some class inherits:

TSQLMyTable = class(TSQLRecord)
private
  ...
published
  ...
end;

TSQLMyTableA = class(TSQLMyTable)
private
  ...
published
  ...
end;

TSQLMyTableB = class(TSQLMyTable)
private
  ...
published
  ...
end;

I create a model depends on used DB:

if ... then
  FModel := TSQLModel.Create([TSQLMyTableA])
else
  FModel := TSQLModel.Create([TSQLMyTableB]);

Then I call VirtualTableExternalRegister and register table as "MyTable" in any case. So in ORM level I can access only to the base fields from TSQLMyTable. But also I can excec some direct sql requests with optimisations for different DB using custom fields from TSQLMyTableA or TSQLMyTableB. All this worked perfectly until your last commit (1.18.1991), now it rises exception: 'TSQLModel must include TSQLMyTable' in GetTableIndexSafe

zed · mORMot 1

Also, I propose increase default buffer size for big blobs in function TODBCStatement.BindColumns:

        if ColumnSize>65535 then
          ColumnSize := 0; // avoid out of memory error for BLOBs

After ColumnSize sets to zero, buffer size sets to 64 and first call GetData returns very small part of data and generate info message in logs about buffer size:

000000000184E389 ! info [01004] The buffer was too small for the GetData. (-2)
0000000001B122A8 ! info [01004] The buffer was too small for the GetData. (-2)

I propose set it at least 64k (MINIMUM_CHUNK_SIZE) or much greater 10*64k for example ("640k will be enough"(c)).

Although, it will not be very important if after first call GetData, buffer will be increased to the actual blob size (now it's increased only to 64k).

zed · mORMot 1

From here: https://msdn.microsoft.com/en-us/library/ms715441.aspx

SQLSTATE: 01004
Error: String data, right truncated
Description: Not all of the data for the specified column, Col_or_Param_Num, could be retrieved in a single call to the function. SQL_NO_TOTAL or the length of the data remaining in the specified column prior to the current call to SQLGetData is returned in *StrLen_or_IndPtr. (Function returns SQL_SUCCESS_WITH_INFO.)

So, after first call of GetData, if we detect that data is truncated, we can use ColumnDataSize to calculate total buffer size for the next call GetData and set result string size. But, I don't know what we should do in case if function return SQL_NO_TOTAL.

zed · mORMot 1

+1 for tests with ODBC.

mORMot Open Source

#51 Re: mORMot 1 » TCurlHttp access to curl.easy_setopt » 2019-09-03 12:53:23

#52 Re: mORMot 1 » TDynArray.Sort bug » 2019-02-07 06:43:44

#53 mORMot 1 » TDynArray.Sort bug » 2019-02-06 08:16:09

#54 Re: mORMot 1 » Created ZIP file is not valid » 2018-07-20 08:21:47

#55 Re: mORMot 1 » Created ZIP file is not valid » 2018-07-19 15:56:16

#56 Re: mORMot 1 » Created ZIP file is not valid » 2018-07-19 15:19:03

#57 Re: mORMot 1 » Created ZIP file is not valid » 2018-07-19 09:35:18

#58 mORMot 1 » MongoDB 3.6 require "cursor" in aggregate requests » 2018-06-22 21:29:23

#59 Re: mORMot 1 » WebSockets memory usage » 2018-06-16 15:25:00

#60 Re: mORMot 1 » WebSockets memory usage » 2018-06-16 15:02:37

#61 Re: mORMot 1 » Pattern match failed » 2018-02-23 21:52:06

#62 Re: mORMot 1 » Pattern match failed » 2018-02-23 19:19:26

#63 mORMot 1 » Pattern match failed » 2018-02-22 19:34:27

#64 Re: mORMot 1 » Decrypt results differ for Win32 and Win64 (Delphi 10.2) » 2017-12-17 13:18:30

#65 Re: mORMot 1 » FastFindInt64Sorted hangs on 32-bit build » 2017-11-20 15:10:42

#66 mORMot 1 » FastFindInt64Sorted hangs on 32-bit build » 2017-11-20 14:59:28

#67 Re: mORMot 1 » WinHTTP, http.sys and WebSockets » 2017-11-14 14:19:43

#68 Re: mORMot 1 » Default Winhttp proxy modification request » 2017-08-16 16:46:42

#69 Re: mORMot 1 » [fixed] The datetime column of Server log suddenly becomes unreadable » 2017-02-07 09:44:54

#70 Re: mORMot 1 » Bug with parsing JSON array of double in TDocVariantData » 2017-02-02 13:03:18

#71 Re: mORMot 1 » Bug with parsing JSON array of double in TDocVariantData » 2017-02-02 08:48:44

#72 Re: mORMot 1 » Bug with parsing JSON array of double in TDocVariantData » 2017-02-02 07:14:11

#73 Re: mORMot 1 » Bug with parsing JSON array of double in TDocVariantData » 2017-02-02 07:00:35

#74 mORMot 1 » Bug with parsing JSON array of double in TDocVariantData » 2017-02-01 17:47:00

#75 Re: Fast JPEG decoder » Rendre jpegdec compatible multithread » 2017-01-15 06:29:21

#76 Re: mORMot 1 » How to use Multi table transaction » 2017-01-13 12:43:17

#77 Re: mORMot 1 » SNAPPY delphi 64 port » 2016-08-08 07:00:54

#78 Re: mORMot 1 » SynOleDB and ExecutePrepared » 2016-05-25 15:40:39

#79 Re: mORMot 1 » Error saving Blobs to MS SQL via OleDB » 2016-02-20 10:35:14

#80 Re: mORMot 1 » Error saving Blobs to MS SQL via OleDB » 2015-11-13 18:30:10

#81 Re: mORMot 1 » Error saving Blobs to MS SQL via OleDB » 2015-11-12 12:42:11

#82 Re: mORMot 1 » Error saving Blobs to MS SQL via OleDB » 2015-11-12 08:44:51

#83 Re: mORMot 1 » MS SQL 2008 via ODBC raise error "Invalid Descriptor Index" » 2015-11-11 19:59:57

#84 mORMot 1 » Error saving Blobs to MS SQL via OleDB » 2015-11-11 19:51:47

#85 Re: mORMot 1 » MS SQL 2008 via ODBC raise error "Invalid Descriptor Index" » 2015-11-09 07:58:14

#86 Re: mORMot 1 » MS SQL 2008 via ODBC raise error "Invalid Descriptor Index" » 2015-11-08 20:53:06

#87 Re: mORMot 1 » MS SQL 2008 via ODBC raise error "Invalid Descriptor Index" » 2015-11-08 18:22:51

#88 mORMot 1 » MS SQL 2008 via ODBC raise error "Invalid Descriptor Index" » 2015-11-08 15:04:00

#89 mORMot 1 » ODBC with SQL_VARBINARY(MAX) gives "Invalid precision value" » 2015-11-04 12:02:16

#90 Re: mORMot 1 » MS SQL ODBC error when set EngineAddUseSelectMaxID to True » 2015-11-02 13:42:26

#91 Re: mORMot 1 » MS SQL ODBC error when set EngineAddUseSelectMaxID to True » 2015-10-20 19:07:51

#92 Re: mORMot 1 » MS SQL ODBC error when set EngineAddUseSelectMaxID to True » 2015-10-20 18:34:35

#93 Re: mORMot 1 » MS SQL ODBC error when set EngineAddUseSelectMaxID to True » 2015-10-16 15:48:08

#94 mORMot 1 » MS SQL ODBC error when set EngineAddUseSelectMaxID to True » 2015-10-16 13:26:44

#95 Re: mORMot 1 » Build 1.18.1991 - TSQLRecord class inherits bug? » 2015-10-15 12:09:41

#96 Re: mORMot 1 » Build 1.18.1991 - TSQLRecord class inherits bug? » 2015-10-14 17:33:02

#97 mORMot 1 » Build 1.18.1991 - TSQLRecord class inherits bug? » 2015-10-14 16:41:12

#98 Re: mORMot 1 » Bug: RetrieveBlob with ODBC connection » 2015-08-15 14:29:39

#99 Re: mORMot 1 » Bug: RetrieveBlob with ODBC connection » 2015-08-15 13:30:07

#100 Re: mORMot 1 » My test 15 - External DB performance and ZEOS 7.2-beta » 2015-08-15 12:39:50

Board footer