6 Replies Latest reply on Dec 8, 2010 6:57 AM by Rob_RH

    Insert XML to BLOB

    Rob_RH Level 1

      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!
        • 1. Re: Insert XML to BLOB
          Jasmin Charbonneau Level 4

          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

          1 person found this helpful
          • 2. Re: Insert XML to BLOB
            Rob_RH Level 1

            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

            • 3. Re: Insert XML to BLOB
              Jasmin Charbonneau Level 4

              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

              • 4. Re: Insert XML to BLOB
                Rob_RH Level 1

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

                ScreenShot326.jpg

                • 5. Re: Insert XML to BLOB
                  Jasmin Charbonneau Level 4

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

                   

                  Could you use that data tye instead?

                   

                  Jasmin

                  • 6. Re: Insert XML to BLOB
                    Rob_RH Level 1

                    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(Abs tractDataTypeRuntimeHandler.java:64)
                        at com.adobe.workflow.datatype.runtime.impl.xml.XMLRuntimeHandler.coerceFrom(XMLRuntimeHandl er.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(PATExecutionCo ntextImpl.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.jav a:118)
                        at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor. java:140)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.DocumentPassivationInterceptor.intercept(DocumentPassi vationInterceptor.java:53)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(Transa ctionInterceptor.java:74)
                        at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTran sactionBMTAdapterBean.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(StatelessSessionConta iner.java:237)
                        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.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:1 73)
                        at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:77)
                        at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.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(EjbTransactionProvi der.java:95)
                        at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInt erceptor.java:72)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStra tegyInterceptor.java:55)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateIntercep tor.java:37)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterc eptor.java:188)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.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(AbstractMessage Receiver.java:93)
                        at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:20 9)
                        at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispat cher.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(ProcessEngineBMTBea n.java:2792)
                        at com.adobe.workflow.engine.ProcessEngineBMTBean.asyncInvokeProcessCommand(ProcessEngineBMT Bean.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(StatelessSessionConta iner.java:237)
                        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.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:1 73)
                        at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:77)
                        at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.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(ProcessCom mandExecutorService.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.jav a: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(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.DocumentPassivationInterceptor.intercept(DocumentPassi vationInterceptor.java:53)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(Transa ctionInterceptor.java:74)
                        at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTr ansactionCMTAdapterBean.java:357)
                        at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.doSupports(Ej bTransactionCMTAdapterBean.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(StatelessSessionConta iner.java:237)
                        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.java:158)
                        at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.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(EjbTransactionProvi der.java:104)
                        at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInt erceptor.java:72)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStra tegyInterceptor.java:55)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateIntercep tor.java:37)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterc eptor.java:165)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
                        at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
                        at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.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(AbstractMessage Receiver.java:93)
                        at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:20 9)
                        at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispat cher.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(WorkflowCommandExecut ionUnit.java:22)
                        at com.adobe.idp.dsc.workmanager.adapter.ManagedAsynchronousWorkAdapter.run(ManagedAsynchron ousWorkAdapter.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