We're upgrading our Content Delivery from 2013 to 8.5, migrating from Oracle DB to MS SQL DB, and so we're also refactoring our custom extensions with the new 8.5 jars. We used the 2013 deployer's tomcat to serve some JSPs, which queried the Broker DB (Oracle Database) using tridion's jars. Since the Content Delivery services in 8.5 uses embedded Tomcat instead of a standard Tomcat installation, we created our own instance of Tomcat 8.5.4 to serve these JSPs using Tridion 8.5 jars, but we can't get it to get data from the Broker DB (now MS SQL Database). In 2013 we used Oracle DB, and we're migrating to MS SQL in the 8.5 upgrade. When the query object calls the executeQuery() method, it always returns null so on the next line it throws a NullPointerException.
<%-- Set the content type header with the JSP directive --%>
<%@ page contentType="text/xml; charset=UTF-8" %>
<%-- Set the content disposition header --%>
<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@ page import="com.tridion.dynamiccontent.*" %>
<%@page import="com.tridion.broker.StorageException,
com.tridion.broker.querying.*,
com.tridion.broker.querying.Query,
com.tridion.broker.querying.criteria.*,
com.tridion.broker.querying.criteria.categorization.*,
com.tridion.broker.querying.criteria.content.*,
com.tridion.broker.querying.criteria.metadata.*,
com.tridion.broker.querying.criteria.operators.*,
com.tridion.broker.querying.criteria.taxonomy.*,
com.tridion.broker.querying.filter.LimitFilter,
com.tridion.broker.querying.sorting.SortParameter,
com.tridion.broker.querying.criteria.metadata.CustomMetaKeyCriteria,
com.tridion.broker.querying.criteria.metadata.CustomMetaValueCriteria,
com.tridion.broker.querying.criteria.operators.AndCriteria,
com.tridion.broker.querying.criteria.operators.OrCriteria,
com.tridion.broker.querying.criteria.operators.NotInCriteria,
java.util.ArrayList,
org.apache.commons.lang.*"%>
<%
String templateUri = "tcm:123-456-78";
int publicationId = 123;
int templateId = 456;
String key = "";
String values[] = null;
StringBuffer sb = new StringBuffer();
String ids = request.getParameter("ids");
String publishedKeys = request.getParameter("publishedKeys");
sb.append("<result>");
if(null != publishedKeys && publishedKeys.indexOf("tcm:") != -1){
ComponentPresentationAssembler cpa = new ComponentPresentationAssembler(publicationId);
if (cpa != null) {
String contentXml = cpa.getContent(publishedKeys, templateUri);
contentXml = contentXml.replace("<tcm:Title>","<tcm:id>"+publishedKeys+"</tcm:id><tcm:Title>");
sb.append(contentXml);
}
}else if((null != publishedKeys && publishedKeys.trim().length() > 0) || (null != ids && ids.trim().length() > 0)){
if(null != ids && ids.trim().length() > 0){
key = "id";
values = ids.split(",");
} else if(null != publishedKeys && publishedKeys.trim().length() > 0){
key = "publishedKey";
values = publishedKeys.split(",");
}
Criteria customKeyCriteria = new CustomMetaKeyCriteria(key);
ArrayList<CustomMetaValueCriteria> criteriaList = new ArrayList<CustomMetaValueCriteria>();
for (String value : values) {
if(key.equals("id")){
criteriaList.add(new CustomMetaValueCriteria(new Float(value)));
}else{
criteriaList.add(new CustomMetaValueCriteria(value));
}
}
Criteria customValueCriteria = new OrCriteria(criteriaList.toArray(new Criteria[values.length]));
Query query = new Query(new AndCriteria(customKeyCriteria, customValueCriteria));
String[] result = query.executeQuery(); // This always returns null, so the next step throws a NullPointerException
for (String uri : result) {
String[] tcmIdArr = uri.split("-");
ComponentPresentationAssembler cpa = new ComponentPresentationAssembler(publicationId);
String tcmKey = tcmIdArr[0].replaceAll("tcm:", "")+tcmIdArr[2]+tcmIdArr[1];
//out.println(String.format("tcmKey " + tcmKey));
boolean validXml= false;
for (String value : values) {
if(value.equals(tcmKey)){
validXml= true;
}
}
if (cpa != null) {
String contentXml = cpa.getContent(Integer.parseInt(tcmIdArr[1]), templateId);
contentXml = contentXml.replace("<tcm:Title>","<tcm:id>"+uri+"</tcm:id><tcm:Title>");
sb.append(contentXml);
}
}
}
sb.append("</result>");
out.print(sb.toString().replaceAll("d4p1","xlink"));
%>
cd_storage_conf.xml
<?xml version="1.0" encoding="UTF-8"?>
<Configuration Version="7.1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="schemas/cd_storage_conf.xsd">
<Global>
<!--Optional:-->
<ObjectCache Enabled="false"> <!--SDLTC:set to true-->
<!--Optional:-->
<Policy Type="LRU" Class="com.tridion.cache.LRUPolicy">
<!--
The maximum size of memory used by the cache. The string must consist a number
followed by units: "k" or "kb" for kilobytes; "m" or "mb" for megabytes (case insensitive)
-->
<Param Name="MemSize" Value="64mb"/>
</Policy>
<!--Optional:-->
<!-- The Features element specifies classes that add additional functionality to the cache. -->
<Features>
<Feature Type="DependencyTracker" Class="com.tridion.cache.DependencyTracker"/>
</Features>
</ObjectCache>
<Storages>
<!--
Optional
Overriding default Tridion dao bundles.
-->
<StorageBindings>
<Bundle src="preview_dao_bundle.xml"/>
<!-- <Bundle src="myDAO.xml"/>
<Bundle src="mybindings.xml"/>
-->
</StorageBindings>
<Wrappers>
<!--
In order to allow storing of the session data a mechanism should be in place to support storing and
retrieving data in a specific user session.
-->
<Wrapper Name="SessionWrapper">
<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" dialect="MSSQL" Id="TRIDIONBROKER_SESSION" JNDIName="java:comp/env/jdbc/TRIDIONBROKER_SESSION"/> <!--SDLTC-->
</Wrapper>
</Wrappers>
<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" dialect="MSSQL" Id="TRIDIONBROKER_STAGE" JNDIName="java:comp/env/jdbc/TRIDIONBROKER_STAGE"/> <!--SDLTC-->
<!--SDLTC-->
<Storage Type="filesystem" Class="com.tridion.storage.filesystem.FSDAOFactory" Id="defaultFile" defaultFilesystem="false">
<Root Path="/jsp-provider/incoming" />
</Storage>
<Storage Type="filesystem" Class="com.tridion.storage.filesystem.FSDAOFactory" Id="defaultDataFile" defaultFilesystem="true" defaultStorage="true">
<Root Path="/jsp-provider/incoming/data" />
</Storage>
</Storages>
</Global>
<ItemTypes defaultStorageId="TRIDIONBROKER_STAGE" cached="false"> <!--SDLTC-->
<Item typeMapping="Page" storageId="defaultFile" cached="false"/> <!--SDLTC-->
<Item typeMapping="Page" storageId="TRIDIONBROKER_STAGE" itemExtension=".json" cached="false"/> <!--SDLTC-->
<Item typeMapping="Binary" storageId="defaultFile" cached="false"/> <!--SDLTC-->
</ItemTypes>
<License Location="/staging/cd_licenses.xml"/> <!--SDLTC-->
</Configuration>
context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources. If one of these changes, the -->
<!-- web application will be reloaded. -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<Resource name="jdbc/TRIDIONBROKER_STAGE" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database1:10433;database=BROKER_LIVE_SAND_EVO;"
username="BrokerUser_Live_Sand" password="secret" maxActive="100" maxIdle="30"
maxWait="30000" validationQuery="select 1"/>
<Resource name="jdbc/TRIDIONBROKER_LIVE" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database2:10433;database=BROKER_PREVIEW_SAND_EVO"
username="BrokerUser_Preview_Sand" password="secret" maxActive="100" maxIdle="30"
maxWait="30000" validationQuery="select 1"/>
<Resource name="jdbc/TRIDIONBROKER_SESSION" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database3.com:10433;database=BROKER_PREVIEW2_SAND_EVO"
username="BrokerUser_Preview2_Sand" password="secret" maxActive="100" maxIdle="30"
maxWait="30000" validationQuery="select 1"/>
</Context>
jars:
activation-1.1.jar
antlr-2.7.7.jar
aopalliance-repackaged-2.5.0-b05.jar
asm-3.2.jar
cd_broker-8.5.0-1014.jar
cd_cache-8.5.0-1013.jar
cd_common_config-8.5.0-1009.jar
cd_common_config_api-8.5.0-1009.jar
cd_common_config_legacy-8.5.0-1009.jar
cd_common_util-8.5.0-1009.jar
cd_core-8.5.0-1011.jar
cd_datalayer-8.5.0-1014.jar
cd_dynamic-8.5.0-1014.jar
cd_model-8.5.0-1013.jar
cd_preview_ambient-8.5.0-1013.jar
cd_preview_content_common-8.5.0-1013.jar
cd_preview_webservice-8.5.0-1013.jar
cd_session-8.5.0-1013.jar
cd_wrapper-8.5.0-1014.jar
classmate-1.3.1.jar
commons-lang-2.6.jar
content-v2-odata-8.5.0-1013.jar
dom4j-1.6.1.jar
easylicense-2.5.jar
geronimo-jta_1.1_spec-1.1.1.jar
hibernate-commons-annotations-5.0.1.Final.jar
hibernate-core-5.2.3.Final.jar
hibernate-jpa-2.1-api-1.0.0.Final.jar
javassist-3.20.0-GA.jar
jboss-logging-3.3.0.Final.jar
jcl-over-slf4j-1.7.12.jar
logback-classic-1.1.7.jar
logback-core-1.1.7.jar
ojdbc6.jar
ojdbc7-12.1.0.2.jar
slf4j-api-1.7.12.jar
spring-aop-4.3.3.RELEASE.jar
spring-beans-4.3.3.RELEASE.jar
spring-context-4.3.3.RELEASE.jar
spring-context-support-4.3.3.RELEASE.jar
spring-core-4.3.3.RELEASE.jar
spring-data-commons-1.12.2.RELEASE.jar
spring-data-jpa-1.10.2.RELEASE.jar
spring-expression-4.3.3.RELEASE.jar
spring-integration-core-4.3.2.RELEASE.jar
spring-integration-jms-4.3.2.RELEASE.jar
spring-jdbc-4.3.3.RELEASE.jar
spring-jms-4.3.3.RELEASE.jar
spring-messaging-4.3.3.RELEASE.jar
spring-orm-4.3.3.RELEASE.jar
spring-plugin-core-1.2.0.RELEASE.jar
spring-plugin-metadata-1.2.0.RELEASE.jar
spring-retry-1.1.4.RELEASE.jar
spring-tx-4.3.3.RELEASE.jar
spring-web-4.3.3.RELEASE.jar
spring-webmvc-4.3.3.RELEASE.jar
sqljdbc4-6.0.jar
stax-api-1.0-2.jar
web-ambient-client-8.5.0-1010.jar
