Connect to an external database using X++ code in AX 2012
Below are the steps to be performed,
- To connect to an external database using ODBC connection, first create a DSN. To create a DSN refer the link https://support.microsoft.com/en-us/kb/300596
- Write the below code:
Note : The database login credentials are given in the DSN. You can use windows credentials or provide SQL credentials, if required.
- In the above code, the below lines of code will initialise the connection to the database.
sq = new SqlSystem(); loginProperty = new LoginProperty(); loginProperty.setDSN(/*your dsn name here*/); loginProperty.setDatabase(/*your database name here*/); oDBCConnection = new ODBCConnection(loginProperty);
- The below lines of code will execute the query and store the data in a resulSet.
sqlStmnt = strFmt("select * from ABC"); //write your query here statement1 = odbcConnection.createStatement(); perm1 = new SqlStatementExecutePermission(sqlStmnt); perm1.assert(); myResult1 = statement1.executeQuery(sqlStmt1);
- We can also execute the stored procedures in the database. Write the below code to do so.
odbcConnection = new OdbcConnection(loginProperty); sqlStmt1 = strFmt("exec [Stored Procedure name]"); perm = new SqlStatementExecutePermission(sqlStmt1); perm.assert(); statement1 = odbcConnection.createStatement(); statement1.executeUpdate(sqlStmt1); CodeAccessPermission::revertAssert();
- Suppose you execute more than one stored procedure in the same code, you may get below error.
"SQL error description: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt"
- To eliminate this error, write below statement after every stored procedure is executed,
statement1.close();
- If there are multiple external databases on different servers, then you can create separate DSN to connect to each server. This connection information can be stored in a master table containing the DSN name, the database name.
- The X++ code can then use the connection details from the master table as shown below.
loginProperty = new LoginProperty(); loginProperty.setDSN(masterTable.databseName); loginProperty.setDatabase(masterTable.databseName);
Related posts:
Unlocking Seamless Financial Operations: The Power of Stripe Integration with Business Central
How to Send D365 CRM Emails with Attachments Using Power Automate
Advance warehouse management – Work Classes and Work Templates in Microsoft D365 F&O - Part 5
Dynamics 365 Business Central: Setting Up an Approval Workflow with Flexible Approvers