CODEWITHSUNDEEP
javamysql
trevalexandro
trevalexandro

Reputation: 213

SELECT after INSERT statement?

So my issue is after I insert a new record in the database, I want to do a SELECT query that should include that new record. However, the data being returned excludes the newly added record. It seems like whenever I first open the connection, whatever is already in the database is what my program goes off. I hope this makes sense. All input is appreciated.

Update:

So here is the INSERT snippet

String DML = "INSERT INTO MEMBERS (FIRST_NAME, LAST_NAME, BIRTHDATE, DEATH_DATE, MARITAL_STATUS,"
+ " WEDDING_DATE, SPOUSE_NAME, MILITARY_SERVICE, DATE_JOINED, DEPARTURE_DATE, ACCEPTANCE_MODE, DEPARTURE_MODE,"
+ " RELATED_TO, NOTES) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(DML);
            pstmt.setString(1, jTextField2.getText());
            pstmt.setString(2, jTextField1.getText());
            pstmt.setString(3, jTextField6.getText());
            pstmt.setString(4, jTextField11.getText());
            pstmt.setString(5, jTextField3.getText());
            pstmt.setString(6, jTextField5.getText());
            pstmt.setString(7, jTextField4.getText());
            pstmt.setString(8, jTextField8.getText());
            pstmt.setString(9, jTextField7.getText());
            pstmt.setString(10, jTextField10.getText());
            pstmt.setString(11, jTextField9.getText());
            pstmt.setString(12, jTextField13.getText());
            pstmt.setString(13, jTextField14.getText());
            pstmt.setString(14, jTextArea1.getText());
            pstmt.executeUpdate();
            if (conn.getAutoCommit() == false)
                conn.commit();

Now this is the SELECT snippet which if fired after the INSERT

pstmt=conn.prepareStatement("SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) AS NAME FROM MEMBERS ORDER BY LAST_NAME, FIRST_NAME");
        rs=pstmt.executeQuery();

Upvotes: 1

Views: 689

Answers (3)

Ankit Deshpande
Ankit Deshpande

Reputation: 3604

Something like this can be done:

You can use an autoincrement column say call it serialID.
Insert data normally. When retrieving use MAX(serialID)

SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) AS NAME FROM MEMBERS ORDER BY
LAST_NAME, FIRST_NAME where serialID = (select max(serialID) from MEMBERS);

You'll have to do this in one transaction.

Upvotes: 0

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 136062

If you mean this

    stmt.executeUpdate("insert into t1 ...");
    ResultSet rs = stmt.executeQuery("select ... from t1");

then data inserted in statement 1 is always visible in statement 2, both in autocommit on and off modes.

It is only possibly that select does not see inserted records if you insert in one transaction, do not commit, and read in another transaction

Upvotes: 0

shafeen
shafeen

Reputation: 2457

It is very likely that you have autocommit mode disabled and/or you are running the 2 queries (INSERT then SELECT) as a transaction.

Try turning autocommit mode on and then running the 2 queries again (INSERT then SELECT), it should work.

Upvotes: 1

Related Questions