SQL DataSource Builder
In this topic ...
Related Topics ...
This Builder allows your model (and the other SQL Builders) to obtain database connections from a local or remote JNDI-based DataSource. The DataSource is typically defined and managed by your application server and made available through a JNDI server provided as part of the application server s implementation.
The DataSource created by the application server will usually provide a set of additional features beyond simply handing out JDBC connections for access to a database. Some of those features might include connection pooling, stale connection handling, and connection timeouts.
This Builder provides lots of flexibility by allowing you to access secure JNDI servers where authentication is required. The Builder also allows you to control the flow of your application by specifying various timeout settings that prevent an application for hanging while attempting to acquire the database connection from the DataSource.
This Builder has the following input groups:
- Required - A Builder name and JNDI Data Source name are the only required inputs
- Verify Connectivity - (optional) When enabled this input instructs the Builder to verify that the named DataSource is accessible from the application server on which the model is to be run.
- Remote JNDI Server Definition - (optional) These settings allow you to connect and, if necessary, authenticate to a remote JNDI server. You might need to do this if you are developing a model that will run on a server that does not have a local JNDI server as part of its implementation; in most cases you ll never need to specify these settings.
- DataSource Authentication and Timeouts - (optional) These settings allow you to over-ride the default authentication of the DataSource and specify timeouts for acquiring connections; in most cases you ll never need to specify these settings.
- Events, Statistics and Logging - These settings enable you to collect database-related server statistics and work with database-related events.
How do I...
Connect to a database?
Build an SQL statement?
Cache query results?
Initiate a transaction and display results?
Quick Tips
- Support Multiple Connections -- Create multiple instances of this Builder, each of which defines a different JNDI data source. These data sources will then be available in a model as selectable inputs in other SQL Builders, allowing you to mix-and-match data sources as you build various statements and transforms.
- Using WebSphere 5 Datasources - The Factory supports the use of both version 5 and version 4 data sources. The version we can use, however, depends on the version of J2EE application you are building. The limitations are as follows:
- J2EE 1.2-based applications and portlets - Must use version 4 datasources
- J2EE 1.3-based applications and portlets - Must use version 5 datasources
The Factory cannot mix datasource versions (v4 and v5) in the same project. You must match the appropriate datasource version with the J2EE version you chose when you created the project with the Project Wizard.
Specifying Inputs
The SQL data source Builder takes the inputs described in the table below. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor"
Input Name Description Name Enter a name for this Builder call. The Designer displays this name in the Builder Call List. Fetch DataSource Names Press this button to fetch a list of JDBC DataSources defined on the application server supporting the Designer. The list will be inserted as choices for the "SQL DataSource" input. If no DataSource names are listed in the "SQL DataSource" input, then the Designer may not be properly configured to query an application server, the server might not be running, or there are no DataSources defined by the application server. Any valid datasource name can be typed into the SQL Datasource builder input. The Fetch Datasource Names button, however, will only list those datasources whose full names begin with jdbc/, jdbc/v4/, or jdbc/v5/. If you know that there is a valid datasource with a name that does not match any of these patterns, type the name in. If the name is correct, it should work.
Depending on the performance of the application server, it might take several seconds to load the list of datasource names.
This button is not displayed if the Builder is configured to use an override SQL DataSource. See the section entitled "Using Override Builders" for more information.
Data Source Name
Enter a JNDI DataSource name. The Builder will use this name to locate the DataSource in the JNDI server. This setting is typically something such as: "jdbc/dbname" and it corresponds to the logical name given to the DataSource by the application server where the DataSource was defined. If you're using WebSphere 5, see tip above regarding datasource versions.
Verify Connectivity Enabling this input causes the Builder to verify that the named DataSource is accessible from the application server on which the model is to be run. The verification is performed when the model containing this Builder is regenerated. Remote JNDI Server Definition (optional) JNDI Context Factory The context factory class associated with the remote JNDI server from which the named DataSource is to be obtained. If your application server does not provide a JNDI server, we can use this setting to specify the fully qualified context factory class that is responsible for handling communication with the JNDI server containing the DataSource.
The syntax of this entry depends on the application server or JNDI server you are using. For example: WebSphere: com.ibm.ws.naming.util.WsnInitCtxFactory
JNDI URL This URL identifies the port and server on the network where a JNDI server is listening for requests. For example: WebSphere: iiop://host_name:9001
Principal If the JNDI server requires authentication, use this entry to identify a user who has access to the server and is allowed to perform resource lookups on that server. Application servers handle this input in different ways. For example:
- On WebSphere - The Principal and Credentials inputs you provide in this Builder will override the ones provided by the datasource on the application server.
Credentials If the JNDI server requires authentication, use this entry to provide the password of the user named as Principal above. DataSource Authentication and Timeouts (optional) DataSource User Enter the name of the DataSource user on whose behalf connections are being created. Application servers handle this input in different ways. For example:
- WebSphere - The user name and password you provide in this Builder will override the ones provided by the DataSource on the application server.
DataSource Password Enter the password of the DataSource user. The note described for the DataSource User input also applies to the DataSource Password input.
Login Timeout Enter the maximum time (in seconds) that the DataSource will wait while attempting to establish a connection to the database. Acquire Timeout Enter the maximum time (in seconds) to wait for a connection to be returned from the DataSource. If a connection cannot be returned in the specified amount of time, then a WebAppRuntimeException will be thrown by the Builder.
Events, Statistics and Logging (optional) Log Server Stats Enable this box to generate server statistics for this Builder instance. Fire Acquire Events Enable this box to fire an event when a connection is acquired from the DataSource. Fire Release Events Enable this box to fire an event when a connection is released to the DataSource.
Tracking Database Events
This Builder can fire the following database-related events in the model:
- Release Event - This event provides a way for a model to manipulate a connection before it is returned to the DataSource for disposal. You could use this event to perform a commit or rollback on a connection to ensure that uncommitted changes to the database are handled if your model fails to do so in other ways.
- Acquire Event - This event provides a way for a model to manipulate a connection before it is handed off to other Builders in the model. You could use this event to consistently set various parameters of a connection before it s used to access the database.
When the Builder is configured to fire events it will create and register in the model an event named SqlDataSourceBuilderEvent. This name then becomes available as an option for the "Event Name" input of an Event Handler Builder. When the Builder fires this event at run-time it passes three parameters that can be used by your event handler action to manipulate the JDBC connection just acquired or about to be released. Those parameters are:
- BuilderName - This string parameter will contain the name of the SQL DataSource Builder that fired the acquire or release event. Since all DataSource Builders in your model use the SqlDataSourceBuilderEvent event name, this input allows you to discriminate among all of the DataSource Builders when handling events.
- eventType - This string parameter indicates what type of event was fired. The two possible values are "Acquire" and "Release."
- connection - This object parameter is the actual JDBC connection that was either acquired from the Builder (eventType equals "Acquire") or is about to be released (eventType equals "Release") to the DataSource for disposal.