11 Replies Latest reply on Jun 1, 2009 10:52 PM by deepusrp

    String Formatting in Workbench

    deepusrp

      Hello All,

       

      I'm using LC 8.2 and the Database is SQLServer.  My requirement is:  I need to send email from a process to all members of a particular user group.  As far as i know there is no functionality in workbench which helps me in doing that.  So i'm taking email ids from the database.

       

      Here is my Query:

      SELECT   ',' + prin.email
      FROM  edcprincipalentity AS prin,
            edcprincipalgrpctmntenti AS gr
      WHERE  prin.id = gr.refchildprincipalid
           AND refparentprincipalid =   (SELECT id FROM edcprincipalentity lcuser
         WHERE lcuser.principaltype = 'GROUP'
         AND lcuser.commonname = '<GroupName>')

       

      And the output will be like this:    

      ,abc@xyz.com

      ,pqr@lmn.com

      ,xyz@cde.com

       

      I'm using 'Query for Multiple Rows as XML', to query the database.  Now I need to convert it to a plane string containing email ids inorder to use in Email control.

       

      I'm using SetValue's execute control to convert that.  I have a string variable to which i'll be putting the string valueof the xml using serializable.

      /process_data/@EmailIds = serialize(/process_data/EmailIdsFromDB)

       

      This is giving me the string EmailIds = ",abc@xyz.com ,pqr@lmn.com ,xyz@cde.com"

       

      Now my problem is i cannot use the result directly on my Email control as it contains an extra comma.  I have to somehow remove that one.

      /process_data/@EmailIds = substring(/process_data/@EmailIds, 2)

       

      But I'm not getting the desired result.

       

      Can any one help me out or suggest me some other method of doing this?

       

      Thanks

      Deepak

        • 1. Re: String Formatting in Workbench
          Jasmin Charbonneau Level 4

          You can use substring-after(string, string) : The substring-after function returns the substring of the first argument string that follows the first occurrence of the second argument string in the first argument string, or the empty string if the first argument string does not contain the second argument string. For example, substring-after("1999/04/01","/") returns 04/01, and substring-after("1999/04/01","19") returns 99/04/01.

           

          Jasmin

          • 2. Re: String Formatting in Workbench
            deepusrp Level 1

            Hello Jasmin,

             

            Thanks for your suggetion.

             

            Your sulution worked out well for formatting string.  But could not emial using that string.

             

            I'm using SetValue's execute control to convert the xml output from the database to string.  I have a string variable to which i'll be putting the string valueof the xml using serializable. I tried both true and false.

            /process_data/@EmailIds = serialize(/process_data/EmailIdsFromDB, false)

             

            This is giving me the string EmailIds = ",abc@xyz.com ,pqr@lmn.com ,xyz@cde.com"

             

            Which I'm formating using substring-after().

            /process_data/@EmailIds = substring-after(/process_data/@EmailIds, ",")

             

            That will give me EmailIds = "abc@xyz.com ,pqr@lmn.com ,xyz@cde.com" which is desired.

             

             

            When displayed this string as part of the message body it is showing the sting perfectly like above.  But i dont know how it will be interpretted when used on the 'TO' address of the email control (it might contain some xml headers).  I tried both xpath variable and normal variable selection for the 'TO' field. 

             

            Is there any other better way to get comma separated email ids from the Database in string format so that i can use it on the email control?

             

            It is giving me the following exception on the stalled operation window:

             

            Extra route-addr: com.adobe.idp.dsc.email.SendMailFailedException: Extra route-addr
            at com.adobe.idp.dsc.email.EmailServiceImpl.sendWithMapOfAttachments(EmailServiceImpl.java:7 38)
            at com.adobe.idp.dsc.email.EmailServiceImpl.sendWithDocument(EmailServiceImpl.java:353)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:585)
            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.transaction.interceptor.TransactionInterceptor$1.doInTransaction(Transa ctionInterceptor.java:74)
            at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTr ansactionCMTAdapterBean.java:342)
            at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.doSupports(Ej bTransactionCMTAdapterBean.java:212)
            at sun.reflect.GeneratedMethodAccessor282.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:585)
            at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
            at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionConta iner.java:214)
            at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.java:149)
            at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.java:154)
            at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor. java:54)
            at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
            at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:363)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
            at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
            at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
            at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor. java:122)
            at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
            at org.jboss.ejb.Container.invoke(Container.java:873)
            at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:415)
            at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
            at $Proxy165.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:132)
            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:118)
            at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMessage(AbstractMessage Receiver.java:91)
            at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:21 5)
            at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispat cher.java:57)
            at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
            at com.adobe.workflow.engine.PEUtil.invokeAction(PEUtil.java:724)
            at com.adobe.workflow.engine.SynchronousBranch.handleInvokeAction(SynchronousBranch.java:465 )
            at com.adobe.workflow.engine.SynchronousBranch.execute(SynchronousBranch.java:862)
            at com.adobe.workflow.engine.ProcessEngineBMTBean.continueBranchAtAction(ProcessEngineBMTBea n.java:2773)
            at com.adobe.workflow.engine.ProcessEngineBMTBean.asyncInvokeProcessCommand(ProcessEngineBMT Bean.java:704)
            at sun.reflect.GeneratedMethodAccessor2127.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:585)
            at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
            at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionConta iner.java:214)
            at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.java:149)
            at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor. java:54)
            at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
            at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
            at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:1 58)
            at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)
            at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.java:154)
            at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
            at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
            at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor. java:122)
            at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
            at org.jboss.ejb.Container.invoke(Container.java:873)
            at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:415)
            at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
            at $Proxy198.asyncInvokeProcessCommand(Unknown Source)
            at com.adobe.workflow.engine.ProcessCommandControllerBean.doOnMessage(ProcessCommandControll erBean.java:156)
            at com.adobe.workflow.engine.ProcessCommandControllerBean.onMessage(ProcessCommandController Bean.java:99)
            at sun.reflect.GeneratedMethodAccessor867.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
            at java.lang.reflect.Method.invoke(Method.java:585)
            at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
            at org.jboss.ejb.MessageDrivenContainer$ContainerInterceptor.invoke(MessageDrivenContainer.j ava:475)
            at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.java:149)
            at org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDrivenInstanceInterc eptor.java:101)
            at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
            at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
            at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
            at org.jboss.ejb.plugins.RunAsSecurityInterceptor.invoke(RunAsSecurityInterceptor.java:94)
            at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
            at org.jboss.ejb.MessageDrivenContainer.internalInvoke(MessageDrivenContainer.java:389)
            at org.jboss.ejb.Container.invoke(Container.java:873)
            at org.jboss.ejb.plugins.jms.JMSContainerInvoker.invoke(JMSContainerInvoker.java:1077)
            at org.jboss.ejb.plugins.jms.JMSContainerInvoker$MessageListenerImpl.onMessage(JMSContainerI nvoker.java:1379)
            at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:256)
            at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer.java:904 )
            at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:160)
            at org.jboss.mq.SpySession.run(SpySession.java:333)
            at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:180)
            at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)
            at java.lang.Thread.run(Thread.java:595)

             

             

            Kindly Guide we why this is happening

             

             

            Thanks

            Deepak

            • 3. Re: String Formatting in Workbench
              Jasmin Charbonneau Level 4

              Is it working fine if you hard code the email list?

               

              Did you use Record and Playback to make sure the string is formatted properly?

               

              Jasmin

              • 4. Re: String Formatting in Workbench
                deepusrp Level 1

                Hello Jasmin,

                 

                Hard Coding the email list is working fine.

                 

                I dont know what is record and playback.  Can you please explain in detail.

                 

                Thanks a lot

                Deepak

                • 5. Re: String Formatting in Workbench
                  Jasmin Charbonneau Level 4

                  Really! You need to know that. That's how you debug stuff in Livecycle.

                   

                  What you can do is right click on your process and go under Record and PlayBack -> Start Recording. Now it's going to start recording all invocations to that process.

                   

                   

                  Then invoke your process using what ever method you want. Once you're done invoking the process, you can go back to Record and PlayBack, and select Play Process Recording.

                   

                  This will allow you to "play" the invocation again and see the content of the variables at each step.

                   

                  Jasmin

                  1 person found this helpful
                  • 6. How to extract values from XML
                    deepusrp Level 1

                    Hello Jasmin,

                     

                    Your suggetion worked well.  Actually the string itself was not formatting properly.  I think i have problem with converting xml to string itself.

                     

                    I'm getting the xml data from that database as:

                    <Email>
                        <EmailId>
                            <mailId type="nvarchar">,abcd@xyz.com</mailId>
                        </EmailId>
                        <EmailId>
                            <mailId type="nvarchar">,efgh@pqr.com</mailId>
                        </EmailId>
                        <EmailId>
                            <mailId type="nvarchar">,lmop@abc.com</mailId>
                        </EmailId>
                    </Email>

                    This i have to convert to a string:  ",abcd@xyz.com ,efgh@pqr.com ,lmop@abc.com"

                     

                    Later I have to format this string which i can use it on the email control:  "abcd@xyz.com ,efgh@pqr.com ,lmop@abc.com" (without comma)

                     

                    I'm using serialize to convert from xml to string but it is not giving me the desired result.  Please suggest me some method to do the above task.

                     

                    Thanks

                    Deepak

                    • 7. Re: How to extract values from XML
                      Jasmin Charbonneau Level 4

                      I'm not sure what you mean by ".. from xml to string but it is not giving me the desired result".

                       

                      This should convert your node into a string. What are you getting?

                       

                      Jasmin

                      • 8. Re: How to extract values from XML
                        deepusrp Level 1

                        Hello Jasmin,

                         

                        I'm getting my xml as:

                        <Email>
                            <EmailId>
                                <mailId type="nvarchar">,abcd@xyz.com</mailId>
                            </EmailId>
                            <EmailId>
                                <mailId type="nvarchar">,efgh@pqr.com</mailId>
                            </EmailId>
                            <EmailId>
                                <mailId type="nvarchar">,lmop@abc.com</mailId>
                            </EmailId>
                        </Email>

                         

                        Finally I want: "abcd@xyz.com ,efgh@pqr.com ,lmop@abc.com"

                         

                        I'm using Serialize to convert xml to string.

                        /process_data/@EmailIds = serialize(/process_data/EmailIdsFromDB, true())

                         

                        It is giving only the first node's value and rest of the xml will be there in the string.  The whole string after serialize will be:

                         

                        ,abcd@xyz.com</mailId>
                            </EmailId>
                            <EmailId>
                                <mailId type="nvarchar">,efgh@pqr.com</mailId>
                            </EmailId>
                            <EmailId>
                                <mailId type="nvarchar">,lmop@abc.com</mailId>
                            </EmailId>
                        </Email>

                         

                        How can i get only the email ids one after the other so that i can use that string on my Email control's 'TO' box.

                         

                        Thanks

                        Deepak

                        • 9. Re: How to extract values from XML
                          Jasmin Charbonneau Level 4

                          Ok,

                          Let's try a different approach.

                           

                          You have your xml information in a xml variable right?

                           

                          Why don't you just use a setValue with a xPath expression to get the value of all the nodes.

                           

                          You can get the count using the following xPath: count(/process_data/xmlvar/Email/EmailId)

                          Then set a counter variable i (data type interger) to the count value.

                          Then loop and increment the counter and build you string using the following xPath

                          /process_data/@strList = concat(/process_data/@strList,/process_data/xmlvar/Email/EmailId[process_data/@i]/mailId)

                           

                          You might have to replace process_data/@i with number(process_data/@i) for the xPath to work properly.

                           

                          Jasmin

                          • 10. Re: How to extract values from XML
                            deepusrp Level 1

                            Hello Jasmin,

                             

                            I'll try this and let you know tomorrow.

                             

                            What is the use of serialize function then?  And dont we have any simple control or method to perform the same task.

                             

                            Thanks a lot for your kind reply.

                             

                            Deepak

                            • 11. Re: How to extract values from XML
                              deepusrp Level 1

                              Hello Jasmin,

                               

                              Its Awesome!!! It worked.

                               

                              Actually I didn't know that, we can use count to count the number of nodes like this.  And even ididnt know to reach XML value directly through xpath expression like this.  Its really helped me a lot.  Thanks a lot.

                               

                              snapshot.JPG

                               

                              Loop Initialize

                              /process_data/@counter = 1

                              /process_data/@emailCount = count(/process_data/EmailIdsFromDB/Email/EmailId)

                               

                              Convert XML to String

                              /process_data/@EmailIds = concat(/process_data/@EmailIds, /process_data/EmailIdsFromDB/Email/EmailId[number(/process_data/@counter)]/mailId)

                               

                              Has More Nodes?

                              /process_data/@counter = /process_data/@counter + 1

                               

                              Yes Route will have condition: /process_data/@counter <= /process_data/@emailCount

                              No -> Default route

                               

                              Format the String

                              /process_data/@EmailIds = substring-after(/process_data/@EmailIds, ",")

                               

                               

                              Thanks

                              Deepak