in reply to SQL Query

As someone who does a lot of SQL from Perl (with Sybase, not MySQL, but does it matter?), may I suggest a little stylistic sugar. I write all of my SQL as HERE docs, rather than attempting to quote them with any of the myriad ways Perl has.

$sql = <<"__SQL__";  # treats it as if double quoted
CREATE TABLE colors (
   color varchar(60) default NULL
)
TYPE=MyISAM;
__SQL__

[download]

After awhile, your eyes will get used to picking out the HERE doc sections (since they are not indented with the Perl code).

This has nothing to do with solving your problem, exactly, but as with keeping consistent about indentation and other such seemingly mindless window dressings, the structure it adds help keep the Perl and the SQL visually segregated, hopefully so syntax errors will stand out.

Also, why are you calling prepare and execute if you have no need for placeholders? Just use $dbh->do($sql).

BTW, die makes print unnecessary, you don't need (and shouldn't use) both together.

dmm

You can give a man a fish and feed him for a day ...
Or, you can teach him to fish and feed him for a lifetime

Replies are listed 'Best First'.
(jeffa) 2Re: SQL Query
by jeffa (Bishop) on Jan 11, 2002 at 07:58 UTC
    Why type all that extra here-doc stuff? 
    $sql = '
CREATE TABLE colors (
   color varchar(60) default NULL
)
TYPE=MyISAM
';

    [download]
    And this looks even better if you don't need to store the SQL for later: 
    $dbh->do('
   CREATE TABLE colors (
      color varchar(60) default NULL
   )
   TYPE=MyISAM
');

    [download]
    Here docs are nice, but overkill in this situation since the database could care less how it 'appears'. Just so long as it is valid syntax.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
[reply]
[d/l]
[select]
      I think that the here doc is a good solution in this case. If not, then I can think of absolutely no situation where it would be a good solution. You avoid quoting issues (both ' and " are valid quotes in sql, so why restict yourself to just one because you chose to open your string with one or the other?) and save yourself a headache when trying to debug.

      thor
[reply]
        I didn't say that a here doc is a _bad_ solution, just too much of a solution. Because i use placeholders ( see bind values), i don't need inner quotes, but there is nothing stopping you from using placeholders inside a here document, either. It really is just a matter of style.

        A good use for here docs is for user display, and i don't mean debugging. But since i discovered the wonderful world of templating, i haven't used a here doc in a long, long time.

        I rather like this quick and dirty method of encapsulating a SQL query, i really think that here docs add line noise: 

        sub get_artists {

   my $artist = shift() . '%';

   return $dbh->selectcol_arrayref('
      select name 
      from artist where 
      name like ?
   ',undef,$artist);
}

        [download]
        All i need is a pair of single quotes. But, this week taught me a pretty important lesson - there really are CPAN modules that take away having to even worry about such issues ... check out the replies by princepawn and autarch at Save MP3 Tag's to Database with SPOPS. (please note that SPOPS itself is meant for much more than what i presented.)

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
[reply]
[d/l]

      HERE docs neatly avoid any quoting issues. And XEmacs colors 'em different so they REALLY stand out :)

      dmm

[reply]
        No they don't. BIND VARS neatly takes care of quoting issues: 
        my $sth = $dbh->prepare('
   insert into foo(bar,baz)
   values (?,?)
');
$sth->execute($foo,$bar);

        [download]

        Here docs would clutter this. Syntax highlighters should treat here docs and strings the same, my gvim highlighter does ...

        NOT! (update) Sorry, the gvim syntax highlighter tends to treat the characters inside a here document as perl code, while strings are one color except for interpolated variables and escaped characters. But anyhow...

        Save yourself the carpel tunnel. ;)

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

        <update>

        Thanks gmax! I have 'posted' the instructions at my perlmonks site: http://jeffa.perlmonk.org/vi/highlight_embed.html.

        </update>

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom