如何使用Delphi XE3在SQLite中插入Blob

Question

我尝试用Delphi XE3在SQLite中插入blob字段。

我一直这样使用TDBXCommand和TSQLConnection。但是blob字段没有插入，甚至我也无法从查询中获得任何结果

procedure TDBXCommandHelper.Init(const AQry: String);
begin
  Parameters.ClearParameters;
  Close;
  Prepare;
  Text := AQry;
end;

procedure dmDB.InsertPicture;
const
  QRY = 'INSERT INTO Memo(Picture) VALUES(?)';
var
  LTransaction: TDBXTransaction;
  LBlob: TDBXParameter;
  LStream: TFileStream;
begin
  LTransaction := FDBCon.BeginTransaction;
  LStream := TFileStream.Create('d:\sample.bmp', fmOpenRead);
  LBlob := TDBXParameter.Create;
  try
    try
      FDBXCmd := FDBCon.DBXConnection.CreateCommand;
      FDBXCmd.CommandType := TDBXCommandTypes.DbxSQL;
      FDBXCmd.Init(QRY);

      LBlob.DataType := TDBXDataTypes.BlobType;
      LBlob.SubType := TDBXSubDataTypes.BinarySubType;
      LBlob.Value.SetStream(LStream, False);
      FDBXCmd.Parameters.AddParameter(LBlob);
      FDBXCmd.ExecuteUpdate;
    except
      on E: Exception do
        FDBCon.RollbackFreeAndNil(LTransaction);
    end;
    FDBCon.CommitFreeAndNil(LTransaction);
  finally
    FreeAndNil(LStream);
    FreeAndNil(LBlob);
  end;
end;

使用TSQLConnection，但我无法获得任何结果

procedure TInsertThread.NoteInsertExcute;
const
  QRY = 'INSERT INTO Memo(Picture) VALUES(:Picture)';
var
  LTransaction: TDBXTransaction;
  LParams: TParams;
  LStream: TMemoryStream;
begin
  LTransaction := FDBCon.BeginTransaction;
  LParams := TParams.Create(nil);
  LStream := TMemoryStream.Create;
  LStream.LoadFromFile(FValues.Values[NAME_PICTURE]);
  try
    LParams.CreateParam(ftBlob, 'Picture', ptInput);
    LParams.ParamByName('Picture').LoadFromStream(LStream, ftBlob);
    FDBCon.Execute(QRY, LParams);
    FDBCon.CommitFreeAndNil(LTransaction);
  finally
    FreeAndNil(LStream);
    FreeAndNil(LParams);
  end;
end;

Answer 1

您可以尝试以下操作：

function GetFileAsBytesValue(AFileName: TFileName): TArray<Byte>;
var
  Len: Integer;
  LStream: TMemoryStream;
begin
  LStream := TMemoryStream.Create;
  try
    LStream.LoadFromFile(AFileName);
    Len := LStream.Size;

    SetLength(Result, Len);
    Move(LStream.Memory^, Result[0], Len);
  finally
    LStream.Free;
  end;
end;

procedure dmDB.InsertPicture;
const
  QRY = 'INSERT INTO Memo(Picture) VALUES(?)';
var
  LTransaction: TDBXTransaction;
  LDBXCmd: TSQLQuery;
  LParam: TParam;
begin
  LTransaction := FDBCon.BeginTransaction;
  LDBXCmd := TSQLQuery.Create(FDBCon);
  try
    try
      LDBXCmd.SQLConnection := FDBCon;
      LDBXCmd.SQL.Text := QRY;
      LParam := LDBXCmd.Params.CreateParam(ftBlob, 'Picture', ptInput);
      LParam.AsBlob := GetFileAsBytesValue('d:\sample.bmp');
      LDBXCmd.ExecSQL;
    except
      on E: Exception do
        FDBCon.RollbackFreeAndNil(LTransaction);
    end;
    FDBCon.CommitFreeAndNil(LTransaction);
  finally
    LDBXCmd.Free;
  end;
end;

Answer 2

这很简单，就像：

var
  ms: TMemoryStream;  
  sq: TSQLQuery;  
begin  
  ms := TMemoryStream.Create;
  ms.LoadFromFile('C:\Pictures\l.jpg');
  if ms <> nil then
   begin
     sq := TSQLQuery.Create(nil);
     sq.SQLConnection := con1;
     sq.SQL.Text := 'update db1 set picture= :photo ;';
     sq.Params.ParseSQL(sq.SQL.Text, true);
     sq.Params.ParamByName('photo').LoadFromStream(ms, ftBlob);
     sq.ExecSQL();
   end;   

。

其中con1是一个TSQLConnection。