| Author |
Topic |
|
budgie
Starting Member
18 Posts |
 Posted - 2004-11-11 : 08:23:59
|
| I am currently building a credit card processing page. Our card processor is a COM object that calls a remote server to authorize the card. My idea at the moment - as I am not sure if it is possible - is to dump the card details into a database table and then get the SQL server to request the authorization, and use the app to poll the database every few seconds to see if the card processing has been completed.To do this I was thinking of writing a trigger to call the COM object and update the database table. The problem is does a trigger execute before or after the insert call has returned functionality to the app? If it does before it returns to the app then it isn't an option cause then the polling wont work and I need it to return to the app for the polling to work.Is this possible? And if the trigger isn't the way to go what would be?Any suggestions would be very helpful.Graham |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-11-11 : 08:43:06
|
| can't comment on the immediate problem...but be aware that triggers execute once be 'insert/delete/update'...regardless of the number of rows inserted/delete/updated at each stage.ie....insert 1000 rows into a table in 1 insert statement...will generate 1 call to the trigger. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-11 : 08:46:04
|
| I would recommend against having the trigger call it, because it can hang the operation if the COM object doesn't respond. Also, INSERTs can be multi-row operations, writing triggers as single-row ops invariably causes problems later on. And if you did insert multiple credit card payments in one INSERT, then you'd have to write a cursor or loop to repeatedly process the COM calls...performance is gonna drop like a rock.Putting the data into a table is fine, and having a job run periodically to process the cards would be an option. This way you can "batch" multiple cards at a time, but the downside is that the application does not give instant feedback. Your other option is to have a stored procedure handle the entire operation. It accepts the payment info (one payment at a time), calls the COM object, and can roll back the whole thing if it fails, or save it for processing later using the job mentioned above. This lets your program continue if there's a problem with one payment, and you don't lose any data. |
|
|
|
budgie
Starting Member
18 Posts |
Posted - 2004-11-11 : 08:46:15
|
quote:
Originally posted by AndrewMurphy
can't comment on the immediate problem...but be aware that triggers execute once be 'insert/delete/update'...regardless of the number of rows inserted/delete/updated at each stage.
Yeah I know that having written trigers before. However I haven't written one like I need right now.Graham |
|
|
|
budgie
Starting Member
18 Posts |
Posted - 2004-11-11 : 09:02:40
|
quote:
Originally posted by robvolk
Your other option is to have a stored procedure handle the entire operation. It accepts the payment info (one payment at a time), calls the COM object, and can roll back the whole thing if it fails, or save it for processing later using the job mentioned above. This lets your program continue if there's a problem with one payment, and you don't lose any data.
The problem with using ia stored procedure is that it doesn't allow the app to carry on with the execution while it is running which is what I require. (Or does it? My experience says no.)Using a job isn't an option for the reason you stated. I am not too concerned about the transaction cause I want it to report both failures and successes so my app - which should be polling - can tell what to do next. e.g. return to card details form or go to the order completion page. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-11 : 09:06:05
|
| I don't think the database server should be the one verifying credit card numbers -- sounds like something the client should be doing. Definteily not a job for trigger. Code like this should be a regular executable or COM object or portion of your application code, not on the database layer. If it needs to run on server of some sort, it should be on an application server, not a SQL Server.sure, your code can USE the database to retrieve a list of numbers to verify, and indeed it might update the database tables to indicate success or failure for each, but that code should not be executing on a DB server and definitely not in a trigger, in my opinion.- Jeff |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-11 : 09:13:59
|
| Graham,What technology are you using for the page processing (asp.net,asp,other..) ? |
|
|
|
budgie
Starting Member
18 Posts |
Posted - 2004-11-11 : 09:17:05
|
quote:
Originally posted by jsmith8858
I don't think the database server should be the one verifying credit card numbers -- sounds like something the client should be doing. Definteily not a job for trigger. Code like this should be a regular executable or COM object or portion of your application code, not on the database layer. If it needs to run on server of some sort, it should be on an application server, not a SQL Server.sure, your code can USE the database to retrieve a list of numbers to verify, and indeed it might update the database tables to indicate success or failure for each, but that code should not be executing on a DB server and definitely not in a trigger, in my opinion.
I did think about this. However as the app is a web app I need a way of executing a script in the background and returning straight away to the app so it can do the polling to see if the transaction has complete - successful or otherwise.I haven't been able to work out how I can get classic ASP to start an external script which does the processing as yet. Although I am investigating this option to.Graham |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-11 : 09:31:49
|
quote:
I haven't been able to work out how I can get classic ASP to start an external script which does the processing as yet. Although I am investigating this option to.
.NET makes this much easier to perform asynchronous processing. But an option might be to pass the request to MSMQ (a fast process which will free up the page quickly) and have a custom windows service that polls the queue for incoming messages, processes the request (ie card validation/processing) then updates some status table in the db. Your asp app then polls the table for updates.In this scenario MSMQ acts as a message broker between the application and card processing component. |
|
|
|
budgie
Starting Member
18 Posts |
Posted - 2004-11-11 : 12:59:10
|
quote:
Originally posted by ehorn
In this scenario MSMQ acts as a message broker between the application and card processing component.
<sarcasm>Now if only I knew about MSMQ or wasn't being pressured to get this job done by the PHB!</sarcasm>I have a quick look at MSMQ and it does look ideal for what I wanted to achieve. However both time constraints and knowledge mean that it isn't an option for this time around.Oh well guess it is back to the drawing board.Graham |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-14 : 20:33:21
|
| The following article discusses a useful pattern for your purposes:http://msdn.microsoft.com/msdnmag/issues/03/12/DesignPatterns/ |
|
|
|
budgie
Starting Member
18 Posts |
Posted - 2004-11-16 : 10:09:40
|
quote:
Originally posted by ehorn
The following article discusses a useful pattern for your purposes:http://msdn.microsoft.com/msdnmag/issues/03/12/DesignPatterns/
Unfornately, that wasn't an option due to the fact that we are a classic ASP house and not an ASP.NET house. I have, however come to a compromise that seems to be working quite well with all the tests that I have done thus far. Knowing my luck and cc processing pages it will start having problems as soon as I put it live  Graham |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-16 : 18:59:41
|
| Good to hear you found a solution! Though if you are in need of alternatives do have look at that article as the solution can be easily applied to classic ASP apps as well - we have used this technique for slower running pages to good effect. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-16 : 23:58:32
|
| Did this a while ago by having an app poll a table continuously.The call centre system put an entry in the table requesting an authorisation and the app went away and did the business - inserting the data into another table when complete. The call centre app then picked up the result and continued with the workflow.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-17 : 03:33:10
|
| I don't suppose your credit card processing "supplier" allows XML does it?We've done several COM credit card payment gateways, and a number of XML ones, and the XML ones were much MUCH easierKristen |
|
|
|
|
calling a COM object from a trigger