mORMot Open Source

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Error saving Blobs to MS SQL via OleDB

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???

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Error saving Blobs to MS SQL via OleDB

Did you try more tuned class than TOleDBMSSQLConnectionProperties ?
Which compiler version are you using?

BTW try to use StringFromFile() and FileFromString() functions (or AnyTextFileToRawUTF8) instead of your overcomplicated stream use.
Take a look also at VirtualTableExternalRegisterAll, which avoid to loop over all tables via single VirtualTableExternalRegister() calls.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: Error saving Blobs to MS SQL via OleDB

>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;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Error saving Blobs to MS SQL via OleDB

You have TOleDBMSSQL2005ConnectionProperties, TOleDBMSSQL2008ConnectionProperties and TOleDBMSSQL2012ConnectionProperties classes to specify every available MS SQL Server providers, i.e. SQLNCLI, SQLNCLI10 or SQLNCLI11.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: Error saving Blobs to MS SQL via OleDB

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

Last edited by zed (2015-11-12 12:43:09)

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Error saving Blobs to MS SQL via OleDB

@Zed

you can also try my Zeos7.3 OleDB approach.
You'll need to checkout testing-7.3 branch from svn://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.3

create the Properties like

TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI('OleDB[mssql]', '',''),
              <DatabaseString>, <UserName>, <Password>)

Hope it helps..

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: Error saving Blobs to MS SQL via OleDB

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

Last edited by zed (2015-11-13 18:33:12)

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 516

Re: Error saving Blobs to MS SQL via OleDB

We encounter a Problem saving Blob under MySQL with UniDAC (atm 6.2.8). The memo - fields remain empty.
Using FireDAC works as expected !

---

Rad Studio 12.1 Santorini

MM

Member

Registered: 2016-02-16

Posts: 1

Re: Error saving Blobs to MS SQL via OleDB

@Zed

Did you find a solution to this issue?
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.

EvaF

Member

Registered: 2014-07-19

Posts: 40

Re: Error saving Blobs to MS SQL via OleDB

I had to solve the saving Blobs into MSSQL  a month ago (http://synopse.info/forum/viewtopic.php?id=3106).
I used a little bit different coding, but the principle is the same:

procedure TOleDBStatement.ExecutePrepared;
...
...
  ftBlob: begin
    B^.dwPart := DBPART_VALUE or DBPART_LENGTH or DBPART_STATUS;
    B^.obValue := PAnsiChar(@P^.VBlob)-pointer(fParams);
    B^.cbMaxLen := sizeof(Pointer);
    P^.VInt64 := length(P^.VBlob);
    B^.obLength := PAnsiChar(@P^.VInt64)-pointer(fParams);
// ---------------ParamBindInfo.ulParamSize-------------------
// For parameters that use a variable-length data type
// The maximum length of the data type in characters (for DBTYPE_STR and DBTYPE_WSTR) or in bytes (for DBTYPE_BYTES and DBTYPE_VARNUMERIC),
    BI^.ulParamSize := P^.VInt64;                       // <--- added the setting of length                
  end;
  ftUTF8: begin
    B^.obValue := PAnsiChar(@P^.VText)-pointer(fParams);
    ...
  end;
  end;
end;
if BI^.ulParamSize = 0  then
  BI^.ulParamSize := B^.cbMaxLen;                   //   <--- Here is set default length  - but it is valid only
                                                    //        for parameters that use a fixed-length data type

 

Still remains the question how to insert Null value into a varbinary(MAX) (=BLOB) field

I modified procedure ExecutePrepared this way, but I cannot see whether there will be other consequences

case P^.VType of
ftNull: begin
  P^.VStatus := ord(stIsNull);
//  BI.pwszDataSourceType := 'DBTYPE_WVARCHAR';          <-- commented by me
  BI.dwFlags := BI^.dwFlags or DBPARAMFLAGS_ISNULLABLE;

akirabbq

Member

Registered: 2012-12-03

Posts: 9

Re: Error saving Blobs to MS SQL via OleDB

Thanks, changing the cbMaxLen line fixed the truncate problem for me.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: Error saving Blobs to MS SQL via OleDB

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?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Error saving Blobs to MS SQL via OleDB

Please check http://synopse.info/fossil/info/e44c083b7d

Any feedback is welcome!

missionhq

Member

From: Australia

Registered: 2019-06-11

Posts: 33

Re: Error saving Blobs to MS SQL via OleDB

Was there a fix to the problem with inserting Null blobs into MS SQL?

Still remains the question how to insert Null value into a varbinary(MAX) (=BLOB) field

I'm wanting to store eg TInt64DynArray in a TSQLRecord property but getting the following if the array is empty 

! EXC   EOleDBException {"Message":"TOleDBConnection: OLEDB Error 80040E14 -   (line 1): Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.\r\n"} 

I am looking at serializing/deserializing all my dynamic arrays as JSON into the DB (which I'm happy to do) but is there already an easy way to do this?

(Using v1.18 and TOleDBMSSQL2012ConnectionProperties but have tried others too with same results)