Java: Calling a stored-procedure I get mysql error

Question

I'm trying to add a date value in yyyy-mm-dd format in mysql using a stored-procedure.

In case I call the stored-procedure within mysql, I can insert the same data properly.

However, when I'm trying to call it though java I get error:

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 'medication(1005,51,'2' at line 1

My stored-procedure is:

CREATE DEFINER=`xxx`@`localhost` PROCEDURE `insert_eD_dispensed medication`(IN `prescription_mapping_id` int(11), IN  `patient_id` int(11),   IN `date_of_dispense` date )
    NO SQL
INSERT INTO `prescription_dispensation_mapping` (`prescription_mapping_id`,  `patient_id`,  `date_of_dispense` )

The call is:

public boolean insert_eD_dispensation (String prescription_mapping_id,  String patient_id,  String date_of_dispense) {
        Connection conn = this.getDBMySQLCon();
        String queryString;
        PreparedStatement preparedStmt = null;
        try {
            queryString = " {CALL insert_eD_dispensed medication(?,?,?)}";
            preparedStmt = conn.prepareStatement(queryString);

            preparedStmt.setInt(1, toInteger(prescription_mapping_id));
            preparedStmt.setInt(2, toInteger(patient_id));
            preparedStmt.setDate(3, java.sql.Date.valueOf(date_of_dispense));
            preparedStmt.execute();
            conn.close();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

The call of the java method is:

String prescription_mapping_id="1005"; 
String patient_id="1"; 
String date_of_dispense="2019-06-19"; 

boolean x = insert_eD_dispensation(prescription_mapping_id, patient_id, date_of_dispense)

score 0 · Accepted Answer · answered Jun 22 '19 at 14:35

0

I think the problem could be in the signature of your proceduce. But also problems with timezone, here is a post for checking this. enter link description here

answered Jun 22 '19 at 14:35

davidza5

86
1
10

Thank you davidza5. Indeed the problem was the signature of my stored-procedure. It's name has a space character `insert_eD_dispensed medication`. I renamed it to insert_eD_dispensed_medication` and now it's working! Thanks! – zinon Jun 22 '19 at 14:43

Java: Calling a stored-procedure I get mysql error

1 Answers1