[Opensource] DBObjects limitations

[Paulo Dias]

I advise you to create database views in order to hide the sql complexity,
and the use DBObject as if it is a simple table.
Trying to match database complex relations out of database world is very
hard and a waste of time.

Paulo Dias
ACE Sistemas Comerciais
(+351)210054200
http://www.ace-sc.com


|--------+------------------------------->
|        |          inadareishvili at worldb|
|        |          ank.org              |
|        |          Sent by:             |
|        |          opensource-admin at jcor|
|        |          porate.com           |
|        |                               |
|        |                               |
|        |          2002-12-16 02:54 PM  |
|        |          Please respond to    |
|        |          opensource           |
|        |                               |
|--------+------------------------------->
  >---------------------------------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                                             |
  |       To:     opensource at jcorporate.com                                                                                                     |
  |       cc:                                                                                                                                   |
  |       Subject:     [Opensource] DBObjects limitations                                                                                       |
  >---------------------------------------------------------------------------------------------------------------------------------------------|




I am pretty new to Expresso framework, trying to port from a system that
was not
using persistence layer, and had  several problems with using DBObjects.
Not
sure if its DBObjects problem or me being a newbie.
Could somebody help, please?

1. Setting up UPPER,LOWER, UNIQUE, DECODE
We were not able to use SQL function like UPPER,LOWER, UNIQUE, DECODE etc.

2. Setting Complex WHERE conditions
Complex WHERE condition needs to be set as
    custList.setCustomWhereClause("CustomerType = \"AA"\ OR CustomerType =
\"BA"
\");
As you can see, we need to generate the WHERE condition with column names
and
other details. This means, the SQL statements with specific COLUMN_NAME
will be
present in JAVA classes. (Note: These are Business Component java classes
and
not DB objects). Any changes to the database/tables, will have impact on
both DB
Object and Business Component JAVA classes.
For Example, we will have to build the SQL WHERE condition as follows
inside our
search
       s.sco_id = a.sco_id
 and s.sco_id = l.sco_id
 and l.country_iso3 in ('AFG')
 and a.start_year >= '1995'
 and a.start_year <= '1996'
 and s.sco_id = sec.sco_id
 and s.org_harmonized_char in
('UNRF','UNRI','UNRW','UNSO','UNTA','UNUE','UNUN','UNVL','UPUN','UTRA',
'WFPR','WHOR','WIPO','WMOR','WTOR','UNIT','UAID','UCAP','UNAI','UNAS','UNCC','UNCD',

'UNCH','UNCR','UNCT','UNDC','UNDE','UNDM','UNDP','UNEP','UNES','UNFD','UNFF','UNFP','UNFS',

'UNHC','UNHQ','UNHR','UNIA','UNIC','UNID','UNIF','PAHO','ITUN','ECAF','ECEU','CLAD','FAOR',

'ESCA','ESCW','IAEA','ICAO','IFAD','ILOR','IMOR','IOCO','ITCE')

Any change to the tables, we need to change the JAVA classes to reflect the
same.

3. Virtual table selects / INNER SELECT statements
We use inner SELECT statements to get the specified range of records. This
is
not possible in DB Objects as the end result is coming from a virtual
table.
(Note : DB Object only offers a mechanism to limit the number of records
retrieved (TOPN). )
This means if we need to display records from 401 to 450, then we need to
take
the records till 450 and ignore the records from 1-400. In the existing
implementation, we eliminate the 1-400 records in the database itself.
Serious
performance issue
Thanks
Irakli


_______________________________________________
Opensource mailing list
Opensource at jcorporate.com
http://mail.jcorporate.com/mailman/listinfo/opensource
Archives: http://mail.jcorporate.com/pipermail/opensource/