Working with Data Sources

27 Working with Data Sources

27.1 About Data Sources

WebCenter Portal supports two types of data sources from which to retrieve data to present on a portal page:

REST Data Sources: You can configure a REST data source in the Data Source wizard or the Define Data Visualization wizard, providing either an internal URL (does not require proxy details) or an external URL (requires proxy details). See Using REST Data Sources.
SQL Data Sources: You can configure a SQL data source in the weblogic console to provide specific database details (such as user name, password, service ID, and host name). See Using SQL Data Sources.

27.2 Using REST Data Sources

This section includes the following topics:

27.2.1 Creating a REST Data Source

Out-of-the-box, no REST data sources exist. You can create a REST data source at the application level or at the portal level. Application-level REST data sources are available for use in all portals, unless the portal has been specifically excluded. Portal-level REST data sources are available for use only in the portal in which they are created.

You can create a REST data source in two ways in WebCenter Portal:

In the Define Data Visualization wizard: Supports basic REST data source creation and editing. Available when you add and configure a data visualization on a portal page to present data from a REST data source in a selected visualization template. For more information, see Configuring a Data Visualization.
On the Assets or Shared Assets page: Supports more advanced REST data source creation and editing, providing more options. This method is described in this section.

To create a REST data source:

Navigate to one of the following:
- To create an application-level REST data source, go to the Shared Assets page. For more information, see Accessing Shared Assets.
- To create a portal-level REST data source, go to the Assets page for the portal in which you want to create it. For more information, see Accessing Portal Assets.
In the left panel, under Data Sources, click REST Data Sources.
In the toolbar, click Create (Figure 27-1) to open the Create New REST Data Source wizard..

Figure 27-1 Creating a REST Data Source

Description of "Figure 27-1 Creating a REST Data Source"
Enter a Name and optional Description for the data source.

Figure 27-2 Create New REST Data Source Wizard
Enter a valid Resource Path URL against which the REST data source will be created, then click Test to confirm the resource path is valid and that it returns data. For example, the resource path http://dev.markitondemand.com/Api/v2/Quote?symbol=AAPL provides stock quotes.

Note:
The Resource Path must support at least the GET method, along with any other method (PUT, POST, DELETE).
In the list of Parameters populated based on the resource path provided, remove or add parameters as needed.

Note:
If you modify the Resource Path URL, the list of parameters will be refreshed, and you may need to respecify the required data source parameters.
Select a Data Source Return Type that specifies data type returned by the Resource Path URL. A URL can return data in either XML format or JSON format.
Select or deselect Use Portal Proxy and Use Authentication as required. For an external REST URL, select Use Portal Proxy.
Select or deselect Enable Caching as required, and enter values for Time To Live (ms) and Max Weight.
From the Methods list, select the method to assign to the REST data source: GET, PUT, POST, or DELETE.
In the list of Parameters populated based on the resource path provided, remove or add parameters as needed.

Type payload values in the Request Payload and Response Payload fields as required. Tip: Enter the Resource Path URL in a browser to see what data is returned.

Note:

While the Resource Path URL can be the same for all methods (GET, PUT, POST, DELETE), the Request Payload and Response Payload can be different for each method. You will need to provide separate payload values for every method that you specify.

An example of XML for Request Payload may look similar to:

<opportunity>
<city>DOCUMENT</city>
<comment>Default</comment>
<country>US</country>
<createdBy>LHUGHES</createdBy>
<creationDate>2015-10-01T00:00:00-08:00</creationDate>
<currencyCode>USD</currencyCode>
<customerInfo>
<contactEmail>david@abccorp.com</contactEmail>
<contactName>David</contactName>
<contactPhone>9987656781</contactPhone>
<customerPhone>9987656781</customerPhone>
</customerInfo>
<formattedAddress>559 Cooper St, SEATTLE, WASHINGTON 98102</formattedAddress>
<locationId>300100000393851</locationId>
<opportunityId>300100029659792</opportunityId>
<opportunityName>OpptyForABCCorp</opportunityName>
<opportunityNumber>CDRM_23685</opportunityNumber>
<postalCode>98102</postalCode>
<primaryOrganizationId>201</primaryOrganizationId>
<primaryRevenueId>300100029659793</primaryRevenueId>
<products>
<productName>Server - Class III</productName>
<quantity>10</quantity>
<totalPrice>8000</totalPrice>
<unitPrice>800</unitPrice>
</products>
<products>
<productName>Pro Server</productName>
<quantity>15</quantity>
<totalPrice>11250</totalPrice>
<unitPrice>750</unitPrice>
</products>
<revenue>8000</revenue>
<salesStage>01 - Qualification</salesStage>
<targetPartyId>100000015802569</targetPartyId><targetPartyName>Designs Cooper</targetPartyName>
</opportunity>

If you selected Use Authentication:
- Click Next.
- On the Auth page, select existing credentials, or click Add Credentials to create a new credential (if you have been granted the required permission to create a new credential).
- On the Credentials page, select from the Select Security Type list:
  - Basic Authentication support is provided via the External Application Service of WebCenter Portal. The user name and password for the REST service are stored in the OPSS Credential Store using the External Application Service. When a data visualization is rendered at runtime, WebCenter Portal retrieves these credentials to present the data on the page.
  - OAuth support is provided by OWSM integrated with REST Data Control. ADF REST Data Control supports only OAuth 2.0 using IDM OAuth Servers. External REST services that are protected using a non-IDM solution are not supported.
  - Taleo Token Generator authentication is provided by a custom security provider to propagate security at runtime. If authentication is successful, a Taleo token is issued. That token must be passed to each subsequent resource request to retrieve data.
    
    Figure 27-3 Create REST Data Source Wizard: Credentials Page
- Under Configuration, define or edit the login information, depending on the security type:
  - For Basic Authentication, enter the Login user name, and Password.
  - For OAuth, enter the Login user name, and Password corresponding to the Client ID and Password configured in OAuth server, and enter the Token Generator URL of the OAuth Server.
  - For Taleo Token Generator, enter the Login user name, and Password, and enter the Login URL and Org Code to access the Taleo end point. A user must be authenticated using user name, password, and organization code (company ID) on a defined login URL.
Click Save.

The newly created data source is listed on the Assets or Shared Assets page.

The empty check box in the Available column indicates that it is not yet published and hence is not available to other users. To publish the data source, click the check box.

For information about publishing a data source, see Showing and Hiding Assets.

27.2.2 Editing a REST Data Source

You can edit a REST data source in two ways in WebCenter Portal:

In the Define Data Visualization wizard: Supports basic REST data source creation and editing. Available when you add and configure a data visualization on a portal page to present data from a REST data source in a selected visualization template. For more information, see Configuring a Data Visualization.
On the Assets or Shared Assets page: Supports more advanced REST data source creation and editing, providing more options. This method is described in this section.

To edit a REST data source:

Navigate to one of the following:
- To edit an application-level REST data source, go to the Shared Assets page. For more information, see Accessing Shared Assets.
- To edit a portal-level REST data source, go to the Assets page for the portal to which it belongs. For more information, see Accessing Portal Assets.
In the left panel, under Data Sources, click REST Data Sources.
Click the Edit quick link in the row for the REST data source that you want to edit to open the Edit REST Data Source wizard (Figure 27-4).

Figure 27-4 Edit REST Data Source Wizard
Modify the entries in the wizard as required.

If the underlying data changes, you can select Refresh Schema to refresh the data source and associated business object with changes such as added, deleted, or renamed attributes. For more significant structural changes, you will need to re-create the data source, and any data visualizations where it is used.

For more information about other field values and selections, refer to Creating a REST Data Source or the online help for the wizard.
Click Save.

27.2.3 Managing a REST Data Source

The following options are available on the Assets or Shared Assets page to enable you to manage REST data sources:

27.3 Using SQL Data Sources

This section includes the following topics:

27.3.1 Creating a SQL Data Source

Out-of-the-box, no SQL data souces exist. You can create a SQL data source at the application level or at the portal level. Application-level SQL data sources are available for use in all portals, unless the portal has been specifically excluded. Portal-level SQL data sources are available for use only in the portal in which they are created.

The default SQL style for all database connections is Oracle. If the data source is a Microsoft SQL Server database, then the system administrator must perform the following tasks:

Manually override the SQL style with one that supports the SQL Server database. To do this, set the Java system property jbo.SQLBuilder to SQLServer. When Oracle WebLogic Server is started with this configuration, it supports the SQL style specified.

Set the jbo.SQLBuilder property in any of the following ways:
- Open the DomainHome/bin/setDomainEnv.sh file and add -Djbo.SQLBuilder=SQLServer to the JAVA_OPTIONS line.
- Edit the managed server start script in a text editor and add -Djbo.SQLBuilder=SQLServer to the JAVA_OPTIONS line.
- In the WebLogic Server Administration Console, navigate to the WC_Spaces managed server, click the Server Start tab and specify the system property in the Arguments text area.
Specify Java as a typemap entry using the jbo.TypeMapEntries property as follows:
```
-Djbo.TypeMapEntries="Java" 
```

To create a SQL data source:

Navigate to one of the following:
- To create an application-level SQL data source, go to the Shared Assets page. For more information, see Accessing Shared Assets.
- To create a portal-level SQL data source, go to the Assets page for the portal in which you want to create it. For more information, see Accessing Portal Assets.
In the left panel, under Data Sources, click SQL Data Sources.
In the toolbar, click Create (Figure 27-5) to open the Create New SQL Data Source wizard.

Figure 27-5 Creating a SQL Data Source

Description of "Figure 27-5 Creating a SQL Data Source"
In the wizard, enter a Name and optional Description for the data source.

Figure 27-6 Create New SQL Data Source Wizard
Under Configuration:
- In the JDBC Data Source field, enter a JNDI name of JDBC data source.
- In the Password field, enter the JDBC data source password.
Enter a SQL Statement to query the SQL data source. For example:
```
SELECT * FROM Persons WHERE City LIKE 'sa%'
```
You can use :bind-name to refer to bind variables in the SQL statement. Bind variables are useful in controlling the data displayed, such as in the following example:
```
SELECT ename, empno, mgr, deptno FROM emp WHERE job IN (:job) ORDER BY empno ASC
```
This query returns details of all employees with a particular job role, for example, sales managers. In this query, :job defines the parameter job, which maps to the job column in the emp database table. The query returns data based on the job value. You can add as many bind variables as required. You can also use the same variable multiple times in the query.
Notes:
- Avoid using SQL reserved words and keywords for parameter names in the query. The use of reserved words will cause the query to fail. For more information, see the topic titled "Oracle SQL Reserved Words and Keywords" in the Oracle Database SQL Language Reference in the Oracle Database documentation library.
- If you are using a SQL Server database, then you may get an error if the query contains a column with the NCHAR or NVARCHAR data type. If the SQL data control fails to execute a query containing NCHAR and NVARCHAR, it will not be added to the Data Controls folder in the resource catalog. To work around this limitation, you must modify the query using the CONVERT function. For example:
```
SELECT CONVERT(VARCHAR(20), col1) col1, CONVERT(VARCHAR(20), col2) col2 FROM table1
```
To allow this SQL data source to be updated, select Updatable. The underlying table must have a primary key defined on the table for this feature to work and updateable query should be based on the single table.
Click Test to confirm whether required entries are complete and valid.
Click Save.

The newly created data source is listed on the Assets or Shared Assets page.

The empty check box in the Available column indicates that it is not yet published and hence is not available to other users. To publish the data source, click the check box. For information about publishing a data source, see Showing and Hiding Assets.

27.3.2 Editing a SQL Data Source

To edit a SQL data source:

Navigate to one of the following:
- To edit an application-level SQL data source, go to the Shared Assets page. For more information, see Accessing Shared Assets.
- To edit a portal-level SQL data source, go to the Assets page for the portal to which it belongs. For more information, see Accessing Portal Assets.
In the left panel, under Data Sources, click SQL Data Sources.
Click the Edit quick link in the row for the SQL data source that you want to edit to open the Edit SQL Data Source wizard (Figure 27-7).

Figure 27-7 Edit SQL Data Source Wizard
Modify the entries in the wizard as required. For more information about the field values and selections, refer to Creating a SQL Data Source or the online help for the wizard.
Click Save.

27.3.3 Managing a SQL Data Source

The following options are available on the Assets or Shared Assets page to enable you to manage SQL data sources: