Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Error in calling stored procedure

 
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


 
Ranch Hand
Posts: 874
Android VI Editor Java
  • 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 VI Editor Java
  • 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.
 
Let's get him boys! We'll make him read this tiny ad!
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic