Announcement

Collapse
No announcement yet.

Quick SQL question

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Quick SQL question

    I learned SQL this month (on Microsoft's SQL server) for my web development job (ASP.NET, if that's relevant information), and I'm encountering an obstacle that I've been able to reliably hack my way around, but my solution makes me uncomfortable. This is important because it has to do with money.

    In the table (we'll call it "payments"), there's a column "payment_pk" which the database fills in automatically whenever a new row is inserted. "pk" stands for "primary key". The rest of the information in the rest of the columns may or may not be unique (which is the problem), but this value is guaranteed to be unique for any row. Now, after inserting a row into "payments", the user is redirected to Paypal, which is instructed to redirect the user back to, say, "/paypal-success" (along with some query information) when the transaction is successful, at which point I want to update the transaction's "payment_confirmed" column in the "payments" table (it's a bit).

    My problem is that I can't determine what the "payment_pk" value is going to be, and I can't rely on the uniqueness of the other columns or the most recent column (for obvious reasons). Each user is given an identification number (which they may or may not be aware of), but they might order more than once, or cancel their order and make another one.

    My current solution is to confirm the most recent transaction made by the user. The problem is that someone could order something very cheap, and while that Paypal page is open, order something expensive, confirm the cheap order, and then fail to pay the expensive order, which would result in the cheap order being unconfirmed and the expensive order confirmed.

    My proposed immediate solution is to either create a new column ("confirmation_code") or modify the "payment_confirmed" column to allow for a unique identifier to be inserted by my application that the "/paypal-success" page would receive and query for.

    The current solution is unstable; the proposed solution is a hack. Is there a way to trick SQL into telling me what the new "payment_pk" value is when I insert a row?
    The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt. ~
    I have a tendency to key out three or four things and then let them battle for supremacy while I key, so there's a lot of backspacing as potential statements are slaughtered and eaten by the victors. ~
    Feel free to quote me. ~

  • #2
    Re: Quick SQL question

    The property you're probably looking to use in Microsoft SQL is IDENTITY. In mySQL it's called AUTO_INCREMENT.

    To get the last IDENTITY value generated by an INSERT, SELECT INTO, or bulk copy statement, use the @@IDENTITY property.


    The guy you want to answer these questions really is WaffleHousChef. He's a professional database guru. And I mean guru in the sense of he's the guy sitting on the top of the mountain that all the wanna-be guru's go ask questions of.

    "Everytime I read your posts I do it with Morgan Freeman's voice in my head as if he is narrating your life" - Aimed

    Comment


    • #3
      Re: Quick SQL question

      http://www.devnewsgroups.net/group/m...opic62098.aspx
      That maybe? Or alternately, the internet also says "If you are inserting a single object, then the id of that object is patched up using @@identity/scope_identity. So if you are looking for the id of the inserted object, that is available."

      My ASP.NET experience is low (but growing). :(
      [volun2]
      NS Game Officer. TF2 Admin. BF2 Admin / Scripter. PM with issues.
      Tempus: Pokerface is nailing it right on the head. Everyone who is arguing against him is simply arguing against reality.
      <anmuzi> it is not permitted to have privacy or anonymity
      <LazyEye> yeah when I play on TG the server digs though my trash

      Arm yourself with knowledge: TG NS TF2 BF2

      Comment


      • #4
        Re: Quick SQL question

        Don't know much about SQL, but I've used before insert triggers in Access. That allows you to know the value before it is inserted in a new row. Or maybe I just don't understand your question...



        DB

        «That looks like a really nice house except for that horrible bathroom.» Donrhos

        | |





        Comment


        • #5
          Re: Quick SQL question

          Damonte's right on this one, you're looking for @@IDENTITY.

          Originally posted by MSSQL Books Online
          Syntax
          @@IDENTITY

          Return Types
          numeric

          Remarks
          After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement....

          ...
          Examples:
          This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

          INSERT INTO jobs (job_desc,min_lvl,max_lvl)
          VALUES ('Accountant',12,125)
          SELECT @@IDENTITY AS 'Identity'
          If you are doing values inserts, or have constrained your statment to single row inserts (with scope and trigger caveats covered in the full Books Online article) you can use @@Identity to grab the value that was assigned to that row Automagically. This is a good thing to use for a return value on your stored procedure (i.e. RETURN @@IDENTITY) if you have a stored proc to dump a record into the payments table.

          This gets tricky if you are doing multi-row inserts, bulk inserts, or passing through alot of triggers. However if you are simply adding payments to a table this variable should be easy to use.

          As a backup, if you are always charging the same amount as the purchase price, you could never confirm an order where the money amount from the purchase does not equal the money amount from paypal. Assuming there is an order status field, and limiting the possibilites to active, non-confirmed orders the one you are looking for should have the same price. This would certainly protect from the small order/big order scenario.

          Code:
          Create Procedure PMT_AddPayment
          	(@CustNo Numeric(18,0),
          	@CustName VarChar(30),
          	@PayAmt Numeric(18,2))
          as
          
          Insert Into Payments
          (CustNo, CustName, PayAmt, ... )
          VALUES
          (@CustNo, @CustName, @PayAmt, ...)
          
          Return @@Identity
          Use the execute method in ADO to call this from your DB, and store the return value to pass to your confirmation page. Then you can just pass the PK value as a parameter to the proc that handles confirmations to pick out the row you need, back that up with the money amount as a second parameter, bam, you're done.

          Code:
          Create Procedure PMT_ConfirmPayment
          	(@PayRow Numeric(18,0),
          	@PayAmt Numeric(18,2))
          as
          
          If @PayAmt <> (Select top 1 PayAmt from Payments where @PayRow = Payment_PK)
            RaiseError (Buncha parameters for custom errorcodes)
          
          Update Payments
          Set PayStatus = True
          Where Payment_PK = @PayRow
          and PayAmt = @PayAmt
          sigpic


          Comment


          • #6
            Re: Quick SQL question

            You far and beyond answered my question. Thanks! I wonder how I failed to come across that in all my research.
            The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt. ~
            I have a tendency to key out three or four things and then let them battle for supremacy while I key, so there's a lot of backspacing as potential statements are slaughtered and eaten by the victors. ~
            Feel free to quote me. ~

            Comment


            • #7
              Re: Quick SQL question

              You're welcome. I love this kinda stuff. Feel free to PM me if you've got any more stumpers that come up. ;)
              sigpic


              Comment


              • #8
                Re: Quick SQL question

                If you're planning to immediately use the newly created record for further manipulation, you'll also want to do a CHECKPOINT before you exit the procedure.

                CHECKPOINT will flush the record to disk and ensure that it is available for your next call. Without it you may find that as you try to insert a record in another table with a foreign key pointing to the record in Payments, you will get an invalid reference exception.

                Comment


                • #9
                  Re: Quick SQL question

                  Originally posted by WaffleHousChef View Post
                  Damonte's right on this one, you're looking for @@IDENTITY.
                  No. @@IDENTITY does not guarantee that it will be the identity of the last autogenerated key for the stored procedure-- it returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
                  If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. There are ways around this, but it's a pain in the ass.

                  Hopefully you are using SQL Server 2005, in which case you can use SCOPE_IDENTITY() which gurantees to return the last identity inserted by the stored procedure you are scoped to.
                  Twisted Firestarter
                  a.k.a |TG| Harkonian
                  sigpic

                  Comment


                  • #10
                    Re: Quick SQL question

                    Unfortunately, it's the version immediately prior to 2005; however, the table is clear of triggers, and the INSERT and SELECT statements are in the same query, which has produced no problems so far. I've tried to screw it up by making simultaneous requests, but either I fail or the method works.
                    The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt. ~
                    I have a tendency to key out three or four things and then let them battle for supremacy while I key, so there's a lot of backspacing as potential statements are slaughtered and eaten by the victors. ~
                    Feel free to quote me. ~

                    Comment


                    • #11
                      Re: Quick SQL question

                      Originally posted by Twisted_Firestarter View Post
                      No. @@IDENTITY does not guarantee that it will be the identity of the last autogenerated key for the stored procedure-- it returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
                      If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. There are ways around this, but it's a pain in the ass.

                      Hopefully you are using SQL Server 2005, in which case you can use SCOPE_IDENTITY() which gurantees to return the last identity inserted by the stored procedure you are scoped to.
                      ahem..

                      Originally posted by WHC
                      This gets tricky if you are doing multi-row inserts, bulk inserts, or passing through alot of triggers. However if you are simply adding payments to a table this variable should be easy to use.
                      The full books online article has all the caveats for the varible (as I mentioned,) I'm sure Ednos has enough sense to have perused that article after he knew what to look for.

                      Scope_Identity() and Ident_Current() are also present in SQL2000, not just SQL2005.
                      sigpic


                      Comment


                      • #12
                        Re: Quick SQL question

                        Originally posted by WaffleHousChef View Post
                        ahem..

                        The full books online article has all the caveats for the varible (as I mentioned,) I'm sure Ednos has enough sense to have perused that article after he knew what to look for.

                        Scope_Identity() and Ident_Current() are also present in SQL2000, not just SQL2005.
                        Well, color me red for skimming over the answers too quickly! :madsmile: I am suitably chastised.

                        I didn't realize scope_identity was available in 2000. If that is the case, why would use use @@identity at all?
                        Twisted Firestarter
                        a.k.a |TG| Harkonian
                        sigpic

                        Comment


                        • #13
                          Re: Quick SQL question

                          Originally posted by Twisted_Firestarter View Post
                          I didn't realize scope_identity was available in 2000. If that is the case, why would use use @@identity at all?
                          If you have the presupposing conditions right for the use of @@Identity, it will be faster and less of a performance hit then running the Scope_Identity() function. The @@Identity value is automagically seeded every time an identitiy insert is made so it's a simple variable lookup, but when you run the function it has to execute the code in the function (and the @@Identity value will be seeded anyways even if you use the function.) I'm not sure this will really matter for this particular application depending on scaling, but I always try and keep everything as lean as possible.
                          sigpic


                          Comment

                          Connect

                          Collapse

                          TeamSpeak 3 Server

                          Collapse

                          Advertisement

                          Collapse

                          Twitter Feed

                          Collapse

                          Working...
                          X