I'm using JDBC and google app script. I need to insert rows created via an HTML interface, only problem is that rows contain 2 inputs that are represented in different tables of my MYSQL database.
My second table has a foreign key that references the first table (product_id references id from table product).
Here are my tables
product:
id, name
product_quantity:
id, product_id, quantity
Here is what I would like to achieve in pseudo-code:
function update(row){
insertProductSQL = "INSERT INTO products (name) VALUES (" + row.name + ")";
insertProductQuantity = "INSERT INTO products_quantity (product_id, quantity) VALUES (" + /*HERE IS THE PROBLEM*/ + ", " + row.quantity + ")"
var conn = getConnection(); //JDBC connection object
var stmt = conn.createStatement();
stmt.executeQuery(insertProductSQL)
stmt = conn.createStatement();
stmt.executeQuery(insertProductQuantity);
conn.close();
}
So problem I'm facing is that I don't know if SQL or JDBC gives a simple way to retrieve the auto incremented value id created on the first insert to use it for my second insert.
Help is greatly appreciated, don't hesitate telling me if unclear to you.
