Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation  Back up to About Overview 
  
[+] Expand All
[-] Collapse All

Accounting Stored Procedure Example

A simple stored procedure can return a result set in the same way as a Select statement. For example, assume you have a table with the following fields: username, password, Alias, and active, where all fields have the datatype varchar. You want a stored procedure that will return a password and alias when the username and password received in the request match entries in the database, provided that active field has a value of Yes.

The following example executes a stored procedure to update an accounting table in Steel-Belted Radius Carrier.

  1. Create an accounting table by executing the following command:
    create table accounting(TransactionDate varchar(20), Username varchar(21), SessionID varchar(12), NASIPAddr varchar(15), NASPort varchar(5), UserIPAddr varchar(15), CallingNum varchar(12), CalledNum varchar(12),
    type varchar(4), Sessiontime varchar(14), Disconnect varchar(12))
  2. Create an rsp_account stored procedure that can be called by a *.acc file.
    create procedure rsp_account@transactiontime varchar(21),@username varchar(21),@AcctSessionID varchar(21),@NASIPAddress varchar(21),@NASPORTTYPE varchar(21),@FRAMEDIPADDRESS varchar(21),@callingstationid varchar(21),@calledstationid varchar(21),@TYPE varchar(21),@ACCTSESSIONTIME varchar(21),@ACCTTERMINATIONCAUSE varchar(21)ASINSERT INTO Accounting (TransactionDate, username, SessionID, NASIPAddr, NASPort, UserIPAddr, CallingNum, CalledNum, type, Sessiontime, Disconnect)VALUES (@transactiontime, @username, @AcctSessionID, @NASIPAddress, @NASPORTTYPE, @FRAMEDIPADDRESS, @callingstationid, @calledstationid, @TYPE, @ACCTSESSIONTIME, @ACCTTERMINATIONCAUSE)
  3. Create the mysqlacct.acc file to call the rsp_account stored procedure.

    The mysqlacct.acc file uses an SQL=EXECUTE procedure_name value1,...valueN statement instead of an SQL=INSERT into table (column1, ...columnN) Values (value1,...valueN), because the stored procedure performs the INSERT action.

    Be sure to configure the CONNECT statement to reflect your operating environment.

    [Bootstrap]LibraryName=sqlacct.dllEnable=1InitializationString=[Settings]Connect=DSN=<dsn_name_here>;UID=<username_for_dB>;PWD=<password_for_dB>ConnectTimeout=25WaitReconnect=2MaxWaitReconnect=360ParameterMarker=?loglevel=2[Type]1=User2=User3=User[Type/User]SQL=Execute rsp_account   %transactiontime/t, \ @user-name/21s, \ @Acct-Session-ID/12s, \ @NAS-IP-Address/15s, \ @NAS-PORT-TYPE/5s, \ @FRAMED-IP-ADDRESS/15s, \ @calling-station-id/12s, \ @called-station-id/12s, \ %TYPE/4s, \ @ACCT-SESSION-TIME/14s, \ @ACCT-TERMINATION-CAUSE/12sConcurrentTimeout=30MaxConcurrent=2

Modified: 2017-09-27