Fact

• PhenixID Identity Manager 5.6.6 or later
• Java class-files
  – SQLGridCustomControl.class
  – SQLGridCustomControlExport.class
• JSP-file
  – SQLGridCustomControl.jsp

Situation

Use the custom control psd.SQLGridCustomControl to display the result from an SQL query in a grid. This data can aslo be exported.

Solution

Configure the custom control

Note: The syntax for the custom control should be set to “String list”.

1 Download the Custom Control

The custom control is downloaded PSD1094.zip.
Depending on the version of PIM you run, make sure you download the correct ZIP-file.

2 Add Custom Control to IM Configurator

Add SQLGridCustomControl.class and SQLGridCustomControlExport.class to the following file path:
\..\PhenixID\IMConfigurator\ext\class\psd
You must restart IM Configurator for the custom control to be active.

3 Add Custom Control to IM WEB

Add SQLGridCustomControl.class and SQLGridCustomControlExport.class to the following file path:
\..\PhenixID\IM\customer\extension\class\psd

Add ShowMultiValueInGrid.jsp to the following file path:
\..\PhenixID\IM\customer\extension\web\jsp\psd
You must restart IM WEB for the custom control to be active.

Configuration

Jsp file to use. Default is /jsp/psd/SQLGridCustomControl.jsp
The default jsp-file to use for this custom control is /jsp/psd/SQLGridCustomControl.jsp but you may use another jsp-file of your choice. In this option the file path is the URL, starting at the IM web app directory.

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

Connection URL for SQL.
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.

Username for SQL
The username for the database account used to read from the database.

Password for SQL
The password for the database account used to read from the database.

SQLQuery
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.

##DATE##
Use ##DATE## to insert the input from date selector.

##TEXT##
Use ##TEXT## to insert input from 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.

IMATTRIBUTE()
Use IMATTRIBUTE(attributeName) to use the value from the attribute with the specified name from the current edit object.

Show date selector (true/false). Default: true.
Set to true if the date selector should be visible in the custom control. The value from the date selector can be used in the sql query by using ##DATE##.

Show text input (true/false). Default: true.
Set to true if the text input field should be visible in the custom control. The value from the text input field can be used in the sql query by using ##TEXT##.

Show search button (true/false). Default: true.
If none of the input fields are shown in the custom control, it is also possible to hide the search button to only show the initial search in the custom control.

Columns to show. Map column name with label: col1|label1,col2|label2
Only the columns specified in this option will be shown in the grid in the custom control. This makes it possible to run a procedure in the database, but only show the desired columns. Each column name can be mapped to a label name, shown as the column header in the grid.

Column sizes in px. Example: 40,*,80. Default will use * for all columns.
Enter the size of each column, separated by comma. The size is in pixel, and you can use * to let one or multiple columns share all the width that is remaining equally.

Column number for initial sorting. Default (0) will not sort.
To make the initial sorting in a specific column, enter the column number here. The first column number is no 1, and the sorting will be ascending. To sort the column descending, enter – before the column number, for example -2 to sort the grid on the second column descending.

Column additional LDAP search.
It is possible to make a search in the LDAP directory using the value from the SQL database, and exchange the value from the database with the one found in the directory. If no object is found in the directory, the original value from the database will remain in the grid. To make an LDAP search for multiple columns, separate each column configuration with a semi-colon.

Syntax for one column LDAP search:
sql-column-name|ldap-search|value-to-show

Example: userID|(&(samaccountname=##userID##))|##samaccountname## (##givenName## ##sn##)
Example result: user1234 (John Smith)

Search base for column additional LDAP search.
The search base used for column additional LDAP search.

Run on init (true/false). Default: false.
If the SQL query should be executed when the custom control is shown. If the query contains ##DATE## or ##TEXT##, those values must get a default value below.

Init value for date selector. 
The initial value for the date selector. Mandatory if the query should be executed on init and the query contains ##DATE##. The value supports the usage of ##TODAY()## and IMATTRIBUTE() as the query does.

Init value for text input. 
The initial value for the text input field. Mandatory if the query should be executed on init and the query contains ##TEXT##. The value supports the usage of ##TODAY()## and IMATTRIBUTE() as the query does.

Show export button (true/false). Default: true.
If the button for exporting the grid values to PDF, Excel or CSV should be visible.

Export file name. Default: IM-Export
The name of the file. File extension will be added when the user chooses the type of the exported file.

Export CSV field separator. Default: , (comma)
The separator used between the values in the CSV exported file.

Label -> Date selector (Default: blank)
The label for the date selector. Default is blank, no label will be shown.

Label -> Text input (Default: blank)
The label for the text input field. Default is blank, no label will be shown.

Label -> Search button (Default: Search)
The label for the search button. Default is ‘Search’.

Label -> Export button (Default: Export)
The label for the export button. Default is ‘Export’.

Label -> Export modal title (Default: Export to file)
The title of the modal where the user chooses to export to Excel or CSV. Default is ‘Export to file’.

Label -> Export text (Default: blank)
An optional text placed above the export buttons in the export modal. Default is blank, no text label will be shown.

Label -> Export to PDF button (Default: Export to PDF)
The label for the button to export to PDF. Default is ‘Export to PDF’.

Label -> Export to excel button (Default: Export to Excel)
The label for the button to export to Excel. Default is ‘Export to Excel’.

Label -> Export to CSV button (Default: Export to CSV)
The label for the button to export to CSV. Default is ‘Export to CSV’.