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);
}
}
}
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); } } }