File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Stored procedure in JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Stored procedure in JSP" Watch "SQL Stored procedure in JSP" New topic

SQL Stored procedure in JSP

Bob Backlund
Ranch Hand

Joined: Jun 05, 2003
Posts: 51
Hi I've just started looking at Stored Procedures in MS-SQL but am having trouble finding out how to call one in my JSPs.
I was wondering if anyone would have an example or tips on doing this.

Faisal Khan
Ranch Hand

Joined: Jun 29, 2003
Posts: 285
There are a number of ways you can call a stored procedure from a JSP, I suggest you look at java.sql package:
Statement, PreparedStatement and CallableStatment interfaces to see what best fits your needs.

The secret to creativity is knowing how to hide your sources.
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63867

Calling any Java from a JSP scriptlet is no different than calling it from anyplace else. Since this is much more aout JDBC than JSP, I'm moving it along to the JDBC forum.

[Asking smart questions] [About Bear] [Books by Bear]
Bob Backlund
Ranch Hand

Joined: Jun 05, 2003
Posts: 51
Still trying to call the stored procedure, got this bit of code:
CallableStatement sp = conn1.prepareCall("{ CALL spdefaultcosting(?,?)}");
sp.setString(1, strJobNumber);
sp.setString(2, strCodeID);
catch (SQLException SQLex)
System.out.println("Error: " + SQLex);
But keep getting the following error:
Error: java.sql.SQLException: Could not find stored procedure 'CALL'.
(Starting to get a little frustrating now).
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15302

Remove CALL and try it. I believe a CallableStatement takes care of this for you.

GenRocket - Experts at Building Test Data
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Just a thought - try changing CALL to call (all lowercase)

The future is here. It's just not evenly distributed yet. - William Gibson
Sonny Gill LinkedIn
I agree. Here's the link:
subject: SQL Stored procedure in JSP
jQuery in Action, 3rd edition