BLOB & MySQL problem
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15

Thread: BLOB & MySQL problem

  1. #1
    Join Date
    Feb 2009
    Posts
    18

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    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)

  3. #3
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    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)

  4. #4
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    Re: BLOB & MySQL problem

    Quote Originally Posted by Shuja Ali View Post
    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.

  5. #5
    Join Date
    Feb 2009
    Posts
    18

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

  6. #6
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    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.

  7. #7
    Join Date
    Feb 2009
    Posts
    18

    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;"

  8. #8
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: BLOB & MySQL problem

    Remove these two statements and then try
    Code:
                    MySQLCommand.Prepare()
                    MySQLCommand.Parameters.Clear()

  9. #9
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,451

    Re: BLOB & MySQL problem

    Quote Originally Posted by nhunter98 View Post
    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\0C\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.

  10. #10
    Join Date
    Feb 2009
    Posts
    18

    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'

  11. #11
    Join Date
    Feb 2009
    Posts
    18

    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)

  12. #12
    Join Date
    Feb 2009
    Posts
    18

    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

  13. #13
    Join Date
    Feb 2009
    Posts
    18

    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.

  14. #14
    Join Date
    Feb 2009
    Posts
    18

    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.

  15. #15
    Join Date
    Feb 2009
    Posts
    18

    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center