title: How to skip the first n rows in a MySQL database with a Java program?
body: <p>I have a problem. I am writing a java application which shall move some files. Because of performance issues the user can select the amount of files that shall be moved. If the file is moved, my database shall be updated with the new paths. I've already coded that but my problem now is that it just works for the first, for example, 10 files. Then the application terminates.</p>
  <p>So until now everything works as expected. Now I want to start the application again and it shall move 10 files again. Now the problem is that it starts again at the top of the database and tries to move the first 10 files, which have been already moved in the first run of my application. So how can I tell my program that it shall skip the first 10(n) files?</p>
  <p>I had the idea of generating a new column to the database with ones and zeros. And every file that has been moved gets a 1 and its set by default to zero. Then the program checks if there is a one or a zero and if there is a one then it just continues with the next row. But I didn't find a way on how to create a column in a database with java. The next problem is that this column shall be deleted after the run of the application.</p>
  <p>I tried to write a procedure and I found this:</p>
  <pre><code>DELIMITER $$
  CREATE PROCEDURE Alter_Table()
  BEGIN
    DECLARE _count INT;
      SET _count =( SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'test_cm_documents' AND
                      COLUMN_NAME='subscribe_all');
    IF _count = 0 THEN
      ALTER TABLE test_cm_documents
      ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
          ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
    END IF;
  END$$
  DELIMITER ;
  </code></pre>
  <p>It worked if I pasted it into the MySQL command line but it did not worked when I tried to execute it with a Java program. I also tried to save that as a *.sql file but I can't execute it from my Java program.</p>
  <p>I have two Java Programs with which I tried to solve my Problems but I got several errors.</p>
  <pre><code>import java.io.*;
  
  public class CmdExec {
  
  public static void main(String[] args) {
      try {
          String line;
          Process p = Runtime.getRuntime().exec(
                  &quot;mysql -u myuser -p xxxx -d cm_database -h 3306 -f C://Users//****//Desktop//procedure.sql&quot;);
          BufferedReader input =
                  new BufferedReader
                      (new InputStreamReader(p.getInputStream()));
          while ((line = input.readLine()) != null) {
              System.out.println(line);
          }
          input.close();
      }catch(Exception err) {
          err.printStackTrace();
      }
  }
  
  }
  </code></pre>
  <p>ERRORS:</p>
  <pre><code>java.io.IOException: Cannot run program &quot;mysql&quot;: CreateProcess error=2, 
  Das 
    System kann die angegebene Datei nicht finden
      at java.lang.ProcessBuilder.start(Unknown Source)
      at java.lang.Runtime.exec(Unknown Source)
      at java.lang.Runtime.exec(Unknown Source)
        at CmdExec.main(CmdExec.java:8)
    Caused by: java.io.IOException: CreateProcess error=2, Das System kann 
   die angegebene Datei nicht finden
  at java.lang.ProcessImpl.create(Native Method)
  at java.lang.ProcessImpl.&lt;init&gt;(Unknown Source)
  at java.lang.ProcessImpl.start(Unknown Source)
  ... 5 more
  </code></pre>
  <p>Second Java program:</p>
  <pre><code>import java.sql.*;
  
  public class startProcedure {
  
  public static void main(String[] args) {
      try(Connection conn = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/cm_database?allowPublicKeyRetrieval=true&amp;SSL=false&amp;serverTimezone=UTC&quot;,
              &quot;myuser&quot;,&quot;xxxx&quot;);
              
              Statement delimiter = conn.createStatement();){
          
              String delimiterStr = 
                      &quot;CREATE PROCEDURE Alter_Table() &quot;
                      + &quot; BEGIN &quot;
                      + &quot; DECLARE _count INT; &quot;
                      + &quot; SET _count =( SELECT COUNT(*) &quot;
                                     + &quot; FROM INFORMATION_SCHEMA.COLUMNS &quot;
                                     + &quot; WHERE TABLE_NAME='test_cm_documents' AND &quot;
                                            + &quot; COLUMN_NAME='subscribe_all'); &quot;
                      + &quot; IF _count = 0 THEN &quot;
                          + &quot; ALTER TABLE test_cm_documents&quot;
                             + &quot; ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,&quot;
                             + &quot; ADD COLUMN subscribe_category varchar(512) DEFAULT NULL &quot;
                             + &quot; END IF; &quot;
                      + &quot; END; &quot;
                      ;
              
              delimiter.executeUpdate(delimiterStr);
      }catch(SQLException ex) {
          ex.printStackTrace();
      }
      
      
  }
   
      }
     
  </code></pre>
  <p>I get following error: You have an error in your SQL Statement.</p>
  <p>Maybe someone can help me, because I am quite new to the topic. Excellent will be an application which will just skip n rows. I thinks this will be the most easy solution.</p>