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)
    AS
    INSERT 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.dll
    Enable=1
    InitializationString=
    [Settings]
    Connect=DSN=<dsn_name_here>;UID=<username_for_dB>;PWD=<password_for_dB>
    ConnectTimeout=25
    WaitReconnect=2
    MaxWaitReconnect=360
    ParameterMarker=?
    loglevel=2
    [Type]
    1=User
    2=User
    3=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/12s
    ConcurrentTimeout=30
    MaxConcurrent=2

Modified: 2018-01-11