Detailed Use Cases

This section provides a few use cases on the SQL Plug-ins.

Working with Stored Procedures

A stored procedure is a sequence of SQL statements that form a logical unit and perform a particular task. You can use stored procedures to encapsulate a set of queries or operations that can be executed repeatedly on a database server. For example, you can code operations on an employee database, such as password lookup, as stored procedures that can be executed by application code. For more information about stored procedures, see the SBR Carrier Administration and Configuration Guide.

The SQL example in the previous section can be replaced by a custom stored procedure. This stored procedure might look something like the following:

PROCEDURE myProc
(
ttime in varchar2,
nasaddr in varchar2,
sessid in varchar2,
ttype in varchar2,
uname in varchar2,
bytein in varchar2,
byteout in varchar2
);
END myProc;
CREATE OR REPLACE PACKAGE BODY myPack1 IS
PROCEDURE myProc
(
ttime in varchar2,
nasaddr in varchar2,
sessid in varchar2,
ttype in varchar2,
uname in varchar2,
bytein in varchar2,
byteout in varchar2
)
IS
BEGIN
INSERT INTO usagelog
( Time, NASAddress, SessionID, Type, Name,
BytesIn, BytesOut )
VALUES
( ttime, nasaddr, sessid, ttype, uname, bytein,
byteout );
END myProc;
END myPack1;

When you invoke the stored procedure, delineate each parameter as an input (!i), output (!o), or input/output (!io) variable.

This stored procedure can be invoked with the following connect string in the radsql.acc file:

SQL=BEGIN myPack1.myProc(%TransactionTime!i,
%NASAddress!i, @Acct-Session-Id!i, %Type!i,
%FullName!i, @Acct-Input-Packets!i,
@Acct-Output-Packets!i); END;

SQL Database Data Retrieval Methods

There are two methods for retrieving the required data items (IMSI, MSISDN, and Authorization String) from the SQL database:

• SQL=SELECT Statement Method
  For specific information, see SQL=SELECT Method for Data Retrieval from SQL Databases.
• Stored Procedure Method
  For specific information, see Stored Procedure Method for Data Retrieval from SQL Databases.

SQL=SELECT Method for Data Retrieval from SQL Databases

The SQL=SELECT method for retrieving the IMSI, MSISDN, and Authorization String from the SQL database involves including a SQL=SELECT statement and a corresponding [Results] section in the sqlaccessor.gen or sqlaccessor_jdbc.gen file.

SQL=SELECT Method: SELECT Statement

Place a SQL=SELECT Statement in the [Settings] section of sqlaccessor.gen or sqlaccessor_jdbc.gen to retrieve the IMSI, MSISDN, and Authorization String.

In the following example, the IMSI, MSISDN, and Authorization String are selected from a subscriber database in which the IMSI is the key. (The user column contains IMSI values.)

SQL=SELECT user, msisdn, authstring FROM my_database WHERE
user=@KeyToRecord

Note: You can also retrieve IMSI, MSISDN, and Authorization String from the subscriber database by setting the MSISDN as the key. In this case, the SELECT statement would be: SQL=SELECT user, msisdn, authstring FROM my_database WHERE msisdn=@KeyToRecord

SQL=SELECT Method: [Results] Section

The [Results] section declares output container variables and maps them to the columns in the SQL query result set. Columns in the SQL query are identified in the [Results] section by the position number in the SQL query and maximum number of characters in the SQL database. For example:

[Results]
ResultIMSI = 1/16
ResultMSISDN = 2/16
ResultAuthString = 3/16

It is mandatory that you define values for all three output variables (ResultIMSI, ResultMSISDN, and ResultAuthString). The SQLaccessor module may fail to work properly if you leave any of the output variables blank.

If the SQL database does not contain an IMSI column or a MSISDN column, then the SQL query would not include this unavailable column in the SQL=SELECT statement. However, a value should be set for the unavailable output variable in the [Results] section instead of being left blank. This value should be the same as that of the value set for the output variable of the key field.

The following example explains how to retrieve Authorization String from the SQL database by setting the IMSI as the key if the database only contains the IMSI and Authorization String columns. In this case, the SELECT statement and the [Results] section would be:

SQL=SELECT user, authstring FROM my_database WHERE user=@KeyToRecords

[Results]
ResultIMSI = 1/16
ResultMSISDN = 1/16
ResultAuthString = 2/16

SQL=SELECT Method: Section Correlations Illustrated

The sqlaccessor.gen file indicates an SQL statement that uses the IMSI contained in @KeyToRecord to match a value in the user column, thereby retrieving an associated row of values in the user, msisdn, and authstring columns. Therefore, the user column must contain IMSI values. The sqlaccessor.gen file [Results] section indicates that the values of the user, msisdn, and authstring columns are returned in the temporary attributes @ResultIMSI, @ResultMSISDN, and @ResultAuthString. Therefore, the user, msisdn, and authstring columns must contain IMSI, MSISDN, and authorization string values respectively. The temporary attributes described in this example are fixed and specific to the SIM feature in combination with the SQL Accessor plug-in.

Figure 12 illustrates the correlation between the SQL=SELECT statement, the [Results] section, the SQL database, and the key identified in gsmmap.gen.

ResultIMSI=1/16 indicates that the first column in the SQL=SELECT statement, the column named user contains the value of ResultIMSI that is retrieved. In Figure 12, the user column contains IMSI values, which are integer values that uniquely identify the cellular network of the user.

ResultMSISDN=2/16 indicates that the second column in the SQL=SELECT statement, the column named msisdn contains the value of ResultMSISDN that is retrieved. In Figure 12, the msisdn column contains MSISDN values, which are integer values that uniquely identify a subscription in a GSM or an UMTS network.

ResultAuthString=3/16 indicates that the third column in the SQL=SELECT statement, the column named authstring contains the value of ResultAuthString that is retrieved. In Figure 12, the authstring column contains authorization key string. Using this string, the correct profile is fetched and assigned to a particular IMSI or MSISDN.

Note: The column headings need not be user, msisdn, or authstring. However, they must be mapped to ResultIMSI, ResultMSISDN, and ResultAuthString as shown in Figure 12.

In Figure 12, the line KeyForAuthorization=IMSI in the gsmmap.gen file indicates that the temporary attribute @KeyToRecord contains IMSI values to be used as the key in obtaining further information for the user to be authenticated.

Figure 12: Relationship Between Sections in sqlaccessor.gen File

Stored Procedure Method for Data Retrieval from SQL Databases

You can use a stored procedure, rather than a SQL=SELECT statement, to retrieve the IMSI, MSISDN, and Authorization String from the database for use by the SQLAccessor plug-in.

The stored procedure must be created in the Oracle database before using it in Steel-Belted Radius Carrier.

Simauth requires the IMSI, MSIDSDN, and Authorization String in the format used by the MAP gateway. However, the SQL database schema might not allow these strings to be obtained in the expected format. Therefore, the SQLaccessor module can use a stored procedure to convert the database information to the expected format.

Stored Procedure Method: BEGIN Statement of sqlaccessor.gen

Include a SQL=BEGIN statement in the [Settings] section of sqlaccessor.gen or sqlaccessor_jdbc.gen to convert the data from the database to the output parameters, ResultIMSI, ResultMSISDN, and ResultAuthString.

Example:

SQL=BEGIN SIM_Server_stored_proc.produce_return_vals(@KeyToRecord!i,
@ResultIMSI!o, @ResultMSISDN!o, @ResultAuthString!o); END;

Stored Procedure Method: [Results] Section of sqlaccessor.gen

The stored procedure converts and maps SQL values to the variables listed in the [Results] section. If you are using the stored procedure method, include this [Results] section in sqlaccessor.gen or sqlaccessor_jdbc.gen exactly as shown here.

[Results]
ResultIMSI
ResultMSISDN
ResultAuthString

Stored Procedure Method: Database Schema

The database schema must exist for the database key and the data to be retrieved from the database.

Example:

imsi VARCHAR2(32)
msisdn VARCHAR2(32)
auth_string VARCHAR2(32)
subscriber_id VARCHAR2(32)

(The column named user contains IMSI values.)

Stored Procedure Method: Data Retrieval

The stored procedure must retrieve the values for the IMSI, MSISDN, and Authorization String from the database and return them in the values of ResultIMSI, ResultMSISDN, and ResultAuthString.

Stored Procedure Method: Example

The following lines retrieve the values for IMSI, MSISDN, and Authorization String and place them in the output parameters ResultIMSI, ResultMSISDN, and ResultAuthString. The database columns are named msisdn, authstring, and user, where user contains IMSI values. The IMSI values are the key values.

.
.
.

CREATE OR REPLACE PACKAGE SIM_Server_stored_proc IS
PROCEDURE produce_return_vals(

KeyToRecord IN VARCHAR2,
ResultIMSI OUT VARCHAR2,
ResultMSISDN OUT VARCHAR2,
ResultAuthString OUT VARCHAR2) IS
.
.
.
-- The cursor holds the result of the query.
SELECT * FROM subscribers WHERE subscriber_id=KeyToRecord;
. -- Execute the query
OPEN cur;
FETCH cur INTO row;
.
.
.

-- If the row was found then convert the data

IF( cur%FOUND ) THEN

ResultMSISDN : = row.msisdn;
ResultIMSI : = row.imsi;
ResultAuthString: = row.auth_string;
.
.
.