Java String can't be converted to MySQL char-CodePudding

I'm doing a Java program that has to interact with a MySQL database, and I'm trying to delete a row of a table from a query in Java.

The problem is that when I try to convert a String in Java, "2021/2022" to char(9) I get an error that says that data is too long for column. Can anyone help?

This is the method that should delete the row:

public boolean borrarMatricula(Connection conn, int alumno, int profesor, int asignatura, 
        String c){
    
    boolean borrado = false;
    
    String drop = "DROP PROCEDURE IF EXISTS DELETE_ENROLLMENT";
    String rutina = "CREATE PROCEDURE DELETE_ENROLLMENT(IN alumno double(4,0), "
              "IN profesor double(2,0), IN asignatura double(3,0), IN c char(9))"
                    "BEGIN "
                    "DELETE FROM MATRICULAS WHERE codigoAlumno=alumno and "
              "codigoProfesor=profesor and codigoAsignatura=asignatura and curso=c;"
                    "END";
    
    try{
        Statement s = conn.createStatement();
        s.execute(drop);
        s.execute(rutina);
        
        CallableStatement cs=conn.prepareCall("{call DELETE_ENROLLMENT("  
                alumno   ","   profesor   ","   asignatura   ","   c   ")}");
        cs.execute();
        
        borrado = true;

    }
    catch(SQLException e){
        Vista.muestraErrorSQL(e);
    }
    catch(Exception e){
        e.printStackTrace(System.err);
    }
    
    return(borrado);
}

"Curso" is defined as a char(9), and the String I'm using is 2021/2022

CodePudding user response：

What happened is that you are concatenating Java variables into your CALL statement:

    CallableStatement cs=conn.prepareCall("{call DELETE_ENROLLMENT("  
            alumno   ","   profesor   ","   asignatura   ","   c   ")}");

So it runs a CALL statement as though you formatted it like this:

mysql> call delete_enrollment(1, 1, 1, 2021/2022);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
 --------- ------ ---------------------------------------- 
| Level   | Code | Message                                |
 --------- ------ ---------------------------------------- 
| Warning | 1265 | Data truncated for column 'c' at row 1 |
 --------- ------ ----------------------------------------

In Java, warnings are promoted to errors. The value is not the string '2021/2022', but a floating-point value which is the quotient of 2021/2022, or approximately 0.9995. It results in a warning when you try to use a floating-point value as a string.

If you used an actual quoted string, there is no warning:

mysql> call delete_enrollment(1, 1, 1, '2021/2022');
Query OK, 0 rows affected (0.00 sec)

But you should just avoid using string concatenation. You're already using a prepared statement, so you should use bound parameters:

    CallableStatement cs=conn.prepareCall("{call DELETE_ENROLLMENT(?, ?, ?, ?)}");
    cs.setDouble(1, alumno);
    cs.setDouble(2, profesor);
    cs.setDouble(3, asignatura);
    cs.setString(4, c);
    cs.execute();

Using parameters instead of string-concatenation is one of the most important reasons to use prepared statements. It makes your code more secure and less error-prone, and it's easier to read and write the code.