How to write perl function 1

[JackTheRussel]

Hi.

I have program where I have lots of SQL-commands.
Now I would like to write some functions because then I should't write so much code, but I have few problems.

Example. I have function like this.

sub delete_id {
 $sth = $db_conn->prepare($sql_string);
 $sth->execute($id); 
 $sth->finish();
}

And I call it here

    $sql_string="DELETE from table where id=?";
    &delete_id($id);

And It works perfectly.

But how I can use this function again when I have two values like here:

 $sql_string="DELETE from table where id=? AND name=?";
 &delete_id($id, $name);

THIS FUNCTION IS NOT OK ANYMORE

sub delete_id {
  $sth = $db_conn->prepare($sql_string);
  $sth->execute($id); #Missing $name
  $sth->finish();
}

So what can I do ?
Can I use this function again or do I have to
give up for using functions in this case?

 

[ishnid]

Even from the first example you posted, the $id variable being used in your subroutine isn't because it's passed into the subroutine call. In other words, if you just called "delete_id()" (or of course "&delete_id"), you'd get the same effect.

When you pass variables into a subroutine, they're contained in the special variable @_. For this kind of sub, I would suggest passing in your SQL string as your first argument and any other parameters after that. Something like this:

delete_id( $first_sql_string, $first_id );
delete_id( $second_sql_string, $second_id, $name );

sub delete_id {
   my ( $sql_string, @parameters ) = @_;
   $sth = $db_conn->prepare($sql_string);
   $sth->execute( @parameters );
   $sth->finish();
}

 

[JackTheRussel]

Thank you very much ishnid!

This was very useful information for me.

 

[spookie]

ishnid,
Excellent information about how to dynamically execute queries. Have a star.


--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[MillerH]

Good info ishnid. There are two enchancements that I would strongly suggest that anyone using this type of utility function employ: error checking and passing return values.

[COLOR=blue]use Carp ();[/color]

delete_id( $first_sql_string, $first_id );
delete_id( $second_sql_string, $second_id, $name );

sub delete_id {
   my ( $sql_string, @parameters ) = @_;
   $sth = $db_conn->prepare($sql_string);
   [COLOR=blue]my $count = [/color]$sth->execute( @parameters )[COLOR=blue] or Carp::croak $db_conn->errstr[/color];
   $sth->finish(); [COLOR=blue]undef $sth;[/color]
   [COLOR=blue]return $count;[/color]
}

This will die on any errors from the caller's perspective, and this will also return the number of rows deleted, updated, or inserted depending on the type of query that you perform.