Re: Consistently insert into two MySQL tables.

mebe wrote:
> i am in need of some advice about how to consistently add data into
> two
> MySQL tables. The database looks like this:
> 
>     CREATE TABLE Items (
>                  itemId    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>                  itemName  VARCHAR(255) DEFAULT NULL,
>                  FULLTEXT (itemName)
> 
>     CREATE TABLE ViewCount (
>                  itemId        INT UNSIGNED PRIMARY KEY,
>                  viewCnt       INT UNSIGNED DEFAULT 0)
>                  TYPE = InnoDB
> 
> Both tables are connected through the column 'itemId'.
> How would a SQL statement look that:
> 1. Inserts a new row into table 'Items'
> 2. Query Items.itemId, (note that itemName is not unique)
> 3. Inserts a new row into table 'ViewCount' using
>    ViewCount.itemId = Items.itemId
> 4. Does the whole operation in a thread save way.
> 
> The main problems I've encountered are how can i query the newly
> inserted Items.itemId if all other columns are not unique? And how do
> i make the two inserts plus the one select atomic/thread save?

You do not query. You can get the last auto increment generated value
for the connection.

Something like:

con.setAutoCommit(false);
....
stmt.executeUpdate("INSERT INTO items(itenname) VALUES('bla bla')");
stmt.executeUpdate("INSERT INTO viewcount VALUES(LAST_INSERT_ID(), 1)");
....
con.commit();

Arne

PS: PreparedStatement would be better and you can get the auto increment
     value in a non MySQL-specific way, but you get the idea.