Problems with stored Procedure.. 1

[niceguyleo]

Hi

i have developed an application using servlets and have SQLServer 2000 as my database, and the Web server i am using is Tomcat 4.0.4.
The problem i ma having is that i have used some Stored procedures and in one such procedure which is having nvarchar datatype as the parameter and when i am calling this stored proceudure in my Servlet code using the Callable Statement wherein i am passing a String type variable for the Stored Procedure which i feel is compatible with the nvarchar data type of the SQL. But when i am calling the stored procedure with the String tpe variable as parameter the Tomcat shows an error as

^error : java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

so it is essentially showing an error converting the String type to nvarchar. and i really donn't know any other datatype in Java that is comatible with nvarchar. and so how do i execute my stored procedure thru my servlets.?? those who don't know about Java Servlets just can help me out regarding nvarchar datatype and is it compatible with the String type (some strange prob that i m havin is that nvarchar works fine but nvarchar raises an error.Strange.....!!)

I am sendin the procedure as well as the servlet code that i m usind to call it..


PROCEDURE STARTS HERE........


CREATE PROCEDURE getMachineDetails @relocationNo varchar(30) AS SELECT * FROM ITEM_ISSUED_DETAILS where s_no=(select s_no from ITEM_ISSUED_DETAILS_HISTORY
WHERE (relocation_no = @relocationNo) AND (date_of_relocation = (SELECT MAX(date_of_relocation) FROM ITEM_ISSUED_DETAILS_HISTORY
WHERE (relocation_no = @relocationNo))))

ENDS HERE...................



SERVLET CODE STARTS HERE......


String machineID="somevalue";

CallableStatement cstmt=con.prepareCall("{call getMachineDetails(?)}"

cstmt.setString(1,machineID);// Error comes here

rs=cstmt.executeQuery();


ENDS HERE...................



If somebody can help me out in this regard, i'll really be so much greatful.

Thanks
Shashank

 

[rt63]

Can you do away with the dynamic parameter in the preparecall instead use the entire string (since you know the machineid beforehand)

sql_statement = "getMachineDetails '"+machineID+"'"

and execute this directly?

Not familiar with JavaScript but is such a code is possible?
Sometimes placeholders are like variant datatypes and may create problems in implicit conversions..just a vague guess

RT

 

[niceguyleo]

Hey RT

thankas 4 ur tip.but i m sorry to say i tried it in my code but it din't help. can u suggest me any other idea 2 do that???
and is nvarchar and nvarchar any diferent ..i mean in their wht we say data types... coz as i said nvarchar does not raise any error but it doesn't return any results either..
do try n let me know rt..

thanks once again..
Shashank

 

[rt63]

Is your procedure working OK from the query analyzer with the same parameter as u r passing from the javascript?

What is the datatype of relocation_no field in ITEM_ISSUED_DETAILS_HISTORY

Just in case there is a problem there...

RT

 

[niceguyleo]

hey RT

thanks 4 helpin me here Rt.i m really really glad..actually the problem wasn't there in the datatype but the sequence in which i was acessing the fields from the result set..i.e. returned by select *...i took it as the datatype problem as in the query analyser when i specified nvarchar the query ran but din't give me the result but i was able to access it in my Java Code...but not when i was specifyin the limit in the nvarchar like nvarcar(30).....

so i m really sorry 4 my mistake..but thanks anyways 4 ur effort dear..and i hope that u'l keep helpin all of us here with ur gr8 tips.


Regards..
Shashank Arora