Hi,
I am trying to refactor my SpringBoot application and replace using XMLType in favor of SQLXML as some of the methods related to XMLType are already deprecated and I want to rewrite it.
I found in the documentation that I should - instead of deprecated:
Document doc = xmlOut.getDocument();
use:
DOMSource domSource = sqlxml.getSource (DOMSource.class);
Document document = (Document) domSource.getNode();
But I have a problem with that. When I try to do this, I get an exception.
Here is fragment of my code:
String xmlInStr;
XMLType xmlIn = null;
XMLType xmlOut = null;
SQLXML sqlxmlIn = null;
SQLXML sqlxmlOut = null;
OracleConnection conn = null;
Connection hikariConn = null;
(...)
try {
hikariConn = jdbcTemplate.getDataSource().getConnection();
sqlxmlIn = hikariConn.createSQLXML();
sqlxmlIn.setString(xmlInStr);
logger.debug("Input xml has been set.");
// input params
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("getQuestionnaireByType")
.withReturnValue()
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlOutParameter("RETURN",OracleTypes.SQLXML),
new SqlParameter("vLang",OracleTypes.VARCHAR),
new SqlParameter("vRefId",OracleTypes.VARCHAR),
new SqlParameter("vSrcId",OracleTypes.VARCHAR),
new SqlParameter("vUserId",OracleTypes.VARCHAR),
new SqlParameter("vQueType",OracleTypes.VARCHAR),
new SqlParameter("vXmlDataIn",OracleTypes.SQLXML)
)
;
jdbcCall.setAccessCallParameterMetaData(false);
jdbcCall.setReturnValueRequired(true);
jdbcCall.withSchemaName("atr_adap");
SqlParameterSource in = new MapSqlParameterSource()
.addValue("vLang", rhData.getLocale().getLanguage())
.addValue("vRefId", rhData.getRequestId())
.addValue("vSrcId", rhData.getSrcId())
.addValue("vUserId", rhData.getUserId())
.addValue("vQueType", queType)
.addValue("vXmlDataIn", sqlxmlIn);
// calling db (with XMLType)
xmlOut = jdbcCall.executeFunction(XMLType.class, in);
logger.debug("Stored Procedure {} executed (XMLType)", jdbcCall.getProcedureName());
String xmlOutStr = null;
xmlOutStr = xmlOut.getString();
logger.info("xml out (XMLType)\r\n{}", xmlOutStr);
doc = xmlOut.getDocument(); // everything ok (but getDocument deprecated ...)
// calling db (with SQLXML)
sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);
logger.debug("Stored Procedure {} executed (SQLXML)", jdbcCall.getProcedureName());
xmlOutStr = sqlxmlOut.getString();
logger.info("xml out (SQLXML)\r\n{}", xmlOutStr); // everything ok
DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // the following exception is throwing from here
doc = (Document) domSource.getNode();
(...)
java.sql.SQLException: Attempt to read a SQLXML that is not readable.
at oracle.xdb.XMLType.getSource(XMLType.java:5159)
at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(<generated>)
at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
...
What I am doing wrong?
Best regards,
--
koli