Jdbc Prepared Statement execute()

[nitishjtm]

Hi, I have written the following 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,fee);
				    int count = ps.getUpdateCount();
				    System.out.println("Rows Affected::"+count);
			}//else
			}//try
			catch(Exception e){}
		}//while
	}//main
}//class

When I execute this code, it asks for query to enter, but after entering the query it doesn't do anything, nor it gives any error or exception. It is accepting only "exit". Please tell me what to do.

Moderator Edit: Code tags added

[Fubarable]

When I execute this code, it asks for query to enter, but after entering the query it doesn't do anything, nor it gives any error or exception. It is accepting only "exit". Please tell me what to do.

Sorry for the bluntness, but how the heck can you say there is no error or exception when you completely ignore any that might occur?:

catch(Exception e){} // ????

[nitishjtm]

Sorry for the bluntness, but how the heck can you say there is no error or exception when you completely ignore any that might occur?:

Even if remove

throws Exception

SQLException will be raised by prepareStatement(),rs.next(),getConnection and IOException by br.readLine().
Is it necessary to write multiple catch cunstructs when most of statements are raising same type of Exception.

[nitishjtm]

And if I don't encapsulate those codes into try block, then when enter exit on command line it will raise an SQL exception saying invalid SQL type.

[Fubarable]

Even if remove

throws Exception

SQLException will be raised by prepareStatement(),rs.next(),getConnection and IOException by br.readLine().
Is it necessary to write multiple catch cunstructs when most of statements are raising same type of Exception.

It's better for your exceptions to be specific, but more important than that, the catch blocks should almost never be empty. Again, if it is empty like you have it, how will you know if an exception does or does not occur?

[Fubarable]

Shoot man, you were instructed not to ignore exceptions in your previous thread from 10/28. This is advice you need to listen to!

[nitishjtm]

Shoot man, you were instructed not to ignore exceptions in your previous thread from 10/28. This is advice you need to listen to!

Now I am not ignoring the exceptions but still the same problem persists.
When I am giving an insert or select query it is not doing anything. Thats for what, I am trying to find the reason.

[nitishjtm]

And everytime I have to press ctrl+break to come out of it.

[masijade]

And if I don't encapsulate those codes into try block, then when enter exit on command line it will raise an SQL exception saying invalid SQL type.

Well, that's your clue. Follow it. something is wrong with your statement, seemingly.

Edit: And what is this

String query = " ";
PreparedStatement ps = con.prepareStatement(query);

You need an actual query in that spot.

[Tolls]

That code is completely messed up.
You are not (as masijade shows) creating your PreparedStatement with any SQL at all.
You do read something that you then assign to query, but that is after you have created the PreparedStatement. And then there are no parameters assigned to it, so there really is no need for it to be a PreparedStatement in the first place.

Then (in the else) you assign some data to the PreparedStatement (which at this point still has no query associated with it) and executeUpdate.

None of that will work.

[nitishjtm]

That code is completely messed up.
You are not (as masijade shows) creating your PreparedStatement with any SQL at all.
You do read something that you then assign to query, but that is after you have created the PreparedStatement. And then there are no parameters assigned to it, so there really is no need for it to be a PreparedStatement in the first place.

Then (in the else) you assign some data to the PreparedStatement (which at this point still has no query associated with it) and executeUpdate.

None of that will work.

Sir, I realized my mistake. I had corrected it, and now its working. But now I am facing problem when I am giving parameterized SQL statement. For a non parameterized statement, it's working fine. Is it possible to give a parameterized SQL statement?

[masijade]

Of course, why don't you show your attempt at it and post the entire error/compiler message(s) you are getting from it. (along with examples of your "input" if necessary)

[Fubarable]

I'm suspecting that we're dealing with a troll here.

[Tolls]

I don't know.
3 threads in 2 or 3 months isn't really troll-like.

[nitishjtm]

I'm suspecting that we're dealing with a troll here.

Sir, I didn't understood what you said

[nitishjtm]

I tried but couldn't succeed. For a parameterized SQL statement it should prompt for entering details but it is not doing so. Here's the code:

package com.stech.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");
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		String query=" ";
		while(!query.equals("exit")){
			       query = br.readLine();
		           PreparedStatement 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));
				}//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
		}//while
	}//main
}//class

[Tolls]

How are you determining whether a query requires parameters?
You're reading the query in from the command line presumably, so what is supposed to happen?
Is the user supposed to type in the SQL?

[nitishjtm]

I have used execute() of prepared statement. From command line I am passing query. It works fine for a select statement.
When I am giving a query as:

insert into student_details values(1,'Nitish',5000)

then also it works.
But I want it to work when query given should be as:

insert into student_details values(?,?,?)
On giving this query it should prompt for entering the respective details, and that its not doing.
Did I did any mistake there then please help me to find it out.

[Tolls]

Then do a indexOf() on the sql string to see if there are any '?'. If there are then do your else stuff. Don't execute the prepared statement until after the if/else.

Of course this is not exactly going to be robust, but I'm guessing this is not a business app.

[RamyaSivakanth]

You have to properly arrange the code in a readable and debuggable manner.
Try to print the query using S.O.P.
Then u send the screenshot for us so that we can give some advice to solve the problem.