Viewing BLOB entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Viewing BLOB entries

Post by anjanesh »

Hi

I have a BLOB field which Im trying to figure out for a long time now how to view the actual contents of that field.
If I enter 'something' into that field, aren't the binary/hex values stored ?
SELECT * FROM tablename shows the text as a string.
Is there nay other view to it ?

Thanks
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Wehn you store a string as binary, it's converting the ascii characters to their values and storing them, when you view something in your web browser the value is being converted back to ascii, if you want to display "10101011"... that kind of thing you need to use unpack and pack. The purpose of the blob field is so that non ascii characters can be stored safely (contents of a jpeg file for example). With a few exceptions text fields and blob fields are pertty similiar. Blob can store text but that's what the text field is for. Blob fields should be used for file storage, or other data that cannot be represented as text.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

when you view something in your web browser the value is being converted back to ascii
But even in mysql.exe console Im getting BLOB values in ASCII.
How can I view the proper contents in mysql.exe ?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

You ARE viewing the proper contents. All data in all fields are stored as binary values, and converted back when you view them. BLOB fields just don't mess up stuff that contains non-ascii values (blob is just a text field that doesn't toy with stuff when you insert it).. for instance text fields might trim preceding white-space off your text


In PHP I do

Code: Select all

$var = 5;
when i echo 5 do I get "00000101"? No.



If you are still confused about this I recommend you read up on how binary works, or play around with php's pack() and unpack() functions.

for example:

Code: Select all

echo decbin(5);
this gives you a binary string (101)

Code: Select all

echo pack('i', 5);
this shows you the number 5's binary representation's ascii equivalent




this is because decbin is going through each bit and converting the 1 bits to the ascii value for one and outputting them to your browser, so you see 101 instead of the ascii character(s) that happen to be represented by 101 (apparent gibberish). If you want to display all the 1's and 0's you need to use the decbin function, even that's only good for numbers though (i think). PHP has base_convert which is usefull if you want the hex values or something. there's also functions for working with hex.
Post Reply