Version 8.2 (2013-03-18)	SEARCH   CONTENTS   |   PDF BOOKS   |   HOME   UP   PREV   NEXT

The DBFIELD control represents a filter criterion of a database query. It provides for a value help that is read from the database, a convenient way to append the filter criterion to the SELECT statement, and the ability to reflect a "to-one" dependency between filter criteria.

This document covers the following topics:

• Example

• Properties

Example

The following image shows an example in which two DBFIELD controls are used for the filter criteria "Country" and "State" within a simple business partner report. Both fields provide for value help. The field "State" is defined to be dependent on "Country". As a consequence, the list of valid values for "State" is country-specific. The result shows partners that reside in state "New York" of the United States of America.

The following screenshots demonstrate the dependency between country and state. The first pop-up shows the valid states if country is set to "US". The second pop-up shows the valid states in Germany.

Have a look at the XML layout definition:

<rowarea name="Search Criteria">
    <itr>
        <label name="Country" width="60">
        </label>
        <dbfield valueprop="dBFieldCountry" querycolumn="COUNTRY" datasource="addressdb" 
                 valuehelptable="COUNTRY" valuehelpcolumn="ID">
        </dbfield>
    </itr>
    <itr>
        <label name="State" width="60">
        </label>
        <dbfield valueprop="dBFieldState" querycolumn="STATE" datasource="addressdb"
                 valuehelptable="STATE" valuehelpcolumn="ID" valuehelpcolumncond="COUNTRY">
        </dbfield>
        <hdist width="100%">
        </hdist>
        <button name="Execute" method="onExecute">
        </button>
    </itr>
</rowarea>
<rowarea name="Result" height="140">
    <itr height="100%">
        <textgridsss2 griddataprop="result" rowcount="5" width="100%">
            <column name="Title" property="TITLE" width="50">
            </column>
            <column name="Last Name" property="LASTNAME" width="100">
            </column>
            <column name="First Name" property="FIRSTNAME" width="100">
            </column>
            <column name="Country" property="COUNTRY" width="50">
            </column>
            <column name="State" property="STATE" width="100%">
            </column>
        </textgridsss2>
    </itr>
</rowarea>

The corresponding adapter code is:

// This class is a generated one.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import com.softwareag.cis.server.Adapter;
import com.softwareag.cis.server.IDynamicAccess;
import com.softwareag.cis.server.util.DBFIELDInfo;
import com.softwareag.cis.server.util.DBQUERYDataObject;
import com.softwareag.cis.server.util.DBQUERYInfo;
import com.softwareag.cis.server.util.DBTEXTGRIDCollection;
import com.softwareag.cis.server.util.DBTEXTGRIDLine;
import com.softwareag.cis.server.util.DBUtil;
import com.softwareag.cis.server.util.DelegateError;
import com.softwareag.cis.server.util.IDBCondition;
import com.softwareag.cis.server.util.IDBQUERYConnectionProvider;
import com.softwareag.cis.server.util.IDBQUERYContextMenuRequestListener;
import com.softwareag.cis.server.util.IDBQUERYGeneratePDFRequestListener;
import com.softwareag.cis.server.util.IDBQUERYOptimizer;
import com.softwareag.cis.server.util.MENUNODEInfo;
import com.softwareag.cis.server.util.TREECollection;

public class DBFIELD_Adapter
    extends Adapter
    implements  IDBQUERYConnectionProvider, IDBDemoAdapter
{
    private Connection m_connection;

    // property >dBFieldCountry<
    DBFIELDInfo m_dBFieldCountry = new DBFIELDInfo(this);
    public DBFIELDInfo getDBFieldCountry() { return m_dBFieldCountry; }

    // property >dBFieldState<
    DBFIELDInfo m_dBFieldState = new DBFIELDInfo(this, m_dBFieldCountry);
    public DBFIELDInfo getDBFieldState() { return m_dBFieldState; }

    // property >result<
    DBTEXTGRIDCollection m_result = new DBTEXTGRIDCollection();
    public DBTEXTGRIDCollection getResult() { return m_result; }
    public void setResult(DBTEXTGRIDCollection value) { m_result = value; }
...
    // ------------------------------------------------------------------------
    // inner classes
    // ------------------------------------------------------------------------
    /** class used for a simple connection management. */
    public class ConnectionProvider
        implements IDBQUERYConnectionProvider
    {
        public Connection getDBConnection(String datasource)
        {
            try
            {
                Class.forName("org.hsqldb.jdbcDriver");
                return DriverManager.getConnection("jdbc:hsqldb:hsql://localhost", "sa", "");
            }
            catch (Exception exc)
            {
                throw new DelegateError(exc);
            }
        }
    }
...
    public void onExecute()
    {
        try
        {
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT * FROM BUSINESSPARTNER INNER JOIN ADDRESS ON BUSINESSPARTNER.ID = 
                                 ADDRESS.BUSINESSPARTNERID");
            DBUtil.addToQuery(sb, new IDBCondition[] { m_dBFieldCountry, m_dBFieldState}, true);
            String dataSource = m_dBFieldCountry.getDataSource();
            Connection con = getDBConnection(dataSource);
            ResultSet rs = con.createStatement().executeQuery(sb.toString());
            m_result.initWithResultSet(rs);
        }
        catch (Exception exc)
        {
            throw new DelegateError(exc);
        }
   }
...
}

Both properties dBFieldCountry and dBFieldState are of type DBFIELDInfo (from the package com.softwareag.cis.server.util). The DBFIELDInfo implements (like all DB controls) the interface IDBCondition (com.softwareag.cis.server.util). With class DBUtil (com.softwareag.cis.server.util), you can append the values of the filter criteria to the SELECT statement in a convenient way. See the JavaDoc documentation of class DBUtil for details.

The DBFIELDInfo class does not open a database connection on its own (same to all DB controls). The embedding adapter provides for an implementation of interface IDBQUERYConnectionProvider (com.softwareag.cis.server.util) when creating a DBFIELDInfo object. The interface method getDBConnection is called once - at the first time the DBFIELDInfo accesses the database. There are no updates (insert/update/delete) done with this connection. As the DBFIELDInfo does not open the connection, it does not care about closing the connection.

You see that the object DBFIELDInfo dBFieldCountry is passed in the constructor of DBFIELDInfo dBFieldState. With this, you define DBFIELDINfo dBFieldState depending on dBFieldCountry. The list of valid states only shows items that belongs to the country actually set.

For displaying the result, the class DBTEXTGRIDCollection (from the package com.softwareag.cis.server.util) is used. This class extends TEXTGRIDCollection by the ability to initialise the collection with a result set (java.sql.ResultSet). For each line of the result set, it creates an object of class DBTEXTGRIDLine (package com.softwareag.cis.server.util). Class DBTEXTGRIDLine implements the interface IDynamicAccess. With this, "normal" text grid controls can be used to visualize the data of the DBTEXTGRIDCollection.

Properties

www.softwareag.com

Copyright © 2005-2013 Software AG, Darmstadt, Germany.

SEARCH   CONTENTS   |   PDF BOOKS   |   HOME   UP   PREV   NEXT