where clause
From: David McDivitt (x12code_at_yahoo.com)
Date: 10/01/03
- Next message: Tim Bunce: "Re: Q: Any module implementing vendor-independent 'create table...'?"
- Previous message: Gulácsi tamás: "DBD-Oracle fails to install"
- Next in thread: Chuck Fox: "Re: where clause"
- Reply: Chuck Fox: "Re: where clause"
- Reply: Hardy Merrill: "Re: where clause"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: dbi-users@perl.org Date: Wed, 01 Oct 2003 09:15:50 -0500
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, [@field];
}
# $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');
- Next message: Tim Bunce: "Re: Q: Any module implementing vendor-independent 'create table...'?"
- Previous message: Gulácsi tamás: "DBD-Oracle fails to install"
- Next in thread: Chuck Fox: "Re: where clause"
- Reply: Chuck Fox: "Re: where clause"
- Reply: Hardy Merrill: "Re: where clause"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
