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("..
