mysql procedures with inout params

bidbrooken · #1 (**permalink**) 07-15-09, 11:00

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

sco08y · #2 (**permalink**) 07-15-09, 23:20

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.)

bidbrooken · #3 (**permalink**) 07-16-09, 04:00

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

mtravel13 · #4 (**permalink**) 11-14-09, 09:55

what exactly the trouble with while loop , i guess if whole thing works then above condition should be ok