MySQL Concat method can make string searching case-sensitive

I have the following table-structure in the database.
An integer column for the number and a varchar as the prefix.

billings:

billing_prefix VARCHAR(200)
billing_nr     INTEGER NOT NULL
.. and more field, but unimportant for this example

The following record is in the database:

billing_prefix: FatOrders-
billing_nr    : 42

The complete billing number is represented to the client as "FatOrders-42".
Now I have a application that enables the user to search the list with a string. (on several fields)
A client searching for 'fat' results in the following query:

SELECT * FROM billings WHERE CONCAT( billing_prefix, billing_nr ) LIKE '%fat%'

No results..
This is not the behaviour I need :P

Mysql tells me this:

If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form

An simple solution is to modify the query to this one:

SELECT * FROM billings WHERE CONCAT( billing_prefix, CAST( billing_nr AS char) ) LIKE '%fat%'

Vi trick to remember

A few days ago I needed to change the line endings of a text file on a Mac. This Mac did not have Textmate (of course).
I guess every unix system has vi:

:set fileformat=unix
:w

that simple :P

btw. This fix, depends on a fileformats setting (available on mosts systems). When there's no fileformat setting the following command can be given:

:%s/<ctrl>-v<enter>//

Freebsd – Broken Ruby Installation After Update

Yesterday I did a Ruby update of my Freebsd ports.
It mentioned Ruby needed to be updated. I always thinks this is Scary because I have several sites depending on the my Ruby installation.
You need to update once in a while, so I just upgraded it...

portupgrade -bapi

Today I saw my mysql-backup-cron Ruby script gave the following error:

mysql_backup.rb:3:in `require': no such file to load -- rubygems (LoadError)
	from mysql_backup.rb:3:in `<main>

WTF. I tried to run a rake task of my Rails apps.. The same error...

The gems still seem work. The command below nicely showed my Gems.

gem list

After trying to reinstall the rubygems port which didn't do a thing.
I found the solution was to REINSTALL RUBY completely. 

cd /usr/lang/ruby18
make 
make deinstall
make install clean

BTW. My Rails apps were not affected because they run in an RVM environment ...

ActiveSupport Option Hashes

A small blog item, for remembering this method...

When using a (normal) Hash in Ruby on Rails accessing an element with a symbol key or string key results in a different item.

Example
(Notice the use of a string and symbol to access a key)

normal = {'foo'=>"lish"} 
normal[:foo] = 'bar'

results in the hash:

{"foo"=>"lish", :foo=>"bar"}

To solve this issue you can convert every Hash to HashWithInDifferentAccess

special = {'foo'=>'lish'}.with_indifferent_access
special[:foo] = 'bar'

Results in 

{"foo"=>"bar"}

special.class => ActiveSupport::HashWithIndifferentAccess

that more like it :-)

Reopended my Blog

After a few months of offline time I finally reopened my Blog.

I didn't post often but the last few months I had several moments I would love to blog some technical solutions I found to certain problems. But I didn't have a place to put it...

I quickly choose a standard layout, maybe I will update it later.

So see you in the next post...