v5. MySQL insert into tables

[kotinkarwak]

Hi,
Need to implement an update to two linked tables in that, I need to get the last value from first table's auto-increment key to be used in subsequent insert to the linked table.
Within MySQL, can get the value using LAST_INSERT_ID() function but tying two of the insert statements with ebase might be failing at the second tables insert.


What I have used.
1). tried to create the string for both insert statements as would work in Mysql into a sql variable to use in generating the stmt as below

Code: Select all

stmt = conn.prepareStatement(sql1);	

In this first case, stmt.execute() fails at line for second statement

2). Split the two SQL statements, insert first as in statements below

sql1 = "INSERT INTO client \
(`firstname`, `middlename`, `lastname`)\
\nVALUES('abc123','','abc123');"


sql2 = "INSERT INTO credentials \
(`username`, `password`, `Client_student_id`)\
\nVALUES ('abc123', 'abc123', LAST_INSERT_ID());"

Code: Select all

stmt = conn.prepareStatement(sql1);	  
stmt.addBatch(sql2)

//execute
stmt.executeBatch();

This fails with a 'a foreign key constraint fails ' error, believe since the field is not assigned value from LAST_INSERT_ID().

Any other way to capture last value in initial insert to be used in formulating the second SQL statement?


UPDATE 1
Please clarify for me the operation for creating a preparestatement since appears my code is ignoring what I assumed was added as the first statement via the line
stmt = conn.prepareStatement(sql1);

but if I add my statements again, the execution works fine.
Would still be ideal to know other ebase way of capturing the auto-generated keys.

Regards[/u]

[Steve James]

Any reason why you are not using a stored procedure to handle both inserts database side? It saves bouncing from application to database again and again.

Code: Select all

CREATE PROCEDURE insertStuff(
 IN first VARCHAR(25),
 IN last VARCHAR(25),
 IN username VARCHAR(25),
 IN pass VARCHAR(25),
 OUT ID INT)
BEGIN
  INSERT INTO client ('firstname', 'middlename','lastname') 
  VALUES(first,'',last); 

  INSERT INTO credentials ('username','password','Client_student_id')
  VALUES (username,pass, LAST_INSERT_ID());

  ID = LAST_INSERT_ID();
END

I'm not overly familiar with MySQL but it is similar to Oracle.

If you import it as a resource in Ebase you simply call.

Code: Select all

resources.INSERTSTUFF.exec();

[Jon]

The value of any auto-increment columns is fed back after an insert - all you need to do is map the column to a form field. There is a small section on this in the Database Resources documentation under Auto-increment fields http://dev-docs.verj.io/ufs/doc/Databas ... c407616143

[kotinkarwak]

much appreciated.
Implemented both ways.