Projects:XSQL Insert Update Review/Technical Documentation
Contents |
XSQL Insert Update Review - Technical Documentation
Overview
This article will explain the process that will be followed to complete XSQL Insert Update Review project.
Current status
Manual windows
At present, Openbravo ERP manual windows, mainly processes, are displaying the following error when they try to insert, update or delete the database and something goes wrong.
If one sees the log file, database is returning a message with much more information that needs to be displayed:
- In Oracle: ORA-01400: no se puede realizar una inserción NULL en "TAD240R2"."C_INVOICE"."PAYMENTRULE"
- In PostgreSQL: @CODE=0@ERROR: null value in column "paymentrule" violates not-null constraint
Generated windows
In generated (WAD) windows, a solution is still running and displays the following message when ones tries to insert a null value in a mandatory (non nullable) column.
Solution
The proposed solution is to follow the same message structure for manual windows and get a message like the following one.
Dependencies
Please notice that the message displayed is in the database language, not in the user's language: bug 0004601. This bug does not stop the execution of the project but should be fixed in order to properly achieve it.
Technical design
At present, generated (WAD) windows use translateError() method of erpCommon/utility/Utility.java file (lines 911 to 994) to parse and display the database message.
/** * Translate the message, searching the @ parameters, and making use of the * ErrorTextParser class to get the appropriated message. * * @param conn: Handler for the database connection. * @param vars: Handler for the session info. * @param strLanguage: Language to translate. * @param message: String with the message to translate. * @return Object with the message. */ public static OBError translateError(ConnectionProvider conn, VariablesSecureApp vars, String strLanguage, String message) { OBError myError = new OBError(); myError.setType("Error"); myError.setMessage(message); if (message!=null && !message.equals("")) { String code = ""; if (log4j.isDebugEnabled()) log4j.debug("translateError - message: " + message); if (message.startsWith("@CODE=@")) message = message.substring(7); else if (message.startsWith("@CODE=")) { message = message.substring(6); int pos = message.indexOf("@"); if (pos==-1) { code = message; message = ""; } else { code = message.substring(0, pos); message = message.substring(pos+1); } } myError.setMessage(message); if (log4j.isDebugEnabled()) log4j.debug("translateError - code: " + code + " - message: " + message); //BEGIN Checking if is a pool problem if (code!=null && code.equals("NoConnectionAvailable")) { myError.setType("Error"); myError.setTitle("Critical Error"); myError.setConnectionAvailable(false); myError.setMessage("No database connection available"); return myError; } //END Checking if is a pool problem //BEGIN Parsing message text if (message!=null && !message.equals("")) { String rdbms = conn.getRDBMS(); ErrorTextParser myParser = null; try { Class<?> c = Class.forName("org.openbravo.erpCommon.utility.ErrorTextParser" + rdbms.toUpperCase()); myParser = (ErrorTextParser) c.newInstance(); } catch (ClassNotFoundException ex) { log4j.warn("Couldn´t find class: org.openbravo.erpCommon.utility.ErrorTextParser" + rdbms.toUpperCase()); myParser = null; } catch (Exception ex1) { log4j.warn("Couldn´t initialize class: org.openbravo.erpCommon.utility.ErrorTextParser" + rdbms.toUpperCase()); myParser = null; } if (myParser!=null) { myParser.setConnection(conn); myParser.setLanguage(strLanguage); myParser.setMessage(message); myParser.setVars(vars); try { OBError myErrorAux = myParser.parse(); if (myErrorAux!=null && !myErrorAux.getMessage().equals("") && (code==null || code.equals("") || code.equals("0") || !myErrorAux.getMessage().equalsIgnoreCase(message))) return myErrorAux; } catch (Exception ex) { log4j.error("Error while parsing text: " + ex); } } } else myError.setMessage(code); //END Parsing message text //BEGIN Looking for error code in AD_Message if (code!=null && !code.equals("")) { FieldProvider fldMessage = locateMessage(conn, code, strLanguage); if (fldMessage!=null) { myError.setType((fldMessage.getField("msgtype").equals("E")?"Error":(fldMessage.getField("msgtype").equals("I")?"Info":(fldMessage.getField("msgtype").equals("S")?"Success":"Warning")))); myError.setMessage(fldMessage.getField("msgtext")); return myError; } } //END Looking for error code in AD_Message } return myError; }
The same translateError() method will be used in manual windows (processes) to catch the message of a method that inserts, updates or deletes records in the database.
// Catch database error message try { AnyProcessData.insertMethod(conn, this, ..., ...); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... try { AnyProcessData.updateMethod(conn, this, ..., ...); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... try { AnyProcessData.deleteMethod(conn, this, ..., ...); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; }
Please notice that, in order to work, the process method, for instance, processButton(), must be of type OBError like OBError processButton(). If it's not the case, change the type of the process to OBError if this does not affect the current functionality.
This way, XSQL Insert Update Review project aims to review the *.java files of all manual windows (processes) that have methods that insert, update or delete records in the database, mainly inside /src/org/openbravo/erpCommon/ad_actionButton/ and src/org/openbravo/erpCommon/ad_process/ folders.
List of process files without reference
There are some process files that are not used within Openbravo ERP, i.e., not used in any menu/window/tab. By the moment, these files will be listed here and will not be part of this project:
- src/org/openbravo/erpCommon/ad_actionButton/CreateCloseFactAcct.java
- src/org/openbravo/erpCommon/ad_actionButton/DropCloseFactAcct.java
- src/org/openbravo/erpCommon/ad_actionButton/RequestAction.java
- src/org/openbravo/erpCommon/ad_forms/EdiFileImport.java
- src/org/openbravo/erpCommon/ad_forms/InvoiceVendorMultiline.java
- src/org/openbravo/erpCommon/ad_forms/InvoiceVendorMultiline_Lines.java
- src/org/openbravo/erpCommon/ad_forms/MatchingPOReceiptInvoice.java
List of process files with non-standard error logic
There are some process files within Openbravo ERP that do not follow the standard error message logic. In order not to refactor all their logic, these files will be listed here and will not be part of this project:
- src/org/openbravo/erpCommon/ad_actionButton/InsertAcces.java
- src/org/openbravo/erpCommon/ad_forms/InitialClientSetup.java
- src/org/openbravo/erpCommon/ad_forms/ShowSessionPreferences.java
- src/org/openbravo/erpCommon/ad_forms/ShowSessionVariables.java
- src/org/openbravo/erpCommon/ad_forms/AcctServer.java
- src/org/openbravo/erpCommon/ad_forms/Role.java
- src/org/openbravo/erpCommon/ad_process/ImportAccount.java
- src/org/openbravo/erpCommon/ad_process/ImportBPartner.java
- src/org/openbravo/erpCommon/ad_process/ImportBudget.java
- src/org/openbravo/erpCommon/ad_process/ImportOrder.java
- src/org/openbravo/erpCommon/ad_process/ImportProcess.java
- src/org/openbravo/erpCommon/ad_process/ImportProduct.java
- src/org/openbravo/erpCommon/ad_process/ImportTax.java
- src/org/openbravo/erpCommon/ad_process/SendMailText.java
- src/org/openbravo/erpCommon/ad_process/Register.java
- src/org/openbravo/erpCommon/ad_process/RequestReOpen.java
Examples
Create Sales Orders from Expenses
Create Sales Orders from Expenses process (Project & Service Management || Transactions || Create Sales Orders from Expenses) has, inside its ExpenseSOrder.java file:
- 3 insert methods:
- ExpenseSOrderData.insertCOrder(conn, this, ..., ...); inside OBError processButton() method.
- ExpenseSOrderData.insertCOrderline(conn, this, ..., ...); inside OBError processButton() method.
- PInstanceProcessData.insertPInstance(this, ..., ...); inside String processOrder() method.
- 1 update method:
- ExpenseSOrderData.updateTimeExpenseLine(conn, this, ..., ...); inside OBError processButton() method.
Once the changes to catch the database messages have been applied, ExpenseSOrder.java file looks like this.
OBError myMessage = null; myMessage = new OBError(); myMessage.setTitle(""); ... // Catch database error message try { ExpenseSOrderData.insertCOrder(conn, this, strCOrderId, data[i].adClientId, data[i].adOrgId, vars.getUser(), strDocumentNo, strDocStatus, strDocAction, strProcessing, docType, docTargetType, strDateOrdered, strDateOrdered, strDateOrdered, data[i].cBpartnerId, ExpenseSOrderData.cBPartnerLocationId(this, data[i].cBpartnerId), ExpenseSOrderData.billto(this, data[i].cBpartnerId).equals("")?ExpenseSOrderData.cBPartnerLocationId(this, data[i].cBpartnerId):ExpenseSOrderData.billto(this, data[i].cBpartnerId), strBPCCurrencyId, data1[0].paymentrule, data1[0].cPaymenttermId.equals("")?SEOrderBPartnerData.selectPaymentTerm(this, data[i].adClientId):data1[0].cPaymenttermId, data1[0].invoicerule.equals("")?"I":data1[0].invoicerule, data1[0].deliveryrule.equals("")?"A":data1[0].deliveryrule, "I", data1[0].deliveryviarule.equals("")?"D":data1[0].deliveryviarule, data[i].mWarehouseId.equals("")?vars.getWarehouse():data[i].mWarehouseId, data[i].mPricelistId, data[i].cProjectId, data[i].cActivityId, data[i].cCampaignId); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseSOrderData.insertCOrderline(conn, this, strCOrderlineID, data[i].adClientId, strOrganization.equals("")?data[i].adOrgId:strOrganization, vars.getUser(), strCOrderId, Integer.toString(line), data[i].cBpartnerId, ExpenseSOrderData.cBPartnerLocationId(this, data[i].cBpartnerId), strDateOrdered, strDateOrdered, data[i].description, data[i].mProductId, data[i].mWarehouseId.equals("")?vars.getWarehouse():data[i].mWarehouseId, data[i].cUomId.equals("")?Utility.getContext(this, vars, "#C_UOM_ID", "ExpenseSOrder"):data[i].cUomId, data[i].qty, strBPCCurrencyId, pricelist, priceactual, pricelimit, strCTaxID, data[i].sResourceassignmentId, strDiscount); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } // Catch database error message try { //Updates expense line with the sales order line ID ExpenseSOrderData.updateTimeExpenseLine(conn, this, strCOrderlineID, data[i].sTimeexpenselineId); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { PInstanceProcessData.insertPInstance(this, pinstance, "104", strCOrderId, "N", vars.getUser(), vars.getClient(), vars.getOrg()); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; }
Create AP Expense Invoices
Create AP Expense Invoices process (Project & Service Management || Transactions || Create AP Expense Invoices) has, inside its ExpenseAPInvoice.java file:
- 3 insert methods:
- ExpenseAPInvoiceData.insert(conn, this, ..., ...); inside OBError processExpense() method.
- ExpenseAPInvoiceData.insertLine(conn, this, ..., ...); inside OBError processExpense() method.
- ExpenseAPInvoiceData.insertInvoicelineAcctdimension(conn, this, ..., ...); inside OBError processExpense() method.
- 3 update methods:
- ExpenseAPInvoiceData.updateInvoiceline(conn, this, ..., ...); inside OBError processExpense() method.
- ExpenseAPInvoiceData.updateAcctdimension(conn, this, ..., ...); inside OBError processExpense() method.
- ExpenseAPInvoiceData.updateExpense(conn, this, ..., ...); inside OBError processExpense() method.
Once the changes to catch the database messages have been applied, ExpenseAPInvoice.java file looks like this.
OBError myMessage = null; myMessage = new OBError(); myMessage.setTitle(""); ... // Catch database error message try { ExpenseAPInvoiceData.insert(conn, this, strcInvoiceId, "N", "", "N", "N", "N", "N", "N", data[i].adClientId, data[i].adOrgId, "", "", strDocumentno, "", "", "Y", docTargetType, strDateInvoiced, strDateInvoiced, data[i].cBpartnerId, strcBpartnerLocationId, "", strPricelistId, strBPCCurrencyId, strSalesrepId, "N", "", "", strPaymentRule, strPaymentterm, "N", "N", data[i].cProjectId, data[i].cActivityId, data[i].cCampaignId, vars.getOrg(), "", "", "0", "0", "DR", strDocType, "N", "CO", "N", vars.getUser(), vars.getUser()); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseAPInvoiceData.insertLine(conn, this, data[i].adClientId, data[i].adOrgId, strcInvoiceId, String.valueOf(qty), String.valueOf(line), "", data[i].mProductId, "", data[i].description, "", strmProductUomId, String.valueOf(qty), data[i].cUomId, strPricestd, strPricelist, strcTaxID, String.valueOf(Float.parseFloat(strPricestd)*qty), "", strPricestd, strPricelimit, "", "", "", "Y", "0", "", "", strcInvoiceLineId, "N", vars.getUser(), vars.getUser()); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseAPInvoiceData.updateInvoiceline(conn, this, String.valueOf(qty), String.valueOf(Float.parseFloat(strPricestd)*qty), strcInvoiceLineId); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseAPInvoiceData.insertInvoicelineAcctdimension(conn, this, strcInvoicelineAcctdimension, data[i].adClientId, data[i].adOrgId, "Y", vars.getUser(), vars.getUser(), strcInvoiceLineId, String.valueOf(qty*Float.parseFloat(strPricestd)), data[i].cProjectId, data[i].cCampaignId, "", ""); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseAPInvoiceData.updateAcctdimension(conn, this, String.valueOf(amount), dataAcctdimension[0].cInvoicelineAcctdimensionId); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; } ... // Catch database error message try { ExpenseAPInvoiceData.updateExpense(conn, this, strcInvoiceLineId, data[i].sTimeexpenselineId); } catch(ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); return myMessage; }