PSD1153 – SQL Grid View

Summary

This PhenixID Solution Document (PSD) is written for PhenixID Identity Manager (IM) 5.4.2 and above.

This view is used to show a result set from an sql query, with a possibility to add a user entered text to the search query. See below a screenshot.

System Requirements

• PhenixID Identity Manager 5.4.2 or later
• JDBC driver for the database to connect to
  Download driver from provider and put it in:
  [IM root folder]/customer/extension/lib

Download

Download and extract PSD1153.zip.

Add filter and JSP to IM Web

Add filter

Place the files SqlGridView.class and SqlGridViewProvider.class in
[IM root folder]/customer/extension/class/psd
If the /psd folder does not exist, then create one.

Add JSP

Place the file sqlGridView.jsp in
[IM root folder]/customer/extension/web/jsp/psd
If the /jsp/psd folder does not exist, then create one.

Configuration

Add the view to DSEditor.properties

Open DSEditor.properties for the role that needs to see the view.
To add the view, add the view’s name psd.SqlGridView to the policy See example below where both new view and Main is available.
LOAD_VIEW=se.nordicedge.view.main.View,psd.SqlGridView

Policies to configure the view

All polices below should be placed in the role DSEditor.properties used.

SQL_GRID_VIEW_JDBC_CLASS=
When connecting to the SQL database, a driver is required. The jar-file containing the driver must be placed in [IM root folder]/customer/extension/lib. In this option, enter the full class name for the driver.

SQL_GRID_VIEW_CONNECTION_URL=
The URL for connecting to the SQL database. The URL has different syntax depending on the database type and the used driver, but it typically contain an IP address, a port and the name of the database.

SQL_GRID_VIEW_USERNAME=
The username for the database account used to read from the database.

SQL_GRID_VIEW_PASSWORD=
The password for the database account used to read from the database.

SQL_GRID_VIEW_SQL_QUERY=
The SQL query that will be used to populate the grid in the custom control. The query can contain a number of parameters that will be translated before the query is executed.

##TEXT##
Use ##TEXT## to insert input from the search text box.

##TODAY()##
Use ##TODAY(yyyy-MM-dd)## to insert the current date/time in specified format. An instruction of how to get the desired format can be found in the javadoc for java.text.SimpleDateFormat.

SQL_GRID_VIEW_INIT_SQL_QUERY=
The SQL query to run when the view is first loaded. This query can contain parameters of type ##TODAY()## as described above. If no initial query is specified, the view will be empty before any search is made.

SQL_GRID_VIEW_SEARCH_LABEL=
The label for the search text field. If the policy is missing or empty, no label will be shown.

Example from DSEditor.properties with policies

SQL_GRID_VIEW_JDBC_CLASS=com.mysql.jdbc.Driver
SQL_GRID_VIEW_CONNECTION_URL=jdbc:mysql://Name_IP_adress:PORT/MsgServices?autoReconnect=true
SQL_GRID_VIEW_USERNAME=username
SQL_GRID_VIEW_PASSWORD=password_for_username
SQL_GRID_VIEW_SQL_QUERY=SELECT whenSent, customername, recipient, broker, info, servername, transactionnr FROM SentMessages WHERE recipient = ‘##TEXT##’ order by whenSent DESC LIMIT 100;
SQL_GRID_VIEW_INIT_SQL_QUERY=
SQL_GRID_VIEW_SEARCH_LABEL=Mobile phone number