EXECUTE AS question: Executing an MS SQL stored procedure from a java servlet

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/defaul...;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);

}
}

}
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" <joe.remove_this@.bea.com.remove_this> wrote in message
> news:3F0A582A.DE7760DD@.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/defaul...;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);
> > > > }
> > > }
> > > > }|||ok, great I have it working now, thanks Joe!!

Dinesh

"Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in message
news:3F0AD70E.8876F92@.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" <joe.remove_this@.bea.com.remove_this> wrote in
message
> > news:3F0A582A.DE7760DD@.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/defaul...;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);
> > > > > > }
> > > > }
> > > > > > }
>|||

Quote:

Originally Posted by dinesh prasad

Thanks for any help!
Dinesh

formHandlingServlet.class

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

Connection dbConn = null;

// create a persistent conneciton to the SQL server

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

//extract parameter information from register.jsp

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

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.

Monday, March 26, 2012

Executing an MS SQL stored procedure from a java servlet

No comments:

Post a Comment

EXECUTE AS question

Blog Archive

About Me