Java - MySQL Calling Stored Procedure

Question

I'm trying to call a Stored Procedure from Java. However, what I did was looking for a Function instead. What did I miss? Here's what I have tried so far;

open();

try {

    statement = conn.prepareStatement(StringConstant.PROC_GET_POSITIONS);
    ResultSet resultSet = statement.executeQuery();

    while( resultSet.next() ){

        System.out.println(resultSet.getString(0));

    }

} catch ( SQLException sqlEx ) {
    sqlEx.printStackTrace();
}

close();

Throwing this Exception;

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION test.get_positions does not exist

This is how the stored procedure is written (This is for the purpose of testing):

CREATE FUNCTION get_positions(OUT o_position VARCHAR(25))
BEGIN

  SELECT pos_name
  INTO o_position
  FROM master_position;

END;

The reason I made the question is that SO suggested this titles:

-automate call stored procedure 1
-How to Call a Stored Procedure within a Stored Procedure in MySQL 1
-Calling a Stored Procedure in Hibernate 2
-Call a Stored Procedure From a Stored Procedure and/or using COUNT 2
-mysql stored procedure call hibernate 2

None of those answered my question.

read this maybe it will help publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/… — Stephan
– Stephan, Commented May 23, 2013 at 7:40
why are you using a stored procedure when you can execute that query directly? — Stephan
– Stephan, Commented May 23, 2013 at 7:44
i don't see any point in converting it into a function, read this dev.mysql.com/doc/refman/4.1/en/… using this you should be able to call the procedure — Stephan
– Stephan, Commented May 23, 2013 at 8:47

Timo Giese · Accepted Answer · 2020-02-14 10:54:10Z

10

I'm right now with this problem. I found this:

        Connection conn = getMySqlConnection();
        // Step-2: identify the stored procedure
        String simpleProc = "{ call simpleproc(?) }";
        // Step-3: prepare the callable statement
        CallableStatement cs = conn.prepareCall(simpleProc);
        // Step-4: register output parameters ...
        cs.registerOutParameter(1, java.sql.Types.INTEGER);
        // Step-5: execute the stored procedures: proc3
        cs.execute();
        // Step-6: extract the output parameters
        int param1 = cs.getInt(1);
        System.out.println("param1=" + param1);

I think this could be a great example.

Source: http://www.java2s.com/Code/Java/Database-SQL-JDBC/CallStoredProcedureInMySql.htm

edited Feb 14, 2020 at 10:54

Timo Giese

5711 bronze badges

answered Jul 23, 2014 at 10:21

Jesus

1,1263 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juned Ahsan · Accepted Answer · 2013-05-23 07:38:45Z

4

You are trying to get the result from the stored procedure. Stored procedures do not return values instead Function does.

answered May 23, 2013 at 7:38

Juned Ahsan

68.9k12 gold badges101 silver badges138 bronze badges

1 Comment

Michael 'Maik' Ardan Over a year ago

Hi Juned, thanks for the reply. I'll update you after trying to convert the procedure into a Function.

S34N · Accepted Answer · 2013-11-26 12:54:18Z

2

Statement stmt;
            stmt = conn.createStatement();
            stmt.execute(String.format("CALL swap_devices(%d,%d)", oldDeviceID, newDeviceID));

answered Nov 26, 2013 at 12:54

S34N

8,4199 gold badges40 silver badges43 bronze badges

4 Comments

Lukas Eder Over a year ago

Using String.format() to pass "bind values" to JDBC is a sure way to get SQL injection vulnerabilities. Please never do this, always use PreparedStatement and bind values, or use a library that correctly escapes embedded values if you must use a static statement.

S34N Over a year ago

When that code was written (9 years ago), that service was not customer-facing. neither of those values passed in were coming from any exposed endpoint nor any human nor 3rd-Party Service. They were populated & executed by a cron-job. So It's still relatively safe to rule out SQL-Injection attack unless otherwise utilised as a subsequent attack prior to another attack eg. heartbleed, log4J attacks. All strings injected via RESTful API were strictly sanitised/scanned.

S34N Over a year ago

To add to the above, you can clearly see that those params are of integer/decimal data type.

Lukas Eder Over a year ago

No one will read this big paragraph. Everyone will copy paste the code snippet.

Collectives™ on Stack Overflow

Java - MySQL Calling Stored Procedure

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related