Re: Character encoding problem STILL
Hi James, set these and try again set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; regards anandkl On 11/9/07, James Sherwood [EMAIL PROTECTED] wrote: Hey all, So I decided to start fresh on a test machine just to see if I could get french characters working. These are my steps: Freshly Installed Mysql 5.0.45 Configure the MySql Server now Detailed Configuration Developer Machine Multifunctional Database Online Transaction Processing Enable TCP/IP Networking and Enable Strinct Mode Best Support for Multilingualism Install as Windows Service Created a DB CREATE DATABASE testdb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; Created a table: CREATE TABLE `testtable` ( `id` INTEGER(11) NOT NULL, `textfield` mediumtext, PRIMARY KEY (`id`))TYPE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; SHOW VARIABLES LIKE 'char%' produced all utf8 Trying INSERT INTO `testtable` VALUES (1,'é û ê à è'); produced incorrect string value DID: SET NAMES utf8; SET CHARACTER SET utf8; Trying INSERT INTO `testtable` VALUES (1,'é û ê à è'); produced incorrect string value Replaced: sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION with: sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION in the my.ini file restarted mysql Tried INSERT INTO `testtable` VALUES (1,'é û ê à è'); and was accepted Select returned nothing in the textfield field Tried SET NAMES utf8; SET CHARACTER SET utf8; INSERT INTO `testtable` VALUES (1,'é û ê à è'); again select returned nothing in the texfield field. Can someone PLEASE explain as to what I am doing wrong and why I cannot get french characters working in mysql 5?? Thanks --James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, After I dump the database in and change everything to utf8(or if I try to dump and make them all utf8 on the dump) I get an incorrect string value error when trying to put french characters in. Any ideas?. --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 6:16 PM Subject: Re: Character encoding problem Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, I too strict mode off on advice of a post in a forum. It allowed me to enter the items but they do not go in.. I am using EMS mysql manager sql editor to do it. This is my variables: Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ The SHOW FULL COLUMNS FROM `newsletter`; says the colums are utf8 and the table is utf8 When I do this statement it basically makes the field blank: SET NAMES utf8; SET CHARACTER SET utf8; update `newsletter` set `newsletter`.`story` = é û ê à èé û ê à èé û ê à è where `newsletter`.`news_id` = 35; Can anyone shed some light on what I am doing wrong? --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 7:37 PM Subject: Re: Character encoding problem Hello, After I dump the database in and change everything to utf8(or if I try to dump and make them all utf8 on the dump) I get an incorrect string value error when trying to put french characters in. Any ideas?. --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 6:16 PM Subject: Re: Character encoding problem Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
James Sherwood wrote: Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? You can convert it using iconv: http://climbtothestars.org/archives/2004/07/18/converting-mysql-database-contents-to-utf-8/ There's a Win version available if you don't already have it: http://gnuwin32.sourceforge.net/packages/libiconv.htm Be sure to edit the CHARSET refs in the dumpfile, though: sed -i 's/latin1/utf8/g' dump.sql This article may be useful: http://dev.mysql.com/tech-resources/articles/4.1/unicode.html brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
thanks to Warren for his comment on the examples... i modified my code trying to get rigth result, but still no luck. doing a deeper research on comparing the data stored into the database and the data stored in the wav file at the same time, i notice that there is a character that is not properly represented, in fact, is not represented at all! this character is: \ (backslash). it is never written in the database, resulting on the missmatch of the sizes of the data (wavfile=176684 bytes, database=14181 bytes) i don't know how a simple character can create such a difference in the sizes! :S i was wondering if this problem may be related with the null endind problem suggested earlier by Warren. am i rigth? does anybody have any suggestions??? as you can see, i really need some help, and would be thankfull for any reply... karima On 8/15/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: do you know about any sample code on using BLOB columns using c++ to create querys??? C++, eh? I happen to be the MySQL++ maintainer. Two of its example programs, cgi_image and load_file, deal with BLOBs. http://tangentsoft.net/mysql++/ Notice the automatic escaping, and the use of C++ strings, both of which render the null issue irrelevant. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: character is: \ (backslash). Of course that will cause problems. This is the escape character in SQL, used extensively when inserting BLOB data. Again, I believe MySQL++ would have prevented this problem, because its escape manipulator would have escaped the backslashes. If you don't want to use MySQL++, then use mysql_escape_string() (or mysql_real_escape_string()) in the C API, which MySQL++'s escape manipulator is based on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
uhmmm... i'll give a try to mysql_real_escape_string(), which i understand formats the data into a valid sql string (without any special characters, like \ and ' ). shoud i use an analog function when retriving the data? because if i encode the data, shouldn't i decode it? if so, which function should i use? thanks for your replys karima On 8/16/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: character is: \ (backslash). Of course that will cause problems. This is the escape character in SQL, used extensively when inserting BLOB data. Again, I believe MySQL++ would have prevented this problem, because its escape manipulator would have escaped the backslashes. If you don't want to use MySQL++, then use mysql_escape_string() (or mysql_real_escape_string()) in the C API, which MySQL++'s escape manipulator is based on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: uhmmm... i'll give a try to mysql_real_escape_string(), which i understand formats the data into a valid sql string (without any special characters, like \ and ' ). shoud i use an analog function when retriving the data? because if i encode the data, shouldn't i decode it? The mysql_real_escape_string() function simply escapes special characters. It will turn your single backslash into a double backslash, which is interpreted as an actual backslash rather than a special character. When you retrieve the data there is no need to decode it with any special function. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: i'm capturing raw audio/video data and want to store it into mysql. in my c++ program i create the query to do this, i use sprintf to create this query using %s format for the printing. Um, you are aware that C strings (which sprintf uses) are null-terminated, and that nulls will be _everywhere_ in raw video and audio data, right? You need to dig up some sample code on using BLOB columns. That will show you how to insert raw binary data into a table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
i know about the null terminating character, but i don't think this is the problem. comparing with the wav file, i notice that there are some characters changed, e.g. rigth data: 82 wrong data: rigth data: ^ wrong data: so, i don't think that this is the main issue that i'm facing here... any toughts? On 8/15/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: i'm capturing raw audio/video data and want to store it into mysql. in my c++ program i create the query to do this, i use sprintf to create this query using %s format for the printing. Um, you are aware that C strings (which sprintf uses) are null-terminated, and that nulls will be _everywhere_ in raw video and audio data, right? You need to dig up some sample code on using BLOB columns. That will show you how to insert raw binary data into a table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: i know about the null terminating character, but i don't think this is the problem. It might not be your immediate problem, but you will run into it eventually. rigth data: 82 wrong data: rigth data: ^ wrong data: That should only happen if your column is set as a non-binary type, which is a bad idea, for the reasons I've pointed out already. Character set conversions do not affect binary columns. See: http://dev.mysql.com/doc/mysql/en/blob.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
thanks for answering... i already know about these datatype, actually i'm using longblob as datatype; which is why i don't really understand what's going on!!! regarding on your previous message, do you know about any sample code on using BLOB columns using c++ to create querys??? best regards, karima On 8/15/05, Warren Young [EMAIL PROTECTED] wrote: Karima Velasquez wrote: i know about the null terminating character, but i don't think this is the problem. It might not be your immediate problem, but you will run into it eventually. rigth data: 82 wrong data: rigth data: ^ wrong data: That should only happen if your column is set as a non-binary type, which is a bad idea, for the reasons I've pointed out already. Character set conversions do not affect binary columns. See: http://dev.mysql.com/doc/mysql/en/blob.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
Karima Velasquez wrote: do you know about any sample code on using BLOB columns using c++ to create querys??? C++, eh? I happen to be the MySQL++ maintainer. Two of its example programs, cgi_image and load_file, deal with BLOBs. http://tangentsoft.net/mysql++/ Notice the automatic escaping, and the use of C++ strings, both of which render the null issue irrelevant. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/charset-upgrading.html An$lia Louren$o [EMAIL PROTECTED] wrote: Hi all! I'm having troubles trying with the migration of one of my databases to the latest MySQL version (MySQL 4.1). My problem is that some of the tables and some of the fields have special characters like $ $ $ . Is there any way I can set a character set that allows me to work with it? All my other databases migration went just fine and it is too bad not been able to use the latest features... Thanks! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding problem-have read docs and search mailing list -but..
in addition i also need to save and load korean, simplified chinese, traditional chinese, japanese and english in the same record. have any suggestion ? __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]