I tried to make a stored proc. & it was successfully executed but on calling it from jsp file it prompts an error

Question

create procedure school_dmo.stud(in bk_cd char(10))
select  bank_cd,bank_nm ,branch_cd,bank_hnm ,user_cd ,update_dt,update_flag, branch_nm ,city_nm 
from bankmst 
;

error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1"

jsp file

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"         
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

    Connection con = null; 
    CallableStatement cstmt=null;
    String BatchCode=request.getParameter("bank_cd");


 try {
            cstmt=con.prepareCall("{call stud(?)}");
            cstmst.setString(1,bank_cd);
            cstmt.registerOutParameter(2, Types.INTEGER);     
            cstmt.executeUpdate(); 
            int val = cstmt.getInt(3);

   if (val == 1) {
   out.println("<h2 align='center'> bank_cd Successfully added</h2>");
   }
   else {
   out.println("<h2 align='center'>  bank_cd already Exists</h2>");

   }
   }
   catch(Exception e){
   e.printStackTrace();
   }
   finally {
   cstmt.close();
   con.close();
   }


</body>
</html>

Have you tried calling stored procedure from sql server by passing parameter? Normally a function is used to return values in mysql If I am not mistaken. And your procedure neither doesn't have any out parameter value. — Jacob, Aug 10 '13 at 13:20
Try the following link, might be useful http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-statements-callable.html — Jacob, Aug 10 '13 at 13:23
thank you for your suggestion .I changed the code as below but now also having the same problem. CREATE PROCEDURE stud( out bk_cd_out CHAR( 4 ) , out bank_nm CHAR( 40 ) , out branch_cd CHAR( 8 ) , out bank_hnm CHAR( 40 ) , out user_cd CHAR( 18 ) , out update_dt DATE, out update_flag CHAR( 1 ) , out branch_nm CHAR( 40 ) , out city_nm CHAR( 40 ) ) SELECT bank_cd, bank_nm, branch_cd, bank_hnm, user_cd, update_dt, update_flag, branch_nm, city_nm FROM bankmst — gourav, Aug 12 '13 at 05:14
using the input variable in the code shows error while executing on sql server — gourav, Aug 12 '13 at 05:15
I changed the stored procedure code, tell me it is correct or not CREATE PROCEDURE stud( in bk_cd_in CHAR( 4 ), out bk_cd_out CHAR( 4 ), out bk_nm CHAR( 40 ), out brh_cd CHAR( 8 ), out bak_hnm CHAR( 40 ), out ur_cd CHAR(18), out updt DATE, out updt_flag CHAR( 1 ), out brh_nm CHAR( 40 ), out cty_nm CHAR( 40 ) ) insert into stud(bk_cd_out, bk_nm, brh_cd, bak_hnm, ur_cd, updt, updt_flag, brh_nm, cty_nm) SELECT bank_cd, bank_nm, branch_cd, bank_hnm, user_cd, update_dt, update_flag, branch_nm, city_nm FROM bankmst WHERE bank_cd = bk_cd_in; — gourav, Aug 12 '13 at 08:44

coding_idiot · Answer 1 · 2013-08-12T07:47:08.733

0

Sample from one of my programs :

Procedure Declaration

create procedure countLeftChildren(in parent_id int,out RightChildren int)...

Calling Code

            String call = "{call countLeftChildren(?,?)}";
            CallableStatement callableStatement = connection.prepareCall(call);
            callableStatement.setInt(1, u.getIdsoftuser());
            callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
            callableStatement.executeUpdate();
            int children = callableStatement.getInt(2);

registerOutParameter (index,type) - index : number at which the variable is declared in procedure's declaration (create code). In the above example it's the second variable.

Your code is wrong, since there is no OUT parameter defined in your procedure's declaration

cstmt.registerOutParameter(2, Types.INTEGER);

create procedure school_dmo.stud(in bk_cd char(10)) - Only one parameter and that 
is a IN parameter

Another very good example.

edited Aug 12 '13 at 07:47

answered Aug 11 '13 at 02:10

coding_idiot

13,526
10
65
116

Simply write the above code in scriptlets! The example that I gave has nothing to do with JSP or servlet anyways. – coding_idiot Aug 12 '13 at 07:46
I changed the stored procedure code, tell me it is correct or not CREATE PROCEDURE stud( in bk_cd_in CHAR( 4 ), out bk_cd_out CHAR( 4 ), out bk_nm CHAR( 40 ), out brh_cd CHAR( 8 ), out bak_hnm CHAR( 40 ), out ur_cd CHAR(18), out updt DATE, out updt_flag CHAR( 1 ), out brh_nm CHAR( 40 ), out cty_nm CHAR( 40 ) ) insert into stud(bk_cd_out, bk_nm, brh_cd, bak_hnm, ur_cd, updt, updt_flag, brh_nm, cty_nm) SELECT bank_cd, bank_nm, branch_cd, bank_hnm, user_cd, update_dt, update_flag, branch_nm, city_nm FROM bankmst WHERE bank_cd = bk_cd_in; – gourav Aug 12 '13 at 08:46
@gourav there is one IN and 9 OUT parameters, now how are you calling this in JSP ? In the example, I wrote in the answer, there's only one IN and one OUT parameter which I mapped while calling it. – coding_idiot Aug 12 '13 at 09:49
try { Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school_dmo","java@ school_dmo","school_dmo"); CallableStatement calstat=conn.prepareCall("{CALL stud(?,?,?,?,?,?,?,?,?,?)}"); calstat.setString(1,s1); calstat.registerOutParameter(2, bk_cd_out); calstat.registerOutParameter(3, bk_nm); . . calstat.registerOutParameter(10, cty_nm); resultset rs = calstat.execute(); conn.close(); calstat.close(); out.println("Your data has been inserted into table."); } catch(Exception e) { out.println("the exception is"+e); } – gourav Aug 12 '13 at 10:02
I doubt if you're using any IDE, but out parameter registration should tell it's datatype, your code is wrong, instead it should be something like `callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);` – coding_idiot Aug 12 '13 at 10:04
on running the jsp file the error that occured: the selection did not contain any resource that can run on server – gourav Aug 12 '13 at 10:26
http://stackoverflow.com/questions/10790679/eclipse-the-selection-did-not-contain-any-resource-that-can-run-on-a-server – coding_idiot Aug 12 '13 at 12:04
Glad to hear that ! Although I still consider the main error was just that OUT params weren't registered properly. Anyways, I guess you mark this as the correct answer now. – coding_idiot Aug 13 '13 at 08:57
the jsp code is ok but now the problem comes to stored procedure its not returning any value. – gourav Aug 13 '13 at 09:51
and found under user defined function heading – gourav Aug 13 '13 at 11:08

I tried to make a stored proc. & it was successfully executed but on calling it from jsp file it prompts an error

1 Answers1