Re: where clause

[Chuck Fox]

Maybe my MSSql is a little rusty but isn't the wildcard symbol the % and 
not the *  ?

Chuck

[EMAIL PROTECTED] wrote:

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');