Read very large file in bytearray and upload to MSSQL

[Icemokka]

Hi,

I'm need to upload a big file ( 600Mb+ ) to a BLOB field in MSSQL
2005.

My code looks like this :

fs = New FileStream(sFilePath, FileMode.Open)
Dim ByteArray(fs.Length) As Byte
fs.Read(ByteArray, 0, fs.Length)
fs.Close()

The problem is when I dim the bytearray with my 600Mb file, the
bytearray becomes invalid.
I think the bytearray can only accept an integer as dimension.

Since MSSQL's limit is 2Gb, how should I then write a big file to
MSSQL.
Is there another method to get the whole bytearray of a file to put
into MSSQL?

Regards,
Sven Peeters

 

[Armin Zingler]

Hi,

I'm need to upload a big file ( 600Mb+ ) to a BLOB field in MSSQL
2005.

My code looks like this :

fs = New FileStream(sFilePath, FileMode.Open)
Dim ByteArray(fs.Length) As Byte
fs.Read(ByteArray, 0, fs.Length)
fs.Close()

As this example shows, it is strongly recommended to switch Option Strict
On. By disabling it, you are not pointed to the fact that a file can be
larger than 2GB. Check the file size before, and if it's not >2GB, you can
safely convert the Long value returend by fs.Length to an Integer.

The problem is when I dim the bytearray with my 600Mb file, the
bytearray becomes invalid.
I think the bytearray can only accept an integer as dimension.

600MB is within the range of an integer, so this is not the problem.
Integer.MaxValue is 2GB.

Be aware that the dim/redim statements expect the upper bound of the array,
so replace "fs.length" by "Cint(fs.length) - 1"

Since MSSQL's limit is 2Gb, how should I then write a big file to
MSSQL.
Is there another method to get the whole bytearray of a file to put
into MSSQL?

Is your current problem getting the byte array into the database or reading
the file into the byte array? If it's the former, have a look at the ADO.Net
group because it is not a VB.Net language related problem. I'm also not sure
whether your problem is to store a 600+ MB file (up to 2GB) into the
database or if you also want to store 2GB+ there. Storing a link to the file
was the only solution (AFAIK).


Armin

 

[Michel Posseth [MCP]]

Well i think it is hard to believe that you reach the uperlimit of
2,147,483,647. wich is the maximum size of an integer in .Net
wich in the case of a byte array is equivalant to 2047 + megabytes or 1.9
+ gigabytes


Michel

 

[Michel Posseth [MCP]]

see this example

http://support.microsoft.com/kb/308042

if it doesn`t work, you are probably hitting another limit ( system
resources , timeout ?? )

Another solution would be to save your file on a file server , and only
store the key and path to the file in the database
mostly SQL dba`s don`t like these hughe blob fields in there database .

HTH

Michel

 

[Icemokka]

Hi,

Thank your for your reply, here is my function. I've turned on Option
Strict and made the changement you proposed.
And suddenly all works perfect. Thank you very much ...

 

[Icemokka]

Your proposal ( option strict & Cint(lenght - 1 ) fixed my problem.
Now I have a second problem, there are 4 * 600Mb files ready to be put
in the database.
After the first file, I see that my app takes 600Mb of memory ( logic
offcourse ).
But I get an out of memory exception on the second file because the
bytearray has not yet released it's memory ( set it to nothing within
the function ).
How can I force that the garbage collector cleans up ( array's don't
have dispose or finally method ).

 

[Armin Zingler]

Your proposal ( option strict & Cint(lenght - 1 ) fixed my problem.
Now I have a second problem, there are 4 * 600Mb files ready to be
put in the database.
After the first file, I see that my app takes 600Mb of memory (
logic offcourse ).
But I get an out of memory exception on the second file because the
bytearray has not yet released it's memory ( set it to nothing
within the function ).

That's a good question. I'm afraid, I can't answer this. I would have
thought that GC will do it automatically. Do I understand it correctly that
you did set the reference to the array to Nothing /before/ creating the new
array?

I mean,

this
var = nothing
redim var(...)

is not the same as

redim var(...)

because in the 2nd case, first the new array is created before the last
reference to the old array has been cleared. So, the 1st version should be
preferred. Though, I don't know if it helps and if the next Redim will wait
until the GC will have destroyed the previous array.

How can I force that the garbage collector cleans up ( array's don't
have dispose or finally method ).

There's the GC.Collect method, but usually it shouldn't be called manually.
Maybe this is an exception. Let's wait for other answers.


Armin

 

[Guest]

Can't you just find the maximum size file, allocate the array to that size,
and reuse the array in a loop over all files?

 

[Icemokka]

That's an idea offcourse, but not very optimal.

Can't you just find the maximum size file, allocate the array to that size,
and reuse the array in a loop over all files?













- Tekst uit oorspronkelijk bericht weergeven -

 

[Icemokka]

Even better, the array is a locally defined variable of the function
that only handles 1 file.
So after every file dump on SQL, I set the array to nothing and then
it gets out of scope.
But the GC does not recycle the memory fast enough!

 

[Guest]

What part is less than optimal? At some point you need an array of n-bytes,
and this method just reuses it. When you are done with the loop, then you
release the memory.

 

[Icemokka]

Gonna pich this solution indeed ...

What part is less than optimal? At some point you need an arrayofn-bytes,
and this method just reuses it. When you are done with the loop, then you
release thememory.





- Tekst uit oorspronkelijk bericht weergeven -