Reading text from a BLOB field
Posted by: Margaret Nelson
Date: July 20, 2012 10:21AM

I was having trouble with question marks being displayed in the text read back from a blob field in MySql. I found a partial solution when I read this conversation: http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql Converting to utf8 (aka UTF-8) solved part of the problem but my text truncated when it ran into a special character (the em or long dash). The CONVERT() function is still the answer, but I had to figure out which character set our database was using. Below are the steps I followed to figure this out. I'm new to MySQL, so there are probably better ways to get at this information.
1. SHOW FULL COLUMNS FROM mytable IN mydatabase; -- note the collation on the text fields;
2. SHOW CHARACTER SET; -- find the collation you saw in step one in either the Description or Default collation columns. Copy the value from the Charset column.
3. In your SQL SELECT statement add a field like this: CONVERT(someBlobTextField, USING copiedCharsetValue)
4. Run your query and review your data. Viola! It works.

I also ran across a PHP function called nl2br() that adds the line breaks the user had entered back in.

Options: ReplyQuote


Subject
Views
Written By
Posted
Reading text from a BLOB field
79972
July 20, 2012 10:21AM
17244
July 25, 2012 12:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.