0

The app that I'm currently working on has a search functionality. When searching for a specific string for e.g,. "4 for £3" which matches a particular column of a table row, the search doesn't return any results although the backend DB has correctly stored that as the value in the column for a particular row.

The log passes the search string as "4 for £3". I'm not sure from where does the additional 'Â' character get appended to my search string(search_str). I tried the following workaround to search for the exact string from the backend but it doesn't seem to work -

Workaround -

params[:search_str].include?('Â') ? params[:search_str].gsub!('£', '£') :

Also, just for additional info when say trying to same thing in IRB, I get the following -

irb(main):020:0> a = "2 for £7"
=> "2 for \302\2437"
irb(main):021:0>

irb(main):013:0> a.include?('Â')
=> true

The same command when executed in Rails, returns false when calling the action from the search button clicked from the view file. Not sure why this abnormal behavior, is it probably because Rails treats "2 for £7" as "2 for £7" and not "2 for \302\2437" ?

I verified that '\302' and '\243' are the octal equivalents as given in this page. I also did have a look at a similar question, but it didn't seem to help my case.

Updated Question

The database.yml is -

development:
  adapter: mysql
  database: db_name 
  username: 
  password: 
  host: localhost 

dev-rails:
  adapter: mysql
  database: 
  username: 
  password: 
  host: 
  port: 

dev-delayedjobs:
  development

test:
  adapter: mysql
  database: db_name
  username: 
  password:  
  host: 
  port:

Results returned as part of the query - show variables like 'char%';

character_set_client utf8

character_set_connection utf8

character_set_database latin1

character_set_filesystem binary

character_set_results utf8

character_set_server latin1

character_set_system utf8

character_sets_dir /app(.. some path..)

halfer
  • 19,824
  • 17
  • 99
  • 186
boddhisattva
  • 6,908
  • 11
  • 48
  • 72
  • you said the DB is storing the value correctly, but the problem is on search. What about on 'get'? (`select * from MY_DB`) - are the values correct there? – dax Apr 28 '14 at 11:25
  • If I'm doing sel * from my_db through a mysql workbench for instance, it returns the correct value. But when I'm displaying it in the browser web page, in one page it displays the pound symbol appropriately but whereas in another page it displays the � symbol instead of displaying the pound symbol. The place where it is displaying the pound symbol correctly has the below code with page source - ` 2 for £3 `. The place where it doesn't display properly has source code - `2 for �3 ` – boddhisattva Apr 28 '14 at 11:37
  • It looks like your db is using UTF8 but rails is using Latin1 or some other smaller character set. Can you add to your question the following: 1) the results of going into mysql and typing `show variables like 'char%';` 2) the contents of your `config/database.yaml` file (minus passwords if you're using them) – Max Williams Apr 28 '14 at 13:22
  • @MaxWilliams - Thanks for your comment, question is updated. – boddhisattva Apr 29 '14 at 13:14
  • Can you try adding `encoding: utf8` to your `config/database.yml` file, in the relevant section (`development` or `dev-rails`, whichever you're using). Then restart your rails server and try the search again. – Max Williams Apr 29 '14 at 14:39
  • @MaxWilliams I can try that, but how would I test that this change will not affect all the other view files as part of the system ? What are the side effects adding the encoding setting to database.yml .. ? – boddhisattva Apr 29 '14 at 15:03
  • It doesn't make any permanent changes: it affects how data is translated between your database and rails. If it doesn't help, change it back. Make that change and try saving and displaying data with special characters. – Max Williams Apr 30 '14 at 08:59
  • @MaxWilliams, thanks for your response, I'll try this and see if the app works normally. Also, I didn't get you, if I revert the encoding change what other change are you suggesting/referring to based on your above comment that I need to make ? – boddhisattva May 01 '14 at 17:23
  • Nothing, just what i said in my 2nd comment, about your config/database.yml file. – Max Williams May 02 '14 at 09:30
  • @MaxWilliams thanks a lot for your answer , it works.. :). There's just some testing pending to see if the app works well throughout. Kindly post your answer so that I can accept it. – boddhisattva May 05 '14 at 17:50

1 Answers1

0

It sounds like your database is storing data in utf8 and rails is translating it using a different character set (probably latin1). Add this line to your development section in your config/database.yml file:

encoding: utf8

then restart your rails server.

Max Williams
  • 32,435
  • 31
  • 130
  • 197
  • Just observed today that with the encoding: utf8 in database.yml.. The '�' gets replaced with 'ï¿1⁄2'. Any idea what can be done in this case to at least use the earlier encoding. I can't do away with the encoding: utf8 in database.yml at the moment.. because that's being used for the search functionality in the system.. – boddhisattva May 27 '14 at 09:19
  • This is similar to another question i asked which resulted in quite a long conversation. One of the posters there had steps to set all the right options, and convert all the existing data, to properly use utf8 across the board. See if it helps - http://stackoverflow.com/questions/22563225/utf8-data-looks-fine-in-mysql-but-is-broken-in-rails – Max Williams May 27 '14 at 09:38
  • Thanks Max, we're still looking into a fix for the issue. – boddhisattva May 28 '14 at 17:53