Appboard/old/database data adapters: Difference between revisions

imported>Cmace
(copied from internal)
 
imported>Jason.nicholls
No edit summary
 
(6 intermediate revisions by 4 users not shown)
Line 1: Line 1:
There are three database data adapters to choose from: JDBC, [[SQL_Query_Adapter|JDBC SQL]], and MySQL.  As each type of data adapter will require different information to connect to AppBoard, this page will assist you in understanding some of the specific options associated with '''database''' data adapters.  For information on options used to connect a database data adapter, [[#Data Source Wizard - Connect|skip to the Connect step]].
{{DISPLAYTITLE:Database Data Adapters}}
[[Category:AppBoard old]]
There are three database data adapters to choose from: JDBC, [[appboard/old/sql_query_adapter|JDBC SQL]], and MySQL.  As each type of data adapter will require different information to connect to AppBoard, this page will assist you in understanding some of the specific options associated with '''database''' data adapters.  For information on options used to connect a database data adapter, [[#Data Source Wizard - Connect|skip to the Connect step]].




Line 15: Line 17:


===Why use a JDBC SQL query adapter?===
===Why use a JDBC SQL query adapter?===


When using the basic JDBC data adapter, you are presented with the visible tables and columns within those tables with little control over how they are formatted or filtered.  This is quick and easy when the data exists in the form it is needed, but often there is a need for more control.  Examples of situations where the JDBC SQL adapter is required include:
When using the basic JDBC data adapter, you are presented with the visible tables and columns within those tables with little control over how they are formatted or filtered.  This is quick and easy when the data exists in the form it is needed, but often there is a need for more control.  Examples of situations where the JDBC SQL adapter is required include:
Line 24: Line 25:
*the raw data set is too large to handle in memory and you need to select a dynamic subset of that data
*the raw data set is too large to handle in memory and you need to select a dynamic subset of that data


=== Using the JDBC SQL adapter with a Netcool Objectserver ===
This section details how to create a JDBC SQL Data Source that connects to a Netcool Objectserver:
First, save the following code into a text file called trimAllNulls.groovy:
import com.edgetech.services.ServiceRequestFacade
import com.edgetech.services.ServiceResponseFacade
import com.edgetech.services.GenericRecord
import com.edgetech.services.Association
import com.edgetech.services.impl.ServiceRequestHelper
import com.edgetech.services.model.AttributeType
import java.util.Collection;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
ServiceResponseFacade serviceResponse = responseFacade;
ServiceRequestFacade serviceRequest = requestFacade;
// this Groovy script intended for use with Netcool Objectserver JDBC sources to
// automatically strip the null termination from all STRING type fields.
Collection recordsToClean = serviceResponse.getOrderedGenericRecords();
if ( ( recordsToClean != null ) && !recordsToClean.isEmpty() ) {
for ( GenericRecord recordToClean:recordsToClean ) {
Collection<String> attributeNames = recordToClean.getAttributeNames();
for ( String attributeName:attributeNames) {
AttributeType type = recordToClean.getAttributeType(attributeName);
if ( type.equals(AttributeType.STRING) ) {
String value = recordToClean.getAttributeValueAsString(attributeName);
if ( value != null ) {
if ( value.endsWith("\0")) {
recordToClean.setAttributeValue(attributeName,value.substring(0,value.length()-1));
}
}
}
}
}
}
Next, perform the following steps to configure the Data Source:
# Save the above Groovy script in a text file and place the file in the <tt>/server/webapps/enportal/WEB-INF/groovy-script/custom/</tt> directory.
# Log in to AppBoard builder as an administrator.
# Create a Data Source and select the JDBC SQL adapter.
# select the "SQLAnywhere" template.
# Set the URL to something like <tt>jdbc:sybase:Tds:localhost:4100/NCOMS?charset=iso_1</tt> (note the charset change).
# Change the driver setting from <tt>com.sybase.jdbc3.jdbc.SybDriver</tt> to <tt>com.sybase.jdbc2.jdbc.SybDriver</tt>.
# Write the query you want using Objectserver SQL.
# On the next tab, select the result table and click the Advanced button in the lower left.
# Click "add script" in the Script Definitions box.
# Enter <tt>custom/trimAllNulls.groovy</tt> in the path box and set "REFRESH" for the "Runs on" option.
# Complete the Data Source wizard.




Line 54: Line 109:
|'''password'''
|'''password'''
|User password credential used to connect to database.
|User password credential used to connect to database.
|-
|'''poolsize'''
|Number of connections to pool.  This adapter re-uses connections for efficiency.
|-
|-
|'''timeout'''
|'''timeout'''
|Time to wait before timing out on connecting to the datasource.
|Time to wait before timing out on connecting to the datasource.
|-
|-
|'''cacheTimeout'''
|'''[[appboard/old/caching_and_polling|cacheTimeout]]'''
|Time limit before re-loading results from the database.
|Time limit before re-loading results from the database.
|-
|-

Latest revision as of 16:36, 17 July 2014

There are three database data adapters to choose from: JDBC, JDBC SQL, and MySQL. As each type of data adapter will require different information to connect to AppBoard, this page will assist you in understanding some of the specific options associated with database data adapters. For information on options used to connect a database data adapter, skip to the Connect step.


JDBC

This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. Equivalent to executing "select * from <table_name>" for the selected tables. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, Postgres SQL Hypersonic (HSQLDB), H2, MySQL, and DB2. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.


JDBC SQL

This adapter retrieves data from a RDBMS using SQL queries to create a single data set. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, Postgres SQL Hypersonic (HSQLDB), H2, MySQL, and DB2. This is typically used when a JOIN is required to produce a more appropriate view of the data (without unnecessary associations) or for performance reasons.


Why use a JDBC SQL query adapter?

When using the basic JDBC data adapter, you are presented with the visible tables and columns within those tables with little control over how they are formatted or filtered. This is quick and easy when the data exists in the form it is needed, but often there is a need for more control. Examples of situations where the JDBC SQL adapter is required include:

  • the required data comes from a SQL stored procedure
  • some SQL calculations need to be done (eg. date calculations, etc)
  • complex joins need to be done in the source
  • the raw data set is too large to handle in memory and you need to select a dynamic subset of that data


Using the JDBC SQL adapter with a Netcool Objectserver

This section details how to create a JDBC SQL Data Source that connects to a Netcool Objectserver:


First, save the following code into a text file called trimAllNulls.groovy:

import com.edgetech.services.ServiceRequestFacade
import com.edgetech.services.ServiceResponseFacade
import com.edgetech.services.GenericRecord
import com.edgetech.services.Association
import com.edgetech.services.impl.ServiceRequestHelper
import com.edgetech.services.model.AttributeType
import java.util.Collection;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
ServiceResponseFacade serviceResponse = responseFacade;
ServiceRequestFacade serviceRequest = requestFacade;
// this Groovy script intended for use with Netcool Objectserver JDBC sources to
// automatically strip the null termination from all STRING type fields.
Collection recordsToClean = serviceResponse.getOrderedGenericRecords();
if ( ( recordsToClean != null ) && !recordsToClean.isEmpty() ) {
	for ( GenericRecord recordToClean:recordsToClean ) {
		Collection<String> attributeNames = recordToClean.getAttributeNames();
		for ( String attributeName:attributeNames) {
			AttributeType type = recordToClean.getAttributeType(attributeName);
			if ( type.equals(AttributeType.STRING) ) {
				String value = recordToClean.getAttributeValueAsString(attributeName);
				if ( value != null ) {
					if ( value.endsWith("\0")) {
						recordToClean.setAttributeValue(attributeName,value.substring(0,value.length()-1));
					}
				}
			}
		}
	}
}


Next, perform the following steps to configure the Data Source:

  1. Save the above Groovy script in a text file and place the file in the /server/webapps/enportal/WEB-INF/groovy-script/custom/ directory.
  2. Log in to AppBoard builder as an administrator.
  3. Create a Data Source and select the JDBC SQL adapter.
  4. select the "SQLAnywhere" template.
  5. Set the URL to something like jdbc:sybase:Tds:localhost:4100/NCOMS?charset=iso_1 (note the charset change).
  6. Change the driver setting from com.sybase.jdbc3.jdbc.SybDriver to com.sybase.jdbc2.jdbc.SybDriver.
  7. Write the query you want using Objectserver SQL.
  8. On the next tab, select the result table and click the Advanced button in the lower left.
  9. Click "add script" in the Script Definitions box.
  10. Enter custom/trimAllNulls.groovy in the path box and set "REFRESH" for the "Runs on" option.
  11. Complete the Data Source wizard.


MySQL

This adapter retrieves a filtered set of tables from a MySQL RDBMS to create multiple Data Collections and is provided as a secondary adapter to the JDBC adapter. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.


Data Source Wizard - Connect

The Data Source Wizard begins with the "Connect" step. Here you must decide on the template for your data source, and fill in the necessary information. These are the possible boxes you will need to fill in for a database data source:


Name Description
url The JDBC connection string/URL for defining the connection. This typically contains information about the database server and name, the user id, a password for connecting to the database, and an Oracle SID at the end which uniquely identifies the name of a particular database to connect to. Example: jdbc:mysql://myservername:3306/mydbname. The default connection string provided when you create the JDBC/Oracle adapter Data Source in AppBoard is the following: jdbc:oracle:thin:@localhost:1521:portal. Note that the sample URL string contains an Oracle SID at the end ("portal"), which uniquely identifies the name of a particular database to connect to. It is recommended that you make sure the connection string includes this SID so the connection can be made between AppBoard and the database. You may need to ask the database or application administrator to provide the Oracle SID for the database. Example Oracle URL: jdbc:oracle:thin:@servername:1521:OracleSID
driver The Java class name for the JDBC driver. Example: oracle.jdbc.driver.OracleDriver
username User name credential used to connect to database.
password User password credential used to connect to database.
timeout Time to wait before timing out on connecting to the datasource.
cacheTimeout Time limit before re-loading results from the database.
catalogName Database catalog name. It is not required, used to limit the set of tables found on the search.
schemaName The database schema name. It is not required, used to limit the set of tables found on the search.
tableNameRegex Regular expression that defines the database tables that will be returned by the adapter. It is not required, but may be used to limit the set of tables found on the search. For example, if you had a couple of tables called Services and Locations, a useful regex to enter in that box would be “(Services|Locations)” - without the quotes. This is case-sensitive, and would match tables with names such as ServicesA, ServicesUS, LocationsB, or any table that contains either "Services" or "Locations" somewhere in the name. If you made the regex "^(Services|Locations)$" then it would only match exactly those names since the ^ and $ anchor the beginning and the end of the string.
sqlQuery SQL query statement to be run on the database. Typically used when it is necessary to perform more advanced JOINS to produce a more appropriate view of the dataset.