8 Replies - 2797 Views - Last Post: 04 April 2013 - 11:05 AM Rate Topic: -----

#1 VIPERHlr  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 85
  • Joined: 05-August 11

Execute multiple sql statements at once

Posted 02 April 2013 - 10:45 PM

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());
        }
}


Is This A Good Question/Topic? 0
  • +

Replies To: Execute multiple sql statements at once

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Execute multiple sql statements at once

Posted 03 April 2013 - 04:04 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 VIPERHlr  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 85
  • Joined: 05-August 11

Re: Execute multiple sql statements at once

Posted 03 April 2013 - 05:38 AM

View Postbaavgai, 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

Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Execute multiple sql statements at once

Posted 03 April 2013 - 11:41 AM

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?
Was This Post Helpful? 0
  • +
  • -

#5 VIPERHlr  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 85
  • Joined: 05-August 11

Re: Execute multiple sql statements at once

Posted 03 April 2013 - 11:14 PM

View Postbaavgai, 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());
        }
}

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Execute multiple sql statements at once

Posted 04 April 2013 - 03:56 AM

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
Was This Post Helpful? 0
  • +
  • -

#7 VIPERHlr  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 85
  • Joined: 05-August 11

Re: Execute multiple sql statements at once

Posted 04 April 2013 - 06:15 AM

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

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

Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Execute multiple sql statements at once

Posted 04 April 2013 - 10:03 AM

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.
Was This Post Helpful? 0
  • +
  • -

#9 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8343
  • View blog
  • Posts: 31,890
  • Joined: 06-March 08

Re: Execute multiple sql statements at once

Posted 04 April 2013 - 11:05 AM

            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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1