8 Replies Latest reply: Aug 23, 2012 1:34 AM by 957165 RSS

    Problem with JDBC + mysql delete query

    957165

      Good day.
      Explain what is wrong:
      Delete-query does not work in java-application, and a mysql-client - all works fine?
      Here's the code:


      private int deleteData(String form,String region,String date){  //0-ok 1-error
      int code=0;
      JdbcConnector connector = new JdbcConnector();
      Connection conn = connector.getConnection();
      PreparedStatement ps = null;
      String QUERY_DELETE=
      "DELETE from inputData " +
      "USING inputData " +
      "join regions on regions.id=inputData.region_id "+
      "join makets on makets.id=inputData.maket_id "+
      "join forms on forms.id=makets.codef_id "+
      "WHERE regions.nameregion=? and " +
      "forms.codeform=? and inputData.period=?";

      try {   
      ps = conn.prepareStatement(QUERY_DELETE);
      ps.setString(1, region);
      ps.setString(2, form);
      ps.setString(3, date);
      ps.executeUpdate();
      } catch (SQLException e1) {
      e1.printStackTrace();
      code=1;
      JOptionPane.showMessageDialog(null,e1.getMessage(),"Error!",JOptionPane.ERROR_MESSAGE);
      }
      finally {
      try {
      if (ps != null) ps.close();
      } catch (SQLException e2) {
      e2.printStackTrace();
      JOptionPane.showMessageDialog(null,e2.getMessage(),"Error!",JOptionPane.ERROR_MESSAGE);}
      }
      connector.close();
      return code;
      }//end


      A slightly different query:
      Code:

      String QUERY_DELETE="delete from inputData where inputData.id in( "+
      "select * from ( select inputData.id from inputData "+
      "join regions on regions.id=inputData.region_id "+
      "join makets on makets.id=inputData.maket_id "+
      "join forms on forms.id=makets.codef_id "+
      "where (regions.nameregion=?) and " +
      "(forms.codeform=?) and (inputData.period=?)"+
      ") as p ) ";


      -the same situation. In java NOT work ...

      Help me? please!

        • 1. Re: Problem with JDBC + mysql delete query
          EJP
          Define 'not work'. Exception? Unexpected 'code'? Correct 'code' but no deletion in database? Computer explodes?
          • 2. Re: Problem with JDBC + mysql delete query
            957165
            The definition of "not working" - in my case means:
            Java-code works without any errors and exceptions, as if everything is fine and query is executed ... but the data is not deleted.

            In Mysql-client - all perfectly works/

            Why so?
            • 3. Re: Problem with JDBC + mysql delete query
              sabre150
              Have you committed the transaction?
              • 4. Re: Problem with JDBC + mysql delete query
                957165
                autocommit = true by default.
                Tried it this way:

                private int deleteData(String form,String region,String date) {  //0-ok 1-error
                          int code=0;
                          JdbcConnector connector = new JdbcConnector();
                     Connection conn = connector.getConnection();
                     PreparedStatement ps = null;
                     String QUERY_DELETE="delete from inputData where inputData.id in( "+
                               "select * from ( select inputData.id from inputData "+
                               "join regions on regions.id=inputData.region_id "+
                               "join makets on makets.id=inputData.maket_id "+
                                    "join forms on forms.id=makets.codef_id "+                    
                                    "where (regions.nameregion=?) and " +
                                              "(forms.codeform=?) and (inputData.period=?)"+
                                    ") as p ) ";                     
                               
                     try {              
                     *     conn.setAutoCommit(false);*
                               ps = conn.prepareStatement(QUERY_DELETE);                                   
                               ps.setString(1, region);     
                               ps.setString(2, form);
                               ps.setString(3, date);
                               ps.executeUpdate();          
                               conn.commit();
                          } catch (SQLException e) {
                               JOptionPane.showMessageDialog(null,e.getMessage(),"ОШИБКА!",JOptionPane.ERROR_MESSAGE);
                               code=1;               
                               *try {*
                *                    conn.rollback();*
                *               } catch (SQLException e1) {*
                *                    JOptionPane.showMessageDialog(null,e.getMessage(),"ОШИБКА!",JOptionPane.ERROR_MESSAGE);                    *
                *               }               *
                               e.printStackTrace();                         
                          }
                          finally {
                               try {
                                    if (ps != null) ps.close();
                                         } catch (SQLException e2) {
                                              e2.printStackTrace();
                                         JOptionPane.showMessageDialog(null,e2.getMessage(),"ОШИБКА!",JOptionPane.ERROR_MESSAGE);}               
                     }                              
                          connector.close();
                          return code;
                     }//end

                All the same data is not deleted ...
                • 5. Re: Problem with JDBC + mysql delete query
                  gimbal2
                  you're sure that the code is actually called right? I don't see any form of logging in there.

                  And delete queries work just fine in Java - its still the database that actually does it, the code only sends a query to instruct the database to do it. If you don't see the results you're expecting then your expectations are wrong. Silly mistake often made: looking in the wrong database.
                  • 6. Re: Problem with JDBC + mysql delete query
                    Joe Weinstein-Oracle
                    Let me guess... Are any of the columns 'region', 'form', or 'date' defined as fixed-length fields?
                    If so, the problem may be that when the variables for comparison are parameterized, the DBMS cannot/does not implicitly adjust the values to pad out to the length of the fixed field, so the comparison fails, whereas if you 'hardwire' the values into the SQL string, the DBMS SQL parser does that.
                    If hardwiring the values into the JDBC SQL string works for you, you could consider changing the DDL to have variable-length columns...
                    Or I might be wrong, in which case good luck.
                    Joe
                    • 7. Re: Problem with JDBC + mysql delete query
                      rp0428
                      >
                      ps.setString(1, region);
                      ps.setString(2, form);
                      ps.setString(3, date);
                      >
                      Post the code that shows what values these variables are set to and the DDL for the tables involved that shows the datatypes.

                      Also test by hardcoding the values into the query and running the query manually. You can also modify the query to change 'DELETE FROM' to SELECT * FROM' to see if any rows are being returned.
                      • 8. Re: Problem with JDBC + mysql delete query
                        957165
                        CREATE TABLE `inputData` (
                        `id` int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
                        `region_id` int(10) UNSIGNED NOT NULL,
                        `maket_id` int(10) UNSIGNED NOT NULL,
                        `value1` double(12,2) NOT NULL DEFAULT '0.00',
                        `value2` double(12,2) NOT NULL DEFAULT '0.00',
                        `value3` double(12,2) NOT NULL DEFAULT '0.00',
                        `value4` double(12,2) NOT NULL DEFAULT '0.00',
                        `value5` double(12,2) NOT NULL DEFAULT '0.00',
                        `value6` double(12,2) NOT NULL DEFAULT '0.00',
                        `value7` double(12,2) NOT NULL DEFAULT '0.00',
                        `value8` double(12,2) NOT NULL DEFAULT '0.00',
                        `value9` double(12,2) NOT NULL DEFAULT '0.00',
                        `value10` double(12,2) NOT NULL DEFAULT '0.00',
                        `value11` double(12,2) NOT NULL DEFAULT '0.00',
                        `value12` double(12,2) NOT NULL DEFAULT '0.00',
                        `value13` double(12,2) NOT NULL DEFAULT '0.00',
                        `value14` double(12,2) NOT NULL DEFAULT '0.00',
                        `value15` double(12,2) NOT NULL DEFAULT '0.00',
                        `value16` double(12,2) NOT NULL DEFAULT '0.00',
                        `value17` double(12,2) NOT NULL DEFAULT '0.00',
                        `value18` double(12,2) NOT NULL DEFAULT '0.00',
                        `value19` double(12,2) NOT NULL DEFAULT '0.00',
                        `value20` double(12,2) NOT NULL DEFAULT '0.00',
                        `date` date NOT NULL,
                        `time` time NOT NULL,
                        `period` varchar(7) NOT NULL,
                        /* Keys */
                        PRIMARY KEY (`id`),
                        /* Foreign keys */
                        CONSTRAINT `fk_inputData_01`
                        FOREIGN KEY (`region_id`)
                        REFERENCES `regions`(`id`)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE,
                        CONSTRAINT `fk_inputData_02`
                        FOREIGN KEY (`maket_id`)
                        REFERENCES `makets`(`id`)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE
                        ) ENGINE =InnoDB;

                        CREATE UNIQUE INDEX `inputData_indexuniq1`
                        ON `inputData`
                        (`region_id`, `maket_id`, `period`);

                        CREATE INDEX `in_inputData_01`
                        ON `inputData`
                        (`maket_id`);

                        CREATE INDEX `in_inputData_02`
                        ON `inputData`
                        (`region_id`);

                        CREATE INDEX `in_inputData_03`
                        ON `inputData`
                        (`id`);

                        CREATE INDEX `in_inputData_04`
                        ON `inputData`
                        (`date`);