Skip navigation
Inkfast
Inkfast
Currently Being Moderated

Advancing variable INT

Nov 1, 2010 1:39 PM

I have an interesting little glitch. I'm advancing a number by 1 to create a unique in a MySql db and cfset worked great until it hit 1000, now I'm getting duplicate entry errors on insert query because my cfset seems to be stuck at that 1000 number. Any thoughts are appreciated!

 

<cfset variable = (GetQuery.variable + 1)>

161 Views   3 Replies   Latest reply: -==cfSearching==-, Nov 1, 2010 2:43 PM
 
Replies
  • -==cfSearching==-
    Currently Being Moderated
    1. -==cfSearching==-,
    Nov 1, 2010 1:55 PM   in reply to Inkfast
    Report

    >> to create a unique in a MySql db

     

    Is there a reason you are not using MySQL's auto incrementing type for this job?

     

    <cfset variable = (GetQuery.variable + 1)>

     

    We need to see more code.

     
    |
    Mark as:
  • Inkfast
    Currently Being Moderated
    2. Inkfast,
    Nov 1, 2010 2:14 PM   in reply to -==cfSearching==-
    Report

    I didn't build it. It was designed for manual entries so, no auto-increment. The cfset number 1 higher was working great until I got to 1000. I figured the number is the problem for some reason.... This is a CartWeaver site.

     

     

     

    <cfquery name="GetMerchSku" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
            select Max(SKU_MerchSKUID) as NewMerchSku
            from tbl_skus
            </cfquery>
           
            <cfset NewMerchSku = (GetMerchSku.NewMerchSku + 1)>
           
            <!-- Insert new sku -->
            <cfquery datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
            INSERT INTO tbl_skus(SKU_MerchSKUID,SKU_ProductID,SKU_Price)
            VALUES('#NewMerchSku#','#GetProducts.Product_ID#','#makeSQLSafeNumber (GetSkus.SKU_Price)#')
            </cfquery>

     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    3. -==cfSearching==-,
    Nov 1, 2010 2:43 PM   in reply to Inkfast
    Report

    Since this seems to be a Cartweaver issue. Have you tried their forums? They seem active and you might get a better answer there..

     

    great until I got to 1000. I figured the number is the

    problem for some reason....

     

    The number 1000 is not a threshold value for any of the CF or mySQL data types. So it is more likely something else is at play.

    http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

     

    select Max(SKU_MerchSKUID)

     

    Start with the obvious. Check the initial SELECT MAX() query and the value after being incremented.  Are they even returning the expected values ..?

     

     

    I didn't build it. It was designed for manual entries so,

    no auto-increment.

     

    Well there are still other ways of handling it. Granted few as simple as auto_increment. Not to mention some are database dependent. I suspect that may be a factor in why it was designed this way. 

     

    >> select Max(SKU_MerchSKUID)

     

    Plus, unless there is some other locking going on that SELECT MAX(..) can be problematic. As there is nothing to prevent two threads from generating the same value under load.  It may not be an issue for most applications, but it is something to be aware of ...

     

    http://mysecretbase.com/get_the_last_id.cfm

     
    |
    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