Its going to be my code at fault, and I'm hoping that posting here will work a bit of magic and let me sus the answer as it often does. In case it doesn't suggestions welcome.

I'm modifying the email subsystem. One of my checks is to see if the email is sent by someone for whom I have an email address on file. There are three places to check.

Whatever is the first result persists ie if its an email address that isn't on the system I get -1 and that sticks, if its one on the system I get eg 1007703 and that then sticks.

This is run in a thread with full isolation of all sessions, databases,  tables, queries and variables.

This is really bollixing up my email handling and driving me even further round the bend than I normally am.

All the bumph is below

Roy Lambert

SQL

Whites := 'SELECT';
Whites := Whites + ' IF(EXISTS (SELECT _fkContacts FROM Career WHERE _EMail = :eddress)';
Whites := Whites + ' THEN (SELECT _fkContacts FROM Career WHERE _EMail = :eddress)';
Whites := Whites + ' ELSE';
Whites := Whites + ' IF(EXISTS (SELECT _ID FROM Contacts WHERE _HomeEMail = :eddress OR (_OtherEddresses IS NOT NULL AND LineMatch(_OtherEddresses, :eddress,''Y'')))';
Whites := Whites + ' THEN (SELECT _ID FROM Contacts WHERE _HomeEMail = :eddress OR (_OtherEddresses IS NOT NULL AND LineMatch(_OtherEddresses, :eddress,''Y'')))';
Whites := Whites + ' ELSE';
Whites := Whites + ' IF (EXISTS (SELECT _fkContacts FROM Career WHERE _SecEMail = :eddress)';
Whites := Whites + ' THEN (SELECT _fkContacts FROM Career WHERE _SecEMail = :eddress)';
Whites := Whites + ' ELSE -1';
Whites := Whites + ' ))) AS ContactID FROM EmptyTable';

The function I use to apply the check is

function TemReceiveBase.FetchContactID(const emAddress: string): boolean;
begin
if iWhitelistID.Prepared then begin
iWhitelistID.Close;
iWhitelistID.ParamByName('eddress').AsString := emAddress;
iWhitelistID.ExecSQL;
iContactID := iWhitelistID.FieldByName('ContactID').AsInteger;
Result := iContactID > 0;
end else begin
// should never get here, but just in case
iContactID := -1;
Result := True;
end;
end;
The UDF LineMatch is
function LineMatch(const FldStr, CompStr, CI, Mode: string): boolean;
var
sl: TStringList;
LineNo: integer;
begin
{
Tests to see if a string is a line in a memo field - string fields are considered to be single line memo fields
CI Yes/Y/No/N
Mode blank or not/NOT
}
Result := False;
if FldStr = '' then begin
Result := False;
Exit;
end else if CompStr = '' then begin
Result := True;
Exit;
end;
sl := TStringList.Create;
sl.Text := FldStr;
LineNo := sl.IndexOf(CompStr);
if LineNo > -1 then begin
if (CI = '') or (UpperCase(CI) = 'Y')
then Result := True
else Result := CompStr = sl[LineNo];
end;
sl.Free;
if (Mode <> '') and (Pos('not', LowerCase(Mode)) > 0) then Result := not Result;
end;

I've found a workround - unprepare and re-prepare the query and it works - but that shouldn't be needed.

Roy Lambert

I pulled the code out of the thread, and ran it with nothing else in its own little application. Works if I unprepare the query each time but not if I don't. I think I'm confusing the poor little parser and it can't figure out it needs to do full rerun rather than just returning the result it already has.


Roy Lambert

Roy,

<< I pulled the code out of the thread, and ran it with nothing else in its own little application. Works if I unprepare the query each time but not if I don't. I think I'm confusing the poor little parser and it can't figure out it needs to do full rerun rather than just returning the result it already has. >>

Email me the database files (catalog and relevant table files) along with the query, and I'll take a look.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Email me the database files (catalog and relevant table files) along with the query, and I'll take a look.

It'll probably be after the weekend since I'll need to reduce the tables in size.

The original idea was to allow me to store some application specific sql in a table and load that into the email subsystem as appropriate (eg a domestic version wouldn't have a secretary email address associated with a contact). What I've done now is have a virtual function in the base class which is overridden by a specific implementation. For TfR (my recruitment app) I split the test into three queries:

function TemReceive.FetchContactID(const emAddress: string; var IDNumber: integer): boolean;
var
interim: integer;
function DoTest(whichQry: TEDBQuery): integer;
begin
 whichQry.Close;
 whichQry.ParamByName('eddress').AsString := emAddress;
 whichQry.ExecSQL;
 if whichQry.RecordCount > 0 then Result := whichQry.FieldByName('ContactID').AsInteger else Result := -1;
end;
begin
IDNumber := -1;
Result := False;
interim := DoTest(iWhiteChk1);
if interim > 0 then begin
 IDNumber := interim;
 Result := True;
end else begin
 interim := DoTest(iWhiteChk2);
 if interim > 0 then begin
  IDNumber := interim;
  Result := True;
 end else begin
  interim := DoTest(iWhiteChk3);
  if interim > 0 then begin
   IDNumber := interim;
   Result := True;
  end;
 end;
end;
end;

Roy Lambert