473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Sum of Column of Blob type

I have a column in my table that is an Image (blob). The data stored
in this blob is basically pdf files. I need a query to determine the
total size of these blob's in the database. Here is what is
happening, if I run it as below I get 23736000 for a result.

select sum(datalength (cast(document AS binary(8000)))) from
plan_report

If I run it like this select sum(datalength (cast(document AS
varbinary(8000)))) from plan_report
I get 23736000 also as a result

If I run it like this select sum(datalength (cast(document AS
varbinary))) from plan_report
I get 89010 this as a result

If I run this: select datalength(document) from plan_report then I
get a result for each row and when I sum those rows I get 5584452854
this as a result.

I do not believe the first 3 queries are returning correct result sets
due to the limit on the fields (binary/varbinary) and since this is an
image field I cannot convert to anything else.....

I am out of ideas, and any help you can give me will be greatly
appreciated...

Apr 10 '07 #1
4 7241
You do not have to convert your BLOB column. DATALENGTH will return the
correct size of the BLOB column if you use it directly.

Something like this:

SELECT SUM(COALESCE(DATALENGTH(document), 0))
FROM plan_report

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Apr 10 '07 #2
Connie (cs*****@rwbaird.com) writes:
If I run it like this select sum(datalength (cast(document AS
varbinary))) from plan_report
I get 89010 this as a result
Because in this context the default is varbinary(30).
If I run this: select datalength(document) from plan_report then I
get a result for each row and when I sum those rows I get 5584452854
this as a result.
The obvious query would be

SELECT SUM(datalength(document)) FROM plan_report

you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

SELECT SUM(cast(datalength(document) as bigint)) FROM plan_report
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 10 '07 #3
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...
>
you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:

SELECT SUM(cast(datalength(document) as bigint)) FROM plan_report
Good catch Erland! I didn't see the big number and the overflow...

Plamen Ratchev
http://www.SQLStudio.com
Apr 10 '07 #4
On Apr 10, 4:46 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
"Erland Sommarskog" <esq...@sommarskog.sewrote in message

news:Xn*********************@127.0.0.1...
you don't say whether you tried this, but I can see that it will fail
with integer overflow. Howeever this should work:
SELECT SUM(cast(datalength(document) as bigint)) FROM plan_report

Good catch Erland! I didn't see the big number and the overflow...

Plamen Ratchevhttp://www.SQLStudio.com
Thanks everyone.......your all awesome, Yes Erland I did try the
first option, but I did not try with the bigint.....I was looking at
it too long and getting frustrated I guess, but thanks again everyone,

Apr 11 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Uwe Grauer | last post by:
Hi, i need to know the database column types returned by kinterbasdb. Implicit type conversion is i nice thing to have, but it hides the real types in the database. So how could i get this...
4
by: Peter Scott | last post by:
I created a table that has a column in that needs to contain a full Unix file path. Since 2048 was too long for a VARCHAR, I made it TEXT. I since populated the table. Now I want to make the...
0
by: brent | last post by:
Hi there... I have a stateless Session Bean issuing a select for a BLOB column. This bean is using a CachedRowSet. It seems to work for all other columns types other than a BLOB. Selecting a...
3
by: Carmine | last post by:
I have to add a date column to a db2/zos table with a blob column. Can I rename the blob table to old, create a new table with the date column, then insert/select the data from the old table? ...
2
by: deepgun74 | last post by:
I have a blob column that is 4MB in length. I need to change it to store a 3GIG binary. 1) What is the maximum size a BLOB column can support? 2) How do i alter the size of an existing BLOB...
2
by: Marcus | last post by:
Hello, I have read several online tutorials advocating storing a sha1() hashed password in a column of type BLOB. As far as I can tell, sha1() always returns a string of 40 characters,...
1
by: CodeMan007 | last post by:
Hi...i have a table with a certain column of type Varchar2 ...i have to change the type of this column to BLOB...and also preserve the data in the table.. . Please help.
1
by: shallowpool | last post by:
Can someone provide me the simple syntax necessary to insert or update to a row containing a single BLOB column, where the BLOB data will be obtained from a file? This is on a linux installation. ...
2
by: sjping | last post by:
This is what I did: >db2 alter table C1016781_T1 alter column itemContent set data type blob(2097152) and I got: DB21034E The command was processed as an SQL statement because it was not a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.