[yunqa.de] Re: BLOB field is incorrectly being written into DB and truncated by BindDataSet - FIXED.
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Wed, 18 Feb 2009 12:56:35 +0100
Jone Word wrote:
>There's an incorrect processing of BLOB field with BindDataSet, which results
>in incorrect posting of this field into DB using TDataSetProvider &
>TDISQLite** objects. This happens when BLOB data contains null (#0)
>characters. BLOB is being
>treated as a string!
You are right, I did think of the implicit string conversions in my code.
Unfortunately, the BLOB must be treated as a string, because this is what
TBlobField.Old/NewValue returns. However, I did not pass it to the DB on a byte
level and the #0 escaped my test suite. Many thanks for the catch!
>I spent hours and hours finding why my BLOB object is always being truncated.
I had also spend hours on the blob issue, and looked at it again just now. I
still believe that there is an intricate problem in how TClientDataSet passes
BLOBs to PSUpdateRecord (see source code comments). However, I took up your
suggestion and modified the BindDataSet procedure using a dirty TDataSet
cracker trick (below).
There is also an acknowledged bug in Provider.pas: D2005, D2006, and D2007
execute PSUpdateRecord twice. This results in double database inserts. There is
no workaround. See http://qc.codegear.com/wc/qcmain.aspx?d=26092 for disucsion
and fix.
I would be glad if you could test my new code and report if you meet any
problems. If you do, I would also appreciate if you could send a test case
which escaped my notice.
I'd like to include the changes in the next version of DISQLite3, which I hope
to release in just a few days. And yes: It will also include the new
life-backup API!
Ralf
//------------------------------------------------------------------------------
type
TDataSetCracker = class(TDataSet);
{ Binds TDataSet to a prepared statement. Only available fields will
be bound - unavailable parameters stay untouched. }
procedure BindDataSet(const AStmt: sqlite3_stmt; const ADataSet: TDataSet);
var
f: TField;
i: Integer;
Old: Boolean;
p: PAnsiChar;
SaveState: TDataSetState;
Value: Variant;
ValueBlob: RawByteString;
begin
for i := 1 to sqlite3_bind_parameter_count(AStmt) do
begin
p := sqlite3_bind_parameter_name(AStmt, i);
// Only named parameters starting with ':'.
if Assigned(p) and (p^ = ':') then
begin
Inc(p);
Old := IsOld(p);
if Old then
Inc(p, 4);
f := ADataSet.FindField(sqlite3_decode_utf8(p));
if Assigned(f) then
if f.IsBlob then
begin
{ Special blob handling necessary because all Values returned
from TBlobField are Variants of type AnsiString.
Additional TBlobField problem: There seems to be no way to
resolve modifications to blob fields reliably.
TBlobField.OldValue always returns an empty variant,
TBlobField.NewValue an empty string for unmodified blobs.
In addition, D2005 and later truncate strings with #0 chars
during string -> variant conversation.
We looked hard for a solution, but it seems best not to use
TBlobFields in WHERE clauses with PSUpdateRecord. }
if Old then
SaveState :=
TDataSetCracker(ADataSet).SetTempState(dsOldValue)
else
SaveState :=
TDataSetCracker(ADataSet).SetTempState(dsNewValue);
try
ValueBlob := f.AsString;
finally
TDataSetCracker(ADataSet).restorestate(SaveState);
end;
sqlite3_check_stmt(sqlite3_bind_blob(
AStmt, i,
PAnsiChar(ValueBlob), Length(ValueBlob),
sqlite3_Destroy_AnsiString), AStmt);
Pointer(ValueBlob) := nil;
end
else
begin
if Old then
Value := f.OldValue
else
begin
Value := f.NewValue;
if VarIsEmpty(Value) then
Value := f.OldValue;
end;
if not VarIsEmpty(Value) then
sqlite3_check_stmt(sqlite3_bind_variant(
AStmt, i, Value), AStmt);
end;
end;
end;
end;
//------------------------------------------------------------------------------
> So I've corrected the code so now it works OK. The following is what I did:
>
>OLD code (DISQLite3DataSet.pas):
>--------------------------
> if f.IsBlob then
> sqlite3_check_stmt(sqlite3_bind_str(AStmt, i, Utf8String(Value)), AStmt);
>--------------------------
>
>NEW code:
>-------------------------
> SValue: String;
>....
> if f.IsBlob then
> begin
> if Old then
> SValue := tblobfield(f).OldValue else
> SValue := tblobfield(f).NewValue;
> sqlite3_check_stmt(sqlite3_bind_blob(AStmt, i,
> PAnsiChar(svalue), tblobfield(f).blobsize, SQLITE_TRANSIENT), AStmt);
> end
>----------------------------
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
