Hi William,

Do your self a favour and do not use a timestamp as your primary key.
It is an unnecessary scalability limitation that may come back to bite
you one day. You will then start having to use logic like:

while true do
begin
try
insert 'now';
break;
except
random delay
end
end;

Which uneccessarily complicates the code, slows you down, adds traffic
etc. Generators provide a concurrent-safe method of allocating a
primary key.

Secondly, I think your confusion lies in that you expect the IN
statement to be evaluated into a list of constants, where IN is
actually implemented by being evaluated each row.

The same sort of issue occurs when you try

insert into a
select * from a;

Maybe its the standard, maybe its a bug, either case it doesn't behave
how you want so you need to do it another way.

This is how I would do it:

Create a table

LoginFails
(
Login_Name
Cnt
)

Add an after insert trigger to Contact_Login_Log.

IF (NEW.STATUS = 'FAILED') THEN
BEGIN
-- increment fail count if wrong
UPDATE LOGINFAILS SET CNT = CNT + 1 WHERE LOGIN_NAME = NEW.LOGIN_NAME;
END
ELSE
BEGIN
-- Reset to 0 if successful
UPDATE LOGINFAILS SET CNT = 0 WHERE LOGIN_NAME = NEW.LOGIN_NAME;
END;

You may also need to add a trigger to your user table to insert a
record to loginfails when a user is added.

Now my query would be something like

select cnt
from LoginFails
where Login_Name = 'Joe';

(except I wouldn't use Name as a primary key :)





--- In firebird-support@yahoogroups.com, "William L. Thomson Jr."
<wlt@o...> wrote:

>
> On Wed, 2005-12-28 at 09:56 +1100, Nigel Weeks wrote:
> > > Yeah, I need to also include anoter identifier there like
> > > login_name='joe' had played with that as well, same results.
> > > Here is a corrected version.
> > >
> > > SELECT COUNT(STATUS) FROM CONTACT_LOGIN_LOG
> > > WHERE LOGIN_NAME='joe' AND STATUS='Failed' AND LOGGED IN (
> > > SELECT FIRST 3 LOGGED FROM CONTACT_LOGIN_LOG
> > > WHERE LOGIN_NAME='joe' ORDER BY LOGGED DESC);
> > >
> > > Being that LOGGED is a primary key, I thought having the
> > > login_name was
> > > redundant or unneccessary?
> >
> > Being a primary key in completely irrelevant. You've got three

values in an

> > 'IN' condition. Nothing more than that.
>
> LOGGED in the outer query would equal only those three right? Since they
> are primary keys would make the outer query have the equivalent of 3 x
> LOGGED= conditions? For example if LOGGED where an int, and had values
> of 1,2,3. Would the outer query not be restricted to returning only rows
> with the primary keys of 1,2,3?
>
> Now at the moment there are 6 failed entries for joe. When I run the
> above query I get 6. If I just return the rows with * instead of
> COUNT(). I get all records, not just ones in the inner query.
>
> > Fwar. There's a lot of tests for that! There might be a simpler way...
> > Are you using this for a system that does the following:
> >
> > "Welcome back, mr. <surname>. There have been three(3)

unsuccessful login

> > attempts since you last logged in."
>
> No. More to lock and flag the account, then to notify someone or take
> further action. There will be further logic, this is just to get a
> result for further testing and other conditionals.
>
> Which is partly why a query would be ideal, or in place a stored proc to
> be called.
>
> --
> Sincerely,
> William L. Thomson Jr.
> Obsidian-Studios, Inc.
> http://www.obsidian-studios.com
>