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...[color=blue]
>
>
> dinesh wrote:
>[color=green]
> > Hi Joseph its nice to get a reply from a BEA employee..I will check out[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
into[color=blue][color=green]
> > 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([/color]
>
> 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[/color]
block).[color=blue]
> Don't create a connection in init(). Just do it in the post() right before[/color]
you're[color=blue]
> 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
>[color=green]
> >
> > ^
> > 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 =[/color][/color]
myDriver.connect("jdbc:weblogic:mssqlserver4",[color=blue][color=green]
> > props);
> >
> > }
> > catch (ClassNotFoundException e)
> > {
> > //throw new UnavailableException("jdbc driver not[/color][/color]
found:" +[color=blue][color=green]
> > 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[/color][/color]
message[color=blue][color=green]
> > news:3F0A582A.DE7760DD@bea.com.remove_this...[color=darkred]
> > >
> > >
> > > dinesh prasad wrote:
> > >
> > > > I'm trying to use a servlet to process a form, then send that data[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > an SQL server stored procedure. I'm using the WebLogic 8 App.[/color][/color][/color]
server.[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
flakey[color=blue][color=green]
> > and[color=darkred]
> > > not threadsafe. You should download and use MS's own type-4 jdbc[/color][/color][/color]
driver.[color=blue][color=green][color=darkred]
> > > 2 - Can you execute this stored procedure from a commandline MS DBMS[/color]
> > client[color=darkred]
> > > when you log in with the same user? I ask this, because this user's[/color]
> > default database[color=darkred]
> > > context might not be in the database where the procedure is.
> > > 3 - You can get quick weblogic-specific help in our support[/color][/color][/color]
newsgroups,[color=blue][color=green]
> > which[color=darkred]
> > > 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[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > > executed: cstmt.execute(); (due to the failure of println statements
> > > > placed afterwards). I get the following error after trying to[/color][/color][/color]
execute[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
microsoft[color=blue][color=green][color=darkred]
> > > > recommended registry fix as well (for a previous
> > > >[/color][/color][/color]
error:
http://support.microsoft.com/default...en-us;Q238971).[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > insertTheForm stored procedure
> > > >
> > > > cstmt.execute();
> > > > out.println("stored procedure executed");
> > > > }
> > > >
> > > > catch (SQLException e)
> > > > {
> > > > throw new UnavailableException("error: " + e);
> > > >
> > > > }
> > > > }
> > > >
> > > > }
> > >[/color][/color]
>[/color]
