Problem with BLOB/UniDAC/REST/MSSQL

itSDS · 2020-03-17 19:47:23

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)

mpv · 2020-03-18 07:19:50

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

itSDS · 2020-03-18 07:51:39

Actually i use synoledb for test i ll post my results

itSDS · 2020-03-19 21:19:33

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.

ab · 2020-03-20 10:23:56

It is weird.

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

Thanks for the report!

itSDS · 2020-03-20 12:56:31

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.

ab · 2020-03-20 14:12:24

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.

itSDS · 2020-03-20 15:21:21

sry ab didn't think about the size

ab · 2020-03-20 19:29:55

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!

itSDS · 2020-03-20 19:45:53

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

itSDS · 2020-03-20 20:08:42

I made it a little "Smarter"

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;

ab · 2020-03-20 20:51:58

Please check https://synopse.info/fossil/info/56de45ebed

mORMot Open Source

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

Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

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

Re: Problem with BLOB/UniDAC/REST/MSSQL

Board footer