RE: Binary Data and BLOB Problem
Mike, Thank you very much for responding to my question. Haven't been able to find much out there. Your article, to which you referred in your response, was something I have already used to get me as far as I had gotten. I'd found it in my search for an answer. I found the connection string and the explanation of the options you used particularly useful. The comment you made about the cursor location confirmed what I read elsewhere, though put a different way. I've read that MySQL simply doesn't support cursors, so you have to set the cursor location to client. In the MySQL docs I've seen where they indicate that in the near future they plan to support cursors. I was able to follow the example on stardeveloper.com (http://www.stardeveloper.com/articles/display.html?article=2001033101page=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 1600, 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-max-nt]Lost connection to MySQL server during query. Sometimes the error is: Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Got a packet bigger than 'max_allowed_packet' I'm sure the file is less than 2MB. With this code: ' Checking to make sure if file was uploaded If fileSize 0 Then set rsImage = Server.CreateObject(ADODB.Recordset) 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=greenFile was successfully uploaded... Response.Write /font Else Response.Write font color=brownNo 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.Stream) adoImageStream.Type = adTypeBinary set rsImage = Server.CreateObject(ADODB.Recordset) 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=greenFile was successfully uploaded... Response.Write /font Else Response.Write font color=brownNo 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(Scripting.Dictionary) begPos = 1 + InStrB(1, value, ChrB(34)) endPos =
RE: Binary Data and BLOB Problem
BEGIN SHAMELESS PLUG
Hi Kevin;
You may want to look at the online article I wrote at
http://www.dynamergy.com/mike/articles/blobaccessvb.html which covers use of
the stream object in detail. While written for VB, it should be easy enough
to convert to ASP.
Mike Hillyer
END SHAMELESS PLUG
-Original Message-
From: Kevin Wixson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 10:29 AM
To: [EMAIL PROTECTED]
Subject: Binary Data and BLOB Problem
Having trouble with my first MySQL database. I want to upload and install
image files to a database for online sample photo gallery for our
customers. The system I've created so far sometimes works, sometimes
doesn't. It seems to depend on the size of the file being uploaded, but
tests have been inconclusive. Having trouble diagnosing the problem since
the error is most often Internal Server Error, which doesn't tell me
much. Otherwise I get:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Lost connection to MySQL
server during query
/shooters/Insert.asp, line 148
The relevant code is:
' Checking to make sure if file was uploaded
If fileSize 0 Then
set rsImage = Server.CreateObject(ADODB.Recordset)
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
Do While lngOffset fileSize
varChunk = LeftB(RightB(fileData, fileSize - lngOffset), conChunkSize)
rsImage(imageData).AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
ERROR--rsImage.Update
'adoImageStream.Close
rsImage.Close
Set rsImage = Nothing
Response.Write font color=greenFile was successfully uploaded...
Response.Write /font
Else
Response.Write font color=brownNo file was selected for uploading
Response.Write .../font
End If
My connection string is:
MM_connShooters_STRING = driver={MySQL ODBC 3.51 Driver};
database=[name];uid=[id];pwd=[pass];OPTION=16427;
The constant conChunkSize is 100. I have checked the max packet size (set
to 1550) and as you can see, set cursors to client.
Alternatively, I have also tried to use the stream object, but I haven't
had any luck there either. I'm sure I'm not doing it right.
Thank you,
Kevin Wixson
IT Manager
Norman Camera Video
(616) 567-5552
1-800-900-6676
Fax: (616) 343-6410
e-mail: [EMAIL PROTECTED]
Visit us on the web at: http://www.normancamera.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary data mysqldump and restore.
If I remember correctly from the manual, you can dump your data generating the SQL statements (this is what you are doing) and your file will have a bunch of INSERTS. Your problem is that those inserts will have something like SET YourBinaryField = 'a-bunch-of-characters'. The problem is that some of the binary data could make the insert impossible to be executed. What I would try is to export the data as a delimited file, then you will have to files, one with your database structure (that you will run first) and another one with the data (delimited by something). Take a look on the manual about this and good luck! --- Mark Williams (MWP) [EMAIL PROTECTED] wrote: Hi all... !! First, can you CC to [EMAIL PROTECTED], as im not on the mailing list. I have a few tables which are storing a few MB of binary data (like jpegs). I can dump the db fine using mysqldump, but on doing a mysql ... dumpfile i get many errors, as it seems mysql isnt accepting the binary data in the dump file. I get hundreds of errors like: ERROR: Unknown command '\Z'. ERROR: Unknown command '\0'. ERROR: Unknown command '\Z'. ERROR: Unknown command '\0'. ERROR: Unknown command '\'. ERROR 1064: You have an error in your SQL syntax near 'GIF89a2\02\0÷ÿ\0\0\0\0ÀÀÀ\0\0 \Z\0\Z 0\ INSE' at line 1 Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) So how do i do this? Thanks, MWP - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary Data mysql_real_escape_
are you null terminating the string?
Andrei Cojocaru
[EMAIL PROTECTED]
- Original Message -
From: Sameer Maggon [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, May 20, 2002 7:51 AM
Subject: Binary Data mysql_real_escape_
Hi,
Please see the code given below::
//
strcpy(query,INSERT INTO blobtest VALUES('abc',');
qptr = query;
len = strlen(query);
qptr += len;
qptr += mysql_real_escape_string(mysql, qptr, data, iBytesRead);
*qptr += '\'';
*qptr += ')';
mysql_real_query(mysql,query, (unsigned int)( qptr - query));
printf(error = %s, mysql_error(mysql));
//
It gives the error
error = You have an error in your SQL syntax
near ''ÿØÿà\0^PJFIF\0^A^A\0\0^
A\0^A\0\0ÿÛ\0C\0^H^F^F^G^F^E^H^G^G^G
^H\n^L^T\r^L^K^K^L^Y^R^S^O^T^]\Z^_^^^]\Z^\^\ $.\' \'
at line 1
HWhat might be the problem
Please help
Sameer
--
http://www.dypatil.edu For Better tomorrow
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary data with embedded nulls (mysql_real_escape_string problem).
On Fri, 14 Dec 2001, Ian Collins wrote: I am having difficulties with entering binary data from a c program. I have passed the string through mysql_real_escape_string. Post the C data then. However read on. . . . drop table if exists junk5; create table junk5 ( id int not null, primary key(id), s1 char(10) not null, i1 smallint not null ); insert junk5 values(1,'abc\0def\0gh', 2); insert junk5 values(2,'abc\\0def\\0gh', 3); insert junk5 values(3,'abc\\0def\0gh', 4); select * from junk5; By running this snippet, you will see that you need to double escape the null to get it in the database. No. What you are putting in the database is (character by character): 1 = 'a', 'b', 'c', 0, 'd' ... 2 = 'a', 'b', 'c', '\', '0', 'd' ... Proof: mysql select id, substring(s1, 1, 3) as first3, ascii(substring(s1, 4, 1)) as fourth, substring(s1, 5, 5) as remainder from junk5; ++++---+ | id | first3 | fourth | remainder | ++++---+ | 1 | abc| 0 | def | | 2 | abc| 92 | 0def\ | | 3 | abc| 92 | 0def | ++++---+ The 'problem' is in the output from the mysql program: it does not have a literal for null (in text); instead it seems to interpret it as end of text; this is probably because it is bound to the C zero-terminated strings model. Using the API you can get it right. -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150-180 PORTO, Portugalmob 351+939354002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary Data storage and Retrieval Problem
On Thu, Jul 12, 2001 at 01:51:29AM +0200, Werner Stuerenburg wrote: Im my understanding, you are storing images. AFAIK, images should not be stored in the db but in the filesystem. There's nothing wrong with doing it, as long as you understand what you're doing. After all, images are (from MySQL's point of view) no different than lots of text, numbers, or any other data. You store only the filename and other data (time etc.) in the db. There are some remarks on this at php.net and other places, as this question is posed often. It's not always possible or easy to get files onto the database server's filesystem. In fact, it's often a good idea to tightly restrict access to a database server's filesystem. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 25 days, processed 193,254,608 queries (86/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary Data storage and Retrieval Problem
Im my understanding, you are storing images. AFAIK, images should not be stored in the db but in the filesystem. You store only the filename and other data (time etc.) in the db. There are some remarks on this at php.net and other places, as this question is posed often. Sie schrieben am Mittwoch, 11. Juli 2001, 23:45:03: Hi, I am trying to store fax images in mysql (they are about 1K per page), which comes in as binary data. I am able to use mysql_escape_string(data, fax, sizeof(fax)) to get the data into mysql, as well as only escaping ' and \ for the query string. I have tried both longblob and longtext mysql fields for the fax data. When I try to query the data back to a file from the command line (I use Linux) mysql -s -e select fax_data from fax_table where record_number = '1' fax_db fax1.01.fax the data I get is all escaped (like \0) and when I try to send the fax out, it has problems with the escaped characters (which were originally nulls, and other non-printing characters). The file size is also much larger with the output file than the input file. Does anyone have any suggestions as to how I should get around this? I need to be able to either store the fax as is or encode the fax in some simple way and de-encode it on retrieval. thanks Joe Johnson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: binary data
Hi okie heres a snippet of my php code:
the code below takes a file uploaded from the web with $file as declared filename on
the form.
if ($file_size0) { //check whether the file is not empty
$data = addslashes(fread(fopen($file, "r"), filesize($file))); //read the file and
add neccessary quotes
$SQL="Insert into Files (Filename,Filetype,Filesize,data) values
('$file_name','$file_type','$file_size','$data')"; //SQL commands to insert into table
named 'FILES'
$result = mysql_query ($SQL)
or die ("ERROR Uploading File:".mysql_errno().": ".mysql_error()."BR$SQL");
}
okie hope that helps!
Teddy
"Robert C. Paulsen Jr." wrote:
On Sat, Feb 03, 2001 at 10:20:07PM +0800, Teddy A Jasin wrote:
I have the scripts in php to do this if u want.
Yes, that would help! thanks.
"Robert C. Paulsen Jr." wrote:
How can I put binary data into a MySQL database?
I assume the data type is blob, but I don't see how I can
use SQL syntax to insert anything but text data. I would
like to include various binary files (tar files, zip files,
image files, etc.).
--
Robert Paulsen [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary data
Hi Barry,
I do not know what do you use for feeding your database, but in Perl you
shoud use the DBI module, and insert the binary data through bindings.
use DBI;
use strict;
...
my $dbh = DBI-connect("dbi:mysql:image_db", "user", "password") || die
"$DBI::errstr\n"; # connect to the database
...
my $sth = $dbh-prepare("INSERT jpeg_table SET size_x = $size_x, size_y =
$size_y, image_data = ?;") || die "$DBI::errstr\n"; # prepare the insert
statement, replacing the image data with a question mark
$sth-bind_param(1, $image_data); # binding the binary data
$sth-execute() || die "$DBI::errstr\n";# executing the insert
...
$dbh-disconnect(); # disconnect
Regards,
Peter Szekszardi
PortoLogic Ltd.
Portal building, design, net survey and more...
On Wed, 31 Jan 2001, Barry Radloff wrote:
hi I am pretty new at this and would like to know how does one store a
binary blob to the mysql DB ie I would like to store a jpeg to the db
Thanks
Barry Radloff
RD
Media24
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Binary data
Barry,
This can be done using normal insert/update statements. You only have to
'escape' the binary zero, the backslash and the quotes (' or ") out;
suppose jpg contains the binary data, the following code will do the trick:
(pseudo code:)
for (i=length(jpg); i--; i0) {
if (jpg[i] in [#0, '\', , '"'] ) { jpg=jpg[1..i-1] + '\' +
jpg[i..length(jpg)]
};
Original Message -
From: "Barry Radloff" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, January 31, 2001 7:55 AM
Subject: Binary data
hi I am pretty new at this and would like to know how does one store a
binary blob to the mysql DB ie I would like to store a jpeg to the db
Thanks
Barry Radloff
RD
Media24
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Binary data
hi I am pretty new at this and would like to know how does one store a binary blob to the mysql DB ie I would like to store a jpeg to the db http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_13_0 http://www.bitbybit.dk/mysqlfaq/faq.html#ch14_1_0 / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Binary data
If your using PHP there is an article here on doing it with PHP3, it works on 4 as well. HTH http://www.phpbuilder.com/columns/florian19991014.php3 Dave W. -Original Message- From: Barry Radloff [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 10:56 PM To: '[EMAIL PROTECTED]' Subject:Binary data hi I am pretty new at this and would like to know how does one store a binary blob to the mysql DB ie I would like to store a jpeg to the db Thanks Barry Radloff RD Media24 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
