[VIPERHlr]

I have a program with a button and when the user presses this button I want it to execute multiple sql statements. This is what I tried. Its not working and gives an ODBC Driver Error

Code:

public void connect()
{
     try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection("jdbc:odbc:RclElectionsDB","","");
            String sql1 = "SELECT SUM ([Total number of votes]) AS SumVotes FROM Votes;";
            String sql2 = "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MAX([Total number of votes]) FROM Votes);";
            String sql3 = "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MIN([Total number of votes]) FROM Votes)";
            String sql4 = "SELECT COUNT * AS Number FROM Registration;";

            Statement stmt = conn.createStatement();
            
            ResultSet rs1 = stmt.executeQuery(sql1);
            ResultSet rs2 = stmt.executeQuery(sql2);
            ResultSet rs3= stmt.executeQuery(sql3);
            ResultSet rs4 = stmt.executeQuery(sql4);
            
            
            while(rs1.next())
            {
                this.jTextField1.setText(rs1.getString("SumVotes"));
            }
            
            while(rs2.next())
            {
                this.jTextField2.setText(rs2.getString("RclNames"));
            }
            while(rs3.next())
            {
                this.jTextField3.setText(rs3.getString("RclNames"));
            }
            
            while(rs4.next())
            {
                this.jTextField4.setText(rs4.getString("RclNames"));
            }
            

            conn.close();

            
        } catch(Exception e) {
           JOptionPane.showMessageDialog(null, e.getMessage());
        }
}

[baavgai]

Yes, but what error? What line?

This is invalid syntax: "SELECT COUNT * AS Number FROM Registration;"

Try: "SELECT COUNT(*) AS Number FROM Registration;"

The while makes no sense, use if. Write another method to handle all this repetition.

[VIPERHlr]

baavgai, on 03 April 2013 - 04:04 AM, said:

Yes, but what error? What line?

This is invalid syntax: "SELECT COUNT * AS Number FROM Registration;"

Try: "SELECT COUNT(*) AS Number FROM Registration;"

The while makes no sense, use if. Write another method to handle all this repetition.

Their is no error in the code. I get an ODBC Error "The SELECT statements includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect", but I dont see anything wrong with my SQL statements. I tried your suggestions but get the same error

This post has been edited by VIPERHlr: 03 April 2013 - 05:39 AM

[baavgai]

Do you still get this error after you fix the SELECT COUNT syntax? Can you figure out which one of those statements is giving you the error?

[VIPERHlr]

baavgai, on 03 April 2013 - 11:41 AM, said:

Do you still get this error after you fix the SELECT COUNT syntax? Can you figure out which one of those statements is giving you the error?

I found that the problem was in the SELECT COUNT statement and fixed it, number was a reserved word. I now have a new problem, it gives me this error "ResultSet is closed".
Heres my code again with the if statements:

public void connect()
{
     try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection("jdbc:odbc:RclElectionsDB","","");
            String sql1 = "SELECT SUM ([Total number of votes]) AS SumVotes FROM Votes;";
            String sql2 = "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MAX([Total number of votes]) FROM Votes);";
            String sql3 = "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MIN([Total number of votes]) FROM Votes)";
            String sql4 = "SELECT COUNT (*) AS N FROM Registration;";

            Statement stmt = conn.createStatement();
            
            ResultSet rs1 = stmt.executeQuery(sql1);
            ResultSet rs2 = stmt.executeQuery(sql2);
            ResultSet rs3= stmt.executeQuery(sql3);
            ResultSet rs4 = stmt.executeQuery(sql4);
            
            
            if(rs1.next())
            {
                this.jTextField1.setText(rs1.getString("SumVotes"));
            }
            
           else if(rs2.next())
            {
                this.jTextField2.setText(rs2.getString("RclNames"));
            }
           else if(rs3.next())
            {
                this.jTextField3.setText(rs3.getString("RclNames"));
            }
            
           else if(rs4.next())
            {
                this.jTextField4.setText(rs4.getString("N"));
            }
            

            conn.close();

            
        } catch(Exception e) {
           JOptionPane.showMessageDialog(null, e.getMessage());
        }
}

[baavgai]

And that error happens... where? You're not really supposed to use the statement object for more than one query...

Write a method that takes the connection and sql and returns a string. Then do all your work in one place. Also, have a method for that connection.

e.g.

// this should be in your constructor
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

private Connection getConnection() { return DriverManager.getConnection("jdbc:odbc:RclElectionsDB","",""); }

private getStringForSQL(Connection conn, String sql) { /* your code here */ }

public void connect() {
	Connection conn = null;
	try {
		conn = getConnection();
		this.jTextField1.setText(getStringForSQL(conn, "SELECT SUM ([Total number of votes]) AS SumVotes FROM Votes;"));
		this.jTextField2.setText(getStringForSQL(conn, "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MAX([Total number of votes]) FROM Votes);"));
		this.jTextField3.setText(getStringForSQL(conn, "SELECT RclNames FROM Votes WHERE [Total number of votes]=(SELECT MIN([Total number of votes]) FROM Votes)"));
		this.jTextField4.setText(getStringForSQL(conn, "SELECT COUNT (*) AS N FROM Registration;"));
	} catch(Exception e) {
		JOptionPane.showMessageDialog(null, e.getMessage());
	} finally {
        if (conn != null) { conn.close(); }
	}
}

More info on using jdbc: http://docs.oracle.c...statements.html

[VIPERHlr]

Whats the method getStringForSQl supposed to do?, What is its purpose?

This post has been edited by VIPERHlr: 04 April 2013 - 06:15 AM

[baavgai]

Um, get String For SQL? i.e. given a SQL statement and the method will return a string value. Sorry, I thought the name was clear enough.

[pbl]

            if(rs1.next())
            {
                this.jTextField1.setText(rs1.getString("SumVotes"));
            }
            
           else if(rs2.next())
            {
                this.jTextField2.setText(rs2.getString("RclNames"));
            }
           else if(rs3.next())
            {
                this.jTextField3.setText(rs3.getString("RclNames"));
            }
            
           else if(rs4.next())
            {
                this.jTextField4.setText(rs4.getString("N"));
            }

So if rs1 is not empty I fill jTextField1 and do not toch the other JTextField ?
And why calling second query if the first ine is executed and so you don't need it ?
And better to close() all there ResultSet by the way... if you use them or not