Jdbc Prepared Statement execute()

[NitishK Kumar]

Hi I have written the following code. After execution when I give inpt from command line it does not do anything. Please tell me is there anything wrong in code.

package com.nitish.jdbc.preparedstatement;
import java.sql.*;
import	java.io.*;

public class Jdbc13{
	public static void main(String []s)throws Exception{
		System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
		String query = " ";
		PreparedStatement ps = con.prepareStatement(query);
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		while(!query.equals("exit")){
			try{
			query = br.readLine();
			Boolean b = ps.execute();
			if(b){
				    ResultSet rs = ps.getResultSet();
				    while(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
				}//while
			}//if
			else{
				    System.out.println("Enter Student ID :: ");
			        int stid=Integer.parseInt(br.readLine());
		    	    System.out.println("Enter Student Name :: ");
			        String name=br.readLine();
				    System.out.println("Enter Student Fee :: ");
				    double stfee = Double.parseDouble(br.readLine());
                    ps.setInt(1,stid);
			        ps.setString(2,name);
			    	ps.setDouble(3,stfee);
				    int count = ps.getUpdateCount();
				    System.out.println("Rows Affected::"+count);
			}//else
			}//try
			catch(Exception e){e.printStackTrace();}//catch
		}//while
	}//main
}//class

[Paul Clapham]

As I understand it, the first thing it would do would be to wait for you to input data. This might appear to be "doing nothing" to somebody who hadn't been told they had to provide input.

Or perhaps "doing nothing" means something else?

[Mohamed Sanaulla]

Create an instance of the PreparedStatement after you have taken input for query. You have provided an empty string as the query, which the PreparedStatment is using it. You taking of input from the command line for the query isnt changing the query in the PreparedStatement.

[Bharath Raja]

please check your code.. you have added the null query to prepared statement.. let us see that...

# String query = " ";  // empty string
#         PreparedStatement ps = con.prepareStatement(query);  //prepared statement with empty query string
#         BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));  
#         while(!query.equals("exit")){  
#             try{  
#             query = br.readLine();  
#             Boolean b = ps.execute();  

initially your query string is empty .. so that it would do nothing for you.. you should put your query string(after reading from user) to prepared statement..

[NitishK Kumar]

Bharath Raja wrote:please check your code.. you have added the null query to prepared statement.. let us see that...

# String query = " ";  // empty string
#         PreparedStatement ps = con.prepareStatement(query);  //prepared statement with empty query string
#         BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));  
#         while(!query.equals("exit")){  
#             try{  
#             query = br.readLine();  
#             Boolean b = ps.execute();  

initially your query string is empty .. so that it would do nothing for you.. you should put your query string(after reading from user) to prepared statement..

Bharath, execute() is for executing any type of sql statements whether it select or non-select. At runtime, if you don't know what type of query is the client going to give, then which behavior will you use. Moreover, if the client gives exit instead of giving any query then what happens. That's why, I have used empty string.

[NitishK Kumar]

mohamed sanaullah wrote:Create an instance of the PreparedStatement after you have taken input for query. You have provided an empty string as the query, which the PreparedStatment is using it. You taking of input from the command line for the query isnt changing the query in the PreparedStatement.

Sir, what should I do in this case, because my requirement is to give queries at runtime whether it may be select, insert, update, etc.

[NitishK Kumar]

Paul Clapham wrote:As I understand it, the first thing it would do would be to wait for you to input data. This might appear to be "doing nothing" to somebody who hadn't been told they had to provide input.

Or perhaps "doing nothing" means something else?

Yes, you are right, it is waiting for me to give an input. After giving input for e.g.:"select * from emp" and pressing enter it does nothing. That's what I mean by "doing nothing". Only one thing it does is exit.
Please suggest me what to do.

[Bharath Raja]

execute() is for executing any type of sql statements whether it select or non-select. At runtime, if you don't know what type of query is the client going to give, then which behavior will you use. Moreover, if the client gives exit instead of giving any query then what happens. That's why, I have used empty string.

what "mohamed sanaullah" said is exactly right.. so how you expected empty prepared statement give you the result.. you should try the following code...

  String query = " ";  // empty string  
         PreparedStatement ps = null;
         BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));    
         while(!query.equals("exit")){    
             try{    
             query = br.readLine();    
             con.prepareStatement(query);
             Boolean b = ps.execute();    

[Mohamed Sanaulla]

So the flow would be:

Ask the user for the Query Input

Take the input

Create PreparedStatement instance using the above query

[NitishK Kumar]

Bharath Raja wrote:

  
         PreparedStatement ps = null;
        

How can you write this type of code, as prepared statement object is instantiated and intialized by connection object and for that prepareStatement() method is defined in the connection interface and this method takes input as SQL statement which is nothing but string in java.

[Bharath Raja]

oops .. sorry .. there is correction in my previous post...

          String query = " ";  // empty string    
          PreparedStatement ps = null;  //created reference variable for PreperedStatement
          BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));      
          while(!query.equals("exit")){      
              try{      
              query = br.readLine();      
              [color=olive]ps = con.prepareStatement(query);[/color]  //instantiate the the PreparedStatement 
                                                                                                //with the query string that was entered by user.
              Boolean b = ps.execute();     

NitishK Kumar wrote:
How can you write this type of code, as prepared statement object is instantiated and intialized by connection object and for that prepareStatement() method is defined in the connection interface

we can... see the comments of the code

[Christophe Verré]

Bharath Raja wrote:oops .. sorry .. there is correction in my previous post...

And what happens when you enter "exit" ?

[Bharath Raja]

Christophe Verré wrote:
And what happens when you enter "exit" ?

his logic is actually wrong... he have to write the while loop after reading input from user right.

[NitishK Kumar]

mohamed sanaullah wrote:So the flow would be:

Ask the user for the Query Input

Take the input

Create PreparedStatement instance using the above query

thank you sir, one more problem I have when I am giving when I am giving non-parameterized SQL statement, it works fine but for parameterized SQL statements, its not working.

[Christophe Verré]

You need to create a new statement to update records. I'd advice you to go through a JDBC tutorial first. Here is one.

[NitishK Kumar]

mohamed sanaullah wrote:So the flow would be:

Ask the user for the Query Input

Take the input

Create PreparedStatement instance using the above query

thank you sir, one more problem I have when I am giving when I am giving non-parameterized SQL statement, it works fine but for parameterized SQL statements, its not working.

[Mohamed Sanaulla]

NitishK Kumar wrote:thank you sir, one more problem I have when I am giving when I am giving non-parameterized SQL statement, it works fine but for parameterized SQL statements, its not working.

As said by Christophe, please go through the JDBC tutorial. And it should work for parametrized/non-parametrized SQL.

[NitishK Kumar]

Bharath Raja wrote:

his logic is actually wrong... he have to write the while loop after reading input from user right.

You mean to say that I should first take query from the user and then should go for checking whether it is equal to exit or not. Am I right?

[Prince Sewani]

NitishK Kumar wrote:

Bharath Raja wrote:

his logic is actually wrong... he have to write the while loop after reading input from user right.

You mean to say that I should first take query from the user and then should go for checking whether it is equal to exit or not. Am I right?

Yes Nitish,that's the way it will work.

[NitishK Kumar]

Prince Sewani wrote:

Yes Nitish,that's the way it will work.

But, when I did so, the resultset loop started executing infinite loop.

[Christophe Verré]

You have to use another ResultSet.

[NitishK Kumar]

Christophe Verré wrote:You have to use another ResultSet.

Sir, can you please explain me?

[Prince Sewani]

NitishK Kumar wrote:

Prince Sewani wrote:

Yes Nitish,that's the way it will work.

But, when I did so, the resultset loop started executing infinite loop.

Alright i don't know what they're mentioning about two result-sets but to me it comes like this:

Yeah as mohamed sanaullah wrote:
"So the flow would be:
Ask the user for the Query Input
Take the input
Create PreparedStatement instance using the above query"

So according to me it should be more like : -

          String query = " ";  // empty string      
          PreparedStatement ps = null;  //created reference variable for PreperedStatement  
          BufferedReader br =  new BufferedReader(new InputStreamReader(System.in)); 
           
          while(!query.equals("exit"))
           { //I know this one is not gonna work in case the user types exit for the very first time,but we're gonna control it below
     
           try{
                   query = br.readLine();  
                   if(query.equals("exit")){
                      break;
                    }
                    else{
                       ps = con.prepareStatement(query);
                       Boolean b = ps.execute();
                        if(b){  
                                  ResultSet rs = ps.getResultSet();  
                                  while(rs.next()){  
                                    System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));  //or whatever
                                }//while  
                             }//if  

                    }
                      
                }
                catch(Exception e)
               {
                 e.printStackTrace();
               }

          }

Now as per the parameterized part,that's something which asks for the analysis of what exactly are you gonna provide to the user,
1. is it gonna be and live query browser? -then you don't have to worry about parameters
2. if you're gonna provide a GUI that's gonna work both the ways that is like a query browser and as well as an interactive version which asks for few values
and based on them it does the rest of the operations then it will be better to define stored procedures in database and use them from your code based on some event like a radio button and all and then using :-

        CallableStatement stmt = c.prepareCall("{call sel_pd(?,?)}");

For that you gotta go to the JDBC tutorial..



regards

Prince Sewani