Database

In the application I've been working on, we have the requirement to handle unique constraint errors gracefully. It wasn't really hard, I just had to check for OracleException.Code == 1. The trick was the testing. Testing a unique constraint error was not a problem. The issue is verifying that other exceptions are bubbled up properly. The basic exception handling code is like this: 1 protected bool HasUniqueConstraintError( Action databaseAction ) 2 { 3 try 4 { 5 databaseAction(); 6 return false;...

[Update Oct ‘09 In response to a number of comments on this post regarding problems with this approach, I’ve added a new blog entry with more details. Check it out.] Those with a SqlServer background will be familiar with the UPDATE .. FROM syntax. For example (totally made up) 1: update employee_bonus 2: set bonus = 0 3: from employee_bonus b 4: inner join employees e on b.employee_id = e.employee_id 5: where e.bonus_eligible = 'N' Those who transitioned from SqlServer to Oracle might find the absence...

I encountered a problem with Oracle's MERGE DML yesterday. What I was trying to do is use the "Upsert" feature of the merge, except with no distinct source. All of the searching I did for the MERGE showed examples of how to merge data from a source into a target. In my case, I thought the source and the target were the same. This is what I initially tried. It didn't work. It turns out that if the source (s) returns an empty set, then the merge is effectively a no-op 1: merge into MY_TARGET t 2: using...

It seems that I have encountered a scenario where many aspects of C# 2.0+ come into play. I needed to handle conversion from an IDataReader.GetValue() result to a generic type, including Nullable<>. It took me a while to figure it out and with a little help from this snippet, I was quite please with the result. So, I decided to share. 1: private static T NullValue<T>( object testValue, T nullValue ) 2: { 3: T returnValue; 4: if( testValue is DBNull ) 5: { 6: returnValue = nullValue; 7:...

I have started on a new project and am looking forward to implementing continuous integration (CI). There are quite a few decisions that need to be made as far as how we want to handle the build and deployment process. I am working with a small group of people who have worked together in the past. They have some practices that have worked well for them. I don't want to rock the boat too much, so I am evaluating their process with what I know and am familiar with myself. The biggest difference between...