[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: Wed, 18 Feb 2009 21:51:11 +0300
Great, Ralf!
Thank you!
Yeah, double PSUpdate was a great problem. With DISQLite I use a workaround,
like this:
.ModifySQL := 'insert or update TABLE ...." which helps me to eliminate the
"Key
violation" problems.
I'll try to test your code in a few days. Currently my workaround works ok
in my case, so
I can make a temporary build of my project. Then I'll try to understand and
test your code!
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
>
>
>
>
Other related posts:
