Connecting other DB through X++ code in AX 2012

// X++, Main method in a class.

static public void Main(Args _args)

{

LoginProperty                       loginProperty;

OdbcConnection                      odbcConnection;

Statement                           statement;

ResultSet                           resultSet;

str                                 sql, criteria;

SqlStatementExecutePermission       perm;

;

 

// Set the information on the ODBC.

loginProperty = new LoginProperty();

loginProperty.setDSN(“RMSHQ”);

loginProperty.setDatabase(“CBHQ”);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

 

if (odbcConnection)

{

/* sql = “SELECT * FROM MYTABLE WHERE FIELD = “

            + criteria

           + ” ORDER BY FIELD1, FIELD2 ASC ;”;*/

 

 

// sql = “SELECT * FROM V_TotalSales”;

// sql = “SELECT VTS.BatchNumber, VTS.ID, VTS.[Date] FROM [CBHQ].[dbo].[V_TotalSales] VTS GROUP BY   VTS.BatchNumber, VTS.ID, VTS.DATE”;

criteria = strFmt(” Where VTS.ID = ‘%1’ and VTS.[Date] = ‘%2’ “, ‘3002’, date2str(mkDate(28,04,2016), 213, DateDay::Auto, DateSeparator::Slash, DateMonth::Digits2, DateSeparator::Slash, DateDay::Auto));

 

 

sql = “SELECT VTS.ID, VTS.BatchNumber, VTS.[Date] FROM [CBHQ].[dbo].[V_TotalSales] VTS ”

+ criteria

+ ” GROUP BY   VTS.BatchNumber, VTS.ID, VTS.[DATE]”;

 

//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.

//COLUMN SEQUENCE

// STOREID, STORE NAME, BATCHNUMBER, DATE, ITEMID, QTY, LINEAMOUNT, DISCOUNT AMOUNT, SALES PRICE

//info(strFmt(“%1, %2, %3, %4, %5, %6, %7, %8, %9 “, resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7), resultSet.getString(8),resultSet.getString(9)));

info(strFmt(“Batch Number %1,     Store ID : %2,     Trans Date:%3”, resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)));

 

}

 

//Close the connection.

resultSet.close();

statement.close();

}

else

{

error(“Failed to log on to the database through ODBC.”);

}

}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s