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