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

The DBSELECTOPTION control manages a single filter criterion of a database query. In contrast to the DBFIELD control, it allows the input of several values in a convenient way. Each value is assigned to an operator (e.g. "equals", "like", "between", etc.). The operator-value pairs are linked with a logical "OR" (represented by the string "||"). You can either directly input a string of operator-value pairs into the DBSELECTOPTION, or you invoke the value help. The resulting pop-up displays a grid of operator-value pairs. Maybe this still sounds rather difficult - wait for the following example.

The grid pop-up itself offers a value help for entering appropriate values. The list of valid values (with an optional description) is read from database. The value help table/column (that contains the valid values) can differ from the table on which the query is executed.

This document covers the following topics:

• Example

• Properties

Example

The following image shows an example in which the DBSELECTOPTION manages the filter criterion "Title" within a simple business partner report. The report shows partners that have a title equal to "Dr" or equal to "Mr".

On value help request, the following pop-up appears:

The first column defines the operator, the second the filter value. The value help of the first column returns a list of valid operators. The second column has value help, too (see properties valuehelptable and valuehelpcolumn). The following pop-up shows the list of valid values read from the table "TITLE".

Have a look at the XML layout definition:

<rowarea name="Filter Criteria">
    <itr>
        <label name="Title" width="60">
        </label>
        <dbselectoption querycolumn="TITLE" datasource="addressdb" 
                        valueprop="dBSelectOptionTitle" valuehelptable="TITLE"
                        valuehelpcolumn="ID">
        </dbselectoption>
        <hdist width="100%">
        </hdist>
        <button name="Execute" method="onExecute">
        </button>
    </itr>
</rowarea>
<vdist>
</vdist>
<rowarea name="Result" height="140">
    <itr height="100%">
        <textgridsss2 griddataprop="result" rowcount="5" width="100%">
            <column name="Title" property="TITLE" width="20%">
            </column>
            <column name="Last Name" property="LASTNAME" width="40%">
            </column>
            <column name="First Name" property="FIRSTNAME" width="40%">
            </column>
        </textgridsss2>
    </itr>
</rowarea>

The corresponding adapter code is:

package com.softwareag.cis.test20;

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

import com.softwareag.cis.server.Adapter;
import com.softwareag.cis.server.util.DBSELECTOPTIONInfo;
import com.softwareag.cis.server.util.DBTEXTGRIDCollection;
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;

public class DBSELECTOPTIONDemoAdapter
    extends Adapter
    implements IDBQUERYConnectionProvider
{
    // ------------------------------------------------------------------------
    // members
    // ------------------------------------------------------------------------

	Connection m_connection;
	
    // ------------------------------------------------------------------------
    // property access
    // ------------------------------------------------------------------------

    // property >dbselectOptionTitle<
    DBSELECTOPTIONInfo m_dBSelectOptionTitle = new DBSELECTOPTIONInfo(this, this);
    public DBSELECTOPTIONInfo getDBSelectOptionTitle() { return m_dBSelectOptionTitle; }
       
    // property >result<
    DBTEXTGRIDCollection m_result = new DBTEXTGRIDCollection();
    public DBTEXTGRIDCollection getResult() { return m_result; }
    public void setResult(DBTEXTGRIDCollection value) { m_result = value; }


    // ------------------------------------------------------------------------
    // public adapter methods
    // ------------------------------------------------------------------------

    /** implementation of interface IDBQUERYConnectionProvider */
    public Connection getDBConnection(String datasource)
    {    	
       if (m_connection == null)
       {
	    try
	    {
	       Class.forName("org.hsqldb.jdbcDriver");
	       m_connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost", "sa", "");
	    }
           catch (Exception exc)
           {
	    	throw new DelegateError(exc);
	    }
	}
	return m_connection;
    }

    /** executes the query with current values of the filter criteria "Title". */  
    public void onExecute()
    {
    	try
    	{
    	   StringBuffer sb = new StringBuffer();
    	   sb.append("SELECT * FROM BUSINESSPARTNER");
	   DBUtil.addToQuery(sb, new IDBCondition[] { m_dBSelectOptionTitle}, true);

	   String dataSource = m_dBSelectOptionTitle.getDataSource();
	   Connection con = getDBConnection(dataSource);
	   ResultSet rs = con.createStatement().executeQuery(sb.toString());
    	   m_result.initWithResultSet(rs);
    	}
    	catch (Exception exc)
    	{
    	   throw new DelegateError(exc);
    	}
    }
}

The adapter property dbselectOptionTitle is of type DBSELECTOPTIONInfo (from the package com.softwareag.cis.server.util). The DBSELECTOPTIONInfo implements (like all DB controls) the interface IDBCondition (com.softwareag.cis.server.util). On report execution, you may use the class DBUtil (com.softwareag.cis.server.util) to append the value of property dbselectOptionTitle to the SQL query. See the JavaDoc documentation of class DBSELECTOPTIONInfo and DBUtil for details.

The DBSELECTOPTIONInfo 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 DBSELECTOPTIONInfo object. The interface method getDBConnection is called once - at the first time the DBSELECTOPTIONInfo has to access the database.

There are no updates (insert/update/delete) done with this connection. As the DBSELECTOPTIONInfo does not open the connection, it does not care about closing the connection.

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