External Database Connection from X++ Batch job

(0) Share

Report

Posted on by sylvesterPowerBi

384

Dear Gents,

i am trying to connect to an external database on different server from x , my purpose is the following:

i am creating a new batch job class on D365 FO to insert data in a table from an external database, database located on different server, not the same one.

i tried the standard ODBC connection but ODBC doesn't allow you to enter UserID and password, only DSN and database.

then i tried the below via ODBC and it didn't work either:

 server static ResultSet executeQuery(str sql, Connection connection = new Connection())
    {

        LoginProperty loginProperty;
        OdbcConnection odbcConnection;
        str con;
        str dsn="server name";
        str UID="UserID";
        str pwd="Password";
        SqlStatementExecutePermission   sqlPerm;
        Statement                       statement;
        ResultSet                       resultSet;
        ;
        con=strFmt("DSN=1%,UID=2%,pwd=3%",dsn,UID,pwd);
        loginProperty = new LoginProperty();
        loginProperty.setDatabase("tm");
        loginProperty.setOther(con);

        odbcConnection = new OdbcConnection(loginProperty);
     
        sqlPerm = new SqlStatementExecutePermission(sql);
        sqlPerm.assert();
        statement = odbcConnection.createStatement();
       
            resultSet = statement.executeQuery(sql);
             CodeAccessPermission::revertAssert();
      
        return resultSet;
           
        
    }

what im facing is when i run the query in batch job, the batch job is returning Error in status, it's not even executing.

kindly provide me with solutions and how to debug if the connection is working or no

Regards,

I have the same question (0)

All responses (34)

Answers (1)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Perhaps there is some additional info (error message) in your batch log?

You can set username and password with the LoginProperty class: dynamics-ax.blogspot.com/.../connecting-to-database-via-odbc.html

Also, it's easier to debug your code if you don't run it as a batch. You can forget the batch for now and use a runnable class instead.

Also, if you run D365FO on cloud, your external database must accept connections from quite a wide range of IP addresses (Azure data Center), so you might need to discuss your architecture with the db admin.

Was this reply helpful? Yes No
sylvesterPowerBi 384 on at

Like (0)

Report

[quote]
Perhaps there is some additional info (error message) in your batch log?

You can set username and password with the LoginProperty class: dynamics-ax.blogspot.com/.../connecting-to-database-via-odbc.html

Also, it's easier to debug your code if you don't run it as a batch. You can forget the batch for now and use a runnable class instead.

Also, if you run D365FO on cloud, your external database must accept connections from quite a wide range of IP addresses (Azure data Center), so you might need to discuss your architecture with the db admin.
[/quote]
Dear Nikolaos,

i am working on "on-prem" environment, and i am trying to connect to an external database on local server not cloud.

based on the link you sent above, i cannot insert password and userid, please check below screenshot:

please advise why, thank you

regards,

Was this reply helpful? Yes No
WillWU 22,361 on at

Like (0)

Report

Hi sylvesterPowerBi,

As Nikolaos said, your external DB needs to be visible to D365.

And you could use System.Data.SqlClient library to connect the database.

allaboutmsdynamics.wordpress.com/.../

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Ah, seems that LoginProperty class doesn't have methods for setting username and password after AX2009.

In this case you should look into System.Data.SqlClient library as suggested above.

Was this reply helpful? Yes No
sylvesterPowerBi 384 on at

Like (0)

Report

thank you

Was this reply helpful? Yes No
sylvesterPowerBi 384 on at

Like (0)

Report

[quote user="Will WU"]
Hi sylvesterPowerBi,

As Nikolaos said, your external DB needs to be visible to D365.

And you could use System.Data.SqlClient library to connect the database.

allaboutmsdynamics.wordpress.com/.../
[/quote]
Will try this one, thanks

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

Like (0)

Report

If you want something more complex, consider using a more powerful framework. For example, you can use Entity Framework to generate a class model from a database schema and use object-oriented and functional-oriented programming to work with the data.

But your fist step should be verifying that you'll be able to connect to the database from Azure. If not, the whole thing can't be done. It it's possible, you should still consider if it's the best design, For example, you could export data from the database to a file and import this file to F&O using data managment.

Was this reply helpful? Yes No
sylvesterPowerBi 384 on at

Like (0)

Report
i tried this code, but im not able to connect, the connection status is always closed.

using System.Data.SqlClient; class IS_DBConnection { ///
/// Runs the class with the specified arguments. ///
/// The specified arguments. public static void main(Args _args) { ResultSet RS; Query query; QueryRun queryRun; DAPAttendanceDetails DAPAttendanceDetails; SqlConnectionStringBuilder connectionStringBuilder; SqlConnection conn; SqlCommand command; SqlDataReader reader; SqlParameterCollection parameterCollection; new InteropPermission( InteropKind::ClrInterop ).assert(); str servername="xx.xx.xx.xx"; str db = "xx"; str user = "xx"; str pass= "xx"; str ConnectionString; FromDate fromDate; ToDate toDate; HcmWorkerRecId worker; DialogRunbase dialog; str q1 = "select * from myTable"; str s = date2Str(FromDate,123,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2, DateSeparator::Hyphen,DateYear::Digits4); str t = date2Str(Todate,123,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2, DateSeparator::Hyphen,DateYear::Digits4); str personnel ="10014"; try { ttsbegin; //connection connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); connectionStringBuilder.set_DataSource(servername); connectionStringBuilder.set_IntegratedSecurity(true); connectionStringBuilder.set_InitialCatalog(db); connectionStringBuilder.set_UserID(user); connectionStringBuilder.set_password(pass); ConnectionString = connectionStringBuilder.get_ConnectionString(); conn = new System.Data.SqlClient.SqlConnection(ConnectionString); command = new System.Data.SqlClient.SqlCommand(q1); command.set_Connection(conn); conn.Open(); reader = command.ExecuteReader(); while (reader.Read()) { str prdcode = reader.GetString(1); info(prdcode); } ttscommit; } catch { conn.Dispose(); } } }

kindly refer if there is something missing in my code.

Regards,

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (1)

Report

Are you able to connect from your dev VM using SQL Server Management Studio? You should test that to verify that a connection can be made in the first place.

Was this reply helpful? Yes No
sylvesterPowerBi 384 on at

Like (0)

Report

[quote]
Are you able to connect from your dev VM using SQL Server Management Studio? You should test that to verify that a connection can be made in the first place.
[/quote]
Its not connecting, will contact the admin

Was this reply helpful? Yes No