• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem retrieving output from stored procedured

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 :
CREATE PROCEDURE GetDetails
@Id numeric ,
@@IdString varchar(100) output
AS
BEGIN
select @@IdString=UserNames from HoliPackageTran where Id = @Id
END

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.
------------------
 
What kind of corn soldier are you? And don't say "kernel" - that's only for this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic