Hi again Marcel,
now being able to look at your code I can add some comments:
> ...
> ResultSet resultSet = null;
>
> try{
> String insertStatement =
> m"INSERT INTO formulier (invoerdatumtijd, voorletters, " +
> "tussenvoegsel, naam, adres, postcode, woonplaats, " +
> "telefoonDag, telefoonAvond, geboortedatum, geslacht, " +
> "email, groep, vrijeInvoer) " +
> "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
>
> PreparedStatement prepStmt =
> this.connection.prepareStatement(insertStatement);
>
> prepStmt.setTimestamp(1, invoerdatumtijd);
> ...
> prepStmt.setString(14, vrijeInvoer);
>
> resultSet = prepStmt.executeQuery();
1) ------------------^
> prepStmt.close();
2) ------^
> }
> catch(SQLException e){
> if (DEBUG) {
> System.out.println("SQLException in FormBean.insertRow");
> System.out.println(e.getMessage());
> } // end of if (DEBUG)
> throw e;
> }
> finally{
> return new Long(resultSet.getLong("id"));
3) ----------------------^
> }
> }
1) your query is a simple INSERT, the only result from an insert
with ANY relational DB is the count of rows inserted. Typically
one would use prepStmt.executeUpdate() which returns int (the count
of rows affected by insert, update and delete, 0 otherwise). You
CAN use executeQuery and the statement will be executed against
the DB (and even successfully, as you see), the method is defined
to return a not null ResultSet, so it will do so ... but no DB
will give back a row from an insert, so the ResultSet you get
will be empty (resultSet.next() will return false)!
2) prepStmt.close() will close the resultSet too, as you migth
easily read from the JavaDocs.
3) before accessing a ResultSet you MUST do a ResultSet.next()
and only if it returns true you are allowed to do a getWhatever
on it. And, as the finally block even is executed in case of
an Exception, how do you expect the ResultSet then to contain
usefull contents? At this place some RuntimeException will be
thrown in every case (probably NullPointerException) in your
code, as the ResultSet's position is before the first row.
Typical idioms for working with ResultSet are:
// build a query returning rows from the DB
// i.e. a SELECT or a stored procedure ...
resultSet = prepStmt.executeQuery();
// ... then, when expecting 0 to many rows ...
while (resultSet.next()) {
resultSet.getThis(...
resultSet.getThat(...
}
// ... or, when expecting exactly one row ...
if (resultSet.next()) {
resultSet.getThis(...
resultSet.getThat(...
}
else {
// handle error
}
The close of ResultSet, Statement and Connection typically are
done in the finally block, so they get closed even in case of
an error. Have a look at the JDBC tutorial from SUN.
I still guess, you are using MySQL's AUTO_INCREMENT and expecting
it somehow to be returned automagically. As I mentioned before,
you have to do it by hand, after inserting your row into a table
with an AUTO_INCREMENT column via executeUpdate() do something
similar to:
String query = null;
try {
// do your insert stuff here with executeUpdate()
...
// retrieve the auto incremented value
query = "SELECT LAST_INSERT_ID()";
prepStmt = connection.prepareStatement(query);
resultSet = prepStmt.executeQuery();
if (resultSet.next()) {
return new Long(resultSet.getLong(1));
}
else {
throw new Exception("Expected one row, didn't get it!");
}
}
catch (SQLException se) {
throw new Exception("Could not execute '" + query + "': " + se);
}
finally {
try {
if (prepStmt != null) {
prepStmt.close();
}
// close connection to give it back into the pool
// to save resources
// if (connection != null) {
// connection.close();
// }
}
catch (SQLException sqlex) {
// empty by intention
}
}
And a final note: it is good practice to close the connection
when done with the work, connections are a very expensive
resource and are tight, you can't have a connection in every
bean instance for it's whole lifetime, which may be weeks. The
connection.close() in JBoss pooled connections actually does
NOT close it really, but instead returns it into the pool to
be available for the next bean instance, that needs one. Be
aware though, between your INSERT and the corresponding
SELECT LAST_INSERT_ID() you must NOT close the connection, as
the last_insert_id() in MySQL is connection based and will
not work properly with different connections.
regards
Georg
___ ___
| + | |__ Georg Rehfeld Woltmanstr. 12 20097 Hamburg
|_|_\ |___ [EMAIL PROTECTED] +49 (40) 23 53 27 10
PS: Just recognized Per's message, he has pointed out most of
the above already.
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
