Board index » delphi » Error updating empty blob value "zero length binary field"

Error updating empty blob value "zero length binary field"

Hi all,

I'm using N4 sp6a, Delphi5, BDE 5.01 and Sybase Adaptive Server
Enterprise 11.9.2.

When updating a blob field with an empty value I get a syntax error from
Sybase complaining about "zero length binary fields". Because I'm using
a third party OO framework when interfacing with the database via BDE
I'm not 100% sure of what the actual SQL statement is but SQL Monitor
and the database trace log shows that the failing statement is something
like:

UPDATE my_table SET my_blobfield=0x WHERE ......

I'm pretty certain the value 0x above should have been 0x0 or NULL.

This problem has surfaced after upgrading BDE (among other things) from
the version provided with Delphi4 to the the version provided with
Delphi5.

Inorder to find out if this is problem with the new version of BDE
(configuration, bug or something else) I would like to know if anyone of
you has had any similar problems or if anyone of you might have a clue
on the cause of this.

Regards,

Pasi Moisander

 

Re:Error updating empty blob value "zero length binary field"


Hi Pasi,

On Tue, 25 Jul 2000 23:19:57 +0200, Pasi Moisander

Quote
<pasi.moisan...@home.se> wrote:

[Snip]
Quote
>In order to find out if this is problem with the new version of BDE
>(configuration, bug or something else) I would like to know if anyone of
>you has had any similar problems or if anyone of you might have a clue
>on the cause of this.

[Snip]
Yup - definitely had a similar problem when using IBM DB2 UDB (the
company that loves TLA's :). Blank string fields caused exactly the
same error. I mst admit because I was in a hurry I didn't think to
check anything behind the scenes (ooops..), I just remember bunging a
little extra code, something like this :

with UpdateBlob do
begin
  ParamByName('KeyVal').AsString := KeyEdit.Text;
  if BlobMemo.Lines.Count > 0 then
  begin
    ParamByName('BlobVal').Clear;
    ParamByName('BlobVal').Bound := True;
  end
  else
    ParamByName('BlobVal').AsMemo := BlobMemo.Text;
  ExecSQL;
end;

where UpdateBlob was a TQuery with something like :

UPDATE ATABLE SET AFIELD = :BlobVal WHERE AKEY = :KeyVal

I must admit I've only ever used DB2 with BDE 5.x, so I'm not sure if
this problem would manifest itself under prior versions. Come to think
of it I'm not even 100% sure that the above code by-passed the
problem, I can vaguely remember having to create a separate query
saying something like :

UPDATE ATABLE SET AFIELD = NULL WHERE AKEY = :KeyVal

But I can't be sure! I'll see if I can dig out the code from
somewhere...

HTH, Dave.
--
Dave Hughes
EMail : d...@waveform.f9.co.uk

Re:Error updating empty blob value "zero length binary field"


Hi Dave,

Thank you for the good input.

We haven't been able to find the actual problem, but your second
suggestion
provided a first workaround for us. A minor difference in our workaround
is
that because of the third party layer between us and the database we
have to
set non used blob values to dummy values. But this isn't a problem for
us.

Also the third party vendor has provided us with a new version of the
framework
that does a better job in only writing changed objects to the database.

So, the problem is not solved but it seems though we are up and running.

Regards,

Pasi        

Quote
Dave Hughes wrote:

> Hi Pasi,

> On Tue, 25 Jul 2000 23:19:57 +0200, Pasi Moisander
> <pasi.moisan...@home.se> wrote:

> [Snip]
> >In order to find out if this is problem with the new version of BDE
> >(configuration, bug or something else) I would like to know if anyone of
> >you has had any similar problems or if anyone of you might have a clue
> >on the cause of this.
> [Snip]

> Yup - definitely had a similar problem when using IBM DB2 UDB (the
> company that loves TLA's :). Blank string fields caused exactly the
> same error. I mst admit because I was in a hurry I didn't think to
> check anything behind the scenes (ooops..), I just remember bunging a
> little extra code, something like this :

> with UpdateBlob do
> begin
>   ParamByName('KeyVal').AsString := KeyEdit.Text;
>   if BlobMemo.Lines.Count > 0 then
>   begin
>     ParamByName('BlobVal').Clear;
>     ParamByName('BlobVal').Bound := True;
>   end
>   else
>     ParamByName('BlobVal').AsMemo := BlobMemo.Text;
>   ExecSQL;
> end;

> where UpdateBlob was a TQuery with something like :

> UPDATE ATABLE SET AFIELD = :BlobVal WHERE AKEY = :KeyVal

> I must admit I've only ever used DB2 with BDE 5.x, so I'm not sure if
> this problem would manifest itself under prior versions. Come to think
> of it I'm not even 100% sure that the above code by-passed the
> problem, I can vaguely remember having to create a separate query
> saying something like :

> UPDATE ATABLE SET AFIELD = NULL WHERE AKEY = :KeyVal

> But I can't be sure! I'll see if I can dig out the code from
> somewhere...

> HTH, Dave.
> --
> Dave Hughes
> EMail : d...@waveform.f9.co.uk

--
                      ''~``
                     ( o o )
+---------------.oooO--(_)--Oooo.----------------------------+
| Pasi Moisander        | E-Mail: Pasi.Moisan...@carmenta.se |
| Carmenta AB           | Voice :+46-(31)7755721             |
| Box 31121             | Fax   :+46-(31)246379              |
| 40032 G?teborg  .oooO |                                    |
| SWEDEN          (   ) | Oooo.                              |
+------------------\ (----(   )------------------------------+
                    \_)    ) /
                          (_/

Other Threads