At work we use Oracle for most of the web applications that we create. The only exception to that would be our intranet apps. We have many small applications that are created for internal use only. Instead of having to go through our DBA's to get these Oracle schema's created I found it much easier to just use Apache Derby the embedded version. If you did not know already Derby ships with ColdFusion so it's really easy to get up and running.

Yesterday I working on application where a user could enter a number and would have to do a lookup on this number to if we had it. So if we pull the problem out and take a look at it everything seems very simple. So far pretty simple right? A user types ABC-1234 in a text box and we do a lookup based on that. Then the requirement came across that we needed to be able to enter many at one time. No problem, we can just change that to a text area and create pass a list to our query. So far so good except for one small problem. I was not taking into account what the users would actually typing in. Yes our model number in our database is 'ABC-1234' but our user knows it as 'ABC1234'. So we need to be able to account for both numbers coming across. You initial thought would might be to do an or lookup on both, but a closer look may bring this issue to light. If the user types in 'ABC1234' we are essentially saying is 'ABC-1234' IN ('ABC1234') and the answer to that is no.

To do this comparison we have to be comparing the same data on both sides. If we are just thinking out loud here we would want our query to look like this. This will now allow us to account for the user entering the number as they know it, or the number as it actually is in the database. So how do we do this? In pretty much any other dbms we have the ability to use built in functions or write our own custom functions. Most every other dbms has a REPLACE() function and I was using MySql on this project I probably would of done something like this. After taking a look at the Built In Function List I realized that this would not be possible. My next logical step would be to write my own function. I did some digging around at found out that custom functions were written in Java. My first thought was what a pain in the a$$. After writing some functions though I am here to tell you its actually pretty awesome. In part II I will go through the process of writing,creating and deploying these custom functions.

 

This entry was posted on February 16, 2010 at 1:16 PM and has received 3097 views. Comments 2 | Print this entry.