Page 1 of 2 12 LastLast
Results 1 to 20 of 38
  1. #1
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Post Jdbc Prepared Statement execute()

    Hi, I have written the following code:

    Java 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
    Last edited by Fubarable; 11-18-2010 at 04:03 AM. Reason: Moderator Edit: Code tags added

  2. #2
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default

    Quote Originally Posted by nitishjtm View Post
    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?:

    Java Code:
    catch(Exception e){} // ????

  3. #3
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Post

    Quote Originally Posted by Fubarable View Post
    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
    Java Code:
    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.

  4. #4
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Post

    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.

  5. #5
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default

    Quote Originally Posted by nitishjtm View Post
    Even if remove
    Java Code:
    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?

  6. #6
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default

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

  7. #7
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Default

    Quote Originally Posted by Fubarable View Post
    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.

  8. #8
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Default

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

  9. #9
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by nitishjtm View Post
    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
    Java Code:
    String query = " ";
    PreparedStatement ps = con.prepareStatement(query);
    You need an actual query in that spot.

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    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.

  11. #11
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    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?

  12. #12
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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)

  13. #13
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

  14. #14
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

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

  15. #15
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Default

    Quote Originally Posted by Fubarable View Post
    I'm suspecting that we're dealing with a troll here.
    Sir, I didn't understood what you said

  16. #16
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Post

    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:

    Java 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
    Last edited by nitishjtm; 12-09-2010 at 06:26 PM.

  17. #17
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    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?

  18. #18
    nitishjtm is offline Member
    Join Date
    Sep 2010
    Posts
    30
    Rep Power
    0

    Post

    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.
    Last edited by nitishjtm; 12-09-2010 at 06:25 PM.

  19. #19
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    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.

  20. #20
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    837
    Rep Power
    6

    Default

    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.
    Ramya:cool:

Page 1 of 2 12 LastLast

Similar Threads

  1. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  2. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 05:29 PM
  3. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 11:21 PM
  4. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •