Last Login Date/time

[swest]

I am trying to record into a database field the last time someone logged into my site. What code do you put in that recognizes the user that logged in and write the date/time to a SQL database field?

[Sparticuz]

Re: Last Login Date/time

I have my login component forward to login.a5w, and that page has xbasic that logs to my log table. (So many logs!) Then login.a5w forwards back to index.a5w

[swest]

Re: Last Login Date/time

Will you post the code you are using? Are you writing to a text file or into a database?

[Sparticuz]

Re: Last Login Date/time

This isn't all of it, but it would be the part that matters

'0: Initialize connections
dim globalcn as sql::connection
flag = globalcn.open("::name::global")
if flag == .f. then
	write2log("Connect to DB",globalcn.CallResult.text)
end if

'Set up this session variable for style
session.style = ""
Session.loggedin = .t.

'1: Log entry into system
dim sql as c
dim args as sql::arguments
args.add("ulink", session.__protected__ulink)
args.add("region_fk", session.regionnum)
args.add("event", "Login")
args.add("user_agent", request.UserAgent)
args.add("ip", request.remote_addr)
sql = "INSERT INTO gen_log (ulink, region_fk, event, user_agent, ip) VALUES (:ulink, :region_fk, :event, :user_agent, :ip)"
flag = globalcn.Execute(sql, args)

'x: Clean up
globalcn.FreeResult()
globalcn.Close()
delete globalcn

'Fin: Redirect to index page after everything is set
Response.Redirect("index.a5w")

EDIT: I actually use a custom function write2log() instead of this, but this is what I used to do.

[swest]

Re: Last Login Date/time

I only need to record last login date and time. In your code where are you doing this? Is it ("event", "login") and ("user_agent", request.useragent)? So the first one is recording the login event and the useragent recording the date into sql?

[Sparticuz]

Re: Last Login Date/time

That whole area is logging the login in sql.
I am recording:
ulink
region_fk (this is specific to my database)
event (Since this table is used for other events as well, I set it to "Login")
user_agent is the user agent of the browser they are logging in from
ip (IP Address of the user, Be careful with this one, my app is private so I don't worry about it. But there are certain countries where it is illegal to capture this type of information without notifying the user).

All my tables have a field ModifiedDate which is a timestamp and is set to auto update when the record is inserted or changed. You would probably just want to add a field called time or something similar and have now() written in the sql statement for that value. My code isn't necessarily storing the datetime field explicitly.

[swest]

Re: Last Login Date/time

Can I set sql variables on a5w pages? So if I say "declare @date as datetime" then set "@date = now()" then have sql update to set last login field to @date? If so what is the code, what I have tried is returing an error and the page wont run

[Sparticuz]

Re: Last Login Date/time

cn.execute("UPDATE user_table SET last_login_time = now() WHERE ulink = " + session.__protected__ulink)

That should be all you really need. No need to declare a sql variable.

[JimDK]

Re: Last Login Date/time

I am using an a5w page between the login page and the initial page., the code in the a5w page is like this:

cn.open("::Name::Trade")
'
' Just to ensure the AEX is current
'
Server.ClearAEXCache()
'
session.norway = ""
currentusername = a5ws_getcurrentuser()
group_list = A5WS_Get_User_Assignments(currentusername)
group_list = crlf_to_comma(group_list)
if inlist2("XXXXX",group_list)
session.XXXXX = "Y"
else
session.XXXXX ="N"
end if

IF currentusername <> ""
args.set("uname",currentusername)
session.initial = sql_lookup(cn,"registeredusers","username=:uname","userinit",args)

IF session.initial <>""
dim fname as c
dim lname as c
fname = sql_lookup(cn,"registeredusers","username=:uname","firstname",args)
lname = sql_lookup(cn,"registeredusers","username=:uname","lastname",args)
session.fullname = alltrim(fname) + " " + alltrim(lname)
session.username = currentusername
setlogintime(currentusername)
END IF
END IF
cn.Close()
Response.Redirect("XXXXXXXX.a5w")

the setlogintime function is like this:

FUNCTION SetLoginTime AS V (Umail AS C )

DIM cn as SQL::Connection
DIM args as sql::arguments
dim sqlUpdateStatement as c
dim flagResult as l

flagResult = cn.open("::Name::Trade")
if flagResult = .f. then
end
end if

args.add("New__LASTLOGIN",now())
args.set("Usermail",Umail)

sqlUpdateStatement = <<%txt%
UPDATE registeredusers SET LASTLOGIN = :New__LASTLOGIN WHERE USERNAME = :Usermail
%txt%
cn.Execute(sqlUpdateStatement,args)

END FUNCTION

[Sparticuz]

Re: Last Login Date/time

I don't see anything wrong with that, but I don't think it needs to be it's own function, you are already opening the connection on the a5w page, why do it again? You should be able to replace that function with this one line of code.

cn.execute("UPDATE registeredusers SET LASTLOGIN = now() WHERE USERNAME = " + currentusername)

or for speed:

cn.execute(*concat("UPDATE registeredusers SET LASTLOGIN = now() WHERE USERNAME = ",currentusername))

EDIT: oh, lol, I though you were OP

[swest]

Re: Last Login Date/time

This is what I have now, I am getting an error and the page wont even run, when I comment out the cn.execute the page will run

'To capture Last Login date and time
'0: Initialize connections
dim globalcn as sql::connection
flag = globalcn.open("::name::XXXX")

'1: Log entry into system
dim sql as c
dim args as sql::arguments
CurrentUserName = a5ws_getcurrentuser() 'CurrentUserName is email_address
cn.execute("UPDATE table_name SET last_login = now() where email_address = CurrentUserName")
flag = globalcn.Execute(sql, args)

'x: Clean u
globalcn.FreeResult()
globalcn.Close()
delete globalcn

[Sparticuz]

Re: Last Login Date/time

It's because your sql statement is incorrect.

dim args as sql::arguments
args.add("CurrentUserName",a5ws_getcurrentuser()) 'CurrentUserName is email_address
flag = globalcn.execute("UPDATE table_name SET last_login = now() where email_address = :CurrentUserName",args)

[swest]

Re: Last Login Date/time

My MSSQL field is not getting updated. I put this statement in sql query analyzer and it saids that now() is not a recongized built in function

UPDATE table_name SET last_login = now() email_address = '[email protected]'


Hopefully when I found out this I can get it to work, thanks for your help

[Sparticuz]

Re: Last Login Date/time

I'm not sure what it is for MSSQL, now() is a mysql function.

Actually, it's getDate()

http://stackoverflow.com/questions/3...-of-mysqls-now

[swest]

Re: Last Login Date/time

That is sql statement

UPDATE table_name SET last_login = now() where email_address = '[email protected]'