SQLBuilder Configuration (Configure JDeveloper to work with MS SQL Server)

This blog post is written to show how we can solve errors with SQLBuilder in ADF application on JDeveloper and WebLogic Server (the example uses MS SQL Server for a database). These errors usually appear when we use a database different from the Oracle Databases. For example:

java.lang.ClassCastException:
weblogic.jdbc.wrapper.PoolConnection_weblogic_jdbc_sqlserverbase_ddah cannot be cast
tooracle.jdbc.OracleConnection

To solve this issue we need to do two simple things – create a listener-class (InitializationListener) and register a listener for this class in the web.xml file. Here`s how the solution is describes in the Oracle’s documentation.

Configure Runtime SQL Generation

Before running your application, you must specify a system property to generate the correct flavor of SQL at runtime. You do this by specifying jbo.SQLBuilder as a system property with the correct value for the database you are using. See the following table: 

You can specify the system property as an operating system environment variable or Java command line option, but the recommended approach is to set up a web application filter, using the steps below.

1. Create a Servlet Context Listener

Create a new class in your application and add code to set the jbo.SQLBuilder property to the correct value for your database. This example is for SQL Server:

[java]
package myproject.common; // Substitute your package name

import java.security.AccessController;
import java.security.PrivilegedAction;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import oracle.jbo.common.PropertyMetadata;

public class InitializationListener implements ServletContextListener {

private ServletContext context = null;

public void contextInitialized(ServletContextEvent event) {
context = event.getServletContext();
AccessController.doPrivileged(new
PrivilegedAction() {
public Object run() {
// Set the SQL Builder to the correct value for your database, SQL Server in this example.
System.setProperty(PropertyMetadata.PN_SQLBUILDERIMPL.getName(),
"SQLServer");
return null;
}
});
}

public void contextDestroyed(ServletContextEvent event) {
context = event.getServletContext();
}
}
[/java]

2. Set up a Web Application Filter Edit your application’s web.xml file and add <listener> tags containing your servlet context listener class. This causes your listener to be executed every time the application starts up.

[xml]
<listener>
<listener-class>myproject.common.InitializationListener</listener-class>
</listener>
[/xml]

Note: The application module’s configuration has ajbo.SQLBuilder property, but setting it has no effect. See the topic below.

jbo.sqlbuilder property as an application module configuration option fails (9018765)
An ADF application module configuration (bc4j.xcfg file) can define a database connection other than Oracle database type, such as SQLServer, IBM DB2, or MySQL. By default the SQL style will be “Oracle” for all database connections and should be modified to manually override the SQL style with one that supports the chosen database vendor. The bc4j.xcfg file provides thejbo.SQLBuilder property for this purpose, but the property is ignored and CRUD operations performed on the application module will result in the exception:

JBO-30003: The application pool (oracle.jbo.server.uniqueid.RowIdAMShared)
failed to checkout an application module
due to the following exception:
java.lang.ClassCastException:
com.microsoft.sqlserver.jdbc.SQLServerConnection
cannot be cast to
oracle.jdbc.OracleConnection at oracle.jbo.server.OracleSQLBuilderImpl.
setSessionTimeZone(OracleSQLBuilderImpl.java:5128)
...

The workaround is to specify jbo.SQLBuilder as a system property instead of an application module configuration option. This will cause the application module to pick up the value from the system properties instead of using the default value (Oracle). However, specifying the system property as an  OS environment  variable or Java command line option comes with high administration overhead. The best solution is to set up a web application filter in the web.xml deployment descriptor and define the system property there. For example, the servlet context listener in the following sample will set the system property for the web application.

[java]
import java.security.AccessController;
import java.security.PrivilegedAction;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import oracle.jbo.common.PropertyMetadata;

public class InitializationListener implements ServletContextListener {
private ServletContext context = null;

public void contextInitialized(ServletContextEvent event) {
context = event.getServletContext();

AccessController.doPrivileged(
new PrivilegedAction() {
public Object run() {
System.setProperty(PropertyMetadata.PN_SQLBUILDERIMPL.getName(), "SQLServer");
return null;
}
});
}

public void contextDestroyed(ServletContextEvent event) {
context = event.getServletContext();
}
}
[/java]

* You can check the SQL Flavor and Type Map opening project properties and navigate to the Business Components.

Useful resources:

https://www.oracle.com/technetwork/developer-tools/jdev/multidatabaseapp-085183.html

https://www.oracle.com/technetwork/developer-tools/jdev/knownissues-097654.html#bc3

What is your usual approach, share in the comments below.