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]

"Icemokka" <ic******@gmail.comschrieb

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

"Icemokka" <ic******@gmail.comschreef in bericht
news:9b**********************************@n20g2000 hsh.googlegroups.com...

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

[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" <ic******@gmail.comschreef in bericht
news:9b**********************************@n20g2000 hsh.googlegroups.com...

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

[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 ...

On 18 nov, 11:53, "Armin Zingler" <[e-mail]:

"Icemokka" <[e-mail]

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

[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 ).

On 18 nov, 11:53, "Armin Zingler" <[e-mail]:

"Icemokka" <[e-mail]

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

[Armin Zingler]

"Icemokka" <ic******@gmail.comschrieb

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

[=?Utf-8?B?RmFtaWx5IFRyZWUgTWlrZQ==?=]

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?

"Armin Zingler" wrote:

"Icemokka" <ic******@gmail.comschrieb

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

[Icemokka]

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

On 19 nov, 13:54, Family Tree Mike
<[e-mail]:

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?

"Armin Zingler" wrote:

"Icemokka" <[e-mail]

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- Tekst uit oorspronkelijk bericht niet weergeven -

- 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!

On 19 nov, 13:25, "Armin Zingler" <[e-mail]:

"Icemokka" <[e-mail]

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

[=?Utf-8?B?RmFtaWx5IFRyZWUgTWlrZQ==?=]

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" wrote:

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

On 19 nov, 13:54, Family Tree Mike
<[e-mail]:

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?

"Armin Zingler" wrote:

"Icemokka" <[e-mail]
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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

[Icemokka]

Gonna pich this solution indeed ...

On 20 nov, 17:45, Family Tree Mike
<[e-mail]:

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.

"Icemokka" wrote:

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

On 19 nov, 13:54, Family Tree Mike
<[e-mail]:

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?

"Armin Zingler" wrote:
"Icemokka" <[e-mail]
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 600Mbofmemory(
logic offcourse ).
But I get anoutofmemoryexceptionon the second file because the
bytearray has not yet released it'smemory( 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 anexception. Let's wait for other answers.

Armin- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -