Error S1000 trying to execute more MySql queries in a Java Application

Question

I have a problem trying to execute more than one query into my Java Application code. I have a procedure that is called in main and is in the class "Fant":

    public void XXX(){
     Connectivity con=new Connectivity(); // this class set up the data for the connection to db; if ( !con.connect() ) {
                    System.out.println("Error during connection.");
                    System.out.println( con.getError() );
                    System.exit(0);
                }
        ArrayList<User> blabla=new ArrayList<User>();
        blabla=this.getAllUsers(con);
        for (User u:blabla)
         {
           try {
                Connectivity coni=new Connectivity();//start a new connection each time that i perform a query
                Statement t;
                t = coni.getDb().createStatement();

               String query = "Select count(*) as rowcount from berebe.baraba";
               ResultSet rs = t.executeQuery(query);
               int numPrenotazioni=rs.getInt("rowcount");
                rs.close();                           //close  resultset
                t.close();                            //close statement
                coni.getDb().close();                  //close connection
                }
          }
            catch (SQLException e)
                 {

                  System.err.println("SQLState: " +
                  ((SQLException)e).getSQLState());
                  System.err.println("Error Code: " +
                  ((SQLException)e).getErrorCode());
                }
         }
            }

The called function is defined as:

ArrayList<User> getAllUsers(Connectivity con) {
 try{
            ArrayList<User> userArrayList=new ArrayList<User>();
            String query = "Select idUser,bubu,lala,sisi,gogo,gg  from berebe.sasasa";
            Statement t;
            t = con.getDb().createStatement();
            ResultSet rs = t.executeQuery(query);

            while (rs.next())
            {
                User utente=new User(....); //user fields got from query
                userArrayList.add(utente);
            }
              rs.close();
              t.close();
              con.disconnect(); //disconnect the connection
              return userArrayList;
            } catch (SQLException e) {

        }
        return null;

    }

The main is:

 public static void main(String[] argv) {
    ArrayList<User> users=new ArrayList<User>();
    System.out.println("-------- MySQL JDBC Connection Testing ------------");
    Fant style = new Fant();
    style.XXX();

}

The query performed into "getAllusers" is executed and into the arraylist "blabla" there are several users; the problem is that the second query that needs the count is never executed. The MYSQlState given when running is= "S1000" and the SQLERROR is "0". Probably i'm mistaking on connections issues but i'm not familiar with statements,connections,resultsets. Thank you.

I'd suggest that you print the entire exception (including stacktrace), and not just the error codes as those are not always helpful (eg S1000 is the ODBC 2.x equivalent of the SQL-standard HY000 which is a (very) generic call-level interface (CLI) error. — Mark Rotteveel, May 27 '14 at 20:49

score 3 · Accepted Answer · answered May 27 '14 at 19:43

3

You might forget to call rs.next() before getting the result form it in XXX()methods as shown below:

ResultSet rs = t.executeQuery(query);

// call rs.next() first here
int numPrenotazioni=rs.getInt("rowcount");

answered May 27 '14 at 19:43

Braj

46,415
5
60
76

aren't any error on connections,statements? for example maybe i'm mistaking of opening more than one connection, i don't know.. – Rotom92 May 27 '14 at 19:52
No you have to maintain it. Have you tried it what I suggested? – Braj May 27 '14 at 19:56
I have already shared a [ConnectionUtil](http://stackoverflow.com/questions/22999830/servlet-server-side-initialization-code-in-gwt/23006184#23006184) class where you can check for all the opened connection. Get the connection for a factory class that is common for whole application -a single point to access the connection. Get the connection for the utility, increment the counter and call its close method to decrease the counter. – Braj May 27 '14 at 19:58

Error S1000 trying to execute more MySql queries in a Java Application

1 Answers1