Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error in calling stored procedure

 
dipti khullar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I want to use the resultset values retrieved from the SP. I am not very much clear about the syntax and usage of registerOutParameter. But, I tried following thing after referring to some posts. Following exception is thrown: java.sql.SQLException: Invalid parameter index 3

Code Snippet:

CallableStatement proc = null;
Statement stmt_MSSql = con_MSSql.createStatement();
ResultSet rs_MSSql = stmt_MSSql.executeQuery("SELECT top 5 pageID FROM A_Pages WHERE fkPageTypeID = 25");
ResultSet rs_proc_MSSql = null;
while(rs_MSSql.next()){
proc = con_MSSql.prepareCall("{ call AAA_PageLanguages(?,?,?) }");


//proc.registerOutParameter(2, java.sql.Types.VARCHAR);
proc.setInt(1, rs_MSSql.getInt(1));
proc.registerOutParameter(2, java.sql.Types.INTEGER);
proc.registerOutParameter(3, java.sql.Types.VARCHAR);
rs_proc_MSSql = proc.executeQuery();

//rs_proc_MSSql = (ResultSet)proc.getObject(1);
while (rs_proc_MSSql.next()) {
System.out.println(rs_proc_MSSql.getInt(1) + "\t" +
rs_proc_MSSql.getString(2));
}

}

SP (Input : pageID and Output : pkID, LanguageID)

SELECT
pkID AS ID , LanguageID
FROM
tblPageLanguage AS PL INNER JOIN tblLanguageBranch AS LB ON
(PL.fkLanguageBranchID = LB.pkID) WHERE PL.fkPageID = @pageID


 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dipti,

I did not see anything wrong in the code. Please use code tags for better readbility. Also please paste the code that was not working, sometimes it might be disguising ( might have commented one line , but not shown here )
 
dipti khullar
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Balu Sadhasivam

Thanks a lot for your reply.

Please see if things are more visible now.




Exception: java.sql.SQLException: Invalid parameter index 3.
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:255)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.registerOutParameter(JtdsCallableStatement.java:190)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.registerOutParameter(JtdsCallableStatement.java:178)
at routines.GetArticleContent_sp.getArticleContent_sp(GetArticleContent_sp.java:43)
at redbullmigration_20090407.trial_0_1.trial.tJava_1Process(trial.java:165)
at redbullmigration_20090407.trial_0_1.trial.runJobInTOS(trial.java:307)
at redbullmigration_20090407.trial_0_1.trial.main(trial.java:220)
 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


I have not used "net.sourceforge.jtds.jdbc.Driver" , and may be you can try to check the documentation if your driver jar ( the version you are using) supports CallabaleStatement fully.

Also , rather than executing statement inside statement , write a simple prgm to see if callable statement is working fine. because the statement syntax seems to be correct.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic