Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45

    Unanswered: Get Id from insert query

    Hi, I have a table called tblAdvert with column: adId, adName, adDate, etc.
    When I insert row to the MySQL database, is it possible to get then new inserted adId in the same query as the insert.

    Thank you for all replies.

    ManhQuynh

  2. #2
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45
    Hi,

    For you who are interested in this query, I've found the solution.

    To retrieve a column from your newly inserted record try this. This is tested with a MySQL database.

    String sql2 = "INSERT INTO tblindex (indexWord) VALUES (?)";
    String sql3 = "INSERT INTO indexbytext (indexId, adId) VALUES (?,?)";

    Context ic = new InitialContext();
    DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/yourDB");
    Connection conn = ds.getConnection();

    PreparedStatement pstmt2 = conn.prepareStatement(sql2);
    PreparedStatement pstmt3 = conn.prepareStatement(sql3);

    pstmt2.setString(1, keyWordArray[i]);
    pstmt2.executeUpdate();
    ResultSet rs = pstmt2.getGeneratedKeys();
    if(rs.next()){
    int indexId = rs.getInt(1);
    System.out.println("inserted id for indexId: " + indexId);
    pstmt3.setInt(1, indexId);
    pstmt3.setInt(2, textId);
    pstmt3.executeUpdate();
    }
    rs.close();
    pstmt2.close();
    pstmt3.close();
    conn.close();

    Enjoy.

    //M

Posting Permissions

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