Quick SQL question

**ednos** · 08-10-2007, 02:34 PM

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?

**Damonte** · 08-10-2007, 03:01 PM

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.

**Pokerface** · 08-10-2007, 03:08 PM

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).

**Dick Blonov** · 08-10-2007, 03:26 PM

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

WaffleHousChef · 08-10-2007, 03:49 PM

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

Quote:

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

**ednos** · 08-10-2007, 07:05 PM

You far and beyond answered my question. Thanks! I wonder how I failed to come across that in all my research.

WaffleHousChef · 08-10-2007, 07:29 PM

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

**Iamthefallen** · 08-12-2007, 01:22 PM

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.

**Twisted_Firestarter** · 08-14-2007, 02:58 PM

Quote:

Originally Posted by WaffleHousChef

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.

**ednos** · 08-14-2007, 03:53 PM

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.

WaffleHousChef · 08-14-2007, 04:41 PM

Quote:

Originally Posted by Twisted_Firestarter

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..

Quote:

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.

**Twisted_Firestarter** · 08-14-2007, 09:23 PM

Quote:

Originally Posted by WaffleHousChef

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!

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?

WaffleHousChef · 08-14-2007, 10:53 PM

Quote:

Originally Posted by Twisted_Firestarter

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.

08-10-2007, 02:34 PM	#1 (permalink)
ednos Join Date: Dec 2006 Location: Massachusetts Posts: 3,836	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?

08-10-2007, 03:01 PM	#2 (permalink)
Damonte Join Date: Feb 2007 Location: Cincinnati, OH Age: 29 Posts: 1,573	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. __________________ \|TG-Irr\| Damonte Irregulars 2142 Training Officer / Irregulars 2142 Platoon Leader TG Primer \| BF2142 Server Rules \| BF2142 SOP's Contact an Admin \| BF2142 Admins \| Join the Irregulars!

08-10-2007, 03:08 PM	#3 (permalink)
Pokerface Join Date: May 2003 Location: MD, USA Age: 30 Posts: 5,773	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). __________________ 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

08-10-2007, 03:26 PM	#4 (permalink)
Dick Blonov Join Date: Feb 2006 Location: In a Vortex! Age: 51 Posts: 3,387	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 __________________ \|TG-6th\|Blonov «Any situation that involves the line "I was ran over by a dumptruck..." can't be good.» eGoatBoy BattleField2 SOPs \| Teamspeak \| Server Rules and SOPs \| The 6th Devil's Brigade

08-10-2007, 07:05 PM	#6 (permalink)
ednos Join Date: Dec 2006 Location: Massachusetts Posts: 3,836	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.

08-10-2007, 07:29 PM	#7 (permalink)
WaffleHousChef Join Date: Feb 2007 Location: Atlanta, GA Age: 33 Posts: 1,099	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.

08-12-2007, 01:22 PM	#8 (permalink)
Iamthefallen Join Date: Aug 2005 Location: Columbia, SC Age: 29 Posts: 933	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. __________________ \| Wearing the TG tags \| Reporting infractions \| How to get started with PoE \|

08-14-2007, 03:53 PM	#10 (permalink)
ednos Join Date: Dec 2006 Location: Massachusetts Posts: 3,836	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. ~ Bertrand Russell 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. ~ Magna Centipede

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Sponsored links

Sponsored links

Sponsored links

Sponsored links