Databases
StackOverflow2013
Postsdbo
POMultiple Delete MSSQL delete queries in Java Program
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Delete MSSQL delete queries in Java Program
    primarykey
    data
    text
    <p>I am writing a program where I am changing roles. The Change Role process involves deleting from two tables(to clear the current role/group), inserting into two tables(to set the role/group). </p> <p>I have <code>allowMultipleQueries = true</code> in my connection string, but it looks like only the first query is running. </p> <p>The database is an MSSQL db. </p> <p>Is there a way to run both queries? Can I delete from both tables?</p> <p>The code I have is below:</p> <pre><code>JButton changeRoleBtn = new JButton("Change Role"); changeRoleBtn.setBounds(50, 375, 150, 30); changeRoleBtn.setToolTipText("Changes the role of the User"); changeRoleBtn.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { if (requesterRole.isSelected()) { StringBuffer getRolesQuery3 = new StringBuffer("delete from hib.personrole where personid = '"); getRolesQuery3.append(userID).append("'"); StringBuffer getRolesQuery4 = new StringBuffer("delete from hib.persongroup where personid = '"); getRolesQuery4.append(userID).append("'"); try { ResultSet rs = stmt.executeQuery(getRolesQuery3.toString()); ResultSet rs1 = stmt.executeQuery(getRolesQuery4.toString()); boolean empty = true; if(empty) { userRoleLbl.setText("The User is a Requester"); System.out.println(rs); System.out.println(rs1); } } catch(Exception e2) { System.out.println(e2); } } } }); </code></pre> <p>I have changed it to have the prepared statement I get the following error though when I run it. <code>java.sql.SQLException: Invalid parameter index 2.</code></p> <pre><code> changeRoleBtn.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { if (requesterRole.isSelected()) { try { PreparedStatement ps1, ps2; ps1 = con.prepareStatement("delete from hib.personrole where personid = ?"); ps2 = con.prepareStatement("delete from hib.persongroup where personid = ?"); ps1.setInt(1, userID); ps2.setInt(2, userID); ps1.executeQuery(); ps2.executeQuery(); con.commit(); userRoleLbl.setText("The user is a requester"); } catch(Exception e3) { e3.printStackTrace(); } } } }); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDarthOpto
    1. USDarthOpto
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. This table or related slice is empty.
      1. VTClose
    3. VO
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. This table or related slice is empty.
      1. VTClose
    1. COSQL Injection ahead! Better use prepared statements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. USBarranka
    2. COyou mean like `delete from hib.personrole where personid in ( [pid1], [pid2] )`?
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. USLucas
    3. COJavadocs are your friend. `By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.` http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html
      singulars
      1. POMultiple Delete MSSQL delete queries in Java Program
      1. USBrian Roach
 

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