Can storing compressed text in long varchar field be a problem?

[Former Member]

Using 11.0.1. A result of COMPRESS function is stored as long varchar. That solution worked for years. Recently, some records were found to be corrupt, that is, DECOMPRESS fails. Please advise if storing a compressed text in a long varchar field could be the culprit.

[MarkCulp]

The output of COMPRESS is binary data so you should be using a LONG BINARY column to store the compressed data. When storing the binary data in a LONG VARCHAR column the server will assume that the data is in the character set of the database, ... so it is conceivable that bytes that fall outside of the database character set could be mangled in some way. Having said that, I can't think of any low level operation within the server that would 'look' at the characters? ... but there may be one that I am not aware of.

Note that using a long varchar blob to store binary data will cause more overhead in the server because, by default, a long varchar column will build a character index for any blob larger than 8 pages.

---

Hmmm, I guess the low level operation that builds the character index is looking at the character data... so if your character set is multibyte I can imagine that the last byte or two in the blob could be mangled if it was not a 'well formed' character.

[Former Member]

Thanks Mark.

[Breck_Carter]

@Mark: I always assumed that mangling would only occur when the varchar-holding-binary-data crossed the client-server boundary and was subject to all the stuff that happens there (transalation etc).

Now you are saying that, depending on what a particular byte value actually is, INSERT, UPDATE, SELECT, COMPRESS and DECOMPRESS operations inside the server could mess that byte up?

Methinks I'm gonna switch away from VARCHAR if I can't trust it to protect my ones and zeros 🙂

[VolkerBarth]

In my understanding guessing, the internal index would only be modified during INSERT and UPDATE, so only these operations might be suspicious here...

I always assumed that mangling would only occur...

That's been my strong assupmtion, as well. Oh well...