Expand my Community achievements bar.

SOLVED

String Formatting in Workbench

Avatar

Level 3
Level 3
5/28/09 7:20:29 AM

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

Views

3.7K

Replies

11
Sign in to like this content

Total Likes

Translate
Translate
1 Accepted Solution

Avatar

Correct answer by
Level 10
Level 10
6/1/09 10:28:20 AM

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

View solution in original post

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate
Jump to reply
11 Replies

Avatar

Level 10
Level 10
5/28/09 7:37:59 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 3
Level 3
5/28/09 9:47:00 PM

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:738)
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.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.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTransactionCMTAdapterBean.java:342)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.doSupports(EjbTransactionCMTAdapterBean.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(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.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(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:132)
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:118)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:91)
at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:215)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispatcher.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(ProcessEngineBMTBean.java:2773)
at com.adobe.workflow.engine.ProcessEngineBMTBean.asyncInvokeProcessCommand(ProcessEngineBMTBean.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(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.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:158)
at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.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(ProcessCommandControllerBean.java:156)
at com.adobe.workflow.engine.ProcessCommandControllerBean.onMessage(ProcessCommandControllerBean.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.java:475)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
at org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDrivenInstanceInterceptor.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(JMSContainerInvoker.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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 10
Level 10
5/29/09 5:39:03 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 3
Level 3
5/29/09 7:47:27 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 10
Level 10
5/29/09 7:52:26 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 3
Level 3
6/1/09 12:08:39 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 10
Level 10
6/1/09 5:52:29 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 3
Level 3
6/1/09 9:56:42 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Correct answer by
Level 10
Level 10
6/1/09 10:28:20 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate
Jump to reply

Avatar

Level 3
Level 3
6/1/09 10:47:03 AM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate

Avatar

Level 3
Level 3
6/1/09 10:51:41 PM

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

Views

1.1K

Replies

0
Sign in to like this content

Total Likes

Translate
Translate
Related Conversations
My data source works in Preview PDF mode but not when I generate the form Solved

Views

2.5K

Likes

0

Replies

2
Floating field with pagenumbers in a fragment is messing up when used in masterpages Solved

Views

1.5K

Likes

0

Replies

2
Change colons to periods in date/time field Solved

Views

1.0K

Likes

0

Replies

2
Decimal field: Sign in front of a positive amount? Solved

Views

984

Likes

0

Replies

2
Unable to convert the HTML to PDF using PDF generator in Adobe workbench Solved

Views

1.7K

Likes

0

Replies

6