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.

Database query result

Avatar

Former Community Member
Hello All,



I need to display a report on the pdf based on the database query result. The thing is My query is working fine. I'm using queryMultipleToXML. The 'test' is giving proper data in the SQL statement. The output of this is being assigned to an XML Variable A. I'm using mapping after this step. I'm mapping this A to a textbox in the pdf with xml binding(string for that field).



Problem is nothing is getting displayed on the pdf form. The problem it seems is the data from sql query result is not getting assigned to A whose type is xml.



Can Any body help me? I want to assign a database query result into a form textbox. if any sample please let me know.



thanks



Deepak
15 Replies

Avatar

Level 10
Can you use Record and Playback to check if A is getting set properly after the database call. It seems that your DB call is working fine.



Also how are you setting your field in the PDF?



I'm thinking it's more something around the form data mapping.



Jasmin

Avatar

Former Community Member
Deepak



Are you using any parameters within your SQL query? For example, take the following:



SELECT *

FROM tblNames

WHERE username = '{$ /process_data/@sName $}'



If you are, can you verify that the parameter actually has a proper value?



Feel free to shoot me over your form and I'll take a look to see what I can find!



Josh Boyle

jboyle@cardinalsolutions.com

Cardinal Solutions Group

Avatar

Former Community Member
Hello Josh,



Thanks a lot for your support.



The report should provide the following information:



Task ID

Assigned To

Created By

Current Step Name

Form Name

Date/Time of Creation



So the query that i'm using is:



SELECT DISTINCT T0.id as 'TaskId','|' as 'DIV1',

T4.canonicalname as 'AssignedTo','|' as 'DIV2' ,

T5.canonicalname as 'CreatedBy','|' as 'DIV3',

T0.step_name as 'step_name','|' as 'DIV4',

T0.instructions as 'instructions','|' as 'DIV5',

T2.create_time as 'create_time','|' as 'DIV6',

T2.complete_time as 'complete_time', '||' as 'DIV7'



FROM lc_db_usr.tb_task T0

INNER JOIN lc_db_usr.tb_assignment T1 ON (T0.current_assignment_id = T1.id)

INNER JOIN lc_db_usr.tb_process_instance T2 ON (T0.process_instance_id = T2.id)

INNER JOIN lc_db_usr.tb_queue T3 ON (T1.queue_id = T3.id)

INNER JOIN lc_db_usr.EDCPRINCIPALENTITY T4 ON (T3.workflow_principal_id = T4.id)

INNER JOIN lc_db_usr.EDCPRINCIPALENTITY T5 ON (T2.creator_id = T5.id)



WHERE (T0.status IN (2,3,4) AND T0.process_name = 'XYZ' AND T2.status = 1) order by T4.canonicalname



*Please have a look at the query. If you find any other better query please suggest me. I'll be greatful.



The problem is not with the form it seems. I'll summarize what i'm doing.



-> Using queryMultipleToXML i'm fetching data from the database.

-> Assigning it to an XML variable A (output of queryMultipleToXML).

-> In the next step, i'm assigning this A to a binded string variable of the form B(text box) using setValue service. Later when displaying the form i'll use this rawvalue of B and populate the table.



Then the form gets displayed. Query is fetching data properly('test' is showing proper data).



The problem might be in mapping/binding as i'm mapping XML to a string. Everything is looking fine but the output is not being displayed. :(



Thanks

Deepak

Avatar

Level 10
Try to save the XML into a file and use Form Designer stand alone to merge the data with you XDP and see if it merges the data properly (PDF Preview).



I'm pretty sure you have a binding issue here.



Jasmin

Avatar

Former Community Member
Deepak



Did Jasmin's suggestion solve your issue? If not let us know!



Josh Boyle

jboyle@cardinalsolutions.com

Cardinal Solutions Group

Avatar

Former Community Member
Hello All,



Actually I was expecting something but it turned out to be something else!!!



The flow is like this.



QueryMultipleToXML (A) -> Set Value (B ) -> Assign Task (C)



My requirement is: From A i'll get data, in B, i'll map raw data from A to form element which i'll process while rendering the form. In C i'm assigning the form to a user.



I was expecting data to be displayed on the report table when user logs in and starts a process (on the displayed form). But what is happening is, the data is not getting displayed there. If the user completes that form, then the form will go to his 'To Do' list. If he opens that then the data is being displayed (on the displayed form).



Is there any way that i can show the data whenever the users starts the process?



Thanks a lot for your kind reply and time.



Actually i was not going further(completing on the start process). So I was thinking that data is not coming to the form.



Thanks

Deepak.

Avatar

Level 10
You'll need to do the same thing in the render service that renders the initital form (under Advanced Settings of the input form variable).



Jasmin

Avatar

Former Community Member
Hello Jasmin,



My requirement is, a query result will be there on an XML variable which i need to put into a text box on the form before it gets rendered.



Could you please elaborate on what i need to do on render service.



In render service, i'm not seeing any configuration to set the form element like a textbox.



Thanks

Deepak

Avatar

Former Community Member
Hello All,



Can anyone please let me know what i need to do?



Please elaborate on render service... I tried lot many ways its not working...



Thanks

Deepak

Avatar

Former Community Member
You want to assign XML in the TextBox?



Are you doing this through a web service call on the form or on the process map?

Avatar

Former Community Member
Hello Paul,



Yes, I want to assign the output of queryMultipleToXML to a textbox in my form.



I'm using process map.

-> I'm assigning the output of queryMultipleToXML to a variable

-> I'm mapping that variable to the form textbox using setValue



The problem is whenever the form is displayed, the data is not displayed. But when submitted, it'll come to to-do list with the data.



So i need the data to be displayed on the first display of the form itself. so kindly guide me in detail, what i have to do on render service.



thanks

deepak

Avatar

Former Community Member
Try chnaging the variable from an XML Var to a String and see if that works.



I have done this but using a Web Service (where it returns XML to the form) and it is returned as a string and not XML.

Avatar

Former Community Member
Hello Paul,



My question is not of variable. if u look at the thread from the beginning. The functionality is working perfectly fine. All that i need is the query result needs to be assigned to a textbox in the form before it gets rendered first.



I WANT TO KNOW WHAT SETTINGS I NEED TO DO ON RENDER SERVICE? in order to have data displayed on the form on its first display.



Thanks

Deepak

Avatar

Level 9
Hi Deepak

I'm working on a sample for this, but it's not done yet. Email me at htreisman-at-avoka.com and we can talk about it.

howard

http://www.avoka.com