Uhh, this is not a JSP question, it's a database question. You really
shouldn't be embedding SQL in your JSPs. Write a bean and call the bean
from your JSP. That will make it much easier to debug as well...
I personally don't recommend using stored procs anyway. Embedding SQL in
Java code is easier to port to other DB platforms and easier to deploy, just
check it out from source control.
I don't use stored procs much, but looking at your code I will make a
suggestion:
create your SQL as follows:
String sql = "STORED_PROC_NAME @ParamName=?, @Param2Name=?";
CallableStatement stmt = connection.prepareCall(sql);
stmt.setInt(1, value);
stmt.setString(2, value);
stmt.executeUpdate();
Or if you are looking for a result set from a stored proc then call
stmt.executeQuery();
& I hope your sa password isn't acutally blank. SQL Server defaults to this
and it's a giant security hole.
----- Original Message -----
From: "Suresh kumar K Badiga" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, December 11, 2000 4:03 AM
Subject: help on CallableStatement
> Hi All
>
> When i execute this bellow code it is giveing the bellow error. plese tell
> me how to comeout from this....
>
> Could not EXECUTE procedure.... java.sql.SQLException: [Microsoft][ODBC
SQL
> Server Driver]Syntax error or access violation Could not EXECUTE
> procedure....
> java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Syntax error or
> access
> violation
>
> And one more thing is it is executin only one String query1 , String
> query2 rest of things are not executing.
>
> Thanks in advance
> -Suresh
>
> my jsp page is
>
****************************************************************************
> ****************************
> <html>
> <head>
> </head>
> <%@ page language="java" import="java.sql.*" %>
> <body>
> <%
> try{
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> }
> catch(java.lang.ClassNotFoundException ex) { }
> String headitem_name = request.getParameter("headitem_name");
> String clasi_name1 = request.getParameter("clasi_name1");
> String clasi_name2 = request.getParameter("clasi_name2");
> String clasi_name3 = request.getParameter("clasi_name3");
> String clasi_name4 = request.getParameter("clasi_name4");
> String clasi_name5 = request.getParameter("clasi_name5");
> String headitem_code = "";
> String clasi_code1 = "";
> String clasi_code2 = "";
> String clasi_code3 = "";
> String clasi_code4 = "";
> String clasi_code5 = "";
> String url="jdbc:odbc:Inventory";
> Connection con=DriverManager.getConnection(url, "sa", "");
> Statement stmt = con.createStatement();
> try
> {
> CallableStatement CStmt = con.prepareCall("{?=call headitemcode[?]}");
> CStmt.registerOutParameter(1,java.sql.Types.VARCHAR);
> CStmt.setString(1,headitem_name);
> CStmt.executeUpdate();
> headitem_code = CStmt.getString(1);
>
> }
> catch (Exception e)
> {
> out.println("Could not EXECUTE procedure....");
> out.println(e.toString());
> }
> try
> {
> CallableStatement CStmt1 = con.prepareCall("{?=call
> subitemcode[?,?,?,?,?]}");
> CStmt1.registerOutParameter(1,java.sql.Types.VARCHAR);
> CStmt1.registerOutParameter(2,java.sql.Types.VARCHAR);
> CStmt1.registerOutParameter(3,java.sql.Types.VARCHAR);
> CStmt1.registerOutParameter(4,java.sql.Types.VARCHAR);
> CStmt1.registerOutParameter(5,java.sql.Types.VARCHAR);
> CStmt1.setString(1,clasi_name1);
> CStmt1.setString(2,clasi_name2);
> CStmt1.setString(3,clasi_name3);
> CStmt1.setString(4,clasi_name4);
> CStmt1.setString(5,clasi_name5);
> CStmt1.executeUpdate();
> clasi_code1 = CStmt1.getString(1);
> clasi_code2 = CStmt1.getString(2);
> clasi_code3 = CStmt1.getString(3);
> clasi_code4 = CStmt1.getString(4);
> clasi_code5 = CStmt1.getString(5);
> }
> catch (Exception e)
> {
> out.println("Could not EXECUTE procedure....");
> out.println(e.toString());
> }
> String query1 ="insert into
>
dbo.head_item1(headitem_code,headitem_name,clasification_name,clasification_
> code)";
> String query2 ="values('" +headitem_code+"','" +headitem_name+ "','"
> +clasi_name1+ "','" +clasi_code1+ "')";
>
> String query3 ="insert into
>
dbo.head_item1(headitem_code,headitem_name,clasification_name,clasification_
> code)";
> String query4 ="values('" +headitem_code+ "','" +headitem_name+ "','"
> +clasi_name2+ "','" +clasi_code2+ "')";
>
> String query5 ="insert into
>
dbo.head_item1(headitem_code,headitem_name,clasification_name,clasification_
> code)";
> String query6 ="values('" +headitem_code+ "','" +headitem_name+ "','"
> +clasi_name3+ "','" +clasi_code3+ "')";
>
> String query7 ="insert into
>
dbo.head_item1(headitem_code,headitem_name,clasification_name,clasification_
> code)";
> String query8 ="values('" +headitem_code+ "','" +headitem_name+ "','"
> +clasi_name4+ "','" +clasi_code4+ "')";
>
> String query9 ="insert into
>
dbo.head_item1(headitem_code,headitem_name,clasification_name,clasification_
> code)";
> String query10 ="values('" +headitem_code+ "','" +headitem_name+ "','"
> +clasi_name5+ "','" +clasi_code5+ "')";
>
> int rowsAffected =
>
stmt.executeUpdate(query1+query2+query3+query4+query5+query6+query7+query8+q
> uery9+query10);
>
> if (rowsAffected ==1)
> {
>
> %>
>
> <H1> Successful Addition of Head Item </h1>
>
> <% }
> else
> {
> %>
>
> <H1> Sorry , Head Item Addition has failed. </h1>
>
> <%
>
> }
> stmt.close();
> con.close();
>
> %>
>
> </body>
> </html>
>
>
===========================================================================
> To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
> For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
> Some relevant FAQs on JSP/Servlets can be found at:
>
> http://java.sun.com/products/jsp/faq.html
> http://www.esperanto.org.nz/jsp/jspfaq.html
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
> http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
>
>
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
