Skip to main content
N
Newsgroup_User
Inspiring
November 7, 2008
Question

MYSQL and CF question

  • November 7, 2008
  • 8 replies
  • 1433 views
Does anyone know how to get the ID of a auto_incremented field in MySQL?

I need to capture the auto_incremented number so I can then pass it on to
my next query and insert it into the next table......


    This topic has been closed for replies.

    8 replies

    J
    JaneUK
    Known Participant
    September 21, 2010

    The Adobe documentation for CFQUERY in CF9 explains the options for each database, including MySQL. Looks like it came in at CF8.

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html

    result_name.IDENTITYCOL

    SQL Server only. The ID of an inserted row.

    result_name.ROWID

    Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.

    result_name.SYB_IDENTITY

    Sybase only. The ID of an inserted row.

    result_name.SERIAL_COL

    Informix only. The ID of an inserted row.

    result_name.GENERATED_KEY

    MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.

    You do need to specify a result name for the query, not just a name as usual:

    result = "result name"

    Nothing for Access, I'm afraid.

    josh_adams1
    josh_adams1
    Participating Frequently
    September 21, 2010

    Unfortunately, in the documentation for ColdFusion 9, we missed that we introduced result_name.generatedkey for ALL databases that support the feature of returning the generated key. Thanks to Todd Rafferty for mentioning this in comments on the documentation page linked to by JaneUK.

    M
    MickDundee
    Participant
    November 15, 2008
    Create a UUID field in the database table then add UUID to the form and then query the database for the UUID number. This will work with all databases.

    This goes into the page with the form submit.
    <INPUT TYPE=HIDDEN NAME="orderID" VALUE="#CreateUUID()#">

    and then run the query on your submitted page after you have inserted the data into the database.
    <cfquery name = "GetOrder" datasource = "database">
    SELECT *
    FROM OrderItems where OrderID = '#OrderID#';
    </cfquery>
    D
    Dan_Bracuk
    Inspiring
    November 15, 2008
    quote:
    Originally posted by: MickDundee
    Create a UUID field in the database table then add UUID to the form and then query the database for the UUID number. This will work with all databases.

    This goes into the page with the form submit.
    <INPUT TYPE=HIDDEN NAME="orderID" VALUE="#CreateUUID()#">

    and then run the query on your submitted page after you have inserted the data into the database.
    <cfquery name = "GetOrder" datasource = "database">
    SELECT *
    FROM OrderItems where OrderID = '#OrderID#';
    </cfquery>

    This approach is good when you are creating your db. You would use the uuid as the primary key instead of the autoincrement as the primary key. It's probably not worth the time and effort to re-write an existing db when there are methods that work for getting the primary key value you want.
    N
    Newsgroup_UserAuthor
    Inspiring
    November 14, 2008
    > Personally I don't like GENERATED_KEY because it's specific to MySQL.

    If one's using MySQL (as per the subject line), I can't see how that's a
    problem... ?

    --
    Adam
    N
    Newsgroup_UserAuthor
    Inspiring
    November 14, 2008
    Azadi wrote:
    > max(id) is far from a good solution.
    >
    > a) [may] require an extra query
    > b) in high-load conditions may return an id inserted by another user's
    > action
    >
    > if you are on cf8 use the GENERATED_KEY var returned buy RESULT
    > attribute of <cfquery> tag

    Sorry, I should have mentioned that you do that inside a transaction.
    Personally I don't like GENERATED_KEY because it's specific to MySQL.

    --
    Mack
    D
    Dan_Bracuk
    Inspiring
    November 14, 2008
    If you have to use max(id), make sure you have as big a where clause as possible to ensure you get the correct one.
    N
    Newsgroup_UserAuthor
    Inspiring
    November 14, 2008
    max(id) is far from a good solution.

    a) [may] require an extra query
    b) in high-load conditions may return an id inserted by another user's
    action

    if you are on cf8 use the GENERATED_KEY var returned buy RESULT
    attribute of <cfquery> tag

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    A
    Anonymous
    November 14, 2008
    Will this work for MS Access also? Or can I only use max(id) with Access for this moment in time? There seems to be a few options - each being database specific, I wondered if there was one for general databases or MS Access in particular?

    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

    Referring to the above page.

    Thanks,
    Mikey.
    N
    Newsgroup_UserAuthor
    Inspiring
    November 14, 2008
    steve grosz wrote:
    > Does anyone know how to get the ID of a auto_incremented field in MySQL?
    >
    > I need to capture the auto_incremented number so I can then pass it on
    > to my next query and insert it into the next table......

    SELECT Max(id) AS id
    FROM table

    --
    Mack
    N
    Newsgroup_UserAuthor
    Inspiring
    November 7, 2008
    add RESULT attribute to your query, i.e. result="myqueryresult".

    then #myqueryresult.GENERATED_KEY# var will give you the inserted id.


    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Daniel_Pride
    Daniel_Pride
    Known Participant
    September 17, 2010

    This is not clear, how do you access the returned longint id value in the script section?

    If you are using a generated cfc like as follows (with the added result=createLineItemsResultId)

        <cffunction name="createLineItems" output="false" access="remote" returntype="any"  result="createLineItemsResultId" >
            <cfargument name="item" required="true" />

            <cfquery name="createItem" datasource="BlueRose" result="result">
                INSERT INTO LineItems (ApptDate, Cid, Iid, Description, Price, Length)
                        VALUES (<CFQUERYPARAM cfsqltype="CF_SQL_TIMESTAMP" VALUE="#item.ApptDate#">,
                                <CFQUERYPARAM cfsqltype="CF_SQL_VARCHAR" VALUE="#item.Description#">,
                                <CFQUERYPARAM cfsqltype="CF_SQL_DECIMAL" VALUE="#item.Price#">)

            </cfquery>
            <cfreturn result.GENERATED_KEY/>       
        </cffunction>

    Then call it passing in a valueObject (selectedLineItem) like

                   createLineItemsResult.token = lineItemsService.createLineItems(selectedLineItem);
                    lineItemsService.commit();

    Where and how do you get the result? tracing the objects lineItemsService and createLineItemsResult I don't see the value anywhere?

    Thanks

    Dan Pride

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices