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.
------------------