Lỗi khi truyền paramater csdl

nhphuocit

New Member
29/5/12
30
7
3
28
Em có đoạn code lấy dữ liệu về ntn:
Code:
public static Table[] getByDate(int day, int month, int year) throws SQLException {
        Statement state = connectDB.conn().createStatement();
        String sql = "call table_get_by_date_reservation(?)";
        CallableStatement calState = connectDB.conn().prepareCall(sql);
        calState.setInt(1, day);
        //calState.setInt(2, month);
        //calState.setInt(3, year);
        ResultSet rs = calState.executeQuery(sql);
        rs.last();
        Table[] tables = new Table[rs.getRow()];
        rs.beforeFirst();
        int i = 0;
        while (rs.next()) {
            tables[i] = new Table(rs.getInt("id"), rs.getString("name"), rs.getInt("type"), rs.getInt("location"), rs.getInt("numOfChair"), rs.getInt("status"));
            i++;
        }
        return tables;
    }
Và procedure nó như này:
Code:
DELIMITER $$

USE `vttu_restaurant`$$

DROP PROCEDURE IF EXISTS `table_get_by_date_reservation`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `table_get_by_date_reservation`(in ngay int)
BEGIN
    SELECT *
    FROM `table` AS tb
    WHERE tb.id NOT IN(
    SELECT tb.id
    FROM `table` AS tb,table_reservation_detail AS tb_r_d,table_reservation tb_r
    WHERE tb.id=tb_r_d.id_table_id AND tb_r_d.id_table_reservation_id=tb_r.id
    AND tb_r.status=FALSE AND
    day(begindate)=ngay and month(begindate)=2 and year(begindate)=2014);
END$$

DELIMITER ;
Khi chạy nó lại báo lỗi này ạ
Code:
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 '?)' 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:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
    at vn.edu.vttu.data.Table.getByDate(Table.java:156)
    at vn.edu.vttu.ui.panel_table_reservatio.loadTableMatrix(panel_table_reservatio.java:69)
    at vn.edu.vttu.ui.panel_table_reservatio.<init>(panel_table_reservatio.java:57)
    at vn.edu.vttu.ui.panel_table$2.actionPerformed(panel_table.java:103)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
    at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
    at java.awt.Component.processMouseEvent(Component.java:6505)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6270)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4861)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:703)
    at java.awt.EventQueue.access$000(EventQueue.java:102)
    at java.awt.EventQueue$3.run(EventQueue.java:662)
    at java.awt.EventQueue$3.run(EventQueue.java:660)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:676)
    at java.awt.EventQueue$4.run(EventQueue.java:674)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:673)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:244)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:163)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:147)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:139)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:97)
Nếu như không truyền paramater vào thì không sao. Và khi gọi trên mysql thì chạy ok
Như vậy xử lý sao ạ
 

vtcNew

Active Member
8/10/11
252
60
28
Mình nghĩ cú pháp gọi procedure của bạn từ java sai.
Procedure đang nhận 1 đối số có kiểu int
 

trieutulonga

Member
15/2/11
58
6
8
Statement state = connectDB.conn().createStatement();

state cái này tạo nhưng không thấy cậu dùng ?

CallableStatement calState = connectDB.conn().prepareCall(sql); // có thể chỗ này không hiểu là muốn gọi đên Statement nên nó hiểu là sql bình thường
try with :

String sql = "call table_get_by_date_reservation("+day+")";
thực ra trong Procedure nó cũng chỉ nhận biến int(kiểu khác mysql sẽ thông báo lỗi) nên không cần dùng kiểu Statement
 
Sửa lần cuối:

Joe

Thành viên VIP
21/1/13
2,732
1,255
113
Young man,
the exception says
at vn.edu.vttu.data.Table.getByDate(Table.java:156)
and you posted only a piece of codes that's less than 156 lines.
When you're so noisy then you'd put the whole source or tell the Forum what is in the line 156. Read the exception before you start to make noise. The way you ask confuses the people who voluntarily want to help you. You understand?
And furthermore:
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 '?)' at line 1
it says: ...check the manual that corresponds to your MySQL server version for the right syntax to use near '?)'
 
Sửa lần cuối:
  • Like
Reactions: javaee