Naresh DAX: Integration using ODBC connection in Ax 2012

SqlStatementExecutePermission perm;
EOxegenDSNSetup EOxegenDSNSetup;
EO2MembersCreationLog MembersCreationLog;
LoginProperty loginproperty;
OdbcConnection odbcconnection;
Statement statement;
ResultSet resultset;
str myConnectionString,sql,ret,
firstName,lastName ,othernames, surName,
accountType,customerGroup,mobile,addresscity,
addresscountry,addressline,addresscounty,postcode,
accountgroup,gin,pin,govId,other,currency,
gender,counter,email,status,processed,
branchid,eo2customertype, beneficary, jobtitle,
dob,integrated,eo2pmtype,partnerNumber, cRMCompany;
str taxexempt;
int64 refrecord;

[SysEntryPointAttribute(false)]
public void processRecords()
{
select firstOnly SystemDSN,DBUserName,DBPassword,
SystemDSN,SetServer,SetDatabase from EOxegenDSNSetup;
myConnectionString = strfmt("Dsn=%1;UID=%2;PWD=%3",EOxegenDSNSetup.SystemDSN,
EOxegenDSNSetup.DBUserName,
EOxegenDSNSetup.DBPassword);
loginProperty = new LoginProperty();
loginProperty.setDSN(EOxegenDSNSetup.SystemDSN);
loginProperty.setServer(EOxegenDSNSetup.SetServer);
loginProperty.setDatabase(EOxegenDSNSetup.SetDatabase);
loginProperty.setOther(myConnectionString);
odbcConnection = new ODBCConnection(loginProperty);
if(odbcconnection)
{
odbcconnection.ttsbegin();
sql = strFmt("select top 10 REFRECID, FIRSTNAME, LASTNAME, MIDDLENAME,"
+"EMAILADDRESS1, MOBILEPHONE, ADDRESS1_CITY, ADDRESS1_LINE2,"
+"ADDRESS1_COUNTRY, ADDRESS1_COUNTY, ADDRESS1_POSTCODE,"
+"Account_Group, CURRENCY, GENDER, GIN_OCCUPATION, UAP_PINNUMBER,"
+"GOVERNMENTID, UAP_OTHERNAME, STATUS, PROCESSED_TIME, UAP_BRANCHID,"
+"EO2_CUSTOMER_TYPE, BENEFICARY, JOBTITLE, DATEOFBIRTH, INTEGRATED,"
+"EO2_PM_TYPE,PARTNERNUMBER, CRMCompany from EO2_CUSTOMERS where PARTNERNUMBER = ''");
perm= new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcconnection.createStatement();
resultset = statement.executeQuery(sql);
while(resultset.next())
{
refrecord = resultset.getInt64(1);
firstName = resultset.getstring(2);
surName = resultset.getstring(3);
othernames = resultset.getstring(4);
email = resultset.getstring(5);
mobile = resultset.getstring(6);
addresscity = resultset.getstring(7);
addressline = resultset.getstring(8);
addresscountry = resultset.getstring(9);
addresscounty = resultset.getstring(10);
accountgroup = resultset.getstring(12);
currency = resultset.getstring(13);
gender = resultset.getstring(14);
gin = resultset.getstring(15);
pin = resultset.getstring(16);
govId = resultset.getstring(17);
other = resultset.getstring(18);
status = resultset.getstring(19);
processed = resultset.getstring(20);
branchid = resultset.getstring(21);
eo2customertype = resultset.getstring(22);
beneficary = resultset.getstring(23);
jobtitle = resultset.getstring(24);
dob = resultset.getstring(25);
integrated = resultset.getstring(26);
eo2pmtype = resultset.getstring(27);
partnerNumber = resultset.getstring(28);
cRMCompany = resultset.getstring(29);
this.createNewCustomer();
}
resultset.close();
statement.close();
odbcconnection.ttscommit();
info(strFmt("Successfully processed %1 records.",counter));
}
else
{
error("Failed to logon to the Database through ODBC");
}
}
--------------------------------------------------------------------------------------------------------------------------
second one
-------------------------------------------------------------------------------------------------------------------------

[SysEntryPointAttribute(false)]
public void processRecords()
{
SqlStatementExecutePermission perm;
EOxegenDSNSetup EOxegenDSNSetup;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, myConnectionString;
int counter;

select firstOnly SystemDSN,DBUserName,DBPassword,SystemDSN,
SetServer,SetDatabase from EOxegenDSNSetup;
myConnectionString=strfmt("Dsn=%1;UID=%2;PWD=%3",EOxegenDSNSetup.SystemDSN,EOxegenDSNSetup.DBUserName,EOxegenDSNSetup.DBPassword);
loginProperty = new LoginProperty();
loginProperty.setDSN(EOxegenDSNSetup.SystemDSN);
loginProperty.setServer(EOxegenDSNSetup.SetServer);
loginProperty.setDatabase(EOxegenDSNSetup.SetDatabase);
loginProperty.setOther(myConnectionString);
odbcConnection = new ODBCConnection(loginProperty);

if (odbcConnection)
{
sql = "SELECT * FROM EO2_CITY_MASTER;";
perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next())
{
info(strFmt('%1-%2',resultSet.getString(1),resultSet.getString(2)));
counter++;
}
resultSet.close();
statement.close();
info(strFmt("Successfully processed %1 records.",counter));
}
else
{
error("Failed to log on to the database through ODBC.");
}
}
-----------------------------------------------------------------------------------------------------------------------
Get Country codes using ODBC
-----------------------------------------------------------------------------------------------------------------------
[SysEntryPointAttribute(false)]
public void processRecords()
{
SqlStatementExecutePermission perm;
EOxegenDSNSetup EOxegenDSNSetup;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, myConnectionString;
int counter;

select firstOnly SystemDSN,DBUserName,DBPassword,SystemDSN,
SetServer,SetDatabase from cicEOxegenDSNSetup;
myConnectionString=strfmt("Dsn=%1;UID=%2;PWD=%3",cicEOxegenDSNSetup.SystemDSN,cicEOxegenDSNSetup.DBUserName,cicEOxegenDSNSetup.DBPassword);
loginProperty = new LoginProperty();
loginProperty.setDSN(cicEOxegenDSNSetup.SystemDSN);
loginProperty.setServer(cicEOxegenDSNSetup.SetServer);
loginProperty.setDatabase(cicEOxegenDSNSetup.SetDatabase);
loginProperty.setOther(myConnectionString);
odbcConnection = new ODBCConnection(loginProperty);

if (odbcConnection)
{
sql = "SELECT COUNTRY_CD ,COUNTRY_NAME FROM COUNTRY_CODES;";
perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next())
{
info(strFmt('%1-%2',resultSet.getString(1),resultSet.getString(2)));
counter++;
}
resultSet.close();
statement.close();
info(strFmt("Successfully processed %1 records.",counter));
}
else
{
error("Failed to log on to the database through ODBC.");
}
}

----------------------------------------------------------------------------------------------------------------------
Update Customer
----------------------------------------------------------------------------------------------------------------------

public void updateCustomerAccount(RefRecId refRecId, Custaccount accountnum)
{
SqlStatementExecutePermission fixedAssetTransferUpdate;
Statement statementTransferUpdate;
ResultSet resultSetTableTransferUpdate;
OdbcConnection odbcConnection3;
str sql3;

odbcConnection3 = new ODBCConnection(loginProperty);
statementTransferUpdate = odbcConnection3.createStatement();
sql3 = strFmt("UPDATE EO2_CUSTOMERS set PARTNERNUMBER ='"+strfmt('%1',accountnum)+"' WHERE EO2_CUSTOMERS.REFRECID = '"+strfmt('%1',refRecId)+"'");
fixedAssetTransferUpdate = new SqlStatementExecutePermission(sql3);
fixedAssetTransferUpdate.assert();
statementTransferUpdate.executeUpdate(sql3);

resultSetTableTransferUpdate = statementTransferUpdate.executeQuery(sql3);
statementTransferUpdate.close();

Thursday, 13 September 2018

Integration using ODBC connection in Ax 2012