Refeching MySql Autoincreamnt value in UI

General questions regarding the development with JVx.

Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Fri Jun 11, 2010 7:03 pm

Dear Sir,

First of all I wish to congratulate you for developing such a nice framework and distributed it as open source.

I hava two tables. one master and one for detail - order and lineitems. lineitems is connected with a forgein key column fk_ord_id to ID column in order table. ID column is an Autoincreament field. In the user interface linitems are linked to master through fk_ord_id and Id column. Now whenever I insert a new order the newly auto generated ID in order table is not refreshed in master UI so detail records can not be inserted into the lineitems table until the master table is saved and reloaded. So please help me how do I get the newly generated ID refeched into UI just after insert.

With best regard
Dipankar
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Fri Jun 11, 2010 10:02 pm

Thanks for the praise!

We need some information to find out where the problem is:

Which database an which version?
Which JVx version?
Is it possible to post your create table statements?

Normally the DBAccess refetches the PK columns. Maybe there is a problem with PK detection...

A manual workaround is to use an after inserted event, but thats only a temporary solution and not our style to solve problems :)
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sat Jun 12, 2010 5:43 am

Than you for so early reply,

The create table statement is as follows:

CREATE TABLE `hdr` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`COMP_CODE` INT(10) NOT NULL DEFAULT '0',
`ACCT_YEAR` SMALLINT(5) NOT NULL DEFAULT '0',
`TRN_TYPE` VARCHAR(25) NOT NULL DEFAULT '',
`DOC_NO` VARCHAR(25) NOT NULL DEFAULT '',
`TRN_DT` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`PARTY` INT(10) NOT NULL DEFAULT '0',
`DOCTOR` INT(10) NULL DEFAULT NULL,
`TOT_TAX` DOUBLE(15,5) NULL DEFAULT NULL,
`AMT_WO_TAX` DOUBLE(15,5) NULL DEFAULT NULL,
`AMOUNT` DOUBLE(15,5) NULL DEFAULT NULL,
`DISC` DOUBLE(15,5) NULL DEFAULT NULL,
`SP_DISC` DOUBLE(15,5) NULL DEFAULT NULL,
`ADJUSTMENT` DOUBLE(15,5) NULL DEFAULT NULL,
`C_NOTE` DOUBLE(15,5) NULL DEFAULT NULL,
`NET_AMOUNT` DOUBLE(15,5) NULL DEFAULT NULL,
`AMOUNT_PAID` DOUBLE(15,5) NULL DEFAULT NULL,
`AMOUN_DUE` DOUBLE(15,5) NULL DEFAULT NULL,
`NARRATION` LONGTEXT NULL,
`PAYMENT_DISC` DOUBLE(15,5) NULL DEFAULT NULL,
`P_DOC_NO` INT(10) NULL DEFAULT NULL,
`P_DT` DATETIME NULL DEFAULT NULL,
`STOCK` TINYINT(1) NULL DEFAULT NULL,
`TAXONFQTY` DOUBLE(15,5) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1234608


CREATE TABLE `inventry` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`COMP_CODE` INT(10) NULL DEFAULT NULL,
`ACCT_YEAR` SMALLINT(5) NULL DEFAULT NULL,
`TRN_TYPE` VARCHAR(10) NULL DEFAULT NULL,
`DOC_NO` VARCHAR(15) NULL DEFAULT NULL,
`TRN_DT` DATETIME NULL DEFAULT NULL,
`PARTY` INT(10) NULL DEFAULT NULL,
`ITEM_CODE` INT(10) NULL DEFAULT NULL,
`BATCH` VARCHAR(10) NULL DEFAULT NULL,
`EXP` INT(10) NULL DEFAULT NULL,
`QTY_S` INT(10) NULL DEFAULT NULL,
`QTY_P` INT(10) NULL DEFAULT NULL,
`PCS_PER_PACK` SMALLINT(5) NULL DEFAULT NULL,
`PCS_PAID_FOR` SMALLINT(5) NULL DEFAULT NULL,
`RATE` DOUBLE(15,5) NULL DEFAULT NULL,
`MRP` DOUBLE(15,5) NULL DEFAULT NULL,
`PTR` DOUBLE(15,5) NULL DEFAULT NULL,
`PTRPERPCS` DOUBLE(15,5) NULL DEFAULT NULL,
`TAX` DOUBLE(7,2) NULL DEFAULT NULL,
`TAX_AMT` DOUBLE(15,5) NULL DEFAULT NULL,
`ED` DOUBLE(15,5) NULL DEFAULT NULL,
`DISC` DOUBLE(15,5) NULL DEFAULT NULL,
`AMT_WO_TAX` DOUBLE(15,5) NULL DEFAULT NULL,
`AMT` DOUBLE(15,5) NULL DEFAULT NULL,
`FREE_AMT` DOUBLE(15,5) NULL DEFAULT NULL,
`FREE_QTY` DOUBLE(15,5) NULL DEFAULT NULL,
`MULT` SMALLINT(5) NULL DEFAULT NULL,
`MRP_PER_PCS` DOUBLE(15,5) NULL DEFAULT NULL,
`Schd` VARCHAR(3) NULL DEFAULT NULL,
`p_date` DATETIME NULL DEFAULT NULL,
`p_time` DATETIME NULL DEFAULT NULL,
`sale_q` INT(10) NULL DEFAULT NULL,
`profit_per` DOUBLE(15,5) NULL DEFAULT NULL,
`stock` TINYINT(1) NULL DEFAULT NULL,
`WBST` DOUBLE(7,2) NULL DEFAULT NULL,
`HDR_ID` INT(10) NULL DEFAULT NULL,
PRIMARY KEY (`ID`),
INDEX `FK_inventry_items` (`ITEM_CODE`),
INDEX `HDR_ID` (`HDR_ID`),
CONSTRAINT `FK_inventry_items` FOREIGN KEY (`ITEM_CODE`) REFERENCES `items` (`ID`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=194141


the hdr table is the master table and inventry table is the detail table which are connected through 1:m realation through hdr.id ->inventry.hdr_id

MySql Server version 5.1.25 rc-community
MySql Client version 5.1.11
jdbc driver: mysql-connector-java-5.1.6-bin.jar

tried with jvx 0.7, 0.8(beta 1) and 0.8(beta 2)

source code:

/*
* Copyright 2009 SIB Visions GmbH
*
* Licensed under the Apache License, Version 2.0 (the "License"); you may not
* use this file except in compliance with the License. You may obtain a copy of
* the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
* License for the specific language governing permissions and limitations under
* the License.
*
*
* History
*
* 27.11.2009 - [JR] - creation
* 22.02.2010 - [JR] - changed column names for auto storages
*/
package apps.firstapp.frames;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.rad.genui.UIDimension;
import javax.rad.genui.UIInsets;
import javax.rad.genui.celleditor.UIDateCellEditor;
import javax.rad.genui.celleditor.UIImageViewer;
import javax.rad.genui.celleditor.UILinkedCellEditor;
import javax.rad.genui.celleditor.UINumberCellEditor;
import javax.rad.genui.component.UIButton;
import javax.rad.genui.component.UILabel;
import javax.rad.genui.container.UIGroupPanel;
import javax.rad.genui.container.UIInternalFrame;
import javax.rad.genui.container.UIPanel;
import javax.rad.genui.container.UISplitPanel;
import javax.rad.genui.control.UIEditor;
import javax.rad.genui.control.UITable;
import javax.rad.genui.layout.UIBorderLayout;
import javax.rad.genui.layout.UIFormLayout;
import javax.rad.io.IFileHandle;
import javax.rad.model.ColumnDefinition;
import javax.rad.model.IChangeableDataRow;
import javax.rad.model.IDataBook;
import javax.rad.model.ModelException;
import javax.rad.model.RowDefinition;
import javax.rad.model.SortDefinition;
import javax.rad.model.IDataBook.SelectionMode;
import javax.rad.model.IDataBook.WriteBackIsolationLevel;
import javax.rad.model.condition.ICondition;
import javax.rad.model.condition.LikeIgnoreCase;
import javax.rad.model.datatype.StringDataType;
import javax.rad.model.event.DataRowEvent;
import javax.rad.model.reference.ReferenceDefinition;
import javax.rad.remote.AbstractConnection;
import javax.rad.remote.MasterConnection;
import javax.rad.ui.IDimension;

import apps.firstapp.FirstApplication;
import apps.firstapp.frames.NavigationTable;

import com.sibvisions.rad.application.Application;
import com.sibvisions.rad.model.mem.DataRow;
import com.sibvisions.rad.model.remote.RemoteDataBook;
import com.sibvisions.rad.model.remote.RemoteDataSource;
import com.sibvisions.rad.persist.jdbc.DBStorage;
import com.sibvisions.util.type.FileUtil;
import com.sibvisions.util.type.ImageUtil;
import com.sun.org.apache.xpath.internal.operations.Equals;

/**
* The <code>ContactsAutoFrame</code> shows contacts, their detail information
* and all educations. The automatic link celleditors will be detected
* automatically. It shows how less code a developer has to write. You can
* compare the LoC with the {@link ContactsFrame} which handles automatic link
* celleditors definition manually.
*
* @author Martin Handsteiner
*/
public class SaleBillFrame extends UIInternalFrame implements IDataScreen {
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Class members
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/** the default image when no image was found. */
private static final String NO_IMAGE = "/com/sibvisions/apps/showcase/images/nobody.gif";

/** the DataSource for fetching table data. */
private RemoteDataSource dataSource = new RemoteDataSource();

/** storage for contacts. */
private RemoteDataBook rdbHdr = new RemoteDataBook();
/** storage for contacts educations. */
private RemoteDataBook rdbInventry = new RemoteDataBook();
/** storage for contacts. */
private RemoteDataBook rdbParty = new RemoteDataBook();
private RemoteDataBook rdbDoctors = new RemoteDataBook();
private RemoteDataBook rdbStock = new RemoteDataBook();
/** search row. */
private DataRow drSearch = null;

/** the frames layout. */
private UIBorderLayout blThis = new UIBorderLayout();
/** the split between contacts and details. */
private UISplitPanel splitMain = new UISplitPanel();
/** the navigator for contacts. */
private NavigationTable navContacts;
/** the navigagor for showing educations. */
private NavigationTable navContEdu;

/** the layout for details. */
private UIFormLayout flDetails = new UIFormLayout();
/** the details. */
private UIPanel panDetails = new UIPanel();
/** the details. */
private UIGroupPanel gpanDedails = new UIGroupPanel();
/** the details. */
private UIGroupPanel gpanEducations = new UIGroupPanel();

/** contacts layout. */
private UIBorderLayout blContacts = new UIBorderLayout();
/** contacts panel. */
private UIPanel panContacts = new UIPanel();
/** search panel. */
private UIPanel panSearch = new UIPanel();

/** Label. */
private UILabel lblDOC_NO = new UILabel();
/** Label. */
private UILabel lblTRN_DT = new UILabel();
/** Label. */
private UILabel lblPARTY = new UILabel();
/** Label. */
private UILabel lblDOCTOR = new UILabel();
/** Label. */
private UILabel lblTOT_TAX = new UILabel();
/** Label. */
private UILabel lblAMT_WO_TAX = new UILabel();
/** Label. */
private UILabel lblAMOUNT = new UILabel();
/** Label. */
private UILabel lblDISC = new UILabel();
/** Label. */
private UILabel lblSP_DISC = new UILabel();
/** Label. */
private UILabel lblADJUSTMENT = new UILabel();
/** Label. */
private UILabel lblNET_AMOUNT = new UILabel();
/** Label. */
private UILabel lblAMOUNT_PAID = new UILabel();
/** Label. */
private UILabel lblFilename = new UILabel();
/** labelSuchen. */
private UILabel lblSearch = new UILabel();

/** Editor. */
private UIEditor edtDOC_NO = new UIEditor();
/** Editor. */
private UIEditor edtTRN_DT = new UIEditor();
/** Editor. */
private UIEditor edtPARTY = new UIEditor();
/** Editor. */
private UIEditor edtDOCTOR = new UIEditor();
/** Editor. */
private UIEditor edtTOT_TAX = new UIEditor();
/** Editor. */
private UIEditor editAMT_WO_TAX = new UIEditor();
/** Editor. */
private UIEditor edtAMOUNT = new UIEditor();
/** Editor. */
private UIEditor edtDISC = new UIEditor();
/** Editor. */
private UIEditor edtSP_DISC = new UIEditor();
/** Editor. */
private UIEditor edtADJUSTMENT = new UIEditor();
/** Editor. */
private UIEditor edtNET_AMOUNT = new UIEditor();
/** Editor. */
private UIEditor edtAMOUNT_PAID = new UIEditor();
/** Editor. */
private UIEditor edtFilename = new UIEditor();
/** editSuchen. */
private UIEditor edtSearch = new UIEditor();
private UIEditor edtHdrId = new UIEditor();
/** contact image. */
private UIEditor icoImage = new UIEditor();
/** load image button. */
private UIButton butLoadImage = new UIButton();
private FirstApplication application;
private AbstractConnection connection;

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Initialization
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Constructs a new instance of <code>ContactsFrame</code>.
*
* @param pApplication
* the application.
* @throws Throwable
* if the initialization throws an error
*/
public SaleBillFrame(FirstApplication pApp) throws Throwable {
super(pApp.getDesktopPane());

application = pApp;
initializeModel();
initializeUI();
}

/**
* Initializes the model.
*
* @throws Throwable
* if the initialization throws an error
*/
private void initializeModel() throws Throwable {
connection = ((MasterConnection) application.getConnection())
.createSubConnection("apps.firstapp.frames.DBEdit");
connection.open();
dataSource.setConnection(connection);
dataSource.open();

rdbHdr.setDataSource(dataSource);

rdbHdr.setName("hdr");
rdbHdr.open();
ICondition filter = new LikeIgnoreCase("TRN_TYPE", "SALE");
rdbHdr.setFilter(filter);
rdbHdr.setSort(new SortDefinition(false,"ID"));
String[] hs = { "ID","TRN_DT", "DOC_NO", "PARTY_DESC", "NET_AMOUNT" };
rdbHdr.getRowDefinition().setTableColumnNames(hs);
// set same labels as in details panel
// rdbHdr.getRowDefinition().getColumnDefinition("ACTI_ACADEMIC_TITLE").setLabel("Academic
// title");
// rdbHdr.getRowDefinition().getColumnDefinition("PARTY").setLabel("First
// name");
// rdbHdr.getRowDefinition().getColumnDefinition("DOCTOR").setLabel("Last
// name");
// rdbHdr.getRowDefinition().getColumnDefinition("AMOUNT").setLabel("AMOUNT");
// rdbHdr.getRowDefinition().getColumnDefinition("ADJUSTMENT").setLabel("DoB");
// rdbHdr.getRowDefinition().getColumnDefinition("SOCIALSECAMT_WO_TAX").setLabel("Social
// security AMT_WO_TAX");
// rdbHdr.getRowDefinition().getColumnDefinition("HEIN_HEALTH_INSURANCE").setLabel("Health
// insurance");

rdbInventry.setDataSource(dataSource);
rdbInventry.setName("inventry");
// rdbInventry.setMasterReference(new ReferenceDefinition(new String[] {
// "COMP_CODE","ACCT_YEAR","TRN_TYPE","DOC_NO","TRN_DT","PARTY"}, rdbHdr, new String[] { "COMP_CODE","ACCT_YEAR","TRN_TYPE","DOC_NO","TRN_DT","PARTY"}));
rdbInventry.setMasterReference(new ReferenceDefinition(new String[] {
"HDR_ID"}, rdbHdr, new String[] { "ID"}));

rdbInventry.open();
String[] s = { "DESCRIPTION", "QTY_S",
"MRP_PER_PCS","BATCH", "EXP", "TAX", "DISC", "AMT" };
rdbInventry.getRowDefinition().setTableColumnNames(s);

rdbParty.setDataSource(dataSource);
rdbParty.setName("parties");
ICondition Pfilter = new LikeIgnoreCase("TYPE", "CUSTOMER");
rdbParty.setFilter(Pfilter);
rdbParty.setSort(new SortDefinition("PARTY_DESC"));
rdbParty.open();
String[] ps = { "PARTY_DESC", "ADDRESS" };
rdbParty.getRowDefinition().setTableColumnNames(ps);

rdbDoctors.setDataSource(dataSource);
rdbDoctors.setName("doctors");
rdbDoctors.setSort(new SortDefinition("DOCT_NAME"));
rdbDoctors.open();
String[] ds = { "DOCT_NAME" };
rdbDoctors.getRowDefinition().setTableColumnNames(ds);

rdbStock.setDataSource(dataSource);
rdbStock.setName("stock");
// rdbStock.setSort(new SortDefinition("DESCRIPTION"));
rdbStock.open();
String[] ss = {"DESCRIPTION","BATCH","EXP_DT","MRP_PER_PCS","STOCK"};
rdbStock.getRowDefinition().setTableColumnNames(ss);
// UIImageViewer imageViewer = new UIImageViewer();
// imageViewer.setDefaultImageName(NO_IMAGE);
rdbHdr.setSelectionMode(SelectionMode.CURRENT_ROW);
// rdbHdr.getRowDefinition().getColumnDefinition("FILENAME").setReadOnly(true);
// rdbHdr.getRowDefinition().getColumnDefinition("IMAGE").getDataType().setCellEditor(imageViewer);

rdbHdr.getRowDefinition().getColumnDefinition("TRN_DT").getDataType()
.setCellEditor(new UIDateCellEditor("dd.MM.yyyy"));
rdbHdr.getRowDefinition().getColumnDefinition("TOT_TAX").getDataType()
.setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("AMT_WO_TAX")
.getDataType().setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("AMOUNT").getDataType()
.setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("DISC").getDataType()
.setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("SP_DISC").getDataType()
.setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("ADJUSTMENT")
.getDataType().setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("NET_AMOUNT")
.getDataType().setCellEditor(new UINumberCellEditor(".00"));
rdbHdr.getRowDefinition().getColumnDefinition("AMOUNT_PAID")
.getDataType().setCellEditor(new UINumberCellEditor(".00"));


rdbHdr.eventAfterInserting().addListener(this,"doAfterInserting_Hdr");
rdbHdr.eventAfterInserted().addListener(this,"doAfterInserted_Hdr");
installLinkedCellEditor(rdbHdr, new String[] { "PARTY", "PARTY_DESC" },
rdbParty, new String[] { "PARTY", "PARTY_DESC" });

installLinkedCellEditor(rdbHdr, new String[] { "DOCTOR", "DOCT_NAME" },
rdbDoctors, new String[] { "ID", "DOCT_NAME" });

installLinkedCellEditor(rdbInventry, new String[] { "ITEM_CODE",
"DESCRIPTION", "BATCH", "EXP", "MRP_PER_PCS" }, rdbStock,
new String[] { "ITEM_CODE", "DESCRIPTION", "BATCH", "EXP_DT",
"MRP_PER_PCS" });



rdbInventry.eventAfterUpdating().addListener(this,
"doAfterUpdating_Inventry");
rdbInventry.eventAfterUpdated().addListener(this,
"doAfterUpdate_Inventry");
rdbInventry.setSelectionMode(SelectionMode.CURRENT_ROW);
// rdbInventry
// .setWritebackIsolationLevel(WriteBackIsolationLevel.DATA_ROW);
rdbInventry.eventAfterInserting().addListener(this,
"doAfterInserting_Inventry");
rdbInventry.eventBeforeInserted().addListener(this,
"doBeforeInserted_Inventry");
rdbInventry.eventBeforeInserting().addListener(this,
"doBeforeInserting_Inventry");
rdbInventry.eventValuesChanged().addListener(this,
"doValueChanged_Inventry");

rdbInventry.eventAfterDeleting().addListener(this,
"doAfterDeleting_Inventry");
rdbInventry.eventAfterDeleted().addListener(this,
"doAfterDeleted_Inventry");
rdbInventry.eventBeforeDeleted().addListener(this,
"doBeforeDeleted_Inventry");
rdbInventry.eventBeforeDeleting().addListener(this,
"doBeforeDeleting_Inventry");
rdbInventry.getRowDefinition().getColumnDefinition("BATCH").setReadOnly(true);


RowDefinition definition = new RowDefinition();
definition.addColumnDefinition(new ColumnDefinition("SEARCH",
new StringDataType()));

drSearch = new DataRow(definition);
drSearch.eventValuesChanged().addListener(this, "doFilter");
}

/**
* Initializes the UI.
*
* @throws Throwable
* if the initialization throws an error
*/
private void initializeUI() throws Throwable {
lblSearch.setText("Search");
edtSearch.setDataRow(drSearch);
edtSearch.setColumnName("SEARCH");

UIFormLayout layoutSearch = new UIFormLayout();

panSearch.setLayout(layoutSearch);
panSearch.add(lblSearch, layoutSearch.getConstraints(0, 0));
panSearch.add(edtSearch, layoutSearch.getConstraints(1, 0, -1, 0));

navContacts = new NavigationTable(application.getLauncher(),
connection, rdbHdr);
navContacts.getTable().setAutoResize(false);

panContacts.setLayout(blContacts);
panContacts.add(panSearch, UIBorderLayout.NORTH);
panContacts.add(navContacts, UIBorderLayout.CENTER);

navContEdu = new NavigationTable(application.getLauncher(), connection,
rdbInventry);
navContEdu.getTable().setPreferredSize(new UIDimension(150, 150));
navContEdu.eventNewDetail().addListener(this, "doNewEducations");

// icoImage.setPreferredSize(new UIDimension(75, 75));
// icoImage.setDataRow(rdbHdr);
// icoImage.setColumnName("IMAGE");

lblDOC_NO.setText("Bill No");
lblTRN_DT.setText("Bill Date");
lblPARTY.setText("Customer");
lblDOCTOR.setText("Doctor");
lblTOT_TAX.setText("Tax");
lblAMT_WO_TAX.setText("Amt w/o Tax");
lblAMOUNT.setText("Amount");
lblDISC.setText("Discount");
lblSP_DISC.setText("SP_DISC");
lblADJUSTMENT.setText("Adjustment");
lblNET_AMOUNT.setText("Net Amt");
lblAMOUNT_PAID.setText("Paid Amt");
// lblFilename.setText("Filename");

edtDOC_NO.setDataRow(rdbHdr);
edtDOC_NO.setColumnName("DOC_NO");
edtDOC_NO.setPreferredSize(new UIDimension(75, 21));
edtTRN_DT.setDataRow(rdbHdr);
edtTRN_DT.setColumnName("TRN_DT");
edtTRN_DT.setPreferredSize(new UIDimension(100, 21));
edtPARTY.setDataRow(rdbHdr);
edtPARTY.setColumnName("PARTY_DESC");
edtPARTY.setPreferredSize(new UIDimension(200, 21));
edtDOCTOR.setDataRow(rdbHdr);
edtDOCTOR.setColumnName("DOCT_NAME");
edtDOCTOR.setPreferredSize(new UIDimension(200, 21));
edtTOT_TAX.setDataRow(rdbHdr);
edtTOT_TAX.setColumnName("TOT_TAX");
edtTOT_TAX.setPreferredSize(new UIDimension(75, 21));
editAMT_WO_TAX.setDataRow(rdbHdr);
editAMT_WO_TAX.setColumnName("AMT_WO_TAX");
editAMT_WO_TAX.setPreferredSize(new UIDimension(75, 21));
edtAMOUNT.setDataRow(rdbHdr);
edtAMOUNT.setColumnName("AMOUNT");
edtAMOUNT.setPreferredSize(new UIDimension(75, 21));
edtDISC.setDataRow(rdbHdr);
edtDISC.setColumnName("DISC");
edtDISC.setPreferredSize(new UIDimension(75, 21));
edtSP_DISC.setDataRow(rdbHdr);
edtSP_DISC.setColumnName("SP_DISC");
edtSP_DISC.setPreferredSize(new UIDimension(75, 21));
edtADJUSTMENT.setDataRow(rdbHdr);
edtADJUSTMENT.setColumnName("ADJUSTMENT");
edtADJUSTMENT.setPreferredSize(new UIDimension(75, 21));
edtNET_AMOUNT.setDataRow(rdbHdr);
edtNET_AMOUNT.setColumnName("AMT_WO_TAX");
edtNET_AMOUNT.setPreferredSize(new UIDimension(75, 21));
edtAMOUNT_PAID.setDataRow(rdbHdr);
edtAMOUNT_PAID.setColumnName("AMOUNT_PAID");
edtAMOUNT_PAID.setPreferredSize(new UIDimension(75, 21));
edtHdrId.setDataRow(rdbHdr);
edtHdrId.setColumnName("ID");
edtHdrId.setPreferredSize(new UIDimension(75, 21));
// edtFilename.setDataRow(rdbHdr);
// edtFilename.setColumnName("FILENAME");

// butLoadImage.setText("Upload");
// butLoadImage.eventAction().addListener(this, "doUpload");
// butLoadImage.setFocusable(false);

flDetails.setMargins(new UIInsets(20, 20, 20, 20));

gpanDedails.setText("Sale Bill");

gpanDedails.setLayout(flDetails);
//gpanDedails.add(icoImage, flDetails.getConstraints(0, 0, 1, 7));
//gpanDedails.add(butLoadImage, flDetails.getConstraints(0, 8));
//gpanDedails.add(edtFilename, flDetails.getConstraints(1, 8));
//col,row
flDetails.setHorizontalGap(15);
gpanDedails.add(lblDOC_NO, flDetails.getConstraints(0, 0));
gpanDedails.add(edtDOC_NO, flDetails.getConstraints(1, 0));
gpanDedails.add(lblTRN_DT, flDetails.getConstraints(0, 1));
gpanDedails.add(edtTRN_DT, flDetails.getConstraints(1, 1));
gpanDedails.add(lblPARTY, flDetails.getConstraints(3, 0));
gpanDedails.add(edtPARTY, flDetails.getConstraints(4, 0 ));
// gpanDedails.add(lblPARTY, flDetails.getConstraints(3, 0));
gpanDedails.add(edtHdrId, flDetails.getConstraints(5, 0 ));
gpanDedails.add(lblDOCTOR, flDetails.getConstraints(3, 1));
gpanDedails.add(edtDOCTOR, flDetails.getConstraints(4, 1));

gpanDedails.add(lblNET_AMOUNT, flDetails.getConstraints(2, 4));
gpanDedails.add(edtNET_AMOUNT, flDetails.getConstraints(3, 4));
gpanDedails.add(lblADJUSTMENT, flDetails.getConstraints(4, 4));
gpanDedails.add(edtADJUSTMENT, flDetails.getConstraints(5, 4));

gpanDedails.add(lblAMOUNT_PAID, flDetails.getConstraints(2, 5));
gpanDedails.add(edtAMOUNT_PAID, flDetails.getConstraints(3, 5));

gpanDedails.add(lblTOT_TAX, flDetails.getConstraints(2, 6));
gpanDedails.add(edtTOT_TAX, flDetails.getConstraints(3, 6));
gpanDedails.add(lblAMT_WO_TAX, flDetails.getConstraints(-2, 6));
gpanDedails.add(editAMT_WO_TAX, flDetails.getConstraints(-1, 6));
gpanDedails.add(lblAMOUNT, flDetails.getConstraints(2, 7));
gpanDedails.add(edtAMOUNT, flDetails.getConstraints(3, 7));
gpanDedails.add(lblDISC, flDetails.getConstraints(4, 7));
gpanDedails.add(edtDISC, flDetails.getConstraints(5, 7));

UIFormLayout layoutSchulung = new UIFormLayout();

gpanEducations.setText("Bill Detail");
gpanEducations.setLayout(layoutSchulung);
gpanEducations.add(navContEdu, layoutSchulung.getConstraints(0, 0, -1,
-1));

UIFormLayout layout = new UIFormLayout();

panDetails.setLayout(layout);
panDetails.add(gpanDedails, layout.getConstraints(0, 0, -1, 0));
panDetails.add(gpanEducations, layout.getConstraints(0, 1, -1, -1));

splitMain.setDividerPosition(250);
splitMain.setDividerAlignment(UISplitPanel.DIVIDER_TOP_LEFT);
splitMain.setFirstComponent(panContacts);
splitMain.setSecondComponent(panDetails);

setTitle("Sale Bill");
setLayout(blThis);
add(splitMain, UIBorderLayout.CENTER);
}

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Interface implementation
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* {@inheritDoc}
*/
public void save() throws ModelException {
dataSource.saveAllDataBooks();
}

/**
* {@inheritDoc}
*/
public void reload() throws ModelException {
dataSource.reloadAllDataBooks();
}

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// User-defined methods
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Saves the image to the contact.
*
* @param pFileHandle
* the file.
* @throws Throwable
* if an error occures.
*/
public void storeFile(IFileHandle pFileHandle) throws Throwable {
String sFormat = FileUtil.getExtension(pFileHandle.getFileName()
.toLowerCase());

if ("png".equals(sFormat) || "jpg".equals(sFormat)
|| "gif".equals(sFormat)) {
ByteArrayOutputStream stream = new ByteArrayOutputStream();

ImageUtil.createScaledImage(pFileHandle.getInputStream(), icoImage
.getSize().getWidth(), icoImage.getSize().getHeight(),
true, stream, sFormat);

stream.close();

rdbHdr.setValue("FILENAME", pFileHandle.getFileName());
rdbHdr.setValue("IMAGE", stream.toByteArray());

try {
rdbHdr.saveSelectedRow();
} catch (Exception pException) {
// Silent Save of current row.
}
} else {
throw new IOException("Image format '" + sFormat
+ "' not supported. Use 'png', 'jpg' or 'gif'!");
}

}

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Actions
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Starts the image upload.
*
* @throws Throwable
* if an error occures.
*/
public void doUpload() throws Throwable {
if (rdbHdr.getSelectedRow() >= 0) {
application.getLauncher().getFileHandle(this, "storeFile");
}
}

/**
* Opens the educations frame.
*
* @throws Throwable
* if the educations frame can not be opened
*/
public void doNewEducations() throws Throwable {
application.openFrame(EducationsFrame.class);
}

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Interface implementation
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Searches the contacts with the search text.
*
* @throws ModelException
* if the search fails
*/
public void doFilter() throws ModelException {
String suche = (String) drSearch.getValue("SEARCH");
if (suche == null) {
rdbHdr.setFilter(null);
} else {
ICondition filter = new LikeIgnoreCase("PARTY_DESC", suche + "*");
rdbHdr.setFilter(filter);
}
}

private void installLinkedCellEditor(RemoteDataBook pDataBook,
String[] pColumnNames, RemoteDataBook pReferencedDataBook,
String[] pReferenceColumnNames) throws Throwable {
ReferenceDefinition referenceDefinition = new ReferenceDefinition();
referenceDefinition.setReferencedDataBook(pReferencedDataBook);
referenceDefinition.setReferencedColumnNames(pReferenceColumnNames);
referenceDefinition.setColumnNames(pColumnNames);

UILinkedCellEditor linkedCellEditor = new UILinkedCellEditor();
linkedCellEditor.setLinkReference(referenceDefinition);
linkedCellEditor.setTableHeaderVisible(true);
linkedCellEditor.setTableReadonly(true);
linkedCellEditor.setValidationEnabled(true);
linkedCellEditor.setPopupSize(new UIDimension(700,150));
pDataBook.getRowDefinition().getColumnDefinition(pColumnNames[1])
.getDataType().setCellEditor(linkedCellEditor);
}

public void doAfterUpdate_Inventry() throws ModelException {
// doCalc_Inventry();
}

public void doAfterUpdating_Inventry() throws ModelException {
// doCalc_Inventry();
}

public void doValueChanged_Inventry() throws ModelException {
doCalc_Inventry();
// rdbInventry.saveSelectedRow();

}

public void doCalc_Inventry() throws ModelException {

try {
// System.out.print(rdbInventry.getValue("MRP_PER_PCS"));
if (rdbInventry.getValue("QTY_S").equals(null))
return;
if (rdbInventry.getValue("MRP_PER_PCS").equals(null))
return;
Double t = Double.valueOf(rdbInventry.getValue("MRP_PER_PCS")
.toString())
* Double.valueOf(rdbInventry.getValue("QTY_S").toString());
rdbInventry.setValue("AMT", t);
doCalc_Hdr();
} catch (Exception e) {
// TODO Auto-generated catch block
// e.printStackTrace();
}
}

public void doCalc_Hdr() throws ModelException {
/*
Dim rs As Recordset
Dim s_t, TAX, wo_amt As Double
s_t = TAX = wo_amt = 0
Set rs = Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
's_t = s_t + rs!AMT * rs!mult * (-1)
wo_amt = wo_amt + IIf(IsNull(rs!AMT_WO_TAX), 0, rs!AMT_WO_TAX) * rs!mult * (-1)
TAX = TAX + IIf(IsNull(rs!TAX_AMT), 0, rs!TAX_AMT) * rs!mult * (-1)
rs.MoveNext
Loop
rs.Close
' Me.Parent.AMOUNT = s_t
Me.Parent.TOT_TAX = TAX
Me.Parent.AMT_WO_TAX = wo_amt
Me.Parent.AMOUNT = Roundoff5Paise(wo_amt + TAX)
Me.Parent.NET_AMOUNT = Roundoff5Paise(Me.Parent.AMOUNT - Me.Parent.SPL_DISC + Me.Parent.ADJUSTMENT)
If Me.Parent.AMOUNT_DUE = 0 Then
Me.Parent.AMOUNT_PAID = Me.Parent.NET_AMOUNT
End If
Me.Parent.AMOUNT_DUE = Me.Parent.NET_AMOUNT - Me.Parent.AMOUNT_PAID
*/




try {
Double SubTot=0.00;
Double Tax = 0.00;
Double LineTotWOTax = 0.00;

for (int i = 0; i < rdbInventry.getRowCount(); i++) {

SubTot = SubTot
+ Double.valueOf(rdbInventry.getDataRow(i).getValue(
"AMT_WO_TAX").toString());

Tax = Tax
+ Double.valueOf(rdbInventry.getDataRow(i).getValue(
"TAX").toString());
}
rdbHdr.setValue("AMT_WO_TAX", SubTot);
rdbHdr.setValue("TAX", Tax);
System.out.println(SubTot);
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ModelException m) {

}

}

public void doAfterDeleting_Inventry() throws ModelException {
// System.out.println("doAfterDeleting :" + rdbInventry.getRowCount());
rdbInventry.saveSelectedRow();
// doCalc_Hdr();

}

public void doBeforeDeleting_Inventry() throws ModelException {
// System.out.println("doAfterDeleting :" + rdbInventry.getRowCount());
// rdbInventry.saveAllRows();

}

public void doBeforeDeleted_Inventry() throws ModelException {
// System.out.println("doAfterDeleting :" + rdbInventry.getRowCount());

}

public void doAfterDeleted_Inventry() throws ModelException {
// doCalc_Hdr();
// System.out.println("doAfterDeleted :" + rdbInventry.getRowCount());
}
public void doBeforeInserting_Inventry() throws ModelException {
// rdbInventry.setValue("COMP_CODE", 1);
// rdbInventry.setValue("QTY_S", 0);
// rdbInventry.setValue("MRP_PER_PCS", 0);
// rdbInventry.setValue("AMT", 0);
// rdbInventry.setValue("MULT", -1);
}
public void doBeforeInserted_Inventry() throws ModelException {

// rdbInventry.setValue("COMP_CODE", 1);
// rdbInventry.setValue("QTY_S", 0);
// rdbInventry.setValue("MRP_PER_PCS", 0);
// rdbInventry.setValue("AMT", 0);
// rdbInventry.setValue("MULT", -1);
}
public void doAfterInserting_Inventry() throws ModelException {
//rdbHdr.reload(SelectionMode.FIRST_ROW);
// rdbInventry.setValue("COMP_CODE", 1);
rdbInventry.setValue("QTY_S", 0);
rdbInventry.setValue("MRP_PER_PCS", 0);
rdbInventry.setValue("AMT", 0);
rdbInventry.setValue("MULT", -1);
// System.out.println("Today = " + rdbInventry.getValue("TRN_DT"));
// rdbInventry.setValue("TRN_TYPE", "SALE");
// rdbInventry.setValue("COMP_CODE", 1);
// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:SS.S");
// Date today;
// int year;
// try {
// today = df.parse(rdbHdr.getValue("TRN_DT").toString());
// System.out.println("Today = " + df.format(today));
// Calendar now = Calendar.getInstance();
// now.setTime(today);
// year = now.get(Calendar.YEAR);
// rdbInventry.setValue("ACCT_YEAR", year);
// } catch (ParseException e) {
// e.printStackTrace();
// }
// rdbInventry.saveSelectedRow();
}
public void doAfterInserting_Hdr() throws ModelException {
rdbHdr.setValue("COMP_CODE", 1);
rdbHdr.setValue("TRN_TYPE", "SALE");

rdbHdr.saveSelectedRow();


// rdbHdr.reload(SelectionMode.CURRENT_ROW);

// rdbHdr.getDataSource().saveAllDataBooks();

System.out.println("ID :" + rdbHdr.getValue("ID"));
//rdbHdr.getDataSource().saveAllDataBooks();
// rdbInventry.saveSelectedRow();
}
public void doAfterInserted_Hdr() throws ModelException {
// RemoteDataBook rdb = new RemoteDataBook();
// rdb.setDataSource(dataSource);
// rdb.setName("Hdrid");
// rdb.open();
// System.out.println("IDn :" + rdb.getValue("ID"));

// rdbHdr.getDataBook().restoreSelectedRow();

// rdbHdr.setValue("COMP_CODE", 1);
// rdbHdr.setValue("TRN_TYPE", "SALE");
// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:SS.S");

// DateFormat df = new SimpleDateFormat("dd.MM.yyyy");
// Date today;
// int year;
// try {
// //today = df.parse(rdbHdr.getValue("TRN_DT").toString());
// today = df.parse(edtTRN_DT.toString());
// System.out.println("Today = " + df.format(today));
// Calendar now = Calendar.getInstance();
// now.setTime(today);
// year = now.get(Calendar.YEAR);
// rdbHdr.setValue("ACCT_YEAR", year);
// } catch (ParseException e) {
// e.printStackTrace();
// }
// // rdbInventry.saveSelectedRow();
}

} // ContactsFrame






/*
* Copyright 2009 SIB Visions GmbH
*
* Licensed under the Apache License, Version 2.0 (the "License"); you may not
* use this file except in compliance with the License. You may obtain a copy of
* the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
* License for the specific language governing permissions and limitations under
* the License.
*
*
* History
*
* 25.08.2009 - [JR] - creation
*/
package apps.firstapp.frames;

import com.sibvisions.rad.persist.jdbc.DBStorage;

import apps.firstapp.Session;

/**
* The LCO for the DBEdit WorkScreen.
* <p/>
* @author René Jahn
*/
public class DBEdit extends Session
{
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// User-defined methods
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Returns the contacts storage.
*
* @return the contacts storage
* @throws Exception if the initialization throws an error
*/
public DBStorage getHdr() throws Exception
{
DBStorage dbsHdr = (DBStorage)get("hdr");

if (dbsHdr == null)
{
dbsHdr = new DBStorage();
dbsHdr.setDBAccess(getDBAccess());
dbsHdr.setRefetch(true);
dbsHdr.setFromClause("MEDSOFT.v_hdr");
dbsHdr.setWritebackTable("MEDSOFT.hdr");
dbsHdr.setAutoLinkReference(true);
dbsHdr.open();

put("hdr", dbsHdr);
}

return dbsHdr;
}

/**
* Returns the contacts storage.
*
* @return the contacts storage
* @throws Exception if the initialization throws an error
*/
public DBStorage getInventry() throws Exception
{
DBStorage dbsHdr = (DBStorage)get("Inventry");

if (dbsHdr == null)
{
dbsHdr = new DBStorage();
dbsHdr.setDBAccess(getDBAccess());
dbsHdr.setFromClause("MEDSOFT.v_inv_items");
dbsHdr.setWritebackTable("MEDSOFT.Inventry");
dbsHdr.setAutoLinkReference(true);
dbsHdr.open();

put("Inventry", dbsHdr);
}

return dbsHdr;
}

public DBStorage getItems() throws Exception
{
DBStorage dbsItems = (DBStorage)get("items");

if (dbsItems == null)
{
dbsItems = new DBStorage();
dbsItems.setDBAccess(getDBAccess());
dbsItems.setFromClause("MEDSOFT.items");
dbsItems.setWritebackTable("MEDSOFT.items");
dbsItems.open();

put("items", dbsItems);
}

return dbsItems;
}
public DBStorage getParties() throws Exception
{
DBStorage dbsParty = (DBStorage)get("parties");

if (dbsParty == null)
{
dbsParty = new DBStorage();
dbsParty.setDBAccess(getDBAccess());
dbsParty.setFromClause("MEDSOFT.party");
dbsParty.setWritebackTable("MEDSOFT.party");
dbsParty.open();

put("parties", dbsParty);
}

return dbsParty;
}

public DBStorage getDoctors() throws Exception
{
DBStorage dbsDoctors = (DBStorage)get("doctors");

if (dbsDoctors == null)
{
dbsDoctors = new DBStorage();
dbsDoctors.setDBAccess(getDBAccess());
dbsDoctors.setFromClause("medsoft.doctors");
dbsDoctors.setWritebackTable("medsoft.doctors");
dbsDoctors.open();

put("doctors", dbsDoctors);
}

return dbsDoctors;
}
public DBStorage getStock() throws Exception
{
DBStorage dbsStock = (DBStorage)get("stock");

if (dbsStock == null)
{
dbsStock = new DBStorage();
dbsStock.setDBAccess(getDBAccess());
dbsStock.setFromClause("MEDSOFT.v_stock_items");
// dbsStock.setWritebackTable("V_STOCK");
dbsStock.open();

put("stock", dbsStock);
}

return dbsStock;
}

public DBStorage getHdrid() throws Exception
{
DBStorage dbs = (DBStorage)get("Hdrid");
if (dbs == null) {
dbs = new DBStorage();
dbs.setDBAccess(getDBAccess());
dbs.setFromClause("SELECT LAST_INSERT_ID()");
dbs.s
dbs.open();

put("Hdrid", dbs);
}
return dbs;
}
} // DBEdit


with regards

Dipankar
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Sat Jun 12, 2010 1:20 pm

which connect string do you use (getDBAccess)?
Do you connect to a different schema than MEDSOFT ?

Try removing schema names:

MEDSOFT.v_hdr -> v_hdr


Another info

You can reuse cell editors: only 1 instance per cell editor/format is enough!
You can put them into a utility class or your application class?

public static UINumberCellEditor ced00 = new UINumberCellEditor(".00")

rdbHdr.getRowDefinition().getColumnDefinition("TOT_TAX").getDataType()
.setCellEditor(ced00);
rdbHdr.getRowDefinition().getColumnDefinition("AMT_WO_TAX")
.getDataType().setCellEditor(ced00);

You save memory if your application grows.
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sat Jun 12, 2010 3:49 pm

here is the connection string

/*
* Copyright 2009 SIB Visions GmbH
*
* Licensed under the Apache License, Version 2.0 (the "License"); you may not
* use this file except in compliance with the License. You may obtain a copy of
* the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
* License for the specific language governing permissions and limitations under
* the License.
*
*
* History
*
* 25.08.2009 - [JR] - creation
*/
package apps.firstapp;

import com.sibvisions.rad.persist.jdbc.DB2DBAccess;
import com.sibvisions.rad.persist.jdbc.DBAccess;
import com.sibvisions.rad.persist.jdbc.HSQLDBAccess;
import com.sibvisions.rad.persist.jdbc.MySQLDBAccess;
import com.sibvisions.rad.persist.jdbc.OracleDBAccess;
import com.sibvisions.rad.persist.jdbc.PostgreSQLDBAccess;

/**
* The LCO for the session.
* <p/>
* @author René Jahn
*/
public class Session extends Application
{
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// User-defined methods
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/**
* Returns access to the database.
*
* @return the database access
* @throws Exception if a connection error occurs
*/
public DBAccess getDBAccess() throws Exception
{
DBAccess dba = (DBAccess)get("dbAccess");

if (dba == null)
{
dba = new MySQLDBAccess();
dba.setDriver("com.mysql.jdbc.Driver");
dba.setUrl("jdbc:mysql://localhost/medsoft");
dba.setUsername("root");
dba.setPassword("admin");
dba.setDBProperty ("useOldAliasMetadataBehavior", "true");
dba.open();
}

return dba;
}

} // Session

I am connecting to medsoft schema and also tried by removing schema name, but result is the same.

what is the work around in after insert event ? please put some code example.
by the how to call store proc or funcntion from event handler say calling insert_last_id() from afterinserted event ?

Thanks

Thank you for other information.
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Sat Jun 12, 2010 11:55 pm

Some infos:

  • don't mix upper and lower case column names. We suggest upper case column names!
    example:
    `profit_per` DOUBLE(15,5) NULL DEFAULT NULL,
    `stock` TINYINT(1) NULL DEFAULT NULL,
    `WBST` DOUBLE(7,2) NULL DEFAULT NULL,
  • your inventry create statement doesn't work because of:
    REFERENCES `items` (`ID`) -> REFERENCES `hdr` (`ID`)
  • refetch: default = true
  • autlink reference detection: default = true

We tried to reproduce your problem with:

mysql server 5.1.33
JDBC driver 5.1.6 (your version)
JVx 0.8 beta2

first JUnit test for the base (success):

Code: Select all
dba = DBAccess.getDBAccess("jdbc:mysql://localhost:3306/test");
dba.setUsername("test");
dba.setPassword("test");
dba.open();

DBStorage dbs = new DBStorage();
dbs.setDBAccess(dba);
dbs.setFromClause("hdr");
dbs.setWritebackTable("hdr");
dbs.open();
      
IBean bean = new Bean();
bean.put("COMP_CODE", new BigDecimal(1));
      
bean = dbs.insertBean(bean);
      
//the NEW ID!
Assert.assertNotNull(bean.get("ID"));


second test with UI (success):

Server side:

Code: Select all
public DBStorage getHdr() throws Throwable
{
   DBStorage dbs = (DBStorage)get("hdr");
   
   if (dbs == null)
   {
      dbs = new DBStorage();
      dbs.setDBAccess(getMyAccess());
      dbs.setFromClause("hdr");
      dbs.setWritebackTable("hdr");
      dbs.open();
      
      put("hdr", dbs);
   }
   
   return dbs;
}

public DBStorage getInventry() throws Throwable
{
   DBStorage dbs = (DBStorage)get("inventry");
   
   if (dbs == null)
   {
      dbs = new DBStorage();
      dbs.setDBAccess(getMyAccess());
      dbs.setFromClause("inventry");
      dbs.setWritebackTable("inventry");
      dbs.open();
      
      put("inventry", dbs);
   }
   
   return dbs;
}


Clien side:

Code: Select all
SubConnection con = macon.createSubConnection("test.MySql");
con.open();

rds = new RemoteDataSource(con);
rds.open();

rdbHdr.setDataSource(rds);
rdbHdr.setName("hdr");
rdbHdr.open();

rdbHdr.setFilter(new LikeIgnoreCase("TRN_TYPE", "SALE"));

rdbHdr.getRowDefinition().setTableColumnNames(new String[] {"ID", "COMP_CODE"});

rdbInventry.setDataSource(rds);
rdbInventry.setName("inventry");
rdbInventry.setMasterReference(new ReferenceDefinition(new String[] {"HDR_ID"}, rdbHdr, new String[] {"ID"}));
rdbInventry.open();

rdbInventry.getRowDefinition().setTableColumnNames(new String[] {"ID", "COMP_CODE"});


We made our screen very simple with a UIBorderLayout and added two UITables (CENTER, EAST).

When we insert a new row into the hdr table (press INSERT key) and afterwards a new row into the inventry table, then the ID of the hdr row will be set/refetched because the master was inserted!

A difference between our test environment and yours is that you use views as from clause, and maybe the DB version (but we don't think that this version difference causes the problem).

We can not reproduce your problem!

Please try to simplify your screen (only hdr and inventry table), and try to reproduce step by step.
Maybe you will post your views?
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sun Jun 13, 2010 8:55 am

Now I got the problem. It is working when I am using the hdr table but when I am using v_hr view then it is not working. But I have to show the party description and doctor description from other two tables. So please give some guide lines how to do that without using view or using view.

the structure of the views and the other tables are as follows
Structure of v_hdr view

select `medsoft`.`hdr`.`ID` AS `ID`,`medsoft`.`hdr`.`COMP_CODE` AS `COMP_CODE`,`medsoft`.`hdr`.`ACCT_YEAR` AS `ACCT_YEAR`,`medsoft`.`hdr`.`TRN_TYPE` AS `TRN_TYPE`,`medsoft`.`hdr`.`DOC_NO` AS `DOC_NO`,`medsoft`.`hdr`.`TRN_DT` AS `TRN_DT`,`medsoft`.`hdr`.`PARTY` AS `PARTY`,`medsoft`.`hdr`.`DOCTOR` AS `DOCTOR`,`medsoft`.`hdr`.`TOT_TAX` AS `TOT_TAX`,`medsoft`.`hdr`.`AMT_WO_TAX` AS `AMT_WO_TAX`,`medsoft`.`hdr`.`AMOUNT` AS `AMOUNT`,`medsoft`.`hdr`.`DISC` AS `DISC`,`medsoft`.`hdr`.`SP_DISC` AS `SP_DISC`,`medsoft`.`hdr`.`ADJUSTMENT` AS `ADJUSTMENT`,`medsoft`.`hdr`.`C_NOTE` AS `C_NOTE`,`medsoft`.`hdr`.`NET_AMOUNT` AS `NET_AMOUNT`,`medsoft`.`hdr`.`AMOUNT_PAID` AS `AMOUNT_PAID`,`medsoft`.`hdr`.`AMOUN_DUE` AS `AMOUN_DUE`,`medsoft`.`hdr`.`NARRATION` AS `NARRATION`,`medsoft`.`hdr`.`PAYMENT_DISC` AS `PAYMENT_DISC`,`medsoft`.`hdr`.`P_DOC_NO` AS `P_DOC_NO`,`medsoft`.`hdr`.`P_DT` AS `P_DT`,`medsoft`.`hdr`.`STOCK` AS `STOCK`,`medsoft`.`hdr`.`TAXONFQTY` AS `TAXONFQTY`,`medsoft`.`party`.`PARTY_DESC` AS `PARTY_DESC`,`medsoft`.`party`.`ADDRESS` AS `ADDRESS`,`medsoft`.`doctors`.`DOCT_NAME` AS `DOCT_NAME` from ((`medsoft`.`hdr` left join `medsoft`.`party` on((`medsoft`.`hdr`.`PARTY` = `medsoft`.`party`.`PARTY`))) left join `medsoft`.`doctors` on((`medsoft`.`hdr`.`DOCTOR` = `medsoft`.`doctors`.`ID`))) order by `medsoft`.`hdr`.`TRN_DT` desc limit 200

Structure if v_inv_items view

select `medsoft`.`items`.`DESCRIPTION` AS `DESCRIPTION`,`medsoft`.`stock_tbl`.`ITEM_CODE` AS `ITEM_CODE`,`medsoft`.`stock_tbl`.`BATCH` AS `BATCH`,`medsoft`.`stock_tbl`.`EXP_DT` AS `EXP_DT`,`medsoft`.`stock_tbl`.`MRP_PER_PCS` AS `MRP_PER_PCS`,`medsoft`.`stock_tbl`.`profit_per` AS `profit_per`,`medsoft`.`stock_tbl`.`STOCK` AS `STOCK` from (`medsoft`.`stock_tbl` join `medsoft`.`items` on((`medsoft`.`stock_tbl`.`ITEM_CODE` = `medsoft`.`items`.`ID`))) order by `medsoft`.`items`.`DESCRIPTION`,`medsoft`.`stock_tbl`.`EXP_DT`

Structure of items table :

CREATE TABLE `items` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`DESCRIPTION` VARCHAR(50) NULL DEFAULT NULL,
`REORD_LVL` INT(10) NULL DEFAULT NULL,
`REORD_QTY` INT(10) NULL DEFAULT NULL,
`MFG_CODE` INT(10) NULL DEFAULT NULL,
`SCHD` VARCHAR(2) NULL DEFAULT NULL,
`MRP` DOUBLE(15,5) NULL DEFAULT NULL,
`RATE` DOUBLE(15,5) NULL DEFAULT NULL,
`TAX` DOUBLE(15,5) NULL DEFAULT NULL,
`PCS_PER_PACK` SMALLINT(5) NULL DEFAULT NULL,
`WBST` DOUBLE(7,2) NULL DEFAULT NULL,
`FREE_AMT` DOUBLE(15,5) NULL DEFAULT NULL,
`FREE_QTY` DOUBLE(15,5) NULL DEFAULT NULL,
`ED` DOUBLE(15,5) NULL DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE INDEX `IT_CODE` (`ID`),
INDEX `MFG_CODE` (`MFG_CODE`),
INDEX `DESCRIPTION` (`DESCRIPTION`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=17819

Structure of party table :

CREATE TABLE `party` (
`PARTY` INT(10) NOT NULL AUTO_INCREMENT,
`COMP_CODE` INT(10) NULL DEFAULT NULL,
`PARTY_DESC` VARCHAR(50) NULL DEFAULT NULL,
`ADDRESS` VARCHAR(50) NULL DEFAULT NULL,
`CITY` VARCHAR(50) NULL DEFAULT NULL,
`DIST` VARCHAR(50) NULL DEFAULT NULL,
`TYPE` VARCHAR(20) NULL DEFAULT NULL,
`COMP_ID` INT(10) NULL DEFAULT NULL,
`PHONE1` VARCHAR(20) NULL DEFAULT NULL,
`PHONE2` VARCHAR(20) NULL DEFAULT NULL,
`DL_NO` VARCHAR(20) NULL DEFAULT NULL,
`photo` LONGBLOB NULL,
PRIMARY KEY (`PARTY`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=5585

Structure of Doctors table
CREATE TABLE `doctors` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`DOCT_NAME` VARCHAR(50) NULL DEFAULT NULL,
`ADDRESS` VARCHAR(50) NULL DEFAULT NULL,
`CITY` VARCHAR(25) NULL DEFAULT NULL,
`DIST` VARCHAR(25) NULL DEFAULT NULL,
`PHONE1` VARCHAR(20) NULL DEFAULT NULL,
`PHONE2` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=897

Structure of stock_tbl :

CREATE TABLE `stock_tbl` (
`ITEM_CODE` INT(10) NULL DEFAULT NULL,
`BATCH` VARCHAR(10) NULL DEFAULT NULL,
`EXP_DT` INT(10) NULL DEFAULT NULL,
`MRP_PER_PCS` DOUBLE(15,5) NULL DEFAULT NULL,
`profit_per` DOUBLE(15,5) NULL DEFAULT NULL,
`STOCK` DECIMAL(37,0) NULL DEFAULT NULL
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

Structure of v_stock_items view :

select `medsoft`.`items`.`DESCRIPTION` AS `DESCRIPTION`,`medsoft`.`stock_tbl`.`ITEM_CODE` AS `ITEM_CODE`,`medsoft`.`stock_tbl`.`BATCH` AS `BATCH`,`medsoft`.`stock_tbl`.`EXP_DT` AS `EXP_DT`,`medsoft`.`stock_tbl`.`MRP_PER_PCS` AS `MRP_PER_PCS`,`medsoft`.`stock_tbl`.`profit_per` AS `profit_per`,`medsoft`.`stock_tbl`.`STOCK` AS `STOCK` from (`medsoft`.`stock_tbl` join `medsoft`.`items` on((`medsoft`.`stock_tbl`.`ITEM_CODE` = `medsoft`.`items`.`ID`))) order by `medsoft`.`items`.`DESCRIPTION`,`medsoft`.`stock_tbl`.`EXP_DT`


So please check whether there is any problen with the view or table definition which causing problem refetching the autoincreament value.

Thank you

with regard

Dipankar
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Sun Jun 13, 2010 9:55 am

Your screen and your views does not work together!

Your detail view (v_inv_items) does not contain the columns ID and HDR_ID?


Some tips:

  • Don't use double for calculation -> BigDecimal
    Our server side returns BigDecimal!
    Use (BigDecimal)rdbInventry.getValue("MRP_PER_PCS") instead of Double.valueOf(rdbInventry.getValue("MRP_PER_PCS").toString())
  • stock_tbl has no Primary Key
  • useOldAliasMetadataBehavior is set to true in MySQLDBAccess
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sun Jun 13, 2010 3:28 pm

Sorry , it was a mistake. The correct structure of v_inv_items is


select `medsoft`.`inventry`.`ID` AS `ID`,`medsoft`.`inventry`.`COMP_CODE` AS `COMP_CODE`,`medsoft`.`inventry`.`ACCT_YEAR` AS `ACCT_YEAR`,`medsoft`.`inventry`.`TRN_TYPE` AS `TRN_TYPE`,`medsoft`.`inventry`.`DOC_NO` AS `DOC_NO`,`medsoft`.`inventry`.`TRN_DT` AS `TRN_DT`,`medsoft`.`inventry`.`PARTY` AS `PARTY`,`medsoft`.`inventry`.`ITEM_CODE` AS `ITEM_CODE`,`medsoft`.`inventry`.`BATCH` AS `BATCH`,`medsoft`.`inventry`.`EXP` AS `EXP`,`medsoft`.`inventry`.`QTY_S` AS `QTY_S`,`medsoft`.`inventry`.`QTY_P` AS `QTY_P`,`medsoft`.`inventry`.`PCS_PER_PACK` AS `PCS_PER_PACK`,`medsoft`.`inventry`.`PCS_PAID_FOR` AS `PCS_PAID_FOR`,`medsoft`.`inventry`.`RATE` AS `RATE`,`medsoft`.`inventry`.`MRP` AS `MRP`,`medsoft`.`inventry`.`PTR` AS `PTR`,`medsoft`.`inventry`.`PTRPERPCS` AS `PTRPERPCS`,`medsoft`.`inventry`.`TAX` AS `TAX`,`medsoft`.`inventry`.`TAX_AMT` AS `TAX_AMT`,`medsoft`.`inventry`.`ED` AS `ED`,`medsoft`.`inventry`.`DISC` AS `DISC`,`medsoft`.`inventry`.`AMT_WO_TAX` AS `AMT_WO_TAX`,`medsoft`.`inventry`.`AMT` AS `AMT`,`medsoft`.`inventry`.`FREE_AMT` AS `FREE_AMT`,`medsoft`.`inventry`.`FREE_QTY` AS `FREE_QTY`,`medsoft`.`inventry`.`MULT` AS `MULT`,`medsoft`.`inventry`.`MRP_PER_PCS` AS `MRP_PER_PCS`,`medsoft`.`inventry`.`Schd` AS `Schd`,`medsoft`.`inventry`.`p_date` AS `p_date`,`medsoft`.`inventry`.`p_time` AS `p_time`,`medsoft`.`inventry`.`sale_q` AS `sale_q`,`medsoft`.`inventry`.`profit_per` AS `profit_per`,`medsoft`.`inventry`.`stock` AS `stock`,`medsoft`.`inventry`.`WBST` AS `WBST`,`medsoft`.`inventry`.`HDR_ID` AS `HDR_ID`,`medsoft`.`items`.`DESCRIPTION` AS `DESCRIPTION` from (`medsoft`.`inventry` left join `medsoft`.`items` on((`medsoft`.`inventry`.`ITEM_CODE` = `medsoft`.`items`.`ID`)))

Thanks
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Sun Jun 13, 2010 6:10 pm

OK, now the screen opens.

Tested following:

1.) inserted a new row (INS key pressed) into the master table (left)
2.) clicked with the mouse on the detail table (right), insert a new row (INS key)
3.) the master id is available -> 17

see following screenshot

refetch_id.png
refetch_id.png (76.79 KiB) Viewed 12148 times


The only thing we commented out:
Code: Select all
doAfterInserting_Hdr()
{
...
}


If we don't comment out the method, the id also there but immediately.

The difference between our code and yours is the server side. We removed the schema name for all of our DBStorages:

Code: Select all
dbsHdr.setFromClause("v_hdr");

instead of
Code: Select all
dbsHdr.setFromClause("test.v_hdr");

in ALL definitions!

With the schema it is possible to reproduce your problem. Looks like a problem with key detection when schema is used.

But in your application its not necessary to use the schema prefix!
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sun Jun 13, 2010 9:15 pm

I have tried after removing schema names. But the result is same. It is working with table but not working with view. May be due to MySql version. I will try after installing new version of MySql and let you know.

Thank you
with regard
Dipankar
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Sun Jun 13, 2010 10:21 pm

Good news. It is working after creating a new schema and transferring the tables hdr and inventry without old data to it. It is working with same version of MySql i.e 5.1.25 -rc. May be there was some problem with data in the tables.

Thank you very much for your sincere and fast cooperation.

Thank you again,

with regard

Dipankar
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Sun Jun 13, 2010 10:36 pm

Did you drop and create the schema with the same name or did you create a second schema with a different name?

Do you set the schema to the DBStorage or without schema?

We don't believe that the data caused the problems :)
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Mon Jun 14, 2010 5:45 am

I tested earlier by removing the schema name from all definition of DBStorage but it failed. Then I decided to create a new schema.

I created a second schema and transferred the tables without data from the old schema to new schema. Then I populated all other tables except hdr and inventry table. Then changed the connection url in DBAcsess.

At presenr there is no schema name in DBStorage deffinition.
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby dipankarray » Mon Jun 14, 2010 6:04 am

Now I found the problem.

There was a limit 200 clause in v_hdr view. I removed the clause and it is working with same old schema.

The hdr tables contains more than 100000 rows and invetry table contains more than 200000 rows. Performance is very good with such large amount of data.

It is also working with the schema name as prefix i.e medsoft.v_hdr and also withou that i.e. v_hr
dipankarray
 
Posts: 9
Joined: Fri Jun 11, 2010 6:43 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Mon Jun 14, 2010 10:30 am

OK.

We saw the limit 200, but it doesn't matter because we had no data in our table.

It is possible to reproduce the problem without data, if we use the schema. The detection of the PK columns fails, in some cases, if the schema is used.

It is no problem if you don't use the schema - which is not necessary four common applications.

Be careful with views and where clauses/limits/conditions because if you show only special data and the user enters data which are not included in your restrictions - the data will not be shown - because the view doesn't show (e.g. where condition: STATE = '1' and the default value of the column is set to '0').

But it's recommended to use views (for complex joins and if you don't need or manually configure auto link cell editors) for display and tables for write back!


Details: Support System
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm

Re: Refeching MySql Autoincreamnt value in UI

Postby Support@SIB » Mon Jun 14, 2010 11:38 am

If you want to know which statements will be used to query data, set the log level:

Code: Select all
com.sibvisions.rad.persist.level = ALL

in your logging.properties or in your source code whith the log API.
User avatar
Support@SIB
 
Posts: 201
Joined: Mon Sep 28, 2009 1:56 pm


Return to Development