in reply to Creating variables while using 'strict'

I could put the data into a Hash but I'm using the data in SQL statements which result in very long, difficult to read, lines.

Using a hash to store the variables could actually make the construction of the SQL statements easier and clearer. If you happen to be building an "insert into my_table ..." sort of statement, a hash makes this very simple to do via a "for" loop -- e.g.: 

...
my $col_spec = my $val_spec = "(";
for my $column ( keys %data )
{
   $col_spec .= "$column,";
   $val_spec .= "$data{$column},";
}
$col_spec =~ s/,$/\)/;
$val_spec =~ s/,$/\)/;

my $sql = "insert into my_table $col_spec values $val_spec";

[download]
and of course, there are shorter ways to do the same thing: 
my $col_spec = join( ",", sort keys %data );
my $val_spec = join( ",", map { $data{$_} } sort keys %data );
my $sql = "insert into my_table ($col_spec) values ($val_spec)";

[download]
Similar methods apply for update and select statements, using a loop to assemble the "col=val" pairs and "where" clause conditions.

If the basic form of the sql statement will always involve the same set of columns and conditions, you should be using the DBI "prepare()" method with parameterized values (using the "?" placeholder, and passing the values as a list to the "execute()" method).

Also, remember that you can use any sort of whitespace as part of an SQL statement, including line-feeds and tabs, so even long constructs can be formatted so that they are not difficult to read.

update: as pointed out in the next reply below, the values being assembled should be quoted if you are not using the parameterized form of sql statement. (Thanks, and ++, blokhead!)

Replies are listed 'Best First'.
Re: Re: Creating variables while using 'strict'
by blokhead (Monsignor) on Jan 06, 2003 at 04:08 UTC
    Always remember to quote data or else use placeholders! ;) 
    
#### added call to $dbh->quote

my $col_spec = join ',', sort keys %data ;
my $val_spec = join ',',
               map  { $dbh->quote( $data{$_} ) }   ### here
               sort keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

#### or with placeholders:

my $col_spec = join ',', sort keys %data;
my $val_spec = join ',', ('?') x keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

$dbh->prepare($sql)
    ->execute( map { $data{$_} } sort keys %data );

    [download]
    Also it might be worth mentioning that the keys of %data should be validated before they are trusted in this code. Cheers.

    blokhead

[reply]
[d/l]
      Actually, there's no real need to sort the keys. And there's definitely no need to sort them more than once: just create an array with them. A hash slice makes the map unnecessary. Here's a modified version of your code: 
      #### or with placeholders:
my $col_spec = join ',', keys %data;
my $val_spec = join ',', ('?') x keys %data;
my $sql      = "insert into my_table ($col_spec) values ($val_spec)";

$dbh->prepare($sql)->execute(@data{keys %data});

      [download]
      In case you want to use the perepared statement more than once, there's no ganratee that keys() will return the keys in the same order over different hashes even though they have the same keys: 
      #### or with placeholders:
my(@col_names, $sth);
{
    local $" = ',';
    @col_names   = keys %data;
    my $val_spec = join ',', ('?') x @col_names;
    my $sql      = "insert into my_table (@col_names) values ($val_spe
+c)";
    $sth = $dbh->prepare($sql);
}

      [download]
      And much later (just make sure $sth and @col_names are still in scope): 
      $sth->execute(@data{@col_names});

      [download]
[reply]
[d/l]
[select]
Re: Re: Creating variables while using 'strict'
by nedals (Deacon) on Jan 06, 2003 at 06:26 UTC
    graff, Being relatively new to Perl, I do not fully understand your response, but I'll figure it out. It does indeed solve the underlying problem of improving the readability of the, later used, SQL statements. I'll also take note of blokhead's response.

    Thank's to both of you. I'll stay with example 4 and use a Hash to gather the data.

[reply]

In Section Seekers of Perl Wisdom