Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: mysql procedures with inout params

    trying to use DBI with mysql and a stored proc
    the proc has 14 input and 3 output params
    I can get it to actually do the work but can't get the return codes
    any ideas?

    my $sql = "call PROC_MEMB(?,?,?,?,?,?,?,?,?,?,?,?,?,?,\@code,\@key ,\@map)";
    $RegUser = $DBRegUser->prepare($sql);
    $RegUser->bind_param(1,$action);
    $RegUser->bind_param(2,$user_uid);
    $RegUser->bind_param(3,$locale);
    $RegUser->bind_param(4,$username);
    $RegUser->bind_param(5,$pass);
    $RegUser->bind_param(6,$dob);
    $RegUser->bind_param(7,$gender);
    $RegUser->bind_param(8,$title);
    $RegUser->bind_param(9,$letters);
    $RegUser->bind_param(10,$firstname);
    $RegUser->bind_param(11,$initials);
    $RegUser->bind_param(12,$lastname);
    $RegUser->bind_param(13,$knownas);
    $RegUser->bind_param(14,$suser);
    $RegUser->execute();
    $RegUser->bind_columns(undef, \$code, \$key, \$map);
    $RegUser->fetch();
    print "Code::$code, Key::$key, Map::$map\r";

    I get nothing back - I know they are working correctly as the output params write to a db log table correctly

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    The way to handle output parameters with DBI is bind_param_inout. But the docs don't say if it works and the examples don't even have any parameters. Lame.

    Finally, I dug through the source, and the verdict is:

    Code:
    /***************************************************************************
     *
     *  Name:    dbd_bind_ph
     *
     *  Purpose: Binds a statement value to a parameter
     *
     *  Input:   sth - statement handle
     *           imp_sth - drivers private statement handle data
     *           param - parameter number, counting starts with 1
     *           value - value being inserted for parameter "param"
     *           sql_type - SQL type of the value
     *           attribs - bind parameter attributes, currently this must be
     *               one of the values SQL_CHAR, ...
     *           inout - TRUE, if parameter is an output variable (currently
     *               this is not supported)
     *           maxlen - ???
     *
     *  Returns: TRUE for success, FALSE otherwise
     *
     **************************************************************************/
    (Um, in case it's not clear, no, it's not going to work. Sorry.)

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Thanks - at least In now know I was in a dead end

    Sorted it though

    my $sql = "call PROC_MEMB(?,?,?,?,?,?,?,?,?,?,?,?,?,?,\@code,\@key ,\@map)";
    $RegUser = $DBRegUser->prepare($sql);
    $RegUser->bind_param(1,$action);
    $RegUser->bind_param(2,$user_uid);
    $RegUser->bind_param(3,$locale);
    $RegUser->bind_param(4,$username);
    $RegUser->bind_param(5,$pass);
    $RegUser->bind_param(6,$dob);
    $RegUser->bind_param(7,$gender);
    $RegUser->bind_param(8,$title);
    $RegUser->bind_param(9,$letters);
    $RegUser->bind_param(10,$firstname);
    $RegUser->bind_param(11,$initials);
    $RegUser->bind_param(12,$lastname);
    $RegUser->bind_param(13,$knownas);
    $RegUser->bind_param(14,$suser);
    $RegUser->execute();
    $RegUser = $DBRegUser->prepare("select \@code, \@key, \@map");
    $RegUser->execute();
    my @res=$RegUser->fetchrow_array();
    print "Code::$res[0], Key::$res[1], Map::$res[2]\r";

    Only issue I have with this whole thing was it was wrapped in a while loop was outside so it went really fast. Having to bring it all into the loop as I can only get access the the sp output if I stay on the same handle.

    Not very efficent - maybe when mysql get more mature with their sp's will be able to revisit this - or if the DBI and MySQL DBD actually sort out method of processing @ stuff in sp's

    Thanks again

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    what exactly the trouble with while loop , i guess if whole thing works then above condition should be ok

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •