[REQ] Improve handling of hexadecimal notation across all MySQL versions (3, 4 & 5)

[GoogleCodeExporter]

Currently Sequel Pro encodes binary fields as hexadecimal for transport in the 
Standard SQL 
method of X'[hexadecimal encoded data]'.  This is not supported before MySQL 
4.0.

Alternative methods include the ODBC-like 0x[hexadecimal], which is supported 
on 3.x.  However, 
X'' is valid, whereas 0x is not.

We need to review whether to change this across the board, for only MySQL 3.x 
hosts (no easy 
transport between versions), and how to change this.  Is there another way we 
can preserve 
binary/BLOB data?

Original issue reported on code.google.com by rowanb@gmail.com on 15 Feb 2010 at 3:39

[GoogleCodeExporter]

When not escape binary data using mysql_real_escape_string() ?
See also this page: http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Original comment by m.van.de...@gmail.com on 15 Feb 2010 at 4:32

[GoogleCodeExporter]

We do that for strings - however binary is a little worse as it can contain nul 
bytes and other nasties which can 
break strings IIRC...

Original comment by rowanb@gmail.com on 15 Feb 2010 at 4:36

[GoogleCodeExporter]

Null bytes could be escaped as \0. There is \Z (ASCII 26)which means end of 
file on
windows. There is a nice list on the page I linked.

--SNIP
If you want to insert binary data into a string column (such as a BLOB column), 
the
following characters must be represented by escape sequences:
NUL NUL byte (ASCII 0). Represent this character by ‘\0’ (a backslash 
followed by an
ASCII ‘0’ character).
\ Backslash (ASCII 92). Represent this character by ‘\\’.
' Single quote (ASCII 39). Represent this character by ‘\'’.
" Double quote (ASCII 34). Represent this character by ‘\"’.
--SNIP

Also take care not to use a C string internally to hold binary data.
Try inserting it as an std::string instead of using the raw data buffer because 
you
don't want the data treated as a C string, which would truncate the data at the 
first
null character.

But I can\t program a line of cocoa to save my life, and I have to say I have 
the
utmost respect for what you've done with Sequel Pro. :)

Original comment by m.van.de...@gmail.com on 15 Feb 2010 at 4:53

[GoogleCodeExporter]

Original comment by stuart02 on 27 Mar 2010 at 1:49

• Changed title: [REQ] Improve handling of hexadecimal notation across all MySQL versions (3, 4 & 5)
• Added labels: Component-Logic, Milestone-Release0.9, Priority-Medium, Type-Enhancement, Usability

[GoogleCodeExporter]

Original comment by mattlangtree on 15 Mar 2012 at 10:36

• Removed labels: Milestone-Release0.9