Re: Consistently insert into two MySQL tables.

mebe a écrit :
Hi Folks,

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?
I appreciate any help,
Thanks in advance!

AFAIK mysql does not support transactions... maybe you can switch to postgresql?
.

Relevant Pages

  • Consistently insert into two MySQL tables.
    ... The database looks like this: ... itemName VARCHARDEFAULT NULL, ... Inserts a new row into table 'ViewCount' using ... The main problems I've encountered are how can i query the newly ...
    (comp.lang.java.databases)
  • Re: Consistently insert into two MySQL tables.
    ... itemName VARCHARDEFAULT NULL, ... The main problems I've encountered are how can i query the newly ... AFAIK mysql does not support transactions... ... It seems from the above that it is InnoDB tables. ...
    (comp.lang.java.databases)
  • Re: Consistently insert into two MySQL tables.
    ... itemName VARCHARDEFAULT NULL, ... Inserts a new row into table 'ViewCount' using ... The main problems I've encountered are how can i query the newly ... for the connection. ...
    (comp.lang.java.databases)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: [Q]Problems related to the MySQL linked Server.
    ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
    (microsoft.public.sqlserver.odbc)