14.1 Database Related Issues
- Billy Sewell (Unlicensed)
On This Page:
The ServiceManager application works partially by interacting directly with the ServiceOptimizer database, and partially via the SOAP APIs into the ServiceOptimizer Server. This page covers a number of previously experienced issues with the former type of connection. If you have such an issue reported to you, please check within this section for a Reason/Resolution before reporting to the ServicePower Support Team.
MSSQL Database Connectivity
The Problem
ServiceManager fails to start up correctly because of incorrect database specification. Inspection of the console log will show the fundamental problem.
| Invalid Data in Connection String | A stack track, similar to that below, will be seen. - using driver: net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://rubbish-host:1433;databaseName=development
- connection properties: {user=testdbuser, password=****}
- Could not obtain connection metadata
java.sql.SQLException: <error message>
at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:364)
at
...
com.servicepower.servicemanager.dao.HibernateSessionFactoryProductionImpl.setSessionFactory(HibernateSessionFactoryProductionImpl.java:162)
...
SERVICEManager: init - ended: unsuccessfully
with java.sql.SQLException: <error message> being replaced with something along the lines of:
| ||||||||
|---|---|---|---|---|---|---|---|---|---|
Invalid Database Driver | - JDBC Driver class not found: net.sourceforge.jtds.jdbc.rubbish
java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.rubbish at
org.apache.catalina.loader.WebappClassLoader. (WebappClassLoader.java:1352)
at
...
com.servicepower.servicemanager.dao.HibernateSessionFactoryProductionImpl.setSessionFactory(HibernateSessionFactoryProductionImpl.java:162)
...
| ||||||||
Invalid Hibernate SQL Dialect | - using driver:net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://jodd:1433;databaseName=development
- connection properties: {user=testdbuser, password=****}
- RDBMS:Microsoft SQL Server, version: 08.00.0760
- JDBC driver:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase, version: 1.2
%%%% Error Creating SessionFactory %%%%
org.hibernate.HibernateException: Dialect class not found: org.hibernate.dialect.SQLServerDialect-unknown
at
org.hibernate.dialect.DialectFactory.buildDialect(DialectFactory.java:81)
...
|
The Reason
ServiceManager relies on accurate and correct set up of its database parameters to execute correctly, if these are not correct when ServiceManager attempts to connect to the database it will fail.
The Resolution
ServiceManager requires a valid specification of its database instance. The above stack traces clearly show the problems that can occur when failing to configure the MSSQL database connection appropriately.
To resolve: terminate Tomcat and edit the hibernate.cfg.xml file and specify the correct connection string:
<session-factory>
<property name="connection.url">
jdbc:jtds:sqlserver://svm:1433;databaseName=development
</property>
<property name="connection.driver_class">
net.sourceforge.jtds.jdbc.Driver
</property>
<property name="connection.username">testdbuser</property>
<property name="connection.password">test</property>
<property name="dialect">
org.hibernate.dialect.SQLServerDialect
</property>
...
</session-factory>
MSSQL Trusted Connection Database Connectivity
The Problem
The customer does not wish to include database username and password details within the ServiceManager configuration files but wishes to leverage the MSSQL's support for single sign-on (SSO) based trusted connections.
The Reason
The default installation of ServiceManager does not support trusted database connections between it and MSSQL database. An amount of manual configuration is required to resolve this issue.
The Resolution
MSSQL trusted connection is implemented through the use of Windows SSO. Once configured ServiceManager will use Windows SSO to authenticate the user under whose account the SERVICEManager/Tomcat instance is being executed. For instructions on activating SSO see MSSQL SSO Configuration.
Oracle Database Connectivity
The Problem
ServiceManager fails to start up correctly because of incorrect database specification. Inspection of the console log will show the fundamental problem.
Invalid Data in Connection String | A stack track, similar to that below, will be seen. - using driver:oracle.jdbc.driver.OracleDriver at URL: dbc:oracle:thin:@unknown:1521:ora112
- connection properties: {user=test, password=****}
- Could not obtain connection metadata
java.sql.SQLException: <error message>
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
...
with java.sql.SQLException: <error message> being replaced with something along the lines of:
| ||||||||
|---|---|---|---|---|---|---|---|---|---|
Invalid Database Driver | - JDBC Driver class not found: oracle.jdbc.driver.OracleDriver-rubbish java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver-rubbish at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1352) ... | ||||||||
Invalid Hibernate SQL Dialect | - using driver:oracle.jdbc.driver.OracleDriver at URL: jdbc:oracle:thin:@jodd:1521:ora112
- connection properties: {user=jodd_v61, password=****}
- RDBMS: Oracle,version: Oracle11i Enterprise Edition Release 11.2.0.1.0 - Production With the OLAP and Oracle Data Mining options
JServer Release 11.2.0.1.0 - Production
- JDBC driver:Oracle JDBC driver, version: 11.2.0.1.0
%%%% Error Creating SessionFactory %%%%
org.hibernate.HibernateException: Dialect class not found: org.hibernate.dialect.OracleDialect-rubbish
at
org.hibernate.dialect.DialectFactory.buildDialect(DialectFactory.java:81)
...
|
The Reason
ServiceManager relies on accurate and correct set up of its database parameters to execute correctly, if these are not correct when ServiceManager attempts to connect to the database it will fail.
The Resolution
ServiceManager requires a valid specification of its database instance. The above stack traces clearly show the problems that can occur when failing to configure the Oracle database connection appropriately.
<session-factory> <property name="connection.url"> jdbc:oracle:thin:@jodd:1521:ora112 </property> <property name="connection.username">test</property> <property name="connection.password">test</property> <property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </property> <property name="dialect"> org.hibernate.dialect.OracleDialect </property> </session-factory>