Description:
Incorrect data returned on method call resultSet.getString("blob_value") when connection is set to useServerPrepStmts=true.
When useServerPrepStmts=true
Calling resultSet.getString("blob_value") returns text of byte[].toString().
Incorrect result.
When useServerPrepStmts=false
Calling resultSet.getString("blob_value") returns actual text value of blob
same as I can get with like new String(blob.getBytes(1l, (int) blob.length()));
Correct result.
How to repeat:
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author Alexander Kunin
*/
public class BlobAsStringTest {
private static String USER = "root";
private static String PASS = "root";
private static String CATALOG = "use_server_prepared_statement_test";
private static String URL = "jdbc:mysql://localhost:3306/"; //
private static String USE_SERVER_PREP_STMTS = "?useServerPrepStmts=true"; //
private static String VALUE_ONE = "bar";
private static String VALUE_BLOB = "mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL";
private static String DRIVER = "com.mysql.jdbc.Driver";
/**
* Constructor
*/
public BlobAsStringTest() {
}
public static void main(String[] args) {
boolean dbExists = false;
Connection conn1 = null;
Connection conn2 = null;
Statement st = null;
PreparedStatement pstm1 = null, pstm2 = null;
ResultSet rs = null, rs1 = null, rs2 = null;
String sql = "select id, val_one, val_blob from foo where val_one = ?";
try {
Class.forName(DRIVER);
System.out.println("Connecting to SQL server ...");
conn1 = DriverManager.getConnection(URL, USER, PASS);
st = conn1.createStatement();
st.execute("DROP DATABASE IF EXISTS `use_server_prepared_statement_test`");
rs = st.executeQuery("SHOW DATABASES");
while (rs.next()) {
String dbName = rs.getString(1);
if (CATALOG.equals(dbName)) {
// we are good to go. Don't need to create DB.
System.out.println("Database " + CATALOG + " found");
dbExists = true;
break;
}
}
if (!dbExists) {
dbExists = BlobAsStringTest.createDb(conn1, CATALOG);
st.execute("use " + CATALOG);
// table with blob (mediumblob etc.) column type
st.execute("CREATE TABLE `foo`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `val_one` VARCHAR(10), `val_blob` blob, PRIMARY KEY (`id`) )");
st.execute("INSERT INTO `foo`(`id`,`val_one`,`val_blob`) VALUES ( NULL,'" + VALUE_ONE + "','"
+ VALUE_BLOB + "')");
}
System.out.println("---------------------------------------------");
// test 1 - OK
System.out.println("Test 1. URL: " + URL);
conn1.setCatalog(CATALOG);
pstm1 = conn1.prepareStatement(sql);
pstm1.setString(1, VALUE_ONE);
rs1 = pstm1.executeQuery();
printDataTypes(rs1);
if (rs1.next()) {
printData(rs1);
String blobString = rs1.getString("val_blob");
compareValues(blobString);
}
System.out.println();
System.out.println("---------------------------------------------");
// - test 2 - Failed
// let's test with setting useServerPrepStmts=true
System.out.println("Test 1. URL: " + URL + USE_SERVER_PREP_STMTS);
conn2 = DriverManager.getConnection(URL + USE_SERVER_PREP_STMTS, USER, PASS);
conn2.setCatalog(CATALOG);
pstm2 = conn2.prepareStatement(sql);
pstm2.setString(1, VALUE_ONE);
rs2 = pstm2.executeQuery();
printDataTypes(rs2);
if (rs2.next()) {
printData(rs2);
String blobString = rs2.getString("val_blob");
compareValues(blobString);
// looks like rs2.getBytes() returns byte[].toString()
byte[] bytes = rs2.getBytes("val_blob");
System.out.println("getString(\"val_blob\") is returning byte[].toString(), for example, calling rs2.getBytes(\"val_blob\").toString() returns "
+ bytes);
}
System.out.println("---------------------------------------------");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
BlobAsStringTest.close(rs);
BlobAsStringTest.close(st);
BlobAsStringTest.close(rs1);
BlobAsStringTest.close(rs2);
BlobAsStringTest.close(pstm1);
BlobAsStringTest.close(pstm2);
BlobAsStringTest.close(conn1);
BlobAsStringTest.close(conn2);
}
}
private static void printData(ResultSet rs) throws SQLException {
System.out.println("----Data----");
System.out.println(rs.getString("id"));
System.out.println(rs.getString("val_one"));
java.sql.Blob blob = rs.getBlob("val_blob");
String str = new String(blob.getBytes(1l, (int) blob.length()));
System.out.println(str);
}
private static void printDataTypes(ResultSet rs) throws SQLException {
System.out.println("----Data Type----");
for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + "(" + rs.getMetaData().getColumnTypeName(i + 1)
+ ") | ");
}
System.out.println();
}
private static void compareValues(String blobString) {
System.out.println("-----Compare values-----");
System.out.println("getString(\"val_blob\") equals actual blob text value: "
+ (blobString.equals(VALUE_BLOB) ? "YES! :)" : "Oh no... :(") + "\n value returned: " + blobString);
System.out.println();
}
public static void close(Statement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
}
}
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
}
}
/** create database and use it
* @throws IOException */
public static boolean createDb(Connection conn, String catalog) throws IOException {
Statement st = null;
try {
st = conn.createStatement();
st.execute("CREATE DATABASE `" + catalog + "`;");
st.execute("USE `" + catalog + "`;");
System.out.println("Database " + catalog + " created");
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
close(st);
}
}
}
Description: Incorrect data returned on method call resultSet.getString("blob_value") when connection is set to useServerPrepStmts=true. When useServerPrepStmts=true Calling resultSet.getString("blob_value") returns text of byte[].toString(). Incorrect result. When useServerPrepStmts=false Calling resultSet.getString("blob_value") returns actual text value of blob same as I can get with like new String(blob.getBytes(1l, (int) blob.length())); Correct result. How to repeat: import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author Alexander Kunin */ public class BlobAsStringTest { private static String USER = "root"; private static String PASS = "root"; private static String CATALOG = "use_server_prepared_statement_test"; private static String URL = "jdbc:mysql://localhost:3306/"; // private static String USE_SERVER_PREP_STMTS = "?useServerPrepStmts=true"; // private static String VALUE_ONE = "bar"; private static String VALUE_BLOB = "mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL"; private static String DRIVER = "com.mysql.jdbc.Driver"; /** * Constructor */ public BlobAsStringTest() { } public static void main(String[] args) { boolean dbExists = false; Connection conn1 = null; Connection conn2 = null; Statement st = null; PreparedStatement pstm1 = null, pstm2 = null; ResultSet rs = null, rs1 = null, rs2 = null; String sql = "select id, val_one, val_blob from foo where val_one = ?"; try { Class.forName(DRIVER); System.out.println("Connecting to SQL server ..."); conn1 = DriverManager.getConnection(URL, USER, PASS); st = conn1.createStatement(); st.execute("DROP DATABASE IF EXISTS `use_server_prepared_statement_test`"); rs = st.executeQuery("SHOW DATABASES"); while (rs.next()) { String dbName = rs.getString(1); if (CATALOG.equals(dbName)) { // we are good to go. Don't need to create DB. System.out.println("Database " + CATALOG + " found"); dbExists = true; break; } } if (!dbExists) { dbExists = BlobAsStringTest.createDb(conn1, CATALOG); st.execute("use " + CATALOG); // table with blob (mediumblob etc.) column type st.execute("CREATE TABLE `foo`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `val_one` VARCHAR(10), `val_blob` blob, PRIMARY KEY (`id`) )"); st.execute("INSERT INTO `foo`(`id`,`val_one`,`val_blob`) VALUES ( NULL,'" + VALUE_ONE + "','" + VALUE_BLOB + "')"); } System.out.println("---------------------------------------------"); // test 1 - OK System.out.println("Test 1. URL: " + URL); conn1.setCatalog(CATALOG); pstm1 = conn1.prepareStatement(sql); pstm1.setString(1, VALUE_ONE); rs1 = pstm1.executeQuery(); printDataTypes(rs1); if (rs1.next()) { printData(rs1); String blobString = rs1.getString("val_blob"); compareValues(blobString); } System.out.println(); System.out.println("---------------------------------------------"); // - test 2 - Failed // let's test with setting useServerPrepStmts=true System.out.println("Test 1. URL: " + URL + USE_SERVER_PREP_STMTS); conn2 = DriverManager.getConnection(URL + USE_SERVER_PREP_STMTS, USER, PASS); conn2.setCatalog(CATALOG); pstm2 = conn2.prepareStatement(sql); pstm2.setString(1, VALUE_ONE); rs2 = pstm2.executeQuery(); printDataTypes(rs2); if (rs2.next()) { printData(rs2); String blobString = rs2.getString("val_blob"); compareValues(blobString); // looks like rs2.getBytes() returns byte[].toString() byte[] bytes = rs2.getBytes("val_blob"); System.out.println("getString(\"val_blob\") is returning byte[].toString(), for example, calling rs2.getBytes(\"val_blob\").toString() returns " + bytes); } System.out.println("---------------------------------------------"); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { BlobAsStringTest.close(rs); BlobAsStringTest.close(st); BlobAsStringTest.close(rs1); BlobAsStringTest.close(rs2); BlobAsStringTest.close(pstm1); BlobAsStringTest.close(pstm2); BlobAsStringTest.close(conn1); BlobAsStringTest.close(conn2); } } private static void printData(ResultSet rs) throws SQLException { System.out.println("----Data----"); System.out.println(rs.getString("id")); System.out.println(rs.getString("val_one")); java.sql.Blob blob = rs.getBlob("val_blob"); String str = new String(blob.getBytes(1l, (int) blob.length())); System.out.println(str); } private static void printDataTypes(ResultSet rs) throws SQLException { System.out.println("----Data Type----"); for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) { System.out.print(rs.getMetaData().getColumnName(i + 1) + "(" + rs.getMetaData().getColumnTypeName(i + 1) + ") | "); } System.out.println(); } private static void compareValues(String blobString) { System.out.println("-----Compare values-----"); System.out.println("getString(\"val_blob\") equals actual blob text value: " + (blobString.equals(VALUE_BLOB) ? "YES! :)" : "Oh no... :(") + "\n value returned: " + blobString); System.out.println(); } public static void close(Statement st) { if (st != null) { try { st.close(); } catch (SQLException e) { } } } public static void close(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } public static void close(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { } } } /** create database and use it * @throws IOException */ public static boolean createDb(Connection conn, String catalog) throws IOException { Statement st = null; try { st = conn.createStatement(); st.execute("CREATE DATABASE `" + catalog + "`;"); st.execute("USE `" + catalog + "`;"); System.out.println("Database " + catalog + " created"); return true; } catch (SQLException e) { e.printStackTrace(); return false; } finally { close(st); } } }