Publish date: 2003-01-05

Work with BLOB fields and parameters

function TPxUserConfigurator.SaveMenuToUser(const strUserName, strMenu: WideString): HResult;
var
  cmd: _Command;
  ra: OleVariant;
begin
  Result := S_OK;
  cmd := CoCommand.Create;
  cmd.Set_ActiveConnection(GetConnection);
  try
    cmd.Set_CommandText('update umenus set menu = ? where username= ?');
    cmd.Parameters.Append(cmd.CreateParameter('username', adVarChar, adParamInput, length(strUserName), strUserName))

    if strMenu <> EmptyStr then
      cmd.Parameters.Append(cmd.CreateParameter('menu', adLongVarChar, adParamInput, length(strMenu), strMenu))
    else
      cmd.Parameters.Append(cmd.CreateParameter('menu', adLongVarChar, adParamInput, 0, Null));
    cmd.Execute(ra, EmptyParam, 0);
    SetComplete;
  except
    on E: Exception do begin
      Result := E_FAIL;
      SetAbort;
      raise;
    end;
  end;
end;

// Getting data from binary BLOB (SUBTYPE 2)

// Function converts a binary BLOB into string
function GetTextBlob(Value: Variant; ActualSize: integer): string;
var
  P: pointer;
begin
  Result := '';
  if Value = Null then
    Exit;
  P := VarArrayLock(Value);
  try
    SetLength(Result, ActualSize);
    Move(P^, Result[1], ActualSize);
  finally
    VarArrayUnlock(Value);
  end;
end;

function TPxUserConfigurator.LoadMenuFromUser(const strUserName: WideString; out strMenu: WideString): HResult;
var
  rs: _Recordset;
  co: _Connection;
  ra: OleVariant;
begin
  Result := S_OK;
  co := GetConnection;
  try
    rs := co.Execute('select menu from umenus ' +
      'where username = ''' + strUserName + '''', ra, 0);
    strMenu := GetTextBlob(rs.Fields['menu'].Value,
      rs.Fields['menu'].ActualSize);
    SetComplete;
  except
    on E: Exception do begin
      Result := E_FAIL;
      SetAbort;
      raise;
    end;
  end;
end;

// The note.
// For getting data from text BLOB does not cost convert the value through GetTextBlob function