Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: MySQL Stored Procedures Example

  1. #1
    Join Date
    Nov 1999
    Posts
    9,729

    Default MySQL Stored Procedures Example

    MYSQL STORED PROCEDURES EXAMPLE
    So because a certain Kettle fan kept spamming this forum, I'm sending you an example of how to use stored procedures on MySQL.

    Please note that procedure support on MySQL is still very young and a couple of issues are to be found in even the latest stable version of the JDBC driver. Therefor, do NOT use the example posted earlier on this forum by this certain Kettle fan, but stick to normal/simple MySQL procedure code.



    Here is an example, use the latest version of Kettle as this one has the latest JDBC driver as well as code for the "Find it button..." as well as a couple of minor fixes to help MySQL PL support along a bit:

    <pre>
    create procedure spro2(IN inarg1 INT, IN inarg2 INT, OUT outarg INT)
    begin
    set outarg = inarg1 + inarg2;
    end;
    </pre>
    OK, to get the result from that, given 2 input fields in Kettle: one and two, both Integer, you use these settings:

    [*]proc-name: spro2[*]result-name: [*]parameter: one / IN / Integer[*]parameter: two / IN / Integer[*]parameter: sum/ OUT / Integer





    That's it. This should work in all it's simplicity, just like it did for other database platforms like Oracle.



    I'm asking everyone to be a bit patient though because there are still bugs being fixed like this one: MySQL bug #15777



    All the best,



    Matt

  2. #2
    Join Date
    May 2014
    Posts
    4

    Default Retrieve OUT parameter from MYSQL stored procedure to stream

    Quote Originally Posted by MattCasters View Post
    MYSQL STORED PROCEDURES EXAMPLE
    So because a certain Kettle fan kept spamming this forum, I'm sending you an example of how to use stored procedures on MySQL.

    Please note that procedure support on MySQL is still very young and a couple of issues are to be found in even the latest stable version of the JDBC driver. Therefor, do NOT use the example posted earlier on this forum by this certain Kettle fan, but stick to normal/simple MySQL procedure code.



    Here is an example, use the latest version of Kettle as this one has the latest JDBC driver as well as code for the "Find it button..." as well as a couple of minor fixes to help MySQL PL support along a bit:

    <pre>
    create procedure spro2(IN inarg1 INT, IN inarg2 INT, OUT outarg INT)
    begin
    set outarg = inarg1 + inarg2;
    end;
    </pre>
    OK, to get the result from that, given 2 input fields in Kettle: one and two, both Integer, you use these settings:
    [*]proc-name: spro2[*]result-name: [*]parameter: one / IN / Integer[*]parameter: two / IN / Integer[*]parameter: sum/ OUT / Integer





    That's it. This should work in all it's simplicity, just like it did for other database platforms like Oracle.



    I'm asking everyone to be a bit patient though because there are still bugs being fixed like this one: MySQL bug #15777



    All the best,



    Matt
    I can't get the OUT parameter to the stream output. Any work arrounds?

  3. #3
    Join Date
    May 2014
    Posts
    4

    Default

    I can't get the OUT parameter to the stream output. Any work arrounds?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.