This week's book giveaways are in the Jython/Python and Object-Oriented programming forums.
We're giving away four copies each of Machine Learning for Business: Using Amazon SageMaker and Jupyter and Object Design Style Guide and have the authors on-line!
See this thread and this one for details.
Win a copy of Machine Learning for Business: Using Amazon SageMaker and JupyterE this week in the Jython/Python forum
or Object Design Style Guide in the Object-Oriented programming forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

Problem retrieving output from stored procedured

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • 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.
------------------
 
Do you pee on your compost? Does this tiny ad?
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!