RE: [Reactor For CF] Duplicate Insert vs. Update?

Bowden, Beth \(NIH/NIEHS\) [E] Wed, 26 Apr 2006 12:10:10 -0700

In theory, your code would look up how constraints are defined in system tables for your database and check to see if any constraints would be violated as part of validation. The code would obviously be database-specific.

Beth

From: Brian Kotek [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 26, 2006 2:58 PM
To: [email protected]
Subject: Re: [Reactor For CF] Duplicate Insert vs. Update?

That is one option, but that results in this:

Table has userID, userName, firstName, lastName columns.
UserID is primary key.

Now I could create as many duplicates with the same userName as I want, even if they have different userIDs. To avoid that, I could apply a unique constraint to userName.

Now what happens if I try to create a new record with a different userID but the same userName as an existing record? The insert will throw an error due to the violation of the unique constraint on userName.

Basically it seems like any way you slice it, it will require some custom code to either: validate in advance any unique constraints the table may have, or to catch these errors at insertion time, and then generate a user friendly error message accordingly.

Maybe some custom code/error handling the only way to solve the problem (I realize Reactor isn't going to do all my work for me). I just wanted to present the issue and see if anyone else had encountered it and thought about a solution.

Thanks,

Brian

On 4/26/06, Doug Sims <[EMAIL PROTECTED]> wrote:

I would switch to an ID field for the primary key, rather than using the username. you can still force that to be unique, just use an ID to locate and identify records.
Doug S.

On 4/26/06, Brian Kotek <[EMAIL PROTECTED]> wrote:

Lets say you have a table called user, with a primary key of userName, and then firstName and lastName columns. If you create a new user and populate a UserRecord with these values and call save(), it will insert this record into the database. This is fine. The problem (as I see it) is this: Later on lets say another new UserRecord is created with the same userName but different firstName and lastName values. When you call save(), it will silently update the existing record instead of telling the user that they are trying to insert a record with a duplicate userName. This seems to be a problem (and a common one at that).

I have a few ideas on how this might be handled:

I could use custom validation code that checks for an existing record and adds an error message to the ErrorCollection. But in that case I'd also need to pass some kind of argument to the validate() method to stop it from doing this check when I am truly editing an existing record and not trying to insert a new one.

I could also not use save() on the record, and instead call the DAO insert() method directly. However, this seems like it is "too late" to be catching something that should probably be caught in the actual validation logic instead, and it would also still mean catching the error and adding something to the ErrorCollection to explain what is wrong.

Am I making sense? I was really wondering if anyone else has run into a similar situation and how they handled it? Also please let me know if you think something is wrong with the premise. Thanks.

On 4/26/06, Doug Hughes <[EMAIL PROTECTED]> wrote:

From my perspective there is no problem…. Am I missing something?

Doug

From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Brian Kotek
Sent: Wednesday, April 26, 2006 2:11 PM
To: [email protected]
Subject: Re: [Reactor For CF] Duplicate Insert vs. Update?

Yep, that's the same method that save() uses to determine whether to insert or update. I just wasn't sure if that is the way others are doing this or if anyone else has even run into the same problem.

On 4/26/06, Bowden, Beth (NIH/NIEHS) [E] <[EMAIL PROTECTED]> wrote:

The base dao.cfc has an "exists" function that returns true or false if the record already exists.

Beth Bowden

Phone: 919-541-3355 FAX: 919-541-3687
E-Mail: [EMAIL PROTECTED]
Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709

From: Brian Kotek [mailto: [EMAIL PROTECTED]]
Sent: Wednesday, April 26, 2006 2:04 PM
To: [email protected]
Subject: [Reactor For CF] Duplicate Insert vs. Update?

Just thought I'd ask in case I'm missing something within the API. I just realized that if a user attempts to insert a duplicate record using the same primary key values as an existing record, when you call save() reactor will treat it as an UPDATE instead of throwing an error due to a duplicate primary key. I'm sure I could create something that checks to see if the record already exists (which is really what save() does under the hood) and skip the save(), but I wanted to ask the group first if they have any other ideas. Thanks.

-- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/

RE: [Reactor For CF] Duplicate Insert vs. Update?

Reply via email to