Data from AX to SQL and vice - versa

public server static void Main(Args _args)

{

    ODBCConnection                  myODBC;

    Statement                       myStatement;

    LoginProperty                   myLoginProperty;

    Resultset                       myResultset;

    SqlStatementExecutePermission   sqlPermission;

    str                             mySQLStatement;

    str                             myConnectionString;

    str myUserName = "BKITECH.COM\\Hmishra8100";

    str myPassword = "Buckeye1";

    ;

    myConnectionString = strfmt("UID=%1;PWD=%2",myUserName,myPassword);

    myLoginProperty    = new LoginProperty();

    myLoginProperty.setServer("STNSR041");

    myLoginProperty.setDatabase("MESDB_PRD");

    myLoginProperty.setOther(myConnectionString);

    try

    {

        myODBC      = new OdbcConnection(myLoginProperty);

        myStatement = myODBC.createStatement();

        //mySQLStatement = "SELECT * FROM BKI_Labels";

        //mySQLStatement  =   strFmt("insert into [MESDB_PRD].[dbo].[BKI_Labels] (printer_name,label_name, lot_no,item_id) values ('%1','%2','%3','%4')", 'Clamp', 'lablename', '123456789','Workbench clamp');

        mySQLStatement  = "insert dbo.BKI_Labels (printer_name,label_name, lot_no,item_id) values ('test', 'lablename', '012345678','Workbench clamp')";

        sqlPermission  = new SQLStatementExecutePermission(mySQLStatement);

        sqlPermission.assert();

        myResultSet = myStatement.executeQuery(mySQLStatement);

        while (myResultSet.next())

        {

            info(strFmt("%1, %2", myResultSet.getString(1), myResultSet.getString(4)));

        }

        CodeAccessPermission::revertAssert();

    }

    catch

    {

        error('Unexpected error');

    }

}

//INSERT dbo.BKI_Labels (printer_name,label_name, lot_no,item_id)    VALUES ('Clamp', 'lablename', '012345678','Workbench clamp')

=======================================================================
Read Data from SQL  to AX :

public void run()
{
      LogInProperty   Lp = new LogInProperty();
    OdbcConnection  myConnection;
    Statement       myStatement;
    ResultSet       myResult;
    str             sqlQuery;
    str             mydatasource = "SADAD_Staging_DB";
    str myDSN="SADAD_Staging_DB";
    str myUserName="shaja";
    str myPassword="dns@123";
    str myConnectionString;
    str       customerId = "1001";
     str sql, sql1, custAccountId;
    str CreatedDate, StagId, modifiedDateTime, queryread;
    SqlStatementExecutePermission perm, perm1;
    SADADBillStagingTable  billStagTable;
    SADADRejectedBillStagingTable   rejectedBillStagTable;
    Resultset                       resultSet, resultSetCount;
    AccountNum  AccountStatus;
    RecId  Id;
    BillID billid;
    str custAccount, batchId,billStatus,BillCategory, serviceType, billingNo, mobileNo;
    utcDateTime billTimeStamp;
    real amount;
    str status,billingaccount;
    SADAD_BillLogTable BillLogTable;
     str Billing_Account, BillStatusCode,BillNumber,DueAmount,LanguageId,LedgerTransType,OfficialId,OfficialIdType,IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS;
    //CodeAccessPermission codeaccessPermission = new CodeAccessPermission();
;
    Lp.setDSN("SADAD_Staging_DB");

    myConnection = new OdbcConnection(LP);

       if (myConnection)
    {
        //queryread = "SELECT * from [SADAD_Staging_DB].[dbo].[SADAD_RajectTable] where [SADAD_Staging_DB].[dbo].[SADAD_RajectTable].Upload = 0 ";

        queryread = "SELECT * from [SADAD_Staging_DB].[dbo].[BillsHistory]"; //where [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].status = rejected ";
        //Assert permission for executing the sql string.
      ////  perm = new SqlStatementExecutePermission(queryread);
       //// perm.assert();

        //Prepare the sql statement.
        myStatement = myConnection.createStatement();
        // myStatement.executeUpdate(sql);
        myResult = myStatement.executeQuery(queryread);
       //// CodeAccessPermission::revertAssert();

         while (myResult.next())
        {
          billingaccount  = myResult.getString(1);
          billid          = myResult.getString(11);
          status          = myResult.getString(15);

          ttsBegin;
            while select forUpdate BillLogTable where BillLogTable.BillId == billid //BillLogTable.ReferenceBillID == billid
            {
                // billStagTable.UploadStatus = NoYes::No;
                // billStagTable.ReferenceBillID = "";
                BillLogTable.Status = status;
                BillLogTable.update();
            }
          ttsCommit;
        }
            //sql1 = strFmt("insert into [SADAD_Staging_DB].[dbo].[SADAD_BillTable](billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno) select billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno from [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable] where  [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].status = 'rejected'"); - 31/0713

        sql1 = strFmt("insert into [SADAD_Staging_DB].[dbo].[Bills](billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno) select billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno from [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable] where  [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].IsRejected = '1'");



           //  sql1 = strFmt("insert into [SADAD_Staging_DB].[dbo].[SADAD_BillTable](billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno) select billid, Billing_Account, BillStatusCode, Billcategory, ServiceType,  BillNumber, BillTimeStamp, DueAmount, LanguageId, OfficialId, IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, mobileno from [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable] where  [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].status = 'rejected'");

             perm1 = new SqlStatementExecutePermission(sql1);
             perm1.assert();

            //Prepare the sql statement.
            myStatement = myConnection.createStatement();
            myStatement.executeUpdate(sql1);
            myResult = myStatement.executeQuery(sql1);
        // sql = strFmt(" DELETE  from [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable] where  [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].status = 'rejected' ");  - 31/07/13

        sql = strFmt(" DELETE  from [SADAD_Staging_DB].[dbo].[BillsHistory] where  [SADAD_Staging_DB].[dbo].[SADAD_BillLogTable].IsRejected = '1' ");
            perm = new SqlStatementExecutePermission(sql);
           // perm.assert();
            myStatement = myConnection.createStatement();
            myStatement.executeUpdate(sql);
            myResult = myStatement.executeQuery(sql);

            CodeAccessPermission::revertAssert();


   //// }
       myStatement.close();
    }
      else
    {
        error("Failed to log on to the database through ODBC.");
    }
    pause;

}
========================================================================
From AX to SQL :


public void run()
{
    LogInProperty   Lp = new LogInProperty();
    OdbcConnection  myConnection;
    Statement       myStatement;
    ResultSet       myResult;
    str             myConnectionString;
    SqlStatementExecutePermission perm;
    CustTrans        custTrans;
    CustTable        CustTableloc, custtableloc1;
    str              sql, sql1,  custAccountId, AccountStatus,BillStatusCode,BillNumber,BillTimeStamp,DueAmount,LanguageId,LedgerTransType,OfficialId,OfficialIdType,IntegrationIndicator,SuccessfullBillSMS,PaymentNotificationSMS, Billcategory, ServiceType ;
    BillID billid;
    str             queryread,queryread1, ReferenceID,custAccountId1,tmp, mobileno;
    SADADOfficialIdMaster officialIdMaster;
    SADADIntegIndi      integInd;
    SADAD_BillLogTable BillLogTable;
    str upperlimit,lowerlimit;
    str duedt, billCycle,createdOn;
    str a,b,temp;
    str N = N;
    real amount;
    str zeroamt,tmpBillHistBillAcct, tmpBillHistBillCycle, tmpBillHistBillAcct1, tmpBillHistBillCycle1;

    int counter;
    ;

    Lp.setDSN("SADAD_Staging_DB");
    myConnection = new OdbcConnection(LP);

    if (myConnection)
    {
        while   select CustTableloc
            where   CustTableloc.SADADCust == NoYes::Yes            &&
            (CustTableloc.SADADAccStatus == SADADAccStatus::Activate || CustTableloc.SADADAccStatus == SADADAccStatus::NewAccount)
            /**/
             //&& CustTableloc.AccountNum == "3231073"
            /**/
        {
            counter++;
            select  sum(AmountMST) from custTrans
                where custTrans.AccountNum == CustTableloc.AccountNum;
            if(custTrans.AccountNum == "3282139") break;
            custAccountId  = CustTableloc.AccountNum;
            Billcategory   = "ICDocBills";
            ServiceType    = "UTIL";
            BillNumber     = custAccountId;
            BillTimeStamp  = DateTimeUtil::toStr(DateTimeUtil::getSystemDateTime());//datetime2str(DateTimeUtil::getSystemDateTime());
            DueAmount      = num2str(custTrans.AmountMST,10,2,0,0);
            LanguageId     = "1";
            OfficialId     = SADADOfficialIdMaster::findbyAccountNumIsPrimary(CustTableloc.AccountNum).OfficialID;
            OfficialIdType = SADADOfficialIdMaster::findbyAccountNumIsPrimary(CustTableloc.AccountNum).OfficialIDType;
            duedt          = DateTimeUtil::toStr(DateTimeUtil::getSystemDateTime());
            upperlimit     = num2str(CustTableloc.SADADUpperLimit,10,2,0,0); //num2str(0,10,2,0,0);
            lowerlimit     = num2str(CustTableloc.SADADLowerLimit,10,2,0,0);
            a = int2str(mthOfYr(today()));
            if(strLen(angel) == 1)
            {
                a = '0' +a;
            }
            b = int2str(year(today()));
            billCycle      = a + b;
            createdOn      =DateTimeUtil::toStr(DateTimeUtil::getSystemDateTime());

            select officialIdMaster
                where officialIdMaster.AccountNum == CustTableloc.AccountNum;
            select integInd
                where integInd.FacilTypeId == officialIdMaster.OfficialIDType;
            IntegrationIndicator =SADADIntegIndi::findbyFacilitytypeId(officialIdMaster.OfficialIDType).IntegrationIndicator;

            select custtableloc1
                where custtableloc1.AccountNum == CustTableloc.AccountNum;
            mobileno       = custtableloc1.phone();

            //if(!mobileno)
                mobileno = "966591117959";
            /*else
                info(mobileno);*/

            if(CustTableloc.AccountNum)
            {
                    SuccessfullBillSMS = "عميلنا العزيز لقد صدرت فاتورتك، يرجى تسديد المبلغ الإجمالي المستحق"
                + DueAmount
                +  "  ريال سعودي قبل مرور 30 يما بحد اقصى مستخدما رقم الحساب "
                + custAccountId
                +  "عبر قنوات سداد" ;
                PaymentNotificationSMS = custAccountId+"ريال سعودي في حسابك رقم  #####   عميلنا العزيز شكرا لقد تم استلام مبلغ " ;// عميلنا العزيز"
            }

            queryread = "SELECT * FROM [SADAD_Staging_DB].[dbo].[Bills] where BillingAcct ='"+custAccountId+"'";
            myStatement = myConnection.createStatement();
            myResult = myStatement.executeQuery(queryread);

            while (myResult.next())
            {
                    ReferenceID = myResult.getString(1);
                    tmp = myResult.getString(devil);
            }

            queryread1 = "SELECT * FROM [SADAD_Staging_DB].[dbo].[BillsHistory] where BillingAcct ='"+custAccountId+"' and BillCycle = '"+billCycle+"'";
            myStatement = myConnection.createStatement();
            myResult = myStatement.executeQuery(queryread1);

            while (myResult.next())
            {
                tmpBillHistBillAcct = myResult.getString(devil);
                tmpBillHistBillCycle = myResult.getString(7);
                if(tmpBillHistBillAcct == BillNumber && tmpBillHistBillCycle == billCycle)
                {
                    tmpBillHistBillAcct1 = tmpBillHistBillAcct;
                    tmpBillHistBillCycle1 = tmpBillHistBillCycle;
                }
            }

            amount = any2real(DueAmount);
            info(strFmt("%1. Customer %2: Amount: %3", counter, custAccountId, amount));

            /*Check if the same amount has been uploaded recently.. it should not then*/
            if(SADAD_BillLogTable::existLastBillAmount(billCycle, custAccountId, amount))
                continue;// do not send the same amount again
            /**/
            /*Check in case any bill has been uploaded for the same customer within 24 hours, which is wrong*/
            if(SADAD_BillLogTable::existsBillInTheSameDay(billCycle, custAccountId))
                continue;
            /**/
            if((amount > 0.00) )
            {
                if(custAccountId != tmp)
                {
                    if(BillNumber == tmpBillHistBillAcct1 && billCycle == tmpBillHistBillCycle1)
                    {
                        BillStatusCode = "2";
                    }
                    else
                    {
                        BillStatusCode = "1";
                    }
                    if(!mobileno) break;
                    sql = strfmt("insert into [SADAD_Staging_DB].[dbo].[Bills](BillingAcct, BillStatusCode, BillCategory, ServiceType,  BillNumber, BillTimeStamp, AmountDue, UpperLimit, LowerLimit, DueDt, Language, SuccessfullBillSMS,PaymentNotificationSMS, MobileNo, BillCycle, CreatedOn) values ('%1','%2','%3','%4','%5','%6','%7','%8','%9','%10','%11', N'%12', N'%13','%14','%15','%16')",custAccountId, BillStatusCode, Billcategory, ServiceType, BillNumber, BillTimeStamp, DueAmount, upperlimit,lowerlimit,duedt, LanguageId, SuccessfullBillSMS, PaymentNotificationSMS, mobileno, billCycle, createdOn);

                }
                else
                {
                    /*Added by Amer, To check if the bill is processed already (exists in Bill History) or not*/
                    if(custAccountId == tmpBillHistBillAcct1 && billCycle == tmpBillHistBillCycle1)
                        BillStatusCode = "2"; // bill has been proceessed before, which means it needs to be updated
                    else
                        BillStatusCode = "1"; // bill has not been processed yet, which means we will need to process it
                    sql = strFmt(&quot..