Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Connecting to External...
Finance | Project Operations, Human Resources, ...
Unanswered

Connecting to External database X++

(0) ShareShare
ReportReport
Posted on by Community member 2
Hi, I'm trying to connect to an external database using the below code but it's not returning any results. The connection gets created but when I open the form the fields are not being populated. Is there perhaps something I need to tweek?
 
 
using System.Data.SqlClient;
class ExternalFields
 
     static public void Main(Args _args)
    {
        BBBEECertIssueDate               BBBEECertIssueDate;
        BBBEECertIssueExpDate            BBBEECertIssueExpDate;
        BBBEECertNo                      BBBEECertNo;
        BBBEECreatedBy                   BBBEECreatedBy;
        BBBEECreatedDate                 BBBEECreatedDate;
        BBBEEProReg                      BBBEEProReg;
        BBBEEStatLevelOfContributor      BBBEEStatLevelOfContributor;
        BlackOwned                       BlackOwned;
        BlackOwnedRural                  BlackOwnedRural;
        BlackOwnedWomen                  BlackOwnedWomen;
        DirectorAppDate                  DirectorAppDate;
        DirectorCellNo                   DirectorCellNo;
        DirectorCompInvolvedIn           DirectorCompInvolvedIn;
        DirectorCountry                  DirectorCountry;
        DirectorCreatedDate              DirectorCreatedDate;
        DirectorEditDate                 DirectorEditDate;
        DirectorEmailAddress             DirectorEmailAddress;
        DirectorEthnicGroup              DirectorEthnicGroup;
        DirectorGender                   DirectorGender;
        DirectorIDType                   DirectorIDType;
        DirectorLastVerDate              DirectorLastVerDate;
        DirectorNames                    DirectorNames;
        DirectorOwner                    DirectorOwner;
        DirectorOwnership                DirectorOwnership;
        DirectorSAIDNo                   DirectorSAIDNo;
        DirectorStatus                   DirectorStatus;
        DirectorSurname                  DirectorSurname;
        DirectorType                     DirectorType;
        IncomeTaxNumber                  IncomeTaxNumber;
        SARSRegistered                   SARSRegistered;
        TaxCreatedDate                   TaxCreatedDate;
        TaxEditDate                      TaxEditDate;
        PurchRFQTmpCompareRFQReply       PurchRFQReply;
        SqlDataReader                    reader;
        SqlCommand                       command;
 
 
 
 
   //LoginProperty loginProperty;
        OdbcConnection odbcConnection;
        Statement statement;
        ResultSet resultSet;
        LoginProperty myLoginProperty;
        str sql, criteria;
        int output;
        SqlStatementExecutePermission perm;
        str myUserName=/Admin/;
        str myPassword=/Admin12345/;
        str myConnectionString;
        ;
 
  // Set the information on the ODBC.
        myConnectionString=strfmt(/UID=%1;PWD=%2/,myUserName,myPassword);
        myLoginProperty = new LoginProperty();
        myLoginProperty.setServer(/Demo/);
        myLoginProperty.setDatabase(/ExternalDB/);
        myLoginProperty.setOther(myConnectionString);
        //Create a connection to external database.
        odbcConnection = new OdbcConnection(myLoginProperty);

        if (odbcConnection)
        {
            Sql = strFmt(/select CertificateIssueDate,CertificateExpiryDate,CertificateNumber,CertificateSignedBy,BlackOwnership,BlackWomanOwnership,[MDDirectorStatus].name,IncomeTaxNumber,SupplierDirectors.CountryTypeCode,SupplierDirectors.CreatedDate,SupplierDirectors.EditDate,SupplierDirectors.Email,SupplierDirectors.EthnicGroupCode,SupplierDirectors.GenderCode,SupplierDirectors.LastVerificationDate,SupplierDirectors.Name,SupplierDirectors.IsOwner,SupplierDirectors.IsOwner,SupplierDirectors.OwnershipPercentage,SupplierDirectors.SAIDNumber,SupplierDirectors.Surname,SupplierContacts.CellphoneNumber,IsRegistered,SupplierDetailsTax.EditDate from dbo.SupplierDetailsBBBEE left join dbo.MDBBBEEStatusLevelOfContributor on SupplierDetailsBBBEE.StatusLevelOfContributorCode = MDBBBEEStatusLevelOfContributor.Code left join dbo.SupplierContacts on SupplierDetailsBBBEE.SupplierNumber = SupplierContacts.SupplierNumber left join dbo.SupplierDirectors  on SupplierDirectors.SupplierNumber = SupplierContacts.SupplierNumber left join [MDDirectorStatus]  on [MDDirectorStatus].Code = SupplierDirectors.DirectorStatusTypeCode left join SupplierDetailsTax on SupplierDirectors.SupplierNumber = SupplierDetailsTax.SupplierNumber/);
 
 
//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.
       
              reader = command.ExecuteReader();
              while(reader.Read())
            {
                //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(3);
                PurchRFQReply.initValue();
                PurchRFQReply.BBBEECertIssueDate = reader.get_Item(/CertificateIssueDate/);
                PurchRFQReply.BBBEECertIssueExpDate = reader.get_Item(/CertificateExpiryDate/);
                PurchRFQReply.BBBEECertNo = reader.get_Item(/CertificateNumber/);
                PurchRFQReply.BBBEECreatedBy = reader.get_Item(/CertificateSignedBy/);
                PurchRFQReply.BBBEECreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.BlackOwned = reader.get_Item(/BlackOwnership/);
                PurchRFQReply.BlackOwnedWomen = reader.get_Item(/BlackWomanOwnership/);
                PurchRFQReply.BBBEEStatLevelOfContributor = reader.get_Item(/Name/);
                PurchRFQReply.DirectorAppDate = reader.get_Item(/AppointmentDate/);
                PurchRFQReply.DirectorCountry = reader.get_Item(/CountryTypeCode/);
                PurchRFQReply.DirectorCreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.DirectorEditDate = reader.get_Item(/EditDate/);
                PurchRFQReply.DirectorEmailAddress = reader.get_Item(/Email/);
                PurchRFQReply.DirectorEthnicGroup = reader.get_Item(/EthnicGroupCode/);
                PurchRFQReply.DirectorGender = reader.get_Item(/GenderCode/);
                PurchRFQReply.DirectorLastVerDate = reader.get_Item(/LastVerificationDate/);
                PurchRFQReply.DirectorNames = reader.get_Item(/Name/);
                PurchRFQReply.DirectorOwner = reader.get_Item(/IsOwner/);
                PurchRFQReply.DirectorOwnership = reader.get_Item(/OwnershipPercentage/);
                PurchRFQReply.DirectorSAIDNo = reader.get_Item(/SAIDNumber/);
                PurchRFQReply.DirectorSurname = reader.get_Item(/Surname/);
                PurchRFQReply.DirectorCellNo = reader.get_Item(/CellphoneNumber/);
                PurchRFQReply.DirectorIDType = reader.get_Item(/Name/);
                PurchRFQReply.DirectorStatus = reader.get_Item(/Name/);
                PurchRFQReply.DirectorType = reader.get_Item(/Name/);
                PurchRFQReply.IncomeTaxNumber = reader.get_Item(/IncomeTaxNumber/);
                PurchRFQReply.SARSRegistered = reader.get_Item(/IsRegistered/);
                PurchRFQReply.TaxCreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.TaxEditDate = reader.get_Item(/EditDate/);
                PurchRFQReply.insert();
            }
            
           

            //Close the connection.
       
            reader.Close();
            statement.close();
        }
        
        else
        {
            error(/Failed to log on to the database through ODBC./);
        }
    }
}
  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    Connecting to External database X++
    Please use the debugger to find out where your code get stuck - I can't do it for you. Even if I was willing, I don't have your data and your form.
  • Community member Profile Picture
    Community member 2 on at
    Connecting to External database X++
    Hi Martin,
     
    I'm using F&O.
     
    I've made changes to the code but the form times out when I open. 
     
    Please see code below:
     
     
    class CSDFieldsIntegration
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [FormEventHandler(formStr(CSDInformation), FormEventType::PostRun)]
        public static void CSDInformation_OnPostRun(xFormRun sender, FormEventArgs e)
        {
            BBBEECertIssueDate               bBBEECertIssueDate;
            BBBEECertIssueExpDate            bBBEECertIssueExpDate;
            BBBEECertNo                      bBBEECertNo;
            BBBEECreatedBy                   bBBEECreatedBy;
            BBBEECreatedDate                 bBBEECreatedDate;
            BBBEEProReg                      bBBEEProReg;
            BBBEEStatLevelOfContributor      bBBEEStatLevelOfContributor;
            BlackOwned                       blackOwned;
            BlackOwnedRural                  blackOwnedRural;
            BlackOwnedWomen                  blackOwnedWomen;
            DirectorAppDate                  directorAppDate;
            DirectorCellNo                   directorCellNo;
            DirectorCompInvolvedIn           directorCompInvolvedIn;
            DirectorCountry                  directorCountry;
            DirectorCreatedDate              directorCreatedDate;
            DirectorEditDate                 directorEditDate;
            DirectorEmailAddress             directorEmailAddress;
            DirectorEthnicGroup              directorEthnicGroup;
            DirectorGender                   directorGender;
            DirectorIDType                   directorIDType;
            DirectorLastVerDate              directorLastVerDate;
            DirectorNames                    directorNames;
            DirectorOwner                    directorOwner;
            DirectorOwnership                directorOwnership;
            DirectorSAIDNo                   directorSAIDNo;
            DirectorStatus                   directorStatus;
            DirectorSurname                  directorSurname;
            DirectorType                     directorType;
            IncomeTaxNumber                  incomeTaxNumber;
            SARSRegistered                   sARSRegistered;
            TaxCreatedDate                   taxCreatedDate;
            TaxEditDate                      taxEditDate;
            PurchRFQTmpCompareRFQReply       purchRFQReply;
            
            PurchRFQReply = sender.datasource().cursor();
            //LoginProperty loginProperty;
            OdbcConnection odbcConnection;
            Statement statement;
            ResultSet resultSet;
            LoginProperty myLoginProperty;
            str sql, criteria;
            int output;
            SqlStatementExecutePermission perm;
            str myUserName="axdbadmin";   
            str myPassword="gI8fkaEMpqdzcru9"; 
            str myConnectionString;
            ;
            // Set the information on the ODBC.
            myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);
            myLoginProperty = new LoginProperty();
            myLoginProperty.setServer("ISDONEBOX");   
            myLoginProperty.setDatabase("CSD_prod_Dev"); 
            myLoginProperty.setOther(myConnectionString);
            //Create a connection to external database.
            odbcConnection = new OdbcConnection(myLoginProperty);

            if (odbcConnection)
            {
                Sql = strFmt("select CertificateIssueDate,CertificateExpiryDate,CertificateNumber,CertificateSignedBy,BlackOwnership,BlackWomanOwnership,[MDDirectorStatus].name,IncomeTaxNumber,SupplierDirectors.CountryTypeCode,SupplierDirectors.CreatedDate,SupplierDirectors.EditDate,SupplierDirectors.Email,SupplierDirectors.EthnicGroupCode,SupplierDirectors.GenderCode,SupplierDirectors.LastVerificationDate,SupplierDirectors.Name,SupplierDirectors.IsOwner,SupplierDirectors.OwnershipPercentage,SupplierDirectors.SAIDNumber,SupplierDirectors.Surname,SupplierContacts.CellphoneNumber,IsRegistered,SupplierDetailsTax.EditDate from dbo.SupplierDetailsBBBEE left join dbo.MDBBBEEStatusLevelOfContributor on SupplierDetailsBBBEE.StatusLevelOfContributorCode = MDBBBEEStatusLevelOfContributor.Code left join dbo.SupplierContacts on SupplierDetailsBBBEE.SupplierNumber = SupplierContacts.SupplierNumber left join dbo.SupplierDirectors  on SupplierDirectors.SupplierNumber = SupplierContacts.SupplierNumber left join [MDDirectorStatus]  on [MDDirectorStatus].Code = SupplierDirectors.DirectorStatusTypeCode left join SupplierDetailsTax on SupplierDirectors.SupplierNumber = SupplierDetailsTax.SupplierNumber");
                //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.
                 while (resultSet.next())
                {
                    ttsbegin;
                    purchRFQReply.clear();
                    purchRFQReply.bBBEECertIssueDate = resultSet.getString(1);
                    purchRFQReply.bBBEECertIssueExpDate = resultSet.getString(2);
                    purchRFQReply.bBBEECertNo = resultSet.getString(3);
                    purchRFQReply.bBBEECreatedBy = resultSet.getString(4);
                    purchRFQReply.blackOwned = resultSet.getString(5);
                    purchRFQReply.blackOwnedWomen = resultSet.getString(6);
                    purchRFQReply.directorStatus = resultSet.getString(7);
                    purchRFQReply.incomeTaxNumber = resultSet.getString(8);
                    purchRFQReply.directorCountry = resultSet.getString(9);
                    purchRFQReply.taxCreatedDate = resultSet.getString(10);
                    purchRFQReply.taxEditDate = resultSet.getString(11);
                    purchRFQReply.directorEmailAddress = resultSet.getString(12);
                    purchRFQReply.directorEthnicGroup = resultSet.getString(13);
                    purchRFQReply.directorGender = resultSet.getString(14);
                    purchRFQReply.directorLastVerDate = resultSet.getString(15);
                    purchRFQReply.directorNames = resultSet.getString(16);
                    purchRFQReply.directorOwner = resultSet.getString(17); 
                    purchRFQReply.directorOwnership = resultSet.getString(18);
                    purchRFQReply.directorSAIDNo = resultSet.getString(19);
                    purchRFQReply.directorSurname  = resultSet.getString(20);
                    purchRFQReply.directorCellNo = resultSet.getString(21);
                    purchRFQReply.sARSRegistered = resultSet.getString(22);
                    purchRFQReply.directorEditDate = resultSet.getString(23);
                    purchRFQReply.insert();
                    ttscommit;
                    
                }
                //Close the connection.
                resultSet.close();     
                statement.close();
            }
            else
            {
                error("Failed to log on to the database through ODBC.");
            }
        }
    }
     
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    Connecting to External database X++
    I see you're utilizing the 'using' statement, which suggest to me that you're actually using F&O and not AX. Am I right?
     
    Looking at the form isn't the right approach to debugging. It could mean that data is fetched correctly and the problem is just with the form. Please use the debugger to see what happens in your code.
     
    If ExecuteReader() runs without any exception but Read() returns false, it seems that the query executed successfully and just didn't find any data. If you get an exception, look at the type and the error message.
     
    I'm surprised that your code compiles at all, because get_Item() returns Object and you're trying to assign it to fields (with primitive data types). You should use methods like GetString() and GetInt32() instead.
     
    By the way, the API you're using is quite cumbersome and I really don't recommend using it. Instead, you can you use of the .NET frameworks to generates classes for your table and get code completion, compile-time validation of types and names, get typed values from columns and so on. It makes things much easier and safer.
     
    Also note that you call Close() only when everything succeeds but not if an exception gets thrown. The easiest approach to handle both cases is utilizing a 'using' block. An alternative is using try/finally.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans