** 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
Extension Variables (Input/Ouput Parameters)
Using in your Leap Application
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
v1.6 - May 11 2017
v1.5 - Apr 27 2016
v1.4 - Apr 1 2016
v1.3 - May 8 2015
v1.2 - April16 2014
v1.1 - April 9 2014
v1.0 - April 8 2014
Extension Files
*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
Type | Parameter | Description | |
Input | dbName | The name of the WAS dataSource registered in Resources...JDB...Data Sources. i.e. jdbc/SampleDB *Deprecated* If dbType = ODBC then this should be the name of the configured DSN. | |
Input | dbTable | The name of the table. | |
Input | action | The type of SQL query to be executed. Valid values are insert, select, update, delete. *Disclaimer* - Be careful with delete and update operations. If you do not properly supply a valid where clause then you could update or delete ALL the rows of the table you are accessing. | |
Input | dbType | Valid Value is DataSource. *Deprecated* the previous values, since the individual database drivers are no longer included in the jar file: Oracle, DB2, MySQL, ODBC, MsSQL | |
Input | dbServer | The host name of the database server. | |
Input | dbPort | The port of the database server. (i.e. 50000) | |
Input | dbUser | The User name of the user to connect to the database. *Deprecated* - Use WAS-J2CAlias instead | |
Input | dbPassword | The password of the user to connect to the database. *Deprecated* - Use WAS-J2CAlias instead | |
Input | WAS-J2CAlias | The alias for the user that should be used to connect to the database. Note: You can configure this in the WAS Admin Console: - Security...Global Security - Java Authentication...J2C Authentication Data... - New...Enter the Alias, username and password | |
Input | assign_<columnName> | Defines the columns that will be used in the assignment clause of applicable SQL queries. For example, creating the following parameters assign_name, assign_age would result in : Select name, age from person If you don't supply any then * will be used for the query: Select * from person | |
Input | where_<columnName> | Defines the columns that will be used in the where clause of applicable SQL queries. For example, creating the following parameters where_name would result in: Select * where name = <value> Note: Any parameter that has a value will automatically be added to the transport outbound parameter list. So if you want to return the values of the where parameters as single items outside of the result set then add individual outbound parameters to your outbound mapping. (Added in v 1.3) | |
Input | whereOp_<columnName> | Valid values are LIKE (Added in v1.5), STARTSWITH, and IN (Added in v1.7). Only specify this for the column name if you want to use wild card (LIKE '%x%') instead of an equality (=) search. | |
Input | whereOperator | This determines the relationship between multiple where clauses. There can only be one relationship. Valid values are AND and OR | |
Input | orderBy | The column name to use to sort the results (SELECT only). Required if paginating the results. (Added in v1.5) | |
Input | orderByOp | Valid values are ASC or DESC. Optional, defaults to ASC. (Added in v1.5) | |
Input | pageNum | Paginates the query results. Page number. Pagination may not work with all databases (I have only tested it against DB2), it is using the SQL query like:
(Added in v1.5) | |
Input | itemsPerPage | Paginates the query results. Specify Items per page. (Added in v1.5) | |
Input | includeBlankValues | Valid values are true or false, default is false. All of the "assign_" parameters will be used to build the query even if they are blank. This parameter is critical to a select action where you defined all the "assign_" parameters that you want returned in the query but none of them are actually being used as inputs to the service (where the fields contain values). | |
Input | includeWhereInOut | Valid values are true or false, default is false. If true, the parameters that are defined as "where" parameters will automatically be included in the service results. | |
Output | <columnName> | Each column of the result set will be provided (in uppercase) as an output parameter. | |
Output | success | Returns true if the query is processed without error, otherwise false. | |
Output | rowsAffected | The number of rows affected by the INSERT, DELETE or UPDATE query. For SELECT will contain the number of rows returned. | |
Output | queryResults | The results of the SELECT query. This is a list object where each row contains the columns that were defined by the assignment portion of the query. The returned parameters are in upper case, so your mapping might look something like:
| |
Output | state | The SQL state, only returned if the SQL query fails. | |
Output | message | The SQL message, only returned if the SQL query fails. | |
Output | error | The SQL error code, only returned if the SQL query fails. |
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.
<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> |
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
6. Create J2C Authentication Alias for the user that will be used to connect to the datasource.
7. For troubleshooting, add the trace string com.ibm.support.examples.*=finest to the WebSphere instance.
Using in your Leap Application
1. Using Select.
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"/> |
2. Using Insert
3. Using Update
4. Using Delete
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:"); |