Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Insert XML to BLOB

Avatar

Level 2

I'm experienceing a problem using the Foundation / Execute Query component.

I'm trying to insert XML into a table using the following INSERT Statement:

INSERT INTO temp_registration (ID, registrationXML)

VALUES ({$ /process_data/@lastID $}, '{$ /process_data/registrationXML $}')

lastID (integer variable) and the registrationXML (xml variable) are populated during the process.
The insert statement works, but the result i see in the database is not what i expect:
The input (simplified):
<?xml version="1.0" encoding="utf-8"?>
<XML>
  <Account>
    <AccountEmail>email@email.com</AccountEmail>
    <FirstName>Rob</FirstName>
  </Account>
  <Request>
    <documentID>1</documentID>
  </Request>
</XML>
The result in the database looks like:
  
    email@email.com
    Rob
 
 
    1
When using MySQL Workbench with the following INSERT statement the data is inserted as i expect...
INSERT INTO temp_registration (id, registrationXML)
VALUES (55, '<?xml version="1.0" encoding="utf-8"?>  <XML>      <Account> <AccountEmail>email@email.com</AccountEmail>        <FirstName>Rob</FirstName>             </Account>                            <Request>                             <documentID>1</documentID>        </Request>       </XML>')


The LiveCycle logs states the following btw:
2010-12-07 15:11:44,850 INFO  [SqlHelper] execute statement: INSERT INTO temp_registration (ID, registrationXML)
VALUES (44, '
 
    email@email.com
    Rob
 
 
    1
 
')

Where and why does this interpreation of my inputXML come from??
Thx!
6 Replies

Avatar

Level 10

It' because of the double quotes in the XML.

The easiest way around this is to use parameterized query.

Your SQL statement will become

INSERT INTO temp_registration (ID, registrationXML)

VALUES (?, ?);

and then you can associate the variable in the table and the right data type.

Jasmin

Avatar

Level 2

Thanks Jasmin for your quick response.

I Changed the insert query to:

ScreenShot325.jpg

Variables defined are INT and XML (w3c.org.dom.document).

Datatype in registrationXML column in DB is BLOB.

The result is this error in the workbench:

2010-12-08 08:51:44,852 INFO  [PreparedSqlHelper] execute statement: INSERT INTO temp_registration (ID, registrationXML)
VALUES (61,?)
2010-12-08 08:51:48,718 ERROR [com.adobe.workflow.AWS] An exception was thrown with name com.adobe.idp.dsc.jdbc.exception.JDBCIllegalParameterException message:Type mismatch is found when preparing SQL statement. Expecting type 'blob', but got type 'DocumentImpl' while invoking service JdbcService

Avatar

Level 10

Are you sure your registrationXML is of type w3c.org.dom.document?

The error says you're providing DocumentImpl which is an Adobe document.

Just XML as your data type in Workbench,.

Jasmin

Avatar

Level 2

I'm sure. To be sure i deleted the variable and created it again:

ScreenShot326.jpg

Avatar

Level 10

I've always use com.adobe.idp.Document in the Blob.

Could you use that data tye instead?

Jasmin

Avatar

Level 2

This works indeed, thanks to your instructions!

The XML is propery inserted to the database. When the XML contains no characters like & the XML can be pulled from the database and inserted to a w3c.org.dom.document (XML variable).

When & is used somewhere in the XML i receive the following error:

ALC-DSC-000-000: com.adobe.idp.dsc.DSCRuntimeException: Internal error.
    at com.adobe.idp.dsc.util.CoercionUtil.toDOMDocument(CoercionUtil.java:688)
    at com.adobe.idp.dsc.util.CoercionUtil.toDOMDocument(CoercionUtil.java:693)
    at com.adobe.idp.dsc.util.CoercionUtil.toType(CoercionUtil.java:998)
    at com.adobe.idp.dsc.util.CoercionUtil.toType(CoercionUtil.java:841)
    at com.adobe.workflow.datatype.runtime.support.AbstractDataTypeRuntimeHandler.coerceFrom(AbstractDataTypeRuntimeHandler.java:64)
    at com.adobe.workflow.datatype.runtime.impl.xml.XMLRuntimeHandler.coerceFrom(XMLRuntimeHandler.java:207)
    at com.adobe.workflow.datatype.runtime.impl.xml.XMLRuntimeHandler.getNode(XMLRuntimeHandler.java:112)
    at com.adobe.workflow.dom.VariableElement.setBoundValue(VariableElement.java:97)
    at com.adobe.workflow.pat.service.PATExecutionContextImpl.setProcessDataValue(PATExecutionContextImpl.java:831)
    at com.adobe.idp.dsc.jdbc.JDBCService.querySingle(JDBCService.java:268)
    at sun.reflect.GeneratedMethodAccessor1367.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.adobe.idp.dsc.component.impl.DefaultPOJOInvokerImpl.invoke(DefaultPOJOInvokerImpl.java:118)
    at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor.java:140)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.DocumentPassivationInterceptor.intercept(DocumentPassivationInterceptor.java:53)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)
    at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTransactionBMTAdapterBean.java:197)
    at sun.reflect.GeneratedMethodAccessor691.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
    at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:173)
    at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:77)
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
    at org.jboss.ejb.Container.invoke(Container.java:960)
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
    at $Proxy342.doBMT(Unknown Source)
    at com.adobe.idp.dsc.transaction.impl.ejb.EjbTransactionProvider.execute(EjbTransactionProvider.java:95)
    at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInterceptor.java:72)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStrategyInterceptor.java:55)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateInterceptor.java:37)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:188)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineImpl.java:115)
    at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:129)
    at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:93)
    at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:209)
    at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:66)
    at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
    at com.adobe.workflow.engine.PEUtil.invokeAction(PEUtil.java:886)
    at com.adobe.workflow.engine.SynchronousBranch.handleInvokeAction(SynchronousBranch.java:510)
    at com.adobe.workflow.engine.SynchronousBranch.execute(SynchronousBranch.java:888)
    at com.adobe.workflow.engine.ProcessEngineBMTBean.continueBranchAtAction(ProcessEngineBMTBean.java:2792)
    at com.adobe.workflow.engine.ProcessEngineBMTBean.asyncInvokeProcessCommand(ProcessEngineBMTBean.java:697)
    at sun.reflect.GeneratedMethodAccessor1349.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
    at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:173)
    at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:77)
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
    at org.jboss.ejb.Container.invoke(Container.java:960)
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
    at $Proxy371.asyncInvokeProcessCommand(Unknown Source)
    at com.adobe.idp.workflow.dsc.service.ProcessCommandExecutorService.invokeProcess(ProcessCommandExecutorService.java:55)
    at sun.reflect.GeneratedMethodAccessor1353.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.adobe.idp.dsc.component.impl.DefaultPOJOInvokerImpl.invoke(DefaultPOJOInvokerImpl.java:118)
    at com.adobe.idp.workflow.dsc.invoker.WorkflowDSCInvoker.invoke(WorkflowDSCInvoker.java:154)
    at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor.java:140)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.DocumentPassivationInterceptor.intercept(DocumentPassivationInterceptor.java:53)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)
    at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTransactionCMTAdapterBean.java:357)
    at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.doSupports(EjbTransactionCMTAdapterBean.java:227)
    at sun.reflect.GeneratedMethodAccessor681.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
    at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:378)
    at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
    at org.jboss.ejb.Container.invoke(Container.java:960)
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
    at $Proxy341.doSupports(Unknown Source)
    at com.adobe.idp.dsc.transaction.impl.ejb.EjbTransactionProvider.execute(EjbTransactionProvider.java:104)
    at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInterceptor.java:72)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStrategyInterceptor.java:55)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateInterceptor.java:37)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:165)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
    at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
    at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineImpl.java:115)
    at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:129)
    at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:93)
    at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:209)
    at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:66)
    at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
    at com.adobe.workflow.engine.PEInvokeProcessCommand.execute(PEInvokeProcessCommand.java:126)
    at com.adobe.workflow.workadapter.WorkflowCommandExecutionUnit.execute(WorkflowCommandExecutionUnit.java:22)
    at com.adobe.idp.dsc.workmanager.adapter.ManagedAsynchronousWorkAdapter.run(ManagedAsynchronousWorkAdapter.java:70)
    at org.jboss.resource.work.WorkWrapper.execute(WorkWrapper.java:204)
    at org.jboss.util.threadpool.BasicTaskWrapper.run(BasicTaskWrapper.java:275)
    at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:743)
    at java.lang.Thread.run(Unknown Source)
Caused by: ALC-DSC-119-000: com.adobe.idp.dsc.util.InvalidCoercionException: Cannot coerce object: [B@4da6c2f6 of type: [B to type: interface org.w3c.dom.Document
    at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
    at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source)
    at com.adobe.idp.dsc.util.DOMUtil.parseDocumentFromString(DOMUtil.java:185)
    at com.adobe.idp.dsc.util.DOMUtil.parseDocumentFromString(DOMUtil.java:130)
    at com.adobe.idp.dsc.util.DOMUtil.parseDocumentFromStream(DOMUtil.java:258)
    at com.adobe.idp.dsc.util.CoercionUtil.toDOMDocument(CoercionUtil.java:675)
    ... 135 more