Discussions

EJB programming & troubleshooting: ejbCreate() and primary key with MS SQL Server

  1. ejbCreate() and primary key with MS SQL Server (1 messages)

    Hello,

    I would like to use a CMP bean 'Person'. My database is Microsoft SQL Server 2000. This means that I do not have a notion of 'sequence', like in Oracle, that will generate a primary key before inserting a record into the database.

    How do I generate a primary key for a CMP for MS SQL Server?

    Sincerely,
    Sergei Batiuk.
  2. Hi Sergei,

    MSSQL Server's IDENTITY values are not the same as Oracle's sequence, but maybe this helps anyway:

        /**
         * This methods returns the last identity value that
         * was generated by the MS SQL Server when a new
         * _identity_ database record was inserted into the
         * DB. This method works only if the Connection
         * is the same connection that was used to insert
         * the record. Also note that the underlaying
         * database table must be configured to use identity
         * primary key values.
         *
         * @param connection the connection that was
         * used to insert the record.
         * @return the newly created id of the
         * previous inserted record
         */
        protected int getNewId(Connection connection)
            throws SQLException
    {
            Statement stmt = connection.createStatement();
            String sSql = "SELECT @@IDENTITY";
            ResultSet rs = stmt.executeQuery(sSql);
            rs.next();
            int new_id = rs.getInt(1);
            stmt.close();
            return new_id;
    }