Jdbc

Notice: The use of the Jdbc plug-in and the following discussion assumes you are familiar with SQL and general database issues, have an SQL compliant database running on an accessible system, and have a JDBC driver for that database installed.

The Jdbc plug-in allows a single SQL statement to be executed in either the Authentication or Accounting PolicyFlow™. The plug-in parses SQL statements once and executes the parsed statement for each RADIUS request. The Jdbc plug-in automatically commits the SQL statement after execution. The plug-in connects to a database through a JDBC driver. Certain values in the SQL statement can be dynamic and can change with each request processed. These dynamic values are marked in the SQL statement by the question mark (?) character and an are called IN parameters. Stored procedures can also be executed using the JDBC call syntax of:

{call procedure_name(?, ?, ...)}

Or, if the stored procedure is a function returning a result code:

{? = call procedure_name(?, ?, ...)}

Note that when the SQL statement calls a stored procedure, some of the question mark (?) characters mark the locations of OUT parameters or INOUT parameters. Stored procedures return OUT parameters at the end of execution. The OUT parameters are mapped into internal variables in out maps . Stored procedures read the values of INOUT parameters in the beginning of execution and return INOUT parameters at the end of execution. The INOUT parameters are mapped from internal variables in bind maps and mapped to internal variables in out maps .

Bind Maps

A bind map is used to assign variables from the current RADIUS request to IN parameters in the SQL statement.

The bind map is processed before the statement is executed.

The right side of the assignment statement in the bind map contains the variable to take the data from. The left side of the bind map assignment statement contains the index number of the ? in the SQL statement (1, 2, 3, etc.). Special modifiers are allowed on the left hand side of assignments to convert string values to appropriate database types. Supported modifiers are: TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, DATE, TIME, and TIMESTAMP. By default, assignments are considered to be of type VARCHAR and do not require the VARCHAR modifier.

Result Maps

A result map is used to assign data values returned from the execution of the SQL statement to variables associated with the current RADIUS request.

Index Number

The right side of the result map assignment statement contains the index number of the data returned from executing in the SQL statement.

For result sets returned for a select statement, the format of this index is the result.row.column where result is the number of the result, row is the row within a result set, and column is the column within the specified row.

The left side of the result map assignment statement contains the variables associated with the current RADIUS request in which the data should be saved. No modifiers are needed or supported in result maps.

Out Maps

An out map is used to assign OUT and INOUT parameters returned from the execution of a stored procedure to variables associated with the current RADIUS request.

The right side of the out map assignment statement contains the index number of the question mark (?) in the SQL statement (1, 2, 3, etc.). The left side of the assignment statement in the result map contains the variables associated with the current RADIUS request in which the data should be saved.

In the right hand side of assignments, special modifiers allow the plug-in to register the data type of OUT and INOUT parameters.

Supported Modifiers

Supported modifiers are: TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, and TIMESTAMP.

By default, OUT and INOUT parameters are considered to be of data type VARCHAR and do not require the VARCHAR modifier.

Policy Flow Results

Success If the Jdbc plug-in succeeds in connecting to the database through the JDBC driver and the SQL command is successfully executed, the method succeeds.
Failure If the Jdbc-TestResult property is set to a value greater than zero and the Jdbc plug-in method successfully executes a statement but the statement failed to select, update, or insert one or more rows for the result number specified by the Jdbc-TestResult property then the method fails.
If the Jdbc-TestOutParameter property is set to a value greater than zero and the Jdbc plug-in method successfully executes a stored procedure but the OUT parameter specified by the Jdbc-TestOutParameter is returned as NULL then the method fails.
Error If a SQL error occurs while executing a statement and the error message contains the word unique then the plug-in assumes a unique index violation has occurred and returns failure rather than error.
If any other error occurs while executing the statement then the method results in an error.

Plug-in Type

Authentication and Accounting

Example

Due to its size, the Jdbc example is presented as a separate topic. Click the following to view the Jdbc Example.

Syntax

MethodInstanceName Method-Type= Jdbc [ Control Properties ]
Jdbc-Driver = dynamic classname
Jdbc-Url = dynamic JDBC style URL
Jdbc-Statement = SQL string
[ Jdbc-ConnectionsPerUrl = dynamic number ]
[ Jdbc-BindMap = mapping text ]
[ Jdbc-ResultMap = mapping text ]
[ Jdbc-OutMap = mapping text ]
[ Jdbc-TestResult = result number ]
[ Jdbc-TestOutParameter = parameter number ]
[ Jdbc-Timeout = dynamic milliseconds ]
[ Jdbc-NewUser = dynamic boolean ]

Method Properties

Jdbc-Driver (Required)

Name of the JDBC driver Java class used to connect to the database. A path to the driver class in the class path setting must be included to launch NavisRadius. If the JDBC driver is placed in the NavisRadius lib directory then the default path setting does not need to be changed. The driver to Sybase databases is included in the class path when NavisRadius is installed.

Examples

Jdbc-Driver = com.sybase.jdbc.SybDriver

Jdbc-Driver = oracle.jdbc.driver.OracleDriver

Jdbc-Driver = ${user.Jdbc-Driver}

Jdbc-Url (Required)

Jdbc-Url specifies the JDBC URL used to connect to the target database. The syntax varies for different JDBC drivers. Include the user and password properties if required by the database.

For Sybase the JDBC URL syntax is:

jdbc:<protocol>:<subprotocol>:<host>:<port>?<property>=<value>&<property>=<value>...

Where

<protocol> = sybase

<subprotocol> = Tds (tabular data stream)

<host> = the fully qualified domain name or IP address of the database machine

<port> = the port the machine is listening for connections on

<property> = is a Sybase specific connection property

<value> = is the value of the Sybase specific connection property

For Oracle the JDBC URL syntax is:

jdbc:<protocol>:<subprotocol>:<username>/<password>@<host>:<port>:<sid>

Where

<protocol> = oracle

<subprotocol> = thin (this is the Oracle pure Java driver that is portable)

<username> = the database account name to use when establishing a connection

<password> = the password of the account name to use when establishing a connection

<host> = the fully qualified domain name or IP address of the database machine

<port> = the port the machine is listening for connections on (TNS Listener)

<sid> = the database name/instance to connect to

Examples

Jdbc-Url = "jdbc:sybase:Tds:localhost:2638?user=dba&password=sql"

Jdbc-Url = "jdbc:oracle:thin:nr/nr@localhost:1521:orc1"

Jdbc-Url = ${user.Jdbc-Url}

Jdbc-Statement (Required)

Jdbc-Statement specifies a SQL statement formatted so that the JDBC driver can parse any optional IN, OUT, or INOUT parameters. IN, OUT, or INOUT parameters are marked using the ? character. Multiple Jdbc-Statement properties may be entered. To include statement information from a file, place an '@' character followed by the name of the file as the value of the property.

Examples

Jdbc-Statement = "select password, check_template, reply_template from authentication where user_name = ?, user_realm = ?"

Jdbc-Statement="{call lease.request(?, ?, ?, ?)}"

Jdbc-ConnectionsPerUrl (Optional)

Jdbc-ConnectionsPerUrl specifies the number of connections to open per JDBC URL. The more connections per URL allows more statements to execute at the same time. Often database licenses limit the number of connections that can be open concurrently. Connections are kept open for the life of the server and are shared among all instances of the Jdbc plug-in.

Default

1

Examples

Jdbc-ConnectionsPerUrl = 5

Jdbc-ConnectionsPerUrl = ${user.Jdbc-ConnectionsPerUrl}

Jdbc-BindMap (Optional)

Jdbc-BindMap specifies text containing the mapping of variables to IN parameters in the statement. There can be multiple instances of this method property. The bind map is processed before the statement is executed. If the statement contains IN parameters either the Jdbc-BindMapFile property or Jdbc-BindMap properties must be specified. To include mapping information from a file, place an '@' character followed by the name of the file as the value of the property. If a statement contains no IN parameters, then no bind locations need be marked and no bind map is required.

Default

No map

Examples

Jdbc-BindMap = "${1} = ${packet.Base-User-Name};"

Jdbc-BindMap = "${2} = ${packet.User-Realm};"

Jdbc-ResultMap (Optional)

Jdbc-ResultMap specifies text containing the mapping of a result indices into variables. There can be multiple instances of this method property. To include mapping information from a file, place an '@' character followed by the name of the file as the value of the property. A result map is not required. However, Jdbc-ResultMap must be specified if there is a result set returned by the statement, and data from that result set needs to be mapped into NavisRadius variables.

Default

No map

Examples

Jdbc-ResultMap = "${check.Password} = ${1};"

Jdbc-ResultMap = "${user.Template} = ${2};"

Jdbc-OutMap (Optional)

Jdbc-OutMap specifies text containing the mapping of OUT parameters in a stored procedure into attributes in variable groups. There can be multiple instances of this method property. To include mapping information from a file, place an '@' character followed by the name of the file as the value of the property. An out map is only required if the stored procedure being called returns OUT parameters. If the JDBC type of an OUT parameter is not type VARCHAR then the type must be specified as the index modifier.

Default

No map

Examples

Jdbc-OutMap = "${user.Port-Limit} = ${3[INTEGER]};"

Jdbc-OutMap = "${user.Groups} = ${4};"

Jdbc-TestResult (Optional)

Jdbc-TestResult specifies which result should be tested to see if rows where modified or selected. Some databases allow multiple results to be returned when executing a stored procedure. Setting this property to 0 prevents the plug-in from testing any result.

Default

1

Examples

Jdbc-TestResult = 0

Jdbc-TestResult = 2

Jdbc-TestOutParameter (Optional)

Jdbc-TestOutParameter specifies which OUT parameter should be tested to see if the returned value is NULL. Setting this property to 0 prevents the plug-in from testing any OUT parameter.

Default

0

Examples

Jdbc-TestOutParameter = 3

Jdbc-TestOutParameter = 2

Jdbc-Timeout (Optional)

Jdbc-Timeout specifies the number of milliseconds to wait for a connection and to run the specified SQL statement. If the time expires while waiting for a connection, the plug-in tries again if there is still time (determined by the client timeout property) to answer the request; otherwise, the plug-in indicates an error. The plug-in also uses the timeout value when running a SQL statement. If a statement takes longer than the specified time, the statement is cancelled and the plug-in returns an error.

Default

10000

Examples

Jdbc-Timeout = 3000

Jdbc-Timeout = ${user.Jdbc-Timeout}

Jdbc-NewUser (Optional)

Jdbc-NewUser specifies whether the read is for a new user. Setting this value to TRUE clears out the reply, check, and user variable groups. To add variables to the existing values in the reply, check, and user variable groups, either set this property to FALSE or do not include it in the configuration properties.

Default

False

Example

Jdbc-NewUser = TRUE

Jdbc-NewUser = ${user.Jdbc-NewUser}