// 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.”);
}
}