Prev: FAQ 4.16 How can I find the Julian Day?
Next: Call for papers: SETP-10, Orlando, USA, July 2010
From: Henry Law on 21 Feb 2010 08:27
(I'm sure Google should have been able to help me with this but
structuring a suitable query defeated me... a million hits or none.)

I'm inserting new records into a MYSQL database using Perl DBI. There
are fourteen fields, each one of which has its corresponding variable in
my program. I want to execute the "bind_param" method for each one of
them. If you're not familiar with DBI here's an example of what I might
code:
$sth->bind_param( 1, $message_id );
$sth->bind_param( 2, $from_name );
...
$sth->bind_param( 14, $body );

Ugly. So I have a list of those variable names, thus
my @variable_name_list = ( '$message_id', '$from_name', ...
and I have a loop with a counter, like this
for ( my $i = 1; $i<=14; $i++ ) {
eval{ $ins_sth->bind_param( $i, $variable_name_list[$i-1] ) };
}

.... but the database is filling up with fields containing the literal
name of my variables:

mysql> SELECT message_id FROM mailitem;
+-------------+
| message_id |
+-------------+
| $message_id |
+-------------+

I know that it's to do with when the variable name "becomes" the
variable value, but I've tried multiple combinations of $, \$, and
nested 'eval' and can't hit it. Can someone help me work out how to do
this? Yes, I could have coded fourteen statements and had it working by
now, but it's a matter of elegance!

--

Henry Law Manchester, England
From: Dr.Ruud on 21 Feb 2010 09:08
Henry Law wrote:

> I'm inserting new records into a MYSQL database using Perl DBI. There
> are fourteen fields, each one of which has its corresponding variable in
> my program. I want to execute the "bind_param" method for each one of
> them. [...]

This is in the DBI documentation:

Here�s a more fancy example that binds columns to the values inside a
hash (thanks to H.Merijn Brand):

$sth->execute;
my %row;
$sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
while ($sth->fetch) {
print "$row{region}: $row{sales}\n";
}

Let it inspire you to do your bind_params properly.

--
Ruud
From: Henry Law on 21 Feb 2010 09:13
Dr.Ruud wrote:
> Henry Law wrote:
>
>> I'm inserting new records into a MYSQL database using Perl DBI. There
>> are fourteen fields, each one of which has its corresponding variable
>> in my program. I want to execute the "bind_param" method for each one
>> of them. [...]
>
> Let it inspire you to do your bind_params properly.

Inspired by the good doctor, to whom thanks, and also prompted by the
intellectual exercise of presenting my problem, I've solved it:

for ( my $i = 1; $i<=$field_count; $i++ ) {
$ins_sth->bind_param( $i, eval($variable_name_list[$i-1]));
}

The "bind_param" method simply wants a value.

--

Henry Law Manchester, England
From: Dr.Ruud on 21 Feb 2010 10:04
Henry Law wrote:
> Dr.Ruud wrote:
>> Henry Law wrote:

>>> I'm inserting new records into a MYSQL database using Perl DBI.
>>> There are fourteen fields, each one of which has its corresponding
>>> variable in my program. I want to execute the "bind_param" method
>>> for each one of them. [...]
>>
>> Let it inspire you to do your bind_params properly.
>
> Inspired by the good doctor, to whom thanks, and also prompted by the
> intellectual exercise of presenting my problem, I've solved it:
>
> for ( my $i = 1; $i<=$field_count; $i++ ) {
> $ins_sth->bind_param( $i, eval($variable_name_list[$i-1]));
> }
>
> The "bind_param" method simply wants a value.

Don't even consider eval. Best use a hash.

I would use the actual column names as keys, or use an id2name hash if
needed.

You won't need it anymore in a proper solution, but remember that you
can write
for ( my $i = 1; $i<=$field_count; $i++ )
as
for my $i ( 1 .. $field_count )

--
Ruud
From: Tad McClellan on 21 Feb 2010 10:21
Henry Law <news(a)lawshouse.org> wrote:

> I'm inserting new records into a MYSQL database using Perl DBI. There
> are fourteen fields, each one of which has its corresponding variable in
> my program. I want to execute the "bind_param" method for each one of
> them.

> my @variable_name_list = ( '$message_id', '$from_name', ...
> and I have a loop with a counter, like this
> for ( my $i = 1; $i<=14; $i++ ) {
> eval{ $ins_sth->bind_param( $i, $variable_name_list[$i-1] ) };
> }


You have disguised symrefs as an eval.

Let's try and avoid that...


> ... but the database is filling up with fields containing the literal
> name of my variables:
>
> mysql> SELECT message_id FROM mailitem;
> +-------------+
> | message_id |
> +-------------+
> | $message_id |
> +-------------+
>
> I know that it's to do with when the variable name "becomes" the
> variable value, but I've tried multiple combinations of $, \$, and
> nested 'eval' and can't hit it. Can someone help me work out how to do
> this?


# untested
my $p_num=1;
foreach my $var ( $message_id, $from_name ) {
$ins_sth->bind_param( $p_num++, $var);
}


Look Ma! No eval!

:-)


--
Tad McClellan
email: perl -le "print scalar reverse qq/moc.liamg\100cm.j.dat/"
 |  Next  |  Last
Pages: 1 2
Prev: FAQ 4.16 How can I find the Julian Day?
Next: Call for papers: SETP-10, Orlando, USA, July 2010