Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
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: 63348

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] [Bear's FrontMan] [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
It's not a secret anymore!