Test of existence on a Table \"Oracle\"

Home Page Forums BizTalk 2004 – BizTalk 2010 Test of existence on a Table \"Oracle\"

Viewing 1 reply thread
  • Author
    • #12425

      Hi everybody,

      I’ve done an orchestration, where I do a test of existence, and I have the following problem:

      So, for my test, I have a send message, connected to a \”send port\”, where I do a simple request select , and I would like to receive the value of the variable that I’ve tested!!

      This variable might exist, but if the patient doesn’t exists, this variable is null!! And I would like to ask you how to do to test this variable, because I would like to affect a value if this variable is null!!!

      If this variable is null, my orchestration is blocked !!!

      Could you help me please ???

      Here is the picture of a part of my orchestration:




      Thank you in advance

    • #12426


      the problem is when I receive, because I can send a request \”Select\”, for instance, I do a select * where IDPatient = IDPatientSource

      Then, when I receive the message, if the patient exists, i do an update, and there are maybe 5 different updates, which depend on the value of the variable \”StatusPatient\”, contained in the response message. If the patient doesn’t exists, I simply do an insert !

      I would like to do a .netComponent, but I’m not able to do it!
      But I’m sure that it’s the best solution!

      So the big problem of my orchestration is that I don’t know how to do when my response message is null !!!

      Thank you

      • #12427


        the problem is not when I receive, but in my Expression form, when I do:

        StatusRoom = Message_ResponseSelect.parameters.Operation1.PATIENT_OMNIS.Select.Record.PATIENT_ROOM;

        StatusRoom is a variable that I’ve created!

        the problem is that if the patient doesn’t exist, the ReceiveMessage is NULL!!!

        So I don’t know how to do the test!! 😕

        I would like to do a test like this:

        \”if the receive message doesn’t exist, then StatusRoom = -1, else StatusRoom = Message_ResponseSelect.parameters.Operation1.PATIENT_OMNIS.Select.Record.PATIENT_ROOM; \”

        Maybe should I do with XPath ??? Or with .Net Component ?? but how ?? 🙁
        Help me please !! 🙁

        Thank you in advance

        • #12428


          somebody told me that I should do with xpath, so I’ve seen a sample of
          Mr. Stephen W. Thomas, and it doesn’t seem to be very difficult!

          But I don’t know the syntax!!

          So I would like to do that :

          When I do a select on a table oracle, I receive a message like those two:

          when the select returns something:

          <?xml version=\”1.0\” encoding=\”utf-8\”?><patient_omnisResponse xmlns=\”http://testOracle.test/patient_omnis\”>

          – When the select doesn’t return anything:

          <?xml version=\”1.0\” encoding=\”utf-8\”?><patient_omnisResponse xmlns=\”http://testOracle.test/patient_omnis\”>
          <Select />

          So I would like to know if there is something in \”Select\”, but I don’t know how to do the test!

          Here is my test:

          nStatus = System.Convert.ToInt32(xpath(\”count(Message_ResponseSelect.parameters.Operation1.PATIENT_OMNIS.Select.Record.PATIENT_ROOM)\”));


          But it doesn’t work!!
          So I tried to write :
          nStatus = System.Convert.ToInt32(xpath(Message_ResponseSelect,\”count(Operation1/PATIENT_OMNIS/Select/Record)\”));

          But the syntax is not correct 🙁

          Could you help me please ???

          In this test, I would like to test the patient_room, but I can test all the record !!!

          I only want to do a test of existence!!

          I’ve tested the patient room, because then I would use it to test in which of the 5 decision I go!!! THe decision depends on the number room for example!!!!

          Thank you in advance

          • #12429


            if I write :

            nStatus = System.Convert.ToInt32(xpath(Message_ResponseSelect.parameters, \”count(//Record)\”));

            the expression editor doesn’t show an error, but when I deploy and test the solution, normally it should have nStatus = 1, because the Record already exists!!!

            But with this test, I have mStatus = 0 😕

            I don’t know why !! 🙁

            Do you have a suggestion ??
            Thank you


            • #12430

              Hi everybody,

              is it possible that the problem comes from de type of the message??

              My message type is not System.Xml.XmlDocument, but the type of the shema that I’ve created!

              Thank you in advance for your reply

              • #12431

                Hi Mr. Stephen W. Thomas,

                Thank you for your reply!!!!!!
                But it still doesn’t work, and I become mad!!!

                Here is what I wrote in the expression editor:

                nStatus = System.Convert.ToInt32(xpath(MessageAssigned.parameters, \”count(/*[local-name()=’patient_omnisResponse’ and namespace-uri()=’http://testOracle.test/patient_omnis’%5D/*%5Blocal-name()=’Operation1&#8242; and namespace-uri()=”]/*[local-name()=’PATIENT_OMNIS’ and namespace-uri()=”]/*[local-name()=’Select’ and namespace-uri()=”]/*[local-name()=’Record’ and namespace-uri()=”])\”));

                Normally it should give me 1 , but it gives me 0 !!

                I’ve sent the message in a file to open it , and the structure is ok, … you can see it upstairs !

                Do you know what is wrong??
                Thank’s for your help!!!

                • #12432

                  Hi Mr. Stephen,

                  YOU ARE THE BEST !!!!

                  Thank you very very very much !!!!!!!

                  It works 🙂

                  Achille C.

                  • #12433

                    Hi Achille,

                    please be aware that Stephen’s xpath will find RECORD’s everywhere in your document. This might be what you need, but that \”Operation1\”-Tag bothers me a bit. What if there is another operation with some other records? You would count them, too.

                    So it really all depends on the possible structure of the XML-File. An alternative approach, that uses a direct path to your data but avoids the namespace-issue, would be just to use local-name() and ignore the namespaces altogether, like:

                    /*[local-name()=’patient_omnisResponse’]/*[local-name()=’Operation1′]/*[local-name()=’PATIENT_OMNIS’ ]/*[local-name()=’Select’]/*[local-name()=’Record’]


                    Disclaimer: By no means I want to diminish the value of Stephen’s advice (I might need it very soon, too :wink:)

                    • #12434

                      Hi Toni,

                      thank you for your tips!

                      So, in my orchestration, I only have one Record possible, so I think that what wrote Stephen works well for my orchestration!

                      But maybe I would need your solution for another case 😉

                      Thank you very much for your help!

                      Achille C.

                      • #12435

                        Well, I’m a little confused if your problem happens before the send or after.

                        I’m guessing it’s after.

                        Does the message actually return or are you just not getting a response back?

                        You might have to change the call to make sure it always returns something or you could front the call with a .net component that does your null logic for you.

                        Just a thought.

                      • #12436

                        Nope, the message type shouldn’t matter.

                        XPath inside an Orchestration is hard to do sometimes due to the namespaces inside the messages.

                        You might want to look at your schema, find the node you want to xpath, and then look at the system generated xpath to that node.

                        Just click on the node inside the schema editor and you should see the xpath under properties someplace. It will be long and ugly – including all the namespaces.

                      • #12437

                        Ok, after about 30 minutes I think I got it.

                        Try this:
                        nStatus = System.Convert.ToInt16(xpath(InXML, \”count(//*[local-name()=’Record’ and namespace-uri()=’http://testOracle.test/patient_omnis’%5D)\”));

                        Best of luck.

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.