Steel-Belted Radius Carrier 7.2.x Administration and Configuration Guide > Configuring SQL Accounting > Accounting Stored Procedure Example

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 a 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