DBI:Mysql sql string multiline error

gottdammer has asked for the wisdom of the Perl Monks concerning the following question:

Hi! Im trying to migrate from an old linux system to a new. I have many sql sentences like:

    my $sql="SELECT * 
             FROM sometable 
             WHERE something";
    $sth=$dbh->prepare($sql);
    $sth->execute;
[download]

The string $sql have many tabular spaces ("\t") to make more readable the sentences. With the old linux is working fine, but instead in new system i have to join the lines like:

    my $sql="SELECT * FROM sometable WHERE something"
[download]

to work properly. What can be wrong?? Wrong file codification?? Sorry for my english..Saludos desde Argentina!

Comment on DBI:Mysql sql string multiline error Select or Download Code

Replies are listed 'Best First'.
Re: DBI:Mysql sql string multiline error by Corion (Patriarch) on Oct 15, 2009 at 07:25 UTC
In theory it shouldn't make a difference whether your SQL contains newlines and tabs ("\t"). What DBD and database (+version) are you using? What error message are you getting? If you want to be sure to remove all those newlines and tabs before passing your SQL to the database, try the following subroutine which converts the tabs and newlines to spaces: `sub clean_sql { my $sql = shift; $sql =~ s/\s+/ /msg; $sql }; # and call it as my $sql = clean_sql("SELECT * FROM sometable ... ");` [download]	[reply] [d/l]
Re: DBI:Mysql sql string multiline error (join ' ') by tye (Sage) on Oct 15, 2009 at 20:09 UTC
I write such things like so: `my $sql= join( ' ', "SELECT *", "FROM sometable", "WHERE something", );` [download] It is a practice that has scaled very well for me for the several years I've been using it. Before that I tried quite a few different styles including here-docs (<<END), sprintf, and several flavors of qq<...\n...>. They all eventually sucked in too many cases. - tye	[reply] [d/l]