JDBC Connection error while executing a procedure from java

Question

I have to execute a stored procedure from a java class by passing value. But when I am trying for that, I am getting the error

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'NULL' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
    at com.coolminds.action.OperatorBillCorrection.main(OperatorBillCorrection.java:27)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

here is my code.

public class OperatorBillCorrection {

    public static void main(String args[]) throws SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/webill_operator_2016","root","root123");
            String opCode="KL04C019" ;

            for(int i=0;i<=12;i++)  {
                String dt=  "2015-"+i+"-1" ;
                PreparedStatement stmts=con.prepareStatement("call sp_getOperatorCollection(?,?)") ;
                stmts.setString(1,opCode);
                stmts.setString(2,dt);
                stmts.execute();

            }
            for(int i=0;i<=2;i++)    {
                String dt=  "2016-"+i+"-1" ;
                PreparedStatement stmts=con.prepareStatement("call sp_getOperatorCollection(?,?)") ;
                stmts.setString(1,opCode);
                stmts.setString(2,dt);
                stmts.execute();
            }

            System.out.println("Completed")  ;
            con.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  
        }
    }
}

It is working when I am using Statement stmt=con.createStatement(); stmt.execute("call sp_getOperatorCollection('KL04C019','2015-09-01')"); Instead of PreparedStatement

Did you try to call the same stored procedure with the same parameters from any SQL client? — Yoav Gur
– Yoav Gur, Commented Feb 24, 2016 at 8:40
yes. It is working when I am using Statement stmt=con.createStatement(); stmt.execute("call sp_getOperatorCollection('KL04C019','2015-09-01')"); Instead of PreparedStatement — Sreevidya Aravind
– Sreevidya Aravind, Commented Feb 24, 2016 at 8:45
if everything else is fine , then try CallableStatement , javadoc says - The interface CallableStatement used to execute SQL stored procedures. — Arun Xavier
– Arun Xavier, Commented Feb 24, 2016 at 9:03

Fahad · Accepted Answer · 2016-02-24 11:36:13Z

1

use java.sql.Date instead of java.util.date.

java.util.Date date = formatter.parse(dateString);

java.sql.Date dt = new java.sql.Date(date.getTime());

answered Feb 24, 2016 at 11:36

Fahad

762 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JDBC Connection error while executing a procedure from java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related