How to retrieve OUT parameter from MYSQL stored procedure to stream in Pentaho Data Integration (Kettle)?

Question

I am unable to get the OUT parameter of a MySQL procedure call in the output stream with the procedure call step of Pentaho Kettle.

I'm having big trouble retrieving OUT parameter from MYSQL stored procedure to stream. I think it's maybe a kind of bug becouse it only occurs with Integer out parameter, it works with String out parameter. The exception I get is:

Invalid value for getLong() - '

I think the parameters are correctly set as you can see in the ktr.

You can replicate the bug in this way:

Schema

create schema if not exists test;
use test;
DROP PROCEDURE IF EXISTS procedure_test;
delimiter $$
CREATE PROCEDURE procedure_test(IN in_param INT UNSIGNED, OUT out_param INT UNSIGNED)
 BEGIN
  SET out_param := in_param;
 END
$$

KTR You can download here .ktr file with the steps. You just have to setup the connection to MySQL test schema to try it.

Other data

• MySQL connector: 5.1.30
• MySQL version: 5.5
• Kettle version: 5.0.1-stable
• OS: Ubuntu 12.04

Any help from the community will be highly appreciated.

Thanks in advance!

Answer 1

The bug does not seem to occur if number (and decimal at DB level) is used as the output parameter type. So this may be used as a work around.

With a DB procedure using this statement (as returned by phpMyAdmin):

CREATE DEFINER = `root`@`localhost` 
     PROCEDURE `procedure_test` ( IN `in_param` INT ZEROFILL, 
                                  OUT `out_param` DECIMAL( 10 ) ) 
     NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
     BEGIN 
        SET out_param =2 * in_param;
     END

I was able to run this transformation

reading this input

IN_VALUE
1
2
3
99

yielding this output

<?xml version="1.0" encoding="UTF-8"?>
<Rows>
  <Row><OUT_VALUE> 2,0</OUT_VALUE> </Row>
  <Row><OUT_VALUE> 4,0</OUT_VALUE> </Row>
  <Row><OUT_VALUE> 6,0</OUT_VALUE> </Row>
  <Row><OUT_VALUE> 198,0</OUT_VALUE> </Row>
</Rows>