DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Using newly created primary key in several other inserts

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    Using newly created primary key in several other inserts

    I'm using cold fusion to access Oracle. I'm doing a poll. When I insert a poll, I'd like to get it's primary key p_id to use as referenced info for each "Answer" insert that I do. I'd like to do them in the same query. Here's what I have:


    INSERT INTO fsnep_polls
    (
    p_id,p_date_added,p_question,
    p_status,p_date_last_used
    )
    VALUES
    (
    unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#',
    #Form.p_status#,#p_date_last_used#
    )



    INSERT INTO fsnep_pollAnswers
    (
    pA_ID,pA_pollID,pA_answer
    )
    VALUES
    (
    unique_pollAnswers_Num_s.NEXTVAL,#Form.p_id#,
    '#Form.p_answer1#'
    )

    break;


  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    Insert Into my_table (col1, ...)
    Values (expr1,...)
    Returning col1 into my_var;
    http://download-west.oracle.com/docs...3a.htm#2122360
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2004
    Posts
    51
    I'm still receiving an error, "invalid character"

    INSERT INTO fsnep_polls
    (
    p_id,p_date_added,p_question,
    p_status,p_date_last_used
    )
    VALUES
    (
    unique_poll_Num_s.NEXTVAL,#Now()#,'#Form.p_question#', #Form.p_status#,#p_date_last_used#
    )
    Returning p_id into new_p_id;


    INSERT INTO fsnep_pollAnswers
    (pA_ID,pA_pollID,pA_answer
    )
    VALUES
    ( unique_pollAnswers_Num_s.NEXTVAL,new_p_id, '#Form.p_answer1#'
    )

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    Perhaps you should be more careful where you "insert your poll"

    I remember when this place was cool.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (Nothing to do with invalid char)
    Any reason not to use unique_poll_Num_s.CURRVAL ?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    #Now()# ?

    Not a valid oracle name
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by Mr.Hanky
    Perhaps you should be more careful where you "insert your poll"


  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Not knowing Cold Fusion I'm not sure about all of the variables with '#'.

    Code:
    INSERT INTO fsnep_polls
          ( p_id,p_date_added,p_question,
            p_status,p_date_last_used )
       VALUES 
          ( unique_poll_Num_s.NEXTVAL, SYSDATE, '#Form.p_question#',
            '#Form.p_status#', '#p_date_last_used#' )
          Returning p_id into new_p_id;
    
    INSERT INTO fsnep_pollAnswers
               (pA_ID,pA_pollID,pA_answer )
       VALUES ( unique_pollAnswers_Num_s.NEXTVAL,
                new_p_id, '#Form.p_answer1#' );

  9. #9
    Join Date
    Sep 2004
    Posts
    51
    >unique_poll_Num_s.CURRVAL

    I didn't want to use CurrVal because since multiple people are entering, I might get duplicate vals, right?

    >#Now()# Not a valid oracle name.

    True, it's a Cold Fusion call. The ## should output the current date. I do this alot.

    >Perhaps you should be more careful where you "insert your poll

    This was posted twice as an answer, but I don't understand. Might you elaborate?

    It seems that if I do each insert independantly (and put in a fake new_p_id, I get no errors, so the inserts are OK.
    If I do them both at once or even just the first with the RETURNING line, I get the error.

    UPDATE: I added quotes around '#p_date_last_used#' because of gandalf's post and it now just gives me a "missing comma". Still it doesn't seems as if it should need it, being a date.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by danielkessler
    >unique_poll_Num_s.CURRVAL

    I didn't want to use CurrVal because since multiple people are entering, I might get duplicate vals, right?
    Wrong currval is only for the session that it is being executed in. if you get a sequence and the use the currval, even though someone else may have also gotten a sequence before you access the currval function, you will get your value when you use currval and the others session get their currval when the execute the function. If it didn't work that way then currval would be useless.

    Originally posted by danielkessler
    >#Now()# Not a valid oracle name.
    True, it's a Cold Fusion call. The ## should output the current date. I do this alot.
    I didn't know the answer, but SYSDATE will always work in Oracle.

    Originally posted by danielkessler
    >#>Perhaps you should be more careful where you "insert your poll

    This was posted twice as an answer, but I don't understand. Might you elaborate?
    Its a really bad pun.

    Originally posted by danielkessler
    It seems that if I do each insert independantly (and put in a fake new_p_id, I get no errors, so the inserts are OK.
    If I do them both at once or even just the first with the RETURNING line, I get the error.

    UPDATE: I added quotes around '#p_date_last_used#' because of gandalf's post and it now just gives me a "missing comma". Still it doesn't seems as if it should need it, being a date.
    Not sure.

Posting Permissions

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


Click Here to Expand Forum to Full Width