By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,397 Members | 2,515 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,397 IT Pros & Developers. It's quick & easy.

Executing an MS SQL stored procedure from a java servlet

P: n/a
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'

The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/default...en-us;Q238971).
Am I missing something? I posted my servlet code below.

Thanks for any help!
Dinesh

formHandlingServlet.class

-------------------------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
/**
*
* @author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";

try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a


dinesh wrote:
Hi Joseph its nice to get a reply from a BEA employee..I will check out the
bea groups. Well, yes I am able to execute the query from the MS query
analyzer. I am also able to perform a table read from a servlet, I run into
problems when trying to insert data. I tried to use the ms jdbc and
implement it as instructed by bea edocs. here is my error:
formHandlingServlet.java [79:1] cannot resolve symbol
symbol : variable conn
location: class showme.formHandlingServlet
CallableStatement cstmt = conn.prepareCall(
The source need some work. You define a connection object in a try block.
That's the full scope of the object (ie: no one sees it outside the try block).
Don't create a connection in init(). Just do it in the post() right before you're
going to use it, and close it in a finally block:

Connect ion conn = null; // outside try block

try {
...
conn = d.connect(...);
... do all jdbc ...
} catch (Exception e) {
...
} finally {
try { conn.close();} catch (Exception (ignore){}
}

Joe Weinstrein

^
source
-------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
import java.util.*;

/**
*
* @author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{

Properties props = new Properties();
props.put("user", "dinesh");
props.put("password", "xyxyxyxy6969");
props.put("db", "users");
props.put("server", "COMPAQSERVER");

try
{

Driver myDriver = (java.sql.Driver)Class.forName
("weblogic.jdbc.mssqlserver4.Driver").newInstance( );
Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4",
props);

}
catch (ClassNotFoundException e)
{
//throw new UnavailableException("jdbc driver not found:" +
dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = conn.prepareCall(
"{call dbo.insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to
execute the insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
out.close();
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}

"Joseph Weinstein" <jo*************@bea.com.remove_this> wrote in message
news:3F***************@bea.com.remove_this...


dinesh prasad wrote:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.


Hi! Two or three things:
1 - We don't support the use of the jdbc-odbc bridge because it's flakey

and
not threadsafe. You should download and use MS's own type-4 jdbc driver.
2 - Can you execute this stored procedure from a commandline MS DBMS

client
when you log in with the same user? I ask this, because this user's

default database
context might not be in the database where the procedure is.
3 - You can get quick weblogic-specific help in our support newsgroups,

which
you can find under the support page at www.bea.com.

Joe Weinstein at BEA

I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'

The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/default...en-us;Q238971).
Am I missing something? I posted my servlet code below.

Thanks for any help!
Dinesh

formHandlingServlet.class

-------------------------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;

/**
*
* @author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";

try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}


Jul 20 '05 #2

P: n/a
ok, great I have it working now, thanks Joe!!

Dinesh

"Joseph Weinstein" <jo*************@bea.com.remove_this> wrote in message
news:3F**************@bea.com.remove_this...


dinesh wrote:
Hi Joseph its nice to get a reply from a BEA employee..I will check out the bea groups. Well, yes I am able to execute the query from the MS query
analyzer. I am also able to perform a table read from a servlet, I run into problems when trying to insert data. I tried to use the ms jdbc and
implement it as instructed by bea edocs. here is my error:
formHandlingServlet.java [79:1] cannot resolve symbol
symbol : variable conn
location: class showme.formHandlingServlet
CallableStatement cstmt = conn.prepareCall(
The source need some work. You define a connection object in a try block.
That's the full scope of the object (ie: no one sees it outside the try

block). Don't create a connection in init(). Just do it in the post() right before you're going to use it, and close it in a finally block:

Connect ion conn = null; // outside try block

try {
...
conn = d.connect(...);
... do all jdbc ...
} catch (Exception e) {
...
} finally {
try { conn.close();} catch (Exception (ignore){}
}

Joe Weinstrein

^
source
-------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
import java.util.*;

/**
*
* @author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{

Properties props = new Properties();
props.put("user", "dinesh");
props.put("password", "xyxyxyxy6969");
props.put("db", "users");
props.put("server", "COMPAQSERVER");

try
{

Driver myDriver = (java.sql.Driver)Class.forName
("weblogic.jdbc.mssqlserver4.Driver").newInstance( );
Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props);

}
catch (ClassNotFoundException e)
{
//throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = conn.prepareCall(
"{call dbo.insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to
execute the insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
out.close();
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}

"Joseph Weinstein" <jo*************@bea.com.remove_this> wrote in message news:3F***************@bea.com.remove_this...


dinesh prasad wrote:

> I'm trying to use a servlet to process a form, then send that data to > an SQL server stored procedure. I'm using the WebLogic 8 App. server. > I am able to retrieve database information, so I know my application
> server can talk to the database.

Hi! Two or three things:
1 - We don't support the use of the jdbc-odbc bridge because it's flakey
and
not threadsafe. You should download and use MS's own type-4 jdbc
driver. 2 - Can you execute this stored procedure from a commandline MS DBMS

client
when you log in with the same user? I ask this, because this user's

default database
context might not be in the database where the procedure is.
3 - You can get quick weblogic-specific help in our support newsgroups, which
you can find under the support page at www.bea.com.

Joe Weinstein at BEA

>
> I've determined the failure occurs when the the following statement

is > executed: cstmt.execute(); (due to the failure of println statements
> placed afterwards). I get the following error after trying to execute > the stored procedure call:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> procedure 'insertTheForm'
>
> The username and password i'm using to connect is a Windows user with > admin rights. It is also associated with the Odbc connection--and of
> course is a database user..with full rights. I have executable
> permissions on the stored procedure set up as well. I did a microsoft > recommended registry fix as well (for a previous
> error:http://support.microsoft.com/default...en-us;Q238971). > Am I missing something? I posted my servlet code below.
>
> Thanks for any help!
> Dinesh
>
> formHandlingServlet.class
>
> -------------------------
> package showme;
> /*
> * formHandlingServlet.java
> *
> * Created on July 6, 2003, 7:01 PM
> */
> import javax.servlet.*;
> import javax.servlet.http.*;
> import java.io.*;
> import java.sql.*;
> import java.text.DateFormat;
>
> /**
> *
> * @author Administrator
> */
> public class formHandlingServlet extends HttpServlet {
>
> private static final String email1 = "email";
> private static final String password1 = "password1";
> private static final String password2 = "password2";
> private static final String displayname = "displayname";
>
> Connection dbConn = null;
>
> // create a persistent conneciton to the SQL server
>
> public void init() throws ServletException
> {
> String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
> String dbURL = "jdbc:odbc:Con2";
> String usernameDbConn = "dinesh";
> String passwordDbConn = "werty6969";
>
> try
> {
> Class.forName(jdbcDriver).newInstance();
> dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
> passwordDbConn);
> }
> catch (ClassNotFoundException e)
> {
> throw new UnavailableException("jdbc driver not found:" + dbURL);
> }
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
> }
> catch (Exception e)
> {
> throw new UnavailableException("error: " +e);
> }
> }
>
> public void doPost(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException
> {
> response.setContentType("text/plain");
> PrintWriter out = response.getWriter();
>
> //extract parameter information from register.jsp
>
> String email1 = request.getParameter("email1");
> String password1 = request.getParameter("password1");
> String password2 = request.getParameter("password2");
> String displayname = request.getParameter("displayname");
>
> try
> {
> //make a callable statement for a stored procedure.
> //It has four parameters
>
> CallableStatement cstmt = dbConn.prepareCall(
> "{call insertTheForm(?, ?, ?, ?)}");
>
> //set the values of the stored procedure's input parameters
>
> out.println("calling stored procedure . . .");
> cstmt.setString(1, email1);
> cstmt.setString(2, password1);
> cstmt.setString(3, password2);
> cstmt.setString(4, displayname);
> //now that the input parameters are set, we can proceed to execute the > insertTheForm stored procedure
>
> cstmt.execute();
> out.println("stored procedure executed");
> }
>
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
>
> }
> }
>
> }

Jul 20 '05 #3

P: 1
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'

The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/default...en-us;Q238971).
Am I missing something? I posted my servlet code below.

Thanks for any help!
Dinesh

formHandlingServlet.class

-------------------------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;


/**
*
* @author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";

try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}



}

Hi,
You cannot call MS SQL Server stored procedure using the "call" verb. You need to use "exec" verb. CallableStatement cstmt = dbConn.prepareCall(
"{exec insertTheForm(?, ?, ?, ?)}");

Hope it helps you.
Sanjeev.
Jun 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.