using more than one sql statements in a preparedstatement

[mahesh chinthalapudi]

import java.sql.*;

public class retrive {
public static void main(String args[])throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//System.out.println("1");//verification of driver
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl1","system","task");

String dq="select * from employee" ;
String dq1="select count(*),max(sal),min(sal),avg(sal),sum(sal) from employee";
PreparedStatement ps=con.prepareStatement(dq);
PreparedStatement ps1=con.prepareStatement(dq1);
ResultSet rs=ps.executeQuery(dq);
ResultSet rs1=ps1.executeQuery(dq1);


while(rs.next())
{
int sno=rs.getInt(1);
String name=rs.getString(2);
float salary=rs.getFloat(3);
System.out.println(sno+"\t"+name+"\t"+salary);
int i=st.executeUpdate(rs);
}rs1.next();
System.out.println("emp details from employee database");
System.out.println("-----------------------------------");
System.out.println("totol no of records="+rs1.getInt(1));
System.out.println("max sal of an employee="+rs1.getInt(2));
System.out.println("min sal of an employee="+rs1.getInt(3));
System.out.println("avg sal of an employee="+rs1.getInt(4));
System.out.println("totol sal of all employees="+rs1.getInt(5));
System.out.println("-----------------------------------");
rs.close();
rs1.close();
ps.close();
ps1.close();
con.close();
}

}

[Jan Cumps]

Yes you can. But what do you want to achieve?
I do not understand what your code is doing.

You are calling rs.executeUpdate() on a select statement.

[Vigneswaran Marimuthu]

Jan Cumps wrote:I do not understand what your code is doing.

I came to know about another Interface through which we can use precompiled SQL For your question Jan, he is just executing a SQL statement which retrieves the content of the table employee. In second statement he is using SQL functions like count(),max() etc.

Jan Cumps wrote:You are calling rs.executeUpdate() on a select statement.

He is not using rs.executeUpdate() itself Jan. Moreover resultset object cannot be used for executing SQL statements. Its used to hold the information, which is the result ofexecution of your SQL statement. You should use either Statement object or preparedStatement object to execute SQL statements.

Statement s = connection.createStatement();
ResultSet rs = s.execute or executeUpdate or executeQuery(SQL Statement)

[Jan Cumps]

Vigneswaran Marimuthu wrote: For your question Jan, he is just executing a SQL statement which retrieves the content of the table employee. In second statement he is using SQL functions like count(),max() etc.

That I knew I didn't get what the poster wanted to achieve with the code.

Vigneswaran Marimuthu wrote:He is not using rs.executeUpdate() itself Jan.

I was pointing to this line: int i=st.executeUpdate(rs);
It is an odd piece of code, because the variable st is not declared, rs does not have an operation called executeUpdate(), and the executeUpdate() I know of don't take a ResultSet as parameter.


(And java convention says that a class name should start with uppercase)

[Jan Cumps]

AND: Welcome to JavaRanch !!

[mahesh chinthalapudi]

Thank you for your suggestions.I got the output with was expected but rather than using multiple string objects for SQL Queries can i use string array?

code=java]


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;

public class perform {

public static void main(String args[])throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//System.out.println("1");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl1","system","task");

String []dq={"select * from employee","select count(*),max(salary),min(salary),avg(salary),sum(salary) from employee"} ;
ResultSet rs1=null;
for (int i=0;i<2;i++)
{ PreparedStatement ps=con.prepareStatement(dq[i]);

rs1=ps.executeQuery(dq[i]);

ps.close();
}

while(rs1.next())
{
int sno=rs1.getInt(1);
String name=rs1.getString(2);
float salary=rs1.getFloat(3);
System.out.println(sno+"\t"+name+"\t"+salary);

}
rs1.next();
System.out.println("emp details from employee database");
System.out.println("-----------------------------------");
System.out.println("totol no of records="+rs1.getInt(1));
System.out.println("max sal of an employee="+rs1.getInt(2));
System.out.println("min sal of an employee="+rs1.getInt(3));
System.out.println("avg sal of an employee="+rs1.getFloat(4));
System.out.println("totol sal of all employees="+rs1.getInt(5));
System.out.println("-----------------------------------");
rs1.close();
rs1.close();

ps1.close();
con.close();
}

}

[/code]

[Martin Vashko]

mahesh chinthalapudi wrote:

   String []dq={"select * from employee","select count(*),max(salary),min(salary),avg(salary),sum(salary) from employee"} ;
    ResultSet rs1=null;
   for (int i=0;i<2;i++)
   {   PreparedStatement ps=con.prepareStatement(dq[i]);
    
     rs1=ps.executeQuery(dq[i]); 
    
     ps.close();
   }

That's not going to work. Only the recordset open in the last pass through the loop will be available, of course, previous ones will be lost. This is no different from Java's handling of object references in general: when you assign a new instance to the rs1 variable, the previous instance is lost. In this particular case, the recordset is even not properly closed, which might in general lead to resource leaks.

It should be possible to submit more SQL queries at once to the database, separated by the databases command separator (typically a semicolon), and then sequentially access all of the generated ResultSets through the Statement.getMoreResults() method. I haven't used this method myself, so I cannot provide more details. Examples should be available on the net, though.