Page 1 of 2 12 LastLast
Results 1 to 20 of 23
  1. #1
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default How can i pass the values in string parameters to a stored procedure

    Below are my string variables where i am getting the selected values into them i need to pass those values to a stored procedure
    String users[] = request.getParameterValues("comments") ;
    String FromDate = request.getParameter("pickedDate");
    String ToDate = request.getParameter("pickedDate1");


    I am passing the parameters of my string variable to query in this way but its not working
    String query = ""dbo.users", "@fromdate", FromDate , "@Todate", ToDate , "@Users",users[]";

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,956
    Rep Power
    7

    Default Re: How can i pass the values in string parameters to a stored procedure

    This works the same as a PreparedStatement, but now it's called a CallableStatement, created with Connection.prepareCall().

    Here's a blog-entry that shows you how to do it.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    Note that, assuming the stored procedure takes DATE types (or similar) for the toDate and fromDate then you'll need to parse your date Strings into java.sql.Date or java.sql.Timestamp objects using SimpleDateFormat.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #4
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    i did not get you could you please show me how to do that i am new to this

  5. #5
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    i did not get you could you please show me how to do that i am new to this i am getting error while passing to query String query = ""dbo.users", "@fromdate", FromDate , "@Todate", ToDate , "@Users",users[]"; this is what i had done but its not working so could you please show me how to do that

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    SurfMan provided a link that shows how to do this thing.

    As I said in the other thread, we do not provide fully coded solutions.

    Can you show us your code where you are using a CallableStatement to make the call to the stored procedure?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    i am new to this this is my code that i used till now

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    try
    {
    SQLServerDataSource ds = new SQLServerDataSource();
    Connection conn=null;
    ds.setUser("");
    ds.setPassword("");
    ds.setServerName("");
    ds.setDatabaseName("");
    conn = ds.getConnection();
    conn.setAutoCommit(true);
    Statement st=conn.createStatement();
    System.out.println("Connected!");
    String users[] = request.getParameterValues("comments") ;
    String FromDate = request.getParameter("pickedDate");
    String ToDate = request.getParameter("pickedDate1");
    PrintWriter writer = response.getWriter();
    response.setContentType("text/html");
    writer.println("<h4>You are selected :</h4>");
    for(String selecteduser :users)
    {
    writer.println("<br><font color=black>"+selecteduser+"</font>");
    }
    ArrayList al = null;
    ArrayList pid_list = new ArrayList();
    String query = ""dbo.users", "@fromdate", FromDate , "@Todate", ToDate , "@Users",users[]";



    writer.close();
    }
    catch(Exception exception)
    {
    exception.printStackTrace();
    }
    }

    i am not able find the solution

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    As SurfMan said, you need to use a CallableStatement.
    You cannot call a stored procedure using a Statement.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    that only i was asking i tried but i am not getting

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    So what did you try?
    The above code is not an example of trying as it looks nothing like the code in the link SurfMan provided.
    In that link Jakob Jenkov walks you through how to set up a CallableStatement and execute the call and pick up the results.
    Nothing I would write would be an improvement on that.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  11. #11
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    Java Code:
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    try
    {
    SQLServerDataSource ds = new SQLServerDataSource();
    Connection conn=null;
    ds.setUser("");
    ds.setPassword("");
    ds.setServerName("");
    ds.setDatabaseName(""); 
    conn = ds.getConnection(); 
    conn.setAutoCommit(true);
    Statement st=conn.createStatement();
    System.out.println("Connected!");
    String users[] = request.getParameterValues("comments") ;
    String FromDate = request.getParameter("pickedDate");
    String ToDate = request.getParameter("pickedDate1");
    PrintWriter writer = response.getWriter();
    response.setContentType("text/html");
    writer.println("<h4>You are selected :</h4>");
    for(String selecteduser :users)
    {
    writer.println("<br><font color=black>"+selecteduser+"</font>");
    }
    ArrayList al = null;
    ArrayList pid_list = new ArrayList();
       CallableStatement cstmt = null;
             try {
             String SQL = "{call Users (?, ?, ?)}";
             cstmt = conn.prepareCall (SQL);
             
             }
             catch (Exception e) {
                 
             }
             finally {
                 cstmt.close();
             }
    
    
    writer.close();
    }
    catch(Exception exception)
    {
    exception.printStackTrace(); 
    }
    }
    this is what i tried i am unable to get how to pass those parameters and retrive from database
    Last edited by kavithav; 07-27-2017 at 12:35 PM.

  12. #12
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    19,942
    Rep Power
    30

    Default Re: How can i pass the values in string parameters to a stored procedure

    Please edit your post and wrap your code with code tags:

    [code]
    **YOUR CODE GOES HERE**
    [/code]

    to get highlighting and preserve formatting.
    If you don't understand my response, don't ignore it, ask a question.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    If you look at that blog entry, he has a section about "Setting Parameter values" which shows this:
    Java Code:
    callableStatement.setString(1, "param1");
    callableStatement.setInt   (2, 123);
    What about that is causing you problems?

    Further down there is a section on OUT parameters, but I don't know if you have any of those.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  14. #14
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    i had wrapped it could you please help me out my requirement is to pass the values that are in stored procedures and retrive the data from database and display that data in table

  15. #15
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    yes i am not getting i need is to pass the values that are in stored procedures and retrive the data from database and display that data in table i had passed in this way

    CallableStatement cstmt = null;
    try {
    String SQL = "{call CodingProductionLog (?, ?, ?)}";
    cstmt = conn.prepareCall (SQL);
    // cstmt.getString(users);
    cstmt.getDate(FromDate);
    cstmt.getDate(ToDate);



    }
    catch (Exception e) {
    e.printStackTrace();
    }
    finally {
    cstmt.close();
    } it is giving me below exception

    e = (com.microsoft.sqlserver.jdbc.SQLServerException) com.microsoft.sqlserver.jdbc.SQLServerException: Parameter 05/01/2017 was not defined for stored procedure users.
    Last edited by kavithav; 07-27-2017 at 12:52 PM.

  16. #16
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    What are the parameters the stored procedure is expecting?
    Can you list them?

    At the moment you are not setting any parameters.
    You seem to be trying to get at some OUT parameters, but using the wrong variable names.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  17. #17
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    Procedure Name : Users
    Parameters : @fromdate , @Todate,@Users

    this is what i did try (CallableStatement s = conn.prepareCall("{CALL Users(@fromdate=?,@Todate=?,@Users=?)}")) {
    s.getDate(FromDate);
    s.getDate(ToDate);
    s.getstring(Users)
    try (ResultSet rs = s.executeQuery()) {
    rs.next();
    System.out.println(rs.getString("response"));
    }

    but its going into catchblock and gives this exception Parameter 05/01/2017 was not defined for stored procedure users.
    Last edited by kavithav; 07-27-2017 at 01:26 PM.

  18. #18
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    Set!
    Not get!

    You are setting the parameters to pass into the stored procedure call.
    The getXX is for getting OUT parameters returned by the stored procedure.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  19. #19
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: How can i pass the values in string parameters to a stored procedure

    can you please help me out how to do that i tried even with set but i am not getting

  20. #20
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How can i pass the values in string parameters to a stored procedure

    So show the set code.
    Note that setXXX takes two parameters, the first of which is an index (starting at 1) representing which parameter you are setting.
    So the first '?' is set (assuming it's a String) using
    Java Code:
    callableStatement.setString(1, myStringVariable);
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Page 1 of 2 12 LastLast

Similar Threads

  1. PL/SQL procedure to Java stored procedure
    By Aispam in forum New To Java
    Replies: 2
    Last Post: 12-13-2013, 10:11 AM
  2. Stored procedure OUT parameter is rounded
    By diamonddragon in forum JDBC
    Replies: 0
    Last Post: 02-10-2012, 09:03 PM
  3. SQL EXception- in Stored Procedure call
    By cnu.nandhikonda@gmail.com in forum JDBC
    Replies: 3
    Last Post: 02-08-2012, 01:52 PM
  4. Replies: 0
    Last Post: 02-28-2009, 11:16 AM
  5. stored procedure
    By sankarigopi in forum JDBC
    Replies: 1
    Last Post: 11-13-2008, 04:53 PM

Tags for this Thread

Posting Permissions

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