#1 2020-03-17 19:47:23

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

Problem with BLOB/UniDAC/REST/MSSQL

I have a Problem with Updating a TSQLRecord's Blob Data. The Problem is visible in this Picture (Screen from LOG Viewer)

LOG Snippet

I update 3 "BinaerdatenRecord" after i insert them. I update 2 fields, BLOB Field Binaerdaten and Integer Field GroesseinBytes

In the First Call mORMot / UniDAC calls Prepare for the Update Statement.
In the following calls the Prepared Statement is used.

As you can see in the violet boxes the BLOB - Data has different sizes 143.5 / 119.2 and 100.4KB
the Blob Parameter is 110210 Byte

The Error is:

All 3 Database Records are filled with the Same BLOB - Data

Now my Question:
Is it a mORMot or a UniDAC Problem ? As Database Server i Use MS-SQL 2012
Is it Possible to disable Prepared Statement for Queries with BLOB Parameter

Last edited by itSDS (2020-03-17 19:48:49)


Rad Studio 12.1 Santorini

Offline

#2 2020-03-18 07:19:50

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Problem with BLOB/UniDAC/REST/MSSQL

itSDS wrote:

Is it a mORMot or a UniDAC Problem ? As Database Server i Use MS-SQL 2012

You can try to replace UniDAC with TOleDBMSSQL2012ConnectionProperties to ensure this is UniDAC problem or not

Offline

#3 2020-03-18 07:51:39

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Actually i use synoledb for test i ll post my results


Rad Studio 12.1 Santorini

Offline

#4 2020-03-19 21:19:33

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Results: After a while of Debugging in found that the Problem is in UNIDAC. I get the same error with MySQL / MSSQL - Provider.
I reproduced the behavior every time. The Problem with mORMot i have only with the SynCrossPlatform Client. Here SynDBUniDAC is used. As a Workaround i modified TSQLDBUniDACConnectionProperties.

My Code is not perfect normally i should set
UseCache := false
in Create.

function TSQLDBUniDACConnectionProperties.IsCachable(P: PUTF8Char): boolean;
// UNIDAC Probleme mit Update - Statements mit BLOB Parametern.
// Bei gecachten Statement wird der BLOB immer aus dem "Cache" genommen
begin
  result := not IdemPChar(P,'UPDATE ');
  if result then
    result := inherited IsCachable(P);
end;

There is some Strange Behavior in UniDAC, where i could not find the error. SetBlobParam set's the Blob correctly, but TCustomDASQL.AssignParamValue( seems to use a wrong Value from "i don't know where" it is taken.


Rad Studio 12.1 Santorini

Offline

#5 2020-03-20 10:23:56

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

It is weird.

I have integrated your patch as https://synopse.info/fossil/info/b8ce17a449

Thanks for the report!

Offline

#6 2020-03-20 12:56:31

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Hi AB,

today i found the real Solution for the BUG.
My Previuosly change is not correct and can be removed.

UniDAC has overriden the TParam with TDAParam in DBAccess.
Your TSQLDBDatasetStatement.DataSetBindSQLParam uses TParam.SetBlobData function and does not check that TDAParam has another Implementation for SetBlobData (Its not overrride / virtual)

As Solution i added TSQLDBUniDACStatement.DataSetBindSQLParam which checks wether TParam is a TDAParam and then calls the correct SetBlobData function.

Pls change my BUG FIX as follows:

SynDBUniDAC.pas

class declaration:

  ///	implements a statement via a UniDAC connection
  TSQLDBUniDACStatement = class(TSQLDBDatasetStatement)
  protected
    /// initialize and set fQuery: TUniQuery internal field as expected
    procedure DatasetCreate; override;
    /// set fQueryParams internal field as expected
    function DatasetPrepare(const aSQL: string): boolean; override;
    /// execute underlying TUniQuery.ExecSQL
    procedure DatasetExecSQL; override;
    procedure DataSetBindSQLParam(const aArrayIndex, aParamIndex: integer; const aParam: TSQLDBParam); override;
  public
  end;
procedure TSQLDBUniDACStatement.DataSetBindSQLParam(const aArrayIndex, aParamIndex: integer; const aParam: TSQLDBParam);
var P: TDAParam;
    I64: Int64;
    tmp: RawUTF8;
begin
// SynDBLog.Enter('DataSetBindSQLParam(%, %, %)', [aArrayIndex, AParamIndex, Length(aParam.VData)], Self);
  if fQueryParams[aParamIndex] is TDAParam then begin
    with aParam do begin
      P := TDAParam(fQueryParams[aParamIndex]);
      P.ParamType := SQLParamTypeToDBParamType(VInOut);
      if VinOut <> paramInOut then
        case VType of
          SynTable.ftNull: begin
            P.Clear;
            {$ifdef UNICODE}
            P.AsBlob := nil; // avoid type errors when a blob field is adressed
            {$else}
            P.AsString := '';
            {$endif}
          end;
          SynTable.ftInt64: begin
            if aArrayIndex>=0 then
              I64 := GetInt64(pointer(VArray[aArrayIndex])) else
              I64 := VInt64;
            {$ifdef UNICODE}
            P.AsLargeInt := I64;
            {$else}
            if (PInt64Rec(@I64)^.Hi=0) or (PInt64Rec(@I64)^.Hi=Cardinal(-1)) then
              P.AsInteger := I64 else
              if TSQLDBDatasetConnectionProperties(Connection.Properties).
                 fForceInt64AsFloat then
                P.AsFloat := I64 else
                P.Value := I64;
            {$endif}
          end;
          SynTable.ftDouble:
            if aArrayIndex>=0 then
              P.AsFloat := GetExtended(pointer(VArray[aArrayIndex])) else
              P.AsFloat := unaligned(PDouble(@VInt64)^);
          SynTable.ftCurrency:
            if aArrayIndex>=0 then
              P.AsCurrency := StrToCurrency(pointer(VArray[aArrayIndex])) else
              P.AsCurrency := PCurrency(@VInt64)^;
          SynTable.ftDate:
            if aArrayIndex>=0 then begin
              UnQuoteSQLStringVar(pointer(VArray[aArrayIndex]),tmp);
              P.AsDateTime := Iso8601ToDateTime(tmp);
            end else
              P.AsDateTime := PDateTime(@VInt64)^;
          SynTable.ftUTF8:
            if aArrayIndex>=0 then
              if (VArray[aArrayIndex]='') and
                 fConnection.Properties.StoreVoidStringAsNull then
                P.Clear else begin
              UnQuoteSQLStringVar(pointer(VArray[aArrayIndex]),tmp);
              if fForceUseWideString then
                P.Value := UTF8ToWideString(tmp) else
                P.AsString := UTF8ToString(tmp);
            end else
              if (VData='') and fConnection.Properties.StoreVoidStringAsNull then
                P.Clear else
              if fForceUseWideString then
                P.Value := UTF8ToWideString(VData) else
                P.AsString := UTF8ToString(VData);
          SynTable.ftBlob:
            {$ifdef UNICODE}
            if aArrayIndex>=0 then
              P.SetBlobData(Pointer(VArray[aArrayIndex]),Length(VArray[aArrayIndex]))
            else
              P.SetBlobData(Pointer(VData),Length(VData));
            {$else}
            if aArrayIndex>=0 then
              P.AsString := VArray[aArrayIndex] else
              P.AsString := VData;
            {$endif}
          else
            raise ESQLDBDataset.CreateFmt(
              '%.DataSetBindSQLParam: Invalid type % on bound parameter #%d',
              [self,ord(VType),aParamIndex+1]);
          end;
    end;
  end else
    inherited DataSetBindSQLParam(aArrayIndex, aParamIndex, aParam);
end;

I made some more modifications to SynDBUniDac. i will send it to you by mail.


Rad Studio 12.1 Santorini

Offline

#7 2020-03-20 14:12:24

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Please follow the forum rules, and don't post so much code in the forum.

The preferred way is to make a pull request on github.

But it is nice seeing some feedback from you!

I am waiting from your email.

Offline

#8 2020-03-20 15:21:21

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

sry ab didn't think about the size


Rad Studio 12.1 Santorini

Offline

#9 2020-03-20 19:29:55

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Please check https://synopse.info/fossil/info/6a79fe05d9132b84

I am not sure TSQLDBUniDACStatement.DataSetBindSQLParam with a full rewrite is mandatory...
But if it works, it is fine! smile

Offline

#10 2020-03-20 19:45:53

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

I just made a copy and fixed the BLOB Part - But its possible to only change the BLOB Part for sure.


Rad Studio 12.1 Santorini

Offline

#11 2020-03-20 20:08:42

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

I made it a little "Smarter" smile

procedure TSQLDBUniDACStatement.DataSetBindSQLParam(const aArrayIndex, aParamIndex: integer; const aParam: TSQLDBParam);
var
  P : TDAParam;
begin
// SynDBLog.Enter('DataSetBindSQLParam(%, %, %)', [aArrayIndex, AParamIndex, Length(aParam.VData)], Self);
  if fQueryParams[aParamIndex] is TDAParam then begin
    with aParam do begin
      P := TDAParam(fQueryParams[aParamIndex]);
      P.ParamType := SQLParamTypeToDBParamType(VInOut);
      if VinOut <> paramInOut then
        case VType of
          SynTable.ftBlob: begin
{$ifdef UNICODE}
            if aArrayIndex>=0 then
              P.SetBlobData(Pointer(VArray[aArrayIndex]),Length(VArray[aArrayIndex]))
            else
              P.SetBlobData(Pointer(VData),Length(VData));
{$else}
            if aArrayIndex>=0 then
              P.AsString := VArray[aArrayIndex]
            else
              P.AsString := VData;
{$endif}
            exit;
          end;
        end;
    end;
  end;
  inherited DataSetBindSQLParam(aArrayIndex, aParamIndex, aParam);
end;

Rad Studio 12.1 Santorini

Offline

#12 2020-03-20 20:51:58

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Offline

Board footer

Powered by FluxBB