Databases
StackOverflow2013
Postsdbo
POPass in variable length lists of foreign keys as arguments to a stored procedure via JDBC and then use the lists to insert rows into a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPass in variable length lists of foreign keys as arguments to a stored procedure via JDBC and then use the lists to insert rows into a table
    primarykey
    data
    text
    <p>I need to construct a single transaction / stored procedure that takes in two lists of foreign keys and inserts new rows into a table from those foreign keys.</p> <p>Specifically, I have a table that has order_ids and item_ids corresponding to the order. I need to add to this table when orders are submitted.</p> <p>To summarize in code, I would like to do:</p> <pre><code>DELIMITER ## BEGIN CREATE STORED PROCEDURE addOrder(IN itemIds VARCHAR(255), IN orderIds VARCHAR(255)) // ----- SQL Equivalent of ----- // for (int i = 0; i &lt; itemIds.length; ++i) // INSERT INTO Items_Ordered(item_id, order_id) VALUES(itemIds[i], order_ids[i]); // ----------------------------- END ## DELIMITER ; </code></pre> <p>So in Java, I could then just do something like:</p> <pre><code>String listOfItems = '1, 2, 6, 7, 8'; String listOfOrders = '2, 1, 99, 82, 7'; PreparedStatement ps = connection.prepareCall("CALL addOrder(" + listOfItems + ", " + listOfOrders + ")"); ps.executeUpdate(); </code></pre> <p>Any ideas?</p> <p>EDIT #1:</p> <p>I really, really, really don't like this hacky solution: <a href="https://stackoverflow.com/questions/595371/pass-array-into-a-stored-procedure">Pass array into a stored procedure</a></p> <p>EDIT #2:</p> <p>Another solution I was thinking was to create a temporary table in the stored procedure with the input, but I don't know how to do that either.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USRyan
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CODo you HAVE to do it in a stored procedure? Have you considered doin the insert directly from Java ?
      singulars
      1. POPass in variable length lists of foreign keys as arguments to a stored procedure via JDBC and then use the lists to insert rows into a table
      1. USKal
    2. COI am working on an Web Service / API that does not require knowing (hardly) anything about the underlying database it uses. All it needs to know are the stored procedure names and the arguments they require. So, do I HAVE to? No. Do I WANT to? Yes, and for good reasons.
      singulars
      1. POPass in variable length lists of foreign keys as arguments to a stored procedure via JDBC and then use the lists to insert rows into a table
      1. USRyan
    3. COThe design still seems flawed to me. If you do want to implement the "stored proc" model, create a procedure that inserts a single pair and then call that in a transaction multiple times. That way you can control and manage your negative test cases much better.
      singulars
      1. POPass in variable length lists of foreign keys as arguments to a stored procedure via JDBC and then use the lists to insert rows into a table
      1. USKal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload