If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? Thanks for your information, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
I've break line misteak in my previous message, this is the fix : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Le Tue, 23 Dec 2008 14:42:40 +, KLEIN Stéphane a écrit : Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? I've the answer ! My previous script corrupt binary data. To fix this issue I need to dump data with --hex-dump option : $ mysqldump --hex-dump --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql$ mysqldump --user=root --password=password --host=mybox mydatabase Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If I use iconv to convert mysql dump data file with some blob field then blob binary data will be corrupted ?
Le Tue, 23 Dec 2008 15:33:34 +, KLEIN Stéphane a écrit : Le Tue, 23 Dec 2008 14:42:40 +, KLEIN Stéphane a écrit : Hi, This my script to convert latin1 database to utf8 : $ mysqldump --user=root --password=password --host=mybox mydatabase -- default-character-set=latin1 mydatabase.latin1.sql$ mysqldump -- user=root --password=password --host=mybox mydatabase --default-character- set=latin1 mydatabase.latin1.sql $ sed -e 's/latin1/utf8/g' mydatabase.latin1.sql mydatabase.utf8.sql $ iconv -f latin1 -t utf8 mydatabase.utf8.sql mydatabase.utf8.sql $ echo SET NAMES utf8; tmp.sql $ cat mydatabase.utf8.sql tmp.sql $ mv tmp.sql mydatabase.utf8.sql I've one question : * if my database have some blob field with binary data (like image...), iconv convert also this data then this data will be corrupted ? I've the answer ! My previous script corrupt binary data. To fix this issue I need to dump data with --hex-dump option : $ mysqldump --hex-dump --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql$ mysqldump --user=root --password=password --host=mybox mydatabase Sorry, it's --hex-blob not --hex-dump $ mysqldump --hex-blob --user=root --password=password --host=mybox mydatabase --default-character-set=latin1 mydatabase.latin1.sql Regards, Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using mysqlbinlog with tables with binary data (blobs)
We have had some cases where we wanted to recover data using the binlogs from some days ago. The database has a lot of binary objects (blobs). The normal way to recover are to restore database files from a consistant point, and then use mysqlbinlog to spool from that point till where we want to end. mysqlbinlog only produces text sql output. This means it is useless for databases with blobs. How can we spool data from our binary logs? mysqldump has an option --hex-blob. I found an old feature request from 2005, asking to add a similar feature to mysqlbinlog. I found no followups to the request. Is this doable? Ingvar -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cli aborts when searching for binary data
Hi, As my mysql command line utility aborts when searching for certain characters in a binary field (it affects bash as well), I decided to convert these characters via CHAR(character) instead, like this: select binary_column from my_table where binary_column like CONCAT (CHAR(3),'pR',CHAR(1),CHAR(3),'pU',CHAR(1),'%') This works fine, but it looks a little clunky. Is there another way to write this query more efficiently? Thanks for feedback! Richard Taubo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing huge amount of binary data
Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing huge amount of binary data
Sometimes, the easiest way to do this is to use the file system of the linux machine to store the files, and make reference to them in the DB...storing not data in the DB and getting rid of all your possible problems. Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -Original Message- From: Cabbar Duzayak [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 12:01 AM To: mysql@lists.mysql.com Subject: Storing huge amount of binary data Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing huge amount of binary data
Cabbar Duzayak wrote: So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? I would say so, yes. One of my biggest databases holds 50-60million rows, and takes up about 5Gb diskspace. I don't think mysql will have any problems running what you describe. 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? Clustering and replication is more to do with data-availability. You'll probably benefit from using RAID in some form - depends on whether you need reliability or speed. 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? I don't think so, no. /Per Jessen, Zürich -- http://www.spamchek.com/freetrial - managed anti-spam and anti-virus solution. Sign up for your free 30-day trial now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing huge amount of binary data
First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? I don't have much experience with individual tables being that large, though I have used MySQL databases with 100G total data with no problems at all. Based on my experience, it's not the table size which can degrade performance, but the index size - if the index is too large or clumsy, then MySQL can not search quickly enough. If the index is well-structured, the data is found quickly and overall performance is great regardless of the quantity of data. Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). This is a good way to help keep your indexes small and thus keep search/seek time to a minimum :) However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. A suggestion to help keep your indexes on this table small: if you have large text fields that you need to index, you may want to use partial indexes. (for example, if you have a column filename varchar(200), create an index on filename(50), or what ever is appropriate. This will cut the size of the index file a lot, and even though it may result in some duplicate entries in the index, it will probably increase overall performance.) And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? Based on my experience with 100GB InnoDB databases, with the right indexes, MySQL can easily handle 3,000 reads/sec on text/numerical data. (This is on dual xeon 3Ghz, 4 GB RAM, SCSI Raid-5 disks.) I've never had any problem with data corruption, but I use primarily the InnoDB engine, which is not prone to corruption in the same way as MyISAM. I don't have experience storing large amounts of binary data, so I can't say anything about MySQL's performance in that area specifically. 2. Can I implement this using regular SCSI disks with regular mysql? Probably. Or do I have need advanced solutions such as clustered, replicated, etc? No need for clustered or distributed databases, from what you've described. Cluster would be useful if you need 100% availability, even in the event of hardware failures. Replication, such as single master - multiple slaves, is useful if you have massive reads and minimal writes, and _may_ be something you will need. I would recommend using the command SHOW PROCESSLIST, or a tool like MyTop, to see what state the client connections spend the most time in. (searching the index, or sending the data over the network? if it's the latter, then you would benefit from distributing the read load to multiple slave servers.) Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing huge amount of binary data
Per Jessen wrote: 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? I don't think so, no. what are you basing this on? SQL Server is a truly great database package, don't let some foolish bias blind you to the fact that it is professional grade software. i like MySQL as much as the next guy, but its being open source does not mean you should ignore the facts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting Other Binary data into DB (NOT IMAGES)
Title: Inserting Other Binary data into DB (NOT IMAGES) Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
Re: Inserting Other Binary data into DB (NOT IMAGES)
Inserting Other Binary data into DB (NOT IMAGES)Inserting images in no different compared to inserting PDFs. What is your problem? What have you tried so far? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting Other Binary data into DB (NOT IMAGES)
Since this post, I have been able to enter a PDF file into the DB using MySQLCC / MySQL CLI and INSERT / UPDATE statements. Now my issue is the ASP / aspUpload. That is not appropriate for this list. Thanks anyways! J.R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 01, 2005 5:09 PM To: [EMAIL PROTECTED]; mysql Subject: Re: Inserting Other Binary data into DB (NOT IMAGES) Inserting Other Binary data into DB (NOT IMAGES)Inserting images in no different compared to inserting PDFs. What is your problem? What have you tried so far? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com Good afternoon all, I have a db (gasp!) that is holding images in a BLOB field already. I can upload and retrieve on the fly - no problems. Now I am looking at inserting binary data such as M$Word Documents (.doc and .rtf), XML (which I have already been exposed to on this list), and PDF files. I have read somewhere that it can be done, however I have not been able to make it work with MySQL. NOTES: -- I can make it work with Acce$$ if I use the OLE Object, but that is not my goal. -- I do not want to store the resulting FSO /path/to/file structure. -- My goal is to try to insert them into the database. DB NOTES: -- MySQL 4.1.10 -- Linux RHEL 3.2 (production) -- Slave on Win2k3 also running 4.1.10 (devel) -- MyODBC 3.51.10 on Win2k3 -- ASP 3.0 with IIS and Apache/Sun One ASP Any help or insight will be greatly appreciated. Thanks! J.R. smime.p7s Description: S/MIME cryptographic signature
importing binary data
Hi, I'd like to insert binary data from a series of image files into a blob column using a sql statement. I have working examples from jsps / java but I'd like to write a sql script to do the same. I've had a look at the load data infile commands but they only seem to relate to text data. more than happy to read the docs, just need a pointer to which docs I should be reading :) thanks Nathan -- Nathan Coast Managing Director Codeczar Ltd mob : (852) 9049 5581 tel : (852) 2834 8733 fax : (852) 2834 8755 web : http://www.codeczar.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reading Binary Data
Hi Folks -- Been working on this issue for a while, and not making much headway, so I thought I'd throw it out to the list and see what wisdom came back :) Problem: I'm trying to find a way to store LOTS of scientific data (mostly 32-bit integers and floating point numbers) in a database for analysis work. The instrument(s) we're working with generate very large volumes of numeric data, roughly 30GB per day. What I'd like to do is have a method read through the data files produced, store the data in tables, making it easy to subset and index the data. OK, doesn't sound too bad off the bat. Here's the issue: Is there a way to store data in BINARY numeric formats into the database (without converting to strings first)? In two or three of my tables, rows contain 2500-3000 individual numbers, which would be a logistical nightmare to throw into a query string, not to mention potential loss of accuracy from rouding errors in floating point numbers. I thought that LOAD DATA INFILE was the next solution, but it appears to, when using fixed-row format, is that it uses the DISPLAY sizes of the fields, rather than the byte storage requirements for them. Anyone else out there dealing with large volumes of numeric data? Thanks in advance, Ken === Diplomacy is the weapon of the Civilized Warrior - Hun, A.T. Ken Gieselman [EMAIL PROTECTED] System Administrator http://www.endlessknot.com/~kgieselm Endlessknot Communications http://www.endlessknot.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
Can CHAR/VARCHAR store strings with nul (ASCII 0) in them? It seems it can't. [I've used CHAR(2), VARCHAR(2), CHAR(2) BINARY, VARCHAR(2) BINARY]. === create table t1 ( id int not null primary key, code varchar(2) not null, unique(code)); insert into t1 values (1,''); insert into t1 values (2,char(0)); insert into t1 values (3,char(32)); insert into t1 values (4,concat(char(0),char(0))); insert into t1 values (5,concat(char(0),char(32))); insert into t1 values (6,concat(char(32),char(0))); insert into t1 values (7,concat(char(32),char(32))); select id, length(code), if(length(code)=0, '', if(length(code)=1, ord(code), concat(ord(substring(code,1,1)),' ',ord(substring(code,2,1))) ) ) as code from t1; === Record 3, 5, and 7 is rejected due to duplicate value. ++--+--+ | id | length(code) | code | ++--+--+ | 1 |0 | | | 2 |1 | 0| | 4 |2 | 0 0 | | 6 |2 | 32 0 | ++--+--+ 4 rows in set (0.00 sec) It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. But why is record 3 rejected too? If I want to store 128-bit MD5 hash as a primary key, what would be the most compact way of storing them? -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
David Garamond wrote: insert into t1 values (3,char(32)); Record 3, 5, and 7 is rejected due to duplicate value. ++--+--+ | id | length(code) | code | ++--+--+ | 1 |0 | | | 2 |1 | 0| | 4 |2 | 0 0 | | 6 |2 | 32 0 | ++--+--+ 4 rows in set (0.00 sec) It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. But why is record 3 rejected too? Ugh, never mind. I forgot about the automatic trailing blanks removal feature. Well, since trailing blanks are always removed, it means CHAR/VARCHAR could never store binary data then... Is there a 128-bit datatype planned? It would be handy to store GUID/UUID or IPv6 addresses. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing binary data (string with nulls/ASCII 0 in them) in CHAR/VARCHAR?
In the last episode (Nov 29), David Garamond said: It seems the string in CHAR/VARCHAR is stored as null-terminated ala C. But why is record 3 rejected too? Ugh, never mind. I forgot about the automatic trailing blanks removal feature. Well, since trailing blanks are always removed, it means CHAR/VARCHAR could never store binary data then... Is there a 128-bit datatype planned? It would be handy to store GUID/UUID or IPv6 addresses. You can use a TINYBLOB, which will consume 9 bytes (due to the length byte), or you can use two BIGINTs. An interesting addition to mysql would be a fixed-length BLOB field which can only hold N characters. Might be more efficient than INTs for storing things like IP addresses, checksums, or other values that happen to be N bytes but aren't really numbers. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
putting binary data into a blob
I am trying to upload an icon from a client to a server and then to other clients over tcp/ip. the icon data will be stored in mysql on the server. the column is defined as a mediumblob field. once on the server, i escape the string and then put it into a mysql statement to insert it into the database, but only 10 or so of the 15000 characters are put in. code looks like this - stringstream query; char *iconNotMySQL=(char*)malloc(icon.length());// icon is a c++ stl string of the icons data char *iconMySQL=(char*)malloc((icon.length() * 2) + 1); memcpy(iconNotMySQL, icon.c_str(), icon.length()); mysql_real_escape_string(mysql, iconMySQL, iconNotMySQL, icon.length()); query UPDATE members SET email = \ email \, location = \ loc \, bio = \ bio \, showemail = \ showemail \, icon = \ iconMySQL \ WHERE username = \ name \; MaGmysql_query(query.str()); when I check the length of query.str() it looks exactly as it should, but not all the data is inserted into the database. can anyone help me out? thanks chris
binary data fields
are there any issues regarding special characters that should be escaped when storing binary data in a BLOB field? any special ascii codes that must be checked for before inserting data into a table? i remember reading something about ascii(26) causing some problems when using mysqldump and dumping back into a table. would this be related to the win32 version? abs Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compare longblob with binary data
hi all a question about checking the data of a longblob for equality. assume the `data` field is of type longblob. the following statement returns the image records with equal data. SELECT * FROM `image` WHERE `data`=? how does mysql performe the test. does it check the length of the blob before comparing the data, or makes it sense to add the length test as follows SELECT * FROM `image` WHERE LENGTH(`data`) = ? AND `data`=? any suggestions? best regard benny
Re[2]: Please help: Can not insert binary data larger than 16 megabytes
Brad, Edward, Try using 'max_allowed_packet=16M' instead of your current value. You may also find help by reviewing the below. http://www.mysql.com/doc/en/Packet_too_large.html That will set the communication buffer to a maximum of 16 MB, but Brad wants more than that ... From the manual: LONGBLOB LONGTEXT A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. Note that because the server/client protocol and MyISAM tables has currently a limit of 16M per communication packet / table row, you can't yet use this the whole range of this type. See section 6.2.3.2 The BLOB and TEXT Types. http://www.mysql.com/doc/en/Column_types.html Well, the manual says currently, and it means 3.23 versions. Another quote from the manual: max_allowed_packet The maximum size of one packet. The message buffer is initialised to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0. http://www.mysql.com/doc/en/SHOW_VARIABLES.html So Brad must use MySQL = 4.0.0, and add this to his configuration file to be able to store blobs up to 1 GB in size: max_allowed_packet=1G Is there a reason you are including the BLOBs in the database instead of just linking via it? I know it's generally considered better practice to do the latter. Like always, that's true. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Please help: Can not insert binary data larger than 16 megabytes
Stefan, That will set the communication buffer to a maximum of 16 MB, but Brad wants more than that ... I know. I was offering the notation, not the exact number. I thought it was implied, but I often take my implications for granted. protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0. ... So Brad must use MySQL = 4.0.0, and add this to his configuration file to be able to store blobs up to 1 GB in size: We apparently read the same sections of the manual. Brad stated in his original post that he is running 4.0.x so that shouldn't be an issue. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: Becoming Digital [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, 07 June, 2003 03:25 Subject: Re[2]: Please help: Can not insert binary data larger than 16 megabytes Brad, Edward, Try using 'max_allowed_packet=16M' instead of your current value. You may also find help by reviewing the below. http://www.mysql.com/doc/en/Packet_too_large.html That will set the communication buffer to a maximum of 16 MB, but Brad wants more than that ... From the manual: LONGBLOB LONGTEXT A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. Note that because the server/client protocol and MyISAM tables has currently a limit of 16M per communication packet / table row, you can't yet use this the whole range of this type. See section 6.2.3.2 The BLOB and TEXT Types. http://www.mysql.com/doc/en/Column_types.html Well, the manual says currently, and it means 3.23 versions. Another quote from the manual: max_allowed_packet The maximum size of one packet. The message buffer is initialised to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0. http://www.mysql.com/doc/en/SHOW_VARIABLES.html So Brad must use MySQL = 4.0.0, and add this to his configuration file to be able to store blobs up to 1 GB in size: max_allowed_packet=1G Is there a reason you are including the BLOBs in the database instead of just linking via it? I know it's generally considered better practice to do the latter. Like always, that's true. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help: Can not insert binary data larger than 16 megabytes
Hello -- I am having trouble inserting binary data that is larger than 16 megabytes into a MySQL database table (binary data such as pdf's, gif's, jpeg's, etc). Can someone take a look at what I am doing and give me any suggestions. If I do insert binary data larger than 16 megabytes I get the following error code: Error 2020 : Got packet bigger than 'max_allowed_packet'. However with MySQL version 4.0.1 and up I should be able to insert binary data larger than 16 megabytes. And if you look below at the my.cnf file I have max_allowed_packet set to 524288000. I am running MySQL version: mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Database table has the following fields: Binary_IdINT UNSIGNED NOT NULL AUTO_INCREMENT BinaryName VARCHAR(40) BinaryMIMEType VARCHAR(40) BinaryData LONGBLOB The my.cnf file in /etc looks like the following: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock key_buffer_size=500M max_allowed_packet=524288000 [mysql.server] user=mysql basedir=/var/lib key_buffer_size=500M max_allowed_packet=524288000 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=500M max_allowed_packet=524288000 [mysql.client] key_buffer_size=500M max_allowed_packet=524288000 Here is the funny thing. If I do a 'mysql --help' I get the following in the help data: max_allowed_packet 16777216. Why would I get this when I have set max_allowed_packet in my.cnf. Can anyone help steer me in the right direction? What am I doing wrong? --Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: Can not insert binary data larger than 16 megabytes
Try using 'max_allowed_packet=16M' instead of your current value. You may also find help by reviewing the below. http://www.mysql.com/doc/en/Packet_too_large.html Is there a reason you are including the BLOBs in the database instead of just linking via it? I know it's generally considered better practice to do the latter. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, 06 June, 2003 17:11 Subject: Please help: Can not insert binary data larger than 16 megabytes Hello -- I am having trouble inserting binary data that is larger than 16 megabytes into a MySQL database table (binary data such as pdf's, gif's, jpeg's, etc). Can someone take a look at what I am doing and give me any suggestions. If I do insert binary data larger than 16 megabytes I get the following error code: Error 2020 : Got packet bigger than 'max_allowed_packet'. However with MySQL version 4.0.1 and up I should be able to insert binary data larger than 16 megabytes. And if you look below at the my.cnf file I have max_allowed_packet set to 524288000. I am running MySQL version: mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Database table has the following fields: Binary_IdINT UNSIGNED NOT NULL AUTO_INCREMENT BinaryName VARCHAR(40) BinaryMIMEType VARCHAR(40) BinaryData LONGBLOB The my.cnf file in /etc looks like the following: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock key_buffer_size=500M max_allowed_packet=524288000 [mysql.server] user=mysql basedir=/var/lib key_buffer_size=500M max_allowed_packet=524288000 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid key_buffer_size=500M max_allowed_packet=524288000 [mysql.client] key_buffer_size=500M max_allowed_packet=524288000 Here is the funny thing. If I do a 'mysql --help' I get the following in the help data: max_allowed_packet 16777216. Why would I get this when I have set max_allowed_packet in my.cnf. Can anyone help steer me in the right direction? What am I doing wrong? --Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to fill an Blob with binary data
Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to fill an Blob with binary data
Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
I know MySQL CC can place a text or image blob into the database, might be worth a look. Mike Hillyer www.vbmysql.com -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 10:54 AM To: [EMAIL PROTECTED] Subject: How to fill an Blob with binary data Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Mascon can do it .. it's a win32 app.. On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
On 27 May 2003 at 19:10, Thomas Hoelsken wrote: isn't there any other solution instead of using PHP just for filling an Blob!? Of course there is, but if you need more specifics you need to give us more details. How are you executing your SQL statements? It's just a matter of getting your data from wherever it comes from, escaping it properly, and putting it into your INSERT or UPDATE. Whatever language you're using should be able to do that. Or maybe you're looking for the LOAD_FILE() function? http://www.mysql.com/doc/en/String_functions.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Thanks, FreeMascon is able to do it und it works fine. If anyone knows another solution, I'm interested in! Thomas --- _ / __// __// ___/Thomas Hoelsken [EMAIL PROTECTED] / /_ / __// /_ / IT-Coordinator /___//_/ // Operations Control FRA HE/I Condor Flugdienst GmbH Tel: +49-6107-939-8304 Am Gruenen Weg 3 Fax: +49-6107-939-145 65451 KelsterbachSITA: FRAH2DE GERMANY -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 7:14 PM To: Thomas Hoelsken Cc: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Mascon can do it .. it's a win32 app.. On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
In my case, its neither. Some random binary data. Could be ANY kind of file... (I'm doing mine programmatically). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
What is the source of the binary data you want to use to fill the blob? -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
In what language? Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:30 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In my case, its neither. Some random binary data. Could be ANY kind of file... (I'm doing mine programmatically). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Copied from my original message: I am writing a database that will contain a blob field for some binary data. My question is, what is the most efficient way to load this binary data in? I could turn it into a string and pass it into an INSERT/UPDATE statement, but I am afraid that problems will arise when it is converted into a string (with newlines, nulls, etc). I considered saving it to a file, and then using LOAD_FILE, but: a) that ALSO invovles turning it into a string, but since MySQL is doing it, it might end up OK. b) I'm not sure how the performance will be when I have to save it to a file, then have MySQL read it back in. The app is using the C++ API and I was wondering if there was any other way to directly 'stream' the data in? Thanks, Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:31 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In what language? Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:30 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In my case, its neither. Some random binary data. Could be ANY kind of file... (I'm doing mine programmatically). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Depending on size of data there are a few different methods... Just like what most people do.. use plain insert statements with the data properly escaped and shouldn't have any problem going in. Pulling data out is pretty quick .. I can stream binary data out of my mysql storage servers via our ftp gateway at speeds about 40MB/sec (~ 4000k/sec) which is pretty quick for the crappy development server I've got.. You mention streaming in.. I've got a ftpgateway to mysql storage that I use.. it's written in java.. but you could also implement one in C++ On Tue, 27 May 2003, Adam Clauss wrote: Copied from my original message: I am writing a database that will contain a blob field for some binary data. My question is, what is the most efficient way to load this binary data in? I could turn it into a string and pass it into an INSERT/UPDATE statement, but I am afraid that problems will arise when it is converted into a string (with newlines, nulls, etc). I considered saving it to a file, and then using LOAD_FILE, but: a) that ALSO invovles turning it into a string, but since MySQL is doing it, it might end up OK. b) I'm not sure how the performance will be when I have to save it to a file, then have MySQL read it back in. The app is using the C++ API and I was wondering if there was any other way to directly 'stream' the data in? Thanks, Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:31 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In what language? Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:30 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In my case, its neither. Some random binary data. Could be ANY kind of file... (I'm doing mine programmatically). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
Hello I prefer SQLyog at http://www.webyog.com/sqlyog Its FREE, extremely fast and very easy to use. Runs only on Windows though. Should give a try!!! Karam --- Thomas Hoelsken [EMAIL PROTECTED] wrote: Thanks, FreeMascon is able to do it und it works fine. If anyone knows another solution, I'm interested in! Thomas --- _ / __// __// ___/Thomas Hoelsken [EMAIL PROTECTED] / /_ / __// /_ / IT-Coordinator /___//_/ // Operations Control FRA HE/I Condor Flugdienst GmbH Tel: +49-6107-939-8304 Am Gruenen Weg 3 Fax: +49-6107-939-145 65451 KelsterbachSITA: FRAH2DE GERMANY -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 7:14 PM To: Thomas Hoelsken Cc: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Mascon can do it .. it's a win32 app.. On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to fill an Blob with binary data
OK question then - when I use LOAD_FILE, does it automatically put escape characters in front of anything that needs it? I am in development on an SMTP server with an SQL backend rather than simply a filesystem. My concern is regarding file attachments which could be pretty much anything imaginable. Adam Clauss [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:53 PM To: [EMAIL PROTECTED] Cc: Adam Clauss Subject: RE: How to fill an Blob with binary data Depending on size of data there are a few different methods... Just like what most people do.. use plain insert statements with the data properly escaped and shouldn't have any problem going in. Pulling data out is pretty quick .. I can stream binary data out of my mysql storage servers via our ftp gateway at speeds about 40MB/sec (~ 4000k/sec) which is pretty quick for the crappy development server I've got.. You mention streaming in.. I've got a ftpgateway to mysql storage that I use.. it's written in java.. but you could also implement one in C++ On Tue, 27 May 2003, Adam Clauss wrote: Copied from my original message: I am writing a database that will contain a blob field for some binary data. My question is, what is the most efficient way to load this binary data in? I could turn it into a string and pass it into an INSERT/UPDATE statement, but I am afraid that problems will arise when it is converted into a string (with newlines, nulls, etc). I considered saving it to a file, and then using LOAD_FILE, but: a) that ALSO invovles turning it into a string, but since MySQL is doing it, it might end up OK. b) I'm not sure how the performance will be when I have to save it to a file, then have MySQL read it back in. The app is using the C++ API and I was wondering if there was any other way to directly 'stream' the data in? Thanks, Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:31 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In what language? Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:30 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data In my case, its neither. Some random binary data. Could be ANY kind of file... (I'm doing mine programmatically). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:23 PM To: Adam Clauss; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Like I said, for Image and text blobs, use MyCC and it should work. Mike Hillyer www.vbmysql.com -Original Message- From: Adam Clauss [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 11:18 AM To: [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data I am in need of help with the same question (sent late last night, but apparently got passed over). Adam Clauss [EMAIL PROTECTED] -Original Message- From: Thomas Hoelsken [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 12:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How to fill an Blob with binary data Hi, isn't there any other solution instead of using PHP just for filling an Blob!? I don't need php and would prefer any other way! Thanks, Thomas -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: Re: How to fill an Blob with binary data Search the mailing list archives for this... There is a link to this article: http://www.php4.com/forums/viewtopic.php?t=6 I wonder if the mailinglist search was powered by google more people would use it? On Tue, 27 May 2003, Thomas Hoelsken wrote: Hi, I would like to fill an Blob with binary data thru SQL-statements or any other MySQL tools and don't know how!? Are there suggestions? Thanks, Thomas Hoelsken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http
RE: MySQL BLOB fields and large binary data
Hi, I'm not sure my reply will be of any use to you, but here it goes.. I've recently had problems with a perl driver (DBD::mysqlPP) with regards to, amongst other things, large BLOB data entries. My short-term work-around is simply to handle all BLOBs on the client side, i.e. split up all long queries into several small ones. This works for almost all types of statements. I know it's more or less obvious, and that you might think it's too impractical. My, smallish, experience does however say that it's not that much work and the performance impace, for me, isn't too great. Just a pointer.. -- Aigars -Original Message- From: Bruno Batarelo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 20:37 To: [EMAIL PROTECTED] Subject: MySQL BLOB fields and large binary data Greetings Seemingly MySQL has a persistant problem that I could not solve whatever I tried to do. It seems that it is impossible to send large amount of binary data to mysql's BLOB field. It is written in manual that 4.0x versions do not have limit for max_allowed_packet. It is also written that bugs regarding that problem are corrected in 4.08. However, I can not send a file to blob field that is larger than 4-5 MB. I use ADO 2.6 and recommended STREAM object for inserting binary data. max_allowed_packet is very large, 60-100MB, database has one table without records, MySQL is 4.08 with its 128 MB of ram, and other 128 is for windows 2000 system. Wait timeout is also very big. Table type is InnoDB. But error message persists: MySQL Server has gone away. MyODBC is 3.51.05. This is recent data regarding this problem in documentation: You will also get a lost connection if you are sending a packet = 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around. One thing that I do not understand is client's version. I presume that it referes to mysql.exe, but I access server using VB application and MyODBC. Has anyone solved this problem. It is, I'd say stupid, that these kind of things can not be done with such robust database system. I like MySQL very much, but my whole project depends on this situation and I'll seriously think about using some other RDBMS if I can't solve this problem. Thank you all in advance Bruno Croatia - 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
MySQL BLOB fields and large binary data
Greetings Seemingly MySQL has a persistant problem that I could not solve whatever I tried to do. It seems that it is impossible to send large amount of binary data to mysql's BLOB field. It is written in manual that 4.0x versions do not have limit for max_allowed_packet. It is also written that bugs regarding that problem are corrected in 4.08. However, I can not send a file to blob field that is larger than 4-5 MB. I use ADO 2.6 and recommended STREAM object for inserting binary data. max_allowed_packet is very large, 60-100MB, database has one table without records, MySQL is 4.08 with its 128 MB of ram, and other 128 is for windows 2000 system. Wait timeout is also very big. Table type is InnoDB. But error message persists: MySQL Server has gone away. MyODBC is 3.51.05. This is recent data regarding this problem in documentation: You will also get a lost connection if you are sending a packet = 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around. One thing that I do not understand is client's version. I presume that it referes to mysql.exe, but I access server using VB application and MyODBC. Has anyone solved this problem. It is, I'd say stupid, that these kind of things can not be done with such robust database system. I like MySQL very much, but my whole project depends on this situation and I'll seriously think about using some other RDBMS if I can't solve this problem. Thank you all in advance Bruno Croatia - 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 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
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
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
using Delphi with MySql to store binary data
Hi, I would like to know how to store binary data as images, sounds, Rich Text Format, pdf-files, as BLOB in MySQL. (from Delphi). Sincerely, Adrian Tarniceru --- Xnet scaneaza automat toate mesajele impotriva virusilor folosind RAV AntiVirus. Xnet automatically scans all messages for viruses using RAV AntiVirus. Nota: RAV AntiVirus poate sa nu detecteze toti virusii noi sau toate variantele lor. Va rugam sa luati in considerare ca exista un risc de fiecare data cand deschideti fisiere atasate si ca MobiFon nu este responsabila pentru nici un prejudiciu cauzat de virusi. Disclaimer: RAV AntiVirus may not be able to detect all new viruses and variants. Please be aware that there is a risk involved whenever opening e-mail attachments to your computer and that MobiFon is not responsible for any damages caused by viruses. - 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: using Delphi with MySql to store binary data
Hi, Read a blob field (containing any kind of data) from the database is easy. You have to do the same thing as in case of other field types. And if you use TBlobField field type (or dynamic typecast), this field type has a .SaveToStream (.SaveToFile) method. You can use this to save the data to anywhere. To write a blob field in a query, you should use parameters. For example: insert into Table values (:BlobValue) Then you have to set the value of this parameter like this: .ParamByName('BlobValue').LoadFromStream(SourceStream, ftBlob); This methods loads the content of SourceStream to the parameter from the cursor to the end of the stream. (you can use .LoadFromFile method the same way). Then you can just execute the query. Good luck, Daniel p.s.: You must do it the same way in case of any SQL servers. At 07:34 2002.11.28._+0200, you wrote: Hi, I would like to know how to store binary data as images, sounds, Rich Text Format, pdf-files, as BLOB in MySQL. (from Delphi). Sincerely, Adrian Tarniceru - 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
MySQL and binary data
I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! Steve Nakhla [EMAIL PROTECTED] __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.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: MySQL and binary data
- Original Message - From: Steven Nakhla [EMAIL PROTECTED] To: QT [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Monday, November 04, 2002 3:47 PM Subject: MySQL and binary data I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! I think you may be running into the max_allowed_packet size, which defaults to 1048576 (1M). Beyond that, the mysqld server issues a Packet too large error and closes the connection. Restart the server with the set-variable=max_allowed_packet=16M option, or any other value of your choice. - Mark - 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: MySQL and binary data
Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. You've hit the default maximum packet size of 1 MB. This is a protocol option - the MySQL client-server protocol limits itself to packets of a certain size. This is both a client and server option. You need to increase both. Here's what I have in my.cnf, in the [mysql] and [mysqld] sections: set-variable= max_allowed_packet=16M - 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: MySQL and binary data
The problem is that you are starting mysql with the wrong arguments. I think you need to set the max_allowed_packet parameter to something larger than the default 1 M. I got mine to do that by using this command: safe_mysqld --user=mysql -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M -Borries I am using Qt 3.0.5's QSql database classes to store information in a MySQL database. MySQL is at version 3.23.49. I am trying to store binary data into a column marked as MEDIUMBLOB (or even LONGBLOB) but am having difficulties. Whenever I insert data that is around 1 Megabyte in size, I get an error saying MySQL server has gone away. It doesn't happen with smaller data, and even if I change the column type to LONGBLOB I still have this problem. The data I am trying to insert is an exectuable application, although I have tried it with a normal text file and have run into the same problems. Can anyone shed some light onto what could be causing this problem? Thanks!! Steve Nakhla [EMAIL PROTECTED] __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ -- List archive and information: http://lists.trolltech.com/qt-interest/ - 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
Binary data dumps?
I've been using MySQL now for around a month. I also administer the system where our MySQL implementation is installed. Each night, we perform a full file system backup around 11:50PM. I just noticed that our MySQL root data directory is filling up with binary data files named so: `hostname`-bin.### So, for instance (our hostname is 'dewey'), I saw the following being deposited into our MySQL data dir: -rw-rw1 root root14909 Oct 7 23:50 dewey-bin.035 There are now 35 of these files sitting in the data dir, some of which take up an enormous amount of disk space. Are these data dumps of some sort? Does this happen every time MySQL shuts down? Should I be concerned about this? Should I save these files or delete them? I've checked the logs and MySQL seems to be shutting down just fine (no errors). However, these files are quickly eating away my disk space. A couple of them are over 500MB in size! If any one has any advice or information it would be appreciated. Thanks, Rob Robert Fox Sr. Programmer/Analyst University Libraries of Notre Dame (574)631-3353 [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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL-4.x GBK Problem with Binary Data
re-posting multibyte problem. does anyone have problem with binary data in MySQL-4.x using GBK charset or any other multibyte charset? inserting/updating/replacing the full description is in mysql mailing list archive, sent by me will not repost full desc until someone have or care this problem thx _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - 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: MySQL-4.x GBK Problem with Binary Data
Binary data is binary data. It doesn't mattrer whether it is GBK or and audio file, or a jpeg. If you escape the data properly before inserting it you will get back the same data you stored. Your application must display it properly. xuefer tinys wrote: re-posting multibyte problem. does anyone have problem with binary data in MySQL-4.x using GBK charset or any other multibyte charset? inserting/updating/replacing the full description is in mysql mailing list archive, sent by me will not repost full desc until someone have or care this problem thx _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - 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: MySQL-4.x GBK Problem with Binary Data
it's true that mysql-4.x process binary data well when not using multi-byte charset but mysql-4.x process it differently when using multi-byte it's only for GBK text escaping, which is not suitable for binary data that's the key point if u don't use multi-byte, and mysql-4.x, u will never have such problem From: Gerald Clark [EMAIL PROTECTED] To: xuefer tinys [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL-4.x GBK Problem with Binary Data Date: Fri, 20 Sep 2002 15:35:20 -0500 Binary data is binary data. It doesn't mattrer whether it is GBK or and audio file, or a jpeg. If you escape the data properly before inserting it you will get back the same data you stored. Your application must display it properly. xuefer tinys wrote: re-posting multibyte problem. does anyone have problem with binary data in MySQL-4.x using GBK charset or any other multibyte charset? inserting/updating/replacing the full description is in mysql mailing list archive, sent by me will not repost full desc until someone have or care this problem thx _ ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà MSN Messenger: http://messenger.microsoft.com/cn/ - 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 _ ÏíÓÃÊÀ½çÉÏ×î´óµÄµç×ÓÓʼþϵͳ¡ª MSN Hotmail¡£http://www.hotmail.com/cn - 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
Binary data mysqldump and restore.
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
FW: Binary data mysqldump and restore.
Just for those people who have the same problem: -Original Message- From: Mark Williams (MWP) [mailto:[EMAIL PROTECTED]] Sent: Monday, June 24, 2002 11:13 To: Bert VdB Subject: Re: Binary data mysqldump and restore. That did it thanks! MWP Hi, Have you tried making a dump with the --opt option ? CB. -Original Message- From: Mark Williams (MWP) [mailto:[EMAIL PROTECTED]] Sent: Monday, June 24, 2002 09:57 To: [EMAIL PROTECTED] Subject: Binary data mysqldump and restore. This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. - 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
Problem with corrupt binary data being returned from MySQL
Hello there, I have a problem that I need some help with. I am trying to insert binary data into a mysql database and simply retrieve it. each time I retrieve the data, it is corrupt. What am I doing wrong? I am running mysql 3.23.41 on Red Hat Linux release 7.2 (Enigma) Kernel 2.4.7-10 on an i686. My table is defined as: CREATE TABLE TEST(ID TINYINT(3),DATA LONGBLOB); To insert data into the table I am using: INSERT INTO TEST VALUES(1,LOAD_FILE('/tmp/test.gif')); The data has been added. I then retrieve the data like so: select data from test where id=1 into outfile /tmp/test2.gif; The returned file is no longer recognised as an image file by a web browser (Netscape). I have tried the same exercise with .mp3 files and have had the same problem. The files have the same size and do contain data but they are corrupt and cannot be read by the program that would read them. I also tried the same using a text file so that I could see what the difference was between the before and after inserting into mysql. See links below for before and after examples of text file and gif image. Gif image... http://www.toffeedog.com/personal/mysqlprob/before.gif http://www.toffeedog.com/personal/mysqlprob/after.gif Text file... http://www.toffeedog.com/personal/mysqlprob/before.txt http://www.toffeedog.com/personal/mysqlprob/after.txt I look forward to your reply, Miles mailto:[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
Re: Problem with corrupt binary data being returned from MySQL
The data isn't actually being corrupted. The problem here is that SELECT INTO OUTFILE wants to escape all of the line breaks as to make it easy to reimport the data using LOAD DATA INFILE. You could work around that by setting the escape character using: FIELDS ESCAPED BY ''; The total query would be: SELECT data FROM test WHERE id=1 INTO OUTFILE /tmp/test2.gif LINES ESCAPED BY ''; That seems to work pretty well for me.. However it seems to be adding an extra line break at the end of the file for some odd reason, not sure why. Most people usually will just select the data and then do whatever needs to be done in the language you are writing your application in. It would be like a 5 line script in either perl or php. Hope this helps you out some. Harrison Miles Martin wrote: Hello there, I have a problem that I need some help with. I am trying to insert binary data into a mysql database and simply retrieve it. each time I retrieve the data, it is corrupt. What am I doing wrong? I am running mysql 3.23.41 on Red Hat Linux release 7.2 (Enigma) Kernel 2.4.7-10 on an i686. My table is defined as: CREATE TABLE TEST(ID TINYINT(3),DATA LONGBLOB); To insert data into the table I am using: INSERT INTO TEST VALUES(1,LOAD_FILE('/tmp/test.gif')); The data has been added. I then retrieve the data like so: select data from test where id=1 into outfile /tmp/test2.gif; The returned file is no longer recognised as an image file by a web browser (Netscape). I have tried the same exercise with .mp3 files and have had the same problem. The files have the same size and do contain data but they are corrupt and cannot be read by the program that would read them. I also tried the same using a text file so that I could see what the difference was between the before and after inserting into mysql. See links below for before and after examples of text file and gif image. Gif image... http://www.toffeedog.com/personal/mysqlprob/before.gif http://www.toffeedog.com/personal/mysqlprob/after.gif Text file... http://www.toffeedog.com/personal/mysqlprob/before.txt http://www.toffeedog.com/personal/mysqlprob/after.txt I look forward to your reply, Miles mailto:[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: Problem with corrupt binary data being returned from MySQL
Err, I don't know what I was thinking. You can simply do a select into dumpfile like: select data from test where id=1 into dumpfile /tmp/test2.gif; That is designed for writing blobs to a file. Harrison Harrison C. Fisk wrote: The data isn't actually being corrupted. The problem here is that SELECT INTO OUTFILE wants to escape all of the line breaks as to make it easy to reimport the data using LOAD DATA INFILE. You could work around that by setting the escape character using: FIELDS ESCAPED BY ''; The total query would be: SELECT data FROM test WHERE id=1 INTO OUTFILE /tmp/test2.gif LINES ESCAPED BY ''; That seems to work pretty well for me.. However it seems to be adding an extra line break at the end of the file for some odd reason, not sure why. Most people usually will just select the data and then do whatever needs to be done in the language you are writing your application in. It would be like a 5 line script in either perl or php. Hope this helps you out some. Harrison Miles Martin wrote: Hello there, I have a problem that I need some help with. I am trying to insert binary data into a mysql database and simply retrieve it. each time I retrieve the data, it is corrupt. What am I doing wrong? I am running mysql 3.23.41 on Red Hat Linux release 7.2 (Enigma) Kernel 2.4.7-10 on an i686. My table is defined as: CREATE TABLE TEST(ID TINYINT(3),DATA LONGBLOB); To insert data into the table I am using: INSERT INTO TEST VALUES(1,LOAD_FILE('/tmp/test.gif')); The data has been added. I then retrieve the data like so: select data from test where id=1 into outfile /tmp/test2.gif; The returned file is no longer recognised as an image file by a web browser (Netscape). I have tried the same exercise with .mp3 files and have had the same problem. The files have the same size and do contain data but they are corrupt and cannot be read by the program that would read them. I also tried the same using a text file so that I could see what the difference was between the before and after inserting into mysql. See links below for before and after examples of text file and gif image. Gif image... http://www.toffeedog.com/personal/mysqlprob/before.gif http://www.toffeedog.com/personal/mysqlprob/after.gif Text file... http://www.toffeedog.com/personal/mysqlprob/before.txt http://www.toffeedog.com/personal/mysqlprob/after.txt I look forward to your reply, Miles mailto:[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 - 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: Extracting binary data from a row
All the numeric types are returned as strings. If you want to convert them to numbers, you need to use scanf or atod type functions. Michael Flora wrote: Hello all, this is my first post to this list. I am relatively new to MySQL, and my question might be a basic one. I have a table that contains both character and binary data. I am reading rows from the table using the function mysql_fetch rows() in a C program. I can obtain character data all right, but I cannot obtain the values of binary data stored in the table as type DOUBLE. My code looks like this: double dnum; . . . row = mysql_fetch_rows(result); dnum = *row[1]; /* The data is stored in the 2nd column of the table */ printf(%10.2f,dnum); When I execute this I get junk. I know from inspection that there are good values in the table. What am I doing wrong? Thanks for any advice, Michael Flora - 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: Problem with binary data in row
Michael Flora wrote: Hello all, I posted this question yesterday but received no feedback so I decided to ask again. I am new to MySQL and I am trying to access binary data stored in a MySQL table using a C program. The table looks like this: date date double firstfield double secondfield double thirdfield My C code looks like this: double first; double second; . . . row = mysql_fetch_row(result); first = *row[1]; . . . Since a row is supposed to be returned as an array of pointers I should be able to dereference to get the field value, right? However I get nothing but junk. What am I doing wrong? Thanks for any suggestions, Michael Flora Please see http://www.mysql.com/doc/C/_/C_API_datatypes.html mysql_fetch_row() returns type MYSQL_ROW, which is an array of byte-strings. You will need to use the C library function atod() to change it into a double. -Mark - 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: Problem with binary data in row
Hi, mysql_fetch_row returns a MYSQL_ROW, that is an array of byte strings. look at http://www.mysql.com/doc/C/_/C_API_datatypes.html Frederic - Original Message - From: Michael Flora [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 29, 2002 5:32 PM Subject: Problem with binary data in row Hello all, I posted this question yesterday but received no feedback so I decided to ask again. I am new to MySQL and I am trying to access binary data stored in a MySQL table using a C program. The table looks like this: date date double firstfield double secondfield double thirdfield My C code looks like this: double first; double second; . . . row = mysql_fetch_row(result); first = *row[1]; . . . Since a row is supposed to be returned as an array of pointers I should be able to dereference to get the field value, right? However I get nothing but junk. What am I doing wrong? Thanks for any suggestions, Michael Flora - 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
Extracting binary data from a row
Hello all, this is my first post to this list. I am relatively new to MySQL, and my question might be a basic one. I have a table that contains both character and binary data. I am reading rows from the table using the function mysql_fetch rows() in a C program. I can obtain character data all right, but I cannot obtain the values of binary data stored in the table as type DOUBLE. My code looks like this: double dnum; . . . row = mysql_fetch_rows(result); dnum = *row[1]; /* The data is stored in the 2nd column of the table */ printf(%10.2f,dnum); When I execute this I get junk. I know from inspection that there are good values in the table. What am I doing wrong? Thanks for any advice, Michael Flora - 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
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
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
Dumping binary data
If a table has a blob type containing binary data, like jpeg images or PDF files for example, can mysqldump be told to encode the data so it is ASCII compatible? (using uuencode or something like that..) I am not sure of the 'right' way to do this, but some informal tests where I dump and reload a table seems to corrupt the binary data. Thanks, -Bob - 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: How to load binary data with Perl DBI?
On Sun, Mar 10, 2002 at 09:59:09AM +0200, Toomas Vendelin wrote: It doesn't look same way :(. Normally I've read data from a file on a client's machine using Perl's read function like this while(read($file, $data, 1024)) { print TO $data; } to write it from $file on client's computer to a file associated with file handle TO. Now I obviously need to store the data in a variable. Just pushing $data strings into array and then joining (join('',@array) doesn't work, because it's not ASCII (file gets corrupted). So, what I do not understand, is how to open a filehandle to a database field and then print to it? :). Start with this: ---snip--- my $data; open FILE, $filename or die; { local($/) = undef; $data = file; } close FILE; ---snip--- Then just create a prepared statement handle with a place holder for the data and execute it, passing $data along the way. The Placeholders and Bind Values section of the DBI documentation explains how to do that. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 31 days, processed 1,033,853,920 queries (382/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
How to load binary data with Perl DBI?
Hello. How to load binary data into a table using Perl DBI module (the data is uploaded from a HTML form)? I failed to find any information on MySQL site. Thanks in advance. - 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: How to load binary data with Perl DBI?
At 16:39 +0200 3/9/02, [EMAIL PROTECTED] wrote: Hello. How to load binary data into a table using Perl DBI module (the data is uploaded from a HTML form)? Same way you load any other kind of data. Use placeholders or the quote() function. I failed to find any information on MySQL site. Thanks in advance. - 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[2]: How to load binary data with Perl DBI?
Hello Paul, Thank you for a quick reply. It doesn't look same way :(. Normally I've read data from a file on a client's machine using Perl's read function like this while(read($file, $data, 1024)) { print TO $data; } to write it from $file on client's computer to a file associated with file handle TO. Now I obviously need to store the data in a variable. Just pushing $data strings into array and then joining (join('',@array) doesn't work, because it's not ASCII (file gets corrupted). So, what I do not understand, is how to open a filehandle to a database field and then print to it? :). Regards, Tom Saturday, March 09, 2002, 9:00:08 PM, you wrote: PD At 16:39 +0200 3/9/02, [EMAIL PROTECTED] wrote: Hello. How to load binary data into a table using Perl DBI module (the data is uploaded from a HTML form)? PD Same way you load any other kind of data. Use placeholders or the quote() PD function. I failed to find any information on MySQL site. Thanks in advance. PD - PD Before posting, please check: PDhttp://www.mysql.com/manual.php (the manual) PDhttp://lists.mysql.com/ (the list archive) PD To request this thread, e-mail [EMAIL PROTECTED] PD To unsubscribe, e-mail [EMAIL PROTECTED] PD Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Best regards, Toomasmailto:[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
Storing Binary data in MySQL
Hello, I'm new to Unix and MySQL. I would like to store binary data in MySQL, but cannot find any sample code on how to go about doing this in C. The binary data is variable size ranging from 3k to 10k. I want to store it in a row along with a User ID so I can retrieve it by doing a query on this ID. How do I store and retrive this binary data in MySQL? I see references to BLOB and mysql_real_escape_string(). Am I on the right track? or is there more to it? Thanks. - 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
Binary data with embedded nulls (mysql_real_escape_string problem).
(The following was done in mysql_4.0 alpha) I am having difficulties with entering binary data from a c program. I have passed the string through mysql_real_escape_string. What I am seeing is that if the binary data contains an ascii 0, then mysql_real_escape_string, correctly, translates it to \0, but the data is still truncated in the database at that \0. In order to get the data in the database you need to double escape the null. You can reproduce the problem by running the following sql snippet using, mysql -D my_database -e source myfile.sql where myfile.sql contains, -- snip -- 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; -- -- snip By running this snippet, you will see that you need to double escape the null to get it in the database. But mysql_real_escape_string doesn't do this. Is this a problem, or am I missing something. Many regards, Ian Collins. - 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
problem of storing binary data in mysql
I use MySQL rpm to install in RedHat 7.2 in one of my pc. Then I store binary data such as image in MySQL and it works normally. However, I install source MySQL in RedHat 7.2 in another pc. But it cannot store binary data. Does anybody know the reason? Is that it need some special configure option when compiling MySQL from source? Thank you very much! __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.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
bulk insert / binary data
I'm doing just general feasibility studying of embedding MySQL in a networking product, and I have a question that I haven't been able to find the answer to after a fair bit of looking - is there some way to insert data that is already in INT UNSIGNED form directly into a table, without converting it into ASCII and then having it converted back again by the database? (I know I could use BLOB, but that would lose some of the benefits of integer-ness.) We would be using the C API. This product generates *large* volumes of data, which is currently all in UINT32 . It would seem to be a large waste of scarce CPU resources to have to convert every single column to and from ASCII. (the only issue I could see would be the endian-ness of it, but that could be managed.) Thanks for your help. - 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: bulk insert / binary data
At 6:59 PM -0700 10/23/01, Bob Purvy wrote: I'm doing just general feasibility studying of embedding MySQL in a networking product, and I have a question that I haven't been able to find the answer to after a fair bit of looking - is there some way to insert data that is already in INT UNSIGNED form directly into a table, without converting it into ASCII and then having it converted back again by the database? (I know I could use BLOB, but that would lose some of the benefits of integer-ness.) We would be using the C API. No. This product generates *large* volumes of data, which is currently all in UINT32 . It would seem to be a large waste of scarce CPU resources to have to convert every single column to and from ASCII. (the only issue I could see would be the endian-ness of it, but that could be managed.) Thanks for your help. -- Paul DuBois, [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
RE: Faliue: Write huge binary data to blob Field of mysql use jdbc
Thanks for your reply. Use database to restore file is the requrement of our project ,So I must find the way of problem. As you said ,I trust the problem is beacuse of mysql protocol that is the filed must be send as a single packet. I have same test in postgresql ,In postgresql the blob type is big object,with the same paramete (16 megs stack size)of jvm ,I can send 50 megs file to database! I think the mysql may be improve on this point. From: Mark Matthews [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: Faliue: Write huge binary data to blob Field of mysql use jdbc Date: Fri, 21 Sep 2001 08:14:45 -0500 Hi, I'm the author of the JDBC driver. I'm not sure what the problem is, but I am going to try and talk you out of storing multi-megabytes as blobs. Here's whyIn most cases it is more overhead than it is worth. If you're going to be serving the data from these blobs from some other type of server (HTTP/NFS, etc), then you've more than doubled your overhead with the over-the-wire costs. RDBMS systems are not optimized for storing and retrieving large binary data, filesystems are. What you probably want to do is generate some unique identifier for the file and store it on a filesystem that the software you are writing has access to. Use MySQL to store metadata about these files (their identifier, author, revision history, etc), and then use standard ways of providing the file (http/nfs/smb, etc). You will find this much more performant. If you still want to store large files in BLOBs, I would look at increasing your JVM heap size (by default it's around 16megs, the VM won't allocate more memory than that), using the -Xmx switch (see your JDK documentation). Because of the way the MySQL protocol works, the entire BLOB must be created in memory as a single packet to be sent to the server, so the driver needs at least the amount of memory as the size of your BLOB, plus some overhead for escaping binary characters, plus overhead for the driver itself. -Mark We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! _ Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ - 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: Faliue: Write huge binary data to blob Field of mysql use jdbc
Hi, I'm the author of the JDBC driver. I'm not sure what the problem is, but I am going to try and talk you out of storing multi-megabytes as blobs. Here's whyIn most cases it is more overhead than it is worth. If you're going to be serving the data from these blobs from some other type of server (HTTP/NFS, etc), then you've more than doubled your overhead with the over-the-wire costs. RDBMS systems are not optimized for storing and retrieving large binary data, filesystems are. What you probably want to do is generate some unique identifier for the file and store it on a filesystem that the software you are writing has access to. Use MySQL to store metadata about these files (their identifier, author, revision history, etc), and then use standard ways of providing the file (http/nfs/smb, etc). You will find this much more performant. If you still want to store large files in BLOBs, I would look at increasing your JVM heap size (by default it's around 16megs, the VM won't allocate more memory than that), using the -Xmx switch (see your JDK documentation). Because of the way the MySQL protocol works, the entire BLOB must be created in memory as a single packet to be sent to the server, so the driver needs at least the amount of memory as the size of your BLOB, plus some overhead for escaping binary characters, plus overhead for the driver itself. -Mark We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! - 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: Faliue: Write huge binary data to blob Field of mysql use jdbc
Glad to hear this. I was already going that way with my site because I didn't want to have to either drop and reload the database or take ther mysql server down to run the cleanup utility. W/o blobs/text you can use fixed length fields which means the database doesn't become fragmented. On Fri, 21 Sep 2001, Mark Matthews wrote: Date: Fri, 21 Sep 2001 08:14:45 -0500 From: Mark Matthews [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Faliue: Write huge binary data to blob Field of mysql use jdbc Hi, I'm the author of the JDBC driver. I'm not sure what the problem is, but I am going to try and talk you out of storing multi-megabytes as blobs. Here's whyIn most cases it is more overhead than it is worth. If you're going to be serving the data from these blobs from some other type of server (HTTP/NFS, etc), then you've more than doubled your overhead with the over-the-wire costs. RDBMS systems are not optimized for storing and retrieving large binary data, filesystems are. What you probably want to do is generate some unique identifier for the file and store it on a filesystem that the software you are writing has access to. Use MySQL to store metadata about these files (their identifier, author, revision history, etc), and then use standard ways of providing the file (http/nfs/smb, etc). You will find this much more performant. If you still want to store large files in BLOBs, I would look at increasing your JVM heap size (by default it's around 16megs, the VM won't allocate more memory than that), using the -Xmx switch (see your JDK documentation). Because of the way the MySQL protocol works, the entire BLOB must be created in memory as a single packet to be sent to the server, so the driver needs at least the amount of memory as the size of your BLOB, plus some overhead for escaping binary characters, plus overhead for the driver itself. -Mark We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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: Faliue: Write huge binary data to blob Field of mysql use jdbc
Hi ? ? -Original Message- From: ? ? [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 12:12 PM To: [EMAIL PROTECTED] Subject: Faliue: Write huge binary data to blob Field of mysql use jdbc Hi all: We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! try setting 'set-variable=max_allowed_packet=20M' in my.cnf (take a look what's the current value with 'show variables') Andy - 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: Faliue: Write huge binary data to blob Field of mysql use jdbc
Thanks! But my setting is max_allowed_packet=50M,I have changed the setting before I send the help. Someone tell me It may be the jdbc problem or the stack value of jvm is too small. I'm very puzzle ! From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: 'Àî Çà' [EMAIL PROTECTED] CC: mysql mailing list (E-Mail) [EMAIL PROTECTED] Subject: RE: Faliue: Write huge binary data to blob Field of mysql use jdbc Date: Thu, 20 Sep 2001 16:29:27 +0200 Hi ? ? -Original Message- From: ? ? [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 19, 2001 12:12 PM To: [EMAIL PROTECTED] Subject: Faliue: Write huge binary data to blob Field of mysql use jdbc Hi all: We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! try setting 'set-variable=max_allowed_packet=20M' in my.cnf (take a look what's the current value with 'show variables') Andy _ Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ - 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
Faliue: Write huge binary data to blob Field of mysql use jdbc
Hi all: We are making a project about document manage ,I use org.gjt.mm.mysql as jdbc to communicate with mysql. I can succeed in writing the binary data (the size about 14M) to database ,but when I write the binary data that's size exceed 20M I get a Exception. Please help me! Thanks! The Exception : Error:java.sql.SQLException: Communication link failure: java.net.SocketExceptio n java.sql.SQLException: Communication link failure: java.net.SocketException at org.gjt.mm.mysql.MysqlIO.sendCommand(MysqlIO.java:459) at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(MysqlIO.java:550) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:885) at org.gjt.mm.mysql.PreparedStatement.execute(PreparedStatement.java:109 3) at com.dextra.java.unijdbc.UniJdbc.exePrepareStatement(UniJdbc.java:312) at com.dextra.java.unijdbc.UniJdbc.main(UniJdbc.java:970) _ Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ - 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
Binary Data storage and Retrieval Problem
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
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: Inserting binary data into a BLOB field
At 08:00 AM 6/16/01 +, you wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query,table If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. You have written the following: Can anyone tell me if the storage size for a blob is the actual raw binary data storage space available (ie. not escaped), or the escaped (ie. including escape chars) size of the variable submitted in INSERT ? Please reply to: [EMAIL PROTECTED] Thanks, Luci - 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
Best field type for binary data
Hello List, I'm going to be storing a LOT of MP3's in a MySQL db, what's the best field type to use? Blob? Thanks, Tyler - 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: advice sought on using a select to extract binary data
On 3/27/01 4:54 PM, "WeAreUs" [EMAIL PROTECTED] wrote: Hi! I enter some .wav encoded (windows) data into a MySql table as follows: $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data))); $result=MYSQL_QUERY("INSERT INTO b(audio,filename,filesize,filetype,audio_description) ". "VALUES ('$data','$form_data_name','$form_data_size','$form_data_type', '$audio_description')"); $uid= mysql_insert_id(); Later, I try to reconstruct the original audio file as follows: $query = mysql_query("select audio from dupe_b where uid='87' into outfile 'c:/windows/desktop/data.wav' "); This does not yield the correct format. I take it that I require "fields terminated by" or something like that to make this work. However, I cannot get any further with this. Any suggestions? Thanks for the time! Have you tried putting the returned value into a variable, opening up a file for writing(as binary) In php (which it looks like you are using) $fh=fopen('newfile.wav','wb'); fputs($fh,$fielddata); fclose($fh); I think that selecting into an outfile does some textencoding and newline conversion type stuff (just guessing). The above is the way I would do it, if I had to put my files into a db. I would rather store my files, as files, in a directory, and store the meta information (file location, name,size,description,copyright whatever) in the database. It is so much easier to have the file path/name inserted into the html, than to pull all the data out, create the file, insert the filename, server up the html. - 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