Re: @@IDENTITY correct syntax?

From: gnazio (usenet.user_at_email.it)
Date: 12/13/03

  • Next message: Roger Sheldon: "Which JDK version does Oracle 9.2 support for stored procedures?" 
    Date: 13 Dec 2003 13:43:21 -0800

    mikekelly100@hotmail.com (Mike) wrote in message news:<3de80bff.0312130744.5315bb9f@posting.google.com>...
    > Hi,
    > I am new to MS SQL and JSP and am having trouble finding documentation
    > or code examples for this feature.
    >
    > I do an INSERT into a table with an IDENTITY column, then want to
    > retrieve the value from the incremented IDENTITY column in the row
    > I've just created. (Called "threadID".) I then insert "threadID"'s
    > value into another table.
    >
    > Does the following appear correct? (And should I be using
    > SCOPE_IDENTITY instead of @@IDENTITY?)

    Does it works?

    >
    > // insert new thread
    > String threadQuery = "insert into threads (forumID, threadTitle,
    > views) values ('"+forumID+"','"+threadTitle+"','"+views+"') select
    > @@IDENTITY as 'Identity'";
    > myStatement.executeUpdate(threadQuery);
    > ResultSet rs = myStatement.GetResultSet();
    > int threadID = rs.getInt("Identity");
    >
    > // insert new post
    > String postsQuery = "insert into posts (threadID, forumID, userName,
    > comments, submitDate, picURL, picCaption) values
    > ('"+threadID+"','"+forumID+"','"+userName+"','"+comments+"','"+submitDate+"','"+imageURL+"','"+imageCaption+"')";
    > myStatement.executeUpdate(postsQuery);
    >
    > Thanks,
    > Mike

    Try this and let me know...
    ...
    // insert new thread
    String threadQuery = "insert into threads (forumID, threadTitle,views)
    values ('"+forumID+"','"+threadTitle+"','"+views+"')";
    myStatement.executeUpdate(threadQuery);

    ResultSet rs = myStatement.executeQuery("select @@IDENTITY");
    rs.next();
    int threadID = rs.getInt(1);
    rs.close();

    // insert new post
    String postsQuery = "insert into posts (threadID, forumID, userName,
    comments, submitDate, picURL, picCaption)
    values('"+threadID+"','"+forumID+"','"+userName+"','"+comments+"','"+submitDate+"','"+imageURL+"','"+imageCaption+"')";
    myStatement.executeUpdate(postsQuery);
    ...

  • Next message: Roger Sheldon: "Which JDK version does Oracle 9.2 support for stored procedures?"