wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problem retrieving output from stored procedured Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem retrieving output from stored procedured" Watch "Problem retrieving output from stored procedured" New topic

Problem retrieving output from stored procedured

Nilesh Soni

Joined: May 14, 2001
Posts: 21
Hi all,
I seek a very urgent and important help from you all. I am working with jsp and SQL Server 7. My requirement is something like this.
I have 2 tables. One is the master table (suppose EmpMaster) in which all employees information is stored like name, address etc. in the second table (suppose EmpTran) i am having 2 fields viz. Id and UserIds. In EmpTran, I am storing data like this.
ID UserIds
1 3,4
2 1,5
The numbers seperated by comma in UserIds field is Ids of employees whose info. is stored in EmpMaster. Now i want to
1) write a stored procedure which can retrieve data/rows from EmpMaster based on Id of EmpTran passed to the procedure.
2) how to call that procedure from java and
3) how to retrieve values returned by procedure.
So the process would be something like this as i have tried myself to solve this problem.
i will call the procedure by passing a Id to that procedure. Suppose i have passed 1. based on that Id, inside procedure i will get UserIds 3,4. i need to seperate them inside procedure itself. so i will get 3 and 4 seperately. then I will do query "select Name, Address from EmpMaster where Id=3 or Id=4" Now whatever result will be retrieved, that is to be sent back. and In Java/jsp i want to retrieve that data.
To solve my problem i have started writing procedure but stuck at the starting only as I cannot retrieve the simple string also like "3,4" from procedure.
I know it may be discouraging to u all as i am asking too much, but if you people can help me to solve, why i cannot retrieve the data from procedure only, I will be greateful to you.
Thanks in advance. Code tried by me are as follows.
SQL server 7 procedure :
@Id numeric ,
@@IdString varchar(100) output
select @@IdString=UserNames from HoliPackageTran where Id = @Id

RETURN @@IdString
Java code which i used to retrieve the data:
cs = con.prepareCall("{call GetDetails(?,?)}") ;
cs.setInt(1,1) ; //
cs.registerOutParameter(2, java.sql.Types.VARCHAR) ;
if(cs.execute()) { //if execute() returns true, it means resultset is returned
r = (ResultSet)cs.getObject(2);
while(r.next()) {
out.println(r.getString("UserIds")) ;
else {
String str = cs.getString(2) ;
out.println(str) ;
cs.close() ;
Exception raised
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '3,4' to a column of data type int.
First of all i cannot understand why it is giving me this exception as i am not trying any where to convert the string returned as '3,4' into int. If any one of you will help me out i will be greateful to you.
I agree. Here's the link: http://aspose.com/file-tools
subject: Problem retrieving output from stored procedured