passing changing amounts of variables to subroutine

[Mike Solomon]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount

Any ideas

#update Address
my $sql = qq {
UPDATE ADDRESS
SET Address_Line_1 = ?,
Address_Line_2 = ?,
State_Province = '$exData{$id}{county}',
Country = '$exData{$id}{country}',
Zip_Code = '$exData{$id}{postcode}',
City = '$exData{$id}{city}'
WHERE Client_Id = '$Client_Id'
AND Address_Id = 0
AND Address_Line_1 is NULL
};

my $error = SqlExecuteP($sql, $exData{$id}{address1},
$exData{$id}{address2});


sub SqlExecuteP {
#Execute Sql or write error to errr log & return
#expect sql statement
my ($sql , $p1, $p2) = @_;

$g_data = $g_dbh->prepare($sql);
my $rows = $g_data->execute($p1, $p2);

#check for error - if error print error & exit
my $sqlError = DBI::errstr;

if ( $sqlError ) {
$sqlError = $sql . "\n" . $sqlError;
return $sqlError;
}

#OEO = 0 rows
$rows =~ s/0E0/0/;
return $rows;
} #end SqlExecuteP
#-----------------------------------------------------------------------

 

[Paul Lalli]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount

Any ideas

#update Address
my $sql = qq {
UPDATE ADDRESS
SET Address_Line_1 = ?,
Address_Line_2 = ?,
State_Province = '$exData{$id}{county}',
Country = '$exData{$id}{country}',
Zip_Code = '$exData{$id}{postcode}',
City = '$exData{$id}{city}'
WHERE Client_Id = '$Client_Id'
AND Address_Id = 0
AND Address_Line_1 is NULL
};

my $error = SqlExecuteP($sql, $exData{$id}{address1},
$exData{$id}{address2});


sub SqlExecuteP {
#Execute Sql or write error to errr log & return
#expect sql statement
my ($sql , $p1, $p2) = @_;

$g_data = $g_dbh->prepare($sql);
my $rows = $g_data->execute($p1, $p2);

#check for error - if error print error & exit
my $sqlError = DBI::errstr;

if ( $sqlError ) {
$sqlError = $sql . "\n" . $sqlError;
return $sqlError;
}

#OEO = 0 rows
$rows =~ s/0E0/0/;
return $rows;
} #end SqlExecuteP
#-----------------------------------------------------------------------

I might not be understanding your problem... you want to execute your SQL
statement with whatever values are passed in to the subroutine, not
knowing how many will be passed in. Any that are not passed in should
default to a specific value. Is that correct? If so, I would make all
the values in the SQL statement placeholders, and then in the subroutine
do:

my $sql = shift;
my $p[0] = $_[0] or $default_value1;
my $p[1] = $_[1] or $default_value2;
my $p[2] = $_[2] or $default_value3;
#etc ...

my $rows = $g_data->execute(@p);


Paul Lalli

 

[Karlheinz Weindl]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount
[...]

sub SqlExecuteP {
#Execute Sql or write error to errr log & return
#expect sql statement
my ($sql , $p1, $p2) = @_;

my ($sql, @params) = @_;

$g_data = $g_dbh->prepare($sql);
my $rows = $g_data->execute($p1, $p2);

my $rows = $g_data->execute(@params);

Is it that what you're looking for?

Karlheinz

 

[Paul Lalli]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount

I might not be understanding your problem... you want to execute your SQL
statement with whatever values are passed in to the subroutine, not
knowing how many will be passed in. Any that are not passed in should
default to a specific value. Is that correct? If so, I would make all
the values in the SQL statement placeholders, and then in the subroutine
do:

my $sql = shift;
my $p[0] = $_[0] or $default_value1;
my $p[1] = $_[1] or $default_value2;
my $p[2] = $_[2] or $default_value3;
#etc ...

my $rows = $g_data->execute(@p);

Better way of doing what I just said, assuming you have all your defaults
in an array: (untested)

my $sql = shift;
my $rows = $g_data->execute(@_, @defaults[@_..$#defaults]);

Paul Lalli

 

[Tore Aursand]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount

That's because you tell the sub-routine to except only two;

sub SqlExecuteP {
#Execute Sql or write error to errr log & return
#expect sql statement
my ($sql , $p1, $p2) = @_;

This line could rather have been written like this:

my ( $sql, @p ) = @_;

my $rows = $g_data->execute($p1, $p2);

And then:

my $rows = $g_data->execute( @p );

#check for error - if error print error & exit
my $sqlError = DBI::errstr;

if ( $sqlError ) {
$sqlError = $sql . "\n" . $sqlError;
return $sqlError;
}

Why all this fuzz. Isn't it enough with the following?

my $rows = $g_data->execute( @p ) or return $sql . "\n" . DBI::errstr;

#OEO = 0 rows
$rows =~ s/0E0/0/;
return $rows;

Does it really return '0E0' when zero rows returned? Strange. I would
have written this as one line;

return ( $rows =~ m,^\d+$, ) ? $rows : 0;

A matter of taste, I guess.

 

[Mike Solomon]

The following bit of my script passes variables to a subroutine for
use as placeholders to update a database using DBI
As I have written it it works for 2 placeholders but I want it to work
for any amount
[...]

sub SqlExecuteP {
#Execute Sql or write error to errr log & return
#expect sql statement
my ($sql , $p1, $p2) = @_;

my ($sql, @params) = @_;

$g_data = $g_dbh->prepare($sql);
my $rows = $g_data->execute($p1, $p2);

my $rows = $g_data->execute(@params);

Is it that what you're looking for?

Karlheinz

That's exactly what I wanted

I think I looked at it to hard and missed the obvious

Thanks everyone

 

[Paul Lalli]

or just:
return $rows + 0; # works with warnings enabled

Why are you futzing with this return value in the first place? execute()
very intentionally and specifically returns "0E0" if there are zero rows
returned, for very good reasons. It is so you can make the following
kinds of determinations:


$rows = $g_data->execute(@params);

if (!$rows){
print "Error with the SQL execution\n";
} elsif ($rows == 0){
print "No rows were affected\n";
} else {
print "Updated $rows rows\n";
}


In other words, execute() will return a false value on error only. It
returns a true value which evaluates to zero in numeric context if the
execute() succeeded, but no rows were updated. This is what was intended,
and should be how it was used. The way you've changed it, you have no way
of knowing if $error is false because you have a SQL syntax error or if
there were simply no rows that matched your statement.

Paul Lalli

 

[ctcgag]

Why are you futzing with this return value in the first place? execute()
very intentionally and specifically returns "0E0" if there are zero rows
returned, for very good reasons.

Hell, I want to know why he's futzing with a half-assed subroutine that
is nothing but a brain-damaged wrapper around DBI->execute() in the first
place.

Maintaining "abstraction for abstraction's sake" code like this
was the bane of my job, until I just chucked it all.

Xho