Calling RPG from a SQL stored procedure

Discussion in 'DB2' started by Christian Dubé, Oct 27, 2015.

Tags:
  1. Christian Dubé

    Christian Dubé New Member

    For simple RPG calls (I haven't tried one with DS or array) I use SQL stored procedure.

    Create the stored procedure from your favourite SQL command entry

    CREATE PROCEDURE MyLib.MyPGM(
    IN @USR CHAR(10),
    IN @ENV CHAR(10),
    OUT @REC CHAR(10)
    )
    LANGUAGE RPGLE
    EXTERNAL NAME FromLIB.MyPGM
    MODIFIES SQL DATA
    PARAMETER STYLE GENERAL



    Then, from PHP, in a standard procedural way:

    /** Create the connection */
    $options['i5_libl'] = array('MyLib1', MyLib2', MyLib3');
    $db2Connection = db2_connect('*LOCAL', 'MyUser', 'MyPassword', $options);


    /** prepare your statement, '?' are place holders */
    $sql = "call MyLib.MyPGM(?, ?, ?)";
    $stm = db2_prepare($this->connect, $sql);


    if ($stm=== false){
    print "Prepare: " . db2_stmt_error();
    print db2_stmt_errormsg();
    die();
    }


    /* IN parameters */
    $MyUser = 'Christian';
    $MyEnv = 'TEST';

    /* Bind paramters, then call the PGM */
    db2_bind_param($stm, 1, "MyUser", DB2_PARAM_INOUT);
    db2_bind_param($stm, 2, "MyEnv", DB2_PARAM_INOUT);
    db2_bind_param($stm, 3, "UserRecord", DB2_PARAM_INOUT);

    $execute = db2_execute($stm);
    if ($execute === false){
    print "Prepare: " . db2_stmt_error();
    print db2_stmt_errormsg();
    die();
    }

    /* if execution is successful, a variable for each OUT parameter is created */
    var_dump($UserRecord);

    I'll try to post more Object Oriented way later this week.
     
    kingston likes this.

Share This Page