Page 2 of 2 FirstFirst 12
Results 21 to 38 of 38
  1. #21
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    I already have.

    The OP is taking queries entered into the cmd line and wants to determine whether they are simple queries/inserts or whether they are an insert that requires further prompting for parameters. Of course this requires that the person entering the SQL knows what they have to enter for it to be valid, but that's a higher level issue than the immediate question of how to identify the two types of query.

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

    Post

    Quote Originally Posted by Tolls View Post
    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.
    Yes sir, this is not a business app. I am learning jdbc.

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

    Post

    Quote Originally Posted by Tolls View Post
    Don't execute the prepared statement until after the if/else.
    I didn't got clearly what you said. Please explain me.
    I tried with indexOf(), and it is recognizing ?.

    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");
    		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
    If I comment the portion of the code written in else and give query such as

    insert into student_details values(1,'Nitish',5000)
    then its working. In case of
    insert into student_details values(?,?,?)
    rather than prompting me for entering the input, it is still waiting for input, which I don't know.
    It is asking me to enter the details when I give query after removing the comment as:
    insert into student_details values(1,'Nitish',5000)

  4. #24
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Quote Originally Posted by nitishjtm View Post
    I didn't got clearly what you said. Please explain me.
    I tried with indexOf(), and it is recognizing ?.
    Well, the code you just posted doesn't show the indexOf part at all, so I'm guessing this isn't your latest version?

    Anyway, this is the problem bit:

    Java Code:
    			       query = br.readLine();
    		           PreparedStatement ps = con.prepareStatement(query);
    	               Boolean b = ps.execute();  [B]<-- why execute here?[/B]
    			       if(b){
    ... do stuff with resultset
    				}//while
    			}//if
    			else{
    ... set all the values, [B]except you never execute this query[/B]
    			}//else
    		}//while
    	}//main
    }//class
    So what I'm saying is, prepare the query, then do the indexOf check. If there is a '?' then do the stuff you currently have in else (to set the values for the insert). Then you execute the query.

    Quote Originally Posted by nitishjtm View Post
    If I comment the portion of the code written in else and give query such as

    insert into student_details values(1,'Nitish',5000)
    then its working. In case of
    insert into student_details values(?,?,?)
    rather than prompting me for entering the input, it is still waiting for input, which I don't know.
    It is asking me to enter the details when I give query after removing the comment as:
    insert into student_details values(1,'Nitish',5000)
    I don't quite understand this bit. Of course when you comment out your prompt (the println()) you won't see it, so the system will then wait for your input.

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

    Post

    Sir, how can I invoke indexOf() on ps.
    Well, the code you just posted doesn't show the indexOf part at all, so I'm guessing this isn't your latest version?
    Earlier I did stupid job, I simply invoked indexOf() on query.

  6. #26
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    indexOf() is a method on String. You use it on the SQL string you read in from the command line. You would know this if you'd clicked on the link I provided for it above.

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

    Default

    That I had already did:
    System.out.println(query.indexOf("?"));
    And it is returning the required index.
    Here's the updated 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");
    		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
    		String query=" ";
    		while(!query.equals("exit")){
    			try{
    			       query = br.readLine();
    				   PreparedStatement ps = con.prepareStatement(query);
    				   System.out.println(query.indexOf("?"));
    				   //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);
                         ps.execute();
    				     System.out.println("Rows Affected::"+ps.getUpdateCount());
    			       //}//else
    			}//try
    			catch(Exception e){}
    		}//while
    	}//main
    }//class
    I did it and its working but what about if{} part? I mean to say as excute() returns true for select query and false for update query and I want both of these operations to be performed.

  8. #28
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Can you please try and format that code properly.
    Don;t use tabs...use spaces (4 is good for each indentation).

    Your if statement should be
    Java Code:
    if (query.indexOf('?') > -1) {
        // Do whatever is required for setting values in the prepared statement.

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

    Post

    Quote Originally Posted by Tolls View Post
    Can you please try and format that code properly.
    Don;t use tabs...use spaces (4 is good for each indentation).
    Sir, it is because I use EditPlus for typing the code.

    Your if statement should be
    Java Code:
    if (query.indexOf('?') > -1) {
        // Do whatever is required for setting values in the prepared statement.
    I did that what its returning me is this:
    Java Code:
    D:\AdvJava\PreparedStatement>javac -d . Jdbc13.java
    
    D:\AdvJava\PreparedStatement>java com.nitish.jdbc.preparedstatement.Jdbc13
    select * from Student_details
    Enter Student ID ::

  10. #30
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Show your code, because I doubt you're doing what I have just suggested.

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

    Default

    Here's the 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");
    		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
    		String query=" ";
    		while(!query.equals("exit")){
    			try{
    			       query = br.readLine();
    				   PreparedStatement ps = con.prepareStatement(query);
    		      if(query.indexOf('?')>-1){
    				    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);
    					 System.out.println("Rows Affected::"+ps.getUpdateCount());
    			       }//else
    				   ps.execute();
    			}//try
    			catch(Exception e){}
    		}//while
    	}//main
    }//class

  12. #32
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    OK.
    If the query string has a '?' in it what do you want to do?

    Please try and think through your logic because I am not writing this for you.
    I have explained the order of things to you several times now.

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

    Default

    I got the clarity. indexOf() will -1 if it does not find '?'.
    I had corrected it.
    The code is as follows:
    Java Code:
     if(query.indexOf('?')>-1){
    				     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);
    					 System.out.println("Rows Affected::"+ps.getUpdateCount());    
    			}//if
    			else{
    					 ResultSet rs = ps.getResultSet();
    				    while(rs.next()){
    					     System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
    				     }//while
    			       }//else
    				   ps.execute();
    But I am getting update count 0. While in database the record has been inserted.

    Doesn't it working against the behaviour of execute()? I mean to say, it returns true when we query the database, so in this it will return resultset while updating it returns false so it returns update count. So, again it is not working for select query.

  14. #34
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    OK.
    You are getting the updateCount before actually executing the statement.

    Similarly in the else, you are getting the result set before you are exceuting the query.

    I'd pull these into their own methods:
    Java Code:
    if ('?') {
        insert(query, con);
    } else {
        select(query, con);
    }
    And create the prepared statement inside each of those methods, and execute them there. That way you can test them individually, because you are getting yourself confused by the volume of stuff going on here.

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

    Default

    I didn't said that I am getting resultset, what I got is this:
    D:\AdvJava\PreparedStatement>java com.nitish.jdbc.preparedstatement.Jdbc13
    insert into Student_details values(?,?,?)
    Enter Student ID ::
    12
    Enter Student Name ::
    most
    Enter Student Fee ::
    5000
    Rows Affected::0
    select * from Student_details
    _
    Means, I am not getting anything for select query. Anyway I will try what you suggested me.
    Last edited by nitishjtm; 12-15-2010 at 05:37 PM.

  16. #36
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    And that's happening becaue you haven't executed the query at theat point:
    Java Code:
    if(query.indexOf('?')>-1) {
        // set the prepared statement values
        System.out.println("Rows Affected::"+ps.getUpdateCount()); // You do updateCount here
    } else {
        ResultSet rs = ps.getResultSet();  // You do getResultSet() here
    }
    ps.execute();  // But you don't actually execute the statement until here!

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

    Default

    Thanks sir, I corrected it and its working properly.
    During initial discussion you told that this is not the effective way to do this. Is there any other way to perform the same functionality.

  18. #38
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Well, anything that involves getting a query direct from the user, and where that query has to be of one of two types, is seriously prone to user error. Also it's hardly secure. What's to stop the user writing "DELETE * FROM student_details"?

    If there's only one of two options then I'd keep the two SQL statements in the system and ask the user:
    What action do you want?
    1) add a new student
    2) list all students

    Or something to that effect.

Page 2 of 2 FirstFirst 12

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
  •