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

So last time I asked this question all I got was commits on how my perl code was not “proper” (although I thought one of the nice things about perl was I could write it how it looked best to me and it still work). So this time around I am hoping to get help with my problem and not just a bunch of people telling me to change the way I program.
Here is the problem. I have a small script that connects to a MS SQL database, runs a view, and displays the result. When I run the view in the SQL management studio a valid result is always shown and when looking directly at the data in table the view pulls from there is no blank rows. But when the view is called via the perl script a blank row will sometimes be pulled. So can anyone look at my code and tell me what in it would be causing a blank row to be pulls.
SQL Code: select top 1 Quote, CharacterName, GameTiel from dbo.GameQuotes order by newid()
Perl Code: 
#!C:\Perl\bin\perl.exe
use strict;
use DBI;
use CGI::Carp qq~fatalsToBrowser~;
print "Content-type: text/html\n\n";
my ($DBH, $STH, @Quote, $Statement);
$Statement = qq~select Quote, CharacterName, GameTitle from Finaldbo.R
+andomQuote~;
$DBH = DBI -> connect('dbi:ODBC:SQLServer', '', '') or die "$DBI::errs
+tr";
@Quote = $DBH -> selectrow_array($Statement);
print qq~$Quote[0]
<br />--$Quote[1]
<br />$Quote[2]~;
$DBH -> disconnect;

[download]

Replies are listed 'Best First'.
Re: Let's try this again. Script pulling mysterious blank row.
by CountZero (Bishop) on Jun 14, 2009 at 18:50 UTC
    Trying the direct link to your script, gives sometimes the random quote as expected and sometimes an error:
    Software error:
[Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-
+01004) at D:\FinalFantasyInfo\cgi-bin\rnd-quote.pl line 10.

    [download]
    Definitely a database or ODBC-error, nothing to do with Perl.

    If you had tested the $DBH -> selectrow_array($Statement); for errors, you would have seen this yourself.

    Here is a discussion of this error and how to fix it. Not having access to an SQL-server I cannot test it. For those interested, it has to do with the ODBC-driver apparently truncating fields that are too long (above the LongReadLen value and unless LongTruncOK == True the driver reports this as an error.

    Update: Well on second sight, it is probably a combination of DBI, DBD::ODBC and SQL-Server that causes this weird behaviour. So Perl is to blame, ... a bit ... a very little bit. ;-)

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
[d/l]
[select]
Re: Let's try this again. Script pulling mysterious blank row.
by Your Mother (Archbishop) on Jun 14, 2009 at 17:26 UTC

    You can write it however you like. My follow-up on the other thread was only because you seemed to not understand any of morgon's suggestions. :)

    I think the answer to your question is what I just suggested on the other thread: there is no Perl reason you'd get an empty row; this is an SQL/data issue.

    The other possibility is what anonymonk above and I on the other thread suggested and told you how to diagnose. Your quote contains wonky HTML, comment, or script tags or something and it's eating the display.

[reply]
Re: Let's try this again. Script pulling mysterious blank row.
by dsheroh (Monsignor) on Jun 14, 2009 at 18:11 UTC
    If you also use warnings, you will get a warning about use of uninitialized values in concatenation if @Quote comes back undefined, which has already been suggested (more than once) as a possible cause of your problems.

    Another avenue you could try would be to also select your table's primary key and print that first. Since it's a primary key, you know it's populated in every record; if it comes up blank, then the database isn't giving you a record. If it displays only the key, then you know exactly which record is having problems, since the primary key uniquely identifies each record. And printing it first safeguards you against any possible unfriendly data in other fields somehow hiding it (assuming it's a typical autonumber-type key rather than a text string).

[reply]
[d/l]
[select]
Re: Let's try this again. Script pulling mysterious blank row.
by Anonymous Monk on Jun 14, 2009 at 16:42 UTC
    I don't know whats in your database, but it could be html, for example <!-- you won't see this in your browser --> I reccomend you use 
    use CGI;
print join '<br />', map { CGI->escapeHTML($_) } @Quote;

    [download]
    So last time I asked this question all I got was commits on how my perl code was not “proper”

    You gave the world commit permission? I would change that quick :p

[reply]
[d/l]
[select]
Re: Let's try this again. Script pulling mysterious blank row.
by Anonymous Monk on Jun 14, 2009 at 17:02 UTC
    In that other thread, first suggestion was to check @Quote for defined-ness. , you should try that
[reply]
      Checking if an array is undefined makes no sense. Checking if selectrow_array resulted in an error would be useful, though.
[reply]
[d/l]

Back to Seekers of Perl Wisdom