Executing an MS SQL stored procedure from a java servlet

[dinesh prasad]

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

formHandlingSer vlet.class

-------------------------
package showme;
/*
* formHandlingSer vlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.* ;
import javax.servlet.h ttp.*;
import java.io.*;
import java.sql.*;
import java.text.DateF ormat;
/**
*
* @author Administrator
*/
public class formHandlingSer vlet 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 = "displaynam e";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

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

try
{
Class.forName(j dbcDriver).newI nstance();
dbConn = DriverManager.g etConnection(db URL, usernameDbConn,
passwordDbConn) ;
}
catch (ClassNotFoundE xception e)
{
throw new UnavailableExce ption("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableExce ption("error: " + e);
}
catch (Exception e)
{
throw new UnavailableExce ption("error: " +e);
}
}

public void doPost(HttpServ letRequest request, HttpServletResp onse
response) throws ServletExceptio n, IOException
{
response.setCon tentType("text/plain");
PrintWriter out = response.getWri ter();

//extract parameter information from register.jsp

String email1 = request.getPara meter("email1") ;
String password1 = request.getPara meter("password 1");
String password2 = request.getPara meter("password 2");
String displayname = request.getPara meter("displayn ame");

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

CallableStateme nt cstmt = dbConn.prepareC all(
"{call insertTheForm(? , ?, ?, ?)}");

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

out.println("ca lling 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("st ored procedure executed");
}

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

}
}

}

[Joseph Weinstein]

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:
formHandlingSer vlet.java [79:1] cannot resolve symbol
symbol : variable conn
location: class showme.formHand lingServlet
CallableStateme nt cstmt = conn.prepareCal l(
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;
/*
* formHandlingSer vlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.* ;
import javax.servlet.h ttp.*;
import java.io.*;
import java.sql.*;
import java.text.DateF ormat;
import java.util.*;

/**
*
* @author Administrator
*/
public class formHandlingSer vlet 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 = "displaynam e";

// create a persistent conneciton to the SQL server

public void init() throws ServletExceptio n
{

Properties props = new Properties();
props.put("user ", "dinesh");
props.put("pass word", "xyxyxyxy6969") ;
props.put("db", "users");
props.put("serv er", "COMPAQSERVER") ;

try
{

Driver myDriver = (java.sql.Drive r)Class.forName
("weblogic.jdbc .mssqlserver4.D river").newInst ance();
Connection conn = myDriver.connec t("jdbc:weblogi c:mssqlserver4" ,
props);

}
catch (ClassNotFoundE xception e)
{
//throw new UnavailableExce ption("jdbc driver not found:" +
dbURL);
}
catch (SQLException e)
{
throw new UnavailableExce ption("error: " + e);
}
catch (Exception e)
{
throw new UnavailableExce ption("error: " +e);
}
}

public void doPost(HttpServ letRequest request, HttpServletResp onse
response) throws ServletExceptio n, IOException
{
response.setCon tentType("text/plain");
PrintWriter out = response.getWri ter();

//extract parameter information from register.jsp

String email1 = request.getPara meter("email1") ;
String password1 = request.getPara meter("password 1");
String password2 = request.getPara meter("password 2");
String displayname = request.getPara meter("displayn ame");

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

CallableStateme nt cstmt = conn.prepareCal l(
"{call dbo.insertTheFo rm(?, ?, ?, ?)}");

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

out.println("ca lling 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("st ored procedure executed");
out.close();
}

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

}
}

}

"Joseph Weinstein" <jo************ *@bea.com.remov e_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

formHandlingSer vlet.class

-------------------------
package showme;
/*
* formHandlingSer vlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.* ;
import javax.servlet.h ttp.*;
import java.io.*;
import java.sql.*;
import java.text.DateF ormat;

/**
*
* @author Administrator
*/
public class formHandlingSer vlet 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 = "displaynam e";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

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

try
{
Class.forName(j dbcDriver).newI nstance();
dbConn = DriverManager.g etConnection(db URL, usernameDbConn,
passwordDbConn) ;
}
catch (ClassNotFoundE xception e)
{
throw new UnavailableExce ption("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableExce ption("error: " + e);
}
catch (Exception e)
{
throw new UnavailableExce ption("error: " +e);
}
}

public void doPost(HttpServ letRequest request, HttpServletResp onse
response) throws ServletExceptio n, IOException
{
response.setCon tentType("text/plain");
PrintWriter out = response.getWri ter();

//extract parameter information from register.jsp

String email1 = request.getPara meter("email1") ;
String password1 = request.getPara meter("password 1");
String password2 = request.getPara meter("password 2");
String displayname = request.getPara meter("displayn ame");

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

CallableStateme nt cstmt = dbConn.prepareC all(
"{call insertTheForm(? , ?, ?, ?)}");

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

out.println("ca lling 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("st ored procedure executed");
}

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

}
}

}

[dinesh]

ok, great I have it working now, thanks Joe!!

Dinesh

"Joseph Weinstein" <jo************ *@bea.com.remov e_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:
formHandlingSer vlet.java [79:1] cannot resolve symbol
symbol : variable conn
location: class showme.formHand lingServlet
CallableStateme nt cstmt = conn.prepareCal l(
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;
/*
* formHandlingSer vlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.* ;
import javax.servlet.h ttp.*;
import java.io.*;
import java.sql.*;
import java.text.DateF ormat;
import java.util.*;

/**
*
* @author Administrator
*/
public class formHandlingSer vlet 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 = "displaynam e";

// create a persistent conneciton to the SQL server

public void init() throws ServletExceptio n
{

Properties props = new Properties();
props.put("user ", "dinesh");
props.put("pass word", "xyxyxyxy6969") ;
props.put("db", "users");
props.put("serv er", "COMPAQSERVER") ;

try
{

Driver myDriver = (java.sql.Drive r)Class.forName
("weblogic.jdbc .mssqlserver4.D river").newInst ance();
Connection conn = myDriver.connec t("jdbc:weblogi c:mssqlserver4" , props);

}
catch (ClassNotFoundE xception e)
{
//throw new UnavailableExce ption("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableExce ption("error: " + e);
}
catch (Exception e)
{
throw new UnavailableExce ption("error: " +e);
}
}

public void doPost(HttpServ letRequest request, HttpServletResp onse
response) throws ServletExceptio n, IOException
{
response.setCon tentType("text/plain");
PrintWriter out = response.getWri ter();

//extract parameter information from register.jsp

String email1 = request.getPara meter("email1") ;
String password1 = request.getPara meter("password 1");
String password2 = request.getPara meter("password 2");
String displayname = request.getPara meter("displayn ame");

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

CallableStateme nt cstmt = conn.prepareCal l(
"{call dbo.insertTheFo rm(?, ?, ?, ?)}");

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

out.println("ca lling 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("st ored procedure executed");
out.close();
}

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

}
}

}

"Joseph Weinstein" <jo************ *@bea.com.remov e_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
>
> formHandlingSer vlet.class
>
> -------------------------
> package showme;
> /*
> * formHandlingSer vlet.java
> *
> * Created on July 6, 2003, 7:01 PM
> */
> import javax.servlet.* ;
> import javax.servlet.h ttp.*;
> import java.io.*;
> import java.sql.*;
> import java.text.DateF ormat;
>
> /**
> *
> * @author Administrator
> */
> public class formHandlingSer vlet 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 = "displaynam e";
>
> Connection dbConn = null;
>
> // create a persistent conneciton to the SQL server
>
> public void init() throws ServletExceptio n
> {
> String jdbcDriver = "sun.jdbc.odbc. JdbcOdbcDriver" ;
> String dbURL = "jdbc:odbc:Con2 ";
> String usernameDbConn = "dinesh";
> String passwordDbConn = "werty6969" ;
>
> try
> {
> Class.forName(j dbcDriver).newI nstance();
> dbConn = DriverManager.g etConnection(db URL, usernameDbConn,
> passwordDbConn) ;
> }
> catch (ClassNotFoundE xception e)
> {
> throw new UnavailableExce ption("jdbc driver not found:" + dbURL);
> }
> catch (SQLException e)
> {
> throw new UnavailableExce ption("error: " + e);
> }
> catch (Exception e)
> {
> throw new UnavailableExce ption("error: " +e);
> }
> }
>
> public void doPost(HttpServ letRequest request, HttpServletResp onse
> response) throws ServletExceptio n, IOException
> {
> response.setCon tentType("text/plain");
> PrintWriter out = response.getWri ter();
>
> //extract parameter information from register.jsp
>
> String email1 = request.getPara meter("email1") ;
> String password1 = request.getPara meter("password 1");
> String password2 = request.getPara meter("password 2");
> String displayname = request.getPara meter("displayn ame");
>
> try
> {
> //make a callable statement for a stored procedure.
> //It has four parameters
>
> CallableStateme nt cstmt = dbConn.prepareC all(
> "{call insertTheForm(? , ?, ?, ?)}");
>
> //set the values of the stored procedure's input parameters
>
> out.println("ca lling 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("st ored procedure executed");
> }
>
> catch (SQLException e)
> {
> throw new UnavailableExce ption("error: " + e);
>
> }
> }
>
> }

[gour_sanjeev]

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

formHandlingSer vlet.class

-------------------------
package showme;
/*
* formHandlingSer vlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.* ;
import javax.servlet.h ttp.*;
import java.io.*;
import java.sql.*;
import java.text.DateF ormat;


/**
*
* @author Administrator
*/
public class formHandlingSer vlet 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 = "displaynam e";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

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

try
{
Class.forName(j dbcDriver).newI nstance();
dbConn = DriverManager.g etConnection(db URL, usernameDbConn,
passwordDbConn) ;
}
catch (ClassNotFoundE xception e)
{
throw new UnavailableExce ption("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableExce ption("error: " + e);
}
catch (Exception e)
{
throw new UnavailableExce ption("error: " +e);
}
}

public void doPost(HttpServ letRequest request, HttpServletResp onse
response) throws ServletExceptio n, IOException
{
response.setCon tentType("text/plain");
PrintWriter out = response.getWri ter();

//extract parameter information from register.jsp

String email1 = request.getPara meter("email1") ;
String password1 = request.getPara meter("password 1");
String password2 = request.getPara meter("password 2");
String displayname = request.getPara meter("displayn ame");

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

CallableStateme nt cstmt = dbConn.prepareC all(
"{call insertTheForm(? , ?, ?, ?)}");

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

out.println("ca lling 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("st ored procedure executed");
}

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

}
}



}

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

Hope it helps you.
Sanjeev.