Board index » delphi » "Invalid BLOB Length" exception

"Invalid BLOB Length" exception

Hi All,

My setup...

* Delphi 6 SP 2 (Enterprise Edition)
* SQLServer 2000

I am using the BDE in order to access the database and I am attempting to
store the BLOB into an image field.
An "Invalid BLOB Length" exception is raised when I attempt to save a BLOB
that is greater then 32KB in size. I found references to similiar problems
which indicated that the problem could be resolved by increasing the BLOB
SIZE parameter value.
I have attempted this resolution but with little success. I increased the
size from the default value of 32 to 64 then 128 and finally 512 but I still
get the "Invalid BLOB Length" error on the save (The blob that I am saving
is about 40KB in size).

Am I missing something or is there some other resolution that I should try.

Here's a dump of all my database parameters...

DATABASE NAME=
ODBC DSN=TESTDSN
OPEN MODE=READ/WRITE
BATCH COUNT=200
LANGDRIVER=
MAX ROWS=-1
SCHEMA CACHE DIR=
SCHEMA CACHE SIZE=8
SCHEMA CACHE TIME=-1
SQLPASSTHRU MODE=SHARED AUTOCOMMIT
SQLQRYMODE=
ENABLE SCHEMA CACHE=FALSE
ENABLE BCD=FALSE
ROWSET SIZE=20
BLOBS TO CACHE=4000
BLOB SIZE=512

Thanks in advance for your help.

Lou

 

Re:"Invalid BLOB Length" exception


Having encountered the exact same problem I set
BLOBSIZE  = 3000
Make sure to set BLOBSTOCACHE = -1 // Cache no blobs

That should do it.

also if you use ADO the problem does not exist.

Re:"Invalid BLOB Length" exception


Hi Greg,

Thanks for answering my question.
I followed your advice, but I was still not able to save a BLOB without
getting the invalid blob length error.

Here's a dump of my database parameters:

DATABASE NAME=
ODBC DSN=TESTDSN
OPEN MODE=READ/WRITE
BATCH COUNT=200
LANGDRIVER=
MAX ROWS=-1
SCHEMA CACHE DIR=
SCHEMA CACHE SIZE=8
SCHEMA CACHE TIME=-1
SQLPASSTHRU MODE=SHARED AUTOCOMMIT
SQLQRYMODE=
ENABLE SCHEMA CACHE=FALSE
ENABLE BCD=FALSE
ROWSET SIZE=20
BLOBS TO CACHE=-1
BLOB SIZE=3000

Let me know if there is anything else that I should do to address this
problem.

Thanks,
Lou

Quote
"Greg Rowland" <supp...@JobTrax.com> wrote in message

news:3eb180e4$1@newsgroups.borland.com...
Quote
> Having encountered the exact same problem I set
> BLOBSIZE  = 3000
> Make sure to set BLOBSTOCACHE = -1 // Cache no blobs

> That should do it.

> also if you use ADO the problem does not exist.

Re:"Invalid BLOB Length" exception


In article <3eb0263...@newsgroups.borland.com>, l...@marketingpilot.com
says...

Quote
> Hi All,

> My setup...

> * Delphi 6 SP 2 (Enterprise Edition)
> * SQLServer 2000

What Greg suggested is absolutely right, I had the same experience.
Create a small demo program to update the blob image field.

-------------------------------
Paul Lambadaris
Delta Singular S.A.
mailto : p...@NOSPAMsingular.gr
www    : http://www.singular.gr

Re:"Invalid BLOB Length" exception


Hi Paul,

I created a small single form project in order to test the database
configuration / blob length limitation.
The logic is in the body of this reply and it should be easy to cut/paste
and get running.

Here it is...

** TDatabase component parameter settings

DATABASE NAME=
ODBC DSN=Asset
OPEN MODE=READ/WRITE
BATCH COUNT=200
LANGDRIVER=
MAX ROWS=-1
SCHEMA CACHE DIR=
SCHEMA CACHE SIZE=8
SCHEMA CACHE TIME=-1
SQLPASSTHRU MODE=SHARED AUTOCOMMIT
SQLQRYMODE=
ENABLE SCHEMA CACHE=FALSE
ENABLE BCD=FALSE
ROWSET SIZE=20
BLOBS TO CACHE=-1
BLOB SIZE=3000
PASSWORD=
USER NAME=

** Logic from an OnClick event for a button

procedure TForm1.Button1Click(Sender: TObject);
// Query1 contains the following SQL - "Select * from Asset"
// The button just saves two JPGs as BLOBs/Images
begin
  Query1.Open;
  Query1.Insert;

  Query1.FieldByName('OID').AsInteger := 1;
  Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Sample.jpg');

  // Save 1 - This blob is about 10KB in size which saves successfully
  Query1.Post;

  Query1.Insert;

  Query1.FieldByName('OID').AsInteger := 2;
  Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Clown.jpg');

  // Save 2 - This blob is about 35KB in size which caused the "Invalid BLOB
Length" exception
  Query1.Post;
end;

Here's the DLL to create the table...

CREATE TABLE dbo.TEST (
   OID INT NOT NULL,
   ASSET IMAGE NOT NULL)
GO
ALTER TABLE dbo.TEST ADD PRIMARY KEY (OID)
GO

I ran this sample a number of times, played with the database parameter
settings and shutdown/restarted Delphi but still was not able to get around
the 32KB limit.
I don't doubt that your code works, but I have not been able to get it to
work by just adjusting the BLOB SIZE and BLOBS TO CACHE parameters.
Is it possible that you are forgetting some other detail?

Thanks,
Lou

Quote
"Paul Lambadaris" <p...@NOSPAMsingular.gr> wrote in message

news:MPG.1921963a8bc31c339897d7@newsgroups.borland.com...
Quote
> In article <3eb0263...@newsgroups.borland.com>, l...@marketingpilot.com
> says...
> > Hi All,

> > My setup...

> > * Delphi 6 SP 2 (Enterprise Edition)
> > * SQLServer 2000

> What Greg suggested is absolutely right, I had the same experience.
> Create a small demo program to update the blob image field.

> -------------------------------
> Paul Lambadaris
> Delta Singular S.A.
> mailto : p...@NOSPAMsingular.gr
> www    : http://www.singular.gr

Re:"Invalid BLOB Length" exception


I had a problem similar to that one and what I did was pretty strange, but
it work
I created invisible TDBimage control assigned with my dataset
Instead of writing to the DB field I was chaging Picture property value for
the corresponding record and then just post....
Now I am storing images up to 2Mb (I do not need more) and it works fine.

Environment D6SP2 Enterprise, MS SQL 2000

Hope it will help

Thank You and have a good day.

____________________________

Alexander Artsikhovsky

Quote
"Lou Immendorf" <l...@marketingpilot.com> wrote in message

news:3ebaa3d5$1@newsgroups.borland.com...
Quote
> Hi Paul,

> I created a small single form project in order to test the database
> configuration / blob length limitation.
> The logic is in the body of this reply and it should be easy to cut/paste
> and get running.

> Here it is...

> ** TDatabase component parameter settings

> DATABASE NAME=
> ODBC DSN=Asset
> OPEN MODE=READ/WRITE
> BATCH COUNT=200
> LANGDRIVER=
> MAX ROWS=-1
> SCHEMA CACHE DIR=
> SCHEMA CACHE SIZE=8
> SCHEMA CACHE TIME=-1
> SQLPASSTHRU MODE=SHARED AUTOCOMMIT
> SQLQRYMODE=
> ENABLE SCHEMA CACHE=FALSE
> ENABLE BCD=FALSE
> ROWSET SIZE=20
> BLOBS TO CACHE=-1
> BLOB SIZE=3000
> PASSWORD=
> USER NAME=

> ** Logic from an OnClick event for a button

> procedure TForm1.Button1Click(Sender: TObject);
> // Query1 contains the following SQL - "Select * from Asset"
> // The button just saves two JPGs as BLOBs/Images
> begin
>   Query1.Open;
>   Query1.Insert;

>   Query1.FieldByName('OID').AsInteger := 1;
>   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Sample.jpg');

>   // Save 1 - This blob is about 10KB in size which saves successfully
>   Query1.Post;

>   Query1.Insert;

>   Query1.FieldByName('OID').AsInteger := 2;
>   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Clown.jpg');

>   // Save 2 - This blob is about 35KB in size which caused the "Invalid
BLOB
> Length" exception
>   Query1.Post;
> end;

> Here's the DLL to create the table...

> CREATE TABLE dbo.TEST (
>    OID INT NOT NULL,
>    ASSET IMAGE NOT NULL)
> GO
> ALTER TABLE dbo.TEST ADD PRIMARY KEY (OID)
> GO

> I ran this sample a number of times, played with the database parameter
> settings and shutdown/restarted Delphi but still was not able to get
around
> the 32KB limit.
> I don't doubt that your code works, but I have not been able to get it to
> work by just adjusting the BLOB SIZE and BLOBS TO CACHE parameters.
> Is it possible that you are forgetting some other detail?

> Thanks,
> Lou

> "Paul Lambadaris" <p...@NOSPAMsingular.gr> wrote in message
> news:MPG.1921963a8bc31c339897d7@newsgroups.borland.com...
> > In article <3eb0263...@newsgroups.borland.com>, l...@marketingpilot.com
> > says...
> > > Hi All,

> > > My setup...

> > > * Delphi 6 SP 2 (Enterprise Edition)
> > > * SQLServer 2000

> > What Greg suggested is absolutely right, I had the same experience.
> > Create a small demo program to update the blob image field.

> > -------------------------------
> > Paul Lambadaris
> > Delta Singular S.A.
> > mailto : p...@NOSPAMsingular.gr
> > www    : http://www.singular.gr

Re:"Invalid BLOB Length" exception


In article <3ebaa3d...@newsgroups.borland.com>, l...@marketingpilot.com
says...

Quote
> ** TDatabase component parameter settings

> DATABASE NAME=
> ODBC DSN=Asset
> OPEN MODE=READ/WRITE
> BATCH COUNT=200
> LANGDRIVER=
> MAX ROWS=-1
> SCHEMA CACHE DIR=
> SCHEMA CACHE SIZE=8
> SCHEMA CACHE TIME=-1
> SQLPASSTHRU MODE=SHARED AUTOCOMMIT
> SQLQRYMODE=
> ENABLE SCHEMA CACHE=FALSE
> ENABLE BCD=FALSE
> ROWSET SIZE=20
> BLOBS TO CACHE=-1
> BLOB SIZE=3000
> PASSWORD=
> USER NAME=

> procedure TForm1.Button1Click(Sender: TObject);
> begin
>   Query1.Open;
>   Query1.Insert;

>   Query1.FieldByName('OID').AsInteger := 1;
>   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Sample.jpg');

>   // Save 1 - This blob is about 10KB in size which saves successfully
>   Query1.Post;

>   Query1.Insert;

>   Query1.FieldByName('OID').AsInteger := 2;
>   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Clown.jpg');

From what I can see, you are using an ODBC connection. I was talking
about BDE connection. The error happened to me also with the ODBC
connection but I finally managed to run it successfully under ODBC also.
What I found at first time was that after defining the ODBC dsn, going
back to Delphi, put a database component on the demo form, double click
it, choose the dsn name, click to "Defaults" and the "BLOB SIZE"
parameter was missing from the list.
Went back to BDE administrator, click to the dsn name and BLOB SIZE
parameter was there (be sure to click to Virtual view in BDE admin
Options in order to seethe dsn name in the databases list).
After changing the BLOB SIZE parameter to let's say 1024, I went back to
Delphi again, click Defaults to the database component and the BLOB SIZE
parameter was in the list. If I run the demo now everything is OK.

-------------------------------
Paul Lambadaris
Delta Singular S.A.
mailto : p...@NOSPAMsingular.gr
www    : http://www.singular.gr

Re:"Invalid BLOB Length" exception


Hi Paul,

I followed your advice and that addressed the problem.
Thanks alot for helping me out.

Have fun,
Lou

Quote
"Paul Lambadaris" <p...@NOSPAMsingular.gr> wrote in message

news:MPG.1925b655e574c2d19897d9@newsgroups.borland.com...
Quote
> In article <3ebaa3d...@newsgroups.borland.com>, l...@marketingpilot.com
> says...
> > ** TDatabase component parameter settings

> > DATABASE NAME=
> > ODBC DSN=Asset
> > OPEN MODE=READ/WRITE
> > BATCH COUNT=200
> > LANGDRIVER=
> > MAX ROWS=-1
> > SCHEMA CACHE DIR=
> > SCHEMA CACHE SIZE=8
> > SCHEMA CACHE TIME=-1
> > SQLPASSTHRU MODE=SHARED AUTOCOMMIT
> > SQLQRYMODE=
> > ENABLE SCHEMA CACHE=FALSE
> > ENABLE BCD=FALSE
> > ROWSET SIZE=20
> > BLOBS TO CACHE=-1
> > BLOB SIZE=3000
> > PASSWORD=
> > USER NAME=

> > procedure TForm1.Button1Click(Sender: TObject);
> > begin
> >   Query1.Open;
> >   Query1.Insert;

> >   Query1.FieldByName('OID').AsInteger := 1;
> >   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Sample.jpg');

> >   // Save 1 - This blob is about 10KB in size which saves successfully
> >   Query1.Post;

> >   Query1.Insert;

> >   Query1.FieldByName('OID').AsInteger := 2;
> >   Query1Asset.LoadFromFile('C:\MarketingPilot\BLOBs\Clown.jpg');

> From what I can see, you are using an ODBC connection. I was talking
> about BDE connection. The error happened to me also with the ODBC
> connection but I finally managed to run it successfully under ODBC also.
> What I found at first time was that after defining the ODBC dsn, going
> back to Delphi, put a database component on the demo form, double click
> it, choose the dsn name, click to "Defaults" and the "BLOB SIZE"
> parameter was missing from the list.
> Went back to BDE administrator, click to the dsn name and BLOB SIZE
> parameter was there (be sure to click to Virtual view in BDE admin
> Options in order to seethe dsn name in the databases list).
> After changing the BLOB SIZE parameter to let's say 1024, I went back to
> Delphi again, click Defaults to the database component and the BLOB SIZE
> parameter was in the list. If I run the demo now everything is OK.

> -------------------------------
> Paul Lambadaris
> Delta Singular S.A.
> mailto : p...@NOSPAMsingular.gr
> www    : http://www.singular.gr

Re:"Invalid BLOB Length" exception


In article <3ebbce7...@newsgroups.borland.com>, l...@marketingpilot.com
says...
Quote
> Hi Paul,

> I followed your advice and that addressed the problem.
> Thanks alot for helping me out.

Very glad to see this.

-------------------------------
Paul Lambadaris
Delta Singular S.A.
mailto : p...@NOSPAMsingular.gr
www    : http://www.singular.gr

Other Threads