how to map odbc query with ax quuery and bring the only those data which not exists in system

(0) Share

Report

Posted on by Faqruddin

1,881

Hello,

I have a scenario, where I need to bring all customers using ODBC connection. I successfully establish the connection and its print the all customers. Now some customers already created before in ax from external system using AIF/.net business connector approach. We create a custom field in Cust table say ExternalCustCode and use this for data validation in .net to bring those data which is not available in ax. Now how Can I query using ODBC connection to achieve same scenario to bring customers which is not available in system. In the below code where I can make validation with ODBC query to print the required customers which not exists in ax ?

// X  , Main method in a class.
 public server static void main(Args _args)
    {
    LoginProperty loginProperty;
        OdbcConnection odbcConnection;
        Statement statement;
        ResultSet resultSet;
        str sql, criteria, axsql;
        SqlStatementExecutePermission perm;
        CustTable custTable;
        ;

        // Set the information on the ODBC.
        loginProperty = new LoginProperty();
        loginProperty.setDSN("systemrote");
        
        //Create a connection to external database.
        odbcConnection = new OdbcConnection(loginProperty);

        if (odbcConnection)
        {
           /* sql = "select *from customermaster WHERE customercode=131"
                  criteria
                  " ORDER BY customername, customerphone ASC ;";*/
       sql = "select *from customermaster";
            //Assert permission for executing the sql string.
            perm = new SqlStatementExecutePermission(sql);
            perm.assert();

            //Prepare the sql statement.
            statement = odbcConnection.createStatement();
            resultSet = statement.executeQuery(sql);

            //Cause the sql statement to run,
            //then loop through each row in the result.
            
            // select firstOnly custTable where custTable.ExternalCustCode != resultSet.getString(1) ;
            while (resultSet.next())
            {
                //It is not possible to get field 3 and then 1.
                //Always get fields in numerical order, such as 1 then 2 the 3 etc.
        

                print resultSet.getString(1);
                // print resultSet.getString(2);
                // print resultSet.getString(3);
               //  print resultSet.getString(4);
               //  print resultSet.getString(5);
               //  print resultSet.getString(6);
               //  print resultSet.getString(7);
               //  print resultSet.getString(8);
              print resultSet.getString(9);
                     pause;
                //}

               // print resultSet.getString(4);

            }

            //Close the connection.
            resultSet.close();
            statement.close();
        }
        else
        {
            error("Failed to log on to the database through ODBC.");
        }
    }

All responses (15)

Answers (0)

Martin Dráb 232,866 Most Valuable Professional on at

Like (1)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

Log queries executed on the external DB and check how the actual query differs from the query you wanted.
Faqruddin 1,881 on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

Thank you. Its MSSQL. I try both query != or <>. Its executed without any issue in external db. The commands is ok. But when comes to ax its not work both way to find out the records. I will try your advise LINQ/Dapper.
Martin Dráb 232,866 Most Valuable Professional on at

Like (1)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

What will you get if you run the same query directly against the external database? And can you log what query it got through ODBC?

What type of database server is it? Microsoft SQL Server, for instance?

By the way, this API is quite difficult to use and debug, as we're seeing again at the moment. I wouldn't use it and I recommend the same to you (although it still may be fine in simple cases). For example, I used LINQ to SQL and Entity Framework on my past projects, when I need to get data from an external DB to AX 2012. You might also be intersted in Dapper (I used it recently for some work unrelated to AX 2012). All require some extra work, that's for sure, but then you can get things like compile-time type checks, IntelliSense and various extra fearures.
Faqruddin 1,881 on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

Oh I'm sorry. It was misunderstanding from my side the operators x++ and sql. I try with sql1 = "select customercode from customermaster where customercode <> '"+_custTable.ExternalCustCode+"';"; But still I'm not getting the value of customercode from external db that not equal to the value of _custTable.ExternalCustCode. Please advise.
Martin Dráb 232,866 Most Valuable Professional on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

I'm sorry, but I have no idea what you're saying. You're using !=, which is an X++ operator for "not equal". And I'm suggesting that you should use use the SQL operator for the same thing (i.e. "not equal"), which is <>, because there you're writing SQL code and not X++.

And you're claiming that using the "not equal" operator isn't suitable for you, despite the fact it's what you're using in your code. Your explanation is that you "are trying to filtering the records which is not exists in custTable.ExternalCustCode", which is a sentence that I don't understand at all. According to your code, you want to find records with value of customercode not equal to the value of _custTable.ExternalCustCode, and then using the "not equal" operator sounds like the thing to me. Therefore your statements doesn't explain to me why consider it not suitable.
Faqruddin 1,881 on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

I try < or > its not suitable for me. I'm tryning to filtering the records which is not exists in custTable.ExternalCustCode. That's the reason the line of the code sql1 is sql1 = "select customercode from customermaster where customercode != '"+_custTable.ExternalCustCode+"';";
Martin Dráb 232,866 Most Valuable Professional on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

I think the operator should be <>, not !=. Give it a try.
Faqruddin 1,881 on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

Please find the value of sql1.
Martin Dráb 232,866 Most Valuable Professional on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

I'm sorry, but your screenshot doesn't show me the value of your sql1 variable.
Faqruddin 1,881 on at

Like (0)

Report

RE: how to map odbc query with ax quuery and bring the only those data which not exists in system

But its not filter as per the query provided in sql1 statement.