TSQLDBStatement.Bind

EMartin · 2015-02-22 12:52:59

Hi @ab, I made a modification but I think that exists better way. These are the steps involved:

The client side generate the parameters and invoke the service:

  //2. Se inicia el cliente
  lRestClient := TSQLHttpClient.Create('127.0.0.1', '8888', CreateModelDBService);
  try
    lRestClient.ServiceDefine([IDBService], sicClientDriven{sicShared});
    if lRestClient.Services['DBService'].Get(lDBSvc) then
    begin
      lParams := _ArrFast(['INSERT_DIAL_DATA', // nombre de la tabla
                           '12345678',     // teléfono
                           FormatDateTime('YYYYMMDDHHNNSS', IncMinute(Now, 3)),
                           'valor 1|valor 2|valor 3', // valores para los campos Vnn_TM
                           1, // cola
                           20]); // prioridad

      lResp := lDBSvc.ExecSqlStm('INSDIALDAT', lParams, lResult);
      Check(lResp = 1, 'No se actualizó la tabla'); 
    end;
  finally
    lDBSvc := Nil;
    lRestClient.Free;
    TearDown;
  end;

The server side (excerpt) execute the query (definition is stored in a Firebird table):

function TDBService.ExecSqlStm(const aIDMDService: RawUTF8; const aParams: Variant; out aResult: RawUTF8): Integer;
var
  ...
  lStm: TSQLDBStatement;
  lParams: array of TVarRec;
  ...
begin
  ...
  // convert aParams to lParams
  TDocVariantData(aParams).InitJSON(aParams);
  SetLength(lParams, TDocVariantData(aParams).Count);
  for I := 0 to TDocVariantData(aParams).Count-1 do
    VariantToVarRec(TDocVariantData(aParams).Values[i], lParams[i]);
  // execute the statement
  lStm.Execute(lSQLStmText, True, lParams);
  ...
end;

Execute invoke the Bind procedure:

procedure TSQLDBStatement.Execute(const aSQL: RawUTF8;
  ExpectResults: Boolean; const Params: array of const);
begin
  Connection.InternalProcess(speActive);
  try
    Prepare(aSQL,ExpectResults);
    Bind(Params);
    ExecutePrepared;
  finally
    Connection.InternalProcess(speNonActive);
  end;
end;

And my problem is in Bind(Params):

procedure TSQLDBStatement.Bind(const Params: array of const;
  IO: TSQLDBParamInOutType);
var i,c: integer;
begin
  for i := 1 to high(Params)+1 do
  with Params[i-1] do // bind parameter index starts at 1
  case VType of
    vtString:     // expect WinAnsi String for ShortString
      BindTextU(i,WinAnsiToUtf8(@VString^[1],ord(VString^[0])),IO);
    vtAnsiString:
      if VAnsiString=nil then
        BindTextU(i,'',IO) else begin
        c := PInteger(VAnsiString)^ and $00ffffff;
        if c=JSON_BASE64_MAGIC then
          BindBlob(i,Base64ToBin(PAnsiChar(VAnsiString)+3,length(RawUTF8(VAnsiString))-3)) else
        if c=JSON_SQLDATE_MAGIC then
          BindDateTime(i,Iso8601ToDateTimePUTF8Char(PUTF8Char(VAnsiString)+3,length(RawUTF8(VAnsiString))-3)) else
          // expect UTF-8 content only for AnsiString, i.e. RawUTF8 variables
          BindTextU(i,RawUTF8(VAnsiString),IO);
      end;
    vtPChar:      BindTextP(i,PUTF8Char(VPChar),IO);
    vtChar:       BindTextU(i,RawUTF8(VChar),IO);
    vtWideChar:   BindTextU(i,RawUnicodeToUtf8(@VWideChar,1),IO);
    vtPWideChar:  BindTextU(i,RawUnicodeToUtf8(VPWideChar,StrLenW(VPWideChar)),IO);
    vtWideString: BindTextW(i,WideString(VWideString),IO);
{$ifdef UNICODE}
    vtUnicodeString: BindTextS(i,string(VUnicodeString),IO);
{$endif}
    vtBoolean:    Bind(i,integer(VBoolean),IO);
    vtInteger:    Bind(i,VInteger,IO);
    vtInt64:      Bind(i,VInt64^,IO);
    vtCurrency:   BindCurrency(i,VCurrency^,IO);
    vtExtended:   Bind(i,VExtended^,IO);
    vtPointer:
      if VPointer=nil then
        BindNull(i,IO) else
        raise ESQLDBException.CreateUTF8('Unexpected %.Bind() pointer',[self]);
    vtVariant:    BindVariant(i,VVariant^,false{true},IO); // *** false instead of true for the parameter IsDataBlob ***
    else
      raise ESQLDBException.CreateUTF8('%.BindArrayOfConst(Param=%,Type=%)',
        [self,i,VType]);
  end;
end;

I had to modify the line // *** because all string values were converted as BLOB execution failing. Is there another solution ?

Thanks.

ab · 2015-02-22 15:36:15

You are doing weird things, IMHO...

For instance, TDBService.ExecSqlStm(const aIDMDService: RawUTF8; const aParams: Variant...) will return already a TDocVariant into aParams.
No need to write awful things like

TDocVariantData(aParams).InitJSON(aParams);

Then, the whole purpose of your code is to run a SQL command remotely.
Your code is not SOA-compatible, nor RESTful compatible: you are not sending data by representation, but sending SQL-like data.
If your purpose is to execute remotely some SQL statement, using SynDBRemote would be a much better idea.

By all means, TDocVariant is not meant to transmit blob data.
What you could do is to transmit the blob as a string with BASE64_MAGIC, using BinToBase64WithMagic(), then identify this pattern on the server side, then use BindBlob() explicitly for this kind of value.

But honnestly I would not follow this path, and either use SynDBRemote, or create true DTO and a proper SOA/REST service.

EMartin · 2015-02-22 16:12:31

Sorry for my bad explanation. I don't want transmit any blob data. I am trying to migrate an existing legacy RemObjects application server. In this application server I have defined SQL queries against RDBMS of our customers, these queries have an associated identifier, then the application server find the query definition with the identifier (aIDMDService), create the SQL object (with ADO or IBX in RemObjects) and assign the parameter values (aParams) to the query parameters.

I want to respect this behavior, but when I convert the parameters (variant) to the query parameters (array of TVarRec) the VariantToVarRec function set all as Variant and the Bind function convert this as blob.

The client applications never send sql statements and so I don't use SynDBRemote.

I hope have better explained.

Thanks in advance.

ab · 2015-02-22 19:29:05

Sorry for the misunderstanding.
I know how difficult it is sometimes when playing with legacy code...

IMHO you should better not use the Bind(array of const), but a simple for ... loop with individual BindVariant().

Anyway, I've enhanced SynDB to allow detection of BASE64_MAGIC text as blobs for variant values.
See http://synopse.info/fossil/info/c9df25129a
It should solve your issue.

Any feedback about why you changed from RO into mORMot is welcome!

EMartin · 2015-02-23 00:31:54

Thanks @ab, I can't test for now your modification but I'll do it. I have added the function ParamsToJson (similar to DatasetToJson) in SynVirtualDataset:

...
/// export parameters of TParams into JSON (format is same as DataSetToJSON)
// - aIncludeParamTypes: if [] all parameters are include.
function ParamsToJSON(Params: TParams; const aIncludeParamTypes: TParamTypes = []): RawUTF8;
...
function ParamsToJSON(Params: TParams; const aIncludeParamTypes: TParamTypes = []): RawUTF8;
var W: TJSONWriter;
    f, lCount, I: integer;
begin
  result := 'null';
  if Params=nil then
    exit;
  if (Params.Count = 0) then
    Exit;
  W := TJSONWriter.Create(nil,true,false);
  try
    // get col names and types
    lCount := 0;
    for f := 0 to Params.Count-1 do
      if (aIncludeParamTypes = []) or (Params[f].ParamType in aIncludeParamTypes) then
        Inc(lCount);
    SetLength(W.ColNames, lCount);
    I := 0;
    for f := 0 to Params.Count-1 do
      if (aIncludeParamTypes = []) or (Params[f].ParamType in aIncludeParamTypes) then
      begin
        StringToUTF8(Params[f].Name,W.ColNames[i]);
        Inc(I);
      end;
    W.AddColumns;
    W.Add('[','{');
    I := -1;
    for f := 0 to Params.Count-1 do begin
      if (aIncludeParamTypes <> []) and (not (Params[f].ParamType in aIncludeParamTypes)) then
        Continue;
      Inc(I);
      W.AddString(W.ColNames[i]);
      with Params[f] do
      if IsNull then
        W.AddShort('null') else
      case DataType of
      ftBoolean:
        W.AddString(JSON_BOOLEAN[AsBoolean]);
      ftSmallint, ftInteger, ftWord, ftAutoInc:
        W.Add(AsInteger);
      ftLargeint:
        W.Add(Params[f].Value);
      ftFloat, ftCurrency, ftBCD:
        W.Add(AsFloat, DOUBLE_PRECISION);
      ftTimeStamp, ftDate, ftTime, ftDateTime: begin
        W.Add('"');
        W.AddDateTime(AsDateTime);
        W.Add('"');
      end;
      ftString, ftFixedChar, ftMemo: begin
        W.Add('"');
        W.AddAnsiString({$ifdef UNICODE}AsAnsiString{$else}AsString{$endif},
          twJSONEscape);
        W.Add('"');
      end;
      ftWideString: begin
        W.Add('"');
        W.AddJSONEscapeW(pointer(TWideStringField(Params[f]).Value));
        W.Add('"');
      end;
      ftVariant:
        W.AddVariantJSON(Value);
      ftBytes, ftVarBytes, ftBlob, ftGraphic, ftOraBlob, ftOraClob: begin
        W.WrBase64(pointer(Params[f].AsBlob),length(Params[f].AsBlob),true);
      end;
      {$ifdef ISDELPHI2007ANDUP}
      ftWideMemo, ftFixedWideChar: begin
        W.Add('"');
        W.AddJSONEscapeW(pointer(AsWideString));
        W.Add('"');
      end;
      {$endif}
      {$ifdef UNICODE}
      ftShortint, ftByte:
        W.Add(AsInteger);
      ftLongWord:
        W.AddU(TLongWordField(Params[f]).Value);
      ftExtended, ftSingle:
        W.Add(AsFloat);
      {$endif}
      else W.AddShort('null'); // unhandled field type
      end;
      W.Add(',');
    end;
    W.CancelLastComma;
    W.Add('}',']');
    W.SetText(result);
  finally
    W.Free;
  end;
end;
...

Maybe you want add this.

On why I am migrating (really I am doing a comparison) is in principle by performance. I think that mORMot is very fast against RemObjects with Indy, I changed in a project http Indy by http mORMot and the performance and the usage resources are abysmal.

ab · 2015-02-23 07:07:33

If you are transmitting json with services, ensure you use RawJson as parameter type.

mORMot Open Source

#1 2015-02-22 12:52:59

TSQLDBStatement.Bind

#2 2015-02-22 15:36:15

Re: TSQLDBStatement.Bind

#3 2015-02-22 16:12:31

Re: TSQLDBStatement.Bind

#4 2015-02-22 19:29:05

Re: TSQLDBStatement.Bind

#5 2015-02-23 00:31:54

Re: TSQLDBStatement.Bind

#6 2015-02-23 07:07:33

Re: TSQLDBStatement.Bind

Board footer