0

Is it possible to do a query without inserting into the database table to find out what the next ID will be? Basically just call the database to see what the next ID number is to prefill into a text box? Taking in consideration that multiple users may be using it at the same time. The program needs to be smart enough to know if the number has already been used or not. I truly doubt people would add a new record at the same exact time but just wanted to see what is possible in the fun world of sql and coldfusion.

David Brierton
  • 6,977
  • 12
  • 47
  • 104
  • Most SQL engines have a feature called **auto increment**. Columns specified with this "flag" will increment their value every time a new row is inserted. Thus you don't have to take care of the ID to insert at all. Having your own transaction-safe insert requires locking/reserving IDs. But why would you want to implement something that SQL already takes care of, right? – Alex Oct 15 '15 at 00:57
  • (Edit): *I truly doubt..* That is not taking into account the statelessness of html and the fact that web apps are multi-threaded. With a small user base, it may be unlikely, but it is still *possible*. There is no way to know with *absolute* certainty. To answer your question, the options are db specific. If you do a search, some options are: http://stackoverflow.com/questions/562578/how-do-you-tell-what-next-identity-column-will-be and http://stackoverflow.com/questions/6761403/how-to-get-the-next-auto-increment-id-in-mysql That said, can I ask why (as most databases handle this for you)? – Leigh Oct 15 '15 at 01:06
  • As there are already a number of existing answers that explain the options for the various dbms's, voting to close as a duplicate. – Leigh Oct 15 '15 at 01:40
  • @DavidBrierton - Well, your post was not very specific. Can you please update your question with your dbms and version, and briefly elaborate on what options you have already explored, so folks do not repeat stuff you may already know? :-) – Leigh Oct 15 '15 at 02:30

1 Answers1

1

This can be done but how it is done will depend on the database system. For example in SQL Server the following query would return the last identity value:

SELECT IDENT_CURRENT('test_table_name') AS last_identity;

This could be passed to SQL Server from ColdFusion using the cfquery tag:

<cfquery name="id_check" datasource="my_dsn">
  SELECT IDENT_CURRENT('test_table_name') AS last_identity;
</cfquery>
<output>
<input name="id_check" value="#id_check.last_identity#">  
</output>
Simon Fermor
  • 116
  • 1
  • 11