How to Retrieve the Autoincrement Id from a Prepared Statement

Is there a way to retrieve the autoincrement ID from a prepared statement

Yes. See here. Section 7.1.9. Change your code to:

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

stmt.executeUpdate();
if(returnLastInsertId) {
   ResultSet rs = stmt.getGeneratedKeys();
    rs.next();
   auto_id = rs.getInt(1);
}

Get autoincrement id after an insert query performed with a prepared statement

Are you sure the last query you preformed was an INSERT?

mysqli->insert_id seems the proper answer:

Return Values

The value of the AUTO_INCREMENT field that was updated
by the previous query. Returns zero if
there was no previous query on the
connection or if the query did not
update an AUTO_INCREMENT value.

Using a prepared statement with auto increment field

Change your statement to not include the id (or the exact name) column:

String sql = "INSERT INTO Users (username, password, email, firstname, lastname)"
    + " values (?,?,?,?,?)";
//...
st.setString(1, username);
st.setString(2, password);
st.setString(3, email);
st.setString(4, firstname);
st.setString(5, lastname);
//...

Insert using PreparedStatement. How do I auto-increment the ID?

Leave the column out of the INSERT statement entirely. It will be generated by the database engine. Your query should be:

INSERT INTO employee (time, name)
VALUES (?, ?)

Secondly, you have to perform the insert first, then get the keys out of the result.

I believe your code should be:

PreparedStatement preparedStatement = 
    connect.prepareStatement("INSERT into employee (time, name) VALUES (?,?)", 
    Statement.RETURN_GENERATED_KEYS);

preparedStatement.setTimestamp(1, 
    new java.sql.Timestamp(new java.util.Date().getTime()));                           
preparedStatement.setString(2, "Test");

preparedStatement.executeUpdate();

ResultSet tableKeys = preparedStatement.getGeneratedKeys();
tableKeys.next();
int autoGeneratedID = tableKeys.getInt(1);

Note this example does not check the success of the executed statement or the existence of returned keys.

How to get the generated ID of an auto increment column after insertion of a record in HSQLDB within same connection?

You can use the identity() method in the next INSERT statement for the child table. In this example the child table has a ref_id column for the foreign key plus a data_col for its data. The child table has its own primary key column and a foreign key column that references the log_id column of the test table.

create table child(child_id integer identity primary_key, ref_id integer, data_col varchar);
alter table child add constraint child_fk foreign key (ref_id) references test(log_id);
insert into child(ref_id, data_col) values (identity(), ?);

Prepared statements and auto_increment error

Try this:

$sql = $connection->prepare("INSERT INTO users (first_name, last_name, email, username, password) VALUES(?,?,?,?,?)");

This should work even if you add the id column back into your table. It should also work without it.

Since the first column is to be ignored, you have to specify which columns you are inserting into. The first column does not get ignored just because you called it id, or just because it is auto_increment. SQL will do what you tell it to, and it just assumes that the first parameter you give it is supposed to go in the first column of the table.

Often times, queries are written like the above, including the table names, so that something doesn't break in the event that someone modifies the table for some reason. Granted, you shouldn't have random modifications in a production situation either way, but you get the idea.

Keep in mind that the columns need to be specified in the same order as you're planning on inserting them.

Java prepared statement not working with auto increment

Its solved. I was doing a mistake. I had to set a default value in the database.

Issue On Inserting Auto Incremented ID in MySQL Using Prepared Statement

Just omit the id in the query i.e.

INSERT INTO MyGuests ( name, age, email) VALUES (?, ?, ?)

It will automatically add the incremented id, hence the name :)

Get last inserted auto increment id in mysql

Try using an alias

rs = st.executeQuery("select last_insert_id() as last_id from schedule");
lastid = rs.getString("last_id");

---

Related Topics