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

The DBQUERY control is designed to significantly reduce the effort for developing queries against relational databases. In the control definition, you specify the SELECT string, the filter criteria and the output columns - the rest is done automatically. The sequence, sorting and grouping of the output grid can be defined in a very flexible way. You even can store so-called variants: if you have certain filter criteria you always want to use in a query, then you can store them under a name in order to have quick access to often-used queries. The following typical aspects of a query are covered:

• Filter criteria (ad hoc input, saved in query variants).

• Result output (server-side scrolling, context menu).

• Report variants (column visibility and column order, sorting, grouping, etc.).

• (Default) PDF generation.

This document covers the following topics:

• Example

• DBQUERY Properties

• DBFILTER Properties

• DBCOLUMN Properties

• DBPARAMSINGLEVALUE Properties

• DBPARAMDOUBLEVALUE Properties

• Variant Management

• PDF Generation

Example

There is a DBQUERY control with its inner components. Look at the corresponding layout definition:

<dbquery valueprop="dBQueryInfo" query="SELECT * FROM ADDRESS AS A, BUSINESSPARTNER AS B 
         WHERE A.BUSINESSPARTNERID = B.ID" datasource="addressdb" title="Adress Report" 
         rowareaname="Report Demo" executebuttonname="Execute" maxrows="200" 
         image="images/addresses.gif" height="100%" rowcount="40" titletext="Address Report">
    <dbfilter labelname="Title" labelwidth="150" querycolumn="title" valuehelptable="title"
              valuehelpcolumn="id" fieldwidth="200" hideout="true">
    </dbfilter>
    <dbfilter labelname="First Name" labelwidth="150" querycolumn="firstname" fieldwidth="200">
    </dbfilter>
    <dbfilter labelname="Lat Name" labelwidth="150" querycolumn="lastname" fieldwidth="200">
    </dbfilter>
    <dbcolumn name="Title" column="TITLE" width="50" widthpdf="2cm" groupby="true">
    </dbcolumn>
    <dbcolumn name="Last Name" column="LASTNAME" width="100" widthpdf="3cm" sortorder="1" 
              sortascending="true" groupby="true">
    </dbcolumn>
    <dbcolumn name="First Name" column="FIRSTNAME" width="100" widthpdf="3cm" sortorder="2"
              sortascending="true">
    </dbcolumn>
    <dbcolumn name="Street" column="STREET" width="150" widthpdf="5cm">
    </dbcolumn>
    <dbcolumn name="Country" column="COUNTRY" width="50" widthpdf="2cm">
    </dbcolumn>
    <dbcolumn name="State" column="STATE" width="100%" widthpdf="5cm">
    </dbcolumn>
</dbquery>

Look at the following items:

• There is a DBQUERY definition with the property valueprop "dBQueryInfo". The property query contains the SELECT string.

• There are DBFILTER definitions for each filter criterion.

• There are DBCOLUMN definitions for each grid column of the result area.

The adapter code is the following:

// This class is a generated one.

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

import com.softwareag.cis.server.Adapter;
import com.softwareag.cis.server.IDynamicAccess;
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.DelegateError;
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;
import com.softwareag.cis.server.util.ValidValueLine;

public class DBQUERYAdapter
    extends Adapter
    implements IDBDemoAdapter
{
    // ------------------------------------------------------------------------
    // 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);
           }
       }
    }
    /** class used for SQL optimization. */
    public class SQLOptimizer
    implements IDBQUERYOptimizer
    {
        public String optimizeQuery(String query, String[] gridColumns)
        {
            // do checks + optimization here
            return query;
        }
    }
    /** class used for pop-up menu. */
    public class MyMenuNodeInfo
    extends MENUNODEInfo
    {
        IDynamicAccess m_row; // = row for that the context menu is requested
        public MyMenuNodeInfo(IDynamicAccess row, String text, String image)
        {
            super(text, image);
            m_row = row;
        }
        public void reactOnSelect()
        {
            outputMessage("S", getText() + " selected");
        }
    }
    /** class used to create the context menu within the result grid. */
    public class ResultAreaContextMenu
    implements IDBQUERYContextMenuRequestListener
    {
        public void reactOnContextMenuRequestFor(IDynamicAccess row)
        {
            // opens a pop-up menu with two entries
           TREECollection cm = new TREECollection();
           cm.addTopNode(new MyMenuNodeInfo(row, "Edit", "images/edit.gif"),true);
           cm.addTopNode(new MyMenuNodeInfo(row,"Remove", "images/remove.gif"),true);
           showPopupMenu(cm);
        }
    }
    /** class used for PDF conversion. */
    public class GeneratePDFRequestListener
    implements IDBQUERYGeneratePDFRequestListener
    {
        public void generatePDFAndDisplayDocument(DBQUERYDataObject dataObject)
        {
        // create PDF document here
        }
    }

       DBQUERYInfo m_dBQueryInfo = new DBQUERYInfo(this,new ConnectionProvider(),
                                   new ResultAreaContextMenu(),
                                   new SQLOptimizer(),
                                   new GeneratePDFRequestListener());
       public DBQUERYInfo getDBQueryInfo() { return m_dBQueryInfo; }
}

Programming the DBQUERY is quite simple. Define an instance of the class DBQUERYInfo. This instance is referenced by the valueprop definition inside the DBQUERY tag. The DBQUERYInfo offers a set of constructors.

Mandatory Parameters

Same to all constructors are the (two) mandatory parameters. First you have to pass the adapter object that defines the DBQUERYInfo instance. This object is used, for example, to open a pop-up inside the DBQUERY control. With the second, you have to pass an implementation of interface IDBQUERYConnectionProvider. As the DBQUERYInfo class does not open a database connection on its own, it uses this object to obtain a connection. The connection is only used to read data from the database. There are no updates (insert/update/delete) done with this connection. Internally, the provided connection is buffered and used each time the query is executed. As the DBQUERYInfo does not open the connection, it does not care about closing the connection.

Optional Parameters

The constructors vary in the list of their optional parameters. By construction, you may pass an implementation of interface IDBQUERYContextMenuRequestListener. This object is called (with method reactOnContextMenuRequestFor) if the user clicks into a line inside of the report result grid with right mouse button. See the Javadoc documentation of interface IDBQUERYContextMenuRequestListener for details.

If you want to check/optimize the SQL statement prior its execution, you may pass an implementation of interface IDBQUERYOptimizer. The object is called with method optimizeQuery each time the report is executed. See the Javadoc documentation of interface IDBQUERYOptimizer for details.

If you do not want to use the default PDF conversion of the DBQUERY control, you may pass an implementation of interface IDBQUERYGeneratePDFRequestListener. This object is called (with method generateAndDisplayPDF) if the user clicks the "PDF" icon within the DBQUERY control. See the Javadoc documentation of interface IDBQUERYGeneratePDFRequestListener for details.

DBQUERY Properties

DBFILTER Properties

The DBFILTER tag is the typical tag that is placed inside a DBQUERY definition. The DBFILTER defines one filter criterion with its binding to a table column (property querycolumn). The parameter values are added dynamically to the WHERE clause of the SQL query prior to report execution. The SQL statement is defined within the query property of the DBQUERY control.

DBCOLUMN Properties

The DBCOLUMN tag is the typical tag that is placed inside a DBQUERY definition. The DBCOLUMN definition defines one column within the result grid. It is bound to a name (column name or column alias) in the result set of the report. This result set is defined by the query property inside the DBQUERY definition.

DBPARAMSINGLEVALUE Properties

The DBPARAMSINGLEVALUE tag is the typical tag that is placed inside a DBQUERY definition. The DBPARAMSINGLEVALUE defines a filter criterion. In contrast to the DBFILTER tag, it is not visualized within the DBQUERY control and is therefore not accessible/changeable by the user.

Example: a table "ADDRESS" may have a column "COUNTRY". You want to restrict the user to see German addresses only. In this case, use a DBPARAMSINGLEVALUE with the property querycolumn set to "COUNTRY", operator set to "=" (equal), and value set to "DE".

DBPARAMDOUBLEVALUE Properties

The DBPARAMDOUBLEVALUE tag is very similar to DBPARAMSINGLEVALUE. The only difference is that you can specify operators that work on two operands (e.g. "is between").

Variant Management

The DBQUERY control provides for so-called query variants. Within a variant, you can save e.g. an often-used filter criteria combination. If you do not want to use variants at all, you just set the property personalizable to false.

Persistence

The variant data is stored in the local file system.

Key

The key of a report variant is consists of:

• the name of the web application,

• the name of the project that contains the HTML page,

• the name of the HTML page that contains the DBQUERY control,

• the actual user logged on (read from the session context on save),

• the name of the variant (specified in the save pop-up).

As the name of the page (that embeds the DBQUERY control) is part of the variant key, you can have multiple pages within one Application Designer project - each with its own variant set. But: as a consequence, variants do not work inside the Layout Painter. Reason: if you preview the XML layout definition, the editor first removes the temporary HTML page (name starts with "ZZZZZZZZ") of a former preview. In a second step, it creates a new page - with a different name (the name contains a timestamp). This means: inside the Layout Painter, you never view the same HTML page twice. If you work with the published HTML page, variants will work properly.

Create/Change/Remove

To create a variant, you just save your current input (filter criteria or settings inside the variant properties pop-up). To save a variant, choose the save icon to the right of the result grid. To change an existing variant, open the variant (with input field to the right of the report title label), apply your changes (either by changing the filter criteria or within the variant properties pop-up - icon above the save icon) and save them. To remove a variant, choose the remove icon (icon below save icon), select one or more variants and choose ok.

PDF Generation

The DBQUERY control provides for a generation of a PDF document. The generation is invoked when choosing the "PDF" icon to the right of the result grid. The document is displayed inside a pop-up. It contains the following data:

• Report title (header line).

• Timestamp of the reports execution (footer).

• Table with used filter criteria (body).

• Table with result of the report (body).

How to do the PDF generation on your own?

The DBQUERYInfo class provides for a constructor where you can pass an implementation of interface IDBQUERYGeneratePDFRequestListener. This implementation is called with the method generatePDFAndDisplayDocument if the "PDF" icon is chosen. The current data of the DBQUERY is passed within that call. For details, see the JavaDoc documentation of interface IDBQUERYGeneratePDFRequestListener and class DBQUERYInfo (both from package com.softwareag.cis.server.util).

www.softwareag.com

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

SEARCH   CONTENTS   |   PDF BOOKS   |   HOME   UP   PREV   NEXT