Wednesday, November 19, 2008

Connection from AX to an External Database

There are a few options. We can create an ODBC connection on a local machine or just to connect directly without creating ODBC record.


For exmaple, we want to check whether some records exist in an external table. We should create a OdbcConnection with appropriate LoginProperty and permit to execute a SQL statement by means of SqlStatementExecutePermission class. 



server boolean checkExternalDB()
{
//connection parameters
#define.ExternalTableName("CustTable")
#define.ExternalFieldName("AccountNum")
#define.ExternalSQLServerName("SRVAXSQL2005")
#define.ExternalSQLDBName("DAXdb401_Standard_DEV")
LoginProperty LP = new LoginProperty();
OdbcConnection myConnection;
SqlStatementExecutePermission permission;
Statement myStatement;
str sqlStmt = "";
ResultSet myResult;
boolean ret = true;
;

LP.setServer(#ExternalSQLServerName);
LP.setDatabase(#ExternalSQLDBName);
try
{
myConnection = new OdbcConnection(LP);
}
catch
{
info("Check connection parameters. "+funcName());
ret = checkFailed(strfmt("External DB Connection error in: %1"), #ExternalSQLDBName);
}

myStatement = myConnection.createStatement();
//anything you want to get from the external table
sqlStmt = "SELECT count (RecId) FROM "+#ExternalTableName+ " where "+#ExternalFieldName + " = '" + this.AccountNum+"'";

permission = new SqlStatementExecutePermission(sqlStmt);
permission.assert();

myResult = myStatement.executeQuery(sqlStmt);
while (MyResult.next())
{
if (MyResult.getInt(1) > 0)
{
//yes, records exist in the external table
ret = checkFailed(strfmt("@LBA53"+"\n"+funcName(), strfmt("[%1].[%2].[%3]", #ExternalSQLServerName, #ExternalSQLDBName, #ExternalTableName)));
break;
}
}

CodeAccessPermission::revertAssert();

return ret;
}

1 comment:

Unknown said...

i connect my ax client with remote sqlserver database it will show errors.

1) ODBC operation failed.Unable to log on to the database.

2) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ASCENDERS\vasanth'.

3)Object 'OdbcConnection' could not be created

but it is work fine when i woked the same code in Server. I dont know how to solve the problem. plz help me with your valuable ideas..

Thanks in Advance,

Vasanth.A