Selecting and Updating Records from an External Database

You can use ODBC to select records from an external data source with no issues. However, selecting then updating a record after it has been processed can be tricky. Below is an example of how to do just that. Our example is how to select in a header record, select in a set of lines for that header, process them into an AX table (not shown) then update the external data source so the records don't get reprocessed.

 

void import()
{
    LoginProperty                   loginProperty;
    OdbcConnection                  odbcConnectionSelect, odbcConnectionSelectLine, odbcConnectionUpdate;
    Statement                       statement,statementLine;
    ResultSet                       resultSetHeader, resultSetLine;
    Str                             sql, criteria;
    SqlStatementExecutePermission   perm, permLine;
    NumberSeq                       num;

    ;
    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("MyDSN");

    //Create a connection to external database.
    odbcConnectionSelect = new OdbcConnection(loginProperty);
    odbcConnectionSelectLine = new OdbcConnection(loginProperty);
    odbcConnectionUpdate = new OdbcConnection(loginProperty);

    if (odbcConnectionSelect)
    {
        sql = "SELECT * FROM [dbo].[MyHeader] WHERE [Status] = 0;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnectionSelect.createStatement();
        resultSetHeader = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSetHeader.next())
        {
            num   = NumberSeq::newGetNum(PurchParameters::numRefEDI943DocId());
            _docId = num.num();

            // set values for the PO header
            _id = resultSetHeader.getString(1);
            this.setHeaderRecord(resultSetHeader);

            // lines
            sql = "SELECT * FROM [dbo].[MyLines] where id = '" + _id + "';";
            perm = new SqlStatementExecutePermission(sql);

            statementLine = odbcConnectionSelectLine.createStatement();
            resultSetLine = statementLine.executeQuery(sql);

            while (resultSetLine.next())
            {
                this.setLineRecord(resultSetLine);
            }
            // Probably could use strfmt()
            //update header
            statement = odbcConnectionUpdate.CreateStatement();
            sql = "UPDATE [dbo].[MyHeader] SET [Status] = 1 WHERE [STATUS] = 0 and id = '" + _id + "';";
            statement.executeUpdate(sql);
            //update lines
            sql = "UPDATE [dbo].[MyLines] SET [Status] = 1 WHERE [STATUS] = 0 and id = '" + _id + "';";
            statement.executeUpdate(sql);

            statement.close();
        }
    }
    else
    {
        error("Failed to log on to the database through ODBC (MyDSN).");
    }
}