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,883

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.");
        }
    }

I have the same question (0)

All responses (15)

Answers (0)

Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

Are you saying the you want to join tables from the different databases placed on two different database servers?

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report
No. Not join the two different databases. Please find my code below. In below code first sqlStatement I read all customers from external db.and I assign to string custId. Second I wrote the loop to ax custtable and match all record with cusid. Again I establish new sqlStatement to connect external db and bring the records where customercode !=custId. But here I stuck and its bringing all records from external db. But I need not match records.

// X , Main method in a class. public server static void main(Args _args) { LoginProperty loginProperty; OdbcConnection odbcConnection, odbcConnection1; Statement statement, statement1; ResultSet resultSet, resultSet1; str sql, criteria, sql1; SqlStatementExecutePermission perm, perm1; CustTable _custTable; Set permissionSet; str 50 custID; ; // Set the information on the ODBC. loginProperty = new LoginProperty(); loginProperty.setDSN("systemro"); //Create a connection to external database. odbcConnection = new OdbcConnection(loginProperty); odbcConnection1 = new odbcConnection(loginProperty); permissionSet = new Set(Types::Class); if (odbcConnection) { /* sql = "select *from customermaster WHERE customercode=13102969" criteria " ORDER BY customername, customerphone ASC ;";*/ sql = "select *from customermaster"; //Assert permission for executing the sql string. perm = new SqlStatementExecutePermission(sql); perm.assert(); // CodeAccessPermission::revertAssert(); //Prepare the sql statement. statement = odbcConnection.createStatement(); resultSet = statement.executeQuery(sql); CodeAccessPermission::revertAssert(); //Cause the sql statement to run, //then loop through each row in the result. // select firstOnly custTable where custTable.MirnahCode != 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. custID = resultSet.getString(1); while select _custTable where _custTable.ExternalCustId == custID { sql1 = "select customercode from customermaster where customercode != '" _custTable.ExternalCustId "';"; perm1 = new SqlStatementExecutePermission(sql1); perm1.assert(); statement1 = odbcConnection.createStatement(); resultSet1 = statement1.executeQuery(sql1); CodeAccessPermission::revertAssert(); while(resultSet1.next()) { print resultSet1.getString(1); pause; } } } } else { error("Failed to log on to the database through ODBC."); } }

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report
Hmm, okay...

If I understand it correctly, you're saying that this SQL command:

sql1 = "select customercode from customermaster where customercode != '" _custTable.ExternalCustId "';";

doesn't actually filter by CustomerCode and it returns all records.

What values do you have in sql1 variable before execution it? Use the debugger to find it out.

By the way, I adjusted your to to make it easier for me to understand it (correct indetation, unused variables removed etc.). Let me share it to help others too.

LoginProperty loginProperty; OdbcConnection odbcConnection, odbcConnection1; Statement statement, statement1; ResultSet resultSet, resultSet1; str sql, criteria, sql1; SqlStatementExecutePermission perm, perm1; CustTable _custTable; Set permissionSet; str 50 custID; ; // Set the information on the ODBC. loginProperty = new LoginProperty(); loginProperty.setDSN("systemro"); //Create a connection to external database. odbcConnection = new OdbcConnection(loginProperty); if (odbcConnection) { /* sql = "select *from customermaster WHERE customercode=13102969" 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.MirnahCode != 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. custID = resultSet.getString(1); while select _custTable where _custTable.ExternalCustId == custID { sql1 = "select customercode from customermaster where customercode != '" _custTable.ExternalCustId "';"; perm1 = new SqlStatementExecutePermission(sql1); perm1.assert(); statement1 = odbcConnection.createStatement(); resultSet1 = statement1.executeQuery(sql1); while(resultSet1.next()) { print resultSet1.getString(1); pause; } } } } else { error("Failed to log on to the database through ODBC."); }

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

yes it doesn't actually filter by CustomerCode and it returns all records. sql1 its same values before/after the filter. I'm stuck this point how to filter this records.

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

Let me ask you once more. Please show the value of sql1 variable that you're using in statement1.executeQuery().

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

I'm sorry, but your screenshot doesn't show me the value of your sql1 variable.

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

Please find the value of sql1.

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

I think the operator should be <>, not !=. Give it a try.

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

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+"';";

Was this reply helpful? Yes No