4 Replies - 805 Views - Last Post: 03 August 2017 - 10:06 PM

#1 kavithav  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-April 17

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

Posted 27 July 2017 - 01:18 AM

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[]";

Is This A Good Question/Topic? 0
  • +

Replies To: How can i pass the values in string parameters to a stored procedure

#2 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3463
  • View blog
  • Posts: 15,673
  • Joined: 20-September 08

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

Posted 27 July 2017 - 01:40 AM

Quote

I am passing the parameters of my string variable to query in this way

You're not, as that would not compile. Please post the actual code or it's just wasting everyone's time
Was This Post Helpful? 0
  • +
  • -

#3 kavithav  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-April 17

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

Posted 27 July 2017 - 01:43 AM

jsp code

<table align="center">
<tr>
<td>Select An Item :</td>
<td> <select name="comments" multiple>
<option value="comment1">I am an intelligent</option>
<option value="comment2">I am a brave person</option>
<option value="comment3">I am a hard worker</option>
<option value="comment4">I am a timid person</option>
</select>
</td>
</tr>
<tr>
<td><input type="submit" value="Send"></td>
<td></td>
</tr>
<tr><td><label for="fileName">Select From Date: </label>
<input id="datepicker" name="pickedDate"/> </td><br/>
<td><label for="fileName">Select To Date: </label>
<input id="datepicker1" name="pickedDate1"/> </td><br/></tr>
</table>


servlet 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();

//stored procedure
String query = ""dbo.users", "@fromdate", FromDate , "@Todate", ToDate , "@Users",users[]";



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

this is my complete code can you please help me out as i am new to this

This post has been edited by kavithav: 27 July 2017 - 01:48 AM

Was This Post Helpful? 0
  • +
  • -

#4 gaurav_072  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 31-July 17

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

Posted 31 July 2017 - 11:35 PM

Below code can be used for large id string to be send over procedure. There's some limitation on IN clause that's I break append || after 5000 records.

int counter = 0;
String ids = "";
for (Integer id : list) {
	if (id != null) {
		if (counter % 5000 == 0) {
			ids = ids.concat("||");
		} else {
			ids = ids.concat(id).concat(",");
		}
          counter++;
	}
    
}



In procedure you need to split that string and use loop.
simple_loop: LOOP
SET a=a+1;
SET str=SPLIT_STR(jobIds,"||",a);

IF str='' THEN
LEAVE simple_loop;
END IF;

SET @query = CONCAT(
 SELECT colname1,colname2 FROM yourtableName 
 where id IN (',str,'));
PREPARE stmt FROM @query;
EXECUTE stmt;

END LOOP simple_loop;


Was This Post Helpful? 0
  • +
  • -

#5 kavithav  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-April 17

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

Posted 03 August 2017 - 10:06 PM

i already have stored procedure and paramaters i just need to pass those parametrs in order to retrive the count from database

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1