#1 2015-11-11 19:51:47

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 sad

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

Offline

#2 2015-11-11 22:44:31

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#3 2015-11-12 08:44:51

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;

Offline

#4 2015-11-12 11:25:37

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#5 2015-11-12 12:42:11

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)

Offline

#6 2015-11-13 04:23:44

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

Offline

#7 2015-11-13 18:30:10

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)

Offline

#8 2015-11-24 19:03:21

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 506

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

Offline

#9 2016-02-16 15:45:58

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.

Offline

#10 2016-02-17 10:53:36

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;

Offline

#11 2016-02-18 08:57:31

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.

Offline

#12 2016-02-20 10:35:14

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 105

Re: Error saving Blobs to MS SQL via OleDB

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?

Offline

#13 2016-05-26 06:42:17

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Error saving Blobs to MS SQL via OleDB

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

Any feedback is welcome!

Offline

#14 2020-01-02 00:14:54

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?

EvaF wrote:

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)

Offline

Board footer

Powered by FluxBB