How to Convert from Blob to Text in MySQL

Converting Blob to String yields just the Blob object name in Java

Seems like you make a small mistake during insertion. Your code should be something like:

String sql = "INSERT INTO db.dbname('blob') VALUES (?);"
PreparedStatement pat = conn.prepareStatement(sql);
pat.setBlob(1, blob);
pat.executeUpdate();

Please use prepared statements with those placeholders. Otherwise attackers could easily run an SQL injection attack. If you want to see how SQL injection works, there‘s a great Computerphile Video about it on YouTube.

BLOB to long text conversion in MySQL

try this,

SELECT CAST(key_initiatives AS CHAR(1000) CHARACTER SET utf8 )  AS key_init FROM OBJSETTING_FOCUS_ON_CUSTOMER

How can I directly view blobs in MySQL Workbench

In short:

1. Go to Edit > Preferences
2. Choose SQL Editor
3. Under SQL Execution, check Treat BINARY/VARBINARY as nonbinary character string
4. Restart MySQL Workbench (you will not be prompted or informed of this requirement).

In MySQL Workbench 6.0+

1. Go to Edit > Preferences
2. Choose SQL Queries
3. Under Query Results, check Treat BINARY/VARBINARY as nonbinary character string
4. It's not mandatory to restart MySQL Workbench (you will not be prompted or informed of this requirement).*

With this setting you will be able to concatenate fields without getting blobs.

I think this applies to versions 5.2.22 and later and is the result of this MySQL bug.

Disclaimer: I don't know what the downside of this setting is - maybe when you are selecting BINARY/VARBINARY values you will see it as plain text which may be misleading and/or maybe it will hinder performance if they are large enough?

---

Related Topics