[sclug] URL encoding/decoding question

[Roland Turner SCLUG]

On Thu, 2006-02-23 at 12:27 +0000, Pieter Claassen wrote:

> To tell you the truth, I have long ago switched to a BLOB with MySQL 4.0
> to get rid of the utf8 issues but didn't have any luck with that (i.e.
> still had the character mangling on the browser->db->browser round trip).

Umm, how long ago; before or after reloading the database to change
encoding?

Do you understand that, once the data is stored as a BLOB (and you've
switched to using byte[]/(In|Out)putStream instead of
char[]/String/Reader/Writer of course), you are _completely_ isolated
from any character mapping stuffups in the database? i.e. That any
remaining problem has _nothing_ to do with the database?

> The current situation with MySQL seems to be that it doesn't support
> preparedstatement with query caching so even though you can switch query
> caching off in 4.1, it is difficult to confirm that it is actually being
> disabled.

You've misunderstood, it's not a question of not _supporting_ it,
prepared statement execution is typically _faster_ than than using the
query cache and, as a result, MySQL doesn't even refer to its query
cache when you are executing a prepared statement.

I don't understand why you are raising this now though; what has it to
do with your character encoding problem?

> The issues seems to be that (quoted from MySQL site
> http://dev.mysql.com/doc/refman/4.1/en/query-cache.html)
> 
> Note: The query cache is not used for server-side prepared statements. If
> you are using server-side prepared statements consider that these
> statements will not be satisfied by the query cache. See Section 17.2.4,
> ?C API Prepared Statements?
> 
> -Shock, horror!!!!!!

No, as above, this is a reasonable design.

> As far as I am concerned, this breakes Tomcat authentication completely.

No, it doesn't. It just means the prepared statements are executed as
prepared statements, rather than fetched from a query cache. But again,
this is a performance tweak that's pretty much irrelevant until you've
got your character encoding/interpretation round trip sort out.

> Any workaround suggested? Well, other then move db's? postgress is
> starting to look REALLY attractive!

Well, as you know, I think PostgreSQL is the better option anyway, but
am still concerned that, as you are looking in the wrong place for your
problem (once you've switched to BLOB and byte-oriented interfaces, the
database _cannot_ contribute to character set problems), porting your
code to another database will port the bug too.

Where are those Ethereal dumps? These will tell us exactly what's going
on, rather than messing about with random tinkering. (Empirical method:
measure first.)

- Raz