Store byte array in MySQL from Java

I'm having trouble storing byte[] from a Java program in a MySQL database.

I've got a Java method:

public void newUser(User user) {
    Connection conn = pool.checkOut();

    try {
        CallableStatement stmt = conn.prepareCall("NewUser(?,?,?)");
        //name salt password
        stmt.setString(1, user.getName());
        stmt.setBytes(2, user.getSalt());
        stmt.setBytes(3, user.getPass());
        stmt.executeUpdate();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        pool.checkIn(conn);
    }
}

Which calls a MySQL stored procedure which I've tested and works:

DELIMITER $$

CREATE DEFINER=`[UserNameGoesHere]`@`%` PROCEDURE `NewUser`(in username varchar(50), in salt blob, in pass blob)
BEGIN

INSERT INTO Schema.Users (Username, Salt, PasswordSecure)
VALUES (username, salt, pass)
;

END

The table has columns name (varchar), salt (blob) and password (blob).

When I run it as a JUnit test I get the following output:

13:42:22.405 [main] DEBUG c.B.B.model.MySQLConnectionPool - Making a new connection pool 572247323.
13:42:23.695 [ForkJoinPool-1-worker-1] DEBUG c.B.B.model.MySQLConnectionPool - Getting a connection from the pool.
13:42:23.696 [ForkJoinPool-1-worker-1] DEBUG c.B.B.model.MySQLConnectionPool - Pool size is now 0.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NewUser('NewUserTest',_binary'!œ&æÏ£–f',_binary'Ã=ÉMcOü¿zQp4)„g|Ã' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2459)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
    at com.mysql.jdbc.CallableStatement.executeUpdate(CallableStatement.java:984)
    at com.BGB.BigIssue.model.MySQLDatabase.newUser(MySQLDatabase.java:81)
    at com.BGB.BigIssue.model.MySQLDatabaseTest.testNewUserCreatesNewUser(MySQLDatabaseTest.java:48)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
13:42:23.778 [ForkJoinPool-1-worker-1] DEBUG c.B.B.model.MySQLConnectionPool - Checking in connection.
13:42:23.778 [ForkJoinPool-1-worker-1] DEBUG c.B.B.model.MySQLConnectionPool - Pool size is now 1.
13:42:23.778 [main] INFO  c.B.B.controller.LoginController - Username GuyTest was not found.
13:42:23.785 [Thread-0] INFO  c.B.B.model.MySQLConnectionPool - Connection pool 2123610602 closing.

I've done this kind of thing in Oracle before but I can't find the problem here. Any ideas?

Answers


The fix is to change the line

CallableStatement stmt = conn.prepareCall("NewUser(?,?,?)");

to

CallableStatement stmt = conn.prepareCall("CALL NewUser(?,?,?)");

That is the correct syntax for executing the stored procedure.


Need Your Help

Switch not working within while loop (Wordpress PHP)

php wordpress loops while-loop switch-statement

I've tried what I could on this and am still stuck, so I'm looking for some help. I'm sure there's something small I'm overlooking or am not aware of, so I'd be grateful for another set of eyes to ...