INSERT INTO PC (PC_NUM, MYCOL2, MYCOL3) VALUES (#seqtestnextval#, '#trim(form.firstval)#', '#trim(form.secval)#') The problem with this is that if I manually enter a record in this table and manually enter a value for PC_NUM, the sequence is not called, and does not know this happened, so the next time the coldfusion app tries to enter a record, it throws a unique_constraint error because it is trying to add the last number in the sequence (which already exists via the manual entry). So, to combat this problem, I've added a trigger. This works fine with manual entries, but increments by two when inserting from the application, since the app & the trigger are both running the sequence. I realize that sequences may not create consecutive numbering sometimes, but this doesn't seem right. How should I be handling this? Thanks in advance!!!! [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => tbemcf14 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> How to Handle Primary Key Auto Increment? - Coldfusion - Advanced Techniques

How to Handle Primary Key Auto Increment? - Coldfusion - Advanced Techniques

Hello, I'm needing some guidance with primary key generation. Oracle 8i db, I've got a table (PC) that uses unique numbers for the pk. Currently, before inserting a record into this table, I perform the following query to get the next number to be inserted in th ID column (PC_NUM): <CFQUERY NAME='getnextnum' DATASOURCE='myds' dbtype='Oracle80'> SELECT (MAX(PC_NUM)+1) AS maxid FROM PC </CFQUERY> <cfset PCNum = getnextnum.maxid> From what I've been reading, this won't work very well if two users are performing inserts at the same time. So, now I'm trying to use a DB sequence (Increment by 1, no max) with ...

  1. #1

    Default How to Handle Primary Key Auto Increment?

    Hello, I'm needing some guidance with primary key generation. Oracle 8i db,
    I've got a table (PC) that uses unique numbers for the pk. Currently, before
    inserting a record into this table, I perform the following query to get the
    next number to be inserted in th ID column (PC_NUM): <CFQUERY
    NAME='getnextnum' DATASOURCE='myds' dbtype='Oracle80'> SELECT (MAX(PC_NUM)+1)
    AS maxid FROM PC </CFQUERY> <cfset PCNum = getnextnum.maxid> From what
    I've been reading, this won't work very well if two users are performing
    inserts at the same time. So, now I'm trying to use a DB sequence (Increment
    by 1, no max) with the following code in coldfusion: <CFQUERY
    NAME='getnextval' DATASOURCE='myds' dbtype='Oracle80'> SELECT
    SEQ_PK_AUTO.nextval as NextVal FROM DUAL </CFQUERY> <cfset seqtestnextval =
    getnextval.NextVal> <!--- Insert New Record ---> <CFQUERY
    NAME='insertnextval' DATASOURCE='myds' dbtype='Oracle80'> INSERT INTO PC
    (PC_NUM, MYCOL2, MYCOL3) VALUES (#seqtestnextval#, '#trim(form.firstval)#',
    '#trim(form.secval)#') </CFQUERY> The problem with this is that if I manually
    enter a record in this table and manually enter a value for PC_NUM, the
    sequence is not called, and does not know this happened, so the next time the
    coldfusion app tries to enter a record, it throws a unique_constraint error
    because it is trying to add the last number in the sequence (which already
    exists via the manual entry). So, to combat this problem, I've added a
    trigger. This works fine with manual entries, but increments by two when
    inserting from the application, since the app &amp; the trigger are both
    running the sequence. I realize that sequences may not create consecutive
    numbering sometimes, but this doesn't seem right. How should I be handling
    this? Thanks in advance!!!!

    tbemcf14 Guest

  2. #2

    Default Re: How to Handle Primary Key Auto Increment?

    tbemcf14 ,

    The main question is: Do you need to have the user view the next ID to be
    placed?

    If no, don't print it and use an insert statement similar to the following:

    INSERT INTO PC (MYCOL2, MYCOL3)
    VALUES ('#trim(form.firstval)#', '#trim(form.secval)#')

    Oracle will automatically use the next value for you. Don't worry about it.

    If yes, you have a few decisions to make.

    As you mentioned, if two users are viewing the page at exactly the same time,
    they would get the same number as the next ID. This can not be avoided. What I
    have done in the past is use this method:

    <CFQUERY NAME="getnextnum" DATASOURCE="myds" dbtype="Oracle80">
    SELECT (MAX(PC_NUM)+1) AS maxid FROM PC
    </CFQUERY>

    In the table have the id as follows:

    <td>#getnextnum.maxid# **</td></tr></table>
    **Next ID entered will be on or near this number

    Something along those lines letting the user know that they will have a number
    near there is usually sufficient. 100% accuracy will never happen so
    disclaimers usually suffice.

    Hope this helps.

    -Phil

    Another Phil Guest

  3. #3

    Default Re: How to Handle Primary Key Auto Increment?

    Another Phil, Thanks for the reply... works great... looks like my problem was
    including PC_NUM in the insert statement. I can just leave it out and let the
    trigger/sequence handle it. There's no need for me to display the numbers to
    users before the insert takes place. I used to, but I'd rather show them the
    generated number that is 100% accurate. Thanks again!

    tbemcf14 Guest

Similar Threads

  1. Auto increment VALUE attribute extension
    By arzo2000 in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: November 11th, 05:55 PM
  2. Auto Increment increase by 100 at a time?
    By Gary@garywhittle.co.uk in forum MySQL
    Replies: 2
    Last Post: November 23rd, 04:50 PM
  3. id after auto increment from a form
    By Faith in forum ASP
    Replies: 7
    Last Post: August 6th, 05:18 PM
  4. Auto Increment a number field
    By jaycee in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 11:22 PM
  5. Howto: Auto increment Primary Key in SQLServer
    By Wali Akthar in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 12:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139