[yunqa.de] Re: BLOB field is incorrectly being written into DB and truncated by BindDataSet - FIXED.
- From: Jone Word <corabelster@xxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 18 Jun 2009 23:42:59 +0400
Hi again
I'm writing concerning this old bug with BLOB field that was being
incorrectly written
into database. I'm sorry to report that the new, (fixed by Ralf)
BindDataSet function is
causing a new error. My solution for fixing this problem doesn't cause
this type of an error.
I can't tell why, because I still don't have enough time.
The error is appearing when you have a blob field in the table
together with a field that
is not physically presented in DB, but rather it's generated dynamically
(something like WHEN ... THEN ....clause). So when this field is being post into
the table by ApplyUpdates - the "no such column" error is being fired.
It's only being
fired in the newer versions of BindDataSet that deal with BLOB problem.
Thank you!
2009/2/18, Delphi Inspiration <delphi@xxxxxxxx>:
> 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
>
>
>
>
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
