ODBC problems at writing data in the Mysql database on windows 10

Hi,

I'm using ODBC MySQL on windows 10 to insert a matlab table variable containing 17000 rows in a MySQL database table using the datainsert() function. I made all the insertions at once. However I'm having the following error and I can't figure out what is wrong (the column type of the column 'idCountry' is ok between the table variable on matlab and the field on the database. Any help or insight will be really appreciated:

java.sql.SQLException: SQL Exception : Unknown SQL Type for PreparedStatement.setObject (SQL Type=-9
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(Unknown Source)
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setObject(Unknown Source)
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)
  at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)
  at com.mathworks.toolbox.database.writeTheData.cellWrite(writeTheData.java:284)
Error using database.jdbc.connection/datainsert (line 301)
Unable to complete insert operation. [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.15-log]Out of range value for column 'idCountry' at row 1|

Thanks,

Carlos

Answer 1

[Piyush Kumar]

Hi,

The issues you are facing might be due to various reasons. You can try the following troubleshooting steps -

1. Check Data Types: Ensure that the data types in your MATLAB table match those in your MySQL database. The error message suggests an issue with the idCountry column. Verify that the data type in MATLAB is compatible with the MySQL column type.
2. Update ODBC Driver: Make sure you are using the latest version of the MySQL ODBC driver.
3. Batch Size: If you’re inserting a large number of rows at once, try breaking the insertion into smaller batches. This can help avoid issues related to memory and transaction size.
4. Prepared Statements: The error message indicates a problem with PreparedStatement.setObject. Ensure that your prepared statements are correctly set up and that the data types are correctly specified.
5. Debugging: Enable detailed logging for your ODBC driver to get more insights into what might be causing the issue. This can help you pinpoint the exact operation that is failing.
6. Check for Out of Range Values: The error message also mentions an “Out of range value for column ‘idCountry’”. Ensure that the values you are trying to insert are within the acceptable range for the column type.