Forum: Ruby on Rails Check if input string conforms with DB collation

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F28db77b476102f32fe1e06171d46a12?d=identicon&s=25 Saurav Kothari (Guest)
on 2016-05-25 12:53
(Received via mailing list)
What we have is a table with an email field - collation
*latin1_swedish_ci*
because of the limitations of our email service.

+----------------+------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field          | Type       | Collation         | Null | Key | Default |
Extra          | Privileges                      | Comment |
+----------------+------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id             | int(11)    | NULL              | NO   | PRI | NULL    |
auto_increment | select,insert,update,references |         |
| email          | text       | latin1_swedish_ci | YES  |     | NULL    |
               | select,insert,update,references |         |
| moved_to_users | tinyint(1) | NULL              | YES  |     | NULL    |
               | select,insert,update,references |         |
| created_at     | datetime   | NULL              | YES  |     | NULL    |
               | select,insert,update,references |         |
| updated_at     | datetime   | NULL              | YES  |     | NULL    |
               | select,insert,update,references |         |
+----------------+------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

This table is checked when a new user arrives to see if he was already
subscribed.


Comes along a user with characters in their email that would not be
valid
for this particular collation. Take for instance *hsıasdf@test.com*

mysql> select * from user_subscriptions where email='hsıasdf@test.com';
ERROR 1267 (HY000): Illegal mix of collations
(latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='

Would it be possible to determine before saving if a string will adhere
to
the collation rules of database?

I tried using transactions as mentioned in this SO answer
<http://stackoverflow.com/a/37409558/2651076> but it does not work or
maybe
I am not using it right.

I wrapped this in a begin rescue block as a validation method

def validate_collation_again
  email = params[:name].try(:strip)
  begin
    UserSubscription.find_by_email!(email)
  rescue Exception => e
    #handle
  end
end

Is it possible for me to check before any DB trigger to
save/create/find;
if an entry with this particular email will agree with the collation?
Some
regex maybe?
String#encode cannot be checked as the application is built using Ruby
1.8.7

This question is also on StackOverflow
<http://stackoverflow.com/questions/37407604/check-...


Regards,
Saurav Kothari
This topic is locked and can not be replied to.