B2B Integration

 View Only
  • 1.  User Exit to Query External Database

    Posted Tue February 11, 2020 10:31 AM
    ​​Hi - I'm creating a map where the input is EDI and the output is SQL.  I am trying to query a DB2 database table on the input side of the map in order to get the cross-reference value of the N104 data. 

    This works  (Where ABCD is the N104 value):
    Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCD"

    This does not work  (Where variable = N104 value):
    Select Column5 from Table where Column1 = 'EDI' and Column2 = variable

    Does anyone know how to accomplish what I'm trying to do?

    Any assistance would be greatly appreciated!

    Thank you - Karen 





    ------------------------------
    Karen Gallello
    ------------------------------

    #SupplyChain
    #B2BIntegration


  • 2.  RE: User Exit to Query External Database

    Posted Wed February 12, 2020 04:43 AM
    Good morning Karen,

    I'm not sure to understand the issue, so pardon me in advance if I seem to be asking dumb questions , or if I sound too condescending.

    Could you share the syntax of the query that fails?
    Cold you confirm you are using the TX variable syntax, using # signs around the variable name?


    ------------------------------
    Laurent Barthelemy
    Technical director
    Satisco
    mont saint guibert
    +33 616792115
    ------------------------------



  • 3.  RE: User Exit to Query External Database

    Posted Wed February 12, 2020 10:58 AM
    Hi Karen,

    It seems that you are trying to query DB from the input side (EDI). You might not be query an External DB table from a regular layout i.e. EDI, XML and CSV, but you can refer or query Sterling internal cross-reference table i.e. Codelist. You can do this only SQL layout which is your output side.

    It would been more helpful if you could have shared map.

    Thanks,
    Raja

    ------------------------------
    Rajasekhar Muthamsetty
    ------------------------------



  • 4.  RE: User Exit to Query External Database
    Best Answer

    Posted Wed February 12, 2020 02:57 PM
    Hello Karen,

    You need to build statement with "Quotes" around the variable. e.g. 

    If Value of variable = "ABCDE"
    then your query will look like
    Select Column5 from Table where Column1 = 'EDI' and Column2 = ABCDE
    However, it should look as
    Select Column5 from Table where Column1 = 'EDI' and Column2 = "ABCDE"  <-Quotes

    So you need to concat strings to build query
    strQuery = "Select Column5 from Table where Column1 = 'EDI' and Column2 = '" + variable + "'"  
    and then use strQuery.

    _Dhiraj Patil

    ------------------------------
    Dhiraj Patil
    ------------------------------



  • 5.  RE: User Exit to Query External Database

    Posted Thu February 13, 2020 10:27 AM
    Good day Karen,

    We were able to successfully implement what you are trying to do.

    • We wrote a java class that has methods manages the database connection and executes sql queries.
    • This class will be installed as a 3rd party jar on SI.
    • In the map we declared Object variable that will invoke the different methods that are needed from database.
    Let me know if you need more details.

    Regards

    ------------------------------
    Clayton Snyman
    ------------------------------



  • 6.  RE: User Exit to Query External Database

    Posted Fri February 14, 2020 08:43 AM
    ​Thank you everyone for your assistance.  I was able to resolve this issue by concatenating the string variable to the query statement.

    string[200] select_string, variable_string, query_string;

    variable_string =  "= '" + #field + "'";
    select_string = "select Column5 from Table where Column1 = 'EDI' and Column2 ";
    query_string = select_string + variable string;



    ------------------------------
    Karen Gallello
    ------------------------------