Custom Transport - Interact with External Database

** The extension provided here is a custom transport written by me (Christopher Dawes) and is not supported by HCL.  This extension is provided *as-is*, for demonstration and teaching purposes, if you are going to use it in a production environment you are responsible for testing it thoroughly within your application.  If you think you have found an issue please post back here so that we can work with you to investigate and fix. **

Database Transport

Table of Contents

Description

Revision History

Extension Files

Extension Variables (Input/Ouput Parameters)

Installation Notes

Using in your Leap Application

Code Review

Author: Christopher Dawes (IBM)

Description

Let me start by saying that I am not not an expert programmer, I am sure there are many people out there that could implement this better than I have.  This transport provides a mechanism for a FEB application to interact directly with an external database.   I tried to build my transport so that the user would not need to know the SQL syntax being generated, they just need to have a basic idea of what is needed.  It is expected that you would create an XML file for each database/table that you want to interact with.  Included in this article is a FEB application that demonstrates the functionality, a sample service description XML file and the custom extension (.jar file).

There are many different approaches that one can take when designing a custom extension, the end result usually depends greatly on how it will be used - there is not always a "one-size fits all" solution especially when it comes to communicating with external databases.  Creating SQL queries are a science all unto themselves and the functionality that I have provided is limited to very basic queries (I have not even attempted nested queries!).  Some examples of supported queries are:

SELECT * FROM PEOPLE
SELECT FIRSTNAME, LASTNAME FROM PEOPLE WHERE LASTNAME = 'Dawes' ORDER BY LASTNAME
SELECT FIRSTNAME, LASTNAME FROM PEOPLE WHERE LASTNAME LIKE '%Daw%'
SELECT FIRSTNAME, LASTNAME FROM PEOPLE WHERE LASTNAME STARTSWITH '%Daw%'
DELETE FROM PEOPLE WHERE LASTNAME = 'Dawes'
INSERT INTO PEOPLE (FIRSTNAME, LASTNAME) VALUES ('Chris', 'Dawes')
UPDATE PEOPLE SET FIRSTNAME='Christopher' WHERE FIRSTNAME = 'Chris' AND LASTNAME = 'Dawes'
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY lastname) AS rn, People.* FROM People) AS tbl WHERE rn BETWEEN 1 AND 25 ORDER BY lastname
SELECT * FROM PEOPLE WHERE FIRSTNAME IN ('Chris','Marty','Ken');

 Hopefully from this list you can start to see how this extension can be used.  If you need to perform more complex queries it is possible but it would require the transport to be modified.

Every time I review the code for this transport I see other ways that this could have been implemented, but again it all depends on what you are trying to achieve.  Although I have sought to provide lots of different functionality within this transport you may find that in your own implementation you would prefer for a more direct approach or you may find that your needs are completely different.  I would love to hear your feedback on this extension and why it works or doesn't work for your needs.  I also hope that this is a helpful educational tool as you embark on building custom transports to integrate FEB with your back-end systems.

Revision History

v1.7 - Sept 2018

• Now supports queries using WHERE IN

v1.6 - May 11 2017

• Queries are now case insensitive

v1.5 - Apr 27 2016

• Added row count as output parameter for select queries
• Added orderBy and orderByOp to sort SELECT query
• Added SELECT query pagination using ROW_NUMBER()
• Added whereOp_<column>, enables wild card searches when using the WHERE clause

v1.4 - Apr 1 2016

• Params are now case-insensitive
• Added more debug logging

v1.3 - May 8 2015

• Any Where parameters that have values will be automatically added to the outbound parameter map.  This means that you can have individual outbound parameters that match the same name as the where parameter and they will be exported outside of the result set.  This is helpful if you want to return the values that where sent in with the query. 
• Enabled the transport to use WAS DataSource.

v1.2 - April16 2014

• Jar now contains DB2 and oracle drivers. Made it so that port is not required and is only added to the db url if it is not empty.

v1.1 - April 9 2014

• Jar now contains the MsSQL driver, sqljdbc4.jar.  Tested with ODBC, MsSQL and DB2

v1.0 - April 8 2014

• Initial deployment

Extension Files

DatabaseTransport.jar

*Deprecated* - DatabaseTransport_wDrivers.jar

DBTransport_XMLFiles.zip - I have included 5 different service description XML files.  There is a generic service that enables the form designer to perform all the database options, then there is a service configuration file for each of the individual services; select, insert, update and delete.  It demonstrates which parameters are applicable for each service.

 DBTransport_ServiceCatalog_Project.zip- I have also included an example service catalog which would be a way to group all the services together, rather then them all appearing under the General category.  This catalog will not be usable as-is since it is specific to my local environment.  I am providing the source files so that you could import it directly into your development IDE. Once you import the project, you can update the contained XML files and then create the JAR and deploy it to your FEB.  The end result is a catalog for the services:

Extension Variables

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY lastname) AS rn, People.* FROM People) WHERE rn BETWEEN 1 AND 2 ORDER BY lastname.

<mapping source="transport:queryResults" target="parameter:queryResults">
      <mapping source="transport:FIRSTNAME" target="parameter:firstname" />
      <mapping source="transport:LASTNAME" target="parameter:lastname" />
      <mapping source="transport:EMAIL" target="parameter:email" />
</mapping>

Installation Notes

1. Copy the jar file into the extensions directory (/opt/IBM/Forms/extensions or c:\IBM\Forms\extensions or where you have defined it).  You may have to change the ownership and permissions so that the server can access/execute the file.

2. Modify the xml file (create additional ones) for the database you want to connect to.

• Modify the xml id and service name (replace tableName with the name of the table you are connecting to) :

  <id>DatabaseTransport-Sample-tableName</id>
  <defaultLocale>en-us</defaultLocale>
  <transportId>com.ibm.support.examples.services.DatabaseTransport.id</transportId>
  <name xml:lang="en-us">DatabaseTransport - tableName</name>

• Add the columns that can be searched (returned)
• Add any columns that can be used in "where" clauses
• Modify the DB specific settings: type, name, table name, server, port, access

3. Copy the xml file(s) into the ServiceCatalog\1 directory

4. After about 1 minute the services should appear in your FEB application, restart is not required.

5. Create a DataSource for your database within WebSphere Administration Console

1. Log in to the WAS Admin console.
2. Navigate to Resources...JDBC...Data sources.
3. Set the scope by selecting from the dropdown and click New.
4. Enter the name for the data source (i.e. My DB2 database)
5. Enter the JNDI name (i.e. jdbc/SampleDB) and click Next.
6. If you have an existing JDBC provider for the database you are connecting to then select it, otherwise select Create new JDBC provider and click Next.
7. Select the Database Type, Provider Type, Implementation Type, provide it a name and click Next.
8. Specify the location on your server for the database driver (typically a jar file) and click Next.
9. Provide the database name, server name, port and driver type. Click Next.
10. Click Next (we will create the alias in the next step).
11. Click Finish,
12. Click Save to apply changes to server.

6. Create J2C Authentication Alias for the user that will be used to connect to the datasource.

1. Click the link for the newly created datasource.
2. Click JAAS - J2c authentication data (on the right side of the page)
3. Click New.
4. Provide the Alias, User ID, Password and click OK.
5. Click Save to apply changes to server.
6. Click the link at the top of the page to go back to the main page for your datasource.  Under Security settings select the alias that you just created in the Component-managed authentication alias.  Click OK.
7. Click Save to apply changes to server.
8. Click the check next to the datasource and click Test Connection to verify that you can connect to the database.

7. For troubleshooting, add the trace string com.ibm.support.examples.*=finest to the WebSphere instance.

Using in your Leap Application

1. Using Select.

• Create a service description and find the Database Transport - tableName

• Define the columns that you want to be returned by the query. If you do not link any "assign" inputs then "*" will be used in the query.

  <mapping target="transport:assign_firstname" source="constant:colSelect"/>
  <mapping target="transport:assign_lastname" source="constant:colSelect"/>
  <mapping target="transport:assign_email" source="constant:colSelect"/>

  
  **In this example we map the source to a constant of "1" which is one way of making sure the parameters are used in the query.  The other approach is to use the new parameter called "includeBlankValues", which will force all the "assign_" parameters to be recognized when the SQL query is constructed.

• Link any "where" inputs that you might want to use:

  <mapping target="transport:where_lastname" source="parameter:where_lastname"/>
  <mapping target="transport:whereOp_lastname" source="parameter:whereOp_lastname"/>
  <mapping target="transport:where_firstname" source="parameter:where_firstname"/>
  <mapping target="transport:whereOp_firstname" source="parameter:whereOp_firstname"/>

• The service returns a list, link the outputs to a list object (dropdown, select one/many or table)
• Setup how the service will be called (i.e. on button click, on form load, on value change of an item, etc)
• The service returns the query results and true/false

2. Using Insert

• Create a service description and find the Database Transport - tableName
• The "assign" inputs must be provided as they will determine the columns/values that are updated and they should link to fields on your form
• "where" inputs are not applicable to insert
• The service returns the number of rows affected and true/false

3. Using Update

• Create a service description and find the Database Transport - tableName
• The "assign" inputs must be provided as they will determine the columns that are updated and they should link to fields on your form
• "where" inputs may be used as it limits which records are updated from the table
• The service returns the number of rows affected and true/false

4. Using Delete

• Create a service description and find the Database Transport - tableName
• The "assign" inputs are not applicable
• "where" inputs should be used as it limits what is deleted from the table
• The service returns the number of rows affected and true/false

Code Review

This section is dedicated to showing certain features that you might want to incorporate into your own transports.

1. How to retrieve WAS J2C Alias     

import com.ibm.wsspi.security.auth.callback.Constants;
       import com.ibm.wsspi.security.auth.callback.WSMappingCallbackHandlerFactory;

       import javax.resource.spi.security.PasswordCredential;
       import javax.security.auth.Subject;
       import javax.security.auth.callback.CallbackHandler;
       import javax.security.auth.login.LoginContext;
       import javax.security.auth.login.LoginException;
       . . .

       String j2cAlias = (String) pParameters.get("WAS-J2CAlias");

       //look up J2C Alias from WebSphere to get DB credentials
        if(j2cAlias != null && !j2cAlias.equalsIgnoreCase("")) {
            sLog.log(Level.FINEST, "FOUND J2C ALIAS: " + j2cAlias );
            try {
                Map<String,String> map = new HashMap<String,String>();
                map.put(Constants.MAPPING_ALIAS, j2cAlias);
                
                CallbackHandler cbh = WSMappingCallbackHandlerFactory.getInstance().getCallbackHandler(map, null);
                LoginContext lc = new LoginContext("DefaultPrincipalMapping", cbh);
                lc.login();        
            
                Subject sub = lc.getSubject();
                Set<Object> creds = sub.getPrivateCredentials();
                
                PasswordCredential passwordCred = (PasswordCredential) creds.iterator().next();
                dbUser = passwordCred.getUserName();
                dbPassword = new String(passwordCred.getPassword());
            } catch (LoginException le) {
                sLog.log(Level.WARNING, "ERROR: The defined alias does not exist. (" + j2cAlias + ")");
                sLog.log(Level.WARNING, "ERROR: " + le.getMessage());
            } catch (Exception e) {
                sLog.log(Level.WARNING, "ERROR: " + e.getMessage());
                e.printStackTrace();
            }
        } 

2. How to access a WAS datasource           

import javax.sql.DataSource;

            import javax.naming.InitialContext;

            . . .

            if(dbType.equalsIgnoreCase("DataSource")) {
                sLog.log(Level.FINEST, "ESTABLISHING DB CONNECTION VIA DATASOURCE: " + dbName) ;
                InitialContext ctx = new InitialContext();
                DataSource ds = (DataSource) ctx.lookup(dbName);
                conn = ds.getConnection();
            }

3. How to log within a Transport (log messages will be written to trace.log, when enabled)

 import java.util.logging.Level;
            import java.util.logging.Logger;

            . . .

            private static Logger sLog = Logger.getLogger(DatabaseTransport.class.getName());

            sLog.log(Level.FINEST, "DEBUG:");