Results 1 to 6 of 6
  1. #1
    fortwnty420 is offline Member
    Join Date
    Mar 2011
    Posts
    21
    Rep Power
    0

    Default Trouble getting last insert id in Sql Server Using Scope_Identity always returns zero

    Hey community, I'm having big prob with scope_identity and Microsoft Sql Server. In the code below, the record writes to the database properly, and it
    does actually auto-increment, but, the scope_identity() always returns zero. The connection is the same in the update and in the following select statement. Just can't figure it out; any help would be appreciated.

    Using MS Sql Server 2008 Express R2
    Java 1.6 Update 31
    JDBC Type 4 driver from Microsoft website: sqljdbc4.jar

    He is the code that reproduces the error:

    package app.xxxxxxx.api;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Statement;

    public class testScopeIdentity {

    public static void main(String[] args) {

    testScopeIdentity tst = new testScopeIdentity();

    }


    public testScopeIdentity(){


    try{


    Class.forName("com.microsoft.sqlserver.jdbc.SQLSer verDriver");
    String url = "jdbc:sqlserver://127.0.0.1:1340;databaseName=testdb";
    String user = "sa";
    String pw = "password123";



    Connection conn = DriverManager.getConnection(url,user, pw);

    String sql = "insert into location " +
    "(location_name, location_uom, location_quantity, " +
    "location_parent, location_note ) values " +
    "(?, ?, ?, ?, ?)";

    PreparedStatement s = conn.prepareStatement(sql);
    s.setString(1,"Location Name");
    s.setString(2,"Location UOM");
    s.setDouble(3,1.5);
    s.setLong(4,0);
    s.setString(5,"Location Note");

    s.executeUpdate();

    sql = "select scope_identity()";

    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery( sql );

    int new_id = 0;
    if ( rs.next() ){
    new_id = rs.getInt(1);
    }
    rs.close();
    stmt.close();

    System.out.println("New Id: "+new_id);


    }
    catch(Exception e){

    }

    }

    }

    Regards,
    Jeff

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,854
    Rep Power
    19

    Default Re: Trouble getting last insert id in Sql Server Using Scope_Identity always returns

    Have you tried manually via the SQL Server front end?
    What does it do there?
    This strikes me as more of a Microsoft question than a JDBC one.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    fortwnty420 is offline Member
    Join Date
    Mar 2011
    Posts
    21
    Rep Power
    0

    Default Re: Trouble getting last insert id in Sql Server Using Scope_Identity always returns

    There is no way that I know of to test this in the front end of sql server. When I execute an insert statement
    it properly auto-increments the _id field.

    I need the equivalent of "select last_insert_id()" in mysql

    supposedly "select scope_identity()" should work, but, always returns zero.

    If you look at the code I posted, you can see that it is even using the same connection. The code
    I posted executes without error, however, it always returns zero as the last insert id.

    The microsoft ado documentation for this feature shows to use a multiple statement with the insert and the
    select in the same statement. I guess ado can execute multiple statements.

    There's not really much doc out there that I can find on exactly how to do this in Java.

    Jeff

  4. #4
    fortwnty420 is offline Member
    Join Date
    Mar 2011
    Posts
    21
    Rep Power
    0

    Default Re: Trouble getting last insert id in Sql Server Using Scope_Identity always returns

    Everything I read about this command says.. "Don't Do It", but, it seems to be working for me..

    to get the last auto increment value this is working..

    select @@IDENTITY as id

    I guess this can cause some real scope problems, but, for my purposes,
    scope won't be an issue...

    Thanks,
    jeff

  5. #5
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,854
    Rep Power
    19

    Default Re: Trouble getting last insert id in Sql Server Using Scope_Identity always returns

    WHy not use the built facilties for this in JDBC?
    getGeneratedKeys().
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    fortwnty420 is offline Member
    Join Date
    Mar 2011
    Posts
    21
    Rep Power
    0

    Default Re: Trouble getting last insert id in Sql Server Using Scope_Identity always returns

    Thanks, getGeneratedKeys() works well for me. Honestly just didn't know about the command.

Similar Threads

  1. methods and returns
    By captain alge in forum New To Java
    Replies: 4
    Last Post: 03-21-2011, 09:00 AM
  2. Average returns 0
    By jehduncs in forum New To Java
    Replies: 2
    Last Post: 12-03-2010, 06:29 PM
  3. Replies: 2
    Last Post: 10-07-2009, 06:24 PM
  4. Replies: 0
    Last Post: 03-05-2009, 05:45 PM
  5. Replies: 3
    Last Post: 02-28-2009, 09:17 AM

Posting Permissions

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