We help IT Professionals succeed at work.

coldfusion 9 send automatic one code from a database

257 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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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


Author

Commented:
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
CERTIFIED EXPERT

Commented:

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


Author

Commented:
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
CERTIFIED EXPERT

Commented:

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?

Author

Commented:
Sends one code(Login, Password) to the client
CERTIFIED EXPERT

Commented:

Ok, then you are all set?

You have the answer..

Author

Commented:
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
CERTIFIED EXPERT

Commented:

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

or


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


Author

Commented:
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)
CERTIFIED EXPERT

Commented:

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

Author

Commented:
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
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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 ;-)
CERTIFIED EXPERT

Commented:
> 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

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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 :)
CERTIFIED EXPERT

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

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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 ;-)

Author

Commented:
Maybe use a tag cftransaction
CERTIFIED EXPERT
Most Valuable Expert 2015

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

Author

Commented:
Can you explain this more
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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.
CERTIFIED EXPERT

Commented:

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

   I wouldn't trust much written there!

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:

Author

Commented:
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.
CERTIFIED EXPERT

Commented:

You can just use  CFQUERY, no procedure is necessary

<cfquery...>
  Begin
     update..

     select..

     update..
  End;
</cfquery>

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.