Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

id question

Posted on 2004-04-01
11
Medium Priority
?
133 Views
Last Modified: 2013-12-24
Is there a way to get a database primary id  before inserting the data?
0
Comment
Question by:Isaac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10734484
Hello,

The short answer is: not reliably.  Especially if multiple users are on the app, or multiple apps access the table.

Depending on your database platform, you can specify a column as an identity or sequence column, and have the insert statement return the value of the record just inserted.

If only your CF app is accessing the table, you can use a 'select max(mycolumn) from mytable' query along with your INSERT query inside a <cflock> and get consistent results.

Let me know if you'd like more elaboration on either of these methods.

Take Care,

Seth
0
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10734487
Also,  What database platform are you on?
0
 
LVL 5

Author Comment

by:Isaac
ID: 10734665
I am using access for now.  Some time in the future I may switch to ORACLE.
0
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 5

Author Comment

by:Isaac
ID: 10734675
I'm new to Coldfusion.  Do you mind elaborating?
0
 
LVL 5

Accepted Solution

by:
Seth_Bienek earned 1000 total points
ID: 10735303

Well, since Access does not support Identity columns (SQL Server) or sequences (Oracle, MySQL), your options are a bit more limited.

The easiest approach for you would be to open your access database and change the datatype of your primary key to "AutoNumber", then you will need to run two queries to do your insert:


<cflock scope="application" timeout="10" type="exclusive">
<cftransaction>
<cfquery datasource="...">
 INSERT INTO MYTABLE (col1, col2) VALUES ('#val1#', '#val2#')
</cfquery>
<cfquery datasource="..." name="newid">
select max(mytableid) as maxid from MYTABLE
</cfquery>
</cftransaction>
</cflock>

The value of #newid.maxid# will contain the row id of your new record.

The <cflock> will suspend other locked code from executing while this executes, to prevent it running for two users simultaneously and the instances stepping on each other.

The <cftransaction> is insurance - it will send the queries to the database server together so that they will most likely be run sequentially (unless your DB server is extremely busy)

The first table will do your insert - note that since your primary key column ('mytableid' in this example) is now an autonumber type, you don't need to provide a value for this in your query - in fact, if you do, Access will throw an error.

The second query returns the highest numeric value  of the 'mytableid' column in that table.  Since the queries run so close together and are locked, we are counting on this to be the id of the record that was just inserted.

Whew!  That was a little verbose.  But hopefully it makes more sense to you now.  Any more questions?

Regards,

Seth
0
 
LVL 5

Author Comment

by:Isaac
ID: 10761240
What would be the effect of your code if there were simultaneous hits to the database.

Thanks.
0
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 10761298
The code that I provided would handle simultaneous hits gracefully.

Using <cflock> essentially single-threads a section of code.  Not the entire app, but the code within cflock.  So if two people requested the template at the exact same time, one of them would be queued while the other insert took place.  It happens so fast that it would most likely not even be noticible to the end user, But you are protected against simultaneous inserts, and ID's being swapped.

If you're on Access, then the likelyhood of two simultaneous requests is extremely low anyway (Access does not support the kind of traffic that would need to be hitting the app for this to be a concern).

Take Care,

Seth
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 10782386
What is the reason you need the id before the data is sent to the database? Are you providing the record number to the user before they submit their form or on a confirmation? I may have a solution for ya, just need to see what ya got and what you're trying to do.

K'rgds,
~trail
0
 
LVL 5

Author Comment

by:Isaac
ID: 10805088
I needed the id so I could pass it to another page.
I am new to coldfusion, so I'm using ASP experience to solve Coldfusion problem, which I'm sure is the wrong idea.
Anyway, the code in ASP is below.  How can I do the below in Coldfusion?

The code below lets me get the id of the record added to the database.

Set rConn = Server.CreateObject("ADODB.RECORDSET")
      rConn.LockType = 3
      rConn.Open "tblTest", con
      rConn.addNew()
      
      rConn("fname") = fname
      rConn("lname") = lname
               
               id = rConn("uid")

rConn.Update()
rConn.Close
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10815402
The equivalent would be a cfquery

<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>

and to get the ID back out you would enclose this in a transaction if the id is a autoincrementing field.

<cftransaction action="begin">
<cfquery datasource="yourdsn">
INSERT INTO tbl_Test (fname, lname)
VALUES(#fname#, #lname#)
</cfquery>

<cfquery datasource="yourdsn" name="GetID">
SELECT MAX(id) AS NewID FROM tbl_Test
</cfquery>
<cftransaction action="commit">
</cftransaction>

Then you would access the newID as

#GetID.NewID#
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10815419
Oh I just saw that Seth already gave you this answer higher up - so I am not sure why you were still asking for how to convert it into Cold Fusion code.

If you switch to Oracle or SQL then you may have options such as the @@Identity to grab the just inserted ID.

Hope these answers help.
0

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 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