AppletTalk.com

sent1729@yahoo.com · Guest

Hi all,

In my java application I am trying to get the ouput parameter form a
stored procedure (iam using sql server 2005 as my database) and this is
what I did:

CallableStatement s = c.prepareCall("{call
spGetGrid('PRJ-001-A',null)}");
s.registerOutParameter(1, java.sql.Types.CLOB);
ResultSet r = s.executeQuery();
String charData = s.getString(1);

The output parameter is of xml data type.

My problem is that Iam getting the eror message at the line
s.registerOutParameter(1, java.sql.Types.CLOB);

Error Message : com.microsoft.sqlserver.jdbc.SQLServerException:
Invalid parameter Index: 1 valid range is 0 to 1.

I tried various numbers for the index parameter(like
s.registerOutParameter(2,3..., java.sql.Types.CLOB), and also tried all
the JDBC types line java.sql.Types.BLOB, java.sql.Types.VARCHAR etc..).
I also made the stored procedure to return a string and tried
s.registerOutParameter(1, java.sql.Types.VARCHAR);

Can any one help me with what is going wrong.

Am I missing something.

Nathan

Juha Laiho · Guest

[email]sent1729 (AT) yahoo (DOT) com[/email] said:

sent1729@yahoo.com · Guest

Yes, I did try ,

CallableStatement s = c.prepareCall("{call spGetGrid(?,?)}");
s.setString(1,"PRJ-001-A");
s.setClob(2, null);
s.registerOutParameter(2, java.sql.Types.CLOB);
ResultSet r = s.executeQuery();

but now im getting the error message:
Implicit conversion from data type xml to nvarchar(max) is not allowed.
use convert function to run this query.

I am not sure what has to be done to convert this output type (xml) to
clob.

Any help would be greatly appreciated.
Nathan