SQL server doesn't return a cursor like oracle does. Therefore, its not necessary to
register an output parameter. Simply use rs = stmt.executeQuery(). The parameter
list should be enclosed with ()'s not []'s. Lastly, the output parameter is
referenced with index 1, the input parameter with index 2...
>>> CStmt.setString(2,headitem_name);
>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
>
===========================================================================
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
