How to display the fields from the table in SQL2005

Hello Xperts,

How to use the table from sql2005 (i.e table is not created through AX) in Ax2009 job. Also i need to get fields of that Sql table in Ax. How to declare the variable for fields and table. I am in a big deal of trouble…Plz help…

For ex, Table in SQL 2005: Invest.

Fields : id(real), rate(real), dealerid(string).

How to select the values and to display in ax?

I have created a ODBC connection, connection is tested successfully, Now to code for that?

See an example here.

You cannot declare a variable pointing to that table, because AX knows nothing about it (AX looks to its metadata, not to the database).

void UpdateOrganizationUnitId()
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, criteria;
SqlStatementExecutePermission perm;
UB_JobId_Temp Job_Temp, Job_Temp1, Job_Temp2;
DirPartyInternalOrganizationTable dirPartyIOTable, dirPartyIOTable1;
DirPartyId partyId;
NumberSequenceReference numberSequenceReference;
NumberSeq numberSeq;
;
delete_from Job_Temp2;
loginProperty = new loginProperty();
loginProperty.setDSN(“UBHRMS”);
odbcConnection = new OdbcConnection(loginProperty);
if(odbcConnection)
{
Team Id or Organization Unit Id set up
sql = “Select * from AX_Repl_Org_Unit”;
perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
while(resultSet.next())
{
ttsbegin;
Job_Temp.JobId = resultSet.getString(1);
Job_Temp.Description = resultSet.getString(2);
Job_Temp.insert();
ttscommit;
}

while select Job_Temp1
{
select forupdate dirPartyIOTable where dirPartyIOTable.OrganizationUnitId == Job_Temp1.JobId;
//if JobId already present in Organization table, Update description
if(dirPartyIOTable)
{
ttsbegin;
dirPartyIOTable.Description = Job_Temp1.Description;
dirPartyIOTable.update();
ttscommit;
}

//if Job Id not persent in Organization table, then insert into Organization table
if(!dirPartyIOTable)
{
numberSequenceReference = DirParameters::numRefDirPartyId();
numberSeq = NumberSeq::newGetNum(numberSequenceReference, false,true);
partyId = numberSeq.num();
ttsbegin;
dirPartyIOTable1.OrganizationUnitId = Job_Temp1.JobId;
dirPartyIOTable1.Description = Job_Temp1.Description;
dirPartyIOTable1.PartyId = partyId;
dirPartyIOTable1.ValidFromDateTime = datetimeutil::getSystemDateTime();
dirPartyIOTable1.ValidToDateTime = datetimeutil::maxValue();
dirPartyIOTable1.insert();
ttscommit;
}
}
}
}

This is one sample coding for that only…But i am not able to understand the code fully…How they are using that so that i can do that with understanding…Plz help…Thanx in advance

Hi,

I have done that The following code will help :

static void UB_AutoHangerPOC(Args _args)
{
//InvSlv _InvSlv;
UB_AutoHanger _UB_AutoHanger; // the table created in Ax
//eal invno;
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, criteria;
SqlStatementExecutePermission perm;
;
loginProperty = new LoginProperty();
loginProperty.setDSN(“UBTest”); // Go to administrative tools, DataSources(ODBC) , System DSN tab, click Add, SqlServer ,give the connection name and then configure the Sql server you want to connect, database name and do test connection and click ok. UBTet is the ODBC connection name i have given…

odbcConnection = new OdbcConnection(loginProperty);
if(odbcConnection)
{
//Team Id or Organization Unit Id set up
sql = “Select * from InvSlv”; // InSlv is the table name from the database i have used with the ODBC connection

perm = new SqlStatementExecutePermission(sql);
perm.assert();
// breakpoint; // The rest of the code you can understand that i hope so…
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
ttsbegin;
while(resultSet.next())
{

// _UB_AutoHanger.clear();
_UB_AutoHanger.Invno = resultSet.getReal(1);
_UB_AutoHanger.Rate = resultSet.getReal(16);
_UB_AutoHanger.dealer_id = resultSet.getString(17);
_UB_AutoHanger.insert();
}
ttscommit;
}

else
{
info(“No ODBC Connection”);
}

}

Thats it…:slight_smile: