Finance | Project Operations, Human Resources, ...

ODBC Connection in D365fo.

(0) Share

Report

Posted on by Srihari.molaka

339

Hi Experts,

I have created a new DB in SQL Server named it as temp. I need to read and write data the DB from D365fo. For that i have followed the below link.

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/how-to-connect-to-an-external-database-from-x-code.

for creating DSN, I have followed this https://kb.blackbaud.com/articles/Article/41081.

I am facing issue at the odbcConnection = new OdbcConnection(loginProperty);

I have mention my code below.

public static void main(Args _args)
    {   
        
        LoginProperty                   loginProperty;
        OdbcConnection                  odbcConnection;
        Statement                       statement;
        ResultSet                       resultSet;
        str                             sql, criteria;
        SqlStatementExecutePermission   perm;

        loginProperty       =   new LoginProperty();
        loginProperty.setDSN("DSN Name");        
        loginProperty.setDatabase("Database");      
         
        odbcConnection      =   new OdbcConnection(loginProperty);
        if(odbcConnection)
        {
            sql = "SELECT * FROM MyTable WHERE FIELD = "
                  criteria
                  " ORDER BY FIELD1, FIELD2 ASC ;";
    
            //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())
            {
                //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);
            }
    
            //Close the connection.
            resultSet.close();
            statement.close();
            info(strFmt("Connection is established"));
        }
        else
        {
            Error("Failed to login to database through ODBC");
        }

I have tried creating DSN with Sql server and Sql server native client 11.0 drivers but i am getting an exception like "login failed for user".

Can anyone guide me the correct path or any links to succeed the connection.

Thanks in advance.

with regards,

srihari.

I have the same question (0)

All responses (18)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Please do remember that in D365FO cloud you don't have access to the test/prod servers so you can't create any DSN there.

Therefore the documentation for AX2012 is not valid for D365FO.

But you can find some discussions how to achieve it in D365FO, one is here: www.axug.com/.../viewthread

Was this reply helpful? Yes No
Suggested answer

Christoph Thaler 5,442 on at

Like (0)

Report

Hi srihari,

you are not able / allowed to connect to a D365FO cloud database. There are some other feature available. You can get an overwiew of the possibilities here: docs.microsoft.com/.../integration-overview

Depending on your scenario you have to pick the right solution.

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

Like (0)

Report

This old API is horrible anyway and I wouldn't use, maybe except of extremely simple cases.

Why would you write T-SQL code manually, without any code completion, syntax verification, table and field names checking, without any type checking and so on? It's time consuming, error-prone and difficult to maintain.

Consider the Entity Framework (database-first workflow) as one of the alternative. It'll create classes from the existing database schema and you'll work with tables in object-oriented manner, benefiting from IntelliSense, compile-type name and type checking all these things.

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

Like (0)

Report

Hi molaka,

Your external DB needs to be visible to D365, and you could use System.Data.SqlClient library to connect the database.

https://allaboutdynamic.com/2019/02/16/d365-ax7connect-to-an-external-sql-database-using-x/

The Bring Your Own Database solution might work for you as well, you could push data from your d365 into an Azure SQL database(BYOD) using data management.

docs.microsoft.com/.../export-entities-to-your-own-database

Was this reply helpful? Yes No
Srihari.molaka 339 on at

Like (0)

Report

Hi Nikolaos,

Thanks for your reply,

I do not have cloud access.

with regards,

srihari.

Was this reply helpful? Yes No
Srihari.molaka 339 on at

Like (0)

Report

Hi Christoph,

Thanks for your reply,

with regards,

sirhari.

Was this reply helpful? Yes No
Srihari.molaka 339 on at

Like (0)

Report

Hi Martin,

Thanks for your reply,

with regards,

srihari.

Was this reply helpful? Yes No
Srihari.molaka 339 on at

Like (0)

Report
Hi WU,

Thanks for your reply,

I have tried this link : https://allaboutdynamic.com/2019/02/16/d365-ax7connect-to-an-external-sql-database-using-x/

with this link i have achieved read operation(retrieving the data) but i am not able to update the records, I mention my code below

public static void main(Args _args) { SqlConnection conn = new SqlConnection("Server = ****; Database = **; User Id = ***; Password = ***; Min Pool Size = 5; Max Pool Size = 100"); str sqlL = @"select * from CustTable"; str update = "Update CustTable SET AccountNum = srihari1234 where AccountNum = ****01"; SqlCommand command = new SqlCommand(sqlL, conn); SqlCommand updateCmd= new SqlCommand(update, conn); System.Data.SqlClient.SqlDataReader reader; System.Data.SqlClient.SqlParameterCollection parameterCollection; new InteropPermission(InteropKind::ClrInterop).assert(); try { conn.Open(); try { reader = command.ExecuteReader(); while(reader.Read()) { str accountNum = reader.get_Item("AccountNum"); Info(accountNum); parameterCollection = command.get_Parameters(); parameterCollection.AddWithValue("DataAreaId","***"); boolean needToUpdate = true; if(needToUpdate) { //update = "Update CustTable SET AccountNum ="" srihari1234 ""WHERE AccountNum = "" ****01 ""; updateCmd.CommandText = update; updateCmd.ExecuteNonQuery(); } Info(accountNum); } reader.Dispose(); } catch { reader.Dispose(); } catch(Exception::CLRError) { reader.Dispose(); } conn.Dispose(); } catch { conn.Dispose(); } catch(Exception::CLRError) { conn.Dispose(); } command.Dispose(); CodeAccessPermission::revertAssert(); }

please help me for update the records.

With regards,

srihari.

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

Like (0)

Report

Could you clarify what do you mean by "not able to update the records"?

Was this reply helpful? Yes No
Srihari.molaka 339 on at

Like (0)

Report

Hi Nikolaos,

Thanks for the reply,

I mean the code is not working for update the records.

with regards,

srihari.

Was this reply helpful? Yes No