How to store many large files in the database?

[Christian Gruber <ch...@web.de>]

Hi,
the maximum size of a table is the maximum file size of the underlying 
operating system (e.g.: 2 GB).
For data with a size of 10 MB I would reach the limit with about 200 
entries.
The data once in the table needs only be read (attachments of messages). 
Perhaps the new table function can be used to show the content of the 
filesystem as a table, when the attachments are stored outside of the 
database.
Is there a way to distribute a table over more than one file or another 
solution?

Any solution is appreciated!
Thanks
Christian

Re: How to store many large files in the database?

[Rick Hillegas <Ri...@Sun.COM>]

Hi Christian,

Derby does not support partitioned tables. I think you will need to 
write your own logic to divide the attachments among many buckets--the 
buckets could either be a growing number of tables inside the database 
or files outside the database. Regardless of how you implement the 
buckets, table functions may be able to hide some of the complexity when 
you SELECT the attachments. You may be able to use database procedures 
to hide some of the complexity of INSERTing into the buckets.

Hope this helps,
-Rick

Christian Gruber wrote:
> Hi,
> the maximum size of a table is the maximum file size of the underlying 
> operating system (e.g.: 2 GB).
> For data with a size of 10 MB I would reach the limit with about 200 
> entries.
> The data once in the table needs only be read (attachments of 
> messages). Perhaps the new table function can be used to show the 
> content of the filesystem as a table, when the attachments are stored 
> outside of the database.
> Is there a way to distribute a table over more than one file or 
> another solution?
>
> Any solution is appreciated!
> Thanks
> Christian
>
>

Re: How to store many large files in the database?

[Brad Berens <th...@gmail.com>]

Not really specific to derby but I typically find it easier to store the
file name as a string in the DB and use code to find the file on the file
system.  Dunno if that fits your requirements.  Good luck.

On Thu, May 22, 2008 at 9:31 PM, Christian Gruber <ch...@web.de>
wrote:

> Hi,
> the maximum size of a table is the maximum file size of the underlying
> operating system (e.g.: 2 GB).
> For data with a size of 10 MB I would reach the limit with about 200
> entries.
> The data once in the table needs only be read (attachments of messages).
> Perhaps the new table function can be used to show the content of the
> filesystem as a table, when the attachments are stored outside of the
> database.
> Is there a way to distribute a table over more than one file or another
> solution?
>
> Any solution is appreciated!
> Thanks
> Christian
>
>
>

Re: How to store many large files in the database?

[Rick Hillegas <Ri...@Sun.COM>]

Christian Gruber wrote:
> Thanks for all the replys!
>  
> I know it is not the best solution to store large files in a database, 
> but it was the easiest in an ejb3 environment.
> My knowledge of the maximum file size seems to be a bit outdated. What 
> I have found now is a sizelimit for Fat32 of 4 GB, NTFS has no limit. 
> I hope no one has used Fat32 on his WindowsXP installation and my 
> fears are causeless.
>  
> None the less does anybody know of a working example of a table 
> function that returns a part of the filesystem as a ResultSet?
>  
> Thanks again!
> Christian
Hi Christian,

If what you need is an example of a table function which wraps a flat 
file, then please look in the demo directory or the Derby source code 
for org.apache.derbyDemo.vtis.example.PropertyFileVTI. That's a table 
function which presents a property file as a 2 column table. In the same 
package, there are some other table functions which wrap log files and 
reports.

Hope this helps,
-Rick

RE: How to store many large files in the database?

[Michael Segel <ms...@segel.com>]

You're not returning a "part of the filesystem" but just a string that
contains the path to the file object.

 

 

  _____  

From: christian.gruber@web.de [mailto:christian.gruber@web.de] 
Sent: Friday, May 23, 2008 4:29 PM
To: Derby Discussion
Subject: Re: How to store many large files in the database?

 

Thanks for all the replys!

 

I know it is not the best solution to store large files in a database, but
it was the easiest in an ejb3 environment.

My knowledge of the maximum file size seems to be a bit outdated. What I
have found now is a sizelimit for Fat32 of 4 GB, NTFS has no limit. I hope
no one has used Fat32 on his WindowsXP installation and my fears are
causeless.

 

None the less does anybody know of a working example of a table function
that returns a part of the filesystem as a ResultSet?

 

Thanks again!

Christian

Re: How to store many large files in the database?

[Christian Gruber <ch...@web.de>]

Thanks for all the replys!

I know it is not the best solution to store large files in a database, but it was the easiest in an ejb3 environment.
My knowledge of the maximum file size seems to be a bit outdated. What I have found now is a sizelimit for Fat32 of 4 GB, NTFS has no limit. I hope no one has used Fat32 on his WindowsXP installation and my fears are causeless.

None the less does anybody know of a working example of a table function that returns a part of the filesystem as a ResultSet?

Thanks again!
Christian

Re: How to store many large files in the database?

[Donald McLean <dm...@gmail.com>]

I believe that FAT32 drives have a 4GB limit. I bought an extra external
hardrive for backups not realizing that it was FAT32.

On Fri, May 23, 2008 at 12:34 PM, Six Fried Rice <te...@sixfriedrice.com>
wrote:

> On May 22, 2008, at 6:31 PM, Christian Gruber wrote:
>
>  the maximum size of a table is the maximum file size of the underlying
>> operating system (e.g.: 2 GB).
>>
>
> I'm curious where the 2GB limit is coming from. Are you certain your max
> file size is 2GB in your environment? For reference, here are max file sizes
> for common filesystems:
>
> NTFS: 16 terabytes
> HFS+ (Mac OS X): 8 exabytes (8 million terabytes)
> EXT3: 16GB - 2TB depending on block size
>
> Is the 2GB a Java limitation? Or perhaps related to some filesystem APIs in
> use?
>

Re: How to store many large files in the database?

[Six Fried Rice <te...@sixfriedrice.com>]

On May 22, 2008, at 6:31 PM, Christian Gruber wrote:

> the maximum size of a table is the maximum file size of the  
> underlying operating system (e.g.: 2 GB).

I'm curious where the 2GB limit is coming from. Are you certain your  
max file size is 2GB in your environment? For reference, here are max  
file sizes for common filesystems:

NTFS: 16 terabytes
HFS+ (Mac OS X): 8 exabytes (8 million terabytes)
EXT3: 16GB - 2TB depending on block size

Is the 2GB a Java limitation? Or perhaps related to some filesystem  
APIs in use?

Not to say there aren't lots of other possibilities, but I just  
thought it might be worth a look.

Geoff