blob data types
I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. Geetanjali Mehra Oracle and MySQL DBA Corporate Trainer
Re: blob data types
Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type signature.asc Description: OpenPGP digital signature
Re: blob data types
Is it possible to move blob data type values out of table and keep it in separate page, keeping BLOB the part of the table. Geetanjali Mehra Oracle and MySQL DBA Corporate Trainer On Mon, May 26, 2014 at 3:26 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type
Re: blob data types
Hello, I can see MyISAM stores BLOB column as same space as other data type column, but InnoDB doesn't. (If you mean same .ibd file it's true) http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html ROW_FORMAT= Compact holds first 768 bytes of BLOB column, ROW_FORMAT= Dynamic or Compressed holds first 20bytes of column. http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-internals-storage-blobs.html 2014-05-26 18:56 GMT+09:00 Reindl Harald h.rei...@thelounge.net: Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type
Re: blob data types
just don't store large binary data in tables save the files somewhere in the application and keep only references to the files in the database Am 26.05.2014 12:11, schrieb geetanjali mehra: Is it possible to move blob data type values out of table and keep it in separate page, keeping BLOB the part of the table. On Mon, May 26, 2014 at 3:26 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type signature.asc Description: OpenPGP digital signature
Re: blob data types
- Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Monday, 26 May, 2014 11:56:26 AM Subject: Re: blob data types Am 26.05.2014 11:40, schrieb geetanjali mehra: I want to know where does MyISAM and innodb stores its BLOB data ; inside the table or outside the table. I tried to understand BLOB using MySQL online docs but failed. inside the table, it's just a field type Didn't InnoDB store them out of line (but still inside the datafile, of course) ? I might be remembering wrong, though. You could design your application to store blobs in files instead; but you preferably also need to use redundant/shared storage, then. It's a common enough design, but it needs though and understanding :-) As a tangential thought, there was also a thirdparty plugin engine at some point that streamed blobs outside of the normal MySQL protocol. Can't remember the name. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: BLOB data gets encoded as utf8!
Thank you for your reply, Janusz.
I appreciate your help.
I have tried making that call before the INSERT statement, but to no avail.
The table collation is set to utf8 - default collation, and all columns are
set to Table default.
I am thinking that this problem might be due to me sending the BLOB data in a
unicode string to the server, i.e:
INSERT INTO TableName (Rawfield) VALUES ('%s'); - whereas the %s string is a
unicode string
That string is encoded by escaping mysql characters according to
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html (0x00, 0x22, 0x27,
0x5C).
Then each byte (and each escaped byte) is followed by a 0x00 before being sent
to the server, just so that I can send it as a unicode string via ODBC.
I.e. the binary data FA 5C 93 A4 would be expanded into FA 00 5C 00 5C 00 93 00
A4 00
Otherwise I can't send the data in a unicode INSERT statement via ODBC from C++
/ MFC.
Do you think that could be the issue? If so, do you have a suggestion on how to
do it better?
Thank you again for your help!
Sincerely,
Andreas Iwanowski
-Original Message-
From: Janusz Paśkiewicz [mailto:ad...@multipasko.pl]
Sent: Wednesday, February 09, 2011 7:45 AM
To: Andreas Iwanowski
Subject: BLOB data gets encoded as utf8! (Anyone?)
Before inserting BLOB data use:
mysql_query(SET CHARACTER SET latin1);
after that you can set it back to:
mysql_query(SET CHARACTER SET utf8);
This is due to:
http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html
If the column has a binary data type (BINARY
http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html , VARBINARY
http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html , BLOB
http://dev.mysql.com/doc/refman/5.1/en/blob.html ), all the values that it
contains must be encoded using a single character set (the character set you're
converting the column to). If you use a binary column to store information in
multiple character sets, MySQL has no way to know which values use which
character set and cannot convert the data properly.
Kind Regards,
Janusz Paskiewicz
www.freelancer4u.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: BLOB data gets encoded as utf8!
I can't help but wonder, if you send a string, does that mean you're putting
text in a blob ? Blobs are binary, and thus don't get encoded in the sense
of UTF8 vs Unicode. For a string, you may want a TEXT type column.
On the other hand, if you're indeed trying to insert binary data, it is not
the best of ideas to insert it into an SQL string, where it will be subject
to UTF8 or whatever interpretation.
For large objects, and generally with repeating statements too, it's best to
use bind variables to send your data. That also removes the need to worry
about escaping, sql insertion and the like.
2011/2/10 Andreas Iwanowski namez...@afim.info
Thank you for your reply, Janusz.
I appreciate your help.
I have tried making that call before the INSERT statement, but to no avail.
The table collation is set to utf8 - default collation, and all columns
are set to Table default.
I am thinking that this problem might be due to me sending the BLOB data in
a unicode string to the server, i.e:
INSERT INTO TableName (Rawfield) VALUES ('%s'); - whereas the %s string is
a unicode string
That string is encoded by escaping mysql characters according to
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html (0x00, 0x22,
0x27, 0x5C).
Then each byte (and each escaped byte) is followed by a 0x00 before being
sent to the server, just so that I can send it as a unicode string via ODBC.
I.e. the binary data FA 5C 93 A4 would be expanded into FA 00 5C 00 5C 00
93 00 A4 00
Otherwise I can't send the data in a unicode INSERT statement via ODBC from
C++ / MFC.
Do you think that could be the issue? If so, do you have a suggestion on
how to do it better?
Thank you again for your help!
Sincerely,
Andreas Iwanowski
-Original Message-
From: Janusz Paśkiewicz [mailto:ad...@multipasko.pl]
Sent: Wednesday, February 09, 2011 7:45 AM
To: Andreas Iwanowski
Subject: BLOB data gets encoded as utf8! (Anyone?)
Before inserting BLOB data use:
mysql_query(SET CHARACTER SET latin1);
after that you can set it back to:
mysql_query(SET CHARACTER SET utf8);
This is due to:
http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html
If the column has a binary data type (BINARY
http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html , VARBINARY
http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html , BLOB
http://dev.mysql.com/doc/refman/5.1/en/blob.html ), all the values that
it contains must be encoded using a single character set (the character set
you're converting the column to). If you use a binary column to store
information in multiple character sets, MySQL has no way to know which
values use which character set and cannot convert the data properly.
Kind Regards,
Janusz Paskiewicz
www.freelancer4u.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Re: BLOB data gets encoded as utf8! (Anyone?)
I think that probably the mistake is in the way you are inserting the
blob.
If i understand right, you are inserting the blob with a traditional
insert statement ??, in my experience i always use prepared statements
to insert blobs, in that way the text goes exactly as the source without
any utf-8 conversion.
Besides this, using the insert can truncate your blob / statement,
because some drivers/connectors limit the size of the sql statement, so
if you are uploading a huge file the statement will not work (I dont
know if this happens with the mysql driver or if there is a limit), so i
strongly recommend using prepared statements for blobs with any database.
Hope this helps.
Carlos Proal
On 8/28/2010 1:04 AM, Andreas Iwanowski wrote:
Has no one encountered this before? There has got to be a solution, and
I still haven't found it... So if you have any input please let me know!
-Original Message-
From: Andreas Iwanowski [mailto:namez...@afim.info]
Sent: Tuesday, August 24, 2010 2:48 PM
To: mysql@lists.mysql.com
Subject: BLOB data gets encoded as utf8!
Hello everyone!
I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
character_set_connection is set to utf8 (Which I believe is the default
for the driver) One of the tables contains two LONGBLOB columns, and the
table default charset is utf-8 (since the application is unicode).
However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.
My insert statement (simplified) does this:
INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');
I have also tried the _binary introducer
(http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html)
INSERT INTO _table_ (Desc, Data) VALUES ('...', _binary'_blobdata_');
I'm escaping 0x00, 0x22, 0x27, and 0x5C, which are the only four that
MySQL requires to be escaped per documentation for BLOB fields, and
according to the MySQL docs columns of any BLOB type use no character
set.
Here is the problem: I found that, for example, 0xFF gets modified to
0xC3BF, which is in fact the UTF8 encoding for ASCII/Binary 0xFF.
Needless to say the data becomes useless. I did, however, determine that
the data is already corrupted in the myodbc.log file that the driver
outputs if the option is set, so there is likely a problem with the
driver settings or with the statement itself.
I cannot be the first one to encounter this issue, so maybe if you have
an idea (or a solution would be even better! :) then please let me know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=namez...@afim.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: BLOB data gets encoded as utf8! (Anyone?)
Is the code you use to get the data out in the same charset as the code you
use to put the data in ? Both should ideally also match your database
setting. Have you tried explicitly setting the connection to UTF8 ?
Just swinging in the dark, here, really.
On Sat, Aug 28, 2010 at 8:04 AM, Andreas Iwanowski namez...@afim.infowrote:
Has no one encountered this before? There has got to be a solution, and
I still haven't found it... So if you have any input please let me know!
-Original Message-
From: Andreas Iwanowski [mailto:namez...@afim.info]
Sent: Tuesday, August 24, 2010 2:48 PM
To: mysql@lists.mysql.com
Subject: BLOB data gets encoded as utf8!
Hello everyone!
I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
character_set_connection is set to utf8 (Which I believe is the default
for the driver) One of the tables contains two LONGBLOB columns, and the
table default charset is utf-8 (since the application is unicode).
However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.
My insert statement (simplified) does this:
INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');
I have also tried the _binary introducer
(http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html)
INSERT INTO _table_ (Desc, Data) VALUES ('...', _binary'_blobdata_');
I'm escaping 0x00, 0x22, 0x27, and 0x5C, which are the only four that
MySQL requires to be escaped per documentation for BLOB fields, and
according to the MySQL docs columns of any BLOB type use no character
set.
Here is the problem: I found that, for example, 0xFF gets modified to
0xC3BF, which is in fact the UTF8 encoding for ASCII/Binary 0xFF.
Needless to say the data becomes useless. I did, however, determine that
the data is already corrupted in the myodbc.log file that the driver
outputs if the option is set, so there is likely a problem with the
driver settings or with the statement itself.
I cannot be the first one to encounter this issue, so maybe if you have
an idea (or a solution would be even better! :) then please let me know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=namez...@afim.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
BLOB data gets encoded as utf8! (Anyone?)
Has no one encountered this before? There has got to be a solution, and
I still haven't found it... So if you have any input please let me know!
-Original Message-
From: Andreas Iwanowski [mailto:namez...@afim.info]
Sent: Tuesday, August 24, 2010 2:48 PM
To: mysql@lists.mysql.com
Subject: BLOB data gets encoded as utf8!
Hello everyone!
I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
character_set_connection is set to utf8 (Which I believe is the default
for the driver) One of the tables contains two LONGBLOB columns, and the
table default charset is utf-8 (since the application is unicode).
However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.
My insert statement (simplified) does this:
INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');
I have also tried the _binary introducer
(http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html)
INSERT INTO _table_ (Desc, Data) VALUES ('...', _binary'_blobdata_');
I'm escaping 0x00, 0x22, 0x27, and 0x5C, which are the only four that
MySQL requires to be escaped per documentation for BLOB fields, and
according to the MySQL docs columns of any BLOB type use no character
set.
Here is the problem: I found that, for example, 0xFF gets modified to
0xC3BF, which is in fact the UTF8 encoding for ASCII/Binary 0xFF.
Needless to say the data becomes useless. I did, however, determine that
the data is already corrupted in the myodbc.log file that the driver
outputs if the option is set, so there is likely a problem with the
driver settings or with the statement itself.
I cannot be the first one to encounter this issue, so maybe if you have
an idea (or a solution would be even better! :) then please let me know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=namez...@afim.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
BLOB data gets encoded as utf8!
Hello everyone!
I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
One of the tables contains two LONGBLOB columns, and the table default
charset is utf-8 (since the application is unicode).
However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.
My insert statement (simplified) does this:
INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');
I have also tried:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
BLOB data gets encoded as utf8! (First post incomplete)
Hello everyone!
I am using an MFC unicode project that uses ODBC to access a MySQL
5.1.50 database via the MySQL ODBC 5.1.6 driver.
character_set_connection is set to utf8 (Which I believe is the default
for the driver)
One of the tables contains two LONGBLOB columns, and the table default
charset is utf-8 (since the application is unicode).
However, when inserting into the LONGBLOB columns via an INSERT
statement, the data gets corrupted/modified because is incorrectly UTF-8
encoded.
My insert statement (simplified) does this:
INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_');
I have also tried the _binary introducer
(http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html)
INSERT INTO _table_ (Desc, Data) VALUES ('...', _binary'_blobdata_');
I'm escaping 0x00, 0x22, 0x27, and 0x5C, which are the only four that
MySQL requires to be escaped per documentation for BLOB fields, and
according to the MySQL docs columns of any BLOB type use no character
set.
Here is the problem: I found that, for example, 0xFF gets modified to
0xC3BF, which is in fact the UTF8 encoding for ASCII/Binary 0xFF.
Needless to say the data becomes useless. I did, however, determine that
the data is already corrupted in the myodbc.log file that the driver
outputs if the option is set, so there is likely a problem with the
driver settings or with the statement itself.
I cannot be the first one to encounter this issue, so maybe if you have
an idea (or a solution would be even better! :) then please let me know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Insert blob data using prepared statements
On 7/26/2010 2:30 AM, Manasi Save wrote: Hi All, I need to insert Blob data in my table using prepared statements. But Whenever I try to insert it using prepared statement it is giving me mysql syntax error. Here's the prepared statement :- SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID) Select ','',Inputdata,'',',',InputID,';'); Prepare stmt1 From @stmt; Execute stmt1; Deallocate prepare stmt1; The executing statement looks like this :- Insert into `mydb`.MyTable(MyData, MyID) Select ** STREAM DATA **, 1; This gives me an error saying mysql syntax near ** STREAM DATA.. Can anyone give me any example how to insert blob data in database with prepared statement. First, have you tried using INSERT ... VALUES ... instead of INSERT ... SELECT ... ? Second, have you tried passing the STREAM data into the EXECUTE command as a parameter? One of the nice things about prepared statements is their ability to substitute data into the statement at runtime. For example, your statement could be 'INSERT INTO `mydb`.MyTable(MyID, MyDATA) VALUES (?,?)' and your execute could be EXECUTE stmt1 (1, 'stream data'); Depending on how you connect, you may also be able to bind one of those ? parameters to a variable in your code. That would completely eliminate the need to copy and escape your data into a quoted string literal. Third, you must always be aware of the max_allowed_packet size for the connection you are on. If you attempt to send a command larger than that size, the server will forcibly disconnect your session under the impression that you are attempting to sabotage the machine by sending queries that are too large. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Insert blob data using prepared statements
Hi All, I need to insert Blob data in my table using prepared statements. But Whenever I try to insert it using prepared statement it is giving me mysql syntax error. Here's the prepared statement :- SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID) Select ','"',Inputdata,'"',',',InputID,';'); Prepare stmt1 From @stmt; Execute stmt1; Deallocate prepare stmt1; The executing statement looks like this :- Insert into `mydb`.MyTable(MyData, MyID) Select ** STREAM DATA **, 1; This gives me an error saying mysql syntax near ** STREAM DATA.. Can anyone give me any example how to insert blob data in database with prepared statement. Thanks in advance. --Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
does ado/myodbc not support blob data insert in vc6?
hi,all!
ENV: mysql server 5.1, myodbc 5.1.5, vc 6.0, I use ado to operate
mysql. I found it can't work to insert a blob data to mysql server. And it is
no problem to get the blob data from mysql server. what's the matter?
I used the source code of MSDN about AppendChunk and GetChunk Methods
Example (VC++) http://msdn.microsoft.com/en-us/library/ms676103(VS.85).aspx
The connection to mysql server was changed by following:
_bstr_t strCnn(DRIVER={MySQL ODBC 5.1 Driver};
SERVER=localhost;DATABASE=test; USER=root; PASSWORD=111;OPTION=3;);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
does ado/myodbc not support blob data insert in vc6?
hi,all!
ENV: mysql server 5.1, myodbc 5.1.5, vc 6.0, I use ado to operate
mysql. I found it can't work to insert a blob data to mysql server. And it is
no problem to get the blob data from mysql server. what's the matter?
I used the source code of MSDN about AppendChunk and GetChunk Methods
Example (VC++) http://msdn.microsoft.com/en-us/library/ms676103(VS.85).aspx
The connection to mysql server was changed by following:
_bstr_t strCnn(DRIVER={MySQL ODBC 5.1 Driver};
SERVER=localhost;DATABASE=test; USER=root; PASSWORD=111;OPTION=3;);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problem with BLOB data.
Hi, I have a problem with the migration of a table that has a column with BLOB data. The source server is MySQL version: 4.0.16 and the destination server is MySQL version: 5.0.45 I tried with mysqldump and SELECT INTO but when import the data on the destination server, the BLOB data are corrupt. Someone can help me? Thanks -- Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma tel:0657060500 email:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blob data
I gave up on putting large blobs in Mysql -- too many limits around 16MB. Instead I broke blobs into pieces, inserting them with a sequence number. Added benefit: Does not clog up replication while huge single-insert is being copied over network and reexecuted on slaves. -Original Message- From: Paul McCullagh [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 27, 2007 2:57 AM To: Ann W. Harrison Cc: MySQL List; MySQL Internal Subject: Re: Blob data Hi Ann, Currently, the thoughts on how to make the BLOB references secure go like this: The BLOB reference consists of 2 components: The first component is basically an index used to find the BLOB on the server. The second component is a random number generated when the BLOB is created. The random number acts as an authorization code, and is checked when the BLOB is requested. So if the authorization code supplied in the BLOB reference does not match the code stored by the server for that BLOB, then the BLOB is not returned. If the authorization code is a 4-byte number, then the chances of getting the correct code for any particular BLOB is 1 in 4 billion. This makes it practically impossible to discover a BLOB by generating BLOB references and requesting them from the server. However, it does mean that once you have a valid BLOB reference it remains valid until the BLOB is deleted. So you can pass it around to your friends, or post it on the internet if you like. In order to prevent this (it will depend on the site, as to whether this is required), it would be possible to add a dynamic component to the BLOB reference which has a certain lifetime (for example, it expires after a certain amount of time, or when a database session is closed). Such a component would have to be added to the BLOB reference URL by the storage engine on the fly. So, as the SELECT result is being generated, the dynamic component is added to the BLOB references returned in the rowset. Security of the BLOB streaming stuff is one of the major issues, so further comments, questions and ideas are welcome! Best regards, Paul On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: Paul McCullagh wrote: It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). How does this work with access privileges? Can you just send random numbers in the URL until you start seeing blob data? Best regards, Ann -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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: Blob data
Rick James wrote: Instead I broke blobs into pieces, inserting them with a sequence number. Understanding the underlying problem, that still seems like an unnatural way to store pictures and documents. Added benefit: Does not clog up replication while huge single-insert is being copied over network and reexecuted on slaves. The design of blobs that Jim did at DEC included the ability to send them across the network in chunks of a client specified size. In 1982 it was quite common to have blobs that were larger than physical memory. What he did more recently was add a blob repository separate from the active tablespace that allowed the backup function to skip unchanged blobs while backing up active data. It also allows replicants to share a single copy of blobs, if appropriate. There are lots of ways of making large blobs work better in relational databases. Regards, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Blob data
Rick is dead on correct, I call I chunking blob data.. There is an article here on a simple implementation: http://www.dreamwerx.net/phpforum/?id=1 I've had hundreds of thousands of files in this type of storage before with no issues. On Tue, 3 Jul 2007, Rick James wrote: I gave up on putting large blobs in Mysql -- too many limits around 16MB. Instead I broke blobs into pieces, inserting them with a sequence number. Added benefit: Does not clog up replication while huge single-insert is being copied over network and reexecuted on slaves. -Original Message- From: Paul McCullagh [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 27, 2007 2:57 AM To: Ann W. Harrison Cc: MySQL List; MySQL Internal Subject: Re: Blob data Hi Ann, Currently, the thoughts on how to make the BLOB references secure go like this: The BLOB reference consists of 2 components: The first component is basically an index used to find the BLOB on the server. The second component is a random number generated when the BLOB is created. The random number acts as an authorization code, and is checked when the BLOB is requested. So if the authorization code supplied in the BLOB reference does not match the code stored by the server for that BLOB, then the BLOB is not returned. If the authorization code is a 4-byte number, then the chances of getting the correct code for any particular BLOB is 1 in 4 billion. This makes it practically impossible to discover a BLOB by generating BLOB references and requesting them from the server. However, it does mean that once you have a valid BLOB reference it remains valid until the BLOB is deleted. So you can pass it around to your friends, or post it on the internet if you like. In order to prevent this (it will depend on the site, as to whether this is required), it would be possible to add a dynamic component to the BLOB reference which has a certain lifetime (for example, it expires after a certain amount of time, or when a database session is closed). Such a component would have to be added to the BLOB reference URL by the storage engine on the fly. So, as the SELECT result is being generated, the dynamic component is added to the BLOB references returned in the rowset. Security of the BLOB streaming stuff is one of the major issues, so further comments, questions and ideas are welcome! Best regards, Paul On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: Paul McCullagh wrote: It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). How does this work with access privileges? Can you just send random numbers in the URL until you start seeing blob data? Best regards, Ann -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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: Blob data
And while you are at it, you may as well compress the chunks. You machine probably can compress/uncompress faster than it can write/read disk. I use Perl's Zlib::Compress or PHP's equivalent instead of Mysql's function for 2 reasons: * The network traffic is compressed. * Mysql puts an unnecessary extra byte on end of the string (ok, this is totally insignificant) And definitely compress each chunk separately. It seems that those library routines slow down (excessive memory realloc??) after about 50K. That is, you can probably compress 20 50K chunks faster than 1 1M chunk. My implementation did File - Database -- the huge blob was never instantiated completely in RAM, only one chunk at a time. (Imagine trying to store a 50GB file.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 03, 2007 2:08 PM To: Rick James Cc: 'Paul McCullagh'; 'Ann W. Harrison'; 'MySQL List' Subject: RE: Blob data Rick is dead on correct, I call I chunking blob data.. There is an article here on a simple implementation: http://www.dreamwerx.net/phpforum/?id=1 I've had hundreds of thousands of files in this type of storage before with no issues. On Tue, 3 Jul 2007, Rick James wrote: I gave up on putting large blobs in Mysql -- too many limits around 16MB. Instead I broke blobs into pieces, inserting them with a sequence number. Added benefit: Does not clog up replication while huge single-insert is being copied over network and reexecuted on slaves. -Original Message- From: Paul McCullagh [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 27, 2007 2:57 AM To: Ann W. Harrison Cc: MySQL List; MySQL Internal Subject: Re: Blob data Hi Ann, Currently, the thoughts on how to make the BLOB references secure go like this: The BLOB reference consists of 2 components: The first component is basically an index used to find the BLOB on the server. The second component is a random number generated when the BLOB is created. The random number acts as an authorization code, and is checked when the BLOB is requested. So if the authorization code supplied in the BLOB reference does not match the code stored by the server for that BLOB, then the BLOB is not returned. If the authorization code is a 4-byte number, then the chances of getting the correct code for any particular BLOB is 1 in 4 billion. This makes it practically impossible to discover a BLOB by generating BLOB references and requesting them from the server. However, it does mean that once you have a valid BLOB reference it remains valid until the BLOB is deleted. So you can pass it around to your friends, or post it on the internet if you like. In order to prevent this (it will depend on the site, as to whether this is required), it would be possible to add a dynamic component to the BLOB reference which has a certain lifetime (for example, it expires after a certain amount of time, or when a database session is closed). Such a component would have to be added to the BLOB reference URL by the storage engine on the fly. So, as the SELECT result is being generated, the dynamic component is added to the BLOB references returned in the rowset. Security of the BLOB streaming stuff is one of the major issues, so further comments, questions and ideas are welcome! Best regards, Paul On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: Paul McCullagh wrote: It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). How does this work with access privileges? Can you just send random numbers in the URL until you start seeing blob data? Best regards, Ann -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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: Blob data
Interesting, never tried compressing the data, sounds like that might be a nice addon.. Do you have any performance numbers you can share? I posted some performance numbers on one of my implementations some time ago. I found the thread here: http://lists.mysql.com/mysql/206337 On Tue, 3 Jul 2007, Rick James wrote: And while you are at it, you may as well compress the chunks. You machine probably can compress/uncompress faster than it can write/read disk. I use Perl's Zlib::Compress or PHP's equivalent instead of Mysql's function for 2 reasons: * The network traffic is compressed. * Mysql puts an unnecessary extra byte on end of the string (ok, this is totally insignificant) And definitely compress each chunk separately. It seems that those library routines slow down (excessive memory realloc??) after about 50K. That is, you can probably compress 20 50K chunks faster than 1 1M chunk. My implementation did File - Database -- the huge blob was never instantiated completely in RAM, only one chunk at a time. (Imagine trying to store a 50GB file.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 03, 2007 2:08 PM To: Rick James Cc: 'Paul McCullagh'; 'Ann W. Harrison'; 'MySQL List' Subject: RE: Blob data Rick is dead on correct, I call I chunking blob data.. There is an article here on a simple implementation: http://www.dreamwerx.net/phpforum/?id=1 I've had hundreds of thousands of files in this type of storage before with no issues. On Tue, 3 Jul 2007, Rick James wrote: I gave up on putting large blobs in Mysql -- too many limits around 16MB. Instead I broke blobs into pieces, inserting them with a sequence number. Added benefit: Does not clog up replication while huge single-insert is being copied over network and reexecuted on slaves. -Original Message- From: Paul McCullagh [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 27, 2007 2:57 AM To: Ann W. Harrison Cc: MySQL List; MySQL Internal Subject: Re: Blob data Hi Ann, Currently, the thoughts on how to make the BLOB references secure go like this: The BLOB reference consists of 2 components: The first component is basically an index used to find the BLOB on the server. The second component is a random number generated when the BLOB is created. The random number acts as an authorization code, and is checked when the BLOB is requested. So if the authorization code supplied in the BLOB reference does not match the code stored by the server for that BLOB, then the BLOB is not returned. If the authorization code is a 4-byte number, then the chances of getting the correct code for any particular BLOB is 1 in 4 billion. This makes it practically impossible to discover a BLOB by generating BLOB references and requesting them from the server. However, it does mean that once you have a valid BLOB reference it remains valid until the BLOB is deleted. So you can pass it around to your friends, or post it on the internet if you like. In order to prevent this (it will depend on the site, as to whether this is required), it would be possible to add a dynamic component to the BLOB reference which has a certain lifetime (for example, it expires after a certain amount of time, or when a database session is closed). Such a component would have to be added to the BLOB reference URL by the storage engine on the fly. So, as the SELECT result is being generated, the dynamic component is added to the BLOB references returned in the rowset. Security of the BLOB streaming stuff is one of the major issues, so further comments, questions and ideas are welcome! Best regards, Paul On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: Paul McCullagh wrote: It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). How does this work with access privileges? Can you just send random numbers in the URL until you start seeing blob data? Best regards, Ann -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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
Re: Blob data
Hi Ann, Currently, the thoughts on how to make the BLOB references secure go like this: The BLOB reference consists of 2 components: The first component is basically an index used to find the BLOB on the server. The second component is a random number generated when the BLOB is created. The random number acts as an authorization code, and is checked when the BLOB is requested. So if the authorization code supplied in the BLOB reference does not match the code stored by the server for that BLOB, then the BLOB is not returned. If the authorization code is a 4-byte number, then the chances of getting the correct code for any particular BLOB is 1 in 4 billion. This makes it practically impossible to discover a BLOB by generating BLOB references and requesting them from the server. However, it does mean that once you have a valid BLOB reference it remains valid until the BLOB is deleted. So you can pass it around to your friends, or post it on the internet if you like. In order to prevent this (it will depend on the site, as to whether this is required), it would be possible to add a dynamic component to the BLOB reference which has a certain lifetime (for example, it expires after a certain amount of time, or when a database session is closed). Such a component would have to be added to the BLOB reference URL by the storage engine on the fly. So, as the SELECT result is being generated, the dynamic component is added to the BLOB references returned in the rowset. Security of the BLOB streaming stuff is one of the major issues, so further comments, questions and ideas are welcome! Best regards, Paul On Jun 26, 2007, at 4:36 PM, Ann W. Harrison wrote: Paul McCullagh wrote: It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). How does this work with access privileges? Can you just send random numbers in the URL until you start seeing blob data? Best regards, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
This one time, at band camp, Warren Young [EMAIL PROTECTED] wrote: Storing arbitrary data in a BLOB column is tantamount to trying to turn the database into a file system. If you want a file system, use a file system. What is a file system, if not a database? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Hi Steve, Some very good points. We are working on a solution for BLOBs in the a MySQL database at www.blobstreaming.org. Any good solution will have to take your comments into account... On Jun 22, 2007, at 9:24 AM, Steve Edberg wrote: At 12:11 PM +0530 6/22/07, Ratheesh K J wrote: Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance Yes, storing files - especially non-textual files - in the file system instead of the database is generally considered the best practice. At one point I had created a document management system that stored everything in the database as you are doing; my rationale was that it allowed me to manage permissions using the existing database permissions, and to back up the whole database using mysqldump, vs mysqldump + doing a tar of the files. However, I abandoned this approach for the following reasons: (1) Storing non-plaintext items (eg; pictures) in the database makes it bigger and slower without added value - you can't (at least not yet, or in the foreseeable future) do a meaningful search on a blob. (2) It becomes more difficult to split storage out onto multiple filesystems; eg, leaving the database files in /var/database, putting the documents themselves into /home/docmanager, etc. (3) It makes queries on the commandline unwieldy; if you have a blob field, doing a select * to check a record's contents can dump a lot of garbage on the screen. (4) It can make doing incremental backups more difficult; if the documents themselves are relatively static, but the document metadata stored in the database is very dynamic, it becomes simple to do a compact daily database dump + a weekly document directory backup (for example) if the files are not in the database. What I do is create a unique SHA1 hash when a file is uploaded (eg; sha1(rand()). The original filename and the 40-character hash are stored in the database, and the document is stored in the filesystem using the hash as the filename. I can optionally compress and encrypt the document as well, storing the encryption key in the database. This gives (for me) adequate document security. An additional advantage is that you can take advantage of the filesystem tree if you have a large number of documents. For example, if a document hash is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the directory tree f0/11/8e/ f0118e9bd2c4fb29c64ee03abce698b8 (extending to as many levels as you feel necessary). By keeping the number of files per directory fairly small, file retrieval becomes relatively fast. As the hashes approximate a random distribution, you should always have a close- to-balanced tree. Lastly, I store a hash of the document itself in the database as well. This allows me to detect if duplicate files are uploaded, and to determine if a previously-uploaded file has been corrupted in some way. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp:// pgfsun.ucdavis.edu/ | | UC Davis Genome Center [EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530) 754-9127 | + said t e lawrence, picking up his fork + -- 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]
Re: Blob data
Hi Konstantin, On Jun 26, 2007, at 1:37 PM, Konstantin Osipov wrote: * Paul McCullagh [EMAIL PROTECTED] [07/06/26 15:35]: I am about to release the first version of the BLOB Streaming engine. We are not that far yet (obviously), but our aim is to create an open platform which will be extendable. So adding various image and fulltext searching should be possible. Also the inclusion of compression and encryption as you mentioned below. Our client/server protocol and SQL interpreter do not have support for blob streaming. We always copy the entire column to and from the engine and, in the protocol, to the client. So it would be actually very interesting to see how you contemplate to deliver data between applications and the storage engine. The BLOB Streaming engine allows you to stream BLOB data directly from a MySQL table using HTTP. To store a BLOB to the database you will use the HTTP PUT method to upload the data into a table row, and to retrieve a BLOB, you use the HTTP GET method to download the data from a table row. The URL specifies the database, table, column and search condition, in order to identify the BLOB location. It will also be possible to store the BLOBs out-of-row. In this case, only a BLOB reference is stored in the row. The reference is basically a URL which can be used to retrieve the data. So when you do an SQL SELECT which includes a BLOB column, the resulting rowset does not contain the data, just the BLOB reference (URL). The BLOB reference URL can be placed directly into an HTML page, or the data can be retrieved by the application using HTTP when it is needed. In order to store a BLOB out-or-row, you upload the BLOB to MySQL without specifying a target row. The PUT command returns the BLOB reference. Then you do a normal INSERT, placing the BLOB reference in the BLOB column. When the INSERT is committed, the uploaded BLOB is committed to the database as well. Best regards, Paul P.S. Thanks for your reply to MySQL forces engine shutdown, and hangs. Saved me quite a bit of time! :) -- -- Konstantin Osipov Software Developer, Moscow, Russia -- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Kevin Waterson wrote: This one time, at band camp, Warren Young [EMAIL PROTECTED] wrote: Storing arbitrary data in a BLOB column is tantamount to trying to turn the database into a file system. If you want a file system, use a file system. What is a file system, if not a database? That's the sort of logic that leads one to believe that the only data structure anyone needs is a hash table (Javascript), or that because bc is Turing complete, that it is a good general purpose programming language. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Ratheesh, If you still want to place you blob data in the database, seperate out the blob storage to a seperate table. This will help you alleviate few of your performance and maintenance problems. ~Alex On 6/22/07, Steve Edberg [EMAIL PROTECTED] wrote: At 12:11 PM +0530 6/22/07, Ratheesh K J wrote: Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance Yes, storing files - especially non-textual files - in the file system instead of the database is generally considered the best practice. At one point I had created a document management system that stored everything in the database as you are doing; my rationale was that it allowed me to manage permissions using the existing database permissions, and to back up the whole database using mysqldump, vs mysqldump + doing a tar of the files. However, I abandoned this approach for the following reasons: (1) Storing non-plaintext items (eg; pictures) in the database makes it bigger and slower without added value - you can't (at least not yet, or in the foreseeable future) do a meaningful search on a blob. (2) It becomes more difficult to split storage out onto multiple filesystems; eg, leaving the database files in /var/database, putting the documents themselves into /home/docmanager, etc. (3) It makes queries on the commandline unwieldy; if you have a blob field, doing a select * to check a record's contents can dump a lot of garbage on the screen. (4) It can make doing incremental backups more difficult; if the documents themselves are relatively static, but the document metadata stored in the database is very dynamic, it becomes simple to do a compact daily database dump + a weekly document directory backup (for example) if the files are not in the database. What I do is create a unique SHA1 hash when a file is uploaded (eg; sha1(rand()). The original filename and the 40-character hash are stored in the database, and the document is stored in the filesystem using the hash as the filename. I can optionally compress and encrypt the document as well, storing the encryption key in the database. This gives (for me) adequate document security. An additional advantage is that you can take advantage of the filesystem tree if you have a large number of documents. For example, if a document hash is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the directory tree f0/11/8e/f0118e9bd2c4fb29c64ee03abce698b8 (extending to as many levels as you feel necessary). By keeping the number of files per directory fairly small, file retrieval becomes relatively fast. As the hashes approximate a random distribution, you should always have a close-to-balanced tree. Lastly, I store a hash of the document itself in the database as well. This allows me to detect if duplicate files are uploaded, and to determine if a previously-uploaded file has been corrupted in some way. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob data
Ratheesh K J wrote: I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Databases are designed to handle arbitrarily large numbers of rows of structured data, where each datum is small and roughly the same size as all others of its kind. Storing arbitrary data in a BLOB column is tantamount to trying to turn the database into a file system. If you want a file system, use a file system. Microsoft's been promising a database-backed file system for something like 15 years now. Maybe it's because they don't write software all that well up there in Redmond. Or maybe it's because this is one of those ideas that sounds good on paper but doesn't work out so well in practice. I'm betting on the latter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Blob data
Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance
Re: Blob data
At 12:11 PM +0530 6/22/07, Ratheesh K J wrote: Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance Yes, storing files - especially non-textual files - in the file system instead of the database is generally considered the best practice. At one point I had created a document management system that stored everything in the database as you are doing; my rationale was that it allowed me to manage permissions using the existing database permissions, and to back up the whole database using mysqldump, vs mysqldump + doing a tar of the files. However, I abandoned this approach for the following reasons: (1) Storing non-plaintext items (eg; pictures) in the database makes it bigger and slower without added value - you can't (at least not yet, or in the foreseeable future) do a meaningful search on a blob. (2) It becomes more difficult to split storage out onto multiple filesystems; eg, leaving the database files in /var/database, putting the documents themselves into /home/docmanager, etc. (3) It makes queries on the commandline unwieldy; if you have a blob field, doing a select * to check a record's contents can dump a lot of garbage on the screen. (4) It can make doing incremental backups more difficult; if the documents themselves are relatively static, but the document metadata stored in the database is very dynamic, it becomes simple to do a compact daily database dump + a weekly document directory backup (for example) if the files are not in the database. What I do is create a unique SHA1 hash when a file is uploaded (eg; sha1(rand()). The original filename and the 40-character hash are stored in the database, and the document is stored in the filesystem using the hash as the filename. I can optionally compress and encrypt the document as well, storing the encryption key in the database. This gives (for me) adequate document security. An additional advantage is that you can take advantage of the filesystem tree if you have a large number of documents. For example, if a document hash is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the directory tree f0/11/8e/f0118e9bd2c4fb29c64ee03abce698b8 (extending to as many levels as you feel necessary). By keeping the number of files per directory fairly small, file retrieval becomes relatively fast. As the hashes approximate a random distribution, you should always have a close-to-balanced tree. Lastly, I store a hash of the document itself in the database as well. This allows me to detect if duplicate files are uploaded, and to determine if a previously-uploaded file has been corrupted in some way. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question about blob data types ie. Storing .xls in blob
Is it possible to store an excel file into a blob field in mysql? If so how do I go about importing the file into the blob field, and get it back out into an .xls file? Thanks in advnace Jack Taffar AOG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question about blob data types ie. Storing .xls in blob
Good binary storage article w/ sample code: http://php.dreamwerx.net/forums/viewtopic.php?t=6 On Mon, 27 Sep 2004 16:47:37 -0500, Jack Taffar [EMAIL PROTECTED] wrote: Is it possible to store an excel file into a blob field in mysql? If so how do I go about importing the file into the blob field, and get it back out into an .xls file? Thanks in advnace Jack Taffar AOG -- 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: Escaped BLOB data in XML
If you base64 encode your binary, it will be valid inside the xml. As far as I know this is the accepted way to transfer binary objects using xml. chris -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: 13 August 2004 05:09 To: Keith Ivey; [EMAIL PROTECTED] Subject: Re: Escaped BLOB data in XML This leads me to another question. What are the valid ASCII characters that XML parser understands. Are they only a-1,A-Z,0-9,., etc or some other characters. Regards, Karam --- Karam Chand [EMAIL PROTECTED] wrote: Hello, Hmmm. I was figuring that out. mysql_escape_string() only escapes characters like \r, \n, \\, 0 etc. it still keep other non-character data same like it keep ascii 15 to ascii 15 that no parser is able to handle. Isnt there any better way then base64 to handle this. Just like replacin to lt; solves the problem in the data? Regards, Karam --- Keith Ivey [EMAIL PROTECTED] wrote: Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- 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: Escaped BLOB data in XML
At 10:46 -0700 8/12/04, Karam Chand wrote: Hello, i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. I assume you mean mysql_real_escape_string(), not mysql_real_escape()? mysql_real_escape_string() is intended for escaping data that you are including in statements to be sent *to* the server. It is not for escaping data that you get back *from* the server in the result from a query. The issue you're describing is an XML issue, not a MySQL issue. You'll probably get more help if you post your question (which boils down to how can I write out binary data to an XML file?) on an XML-related list. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Escaped BLOB data in XML
Hello, i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. Any pointersI am using Expat as my apps XML parser. Regards, Karam __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaped BLOB data in XML
Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaped BLOB data in XML
Hello, Hmmm. I was figuring that out. mysql_escape_string() only escapes characters like \r, \n, \\, 0 etc. it still keep other non-character data same like it keep ascii 15 to ascii 15 that no parser is able to handle. Isnt there any better way then base64 to handle this. Just like replacin to lt; solves the problem in the data? Regards, Karam --- Keith Ivey [EMAIL PROTECTED] wrote: Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaped BLOB data in XML
This leads me to another question. What are the valid ASCII characters that XML parser understands. Are they only a-1,A-Z,0-9,., etc or some other characters. Regards, Karam --- Karam Chand [EMAIL PROTECTED] wrote: Hello, Hmmm. I was figuring that out. mysql_escape_string() only escapes characters like \r, \n, \\, 0 etc. it still keep other non-character data same like it keep ascii 15 to ascii 15 that no parser is able to handle. Isnt there any better way then base64 to handle this. Just like replacin to lt; solves the problem in the data? Regards, Karam --- Keith Ivey [EMAIL PROTECTED] wrote: Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to insert blob data remotely
Hi all!..
How can I insert blob data (specifically a jpg image) into a blob field
remotely?.. I mean, the client has to choose a jpg image in his/her PC
and upload it to the server.
I found this piece of code:
INSERT INTO car_models
(Brand, Picture)
VALUES
('Subaru Impreza', LOAD_FILE('/somepath/impreza.jpg'));
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
Any Idea?
Thanks in Advance
Hector.
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to insert blob data remotely
On Thu, 2004-07-01 at 11:06, Héctor Maldonado wrote: But LOAD_FILE only works if 'picture.jpg' is placed on the same machine where the server is running. That makes sense. You could of course NFS mount from the remote machine to the machine you are working from but that is much the same thing. Alternatively if you are working in a language like Perl there are other tricks you can play... -- Peter L. Berghold[EMAIL PROTECTED] Dog event enthusiast, brewer of Belgian (style) Ales. Happiness is having your contented dog at your side and a Belgian Ale in your glass. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to insert blob data remotely
Is using a programming language such as Java, PHP or VB an option?
-Original Message-
From: Hctor Maldonado
To: [EMAIL PROTECTED]; Omar De la Cruz
Sent: 7/1/04 10:06 AM
Subject: how to insert blob data remotely
Hi all!..
How can I insert blob data (specifically a jpg image) into a blob field
remotely?.. I mean, the client has to choose a jpg image in his/her PC
and upload it to the server.
I found this piece of code:
INSERT INTO car_models
(Brand, Picture)
VALUES
('Subaru Impreza', LOAD_FILE('/somepath/impreza.jpg'));
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
Any Idea?
Thanks in Advance
Hector.
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
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 insert blob data remotely.. SOLVED :)
Hi Again..
Fortunately I found a way to do this using C++/Qt. Here's the code for
who are interested in:
QFile *myFile;
QSqlQuery query;
QString strQuery;
QByteArray myByteArray;
// open the image and fill the bytearray
myFile = new
QFile(/root/hm/variostepsa/fotos_marcopolo/paradiso1.jpg);
myFile-open(IO_ReadOnly);
myByteArray = myFile-readAll();
myFile-close();
// Insert the image
strQuery = INSERT INTO Model (Brand, Picture) VALUES ('Subaru', :pic);
query.prepare(strQuery);
query.bindValue(:pic, myByteArray);
if (!query.exec())
qWarning(Eorrr);
I don't know if this is optimal, but it works.. :)
Thanks to all those reply..
Regards,
Hector.
El jue, 01-07-2004 a las 20:32, Peter L. Berghold escribi:
On Thu, 2004-07-01 at 11:06, Hctor Maldonado wrote:
But LOAD_FILE only works if 'picture.jpg' is placed on the same machine
where the server is running.
That makes sense. You could of course NFS mount from the remote machine
to the machine you are working from but that is much the same thing.
Alternatively if you are working in a language like Perl there are other
tricks you can play...
--
Ing. Hctor Maldonado Melgar
Dpto. Desarrollo de Software
TCI S.A., Lima-Per
[EMAIL PROTECTED]
Of.: 421-3222
Cel: 9503-9205
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB data and mysql_escape_string
I'm attempting to put an image into a BLOB field. I'm using Visual Basic 6 with ADO, although I wouldn't think that's making a difference. I create a string that contains the binary representation of the file. At this point the string is 5064 characters long. I call mysql_escape_string and the string becomes 5350 characters long. That seems normal as there will be a lot of characters in the binary data that need to be escaped. After selecting it out of the database, the size of the data retrieved is 5023 bytes. Clearly this is a problem when trying to re-create the file. Any suggestions as to why? Is there a function other than mysql_escape_string that should be used with binary data? Do I have to write my own? If so, what bytes need to be escaped? I assumed just NULL, ', \ and ...but didn't bother writing one since mysql_escape_string seemed to do what I wanted. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying blob data to remote box
Hello, I am running a DB on a machine in which I am developing on. Then I have been copying the contents of ~mysql/data/databasename to another box where I am running my code. All the data I modify copies well, and I can run on my production box with no problems. Except for binary blob data. When I go to my production box, the blob data does not seem to work properly. Are there other files, directories that I need to copy over in order to get this to work? The machines are not networked together, I have to move files via CD. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copying blob data to remote box
Are you just copying the files? I'd suggest using mysqldump if you are not already.. On Tue, 10 Feb 2004, Scott Purcell wrote: Hello, I am running a DB on a machine in which I am developing on. Then I have been copying the contents of ~mysql/data/databasename to another box where I am running my code. All the data I modify copies well, and I can run on my production box with no problems. Except for binary blob data. When I go to my production box, the blob data does not seem to work properly. Are there other files, directories that I need to copy over in order to get this to work? The machines are not networked together, I have to move files via CD. Thanks, Scott -- 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: copying blob data to remote box
Well, I gave it a shot, but for some reason, the dump caused my command window to die, while screaming. The database is owned by a user with a password, so I typed the following: mysqldump databasename --user=xx --password= It started doing something, but like I mentioned, the system begain complaining. I am looking at the mysql --help, but I do not see anything about a password, or about where to put the files. Thanks, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 8:24 AM To: Scott Purcell Cc: [EMAIL PROTECTED] Subject: Re: copying blob data to remote box Are you just copying the files? I'd suggest using mysqldump if you are not already.. On Tue, 10 Feb 2004, Scott Purcell wrote: Hello, I am running a DB on a machine in which I am developing on. Then I have been copying the contents of ~mysql/data/databasename to another box where I am running my code. All the data I modify copies well, and I can run on my production box with no problems. Except for binary blob data. When I go to my production box, the blob data does not seem to work properly. Are there other files, directories that I need to copy over in order to get this to work? The machines are not networked together, I have to move files via CD. Thanks, Scott -- 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: copying blob data to remote box
By default mysqldump just dumps to stdout.. so you need to (pipe) it to a textfile.. the correct syntax, you need to put the database name after the username/password (after the mysql options) .. give that a shot.. most likely all the garbage/output wacked out your session.. On Tue, 10 Feb 2004, Scott Purcell wrote: Well, I gave it a shot, but for some reason, the dump caused my command window to die, while screaming. The database is owned by a user with a password, so I typed the following: mysqldump databasename --user=xx --password= It started doing something, but like I mentioned, the system begain complaining. I am looking at the mysql --help, but I do not see anything about a password, or about where to put the files. Thanks, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 10, 2004 8:24 AM To: Scott Purcell Cc: [EMAIL PROTECTED] Subject: Re: copying blob data to remote box Are you just copying the files? I'd suggest using mysqldump if you are not already.. On Tue, 10 Feb 2004, Scott Purcell wrote: Hello, I am running a DB on a machine in which I am developing on. Then I have been copying the contents of ~mysql/data/databasename to another box where I am running my code. All the data I modify copies well, and I can run on my production box with no problems. Except for binary blob data. When I go to my production box, the blob data does not seem to work properly. Are there other files, directories that I need to copy over in order to get this to work? The machines are not networked together, I have to move files via CD. Thanks, Scott -- 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 display blob data back to text
Hi, I m using the following query SELECT id, subject, news, date_format(date, '%m/%d/%Y %h:%i %p') AS date, author, link FROM news ORDER BY id DESC LIMIT 5 heres my table structure CREATE TABLE news ( id int(10) unsigned DEFAULT '0' NOT NULL auto_increment, subject varchar(50) NOT NULL, news blob NOT NULL, date datetime DEFAULT '-00-00' NOT NULL, author varchar(50) NOT NULL, link varchar(150), PRIMARY KEY (id) ); heres the output i get in my web page 3 Test2 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan 2 Test1 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan Can i tweak the query so that i can display BLOB data properly. Have a great day. Karthikeyan. -- Karthikeyan Balasubramanian [EMAIL PROTECTED] -- http://www.fastmail.fm - IMAP accessible web-mail - 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 display blob data back to text
At 5:39 -0800 3/19/03, Karthikeyan Balasubramanian wrote: Hi, I m using the following query SELECT id, subject, news, date_format(date, '%m/%d/%Y %h:%i %p') AS date, author, link FROM news ORDER BY id DESC LIMIT 5 heres my table structure CREATE TABLE news ( id int(10) unsigned DEFAULT '0' NOT NULL auto_increment, subject varchar(50) NOT NULL, news blob NOT NULL, date datetime DEFAULT '-00-00' NOT NULL, author varchar(50) NOT NULL, link varchar(150), PRIMARY KEY (id) ); heres the output i get in my web page 3 Test2 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan 2 Test1 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan Can i tweak the query so that i can display BLOB data properly. Possibly, but you specify neither what's wrong the the values as shown above, or how you want them to be displayed, so I see no way to answer your question without additional information. Have a great day. Karthikeyan. -- Karthikeyan Balasubramanian [EMAIL PROTECTED] -- Paul DuBois http://www.kitebird.com/ sql, query - 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 display blob data back to text
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Paul DuBois wrote: At 5:39 -0800 3/19/03, Karthikeyan Balasubramanian wrote: Hi, I m using the following query SELECT id, subject, news, date_format(date, '%m/%d/%Y %h:%i %p') AS date, author, link FROM news ORDER BY id DESC LIMIT 5 heres my table structure CREATE TABLE news ( id int(10) unsigned DEFAULT '0' NOT NULL auto_increment, subject varchar(50) NOT NULL, news blob NOT NULL, date datetime DEFAULT '-00-00' NOT NULL, author varchar(50) NOT NULL, link varchar(150), PRIMARY KEY (id) ); heres the output i get in my web page 3 Test2 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan 2 Test1 [EMAIL PROTECTED] 03/19/2003 06:28 PM karthikeyan Can i tweak the query so that i can display BLOB data properly. Possibly, but you specify neither what's wrong the the values as shown above, or how you want them to be displayed, so I see no way to answer your question without additional information. Ahh, it appears you're using JDBCIf you dig down way deep in the JDBC spec when it talks about data type mappings, you will see that BLOB SQL datatypes map to java byte[]s...So, if you do a getObject() on a column that is a BLOB, you won't get a String, you'll get a byte[]...If you try and use this byte[] as a String, java prints out the address of it, like '[EMAIL PROTECTED]'. You should be using getString() if what you really want is a String. Alternatively, use the TEXT types in MySQL, which have many of the same properties of a BLOB, but are treated as text, not binary data. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+eJHttvXNTca6JD8RAhNfAJ9z03ijjCHNYsdpl90RXtTGVQz3kACgswOU ysLhcd6XpWtCNb5h5H5TidE= =qGYa -END PGP SIGNATURE- - 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 mysqlbinlog to restore blob data
Thanks for the comments Steve I am using MySLQ 3.23.53 on XP. I can try the current version and see what happens. As an example, I can make a backup with mysqldump, flush the logs, return to my app and add an item to the database that contains an attachment that might be an mp3 or bmp stored in a blob column. If I restore the database from the dump (no problem here) and then try mysqlbinlog logfile-bin.xxx | mysql -uuser -psomepass I will get sql syntax errors for the INSERT of the blob column. But not if the attachment was a text file, only things like a *.bmp, or *.mp3. Have you restored this type of data before? An interesting thing is that mysqldump handles this data with no problem - it will do the very same files without complaining, while the logs will fail. The data is coming in originally through JDBC using the MySQL driver. Rick Arthur Knexa.com Enterprises Inc. 519.747.1139 x 109 - 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 mysqlbinlog to restore blob data
Hi, I want to be able to use the MySQL binary logs and mysqlbinlog to restore data if needed. This works fine, except when the log contains sql that is written when I save a binary stream to a BLOB column in a table. I will get one of those messages like 'there is an error in your SQL at ...' when it tries to execute the insert. When I read the log out to a file, it looks to me as if there are characters in the values clause of the insert statement (i.e. the binary stream for the BLOB column) that need to be escaped, and these are being interpreted as part of the sql command syntax, causing the error. Has anyone dealt with this type of problem before? Are there some options I can set on the binary logs or on mysqlbinlog to get around this problem? Another question about mysqlbinlog: what is the -t option used for ? I can't find an explanation for it, other than in the help. TIA Rick Arthur Knexa.com Enterprises Inc. 519.747.1139 x 109 - 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 mysqlbinlog to restore blob data
Rick, I am able to restore from logs that had binary data (even though the output looked real strange and messed up the terminal window). I did have a problem once when I tried filtering data between mysqlbinlog and mysql. Be careful if you do that. What version of mysql are you using? I have no idea about the -t option for mysqlbinlog, but I'd guess it is similar to the 'load data/table from master' command inside mysql. (mysqlbinlog can connect to a remote server rather than using a local file). -steve- - 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 v3.23.53a] BLOB Data and Table Size
I have two tables containing a BLOB cloumn as; -- supposed to be able to handle up to about 16MB CRETAE TABLE gallery1 ( idINTEGER, titleVARCHAR(255), mediumimageMEDIUMBLOB ); -- supposed to be able to handle up to about 4GB CRETAE TABLE gallery2 ( idINTEGER, titleVARCHAR(255), largeimageLONGBLOB ); Both table show the size about 48KB initially. And I added a 16MB(about 7,863KB) image into the table, gallery1 and a 4GB(about 11,620KB) into the table, gallery2, respectively. The size of gallery1, i,e, gallery1.db, shows 8,232KB and the size of gallery2, i.e., gallery2.db, shows 11,712KB. Each table contains only one entry. And I deleted both entries from two tables. So both tables contain no entry. However, the size of tables remain same as after insertion made. Q: How come the size of table does not decrease after deletion? Thank you. And regards, Pae P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). - 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 v3.23.53a] BLOB Data and Table Size
On 26 Nov 2002, at 8:52, Pae Choi wrote: Q: How come the size of table does not decrease after deletion? MySQL will reuse that space when you insert new records. To recover the space, you need to optimize the table: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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 v3.23.53a] BLOB Data and Table Size
First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; On 26 Nov 2002, at 8:52, Pae Choi wrote: Q: How come the size of table does not decrease after deletion? MySQL will reuse that space when you insert new records. To recover the space, you need to optimize the table: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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 v3.23.53a] BLOB Data and Table Size
Pae, Tuesday, November 26, 2002, 6:52:20 PM, you wrote: PC I have two tables containing a BLOB cloumn as; PC -- supposed to be able to handle up to about 16MB PC CRETAE TABLE gallery1 ( PC idINTEGER, PC titleVARCHAR(255), PC mediumimageMEDIUMBLOB PC ); PC -- supposed to be able to handle up to about 4GB PC CRETAE TABLE gallery2 ( PC idINTEGER, PC titleVARCHAR(255), PC largeimageLONGBLOB PC ); PC Both table show the size about 48KB initially. PC And I added a 16MB(about 7,863KB) image into the PC table, gallery1 and a 4GB(about 11,620KB) into PC the table, gallery2, respectively. PC The size of gallery1, i,e, gallery1.db, shows 8,232KB PC and the size of gallery2, i.e., gallery2.db, shows PC 11,712KB. PC Each table contains only one entry. And I deleted PC both entries from two tables. So both tables contain PC no entry. PC However, the size of tables remain same as after PC insertion made. PC Q: How come the size of table does not decrease PC after deletion? It's an expected behaviour. According to the MySQL manual: In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 v3.23.53a] BLOB Data and Table Size
no database manager that i know of automatically packs the database after every record deletion. it would be too costly. that is one of the reasons that most of us do not store blobs in databases, but store them externally. I have two tables containing a BLOB cloumn as; -- supposed to be able to handle up to about 16MB CRETAE TABLE gallery1 ( idINTEGER, titleVARCHAR(255), mediumimageMEDIUMBLOB ); -- supposed to be able to handle up to about 4GB CRETAE TABLE gallery2 ( idINTEGER, titleVARCHAR(255), largeimageLONGBLOB ); Both table show the size about 48KB initially. And I added a 16MB(about 7,863KB) image into the table, gallery1 and a 4GB(about 11,620KB) into the table, gallery2, respectively. The size of gallery1, i,e, gallery1.db, shows 8,232KB and the size of gallery2, i.e., gallery2.db, shows 11,712KB. Each table contains only one entry. And I deleted both entries from two tables. So both tables contain no entry. However, the size of tables remain same as after insertion made. Q: How come the size of table does not decrease after deletion? Thank you. And regards, Pae P.S.: MySQL, SQL, and Query(to clarify that it's not spam nor OT). -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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 v3.23.53a] BLOB Data and Table Size
At 10:36 -0800 11/26/02, Pae Choi wrote: First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; OPTIMIZE performs defragmentation only for MyISAM tables. You can shrink your tables by dumping and reloading them: mysqldump --opt db_name gallery gallery2 dump.sql mysql db_name dump.sql - 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 v3.23.53a] BLOB Data and Table Size
Paul, It really did the trick. Thanks. Regards, Pae - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Pae Choi [EMAIL PROTECTED]; Keith C. Ivey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 10:59 AM Subject: Re: [MySQL v3.23.53a] BLOB Data and Table Size At 10:36 -0800 11/26/02, Pae Choi wrote: First of all, thanks for your reply as well as a pointer to the Doc. I tried the OPTIMIZE command as follows: mysql optimize table gallery; mysql optimize table gallery2; Both does not contain any entry since I deleted the BLOB data. But the size of tables, gallery.db and gallery2.db does not decresed. It remains as it was. Any comments? Thanks. Pae P.S.: The table is created with type=bdb option as: CREATE TABLE ... TYPE=BDB; OPTIMIZE performs defragmentation only for MyISAM tables. You can shrink your tables by dumping and reloading them: mysqldump --opt db_name gallery gallery2 dump.sql mysql db_name dump.sql - 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 in mysql - blob data type using asp
dear all, i've problem when i want to query mysql data using asp command. this is my error message in browser... --- Process ID: 1 Subject: TEST INPUT PERTAMA KALI News: ADODB.Field error '800a0c93' The operation requested by the application is not allowed in this context. /scripts/asp/qmknwp.asp, line 45 --- and this is my script in asp... --- while not objrs.eof response.write font size=1Process ID: objrs(twmknw_cd) /fontBRfont size=1Subject: objrs(twmknw_sbj) /fontBRfont size=1News: BR blob_size = objrs(twmknw_news).ActualSize byte_f = objrs(twmknw_news).GetChunk(blob_size) '=== this is line 45 response.ContentType = plain/html response.BinaryWrite byte_f response.write /fontBR objrs.movenext wend --- and this is also mysql table structure --- twmknw_cd bigint null auto_increment, twmknw_sbj varchar(100) null, twmknw_news blob null, --- can u help me? TIA Ivan Paul sql,query,mysql - 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
fail to insert BLOB data on multibyte charset MySQL server
failed to insert BLOB data on multibyte charset
i'm using GBK charset. mysql 4.01
use PHP to insert, did addslashes before insert
however, failed.
i've read the mysql_escape_string() source code. noticed that, mb char is
processed differently, but is it a must ?
yes, php+libmysql is not up to date, but i'm trying to use my own escaping
function
function GBK_escape_string($data)
{
$ret = '';
$l = strlen($data);
for ($i = 0; $i $l; $i ++)
{
$c = ord($data{$i});
if (0x81 = $c $c = 0xfe ($i+1) $l)
{
$c2 = ord($data{$i+1});
if ((0x40=$c2 $c2=0x7e) ||
(0x80=$c2 $c2=0xfe))
{
$ret .= $data{$i};
$ret .= $data{++$i};
continue;
}
}
switch ($data{$i})
{
case \\: $ret .= ; break;
case \0: $ret .= \\0; break;
case \n: $ret .= \\n; break;
case \r: $ret .= \\r; break;
case ': $ret .= \\'; break;
case \: $ret .= \\\; break;
case \032: $ret .= \\Z; break;
default: $ret .= $data{$i}; break;
}
}
return $ret;
}
still won't work
anyone could give me a hand?
i'm supposing the error:
string: \n\xE0'.
according to ctype-gbk.c ismbchar_gbk
\xE0' is not mbchar, \xE0 should not escape, and ' should escaped.
result: \x5Cn\xE0\x5C'\x5C. (0x5C is backslash)
when mysqld scanning this string
\x5Cn is scan as \n, correct
but \xE0\x5C is scan as a GBK char, then ' will be a string terminator
and complain that
SQL syntax near '\'
which is the escaped double-quote ...0x5C...
_
ÓëÁª»úµÄÅóÓѽøÐн»Á÷£¬ÇëʹÓà 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
sql command to examine blob data?
What Mysql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; 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
sql command to examine blob data?
What Mysql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; 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: sql command to examine blob data?
What Mysql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; HEX() converts numbers, not strings. There is no function in MySQL for what you want. - 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: Re: sql command to examine blob data?
Paul, Friday, April 26, 2002, 5:57:56 PM, you wrote: What Mysql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; PD HEX() converts numbers, not strings. There is no function in MySQL PD for what you want. Now the situation is changed. :-) If the argument of HEX() is a string, HEX() returns a hexadecimal string of argument where each character is converted to 2 hexadecimal digits. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: Re: sql command to examine blob data?
At 18:12 +0300 4/26/02, Victoria Reznichenko wrote: Paul, Friday, April 26, 2002, 5:57:56 PM, you wrote: What Mysql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; PD HEX() converts numbers, not strings. There is no function in MySQL PD for what you want. Now the situation is changed. :-) If the argument of HEX() is a string, HEX() returns a hexadecimal string of argument where each character is converted to 2 hexadecimal digits. You're right! This requires MySQL 4.0.1, so I assume the original poster is using an older version. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: sql command to examine blob data?
Nissim, Wednesday, April 24, 2002, 9:30:36 PM, you wrote: NL What sql function can I use to examine blob in a table. I need to display NL part of the blob in hex values. Shouldn't the following sql command work : NL select hex(substring(col1,1,10)) from table tbl1; Yes, you can use HEX() and SUBSTRING() functions, but your statement doesn't work because wrong SELECT syntax ... Correct sytnax is: SELECT FROM table_name; NL Thanks -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
sql command to examine blob data?
What sql function can I use to examine blob in a table. I need to display part of the blob in hex values. Shouldn't the following sql command work : select hex(substring(col1,1,10)) from table tbl1; 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
INSERT blob data in C
Howdy - I looked in the doc @ mysql.com and the New Riders and O'Reilly MySQL books. Plus, I consulted the archives as best I could and did not see an answer for this: How do you insert binary data using the C API? I know you need to use mysql_real_query method and pass length, but how do you delimit the blob field? INSERT INTO some_table (key_field,blob_field) VALUES ( 'A', 'binary data'); INSERT INTO some_table SET key_field = 'A', blob_field = 'binary data'; Both have the issue of having the single quote appear in the data. These fields I'm storing are between 10 and 2000 bytes long and there are millions of them so I can't store them as separate files and then use the file name like I would for a graphic or downloadable file on a web page. TIA! Have A Great Scouting Day Ken Hylton 7826 Falcon Ridge Drive San Antonio, Texas 78239-4032 210-646-9508 (home) 210-949-7261 (work) 210-949-7254 (fax) 210-287-6756 (cell) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] 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: INSERT blob data in C
just some code I did some time ago:
/* */
#include stdio.h
#include stdlib.h
#include unistd.h
#include fcntl.h
#include sys/fcntl.h
#include mysql/mysql.h
MYSQL dbcon;
int main(int argc, char *argv[])
{
int i;
char *tmpQ=malloc(2*1024*1024);
char *end;
int file;
int size;
char *mem;
char tmpstr[1024];
mysql_init(dbcon);
mysql_real_connect(dbcon, NULL, username, password,
dbname,0,/tmp/mysql.sock,0);
for(i=1;iargc;i++) {
file = open(argv[i], O_RDONLY);
lseek(file, 0, SEEK_SET);
size = lseek(file, 0, SEEK_END);
lseek(file, 0, SEEK_SET);
mem = malloc(size);
read(file,mem,size);
printf(inserting: %s\n,argv[i]);
end = (char *) strmov(tmpQ,INSERT INTO image values();
*end++ = '\'';
sprintf(tmpstr,%i,i);
end = strmov(end, tmpstr);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
sprintf(tmpstr,%i,i);
end = strmov(end, tmpstr);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
end += mysql_escape_string(end, mem, size);
*end++ = '\'';
*end++ = ')';
mysql_real_query(dbcon, tmpQ, (unsigned int) (end - tmpQ));
free(mem);
close(file);
}
mysql_close(dbcon);
exit(0);
return 0;
}
Kenneth Hylton wrote:
Howdy -
I looked in the doc @ mysql.com and the New Riders and O'Reilly MySQL books.
Plus, I consulted the archives as best I could and did not see an answer for
this:
How do you insert binary data using the C API?
I know you need to use mysql_real_query method and pass length, but how do
you delimit the blob field?
INSERT INTO some_table (key_field,blob_field) VALUES ( 'A', 'binary
data');
INSERT INTO some_table SET key_field = 'A', blob_field = 'binary data';
Both have the issue of having the single quote appear in the data.
These fields I'm storing are between 10 and 2000 bytes long and there are
millions of them so I can't store them as separate files and then use the
file name like I would for a graphic or downloadable file on a web page.
TIA!
Have A Great Scouting Day
Ken Hylton
7826 Falcon Ridge Drive
San Antonio, Texas 78239-4032
210-646-9508 (home)
210-949-7261 (work)
210-949-7254 (fax)
210-287-6756 (cell)
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED]
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: INSERT blob data in C
Thanks for your quick response!
That's just what I needed.
The secret is using mysql_escape_string to change the data such that it will
not cause problems with delimiters!
Thank You!
Have A Great Scouting Day
Ken Hylton
7826 Falcon Ridge Drive
San Antonio, Texas 78239-4032
210-646-9508 (home)
210-949-7261 (work)
210-949-7254 (fax)
210-287-6756 (cell)
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
-Original Message-
From: paradoxix [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 28, 2002 3:04 PM
To: Kenneth Hylton
Cc: [EMAIL PROTECTED]
Subject:Re: INSERT blob data in C
just some code I did some time ago:
/* */
#include stdio.h
#include stdlib.h
#include unistd.h
#include fcntl.h
#include sys/fcntl.h
#include mysql/mysql.h
MYSQL dbcon;
int main(int argc, char *argv[])
{
int i;
char *tmpQ=malloc(2*1024*1024);
char *end;
int file;
int size;
char *mem;
char tmpstr[1024];
mysql_init(dbcon);
mysql_real_connect(dbcon, NULL, username, password,
dbname,0,/tmp/mysql.sock,0);
for(i=1;iargc;i++) {
file = open(argv[i], O_RDONLY);
lseek(file, 0, SEEK_SET);
size = lseek(file, 0, SEEK_END);
lseek(file, 0, SEEK_SET);
mem = malloc(size);
read(file,mem,size);
printf(inserting: %s\n,argv[i]);
end = (char *) strmov(tmpQ,INSERT INTO image values();
*end++ = '\'';
sprintf(tmpstr,%i,i);
end = strmov(end, tmpstr);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
sprintf(tmpstr,%i,i);
end = strmov(end, tmpstr);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
end += mysql_escape_string(end, mem, size);
*end++ = '\'';
*end++ = ')';
mysql_real_query(dbcon, tmpQ, (unsigned int) (end -
tmpQ));
free(mem);
close(file);
}
mysql_close(dbcon);
exit(0);
return 0;
}
Kenneth Hylton wrote:
Howdy -
I looked in the doc @ mysql.com and the New Riders and
O'Reilly MySQL books.
Plus, I consulted the archives as best I could and did not
see an answer for
this:
How do you insert binary data using the C API?
I know you need to use mysql_real_query method and pass
length, but how do
you delimit the blob field?
INSERT INTO some_table (key_field,blob_field) VALUES (
'A', 'binary
data');
INSERT INTO some_table SET key_field = 'A', blob_field =
'binary data';
Both have the issue of having the single quote appear in
the data.
These fields I'm storing are between 10 and 2000 bytes
long and there are
millions of them so I can't store them as separate files
and then use the
file name like I would for a graphic or downloadable file
on a web page.
TIA!
Have A Great Scouting Day
Ken Hylton
7826 Falcon Ridge Drive
San Antonio, Texas 78239-4032
210-646-9508 (home)
210-949-7261 (work)
210-949-7254 (fax)
210-287-6756 (cell)
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED]
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
Re: Cannot add blob data to innodb table
On Mon, 12 Nov 2001, Heikki Tuuri wrote: Date: Mon, 12 Nov 2001 09:06:42 +0200 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Please don't fix this. Case insensitivity means that you can't use windows to test something which will run under Unix where CASE COUNTS. Case insensitivity in table names is one of the more annoying Gatesisms that limit the usability of windows for testing. Does mysql.err contain anything? Regards, Heikki - 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: Cannot add blob data to innodb table
Hi Heikki, Comments inserted in text below: Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? The create statement from the query log is: CREATE TABLE tbl_boards(BID INT NOT NULL,_Group INT NULL,Code CHAR(10) NULL,Description CHAR(50) NULL,Board_Size CHAR(50) NULL,Product_Line CHAR(30) NULL,Featuring CHAR(255) NULL,Colour CHAR(255) NULL,Picture LONGBLOB NULL,Label LONGBLOB NULL, PRIMARY KEY (BID)); and the INSERT statement is: INSERT INTO `tbl_boards` (`BID`,`_Group`,`Code`,`Description`,`Board_Size`,`Product_Line`,`Picture`,`Label`)VALUES (1,1,'1','Colour Board - Glasstyle','450mm x 450mm x 6mm','Glasstyle','^U^\3\0^B\0\0\0^S\0^L\0^T\0\'\0Photo Editor Photo\0MSPhotoEd.3\0^A^E\0\0^B\0\ etc. How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. The blobs are about 250-400K each, the average row length is 850K and the max_allowed_packet is 1M. It could be that some of the records are 1M. I will check that. However, as I mentioned before, the INSERT works with myisam tables. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Does mysql.err contain anything? I am running mysql-max on Linux (RedHat7.1) and mysql.err does not have any error-related messages. Regards, Heikki Thanks, Stephen - 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: Cannot add blob data to innodb table
Stephen, At 12:53 AM 11/12/01 -0800, you wrote: Hi Heikki, Comments inserted in text below: Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? The create statement from the query log is: CREATE TABLE tbl_boards(BID INT NOT NULL,_Group INT NULL,Code CHAR(10) NULL,Description CHAR(50) NULL,Board_Size CHAR(50) NULL,Product_Line CHAR(30) NULL,Featuring CHAR(255) NULL,Colour CHAR(255) NULL,Picture LONGBLOB NULL,Label LONGBLOB NULL, PRIMARY KEY (BID)); and the INSERT statement is: INSERT INTO `tbl_boards` (`BID`,`_Group`,`Code`,`Description`,`Board_Size`,`Product_Line`,`Picture`, `Label`)VALUES (1,1,'1','Colour Board - Glasstyle','450mm x 450mm x 6mm','Glasstyle','^U^\3\0^B\0\0\0^S\0^L\0^T\0\'\0Photo Editor Photo\0MSPhotoEd.3\0^A^E\0\0^B\0\ etc. How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. The blobs are about 250-400K each, the average row length is 850K and the max_allowed_packet is 1M. It could be that some of the records are 1M. I will check that. However, as I mentioned before, the INSERT works with myisam tables. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Does mysql.err contain anything? I am running mysql-max on Linux (RedHat7.1) and mysql.err does not have any error-related messages. Regards, Heikki Thanks, Stephen how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki - 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: Cannot add blob data to innodb table
how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki Heikki, Manually inserting text into the blob fields work fine. I was also able to convert the table type from MyISAM to INNODB and the blob fields stayed intact. I guess there must be some unescaped sequence in the blobs that is preventing the insertion of the first record when the table type is innodb. I'm not sure how (or where) you would include an escape function in Access97. I don't see any obvious switch that can be set in MyODBC either. Any suggestions? I will post the problem to the MyODBC list. Thanks, Stephen - 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: Cannot add blob data to innodb table
Hi! At 08:57 AM 11/12/01 -0800, you wrote: how do you communicate the binary strings to MySQL? From the manual I found: If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. ASCII 34, double quote. Represent this by `\'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki Heikki, Manually inserting text into the blob fields work fine. I was also able to convert the table type from MyISAM to INNODB and the blob fields stayed intact. I guess there must be some unescaped sequence in the blobs that is preventing the insertion of the first record when the table type is innodb. I'm not sure how (or where) you would include an escape function in Access97. I don't see any obvious switch that can be set in MyODBC either. Any suggestions? I will post the problem to the MyODBC list. You could try to insert the first row from the command line: cut it from the query log and feed it to mysql, on Unix e.g.: mysql yourdatabasename atextfilecontainingtheinsertstatement The query log seemed to contain correctly escaped characters. Or you can send the insert statement to [EMAIL PROTECTED] who is responsible for MyODBC, I think. I am Ccing this email to Venu. Thanks, Stephen Regards, Heikki http://www.innodb.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: Cannot add blob data to innodb table
Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.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: Cannot add blob data to innodb table
Steve, Date: Mon, 12 Nov 2001 08:52:54 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Cannot add blob data to innodb table Stephen, Hi, I have an Access97 table containing binary fields (gifs) that I would like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max (3.23.44). The MySQL query log shows binary-like characters being received but there never seems to be progression to the next record. After 5 or so minutes, the append query in Access97 quits and an ODBC error window complaining about a lost connection pops-up. When I convert the table to myisam type, the append query works. Is there something in my.cnf I need to adjust to fix thisproblem? inserting binary BLOBs should work. What is an 'append' query in MS Access? Is it translated to an INSERT in MySQL? Could you paste a copy of what the MySQL query logs shows? How big is the BLOB? What is the CREATE TABLE statement? Note that a BLOB bigger than 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the CREATE TABLE statement. If you try inserting an ASCII text file does that work? How big you have set max_allowed_packet in my.cnf? That restricts the size of rows communicated from a client to the server. Thanks, Stephen Regards, Heikki http://www.innodb.com actually, are you running MySQL on Windows? Then the problem might be the bug introduced in 3.23.42: to access InnoDB tables you must use the same case of letters in the database name as you used in the CREATE TABLE statement. Make sure you consistently use lower case in database names. The bug is fixed in upcoming 3.23.45. Does mysql.err contain anything? Regards, Heikki - 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: could not insert BLOB data type in a table
PD At 10:43 AM +0530 7/25/01, [EMAIL PROTECTED] wrote: Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. PD Use $dbh-quote() or placeholders to insert the BLOB value into PD the query string. See the DBI docs. Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: could not insert BLOB data type in a table
At 5:23 PM +0400 7/25/01, Ilya Martynov wrote: PD At 10:43 AM +0530 7/25/01, [EMAIL PROTECTED] wrote: Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. PD Use $dbh-quote() or placeholders to insert the BLOB value into PD the query string. See the DBI docs. Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). Please show me the part of the documentation that you're referring to, and please supply a counter-example. quote() is extremely useful for producing SQL statements that will be executed by another program, a situation for which placeholders are useless. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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: could not insert BLOB data type in a table
Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). PD Please show me the part of the documentation that you're referring to, PD and please supply a counter-example. quote() is extremely useful for PD producing SQL statements that will be executed by another program, a PD situation for which placeholders are useless. perldoc DBI: Quote will probably not be able to deal with all pos- sible input (such as binary data or data containing newlines), .. There is no need to quote values being used with the Placeholders and Bind Values entry elsewhere in this document. Here a proof that quote sucks: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msp:73935:phlgjhgmdiikjknclakk User had trobles with quote and binary data but once he rewrote code with placeholders problem disappeared. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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
could not insert BLOB data type in a table
Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. Thanks in advance. Regards, Thakur Gyawali Nepal - 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: could not insert BLOB data type in a table
At 10:43 AM +0530 7/25/01, [EMAIL PROTECTED] wrote: Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. Use $dbh-quote() or placeholders to insert the BLOB value into the query string. See the DBI docs. Thanks in advance. Regards, Thakur Gyawali Nepal -- 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
