Appboard/2.4/builder/data sources/database - Revision history

imported>Mike.berman at 20:00, 4 February 2014

2014-02-04T20:00:24Z

← Older revision		Revision as of 20:00, 4 February 2014
Line 200:		Line 200:

	=== Missing JDBC driver ===		=== Missing JDBC driver ===

			==== Case 1: Adapter JAR File Missing from Classpath ====

	The most common issue when connecting to a database is a missing JDBC driver. Within the builder this may appear as an error message showing "Data was not found." when progressing from the "Connect" step in the Data Source Wizard.		The most common issue when connecting to a database is a missing JDBC driver. Within the builder this may appear as an error message showing "Data was not found." when progressing from the "Connect" step in the Data Source Wizard.
Line 209:		Line 211:

	See the [[appboard/2.4/admin/db_drivers\|Loading Database Drivers]] page for more information.		See the [[appboard/2.4/admin/db_drivers\|Loading Database Drivers]] page for more information.


			==== Case 2: Security Issue With /servlet Path ====

			The use of custom JDBC addapters is also supported. You can add your own JDBC driver to the J2EE classpath, and save the driver JAR file under [AppBoard_Home]/server/webapps/enportal/WEB-INF/lib/. In this case, if the "missing JDBC driver" message is displayed, it could be a security conflict caused by the JAR adapter having a /servlet/ directory. This is redundant with the AppBoard Tomcat configuration. Delete the /servlet/ directory from the JAR.


	=== Incorrect or Unexpected Date/Time Values and Formatting ===		=== Incorrect or Unexpected Date/Time Values and Formatting ===

imported>Mike.berman at 22:10, 16 January 2014

2014-01-16T22:10:13Z

← Older revision		Revision as of 22:10, 16 January 2014
Line 213:		Line 213:

	Occasionally, date and time values from a database will not appear in AppBoard with the correct value or format when using the default settings in configuring the data source. One of the difficulties in handling date and time values from various types of databases is that not all database types handle date and time data in the same manner. It is possible, however, to adjust certain settings within AppBoard and the database itself to achieve better results. For more information on this topic, please see [[appboard/2.4/builder/data_sources/database/date_time_settings\|Advanced Settings for Date and Time Data]].		Occasionally, date and time values from a database will not appear in AppBoard with the correct value or format when using the default settings in configuring the data source. One of the difficulties in handling date and time values from various types of databases is that not all database types handle date and time data in the same manner. It is possible, however, to adjust certain settings within AppBoard and the database itself to achieve better results. For more information on this topic, please see [[appboard/2.4/builder/data_sources/database/date_time_settings\|Advanced Settings for Date and Time Data]].

			===Empty Values in MySQL Date/Time Fields ===
			*<b>Issue:</b>: Java cannot manage '00-00-00 00:00:00' in MySQL datetime fields. Even if this kind of datetime value is not supported within MySQL operations, values are allowed in general and used by some implementations as default for empty datetime values. The issue can result in many messages in Appboard error.log.
			*<b>Resolution</b>: Convert the zero values to NULL values within the data-source. Example url: <tt>jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull</tt>

imported>David.moore: /* Database Query */

2013-12-31T16:27:59Z

Database Query

← Older revision		Revision as of 16:27, 31 December 2013
Line 145:		Line 145:
	\|-		\|-
	\|'''Name'''		\|'''Name'''
	\|Name of this SQL query entity. This field is limited to 256 characters. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts.		\|Name of this SQL query entity. This field is limited to 256 characters. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts. '''Note: This field is required.'''
	\|-		\|-
	\|'''Description'''		\|'''Description'''
Line 154:		Line 154:
	\|-		\|-
	\|'''SQL Query'''		\|'''SQL Query'''
	\|SQL statement defining the records to be returned by the query. The SQL Query can contain a maximum of 8000 characters.		\|SQL statement defining the records to be returned by the query. The SQL Query can contain a maximum of 8000 characters. '''Note: This field is required.'''
	\|-		\|-
	\|'''Update Statement'''		\|'''Update Statement'''

imported>David.moore: /* Database Query */

2013-12-23T19:13:03Z

Database Query

← Older revision		Revision as of 19:13, 23 December 2013
Line 145:		Line 145:
	\|-		\|-
	\|'''Name'''		\|'''Name'''
	\|Name of this SQL query entity. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts.		\|Name of this SQL query entity. This field is limited to 256 characters. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts.
	\|-		\|-
	\|'''Description'''		\|'''Description'''
	\|A summary of the purpose of the query and any details or notes to provide for the system administrator		\|A summary of the purpose of the query and any details or notes to provide for the system administrator. The Description field has a character limit of 512 characters.
	\|-		\|-
	\|'''[[appboard/2.4/builder/caching_and_polling\|Cache Timeout (seconds)]]'''		\|'''[[appboard/2.4/builder/caching_and_polling\|Cache Timeout (seconds)]]'''
Line 154:		Line 154:
	\|-		\|-
	\|'''SQL Query'''		\|'''SQL Query'''
	\|SQL statement defining the records to be returned by the query		\|SQL statement defining the records to be returned by the query. The SQL Query can contain a maximum of 8000 characters.
	\|-		\|-
	\|'''Update Statement'''		\|'''Update Statement'''
	\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>		\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>. The Update Statement can contain a maximum of 1024 characters.
	\|-		\|-
	\|'''[[appboard/2.4/builder/data_processing_scripts\|Data Processing Script(s)]]'''		\|'''[[appboard/2.4/builder/data_processing_scripts\|Data Processing Script(s)]]'''

imported>David.moore at 15:57, 20 December 2013

2013-12-20T15:57:44Z

← Older revision		Revision as of 15:57, 20 December 2013
Line 198:		Line 198:

	== Troubleshooting ==		== Troubleshooting ==

			=== Missing JDBC driver ===

	The most common issue when connecting to a database is a missing JDBC driver. Within the builder this may appear as an error message showing "Data was not found." when progressing from the "Connect" step in the Data Source Wizard.		The most common issue when connecting to a database is a missing JDBC driver. Within the builder this may appear as an error message showing "Data was not found." when progressing from the "Connect" step in the Data Source Wizard.
Line 207:		Line 209:

	See the [[appboard/2.4/admin/db_drivers\|Loading Database Drivers]] page for more information.		See the [[appboard/2.4/admin/db_drivers\|Loading Database Drivers]] page for more information.

			=== Incorrect or Unexpected Date/Time Values and Formatting ===

			Occasionally, date and time values from a database will not appear in AppBoard with the correct value or format when using the default settings in configuring the data source. One of the difficulties in handling date and time values from various types of databases is that not all database types handle date and time data in the same manner. It is possible, however, to adjust certain settings within AppBoard and the database itself to achieve better results. For more information on this topic, please see [[appboard/2.4/builder/data_sources/database/date_time_settings\|Advanced Settings for Date and Time Data]].

imported>Jason.nicholls at 07:03, 14 December 2013

2013-12-14T07:03:08Z

← Older revision		Revision as of 07:03, 14 December 2013
Line 1:		Line 1:
	{{DISPLAYTITLE:Database Data Adapters}}		{{DISPLAYTITLE:Database Data Adapters}}
			[[Category:AppBoard 2.4]]
	This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.4/builder/data_sources\|AppBoard Data Sources]]		This page provides details for configuring database adapters in AppBoard. For information on creating the database adapter, or other AppBoard adapters, see [[appboard/2.4/builder/data_sources\|AppBoard Data Sources]]

imported>Jason.nicholls: /* Database Query */

2013-10-15T07:53:46Z

Database Query

← Older revision		Revision as of 07:53, 15 October 2013
Line 144:		Line 144:
	\|-		\|-
	\|'''Name'''		\|'''Name'''
	\|Name of the ~~query~~ to ~~be displayed~~ in the ~~name of~~ the ~~resulting Data Collection~~		\|Name of this SQL query entity. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts.
	\|-		\|-
	\|'''Description'''		\|'''Description'''

imported>Jason.nicholls: /* Database Query */

2013-10-02T06:06:45Z

Database Query

← Older revision		Revision as of 06:06, 2 October 2013
Line 158:		Line 158:
	\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>		\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>
	\|-		\|-
	\|'''Data Processing Script(s)'''		\|'''[[appboard/2.4/builder/data_processing_scripts\|Data Processing Script(s)]]'''
	\|List of one or more Data Processing Scripts to be run on the query.		\|List of one or more Data Processing Scripts to be run on the query.
	\|}		\|}

imported>Jason.nicholls: /* Database Query */

2013-10-02T05:50:22Z

Database Query

← Older revision		Revision as of 05:50, 2 October 2013
Line 148:		Line 148:
	\|'''Description'''		\|'''Description'''
	\|A summary of the purpose of the query and any details or notes to provide for the system administrator		\|A summary of the purpose of the query and any details or notes to provide for the system administrator
			\|-
			\|'''[[appboard/2.4/builder/caching_and_polling\|Cache Timeout (seconds)]]'''
			\|Maximum time a response from the database is kept before discarding.
	\|-		\|-
	\|'''SQL Query'''		\|'''SQL Query'''
Line 154:		Line 157:
	\|'''Update Statement'''		\|'''Update Statement'''
	\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>		\|Used in conjunction with the [[appboard/2.4/builder/widgets/actions/write_static_value\|Write Static Value]] Action to write back to the original Data Source. Example: <tt>update asset set status=:status where assetName=:assetName</tt>
	\|-
	~~\|'''[[appboard/2.4/builder/caching_and_polling\|Cache Timeout (seconds)]]'''~~
	~~\|Time limit before re-loading results of the query from the database.~~
	\|-		\|-
	\|'''Data Processing Script(s)'''		\|'''Data Processing Script(s)'''

imported>Jason.nicholls at 05:43, 2 October 2013

2013-10-02T05:43:23Z

← Older revision		Revision as of 05:43, 2 October 2013
Line 25:		Line 25:

	In many cases, you will want to pass a parameter value into a query rather than using a static query definition. AppBoard provides a system of variable expressions, called SHIM, that can be used for this purpose. For instructions on this option, see [[appboard/2.4/builder/shim_expressions\|SHIM Expressions]].		In many cases, you will want to pass a parameter value into a query rather than using a static query definition. AppBoard provides a system of variable expressions, called SHIM, that can be used for this purpose. For instructions on this option, see [[appboard/2.4/builder/shim_expressions\|SHIM Expressions]].


	== Database Data Source Configuration ==		== Database Data Source Configuration ==
Line 46:		Line 47:
	* SQL Server		* SQL Server

			=== Configure Database Connection Settings ===

	~~=== Configure Database Connection Settings ===~~
	The following tables detail the settings available in the database adapters.		The following tables detail the settings available in the database adapters.

Line 101:		Line 102:

	You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.		You can configure one or more queries in each Database Query adapter configuration. Each configured query will have a different name, and result in a different Data Collection with that name. When naming a query, only alphanumeric characters, underscores, and hyphens are permitted.


	The table below details the settings you can configure for a <b>Database Query</b> Data Source:		The table below details the settings you can configure for a <b>Database Query</b> Data Source:
Line 194:		Line 194:

	In order to configure an connection setting on a particular adapter or SQL driver, specify the setting as '<namespace>'.'<setting>' where '<namespace>' corresponds to the fully qualified name of your adapter or SQL driver followed by the configuration parameter name above (omitting "appboard.jdbc").		In order to configure an connection setting on a particular adapter or SQL driver, specify the setting as '<namespace>'.'<setting>' where '<namespace>' corresponds to the fully qualified name of your adapter or SQL driver followed by the configuration parameter name above (omitting "appboard.jdbc").


	== Troubleshooting ==		== Troubleshooting ==