473,372 Members | 898 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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/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
3 22062


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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Subodh | last post by:
HI, I need to run same kind of transactions (basically deleting records) in a loop but I have only 1 hour in a day to run my procedure. So I need to set a timer in a SP so that SP terminates...
1
by: rvdw | last post by:
Hi All, I've a serious problem with executing stored procedures (SQL2000) from an Access db (version 97). After executing a stored procedure , msaccess hangs. The whole call to the procedure is...
1
by: Belee | last post by:
I am developing a c# program VS 2003 and I have created my own stored procedure to insert data into 3 tables The following is the stored procedure, the code and the error message from visual studio...
1
by: Ville Huovinen | last post by:
Platform: Windows 2003 Server (MS SQL Server 2003 SP3) Language: C# Problem: My stored procedures times out randomly, some proces works fine when using them from C#, and some generate...
1
by: Prabhat | last post by:
Hi All, How Do I set a DTS so that after the DTS is executed - The DTS will Execute a Stored procedure? Or Which TASK I have to use for that and How Do I Set that? Thanks Prabhat
1
by: rraw | last post by:
hi!! i want to view a set of record in datagrid.i am using store procedure i am able to execute the stored procedure . the only problem is getting those records/data on a datagrid need help.
2
by: staeri | last post by:
When I execute a stored procedure with the following code it takes forever and result in a timeout or a hang: Sub TransformData() Dim myConnection As New SqlConnection(ConnectionString) Dim...
2
by: drolfe | last post by:
See below the code for details about the scenario. ------------------------------------------------------------------------------------ Public WithEvents conADS As New SqlConnection("Data...
0
by: Krandor | last post by:
I am trying to execute the code below. When I execute the exact same code in the Query Analyzer, I get 6 records so there are records to be found. But when I try to do it through ASP, I get Error...
2
by: hanuman308 | last post by:
hi there i have written follwing SP to add two columns in my table (passing tablename as parameter) create procedure sp_addCol @tablename varchar (50) as DECLARE @tsql_TZ varchar (200) SET...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.