I've been playing around with the Learn With series and building the backend services in Java. I was going right along until I got to the chapter to create and update data. The query is something like this:
String SQL = "insert into tasks(taskCategoryID, userID, description, completed, dateCreated) values(?,?, ?, 0, ? )
SELECT SCOPE_IDENTITY() as taskID";
This is a super common approach to creating a new item in SQL Server, and then immediately selecting the newly created ID with SCOPE_IDENTITY(). I'd expect it to work easily.
The rest of the code:
PreparedStatement pstmt = connection.prepareStatement(SQL);
// set parameters
LocalDate dateCreated = LocalDate.now();
ResultSet rs = pstmt.executeQuery();
The last line will throw an error claiming that the query returned no results sets and blow up. I had been swimming along smoothly until I ran into that issue. It turns out that, for updates and inserts, that is the wrong approach. Instead of using executeQuery(), I need to use executeUpdate(), like this:
After that, I can get the second result set using this:
ResultSet rs = pstmt.getGeneratedKeys();
Then things are good to go.
You'll probably find the LearnWith series supporting Java next month.