Version 8.2 (2013-03-18)
 —  Special Development Topics  —

DBSELECTOPTION

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

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".

graphics/image238.png

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

graphics/image239.png

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".

graphics/image240.png

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.

Top of page

Properties

Basic
querycolumn

Name of the column in the query to that the filter criteria belongs to. This column may differ from the value help table/column (properties VALUEHELPTABLE, VALUEHELPCOLUMN). This name is used to build a SQL string in method "toSQLString".

Obligatory  
datasource

Logical identifier of the data source to use. This name is passed to the connection provider in method "IDBQUERYConnectionProvider.getDBConnection".

Obligatory  
valueprop

Property that returns a DBSELECTOPTIONInfo -instance. This instance provides for the value help read from database as well as for a convenient way to append the filter value to query string.

Obligatory  
valuehelptable

Name of the table from where the valid values of the DBSELECTOPTION control are stored.

Optional  
valuehelpcolumn

Name of the column from where the valid values of the DBSELECTOPTION control are stored.

Optional  
valuehelpcolumndescr

Name of a column where an additional description of the valid values is stored. The column must be inside the "value help table" (property VALUEHELPTABLE).

Optional  
width

Width of DBFIELD in pixels or as percentage value.

Optional  
length

Width of DBFIELD in amount of characters. WIDTH and LENGTH should not be used together.

Optional  
datatype

The DBSELECTOPTION control manages multiple "operator-value"-pairs.

By default, each is managed as string. By the DATATYPE property, force the type of the data that is represented. As a consequence the DBFIELD controls inside the "operator-value"-popup is checking the data during input (e.g. if the DATATYPE is "int", it is not allowed to enter alphabetic characters) and adds a logic to transfer the data into various output formats (e.g. if the DATATYPE is "date", the date is formatted into the right date format). In addition it displays a standard "value help" popup dialog for some data types (e.g. if the DATATYPE is "date" then automatically a date input dialog pops up if invoking "value help").

Optional

int

float

date

flush

Flushing behaviour, please view "Common Rules" for details

Optional

screen

server

displayonly

If set to "true", the DBFIELD will not be accessible for input. It is just used as an output field.

Optional

true

false

align

Explicit Alignment

Optional  
valign

Explicit Alignment

Optional  
colspan

Number of columns occupied by this control.

Optional  
rowspan

Number of rows occupied by this control.

Optional  
fieldstyle

Explicit style information passed to the DBFIELD.

Example: if you want the text inside the DBFIELD to be right aligned, define "text-align: right".

Optional  
helpid

Identifier that is used for building the URL of the online help page. Please refer to "Online Help Management" for details.

Optional  
comment

Comment without any effect on rendering and behaviour. The comment is shown in the layout editor's tree view.

Optional  

Top of page