write2log xbasic function

**Sparticuz** · 08-14-2012, 12:55 PM

Hey all, I've written a basic sql error logging/debugging xbasic function. Basically, it lets me call write2log("Error","This is the error") from anywhere in xbasic. It will then insert a record in a logging table in my database.
It includes the following information:
1) The Ulink of the user that is logged in.
2) The pagename or scriptname that the call occured on or in.
3)The User agent of the browser the user was using
4) The IP Address of the user (check local laws if you are allowed to capture this info)

Code:

'Date Created: 27-Jun-2011 01:34:31 PM
'Last Updated: 23-Jul-2012 01:07:14 PM
'Created By  : Sparticuz
'Updated By  : Sparticuz
FUNCTION write2log as v ( event as c, error as c = "" )


    dim cn as SQL::Connection
    dim flag as l 
    flag = cn.open("")
    if flag == .f. then 
        goto static_error
    end if 


    dim args as sql::arguments
    
    if variable_exists("Session") then
        if variable_exists("Session.__protected__ulink") then
            args.add("ulink",Session.__protected__ulink)
        else
            args.add("ulink","Not Logged In")
        end if
    else
        args.add("ulink","localhost")
    end if
    
    args.add("event",event)
    args.add("desc",error)
    
    if variable_exists("Request") then
        if variable_exists("Request.Variables.__pageName") then
            args.add("cur_page",Request.Variables.__pageName)
        else
            args.add("cur_page",Request.Script_name)
        end if
        args.add("user_agent",Request.UserAgent)
        args.add("ip",Request.Remote_Addr)
    else
        args.add("cur_page","localhost")
        args.add("user_agent","localhost")
        args.add("ip","127.0.0.1")
    end if
    
    dim sql as c
    sql = "INSERT INTO gen_log (`ulink`, `event`, `desc`, `cur_page`, `user_agent`, `ip`) VALUES (:ulink,:event,:desc,:cur_page,:user_agent,INET_ATON(:ip));"    
    flag = cn.Execute(sql,args)
    if flag == .f. then
        goto static_error
    end if
    
    cn.FreeResult()
    cn.Close()
    delete flag
    delete cn
    delete sql
    delete args
    end 
    
    static_error:
        'The log entry in the DB has failed.
        'Lets try a static text file instead
        'TODO
    end
    
END FUNCTION

Then the code for the table (mysql):

Code:

CREATE TABLE `gen_log` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`ulink` CHAR(36) NULL DEFAULT NULL,`event` VARCHAR(50) NULL DEFAULT NULL,`desc` TEXT NULL,`cur_page` VARCHAR(50) NULL DEFAULT NULL,`user_agent` VARCHAR(255) NULL DEFAULT NULL,`ip` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Use INET_ATON("ip") to convert to int and use INET_NTOA(num) to convert to ip.',`modified_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`))COMMENT='Table used for logging purposes'COLLATE='latin1_swedish_ci'ENGINE=InnoDB;

**Peter.Greulich** · 08-15-2012, 03:18 PM

Not to take anything away from Sparticuz, but here is one I wrote recently:

Code:

'Date Created: 05-Jul-2012 11:00:02 AM
'Last Updated: 05-Jul-2012 01:35:27 PM
'Created By  : pgreulich
'Updated By  : pgreulich

FUNCTION f_ErrorGet AS C (locVars AS P, vObject="" as C )
''use in script as f_ErrorGet(local_variables()) 
''or, e.g. as f_ErrorGet(local_variables(), "PUB_Users_A dialog") 

Dim errDateTime as T
Dim errScript as C
Dim errCode as N
Dim errLine as N
Dim errMsg as C

errDateTime = now()
errObject = vObject
errScript = error_script_get()
errCode = error_code_get()
errLine = error_line_number_get()
errMsg = error_text_get()
''---------------------------------------------
Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments

args.add("errDateTime",errDateTime)
args.add("errObject",vObject)
args.add("errScript",errScript)
args.add("errCode",errCode)
args.add("errLine",errLine)
args.add("errMsg",errMsg)

vArgs = "(:errDateTime, :errObject, :errScript, :errCode, :errLine, :errMsg)"
vFields = "(errDateTime, errObject, errScript, errCode, errLine, errMsg)"

SQL = "INSERT INTO Error_Log " + vFields + " VALUES " + vArgs

conn.open("::Name::System")							
flag = conn.execute(SQL, args)
conn.close()

f_ErrorGet = errMsg
'f_ErrorGet = conn.callResult.text

END FUNCTION

It is used in a script like this:

Code:

ON ERROR GOTO ERROR_HANDLER

'xbasic code...

flag = conn.execute(SQL, args)

If flag = .f.
	vMessage = "Unable to create new account."
	vErrorText = conn.callResult.text
	error_generate(vMessage + " SQL: " + vErrorText)
End if

'xbasic code...

END
''=============================================================
ERROR_HANDLER:
f_ErrorGet(local_variables(),"PUB_ConfirmAccount.a5w") 

vHelpMsg = f_HelpGet("PUB_ConfirmAccount.a5w_ERROR")

errLine = ltrim(str(error_line_number_get()))
session.message = "<br><br>System Error " + errLine + "<br>" + vMessage + "<br>" + vHelpMsg
response.redirect("PUB_Message.a5w")
End

Or other variations thereof.

Your mileage may vary!

Note: needless to say, this is for the web.

**Sparticuz** · 08-15-2012, 03:27 PM

This is great. Mine is more for logging, whereas yours is more for error catching and alerting. I was eventually also going to add in line numbers and more for error catching/alerting too. (Maybe email notification for errors as well). I also use mine for user tracking. i.e. write2log("Login", :ulink)

**Peter.Greulich** · 08-15-2012, 03:37 PM

Yeah, the more the merrier! I use Alpha's built in security which has an option for a UDF that fires on login/logout and captures IP address etc and I send that to a login table. That works real well. I also have a log table and a function to capture certain script events and milestones - some of which will fire after hours. That way the system manager know if something failed that shouldn't have/or if it succeeded.

**Sparticuz** · 08-16-2012, 09:36 AM

I didn't even know there was a UDF for login/logout. I set up my security in v10 and have really, never looked at it again. Does it fire on all logouts? Or just when logout is called? (i.e. Does it fire on-user closes browser, session times out on server and never 'logs' out)