Blob Editor?

Hi guys, I've just decided to finally upgrade HeidiSQL and jumped right into the version 4 RC3, which so far I am loving. However one thing I may be blindly missing?

In our database we have a longblob that is use to store the raw form of large XML documents. In my old version of HeidiSQL I could click on the field and it would show me the converted field. In the current version when I select the field I get the binary editor, showing me the hex values. (which while being handy to sometimes dive into is far less useful)

Am I being blind? Has this feature been removed? (or not added yet)

Many thanks, Ben Woodward

BLOBs for XML ? You should convert those columns to TEXT or MEDIUMTEXT, as they normally don't contain binary data. Once you've done so, HeidiSQL pops up with a text editor for editing.

HEX is displayed in all columns which have a binary collation so it's safe to edit a binary file for example.

Sadly we can't control the schema for how we get things. Also theoretically these data structures could contain many megabites of data. (I assume, hence the blob)

BLOB = Binary Large Object
CLOB = Character Large Object

CLOB is like blob but it is fit for text object like XML.

BLOB = Binary Large Object
CLOB = Character Large Object

CLOB is like blob but it is fit for text object like XML.

I know, but it's not our schema to control :(

So we should break everyone's data and display binary stuff as text?

Well, just starr rfe #955 - it basically says what you need - add a text viewer to the binary editor.

I have a rather agnostic and minimalist approach to database type issues. Personally, I think database types are essentially useless and only exist to give the impression that databases interoperate with programming languages, and POSSIBLY but not necessarily to provide a basic clue as to what is indexable and POSSIBLY suggest a minimal storage strategy.

All of this information could be provided in a myriad of ways to a database with no notion of a column type at all. Its just a crutch from the history of software development.

The fact is, databases dont interoperate with programming languages. No database integrates type wise except through a translation layer to its clients. They may agree on certain basics like whats a number or whats a string, or a date if your lucky, but if you wanna use Java as an example not a single DB vendor fully implements the JDBC specification going back a decade. And thats a sunny, rosy best case example. ODBC and anything else you could speak of are far worse.

Thats why, in my code I always convert every single column value to a string. Because in actuality its the only method in Java that ALWAYS works. It always does a proper and btw human readable conversion. If you convert every column into a string in the client your database is actually readable.

But JDBC 3 is going to change all that so I will have to work harder to enforce (WHAT SHOULD BE) the status quo ;)

Store actual binary data in your database, well I wont be able to read it but it wont be corrupted either.

I know, but it's not our schema to control :(

Pick up the phone, call the guy that controls it, tell him he's doing it wrong.

As a workaround, create a VIEW to cast the data:

CREATE VIEW mysql.abc AS SELECT CONVERT(mysql.user.user USING utf8) FROM mysql.user

It will probably be read-only though, given that HeidiSQL doesn't currently recognize the PKs in a view, even if all of the original table(s)'s PK(s) columns are included.

Thats why, in my code I always convert every single column value to a string.

Every single column?

If by "string" you mean handle data as Unicode (your application supports i18n) then you convert to string == you eventually loose data. Not every binary character fits in a Unicode string, and a lot of string handling functions will barf up garbage if you try anyway. That usually means cutting the string short, or removing a couple of characters from it.

If by "string" you mean handle data as 8-bit in some character set, then yeah you'll be fine, as long as you don't use any C functions which will abort on NUL character, giving you garbage (truncation) again. Also if you mismatch the character set you'll see wrong stuff, so there's a bit of manual work involved, matching everything up.

If by "string" you mean "byte array", then yeah that should work fine. Not a very useful representation, though, being essentially just a bunch of numbers.

Ho hum.