15 Replies Latest reply on Mar 21, 2009 11:23 PM by HowardTreisman

    Database query result

    Level 1
      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
        • 1. Re: Database query result
          Jasmin Charbonneau Level 4
          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
          • 2. Re: Database query result
            JoshDBoyle Level 1
            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
            • 3. Re: Database query result
              Level 1
              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
              • 4. Re: Database query result
                Jasmin Charbonneau Level 4
                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
                • 5. Re: Database query result
                  JoshDBoyle Level 1
                  Deepak

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

                  Josh Boyle
                  jboyle@cardinalsolutions.com
                  Cardinal Solutions Group
                  • 6. Re: Database query result
                    Level 1
                    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.
                    • 7. Re: Database query result
                      Jasmin Charbonneau Level 4
                      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
                      • 8. Re: Database query result
                        Level 1
                        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
                        • 9. Re: Database query result
                          Level 1
                          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
                          • 10. Re: Database query result
                            Level 1
                            Can any body help!!!
                            • 11. Re: Database query result
                              pguerett Level 6
                              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?
                              • 12. Re: Database query result
                                Level 1
                                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
                                • 13. Re: Database query result
                                  pguerett Level 6
                                  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.
                                  • 14. Re: Database query result
                                    Level 1
                                    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
                                    • 15. Re: Database query result
                                      HowardTreisman Level 1
                                      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