SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: PK Insert Error

  1. #1
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PK Insert Error

    This may belong in a Web Application Forum but it also relates to this category as well.

    I would like for anyone who consumes our web data provider layer to only have to call UpdateX and if the object did not exist in the database it would do an INSERT. If the object does exist then I think an UPDATE should be done instead.

    Now for me to be able to get around the fact that if I attempt to do an UPDATE and the object does not yet exist, or I try to do an INSERT and the object does exist, should I do a REMOVE first and then do the INSERT?

    the other option could be for me to

    have the ID property in all of our objects as a READONLY property that can only be defined internally. When the enduser calls the DataProvider Update method we check to see if the ID is there and if it is then we call an UPDATE otherwise we do an INSERT.
    --
    Regards
    Josh

  2. #2
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thinking about it more I don't understand why there should be a setter for your ID attribute. That adds to the risk that a rogue developer could come along create the object with an ID already in the DB and change all of its properties to these crazy values. Right?
    --
    Regards
    Josh

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do the INSERT first, and if the return code is successful, you're done, otherwise do the UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that returned in the ExecuteNonQuery method?
    --
    Regards
    Josh

  5. #5
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm finding that if the Primary Key is broken on the insert that the ExecuteNonQuery throws an exception instead of returning a response value
    --
    Regards
    Josh

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, man, i have no idea what you're talking about

    objects? properties? methods? these are all part of the application language that interfaces with the database, they have nothing to do with the database itself

    perhaps you would like to have your thread moved to a specific language forum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    then catch the exception and handle it.

  8. #8
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,652
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I also vote to move this to the ASP.NET forum or maybe the general app development forum. This really is an application-layer issue--the sql bit is simple with stored procedures.

    Insofar as what to do, it really depends on requirements. Inside the data access layer, I tend to have the objects figure out if they need to be inserted based on null or default id values.

    The real trick you have here is how to figure out if those remote clients accessing your web service have the necessary permissions to update the records they wish to update . . .

    Off Topic:

    Pfft . . . pfft . . . catching exceptions is NOT a way to handle business logic. They are very, very expensive items to throw and catch

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    [exceptions] are very, very expensive items to throw and catch
    how does that particular expense compare with a SELECT?

    lots of people do a SELECT, and based on whether it returns a row, then they decide whether to do the INSERT or the UPDATE

    that's 2 separate database calls, right? and note: on every single instance

    whereas doing the INSERT first, followed by the UPDATE if necessary, that would be 1 database call, or 2 in some cases, with the net result being that it's faster, on average
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This last post is exactly what I WANTED to say.... thanks r937.

    Application Layer or not... How do you deal with the fact that your Database Connection in the Application Layer throws an exception when you attempt to INSERT an object that already uniquely exists (e.g one that will be breaking PK Constraint)? If it already exists then I want to UPDATE it.
    --
    Regards
    Josh

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jtucholski View Post
    Application Layer or not... How do you deal with the fact that your Database Connection in the Application Layer throws an exception when you attempt to INSERT an object that already uniquely exists (e.g one that will be breaking PK Constraint)? If it already exists then I want to UPDATE it.
    switch to mysql which has the INSERT ... ON DUPLICATE KEY UPDATE syntax.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Off Topic:

    Pfft . . . pfft . . . catching exceptions is NOT a way to handle business logic. They are very, very expensive items to throw and catch
    i hear these two things a lot (exceptions are not for business logic, exceptions are expensive) but no one has ever explained them. can you tell why, or point a previous discussion?

  13. #13
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    longneck good call on the INSERT....ON DUPLICATE KEY UPDATE...thats what we need.
    --
    Regards
    Josh

  14. #14
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,652
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    @r937: actual expense of database calls depends on systems and some local topography. But I suspect the expense of the exception is at least equal, especially considering the application has to make the call to generate the exception. I also suspect database-level errors make for a more expensive call on the DBMS end of things, as error handling tends to be expensive. Furthermore, if your DBMS supports stored procedures, this can easily be wrapped in one, getting the incident down to one trip to the DB.

    @longneck: Good question. I did some quick searches and could not find the wonderful articles I base my statements on. But basically, exceptions require stopping execution and walking back up to the stack. And stack walks are expensive. You are much better off dealing with "expected" errors with conditional statements rather than catching "expected" exceptions and dealing with things in a catch block.

    Short anecdotal evidence: a friend of mine was working on an app, and buried deep within was a method which was throwing and catching an exception 30% of the time. They tracked it down and fixed it, causing something like a 100% performance gain overall.

    @jtucholski: You can handle this in a number of ways. Easiest might be to wrap the save calls in a stored procedure that UPDATEs when it needs UPDATing and INSERTS when it needs INSERTing. Something like:

    Code:
    CREATE PROC Object_Save
    (
        @ID int,
        @Value varchar(50),
        @InsertId int OUTPUT
    )
    AS
    IF EXISTS(SELECT ID FROM Objects WHERE ID=@ID)
    BEGIN
       UPDATE Objects
       SET (Value=@Value)
       WHERE ID=@ID
       SET @InsertId=-1
    END
    ELSE
    BEGIN
       INSERT INTO Objects (Value)
       VALUES (@Value)
       SET @InsertId=SCOPE_IDENTITY()
    END
    (NB: that is Ms-Sql specific, as well as typed on the go, so your milage may vary)

    Another option would be to make your business object be able to devine on its own if the primary key exists and if it should call an UPDATE or INSERT method. This works best if your primary keys are auto-incriments or GUIDs, as you can just have a default value--eg: Int.MinValue or GUID.NewGuid.

    Bottom line is that you need to have conditional logic somewhere to prevent this from creating an error. Moreover, if this is a publically accessible service API, you need to make sure there are proper checks in place to make certain that people are not updating (or reading) inappropriate bits of data.

  15. #15
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wwb_99,

    For the cost of exception handling, does the cost only factor in if the exception is actually thrown? If you have the try-catch block wrapped around and the exception rarely gets thrown is it any different than not having it all in those instances?
    --
    Regards
    Josh

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,652
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    From what I have read, they made the try bit of exception handling very cheap at the cost of making the catch bit very expensive.

    Still, you should not be using a try-catch for business logic here but that should rather be modeled somewhere in the application.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Still, you should not be using a try-catch for business logic here but that should rather be modeled somewhere in the application.
    this is diametrically opposite and in complete contradiction with your other advice to bury this logic inside the database in a stored proc

    in my opinion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,652
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Depends on where one sees the database here. I handle these things in code, mainly because there tend to be other rules surrounding UPDATE vs INSERT and they should be centralized, and I try and keep significant logic out of the database for maintainability's and testability's sake.

    OTOH, one can make the argument that the stored procedure is a part of the application and it is valid to have the logic of the mechanics of saving data there. Especially if there is little difference in the business rules of INSERTing vs UPDATing a record.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    OTOH, one can make the argument that the stored procedure is a part of the application and it is valid to have the logic of the mechanics of saving data there.
    yes, that's what i do

    and i try real hard never to use the word layer in this context
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •