Description:
# Issue with JSON via JDBC
I have a table that I want to store json
```sql
CREATE TABLE `schemas_dev`.`article` (`id` BIGINT AUTO_INCREMENT NOT NULL, `content` JSON NULL, CONSTRAINT `PK_ARTICLE` PRIMARY KEY (`id`));
```
Then running the following causes the following.
```java
try(
InputStream JSON = new ClassPathResource("data.json").getInputStream();
Connection connection = datasource.getConnection();
PreparedStatement statement = connection.prepareStatement("insert into article(content) values(?)", Statement.RETURN_GENERATED_KEYS )) {
statement.setObject(1, JSON);
statement.executeUpdate();
try (ResultSet keys = statement.getGeneratedKeys()) {
while (keys.next()) {
System.out.println(keys.getLong(1));
}
}
}
catch (SQLException e) {
throw new IllegalArgumentException(e);
}
```
I get the following error
```
: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
at JDBCJSON.main(JDBCJSON.java:86)
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
```
However using column type `LONGTEXT` or `LONGBLOB` doesn't raise this error.
Please allow `JSON` to accept `InputStream` via `setObject` to allow large json objects in prepared statements?
How to repeat:
import com.mysql.cj.MysqlType;
import com.mysql.cj.jdbc.MysqlDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
public class JSONJDBCSetParameter {
public static void main(String[] args) {
final MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setPassword("");
dataSource.setUser("root");
dataSource.setUrl("jdbc:mysql://localhost:3306/db");
try(final Connection connection = dataSource.getConnection();
final Statement statement = connection.createStatement()) {
statement.execute("DROP TABLE IF EXISTS `article`;");
statement.execute("CREATE TABLE `article` (`id` BIGINT AUTO_INCREMENT NOT NULL, `content` JSON NULL, CONSTRAINT `PK_ARTICLE` PRIMARY KEY (`id`));");
}
catch(SQLException e) {
throw new IllegalArgumentException(e);
}
try(
final InputStream inputStream = JSONJDBCSetParameter.class.getResourceAsStream("/dbzium.json");
final Connection connection = dataSource.getConnection();
final PreparedStatement statement = connection.prepareStatement("INSERT INTO `article`( `content`) VALUES (?)", Statement.RETURN_GENERATED_KEYS )) {
statement.setObject(1, inputStream, MysqlType.JSON);
statement.executeUpdate();
try (ResultSet keys = statement.getGeneratedKeys()) {
while (keys.next()) {
System.out.println(keys.getLong(1));
}
}
}
catch(SQLException | IOException e) {
throw new IllegalArgumentException(e);
}
}
}