SQL call formatting style

[Christopher Hicks]

On Tue, 25 Mar 2003, Gervase Markham wrote:
> My suggestion is as follows:
> 
>    my ($comma, $separated, $list, $of, $vars, $on, $one, $line) =
>      $dbh->some_longwinded_methodname("SELECT some SQL " .
> 				     "ON multiple lines " .
> 				     "IF necessary " .
> 				     "AND enclosed in quotes " .
> 				     "BECAUSE we all understand them " .
>                                       "WITH placeholders ?, ?, ?",
>                                       undef,
>                                       $placeholder, $variables, $here);
> 
> but obviously it's only a suggestion. The last few lines can be 
> block-shifted to the left if necessary to make more room.
> 
> In contrast, bbaetz recently went for:
> 
>      my ($id,
>          $login,
>          $name,
>          $mybugslink) = $dbh->selectrow_array(qq{SELECT userid,
>                                                         login_name,
>                                                         realname,
>                                                         mybugslink
>                                                    FROM profiles
>                                                   WHERE $cond},
>                                               undef,
>                                               $val);

Having the SQL statement in a scalar makes the $dbh calls much cleaner.  
It also allows you to easily replace extra whitespace with a single space 
if you'd like to do so for logging.

	$sql = qq{ SELECT userid, login_name, realname, mybugslink
			FROM profiles
			WHERE $cond
		};

Having more width for the SQL statement lets you do thinks like

	$sql = qq{ SELECT 
					userid*256 as
				userid, 
					left(login_name,2) as
				login_name, 
					right(realname,10) as
				realname,
					substr(mybugslink,2,20) as
				mybugslink
			FROM profiles
			WHERE $cond
		};

An acceptable alternative should allow for the SQL statement to be built
up across a chunk of code:

	my $sql = "select ";
	$sql .= join(', ', at whatweneed);
	$sql .= " from sometable";

> One of the factors is multi-line strings. Myk: am I correct in saying 
> that you have asked for
> "separate quotes for " .
> "each line of the string"
> because it makes logs look better than
> "one string which runs
>   over multiple lines"?

By putting the SQL in a scalar it's easy to gclean it up before logging
it:

	$logsql = $sql;
	$logsql =~ s/\s+/ /g;

-- 
</chris>

The death of democracy is not likely to be an assassination from ambush. It
will be a slow extinction from apathy, indifference, and undernourishment.
-Robert Maynard Hutchins, educator (1899-1977)