?
Solved

coldfusion 9 send automatic one code from a database

Posted on 2011-04-28
29
Medium Priority
?
234 Views
Last Modified: 2012-06-27
The application I’m creating will be described below:

The users makes the payment e.g. in the paypal system and I receive the reply to the cfc component of the true value

<cfif payment is "true">

 1. This is where the query must be placed to get one the code from the database.

2. I move the code to another column or mark it as used.

3. I mail this code to the user.

</cfif>

Problem

1. How to get one code from the database and which tag functions should be used.

2. There is a possibility that  users will make payments at the same time and receive the same code. How to prevent this from happening.

Send me an exemplary cfc component which will get one code from the database and then send it to the user and mark it as used. This will counteract situations in which one code  is used many times.

I used the following function:

SELECT TOP 1 * or <cfset Row = randRange(1,qInfo.recordcount)>

However, there have been some situations in which I sent the given code to several users.

I use Coldfusion 9, sql server 2008, actionscript 3.0
0
Comment
Question by:bandziohr
  • 11
  • 10
  • 8
29 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35484030
What code are you talking about?   What is the purpose of the code, are you creating the code?


How to come up with unique values...

You can use the session.sesssionID value  (unique to a user's session)

Add date/time stamp to sessionID to make it unique within the user's session

You can create a dummy table with a sequence number (Identity in SQL Server) and just pull the next sequence number from that table


0
 

Author Comment

by:bandziohr
ID: 35485539
I need to send username and password (The account hotfile.com) to the customer.
The login and password can not be repeated in the Message e-mail.
You can send me a simple example of code coldfusion. How to get one code from the database without repetition.

screen shots in the Annex(file)
images.zip
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35485637

Are you saying you want to email the user with a code that will uniquely identify their account in an email so you don't have to specify their username and password?

The easiest way is to use the USER_ID which is the primary key of their table.  

The user_Id is guaranteed not to be repeated, but the problem is that it can be guessed very easily.    To keep it from being guessed, you can hash() the USER_ID along with "salt" or a secret word.

<cfset uniqueCode = hash(getProfile.USER_ID & "my secret word 1234")>

Then you will have a unique entry that cannot be guessed


0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:bandziohr
ID: 35485846
The company (fileserve.com or hotfile.com ) generates my logins and passwords for accounts.
I have to put them in a database or txt file and send to the client. After payment by credit card or paypal

customer pays ---> Accepted payment ---> Sends one code to the client
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35485888

Ok, that sounds like only half the story.


They generate the username and password, you load them into the database and then email that information to the client.  

But you said you don't want to email the username and password?  

> The login and password can not be repeated in the Message e-mail


Where does the code come in?


If you do need a code,  what is wrong with the suggestion in my previous post?

0
 

Author Comment

by:bandziohr
ID: 35485898
Sends one code(Login, Password) to the client
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35485987

Ok, then you are all set?

You have the answer..
0
 

Author Comment

by:bandziohr
ID: 35486022
I'm from Polish my English is not good.
I do not want to send the same code(Login, Password) repeatedly (many times).

      
sending code(Login, Password) ---> I delete or disable the code that was sent an e-mail to the customer
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35486119

Do you want to create a Login that is not repeated?

or


Do you want to make sure you only send ONE email ?


0
 

Author Comment

by:bandziohr
ID: 35486302
1. I have 1000 codes(Login, Password) from hotfile.com

Login,Password
aaaaa,sdfsdf
asfas,afaffaf
xxxxx,vvvvvv

2. Put them in a database.

3. Two clients can not get the same code (Login and password)

Do you want to create a Login that is not repeated?

get one the code from the database. (Login and password)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35486379

when you get a login from the database, just update the record that it has been taken..

Login, Password,  Taken

aaaa,  sdlfjsd,    Yes
bbbb, lkjsdfsdf,  Yes
ccccc, sdlfkjsdf,   No
dddd, sldfkjsd,   No

Select * from table
where Taken = 'No'

Update table
 set Taken = 'Yes'
where Login = get.Login

0
 

Author Comment

by:bandziohr
ID: 35486595
Thank you

If two user pays the same time will not receive the same code.
1000 users click on at the same time button pay the database does not return the same code.

If the following code will work well

Select * from table
where Taken = 'No'

Update table
 set Taken = 'Yes'
where Login = get.Login
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 35486843

If you put the code between BEGIN and END it is extremely unlikely ( 1/1000 of a second) that any other process can interupt.

Here's a method even more secure...

Begin

 Update table
    set  Taken = mySessionID
 where TableID in (select top 1 tableID  from taken where taken is NULL)

 select * from table
 where taken = mySessionID

 if exactly ONE record is returned then use that username, password
 then
 
   update table
     set Taken = 'yes'
   where tableID = #getTable.ID#

 If more than one record was returned,  set taken to No and try again



0
 
LVL 53

Expert Comment

by:_agx_
ID: 35486996
Here's a method even more secure...


That method combined with a guaranteed unique value (like a UUID/GUID) is the best.  But keep in mind an exclusive lock is *not* guaranteed unless you explicitly use one (ie serializable transaction, etc..).  Though you'd probably only see issues under heavy load
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35487028
Update table
set  Taken = mySessionID
where TableID in (select top 1 tableID  from taken where taken is NULL)

update table set Taken = 'yes' where tableID = #getTable.ID#


Since we're setting Taken equal to a unique value ID earlier, I wouldn't even bother setting it Taken = 'yes'.  The fact that it's no longer NULL automatically means it's taken ;-)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35487047
> Since we're setting Taken equal to a unique value ID earlier, I wouldn't even bother setting it Taken = 'yes'.  The fact that it's no longer NULL automatically means it's taken

But, if the same session goes after another login (for some reason) it could conflict, so I reset it to clear the UID

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35487061
That's why I was suggesting using a guaranteed unique ID (each time).  With a UUID/GUID you wouldn't have to worry about it.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35487084
If you put the code between BEGIN and END it is extremely unlikely

Just curious but what's the point of the begin and end? It doesn't actually lock anything, so the potential for issues under high load still exists.   Okay, I must be missing something :)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35487194
> That's why I was suggesting using a guaranteed unique ID (each time).  With a UUID/GUID you wouldn't have to worry about it.

I read on the run, I didn't catch that it was a UUID, I was thinking the same session ID that I mentioned.  Of course with the UUID that would work

> Just curious but what's the point of the begin and end

Meaning that it would within one cfquery (Begin/End optional) but you can't use Begin and end if they are split between two cfqueryies :)  (you know, Begin in one, End in the other, lol)

Just trying to get them together..

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35487269
Lol. Okay.  I read that as a t-sql BEGIN / END ;-)

Since concurrency clashes are still possible under high load, I'd probably go with a UUID plus and explicit transaction lock.  (I know everybody thinks they don't need them. At least not until they get burned once ;-)
0
 

Author Comment

by:bandziohr
ID: 35487585
Maybe use a tag cftransaction
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35487682
Yeah but w/transactions you have to use the right level. The default level doesn't exclusively lock out other threads.
0
 

Author Comment

by:bandziohr
ID: 35487750
Can you explain this more
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35488103
First, there's some questionable remarks in that link.  To understand how transactions actually work, you should read your db's documentation instead.  In reality CF has next to nothing to do with transactions. Other than issuing the BEGIN/END TRANSACTION command, it's all controlled by whatever db you're using.  So your db's API is will explain how *it* handles transactions more accurately and in more depth.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35488200

lol, that blog is written by a frequent asker on this site!!  

   I wouldn't trust much written there!

0
 

Author Comment

by:bandziohr
ID: 35489993
OK last question

If you put the code between BEGIN and END it is extremely unlikely ( 1/1000 of a second) that any other process can interupt.

1. I have to write a function t-sql BEGIN and END
2. Use the cfstoredproc tag which runs the functions of the t-sql
3. In the cfquery tag can't use BEGIN and END

I'm sorry that asks stupid questions. But I do not understand where to put the BEGIN and END.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35490994

You can just use  CFQUERY, no procedure is necessary

<cfquery...>
  Begin
     update..

     select..

     update..
  End;
</cfquery>

0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question