Re: Columns named with the # character
Erling and MySQL guys, On Fri, Mar 16, 2001 at 02:18:55AM +0100, Erling Paulsen wrote: I just exported (via myodbc) an old access database for my sportsclub to mysql (the tables). The ms-access database application still works nicely on the new linked tables in mysql. However, I'm now also writing a php based client to use the database over the web and the problem is as follows: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! The mysql client doesn't know about backticks and therefore handles the # (and '-- ') as the beginning of a comment. It wouldn't do that for quoted and double-quoted strings. I see no workaround other than to change the client/mysql.cc source and recompile. Line 983 of client/mysql.cc (in the add_line function) should be changed from: else if (!*in_string (inchar == '\'' || inchar == '"')) to else if (!*in_string (inchar == '\'' || inchar == '"' || inchar == '`')) This is for version 3.23.33. The line number may be different for other versions. If you want to see the correct continuation character for multi-line backticks, you would change a few occurrances in read_lines of: tee_fputs(glob_buffer.is_empty() ? "mysql " : !in_string ? "- " : in_string == '\'' ? "' " : "\" ",stdout); to tee_fputs(glob_buffer.is_empty() ? "mysql " : !in_string ? "- " : in_string == '\`' ? "` " : in_string == '\'' ? "' " : "\" ",stdout); I cc'ed this to the bugs mailing list, so it may be fixed in a next release. Looking at the sources, the MySQL server will handle this just fine, so your PHP scripts should run unless PHP does its own parsing of SQL. (I never use PHP, but I guess it does no such thing). Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
On Fri, Mar 16, 2001 at 10:33:05AM +0100, Fred van Engen wrote: The mysql client doesn't know about backticks and therefore handles the # (and '-- ') as the beginning of a comment. It wouldn't do that for quoted and double-quoted strings. I see no workaround other than to change the client/mysql.cc source and recompile. Oh yes, note that I didn't test these source changes, but they seem simple enough. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Columns named with the # character
I just exported (via myodbc) an old access database for my sportsclub to mysql (the tables). The ms-access database application still works nicely on the new linked tables in mysql. However, I'm now also writing a php based client to use the database over the web and the problem is as follows: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! -- Erling Paulsen Norway - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
At 2:18 AM +0100 3/16/01, Erling Paulsen wrote: I just exported (via myodbc) an old access database for my sportsclub to mysql (the tables). The ms-access database application still works nicely on the new linked tables in mysql. However, I'm now also writing a php based client to use the database over the web and the problem is as follows: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! Use backticks around the column name when you refer to it: `Person#` -- Erling Paulsen Norway -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
I've tried to escape it and also to `bakctick` it - neither seems to work. ex1- backticks: mysql select * from temp01 where `Post#` = 9019; - - It does not compute as finnished and prompts for more, when I finish manually it says: - ; ERROR 1054: Unknown column 'Post' in 'where clause' - ex2 - escaped: mysql select * from temp01 where Post\# = 9019; ERROR 1054: Unknown column 'Post' in 'where clause' - I don't know what to do! I'm wondering if it might have something to do with the characterset? I'm using latin1. The last thing I wanna do is rename all the columns. MYSQL Server Info: mysql status -- mysql Ver 11.12 Distrib 3.23.33, for pc-linux-gnu (i686) Connection id: 131 Current database: tkk Current user: root@localhost Current pager: = 9019; Using outfile: '' Server version: 3.23.33 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 1 day 17 hours 47 sec Threads: 19 Questions: 2457 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 19 Queries per second avg: 0.017 -- - Original Message - From: "Sam Smith" [EMAIL PROTECTED] To: "Erling Paulsen" [EMAIL PROTECTED] Sent: Friday, March 16, 2001 3:21 AM Subject: Re: Columns named with the # character On Fri, 16 Mar 2001, Erling Paulsen wrote: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! try \# -- the \ should remove the specialness of the # Although the number of \ may have to be increased, depending on what it is you are actually doing, and how many times your \ and # are going to get interpretted. Regards Sam -- Disservice: It Takes Months to Find a Customer, But Only Seconds to Lose One. The Good News is We Should Run Out of Them In No Time. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php