Skip navigation
meensi
meensi
Currently Being Moderated

CFQUERY always returns the same value

Jun 11, 2012 10:29 AM

Tags: #cfquery

<cfquery name="qryGetMAXID" datasource="#Request.DSN#">

        SELECT

            MAX(FLEET_CON_ID)+1 as MaxFleetId

        FROM

            CONFIGURATION

    </cfquery>

    <cfset intFLEET_CON_ID = #qryGetMAXID.MaxFleetId#/>

    <cfquery name="qryAddOperator" datasource="#Request.DSN#" result="testing">

        INSERT INTO

            CONFIGURATION

        (FLEET_CON_ID,

        COL2,

        COL3,

        COL4)

        VALUES

        (#intFLEET_CON_ID#,

        "ADD OPERATOR",

        '#strCode#',

        '#strName#')

    </cfquery>

 

the Query qryGETMAXID always returns the same value as 18703. I inserted some values into the database directly. the Query should return 18705. When I run the same query in SQL Developer it returns the correct value. I have not cached the query.

Please help me out.

Thanks in advance

531 Views   4 Replies   Latest reply: -==cfSearching==-, Jun 11, 2012 8:31 PM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Jun 11, 2012 11:45 AM   in reply to meensi
    Report

    My suggestion is to let the database determine the value of the id field.  The way to do that depends on what type of database you are using.

     
    |
    Mark as:
  • WolfShade
    Currently Being Moderated
    2. WolfShade,
    Jun 11, 2012 12:00 PM   in reply to meensi
    Report

    This can happen if the queries are being cached.  If you don't have the "cachedwithin" parameter in your CFQUERY tag, check the CFAdmin to see if queries are being cached, there.

     

    @Dan: Sometimes, rarely, there is a reason for manual incrementing.

     

    ^_^

     
    |
    Mark as:
  • meensi
    Currently Being Moderated
    3. meensi,
    Jun 11, 2012 12:08 PM   in reply to meensi
    Report

    Dan and Wolfshade,

    Thanks for the problem.

     

    I figured out the problem, while I insert into the database, I did not commit it. so it was returning the same value.

    sorry..

     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    4. -==cfSearching==-,
    Jun 11, 2012 8:31 PM   in reply to meensi
    Report

    SELECT  MAX(FLEET_CON_ID)+1 as MaxFleetId

     

    Unless you are using locking, a serializable transaction (not the default), etcetera two threads can still obtain the same "MaxFleetId" value. If that will cause a problem in your application, you should consider letting the database determine the next available id as Dan suggested.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points