Just a guess as I have no experience with MS SQL Server,
but LIKE clauses, at least in some databases (like Oracle)
use the percent-sign(%) as the wildcard character. You
might try the percent-sign instead of the asterisk as your
wildcard in the LIKE.
HTH.
--
Hardy Merrill
Red Hat, Inc.
David McDivitt [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');
