BoBi wrote:
>> I would like to get the value assigned to sid by Mysql back into my
>> client Java application
>> preferably with the one sql statement I'm already using to insert.
>> Only if really necessary
>> with a second sql statement. What is the best way to programm this?
David Harper wrote:
> The best solution is to use Connection.prepareStatement(String sql, int
> autoGeneratedKeys) to create a prepared statement:
>
> String sqlString = "INSERT INTO country(countryCode,countryName)
> VALUES(?,?)";
>
> PreparedStatement pstmt = conn.prepareStatement(sqlString,
> Statement.RETURN_GENERATED_KEYS);
>
> assuming that your table has columns named countryCode and countryName
> to store the country code and name respectively.
>
> Then insert a row using code such as
>
> pstmt.setString(1, country.getCode());
> pstmt.setString(2, country.getName());
>
> int rc = pstmt.executeUpdate();
>
> // rc is the number of rows actually inserted
>
> and retrieve the auto_increment value generated by the server like this:
>
> ResultSet rs = pstmt.getGeneratedKeys();
If only you were using PostgreSQL, you could use their extension to the
INSERT
command:
[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
> The optional RETURNING clause causes INSERT to compute and return
value(s)
> based on each row actually inserted. This is primarily useful for
obtaining
> values that were supplied by defaults, such as a serial sequence number.
> However, any expression using the table's columns is allowed. The syntax
of
> the RETURNING list is identical to that of the output list of SELECT.
<http://www.postgresql.org/docs/8.2/interactive/sql-insert.html>
--
Lew
|
