none
SQL Connection String... RRS feed

  • Question

  • Asking the experts...

    I'm new to BizTalk (BizTalk Server 2009) and need a little assistance location the SQL statement for connecting/writing to an Oracle database.

    We have an existing orchestration (fully functional) that processes an EDI ANSI X12 PO (850) and writes the PO information to an Oracle database.  My questions is, where do I find the SQL Statement that connects to and writes the data to the Oracle database?

    Any assistance would be greatly appreciated.

    Many Thanks!

    Wednesday, April 20, 2011 7:11 PM

All replies

  • Either on the receive or send port. Make sure that you have set up the adapter for oracle.
    Please Indicate "Mark as Answer" if this Post has Answered the Question
    Wednesday, April 20, 2011 7:17 PM
  • Hi Carlos,

    I've found the "Poll SQL Statement" in the receive port for pulling/reading data out of our Oracle database.

    The send port is configured for an Oracle database connection, but I don't see the SQL statement to write to the database.

    The OracleDB Transport Properties window does not appear to have a field to specify the SQL Statement.

     

    What am I missing??


    Wednesday, April 20, 2011 7:25 PM
  • Hi,

    The adapter will create the SQL from the content of the message. For stored procedures the XML record will be the name of the stored procedure, and the elements or attributes will be the names and values of the parameters. A similar systemis used for table inserts, updates, deletes etc, but with collumn names and values.

    If you look at the XSD files that are generated with the wizard you shold see schemas corresponding to the database artifacts.

    Regards,

    Alan


    http://www.CloudCasts.net - Community Webcasts Powered by Azure
    Wednesday, April 20, 2011 7:43 PM
  • Hi,

    The adapter will create the SQL from the content of the message. For stored procedures the XML record will be the name of the stored procedure, and the elements or attributes will be the names and values of the parameters. A similar systemis used for table inserts, updates, deletes etc, but with collumn names and values.

    If you look at the XSD files that are generated with the wizard you shold see schemas corresponding to the database artifacts.

    Regards,

    Alan


    http://www.CloudCasts.net - Community Webcasts Powered by Azure

    Thanks for the fast reply.

    Is there any way I can view the SQL statement?  There are some complex table links that need to be maintained correctly, I also need to update the logic used to populate an existing field (need to add a multiplier to the received value).

     

    Thanks,

    Brad

    Thursday, April 21, 2011 3:06 PM
  • Hi,

    The adapter will create the SQL from the content of the message. For stored procedures the XML record will be the name of the stored procedure, and the elements or attributes will be the names and values of the parameters. A similar systemis used for table inserts, updates, deletes etc, but with collumn names and values.

    If you look at the XSD files that are generated with the wizard you shold see schemas corresponding to the database artifacts.

    Regards,

    Alan


    http://www.CloudCasts.net - Community Webcasts Powered by Azure


    Here's one of my concerns...

    In the schema for the output to the Oracle database a field is named "USER_DEF_8", however, the field in the database itself is called "USER_8" (table name is "CUSTOMER_ORDER").

    I've searched the entire solution (XSD file included) and I cannot find any reference to either the table ("CUSTOMER_ORDER") or the field ("USER_8").

     

    Where am I going or doing wrong??

     

    Thanks,

    Brad


    • Edited by ReninCorp Monday, May 2, 2011 7:14 PM
    Thursday, April 21, 2011 3:17 PM
  • Anyone???
    Monday, May 2, 2011 7:13 PM