Hmm, Most of what i am going to say in this here, will be childish, but still....
DimensioN is built on top of JBOSS. It has some inbuilt functionality to export data from one database to another, with some rules associated with it. and that is great, But what it lacks is you cannot pick and choose which records you want to transfer. That was a problem.
There is a way to atttain the solution for this problem in DimensioN, but the application architecture which we had decided upon did not permit, or to be correct will make it too complex, for the future DimensioN developer to understand and tackle any problems if they should arise. this would invove the logic as follows,
rModify --> DbImport --> rModify....
And as you can see rModify is a bit of a highlevel feature, and many still dread to use it. so the only thing which i could think off was using some thing else which is out of DimensioN but still could be used with it, like Ant target or something.
My first choice was to use Ant target, coz i have done database manipulation with it for an other project, previously. As i can use it as a template and proceed off, and that would save time too. But, i ran into trouble with it, First i had to make javascript to work from inside Ant and then after i had succeded, when i had tried to do the database manipulation, it was creating a exception saying Illegal Access Exception, some AntClassLoader came in and some bullshit which i was not able to understand. and i had waited for 3 days for OS support, but when i came to know that help might not arrive on time, i wanted to put in something to get things going till the support arrived.
Another two days and that jsp expert had not finished the job yet, and they were pressing me for an answer, as though i was a part of that project. ( i had moved to another one even b4 the problem started). Well then, i had to sit back and write the code again for the jsp. (i hate to code. but this was as usual Reverse Engineering, as i would put it,coz i know the basics to do anything, i just did not have the time to proceed to write things from scratch.) I had set a deadline of 4 hours for me to finish. and i sat down to write the code. it took me 3 hours and a half to do it. and it was pretty much good, without any problems, and is working fine.
I know there are still 2 potential pit falls..... (1) I haven't used a try catch block, so if any exceptions are thrown the page will be a error. which i will have to correct in the future... (2) I haven't used parameterized prepare statement, so if the data is going to have any problems like a single quote, or anything that would hamper a insert statement on syntax, yet again the page will break with an error.
So with these details, i give you the code. it might be helpful to someone.
-----------------------------------------------------------------------
<%@ page import="java.sql.*" %>
<%@ page language="java" session="true" %>
<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import="org.apache.commons.fileupload.disk.*"%>
<%@ page import="org.apache.commons.fileupload.servlet.*"%>
<%@ page import="org.springframework.jdbc.core .*"%>
<%
////////////////////////////////////////////////////////////////////////////////////////////////////////
// ExporttoErp.jsp //
// //
// File Created on September 5th 2008 at 1807 hrs. //
// Last Modified on September 5th 2008 at 2030 hrs. //
// Modified By Anbarasan //
// //
// This page exports data from dimension database to navision database. //
// We get the record identifier from the url. //
// Then we proceed to retrive data from dimension and then push it into navision. //
/////////////////////////////////////////////////////////////////////////////////////////////////////////
%>
<html>
<head>
<title>
DbExport utility to export Single Record of choice to ERP.
</title>
</head>
<body>
<%
String id=request.getParameter("docid");
////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see the primary key id that is being passed. //
///////////////////////////////////////////////////////////////////////////////////////////////
// out.println(id);
////////////////////////////////////////////////////////
// Connection to Dimension's Database //
////////////////////////////////////////////////////////
DataSource dimds = (DataSource)com.orangescape.hce.server.services.CsServiceLocator.getInstance().fetchObject("java:dimension");
Connection dimconn = null;
dimconn = dimds.getConnection();
//////////////////////////////////////////////////
// Connection to Navision's Database //
//////////////////////////////////////////////////
DataSource navds = (DataSource)com.orangescape.hce.server.services.CsServiceLocator.getInstance().fetchObject("java:erp");
Connection navconn = null;
navconn = navds.getConnection();
String dimparentqry="select [Amount],[Bill-to Customer No_],[Chain Name],[Created On Date],[Customer_PO_date],[External Document No_],[Location Code],[Octroi Amount],[Octroi Applicable],[Order Type],[Order Value],[Project Date],[project No_ updation],[Promised Delivery Date],[Req_ delivery Date],[Sales Cordinator Code],[Sales Cordinator Name],[Salesperson Code],[Sell-to Customer No_],[Shortcut Dimension 1 Code],[Shortcut Dimension 12 Code],[DocID] from [xxxxxxxx Pvt_ Ltd_$temp sales header] where docid = '"+id+"'";
String dimchildqry="select [Approx_ Cost],[Line No_],[No_],[puchase order no_line],[Quantity],[SODetailsId],[Unit of Measure],[Unit Price],[Variant Code] from [xxxxxxxxx Pvt_ Ltd_$temp sales line] where SODetailsId='"+id+"'";
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data retrival query from dimension database is..... //
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println("<b>ParentQuery</b><br>"+dimparentqry);
// out.println("<br><br><br>");
// out.println("<b>ChildQuery</b><br>"+dimchildqry);
// out.println("<br><br><br>");
//////////////////////////////////////////////////////////////////////////////////////////////////////////
Statement dimst=dimconn.createStatement();
ResultSet dimrs=dimst.executeQuery(dimparentqry);
while(dimrs.next()){
String navparentqry="insert into [xxxxxxxx Pvt_ Ltd_$temp sales header] ([Amount],[Bill-to Customer No_],[Chain Name],[Created On Date],[Customer_PO_date],[External Document No_],[Location Code],[Octroi Amount],[Octroi Applicable],[Order Type],[Order Value],[Project Date],[Project No_],[Promised Delivery Date],[Req_ delivery Date],[Sales Cordinator Code],[Sales Cordinator Name],[Salesperson Code],[Sell-to Customer No_],[Shortcut Dimension 1 Code],[Shortcut Dimension 12 Code],[SO Document ID],[SO No_ Of SCM]) values (";
navparentqry = navparentqry + dimrs.getString("Amount") + ", ";
navparentqry = navparentqry + "'" + dimrs.getString("Bill-to Customer No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Chain Name") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Created On Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Customer_PO_date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("External Document No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Location Code") + "', ";
navparentqry = navparentqry + dimrs.getString("Octroi Amount") + ", ";
navparentqry = navparentqry + dimrs.getString("Octroi Applicable") + ", ";
navparentqry = navparentqry + "'" + dimrs.getString("Order Type") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Order Value") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Project Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("project No_ updation") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Promised Delivery Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Req_ delivery Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sales Cordinator Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sales Cordinator Name") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Salesperson Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sell-to Customer No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Shortcut Dimension 1 Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Shortcut Dimension 12 Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("DocID") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("DocID") + "')";
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data posting query to erpdatabase is..... //
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println(navparentqry);
PreparedStatement navst=navconn.prepareStatement(navparentqry);
navst.executeUpdate();
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// i don't know what is wrong with this method, but it throws com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. //
// ResultSet navrs=navst.executeQuery(navparentqry); //
// navst.executeQuery(navparentqry); //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
dimrs=null;
dimst=dimconn.createStatement();
dimrs=dimst.executeQuery(dimchildqry);
while(dimrs.next()){
String navchildqry ="insert into [xxxxxxxx Pvt_ Ltd_$temp sales line] ([Approx_ Cost],[Line No_],[No_],[po no update],[Quantity],[SO No_ Of SCM],[Unit of Measure],[Unit Price],[Variant Code]) values (";
navchildqry = navchildqry + dimrs.getString("Approx_ Cost") +", ";
navchildqry = navchildqry + dimrs.getString("Line No_") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("No_") +"', ";
navchildqry = navchildqry + "'" + dimrs.getString("puchase order no_line") +"', ";
navchildqry = navchildqry + dimrs.getString("Quantity") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("SODetailsId") +"', ";
navchildqry = navchildqry + "'" + dimrs.getString("Unit of Measure") +"', ";
navchildqry = navchildqry + dimrs.getString("Unit Price") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("Variant Code") +"') ";
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data posting query to erp database is..... //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println(navchildqry);
PreparedStatement navst=navconn.prepareStatement(navchildqry);
navst.executeUpdate();
}
navconn.close();
dimconn.close();
//////////////////////////////////////////////////////////////////////////////////////////
// A display message to indicate the end of the posting of data process..... //
//////////////////////////////////////////////////////////////////////////////////////////
out.println("<center><h1>Data Transferred to erp with SO Number : "+id+"</h1></center>");
%>
</body>
</html>
DimensioN is built on top of JBOSS. It has some inbuilt functionality to export data from one database to another, with some rules associated with it. and that is great, But what it lacks is you cannot pick and choose which records you want to transfer. That was a problem.
There is a way to atttain the solution for this problem in DimensioN, but the application architecture which we had decided upon did not permit, or to be correct will make it too complex, for the future DimensioN developer to understand and tackle any problems if they should arise. this would invove the logic as follows,
rModify --> DbImport --> rModify....
And as you can see rModify is a bit of a highlevel feature, and many still dread to use it. so the only thing which i could think off was using some thing else which is out of DimensioN but still could be used with it, like Ant target or something.
My first choice was to use Ant target, coz i have done database manipulation with it for an other project, previously. As i can use it as a template and proceed off, and that would save time too. But, i ran into trouble with it, First i had to make javascript to work from inside Ant and then after i had succeded, when i had tried to do the database manipulation, it was creating a exception saying Illegal Access Exception, some AntClassLoader came in and some bullshit which i was not able to understand. and i had waited for 3 days for OS support, but when i came to know that help might not arrive on time, i wanted to put in something to get things going till the support arrived.
Another two days and that jsp expert had not finished the job yet, and they were pressing me for an answer, as though i was a part of that project. ( i had moved to another one even b4 the problem started). Well then, i had to sit back and write the code again for the jsp. (i hate to code. but this was as usual Reverse Engineering, as i would put it,coz i know the basics to do anything, i just did not have the time to proceed to write things from scratch.) I had set a deadline of 4 hours for me to finish. and i sat down to write the code. it took me 3 hours and a half to do it. and it was pretty much good, without any problems, and is working fine.
I know there are still 2 potential pit falls..... (1) I haven't used a try catch block, so if any exceptions are thrown the page will be a error. which i will have to correct in the future... (2) I haven't used parameterized prepare statement, so if the data is going to have any problems like a single quote, or anything that would hamper a insert statement on syntax, yet again the page will break with an error.
So with these details, i give you the code. it might be helpful to someone.
-----------------------------------------------------------------------
<%@ page import="java.sql.*" %>
<%@ page language="java" session="true" %>
<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import="org.apache.commons.fileupload.disk.*"%>
<%@ page import="org.apache.commons.fileupload.servlet.*"%>
<%@ page import="org.springframework.jdbc.core .*"%>
<%
////////////////////////////////////////////////////////////////////////////////////////////////////////
// ExporttoErp.jsp //
// //
// File Created on September 5th 2008 at 1807 hrs. //
// Last Modified on September 5th 2008 at 2030 hrs. //
// Modified By Anbarasan //
// //
// This page exports data from dimension database to navision database. //
// We get the record identifier from the url. //
// Then we proceed to retrive data from dimension and then push it into navision. //
/////////////////////////////////////////////////////////////////////////////////////////////////////////
%>
<html>
<head>
<title>
DbExport utility to export Single Record of choice to ERP.
</title>
</head>
<body>
<%
String id=request.getParameter("docid");
////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see the primary key id that is being passed. //
///////////////////////////////////////////////////////////////////////////////////////////////
// out.println(id);
////////////////////////////////////////////////////////
// Connection to Dimension's Database //
////////////////////////////////////////////////////////
DataSource dimds = (DataSource)com.orangescape.hce.server.services.CsServiceLocator.getInstance().fetchObject("java:dimension");
Connection dimconn = null;
dimconn = dimds.getConnection();
//////////////////////////////////////////////////
// Connection to Navision's Database //
//////////////////////////////////////////////////
DataSource navds = (DataSource)com.orangescape.hce.server.services.CsServiceLocator.getInstance().fetchObject("java:erp");
Connection navconn = null;
navconn = navds.getConnection();
String dimparentqry="select [Amount],[Bill-to Customer No_],[Chain Name],[Created On Date],[Customer_PO_date],[External Document No_],[Location Code],[Octroi Amount],[Octroi Applicable],[Order Type],[Order Value],[Project Date],[project No_ updation],[Promised Delivery Date],[Req_ delivery Date],[Sales Cordinator Code],[Sales Cordinator Name],[Salesperson Code],[Sell-to Customer No_],[Shortcut Dimension 1 Code],[Shortcut Dimension 12 Code],[DocID] from [xxxxxxxx Pvt_ Ltd_$temp sales header] where docid = '"+id+"'";
String dimchildqry="select [Approx_ Cost],[Line No_],[No_],[puchase order no_line],[Quantity],[SODetailsId],[Unit of Measure],[Unit Price],[Variant Code] from [xxxxxxxxx Pvt_ Ltd_$temp sales line] where SODetailsId='"+id+"'";
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data retrival query from dimension database is..... //
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println("<b>ParentQuery</b><br>"+dimparentqry);
// out.println("<br><br><br>");
// out.println("<b>ChildQuery</b><br>"+dimchildqry);
// out.println("<br><br><br>");
//////////////////////////////////////////////////////////////////////////////////////////////////////////
Statement dimst=dimconn.createStatement();
ResultSet dimrs=dimst.executeQuery(dimparentqry);
while(dimrs.next()){
String navparentqry="insert into [xxxxxxxx Pvt_ Ltd_$temp sales header] ([Amount],[Bill-to Customer No_],[Chain Name],[Created On Date],[Customer_PO_date],[External Document No_],[Location Code],[Octroi Amount],[Octroi Applicable],[Order Type],[Order Value],[Project Date],[Project No_],[Promised Delivery Date],[Req_ delivery Date],[Sales Cordinator Code],[Sales Cordinator Name],[Salesperson Code],[Sell-to Customer No_],[Shortcut Dimension 1 Code],[Shortcut Dimension 12 Code],[SO Document ID],[SO No_ Of SCM]) values (";
navparentqry = navparentqry + dimrs.getString("Amount") + ", ";
navparentqry = navparentqry + "'" + dimrs.getString("Bill-to Customer No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Chain Name") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Created On Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Customer_PO_date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("External Document No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Location Code") + "', ";
navparentqry = navparentqry + dimrs.getString("Octroi Amount") + ", ";
navparentqry = navparentqry + dimrs.getString("Octroi Applicable") + ", ";
navparentqry = navparentqry + "'" + dimrs.getString("Order Type") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Order Value") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Project Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("project No_ updation") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Promised Delivery Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Req_ delivery Date") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sales Cordinator Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sales Cordinator Name") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Salesperson Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Sell-to Customer No_") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Shortcut Dimension 1 Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("Shortcut Dimension 12 Code") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("DocID") + "', ";
navparentqry = navparentqry + "'" + dimrs.getString("DocID") + "')";
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data posting query to erpdatabase is..... //
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println(navparentqry);
PreparedStatement navst=navconn.prepareStatement(navparentqry);
navst.executeUpdate();
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// i don't know what is wrong with this method, but it throws com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. //
// ResultSet navrs=navst.executeQuery(navparentqry); //
// navst.executeQuery(navparentqry); //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
dimrs=null;
dimst=dimconn.createStatement();
dimrs=dimst.executeQuery(dimchildqry);
while(dimrs.next()){
String navchildqry ="insert into [xxxxxxxx Pvt_ Ltd_$temp sales line] ([Approx_ Cost],[Line No_],[No_],[po no update],[Quantity],[SO No_ Of SCM],[Unit of Measure],[Unit Price],[Variant Code]) values (";
navchildqry = navchildqry + dimrs.getString("Approx_ Cost") +", ";
navchildqry = navchildqry + dimrs.getString("Line No_") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("No_") +"', ";
navchildqry = navchildqry + "'" + dimrs.getString("puchase order no_line") +"', ";
navchildqry = navchildqry + dimrs.getString("Quantity") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("SODetailsId") +"', ";
navchildqry = navchildqry + "'" + dimrs.getString("Unit of Measure") +"', ";
navchildqry = navchildqry + dimrs.getString("Unit Price") +", ";
navchildqry = navchildqry + "'" + dimrs.getString("Variant Code") +"') ";
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Enable the following line to see what the data posting query to erp database is..... //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// out.println(navchildqry);
PreparedStatement navst=navconn.prepareStatement(navchildqry);
navst.executeUpdate();
}
navconn.close();
dimconn.close();
//////////////////////////////////////////////////////////////////////////////////////////
// A display message to indicate the end of the posting of data process..... //
//////////////////////////////////////////////////////////////////////////////////////////
out.println("<center><h1>Data Transferred to erp with SO Number : "+id+"</h1></center>");
%>
</body>
</html>
No comments:
Post a Comment