kevinkcw
asked on
Inserting Images Into MySQL
How would one adapt the code for the example at:
http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1
...for use with MySQL. My attempts thus far have been unsuccessful. The primary problem I have had is that using the .AppendChunk method results in the error: "Lost connection to MySQL server during query"
I have made the appropriate changes to the cursor location.
http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1
...for use with MySQL. My attempts thus far have been unsuccessful. The primary problem I have had is that using the .AppendChunk method results in the error: "Lost connection to MySQL server during query"
I have made the appropriate changes to the cursor location.
ASKER
Keisha,
Thank you very much. Here's some additional information: The application is in ASP, and as far as I know ASP doesn't have problems with file sizes. The problem isn't caused by timeout. I know this because the error returns within 15 seconds. I have set the max_allowed_packet on the server with "set max_allowed_packet=1600000
Thank you very much. Here's some additional information: The application is in ASP, and as far as I know ASP doesn't have problems with file sizes. The problem isn't caused by timeout. I know this because the error returns within 15 seconds. I have set the max_allowed_packet on the server with "set max_allowed_packet=1600000
Ok, couple more questions/comments:
Have you been able to insert any pictures at all? Try with a very small iamge and let me know what happens.
mySQL has a limit on table size. Not normally a problem, but with an image database, it could become one. How large are the files that you are trying to put in, and how many are already in(if any)?
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Table_size
In the database that you are trying to insert the image into, what is the type of the field? At the command line, "describe" the table and it will tell you.
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types
Different types of fields allow different amounts of data. You may be able to set max packets high, but if the field isn't large enough, or is the wrong type, it will throw error messages.
With it being ASP, I'm assuming that you are running Microsoft IIS server? I'm not familar with that at all, but you might check to see if the server itself doesn't have a size limitation as well.
-Keisha
Have you been able to insert any pictures at all? Try with a very small iamge and let me know what happens.
mySQL has a limit on table size. Not normally a problem, but with an image database, it could become one. How large are the files that you are trying to put in, and how many are already in(if any)?
http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Table_size
In the database that you are trying to insert the image into, what is the type of the field? At the command line, "describe" the table and it will tell you.
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types
Different types of fields allow different amounts of data. You may be able to set max packets high, but if the field isn't large enough, or is the wrong type, it will throw error messages.
With it being ASP, I'm assuming that you are running Microsoft IIS server? I'm not familar with that at all, but you might check to see if the server itself doesn't have a size limitation as well.
-Keisha
ASKER
Keisha,
Thank you again, for considering this question. Yes, I have been able to upload images using the .AppendChunk method, up to a file size of about 1 MB, and have uploaded around 8 images of various sizes below 1 MB. Using MS Access as the database on the same machine I have successfully uploaded files of up to 15MB in size. The field in the MySQL is of type MEDIUMBLOB, which should be good up to 16 MB. The table size is certainly not an issue yet, but it is interesting to note that there are limits. I'll investigate the possibility that IIS is interfering with the upload, which may in fact be the problem when I get "Internal Server Error" for all I know, but I assume that it's not a hard problem since I have been able to upload 15 MB files before (into Access,) from a fast connection.
I can see it might be useful for a more complete description of the problem and issues:
I was able to follow the example on stardeveloper.com (http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1) for uploading files and inserting them into a database as described without trouble.
The troubles start when I try to adapt the example for use with MySQL instead of MS Access.
I can't for the life of me get it to work consistently. I have changed the cursor location to client, I have changed the max_allowed_packed to 16000000, I have swapped out the table names that you can use with Access with the complete select statements that MySQL requires. Still, when I try to upload a file larger than 1 MB I get the following error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-
With this code:
' Checking to make sure if file was uploaded
If fileSize > 0 Then
set rsImage = Server.CreateObject("ADODB
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0
' Adding data
rsImage.AddNew
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
ERROR-->rsImage.Update
rsImage.Close
Set rsImage = Nothing
Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If
When I adapt the code to use the Stream object instead of .AppendChunk, I get this error: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." The code for the insert is as follows:
' Checking to make sure if file was uploaded
If fileSize > 0 Then
set adoImageStream = Server.CreateObject("ADODB
adoImageStream.Type = adTypeBinary
set rsImage = Server.CreateObject("ADODB
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0
' Adding data
rsImage.AddNew
adoImageStream.Open
ERROR--> adoImageStream.Write fileData
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
rsImage("imageData") = adoImageStream.read
rsImage.Update
adoImageStream.Close
rsImage.Close
Set rsImage = Nothing
Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If
I'm almost sure this error arises because the class object used for the page (found on the site with the tutorial, called 'loader.asp') changes the binary form data to a string, so the stream of type adTypeBinary doesn't accept the data, but when I change the stream type to text then I get an error because the field is expecting binary data. I haven't found an efficient enough way to change the data back to binary or to alter the class so that it doesn't turn the file data into a string in the first place. The relevant code from the class is this subroutine, which gets called when the object is initialized, to parse the form data:
Private Sub getData(rawData)
Dim separator
separator = MidB(rawData, 1, InstrB(1, rawData, ChrB(13)) - 1)
Dim lenSeparator
lenSeparator = LenB(separator)
Dim currentPos
currentPos = 1
Dim inStrByte
inStrByte = 1
Dim value, mValue
Dim tempValue
tempValue = ""
While inStrByte > 0
inStrByte = InStrB(currentPos, rawData, separator)
mValue = inStrByte - currentPos
If mValue > 1 Then
value = MidB(rawData, currentPos, mValue)
Dim begPos, endPos, midValue, nValue
Dim intDict
Set intDict = Server.CreateObject("Scrip
begPos = 1 + InStrB(1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))
nValue = endPos
Dim nameN
nameN = MidB(value, begPos, endPos - begPos)
Dim nameValue, isValid
isValid = True
If InStrB(1, value, stringToByte("Content-Type
begPos = 1 + InStrB(endPos + 1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))
If endPos = 0 Then
endPos = begPos + 1
isValid = False
End If
midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "FileName", trim(byteToString(midValue
begPos = 14 + InStrB(endPos + 1, value, stringToByte("Content-Type
endPos = InStrB(begPos, value, ChrB(13))
midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "ContentType", trim(byteToString(midValue
begPos = endPos + 4
endPos = LenB(value)
nameValue = MidB(value, begPos, ((endPos - begPos) - 1))
Else
nameValue = trim(byteToString(MidB(val
End If
If isValid = True Then
intDict.Add "Value", nameValue
intDict.Add "Name", nameN
dict.Add byteToString(nameN), intDict
End If
End If
currentPos = lenSeparator + inStrByte
Wend
End Sub
An effective solution to my problem lies either in what I'm doing wrong with MySQL (obviously I'm doing something wrong, else I wouldn't be getting errors using the AppendChunk method) or with converting the data into a binary type so it can be written to the adTypeBinary type Stream object.
Thank you again, for considering this question. Yes, I have been able to upload images using the .AppendChunk method, up to a file size of about 1 MB, and have uploaded around 8 images of various sizes below 1 MB. Using MS Access as the database on the same machine I have successfully uploaded files of up to 15MB in size. The field in the MySQL is of type MEDIUMBLOB, which should be good up to 16 MB. The table size is certainly not an issue yet, but it is interesting to note that there are limits. I'll investigate the possibility that IIS is interfering with the upload, which may in fact be the problem when I get "Internal Server Error" for all I know, but I assume that it's not a hard problem since I have been able to upload 15 MB files before (into Access,) from a fast connection.
I can see it might be useful for a more complete description of the problem and issues:
I was able to follow the example on stardeveloper.com (http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1) for uploading files and inserting them into a database as described without trouble.
The troubles start when I try to adapt the example for use with MySQL instead of MS Access.
I can't for the life of me get it to work consistently. I have changed the cursor location to client, I have changed the max_allowed_packed to 16000000, I have swapped out the table names that you can use with Access with the complete select statements that MySQL requires. Still, when I try to upload a file larger than 1 MB I get the following error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-
With this code:
' Checking to make sure if file was uploaded
If fileSize > 0 Then
set rsImage = Server.CreateObject("ADODB
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0
' Adding data
rsImage.AddNew
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
ERROR-->rsImage.Update
rsImage.Close
Set rsImage = Nothing
Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If
When I adapt the code to use the Stream object instead of .AppendChunk, I get this error: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." The code for the insert is as follows:
' Checking to make sure if file was uploaded
If fileSize > 0 Then
set adoImageStream = Server.CreateObject("ADODB
adoImageStream.Type = adTypeBinary
set rsImage = Server.CreateObject("ADODB
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0
' Adding data
rsImage.AddNew
adoImageStream.Open
ERROR--> adoImageStream.Write fileData
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
rsImage("imageData") = adoImageStream.read
rsImage.Update
adoImageStream.Close
rsImage.Close
Set rsImage = Nothing
Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If
I'm almost sure this error arises because the class object used for the page (found on the site with the tutorial, called 'loader.asp') changes the binary form data to a string, so the stream of type adTypeBinary doesn't accept the data, but when I change the stream type to text then I get an error because the field is expecting binary data. I haven't found an efficient enough way to change the data back to binary or to alter the class so that it doesn't turn the file data into a string in the first place. The relevant code from the class is this subroutine, which gets called when the object is initialized, to parse the form data:
Private Sub getData(rawData)
Dim separator
separator = MidB(rawData, 1, InstrB(1, rawData, ChrB(13)) - 1)
Dim lenSeparator
lenSeparator = LenB(separator)
Dim currentPos
currentPos = 1
Dim inStrByte
inStrByte = 1
Dim value, mValue
Dim tempValue
tempValue = ""
While inStrByte > 0
inStrByte = InStrB(currentPos, rawData, separator)
mValue = inStrByte - currentPos
If mValue > 1 Then
value = MidB(rawData, currentPos, mValue)
Dim begPos, endPos, midValue, nValue
Dim intDict
Set intDict = Server.CreateObject("Scrip
begPos = 1 + InStrB(1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))
nValue = endPos
Dim nameN
nameN = MidB(value, begPos, endPos - begPos)
Dim nameValue, isValid
isValid = True
If InStrB(1, value, stringToByte("Content-Type
begPos = 1 + InStrB(endPos + 1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))
If endPos = 0 Then
endPos = begPos + 1
isValid = False
End If
midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "FileName", trim(byteToString(midValue
begPos = 14 + InStrB(endPos + 1, value, stringToByte("Content-Type
endPos = InStrB(begPos, value, ChrB(13))
midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "ContentType", trim(byteToString(midValue
begPos = endPos + 4
endPos = LenB(value)
nameValue = MidB(value, begPos, ((endPos - begPos) - 1))
Else
nameValue = trim(byteToString(MidB(val
End If
If isValid = True Then
intDict.Add "Value", nameValue
intDict.Add "Name", nameN
dict.Add byteToString(nameN), intDict
End If
End If
currentPos = lenSeparator + inStrByte
Wend
End Sub
An effective solution to my problem lies either in what I'm doing wrong with MySQL (obviously I'm doing something wrong, else I wouldn't be getting errors using the AppendChunk method) or with converting the data into a binary type so it can be written to the adTypeBinary type Stream object.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Keisha,
Thanks! Spot on with the answer and thanks for sticking with it. The server did in fact need to be restarted and changes to the .ini file needed to be made. It turned out to be an education in MySQL server administration that I probably should have had prior to dinking with it in the first place.
To be specific, since I'm running MySQL server on a Windows 2000 Server machine the service is started automaticly at startup, through the winmysqladmin-nt.exe program. That opens the GUI client, but throws it in the task bar, minimized. Click on that icon and select Show Me, and the GUI opens. Click on the "my.ini setup" tab. In the [mysqld] section add the line "set-variable=max_allowed_
To confirm that the changes are in effect, open a command prompt and navigate to the install folder (usually C:\mysql\bin\) and open the mysql command line shell. From there type SHOW VARIABLES; and look for the "max_allowed_packet" entry. If this is 16776192 then Bob's Your Uncle and you can get on with inserting large binary data into your database.
Thanks again,
-Kevin
Thanks! Spot on with the answer and thanks for sticking with it. The server did in fact need to be restarted and changes to the .ini file needed to be made. It turned out to be an education in MySQL server administration that I probably should have had prior to dinking with it in the first place.
To be specific, since I'm running MySQL server on a Windows 2000 Server machine the service is started automaticly at startup, through the winmysqladmin-nt.exe program. That opens the GUI client, but throws it in the task bar, minimized. Click on that icon and select Show Me, and the GUI opens. Click on the "my.ini setup" tab. In the [mysqld] section add the line "set-variable=max_allowed_
To confirm that the changes are in effect, open a command prompt and navigate to the install folder (usually C:\mysql\bin\) and open the mysql command line shell. From there type SHOW VARIABLES; and look for the "max_allowed_packet" entry. If this is 16776192 then Bob's Your Uncle and you can get on with inserting large binary data into your database.
Thanks again,
-Kevin
You don't mention what language you are trying to use, but that provides examples and troubleshooting for PHP.
<snip>
How to handle files larger than 1 MB:
If you want to upload and store files bigger than 1 MB, you have make several changes to the scripts and your php/sql setup, as it is caused by default limitations of the programs. Do the following to be able to store files as large as 24 Megabyte:
Edit the store.php3 script. Change the MAX_FILE_SIZE value (in the form) to 24000000.
Remove the filesize limitation from your php installation. This is set either in your php.ini or in your apache config files. By default, php3 only allowes files smaller than 2 MB. You have to change the max_filesize variable to: max_filesize = 24000000
Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB.
You have to restart your database with some parameters, which will remove the limitations.
If you still get errors:
This could be a timeout problem. If you upload large files via a slow connection, php's default timeout of 30 seconds might kill your process. Change the max_execution:time variable in your php.ini to: max_execution_time=-1
-Keisha