where clause

[David McDivitt]

After posting to the newsgroup I thought I should post to the dbi-users mailing list 
instead.

I am unable to get a where clause to work against MS SQL Server. I get no runtime 
errors, but my recordset is always empty. I have
the latest version from ActivePerl and Win2000. I just installed DBI and DBD-ODBC 
using PPM so they should be current. To debug I am
displaying the SQL string, and if I paste the string into a MS Access query or 
Enterprise Manager it works fine. I did trial and
error with everything I could think of changing syntax and had no success. This is my 
first Perl application employing a database,
and is a test case to see what we can do in Perl rather than Java. Code is pasted 
below. I changed my posting width to 132
characters to avoid wrapping but usually post with 76. If someone could help I'd 
appreciate it. Thanks

#!e:\perl\bin\perl.exe

   use warnings;
   use strict;
   use DGM;
   use DBI;
   use DBD::ODBC;
   require 'RecipAuthFunc.pl';

   my $select = 'SELECT TOP 201 AuUnique, AuRIN, AuCaseID, AuFirstName, AuLastName, 
AuMiddleInitial, AuSSN FROM Authorize ';
   my ($connection, $recordset, @field, @record, $filter, $count, $toomany);

   RecipAuthStart(1);   #this creates %passed from CGI arguments, insures there are no 
undefs, and writes the HTML header

$passed{'LName'} = 'W';          #this is so I can skip the selection screen
$passed{'queryby'} = 'select+using+name';

   if ($passed{'queryby'} eq 'select+using+RIN') {$filter = "WHERE AuRIN LIKE 
'$passed{'RIN'}*'"}
   elsif ($passed{'queryby'} eq 'select+using+case') {$filter = "WHERE AuCaseID LIKE 
'$passed{'CaseID'}*'"}
   elsif ($passed{'queryby'} eq 'select+using+name') {$filter = "WHERE (AuFirstName 
LIKE '$passed{'FName'}*') AND ".
                                                                "(AuLastName LIKE 
'$passed{'LName'}*') AND ".
                                                                "(AuMiddleInitial LIKE 
'$passed{'Initial'}*')"}
   elsif ($passed{'queryby'} eq 'select+using+SSN') {$filter = "WHERE AuSSN LIKE 
'$passed{'SSN'}*'"}
   elsif ($passed{'queryby'} eq 'select+using+all') {$filter = "WHERE (AuRIN LIKE 
'$passed{'RIN'}*') AND ".
                                                               "(AuCaseID LIKE 
'$passed{'CaseID'}*') AND ".
                                                               "(AuFirstName LIKE 
'$passed{'FName'}*') AND ".
                                                               "(AuLastName LIKE 
'$passed{'LName'}*') AND ".
                                                               "(AuMiddleInitial LIKE 
'$passed{'Initial'}*') AND ".
                                                               "(AuSSN LIKE 
'$passed{'SSN'}*')"}
   else {problem('Invalid invocation')}

print "<br>".$select.$filter.' ORDER BY AuRIN<br>'; #debug

   $connection = DBI->connect("dbi:ODBC:RATS",'RATSDefault','none') or problem('Cannot 
connect to database');
   $recordset = $connection->prepare($select.$filter.' ORDER BY AuRIN') or 
problem('Cannot prepare SQL statement');
   $recordset->execute() or problem('Cannot create recordset');
   $count = 0;
   while (@field=$recordset->fetchrow_array) {
      $count++;
      foreach (@field) {$_ ='' unless $_}
      push @record, [EMAIL PROTECTED];
      }
#   $count = @record;
print "<br>$count<br>"; #debug
   if ($count > 200) {
      $count = 200;
      $toomany = 'Too many records were returned for the selection criteria.<br>Change 
the criteria and do again.';
      }
   else {$toomany = '<br><br>'}

   print '<font size=1.5 face="Arial"><form name="RecipAuth" action="RecipAuthView.pl" 
method="post">';
   print '<table border=0 cellspacing=4 align=center>';
   print '<tr>',tdfont($hidden),
                tdfont($toomany),'</tr>';
   foreach (0..($count-1)) {
      print '<tr>',tdfont($record[$_][0]).  #this is not the final display version of 
course
                   tdfont($record[$_][1]).
                   tdfont($record[$_][2]).
                   tdfont($record[$_][3]).
                   tdfont($record[$_][4]).
                   tdfont($record[$_][5]).
                   tdfont($record[$_][6]),'</tr>';
     }
   print '</table></form></font></body></html>';
   exit;

This is the displayed SQL statement:

SELECT TOP 201 AuUnique, AuRIN, AuCaseID, AuFirstName, AuLastName, AuMiddleInitial, 
AuSSN FROM Authorize WHERE (AuFirstName LIKE
'*') AND (AuLastName LIKE 'W*') AND (AuMiddleInitial LIKE '*') ORDER BY AuRIN

If I remove the where clause and prepare as follows it returns records:
   $recordset = $connection->prepare($select.' ORDER BY AuRIN') or problem('Cannot 
prepare SQL statement');