-
March 17th, 2009, 02:00 PM
#1
BLOB & MySQL problem
I am recieving an error when I am trying to write a blob file to MySQL. Can anyone help me figure out what I am doing wrong.
Here is my code:
Code:
Dim ScreenFileSize As UInt32 = 0
Dim ScreenFile() As Byte
Dim ScreenStream As FileStream
ScreenStream = New FileStream(ScreenImageLocation, FileMode.Open, FileAccess.Read)
ScreenFileSize = ScreenStream.Length
ScreenFile = New Byte(ScreenFileSize) {}
ScreenStream.Read(ScreenFile, 0, ScreenFileSize)
ScreenStream.Close()
Try
MySQLQuery = "UPDATE " & MySQLDatabase & ".btt (ScreenShot) VALUES(?FileUpload) WHERE Ticket='" & strTicketNo & "'"
MySQLCommand = New MySql.Data.MySqlClient.MySqlCommand(MySQLQuery, MySQLConnection)
MySQLConnection.Open()
MySQLCommand.CommandText = MySQLQuery
MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()
MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
MySQLCommand.ExecuteNonQuery()
MySQLConnection.Close()
Catch ex As Exception
End Try
End If
Here is the first part of the error I recieve:
MySql.Data.MySqlClient.MySqlException was caught
ErrorCode=-2147467259
Message="You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ScreenShot) VALUES(_binary 'ÿØÿà\0JFIF\0\0`\0`\0\0ÿÛ\0C\0
' at line 1"
Number=1064
Source="MySql.Data"
Last edited by Shuja Ali; March 17th, 2009 at 02:07 PM.
Reason: Added Code Tags
-
March 17th, 2009, 02:14 PM
#2
Re: BLOB & MySQL problem
The code looks ok to me. What version of MySQL are you using?
Also try doin git like this while adding a parameter
Code:
Dim imageFile As MySQLParameter
imageFile = New MySqlParameter(”?File”, ScreenFile)
MySQLCommand.Parameters.Add(imageFile)
-
March 17th, 2009, 02:24 PM
#3
Re: BLOB & MySQL problem
It looks to me like you are mixing UPDATE and INSERT SQL Statements.
Insert should be.
Code:
MySQLQuery = "INSERT INTO " & MySQLDatabase & ".btt (ScreenShot) VALUES(?FileUpload);"
Update should be
Code:
MySQLQuery = "UPDATE " & MySQLDatabase & ".btt SET ScreenShot = ?FileUpload WHERE Ticket=?TN"
MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()
MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
MySQLCommand.Parameters.Add("?TN", strTicketNo)
-
March 17th, 2009, 02:28 PM
#4
Re: BLOB & MySQL problem
Originally Posted by Shuja Ali
The code looks ok to me. What version of MySQL are you using?
Also try doin git like this while adding a parameter
Code:
Dim imageFile As MySQLParameter
imageFile = New MySqlParameter(”?File”, ScreenFile)
MySQLCommand.Parameters.Add(imageFile)
This is a very good point. It appears from you code that you are using the
MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
If this actually works, then you are using a very old version of the MySQL connector. I would recommend you upgrade. The statement in the new connector is...
MySQLCommand.Parameters.AddWithValue("?FileUpload", ScreenFile)
Or do it as Shuja Ali has suggested.
-
March 18th, 2009, 12:51 PM
#5
Re: BLOB & MySQL problem
I have tried all of the above. All result in the same error.
I am using
MySQL version 5.1.30
MySql-connector-net-5.0.9
VB.NET 2008
Any other ideas??
-
March 18th, 2009, 01:23 PM
#6
Re: BLOB & MySQL problem
put
messagebox.show(ex.message)
in the catch part and show us the error.
ETA: And can you show us how MySQLConnection is initialized.
-
March 18th, 2009, 03:35 PM
#7
Re: BLOB & MySQL problem
here is the error:
+ ex {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES(_binary 'PNG \0\0\0 IHDR\0\0\0\0\0
________________________________________
\0 \0\0\0¾ôC\0\0\0sRGB\0®Î é\0' at line 1"} System.Exception
Here is the connection:
Friend strBTTConnString As String = "server=link_server;" & "uid=User;" & "pwd=password;" & "database=data;"
-
March 18th, 2009, 03:42 PM
#8
Re: BLOB & MySQL problem
Remove these two statements and then try
Code:
MySQLCommand.Prepare()
MySQLCommand.Parameters.Clear()
-
March 18th, 2009, 06:52 PM
#9
Re: BLOB & MySQL problem
Originally Posted by nhunter98
here is the error:
+ ex {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES(_binary '‰PNG \0\0\0 IHDR\0\0\0\0\0
________________________________________
\0 \0\0\0¾“ôC\0\0\0sRGB\0®Î é\0' at line 1"} System.Exception
Here is the connection:
Friend strBTTConnString As String = "server=link_server;" & "uid=User;" & "pwd=password;" & "database=data;"
This statement is telling you that it is a problem with your SQL Statement. It is telling you right where the issue is also. Everything upto the "Values" keyword is correct. Could you please post which SQL statement you are using. You seem to be confusing the UPDATE syntax with the INSERT syntax. If your query is the update statement, you need to use the update syntax I posted above. ie.
UPDATE TableName SET FieldToUpdate=Valueforthefield WHERE KeyFieldName=KeyFieldValue.
If you have not done so, you should download and install the MySQL gui tools. This has a query browser where you can try out your queries.
-
March 19th, 2009, 07:23 AM
#10
Re: BLOB & MySQL problem
I tried removing:
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()
this did not help.
this is the update statement I am using:
UPDATE btt.btt SET ScreenShot=VALUES(?FileUpload) WHERE Ticket='72'
-
March 19th, 2009, 07:24 AM
#11
Re: BLOB & MySQL problem
I have also tried the code below which you asked me to do and get the same result.
modMySql.MySQLCommand.Parameters.AddWithValue("?FileUpload", ScreenFile)
-
March 19th, 2009, 07:28 AM
#12
Re: BLOB & MySQL problem
here is all my code:
Dim ScreenFileSize As UInt32 = 0
Dim ScreenFile() As Byte
Dim ScreenStream As FileStream
ScreenStream = New FileStream(ScreenImageLocation, FileMode.Open, FileAccess.Read)
ScreenFileSize = ScreenStream.Length
ScreenFile = New Byte(ScreenFileSize) {}
ScreenStream.Read(ScreenFile, 0, ScreenFileSize)
ScreenStream.Close()
Try
modMySql.MySQLQuery = "UPDATE " & modMySql.MySQLDatabase & ".btt SET ScreenShot=VALUES(?FileUpload) WHERE Ticket='" & strTicketNo & "'"
modMySql.MySQLCommand = New MySql.Data.MySqlClient.MySqlCommand(modMySql.MySQLQuery, modMySql.MySQLConnection)
modMySql.MySQLConnection.Open()
modMySql.MySQLCommand.CommandText = modMySql.MySQLQuery
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()
modMySql.MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
modMySql.MySQLCommand.ExecuteNonQuery()
modMySql.MySQLConnection.Close()
Catch ex As Exception
End Try
-
March 19th, 2009, 07:37 AM
#13
Re: BLOB & MySQL problem
we have progress, I found that I missed a post from above. My new code is:
modMySql.MySQLQuery = "UPDATE " & modMySql.MySQLDatabase & ".btt SET ScreenShot = ?FileUpload WHERE Ticket=?TN"
modMySql.MySQLConnection.Open()
modMySql.MySQLCommand.CommandText = modMySql.MySQLQuery
modMySql.MySQLCommand.Prepare()
modMySql.MySQLCommand.Parameters.Clear()
modMySql.MySQLCommand.Parameters.Add("?FileUpload", ScreenFile)
modMySql.MySQLCommand.Parameters.Add("?TN", strTicketNo)
My new error is:
+ ex {"Data too long for column 'ScreenShot' at row 52"} System.Exception
Any ideas on this error? Thanks again for all your help.
-
March 19th, 2009, 07:54 AM
#14
Re: BLOB & MySQL problem
ok here is another update. I changed my column in my db to LONGBLOB instead of BLOB and my data is entered into the db.
So here is my next problem. How do you retrieve this file (it is a png file) from the db and display in an Picturebox? Can someone point me in the right direction.
Thanks again for all who have helped.
-
March 19th, 2009, 03:14 PM
#15
Re: BLOB & MySQL problem
thanks everyone for your help. I have all my answers. You all have been a huge help. Thanks again.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|