byterock

In part 9 we had a look at ASIC transactions as implemented by DBI now we are going to have a look at getting more back from DBD $sth than just the rows updated or the the record set.

Look Ma no Hands

There are many times when playing with RDBS that we would like to get just a little more back form the DB when we do something. What comes to mind first of is those nasty primary_key values after we do an insert. Most if not all RDBS have some way to get them back at least in the SQL world but how do I do this in DBI.

bind_param_inout

Well the answer is to use the SQL and the bind_param_inout method to get value from the DB. Most mainstream DBD do work I will just use DBD::Oracle as I have the code handy.

AUTOINCREMENT

Oh wait this is Oracle we do not have that type of field and I know there are many many many upset little MySQL and DBIx::Class users that whine like someone just took away their lollypop, when they encounter this!. Well all I can say is Buck up Smarty pants and get yourself a real DB. Oh wait MySQL is owned by Oracle now so I guess they are the same anyway. Soory I am loosing my topic.

Anyway the point being, that without an AUTOINCREMENT field type you where always stuck getting the the primary_key value first before you did your inserts which was a little bit of a resource waste and always a dreadful chore.

Returning Into

Oracle programers have come up with any number of tricks for inserting Primary_keys but the one I am going to show you works really well with the 'bind_param_inout' method.

So given this sql

my $sql=<<'Create_Data';
insert into cr_action_plan
(plan_id,
P_ID,
WHO_CHANGED,
TIME_ENTERED,
ESTIMATED_HOURS,
estimated_mins,
active_flag)
values (CR_PLAN_SEQ.nextval,
:p_p_id,
:p_dba_id,
sysdate,
:p_est_hrs,
:p_est_mins,
1)
returning plan_id
into :p_new_id
Create_Data

we are using 'CR_PLAN_SEQ.nextval,' to put the next sequence value into the primary key and then at the end of the sql we tell it to return 'plan_id' into a col that we will bind to with the

returning plan_id Into :p_new_id*

Now the rest of our code looks like this

my $c=$db->prepare($sql);
$c->bind_param(":p_p_id",$self->get_p_id());
$c->bind_param(":p_est_hrs",$self->get_estimated_hours());
$c->bind_param(":p_dba_id",$self->get_who_changed());
$c->bind_param(":p_est_mins",$self->get_estimated_mins());
my $p_new_id='-1';
$c->bind_param_inout(":p_new_id",\$p_new_id,38);
$c->execute();
$self->set_id($p_new_id);

Note how I used the 'bind_param_inout' works the same way as regular binds except
I send it a reference to my '$p_new_id' scalar not the scalar itself, as well you have to add in some space for memory, in this case 38 bytes which should be big enough. Depending on the DBD you many get an error if the returned data is too big or it may just truncate.

That's about it really the only other point is this little guy also very useful to get values returned from stored procedures bu then again how many perl programmers care about that.