problem with addjoin with mysql database

[Keshava Murthy <ke...@bigtec.org>]

Hi ,
The following method in screen class returns the data without any problem if
the database used is Oracle 8i and when I change the database to mysql the
method returns null value.

private Vector getPatientRegnData(){
 try
 {
String strPin="1";
 String strEncNo="1";
 Criteria criteria          =  new Criteria();

/* columns from his_mas_patregn table /*


criteria.addJoin(HisMasPatregnPeer.IPAT_INO_PREG,HisTrxPatrevisitPeer.IPAT_I
NO_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_INO_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_TITLE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_FNAME_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_MNAME_PREG );
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_LNAME_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_SEX_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.DTPAT_DOB_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_TYPE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_BLDGRP_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_IDFNMARKS_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_MRLSTS_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_ADDR_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_AREA_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_CITY_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_STATE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_CTRY_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_ZIP_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_PHONE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_ATNDOC_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IPAT_REFBY_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.SPAT_NOTES_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.DTPAT_REGDT_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.DTCRT_DATE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.DTMDF_DATE_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.ICRT_BY_PREG);
 criteria.addSelectColumn(HisMasPatregnPeer.IMDF_BY_PREG);

/* columns from his_trx_patrevisit table /*

 criteria.addSelectColumn(HisTrxPatrevisitPeer.IPAT_ENCNO_PRVT);
 criteria.addSelectColumn(HisTrxPatrevisitPeer.DTPAT_RVTDTTM_PRVT);

 Criteria.Criterion a1  =
criteria.getNewCriterion(HisMasPatregnPeer.IPAT_INO_PREG,strPin,Criteria.LIK
E);
 Criteria.Criterion b1  =
criteria.getNewCriterion(HisTrxPatrevisitPeer.IPAT_ENCNO_PRVT,strEncNo,Crite
ria.LIKE);
 criteria.add(a1.and(b1));
  return HisMasPatregnPeer.doPSSelect(criteria);
 }
 catch (Exception e)
 {
 return null;
}
}

I also did some debugging and found that if I comment columns from
his_trx_patrevisit table the method returns vector conatins data.
I also checked the sql query generated(criteria.toString()) from mysql
prompt and it returns proper data, which means that addjoin query is not
working in mysql  and it is working properly when oracle is the database
when application is run.

My question is why it is returning vector containg null  when I use mysql as
database?

regards,
Keshava Murthy. S