EXECUTING SQL DIRECTLY FROM X++ RUN SQL QUERY FROM AX 2012 CODE HOW TO DYNAMICS AX

Executing SQL directly from X++

The X++ language supports a number of ways to execute native SQL against any SQL data source.

Example #1: Retrieve data:
Since this example uses a Connection class, data is retrieved from the database where Axapta is currently connected.
void Sample_1(void)
{
Connection Con = new Connection();
Statement Stmt = Con.createStatement();
ResultSet R =Stmt.executeQuery(‘SELECT * from table’);
while ( R.next() )
{
print R.getString(1);
}
}
Example #2: Manipulating data (deletion):
void Sample_2(void)
{
str sql;
Connection conn;
SqlStatementExecutePermission permission;
;
sql = ‘delete from salstable’;
permission = new SqlStatementExecutePermission(sql);
conn = new Connection();
permission = new SqlStatementExecutePermission(sql);
permission.assert();
conn.createStatement().executeUpdate(sql);
// the permissions needs to be reverted back to original condition.
CodeAccessPermission::revertAssert();
}

database staging import

public class VikasStagingPricingMasterImport

{

    LoginProperty loginProp;

    ODBCConnection conn;

    Statement statement1;

     Set existingItemIds;

}

private void ConnectToStagingDB()

{

     // Set Server Database

    loginProp = new LoginProperty();

     loginProp.setDSN(abc);

   // loginProp.setServer(bcd);

    loginProp.setDatabase(‘xyz_Db'); 

    // Create Connection and SQL Statement

    conn = new ODBCConnection(loginProp);

    statement1 = conn.createStatement();

}

public static void main(Args _agrs)

{

   System.Exception ex;

   VikasStagingPricingMasterImport importObj=new VikasStagingPricingMasterImport();

 if(Box::okCancel("To you want to Import Price Master ?", DialogButton::Cancel," Price Master Import") == DialogButton::Ok)

        {

    try

    {

     importObj.ConnectToStagingDB();

     importObj.ImportPricingMasters();

    }

    catch( Exception::CLRError)

    {

         ex = ClrInterop::getLastException();

        if (ex != null)

         {

            ex = ex.get_InnerException();

            if (ex != null)

            {

                error("Unexpected Error Occured.   " + ex.ToString() +   "  Please contact Administrator." );

            }

        }

   }

    info(" Import of Pricing master has finished Successfully.");

    }

}

private str formatItemIdsForQuery(Set conItemIds)

{

    SetEnumerator sm;

    str itemIds;

    sm=conItemIds.getEnumerator();

    itemIds="";

    while (sm.moveNext())

        {

            itemIds= itemIds + ",'" +  sm.current() + "'" ;

        }

    if(strLen(itemIds) >0)

    {

        itemIds=subStr(itemIds,2, strLen(itemIds));

    }

    return itemIds;

}

//Sales Price master upload in the system

private void ImportPricingMasters()

{

       str queryPricingMaster,itemId,existingItemIdsList,tempValue;

       ResultSet resultSet;

       InventTable inventTable;

       itemId tempItemId;

      AifEntityKeyList                        keys;

       VIK_RRP_Staging                       RRPStaging;

       PricePriceDiscJournalService            PriceDiscSvc;

       PricePriceDiscJournal                   PriceDiscJour;

        PricePriceDiscJournal_PriceDiscAdmTrans PriceDiscJourAdmTrans;

        PricePriceDiscJournal_InventDim         PriceDiscJourDim;

         #AviFiles

    SysOperationProgress progress1 = new SysOperationProgress();

        PriceDiscSvc = PricePriceDiscJournalService::construct();

        PriceDiscJour = new PricePriceDiscJournal();

        PriceDiscJourAdmTrans = PriceDiscJour.createPriceDiscAdmTrans().avikNew();

        PriceDiscJourDim = PriceDiscJourAdmTrans.createInventDim().avikNew();

        PriceDiscJourDim.parminventDimId("Allblank");

        progress1.setCaption("Updation of pricing master is in progress…");

    progress1.setAnimation(#AviUpdate);

    existingItemIds = new Set(Types::String);

    queryPricingMaster = "Select * FROM [Staging_Db].[dbo].[VIK_STAGING] where DATAUPLOADED = 0 ";

    resultSet = statement1.executeQuery(queryPricingMaster);

    ttsBegin;

    while (resultSet.next())

    {

        itemId="";

        TempValue=resultSet.getString(1);

        tempItemId=resultSet.getString(2);

         progress1.setText(strfmt("Item ID  %1", tempItemId));

        // Set PriceDiscJourAdmTrans

          while select forUpdate * from inventTable

            where inventTable.ItemId == tempItemId

        {

            PriceDiscJourDim.parminventDimId("Allblank");

            PriceDiscJourAdmTrans.parmAccountCode(TableGroupAll::GroupId);

            PriceDiscJourAdmTrans.parmAccountRelation(TempValue);

            PriceDiscJourAdmTrans.parmInventDim().avik(PriceDiscJourDim);

            PriceDiscJourAdmTrans.parmItemRelation(inventTable.ItemId);

            PriceDiscJourAdmTrans.parmItemCode(TableGroupAll::Table);

            PriceDiscJourAdmTrans.parmAmount(resultSet.getReal(3));

            PriceDiscJourAdmTrans.parmFromDate(resultSet.getDate(4));

            PriceDiscJourAdmTrans.parmToDate(resultSet.getDate(7));

            PriceDiscJourAdmTrans.parmrelation(PriceType::PriceSales);

            PriceDiscJourAdmTrans.parmCurrency("INR");

            PriceDiscJourAdmTrans.parmQuantityAmountFrom(0);

           // Post PriceDiscJour

            keys = PriceDiscSvc.create(PriceDiscJour);

            itemId=inventTable.ItemId;

        }

        if(strLen(itemID)>0)

        {

            existingItemIds.avik(itemID);

        }

    }

    existingItemIdsList=this.formatItemIdsForQuery(existingItemIds);

    if(strLen(existingItemIdsList)>0)

    {

    queryPricingMaster = strFmt("UPDATE [dbo].[test] SET [DATAUPLOADED] = 1, LASTUPDATED = '%1' WHERE DATAUPLOADED = 0 AND ITEMNUMBER in (%2)",today(), existingItemIdsList);

    statement1.executeUpdate(queryPricingMaster);

    }

    ttsCommit;

}