Uploading Binary Data to Database (mySql or SQL Server)

[BlackRed]

Hi,

I'm trying to upload a PDF and store it in a database. At the moment we're using mySql but we have plans to change to SQL in the future.


I've currently got a function that takes the Binary data as a Byte array, but at the moment when it inserts it it inserts the string "System.Byte[]" in binary format!


What do I do with the byte array to correctly save the PDF data?

This is my function, it's simple enough:



1 Public Function File_Upload(ByVal DocumentID As Integer, ByVal Data As String, ByVal LiveFrom As DateTime) As Boolean
2
3 Dim myConnection As MySqlConnection
4 Dim myCommand As MySqlCommand
5
6 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings.Item("MySql").ConnectionString)
7
8 Dim strSQL As New System.Text.StringBuilder
9 strSQL.Append("INSERT INTO files (Document_ID, pdf_data, version, live_from) VALUES (")
10 strSQL.Append(DocumentID)
11 strSQL.Append(",'")
12 strSQL.Append(Data)
13 strSQL.Append("', ")
14 strSQL.Append(GetVersionNumber(DocumentID))
15 strSQL.Append(", '")
16 strSQL.Append(LiveFrom.Year & "-" & LiveFrom.Month & "-" & LiveFrom.Day & " 00:00:00")
17 strSQL.Append("')")
18
19 myConnection.Open()
20 myCommand = New MySqlCommand(strSQL.ToString, myConnection)
21
22 Return myCommand.ExecuteNonQuery > 0
23
24 End Function

I call it like...

wsDocument.File_Upload(Request.QueryString("DocumentID"), fuFile.FileBytes, GetLiveFrom())

"fuFile" is the built in .NET 2 file upload control.

 

[NoCoolHandle]

You need to send it a byte array....

e.g. for a proc that looks like
Create Proc SomeProc
@int int,
@filedata image
as

the vbcode would look like..

Private Function UseSomeProc (byVal filedata as byte(), byVal int as int32) as SqlClient.SqlDataReader
Dim con as New SqlClient.SqlConnection("server=localhost;database=Northwind;trusted_connection=yes")
Dim cmd as New SqlClient.SqlCommand("SomeProc",con)
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim dr As  sqlClient.sqlDataReader
With cmd
.CommandType = CommandType.StoredProcedure
With .Parameters
.Add("@filedata", SqlDbType.image, 16).Value = filedata
.Add("@int", SqlDbType.int, 4).Value = int
End With
con.Open()
try
dr  = cmd.ExecuteReader(CommandBehavior.CloseConnection)

 

[PGoRule]

May be this article will help you...
Saving and Retrieving Binary Files With SQL Server

Sharing the best from my side...

--Prashant--

 

[BlackRed]

Sorry there's an error in my initial post, where it says "ByVal Data As String" it is actually "ByVal Data As Byte()".

So I am passing the data through as a byte array, but it's inserting it as a string.

I'm not using SQL Server either, it's MySql at the moment, and does it matter that the data is a PDF and not an image?

 

[NoCoolHandle]

Does SQL Express support storedprocs?

Use parameters rather than an insert statement.


Rob

 

[chrissie1]

Blackred, I don't know if you noticed but nocoolhandle uses parameters to save the data wich you don't.

No it doesn't matter if it 's a pdf/image/anything else.

Did you actually try his example?

And if you did a little search on this site you would have found millions of examples.

But thank you anyway.

Christiaan Baes
Belgium

"My new site" - Me

 

[BlackRed]

Thanks for patronising me chrissie1, much appreciated.

Actually i did search the site and didn't find anything - if you know where I can find an example to insert binary data from .NET to MySql then point me in the direction.

I can't use stored procedures because the version of mysql I have to use doesn't support stored procedures, which is why I'm having problems with it. So in answer to your question, no I didn't try his example because it won't work for me.

 

[chrissie1]

The parameters used have nothing to do with stored procedures. They are what the command object uses. It doesn't need a stored procedure to work. it will work with commandtype.commandtext just as good.

Christiaan Baes
Belgium

"My new site" - Me

 

[NoCoolHandle]

Blackred..

My guess is you are using MYSQL because it is free..

So is SQL Express (MS) and it has heaps of functionality built in!

Get rid of the MySQL and you will be much happier..
(did i mention FREE!)

Rob

 

[BlackRed]

by the way, thanks NoCoolHandle and PGoRule for the Sql examples, but I'm not sure when we'll be converting to that.

 

[BlackRed]

Rob,

If it was my choice I'd be using Sql Server 2005

The site will be hosted on the clients network and they already have sites using mysql 3 so they're a bit nervous about upgrading just now.

I did request that they run MySql 5 alongside MySql 3 but this would involve their IT dept doing work, and I think they're also afraid of that!

 

[NoCoolHandle]

Client should be a 4 letter word.. If it wasn't for the fact they put food on our tables.......

 

[wangdong]

I did the same thing before, and it worked. What's the field type that you are using to store your PDF data?



Chinese Java Faq Forum

http://www.4ugo.com/bbs/index.htm

 

[BlackRed]

OK, I managed to persuade the client to switch over to SQL Server 2000 now, so while they're doing that I changed my upload function, but I'm still having a problem.

Now, instead of inserting "System.Byte[]" as binary, it's just putting in the beginning of the PDF: "%PDF-1.4 %????"

So, I'm a step closer at least!

Here's my updated function..

<WebMethod()> _
	Public Function File_Upload(ByVal DocumentID As Integer, ByVal Data As Byte(), ByVal LiveFrom As DateTime, ByVal FileSize As Integer) As Boolean
		
		Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings.Item("Sql").ConnectionString)
		Dim myCommand As SqlCommand = New SqlCommand
		myCommand.CommandType = CommandType.Text
		myCommand.Connection = myConnection
		
		Dim strSQL As String = "INSERT INTO files (Document_ID, pdf_data, version, live_from, FileSize) VALUES (@DocumentID, @Data, @Version, @LiveFrom, @FileSize)"
		
		With myCommand.Parameters
			.Add(New SqlParameter("@DocumentID", SqlDbType.Int)).Value = DocumentID
			.Add(New SqlParameter("@Data", SqlDbType.Image, 16)).Value = Data
			.Add(New SqlParameter("@Version", SqlDbType.Int)).Value = File_GetVersion(DocumentID)
			.Add(New SqlParameter("@FileSize", SqlDbType.Int)).Value = FileSize
			.Add(New SqlParameter("@LiveFrom", SqlDbType.DateTime)).Value = LiveFrom.Year & "-" & LiveFrom.Month & "-" & LiveFrom.Day & " " & LiveFrom.Hour & ":" & LiveFrom.Minute & ":" & LiveFrom.Second
		End With
		
		myCommand.CommandText = strSQL
		
		myConnection.Open()
		Return myCommand.ExecuteNonQuery > 0
		
	End Function

I'm stumped tbh.

 

[chrissie1]

thread796-1275682

I admit that the subject of the thread doesn't help much. But it has the solution to your problem.



Christiaan Baes
Belgium

"My new site" - Me

 

[BlackRed]

OK,

The problem with the second problem was this line:

.Add(New SqlParameter("@Data", SqlDbType.Image, 16)).Value = Data

I changed it to:

.Add(New SqlParameter("@Data", SqlDbType.Image)).Value = Data

And it works!

Thanks for all your help.