byterock

In part 10 we had a look at a look at the $sth 'bind_param_inout' method to get something extra from an DBI query. This time we will stick with this but look at another common use at was hinted at in the last post.

Store This!!

Unlike many perl programmers I think SPs are a great. The encapsulate biz-rules really well, in the old days they ran faster, are easier to maintain and they can protect you when the Boss tells you we want to move over to 'Elixir' front end with a 'D' back end.

Calling all Cars

Calling an SP with DBI is quiet strait forward, though implementation is different between RDBMS languages. Some require some sort keyword others only let you run an SP inside their own procedural language.

I will use a calls to Oracle SPs for our example, and it is RDBMS that requires you to run an SP inside its own PL/SQL language. It is simple enough as all one does it to just wrap the SP call between the 'BEGIN' and 'END' keywords, so in essence you are calling an SP from an SP.

So with DBD::Oracle it is as simple as this;

$sth = $dbh->prepare("BEGIN fire_employee(:p_employee_id); END;");
$sth->bind_param(':p_employee_id', 101);
$sth->execute();

or simpler still this;

$dbh->do("BEGIN fire_employee(?); END;",101);

and poor 101 is off to job.perl.org and hoping that Ask has some goodies for her.

In this case, there is only one input parameter in the procedure, so we need only bind our value to it.

Are We There Yet??

Lets try an SP that returns something. So lets say we have been tasked to put this little bit of Biz logic 'How long is it till my superannuation date' (yeah right like any of us tail end Boomers are going to get a pension) up on our company's intranet.

Fortunately one of our very kind DBAs has given us a nice little SP called 'get_employee_SA_date(employee_id,sa_date)' . It takes the first param 'employee_id' and then puts the 'date' into the second param 'sa_date', Oh how lovely, this SP takes into account, such silly things as start date, probational period, paternal leave, sick leave, etc etc, and the bonus is, if it is wrong, we can blame the DBA who wrote it.

So like our earlier examples all we do is;

$in_employee_id = 100;
my $sa_date;
$sth = $dbh->prepare("BEGIN get_employee_SA_date(:p_employee_id, :p_sa_date);
END;");
$sth->bind_param(':p_employee_id', $in_employee_id );
$sth->bind_param_inout(":p_sa_date", \$sa_date, "SQL_NUMERIC");
$sth->execute();

and then we can go and print out '$sa_date' on our web page.

You might have noticed that in this example, SQL_NUMERIC was added to bind_param_inout() call rather than an arbitrary size of say 24. DBI does define a number of 'Constants' you can import. Most of the DBDs support them. I could of used 'SQL_DATETIME_SUB' here as well and it would of most likely worked.

Perl is of course a loosely-typed language, so some times one has to explicitly tell the DBD driver what type of variable is being passed from DBI to the DBD at then onto the RDBMS and then back up. The vast majority or times we don't really care as most DBDs can figure things out on their own or at least guess at it.

In this case DBD::Oracle uses a 'C client' which perl talks to with 'XS' and both 'XS' and 'C' have to know how much memory to reserve for our the returned value before the code is run.