Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
SQL in a thread giving persistent result |
Wed, May 25 2016 8:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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; |
Wed, May 25 2016 8:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I've found a workround - unprepare and re-prepare the query and it works - but that shouldn't be needed.
Roy Lambert |
Wed, May 25 2016 10:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 25 2016 11:44 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, May 26 2016 2:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |