Hi everyone,
I'm following up on my own question. There appears to be a bug in the way
how QUOTE() interacts with the mysql client.
Using --raw mode can apparently solve the problem reported in my original
post:
shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Pitt\'s Place'
That seems about right, but it doesn't solve the whole problem. Consider
this:
shell> mysql -e "INSERT INTO foo VALUES('Joe\'s place\tthat\'s nice');" test
(note: there is a <TAB> between 'place' and 'that')
Let's retrieve this from the db, with and without --raw:
shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Joe\'s place that\'s nice'
shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Joe\\'s place\tthat\\'s nice'
None of those results can be re-inserted as is. The only possibility to
fix this would probably be to add a new option (how about --medium-raw ?)
to the mysql client. All else would break existing scripts.
Thomas Spahni
On Tue, 15 Mar 2005, Thomas Spahni wrote:
> Dear list,
>
> I don't understand what happens here:
>
> shell> mysql -N -e "SHOW VARIABLES LIKE 'version';" test
> +---------+------------+
> | version | 4.0.14-log |
> +---------+------------+
>
> shell> mysql -e "CREATE TABLE foo (sometext VARCHAR(255));" test
> shell> mysql -e "INSERT INTO foo VALUES('Pitt\\'s Place');" test
> shell> mysql -N -e "SELECT QUOTE(sometext) FROM foo;" test
> +-----------------+
> | 'Pitt\'s Place' |
> +-----------------+
>
> So far so good; exactly what I would expect. The string is nicely escaped
> with ONE backslash. But now, look at this:
>
> shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
> 'Pitt\\'s Place'
>
> Double backslash in batch mode. Same result if I pipe the query into
> mysql. Why? This can't be fed into any INSERT query. Bug or feature?
>
> Any comments from the list are very welcome.
>
> Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
