com.inet.report
Class TableSource

java.lang.Object
  com.inet.report.TableSource

All Implemented Interfaces:

NodeParser, com.inet.report.ReferenceHolder, java.io.Serializable

public class TableSource
extends java.lang.Object
implements java.io.Serializable, com.inet.report.ReferenceHolder, NodeParser

A TableSource is the represantation of a table, a view, a stored procedure or a SQL command. The table desciption contains of the table name, the alias and the column description. Every datasource will be represented as a single table in the report design. Thereby it is possible to use all tables, stored procedures and sql statements in one report together. The result of that are multiple requests to the database. Then results of the requests will be joined together by i-net Clear Reports.

Example code:

 Add a table to the report
  
 DatabaseTables dbTables = engine.getDatabaseTables();
 Datasource ds = dbTables.createDatasource("pdssql.dll","localhost","","Northwind","sa","MyPassword");
 //adding a TableSource to the Datasource
 TableSource tabOrderDetails = ds.createTableSource("Order Details");
 tabOrderDetails.addColumn("OrderId",Field.NUMBER);
 tabOrderDetails.addColumn("ProductId",Field.NUMBER);
 tabOrderDetails.addColumn("UnitPrice",Field.NUMBER);
 tabOrderDetails.addColumn("Quantity",Field.NUMBER);
 tabOrderDetails.addColumn("OrderId",Field.NUMBER);
 tabOrderDetails.addColumn("Discount",Field.NUMBER);
 //to receive the DatabaseField of column "OrderId call: 
 tabOrderDetails.getDatabaseField(tabOrderDetails.getColumnName(0));
      
 
 
 Add a stored procedure to the report:
 
 TableSource sp_salesByYear = ds.createTableSource("Sales By Year");
 //add the result columns to the stored procedure
 sp_salesByYear.addColumn("ShippedDate", Field.DATETIME);
 sp_salesByYear.addColumn("OrderID", Field.NUMBER);
 sp_salesByYear.addColumn("Subtotal", Field.NUMBER);
 sp_salesByYear.addColumn("Year", Field.STRING);
    
 //define the input parameter of the stored procedure
 String[] inputParamNames =  new String[]{"Beginning_Date","Ending_Date"};
 int[] inputParamTypes =  new int[]{Field.DATETIME,Field.DATETIME};
 sp_salesByYear.setInputParameter(inputParamNames,inputParamTypes);
 
 
 
 Add a sql command to the report:
 
 TableSource cmdOrderDetails = ds.createTableSource("Order Details");
 //Hint: Datasource.createTableSourceCommand(String, String) adds the columns and DatabaseFields automatically.
 cmdOrderDetails.setSql("select * from \"Order Details\" where OrderId = {?OrderId}");
 //add the required PromptField to the report
 cmdOrderDetails.setInputParameter( new String[]{"OrderId"},  new int[]{Field.NUMBER});
 //add the result columns to the sql command
 cmdOrderDetails.addColumn("OrderId",Field.NUMBER);
 cmdOrderDetails.addColumn("ProductId",Field.NUMBER);
 cmdOrderDetails.addColumn("UnitPrice",Field.NUMBER);
 cmdOrderDetails.addColumn("Quantity",Field.NUMBER);
 cmdOrderDetails.addColumn("OrderId",Field.NUMBER);
 cmdOrderDetails.addColumn("Discount",Field.NUMBER);
 
 
 
 
 If the Datasource of the TableSource can create a database Connection, you can use the refresh() to add a table or a stored procedure.
 
      DatabaseTables dbTables = engine.getDatabaseTables();
      Datasource ds = dbTables.createDatasource("pdssql.dll","localhost","","Northwind","sa","MyPassword");
      //adding a TableSource to the Datasource
      TableSource tabOrderDetails = ds.createTableSource("Order Details");
      tabOrderDetails.refresh();//scans the table and adds all columns to TableSource
  
 

Since:

6.0

See Also:

Serialized Form

Field Summary

static int	TYPE_COMMAND Identifies a TableSource as a sql statement.
static java.lang.String	TYPE_NAME_SYSTEM_TABLE The name of system table data source returning from meta data.
static java.lang.String	TYPE_NAME_TABLE The name of table data source returning from meta data.
static java.lang.String	TYPE_NAME_VIEW The name of view data source returning from meta data.
static int	TYPE_PROCEDURE Identifies a TableSource as a stored procedure.
static int	TYPE_TABLE_OR_VIEW Identifies a TableSource as a table or a view.

Method Summary

void	addColumn(java.lang.String columnName, int columnType) Adds a column to the table definition.
void	addColumn(java.lang.String columnName, int columnType, java.lang.String columnLabel) Adds a column with column describtion to the table definition.
void	canRemoveColumn(java.lang.String columnName) Checks the column of this table can be removed from the report.
void	changeAliasReferences(java.lang.String newAlias) Changes the alias name to newAlias.
static void	checkAliasValidity(java.lang.String newAlias) Checks the name for whether it is a valid name for this TableSource.
void	checkExistsOnCurrentDatasource() Sends a test statement to the data source to check if the TableSource is executable.
boolean	equals(java.lang.Object o) Checks whether the given Object is a TableSource object with the same alias name.
java.lang.String	getAlias() Returns the alias name.
int	getColumnCount() Returns the number of columns.
java.lang.String	getColumnName(int idx) Returns the column name of the selected column.
java.lang.String[]	getColumnNames() Returns an array of all column names.
int	getColumnType(int idx) Returns the column type of the selected column.
int[]	getColumnTypes() Returns an array of all column types.
DatabaseField	getDatabaseField(int idx) Returns the corresponding DatabaseField to that column index.
DatabaseField	getDatabaseField(java.lang.String columnName) Returns the corresponding DatabaseField to that column name.
DatabaseField[]	getDatabaseFields() Returns all DatabaseFields that belongs to this TableSource.
java.lang.String	getDatabaseIdentifier() Returns the original name of the table source.
Datasource	getDatasource() Returns the Dataosource object, to which this TableSource belongs.
java.util.List	getInputParameters() Returns the list of PromptFields which are used by this table source as store procedures parameter.
java.sql.ResultSet	getResultSet() Returns the ResultSet of that TableSource.
java.lang.String	getSql() Returns the SQL command if it was set.
java.lang.String	getSqlWithPromptFieldValues() Returns the SQL command if it was set.
int	getType() Returns the type of that TableSource.
boolean	isUsed() Returns the columns of this report are used in the report or this table is used in a join.
void	refresh() Refreshs the columns of the TableSource by requesting meta information from the database.
void	removeColumn(java.lang.String columnName) Removes a specific column.
void	setDatabaseIdentifierName(java.lang.String newDatabaseIdentifier) Sets the identifier name of the TableSource.
void	setInputParameter(java.lang.String[] paramNames, int[] paramTypes) Defines the input parameter for this TableSource.
void	setInputParameter(java.lang.String[] paramNames, int[] paramTypes, java.lang.Object[] values) Defines the input parameter for this TableSource.
void	setInputParameter_TypesOfSQLType(java.lang.String[] paramNames, int[] paramTypes) Defines the input parameter for this TableSource.
void	setInputParameter_TypesOfSQLType(java.lang.String[] paramNames, int[] paramTypes, java.lang.Object[] values) Defines the input parameter for this TableSource.
void	setLocation(java.lang.String location, Datasource newDatasource) Set the location information of the identifier.
void	setSql(java.lang.String newSql) Sets the SQL statement and changes the type of this TableSource to TYPE_COMMAND.
java.lang.String	toString() Returns the alias name.

Methods inherited from interface com.inet.report.parser.NodeParser

isDOMParser, parseDOM, parseElement, parseEndElement, parseText

Field Detail

TYPE_TABLE_OR_VIEW

public static final int TYPE_TABLE_OR_VIEW

Identifies a TableSource as a table or a view.

See Also:

getType(), Constant Field Values

TYPE_PROCEDURE

public static final int TYPE_PROCEDURE

Identifies a TableSource as a stored procedure.

See Also:

getType(), Constant Field Values

TYPE_COMMAND

public static final int TYPE_COMMAND

Identifies a TableSource as a sql statement.

See Also:

getType(), Constant Field Values

TYPE_NAME_TABLE

public static final java.lang.String TYPE_NAME_TABLE

The name of table data source returning from meta data.

See Also:

Constant Field Values

TYPE_NAME_VIEW

public static final java.lang.String TYPE_NAME_VIEW

The name of view data source returning from meta data.

See Also:

Constant Field Values

TYPE_NAME_SYSTEM_TABLE

public static final java.lang.String TYPE_NAME_SYSTEM_TABLE

The name of system table data source returning from meta data.

See Also:

Constant Field Values

Method Detail

getType

public int getType()
            throws java.sql.SQLException,
                   ReportException

Returns the type of that TableSource. Possible are three types. If there is a SQL Statement, the TableSource is from type TYPE_COMMAND. Otherwise it is from type TYPE_PROCEDURE or TYPE_TABLE_OR_VIEW. To find out, which of both it is, the TableSource have to request the DatabaseMetaData. So it is possible to use on one database a view and on an other database instead of the view a stored procedure, if both have same name.

Returns:

The type of this TableSource.

Throws:

ReportException - if there occurs a problem while creating the Connection

java.sql.SQLException - if there occurs a problem while requesting the DatabaseMetaData

See Also:

TYPE_COMMAND, TYPE_PROCEDURE, TYPE_TABLE_OR_VIEW

getResultSet

public java.sql.ResultSet getResultSet()
                                throws ReportException,
                                       java.sql.SQLException

Returns the ResultSet of that TableSource. If the TableSource is from TYPE_COMMAND or from TYPE_PROCEDURE, the Statement will be executed directly and the ResultSet contains all columns.
If the stored procedure needs input parameter, it reads the values from the according prompt fields. If the TableSource is from type TYPE_TABLE_OR_VIEW, only these columns will be requested, that are needed in the report.

Returns:

the ResultSet for that TableSource

Throws:

ReportException - if there occure a problem on create the Connection

java.sql.SQLException - if there occure a problem on execute the Statement.

checkExistsOnCurrentDatasource

public void checkExistsOnCurrentDatasource()
                                    throws ReportException

Sends a test statement to the data source to check if the TableSource is executable.

Throws:

ReportException - If the TableSource is not executable on the data source OR if creating the Connection to the data source failed.

addColumn

public void addColumn(java.lang.String columnName,
                      int columnType)
               throws ReportException

Adds a column to the table definition.

Parameters:

columnName - The column name

columnType - The i-net Clear Reports value type

Throws:

ReportException - when columnType is not valid

See Also:

Field.NUMBER, Field.STRING, Field.TIME, Field.DATETIME, Field.DATE, Field.BOOLEAN, Field.BINARY, Field.CURRENCY

addColumn

public void addColumn(java.lang.String columnName,
                      int columnType,
                      java.lang.String columnLabel)
               throws ReportException

Adds a column with column describtion to the table definition. The label is an additional information only that can be used to explain abstract column names. This label will be used from i-net Designer for example. It don't influence the SQL generation.

Parameters:

columnName - The column name

columnType - The i-net Clear Reports value type

columnLabel - additional information that can be used to explain abstract column names

Throws:

ReportException - when columnType is not valid

See Also:

Field.NUMBER, Field.STRING, Field.TIME, Field.DATETIME, Field.DATE, Field.BOOLEAN, Field.BINARY, Field.CURRENCY, DatabaseField.getColumnLabel(), DatabaseField.setColumnLabel(String), DatabaseField.hasColumnLabel()

getDatabaseField

public DatabaseField getDatabaseField(int idx)

Returns the corresponding DatabaseField to that column index.

Parameters:

idx - A 0-based index of DatabaseField.

Returns:

The corresponding DatabaseField to that column index.

Since:

9.0

See Also:

addColumn(String, int), getColumnNames(), getColumnCount()

getDatabaseField

public DatabaseField getDatabaseField(java.lang.String columnName)

Returns the corresponding DatabaseField to that column name.

Parameters:

columnName - A column of this TableSource.

Returns:

The corresponding DatabaseField to that column name or null if column is not defined.

Since:

6.1

See Also:

addColumn(String, int), getColumnNames()

getDatabaseFields

public DatabaseField[] getDatabaseFields()

Returns all DatabaseFields that belongs to this TableSource.

Returns:

All DatabaseFields that belongs to this TableSource.

Since:

6.1

See Also:

addColumn(String, int)

getColumnName

public java.lang.String getColumnName(int idx)

Returns the column name of the selected column.

Parameters:

idx - The 0-based index of a column name.

Returns:

The column name of the selected column.

See Also:

getColumnCount(), addColumn(String, int), getColumnType(int)

getColumnNames

public java.lang.String[] getColumnNames()

Returns an array of all column names.

Returns:

An array of all column names.

getColumnTypes

public int[] getColumnTypes()

Returns an array of all column types.

Returns:

An array of all column types.

getColumnType

public int getColumnType(int idx)
                  throws java.lang.IndexOutOfBoundsException

Returns the column type of the selected column.

Parameters:

idx - index of the column

Returns:

The column type of the selected column.

Throws:

java.lang.IndexOutOfBoundsException - - if index is out of range (index < 0 || index >= getColumnCount())

See Also:

getColumnCount(), addColumn(String, int), getColumnName(int)

removeColumn

public void removeColumn(java.lang.String columnName)
                  throws ReportException

Removes a specific column.
If the column is only used as join condition, the join condition will be removed.

Parameters:

columnName - Column name to be removed.

Throws:

ReportException - If the column is used in the report.

Since:

6.0

See Also:

getColumnNames()

canRemoveColumn

public void canRemoveColumn(java.lang.String columnName)
                     throws ReportException

Checks the column of this table can be removed from the report. Throws a exception if the column is used.

Parameters:

columnName - the short column name

Throws:

ReportException - if the column is used and cannot be removed

Since:

10.0

checkAliasValidity

public static void checkAliasValidity(java.lang.String newAlias)
                               throws ReportException

Checks the name for whether it is a valid name for this TableSource. Aliases may not be empty or null, must start with a letter or a square bracket, and must contain only letters, digits, or one of the following characters: _;-$] They also may not be any SQL keywords. If the name is invalid, this method will throw a ReportException.

Parameters:

newAlias - alias name to check

Throws:

ReportException - if the name is not valid

Since:

11.1

changeAliasReferences

public void changeAliasReferences(java.lang.String newAlias)
                           throws ReportException

Changes the alias name to newAlias. This also updates all references to this source in all fields and joins.

Parameters:

newAlias - Alias to be set

Throws:

ReportException - If there is no such data source, or if there is a different problem

setDatabaseIdentifierName

public void setDatabaseIdentifierName(java.lang.String newDatabaseIdentifier)
                               throws ReportException

Sets the identifier name of the TableSource. This is useful if the table name was changed in the database.

Parameters:

newDatabaseIdentifier - The new identifer name of the TableSource.

Throws:

ReportException - If the identifier name is empty.

getColumnCount

public int getColumnCount()

Returns the number of columns.

Returns:

the number of columns.

getAlias

public java.lang.String getAlias()

Returns the alias name. The name is a unique identifier of this tablesource.

Returns:

the alias name.

getDatabaseIdentifier

public java.lang.String getDatabaseIdentifier()

Returns the original name of the table source.

Returns:

the original name of the table source.

setLocation

public void setLocation(java.lang.String location,
                        Datasource newDatasource)

Set the location information of the identifier. This is useful if the table was moved into another schema, catalog or package.

 Example 1:
 
 //old identifier name = "SCOTT.MYTABLE"
 tableSource.setLocation("OTHERSCHEMA.MYPACKAGE", anOtherDatasource);
 //the new identifier name = "OTHERSCHEMA.MYPACKAGE.MYTABLE"
 
 
 Example 2:
 
 //old identifier name = "SCOTT.MYTABLE"
 tableSource.setLocation(null,null);
 //the new identifier name = "MYTABLE"
 
 

Parameters:

location - The location information of the identifier. Passing null will not change the location. Set empty String to remove the location information.

newDatasource - Set an other Datasource if the TableSource has to move from one Datasource to the other. Passing null will not move the TableSource.

Since:

6.0

toString

public java.lang.String toString()

Returns the alias name.

Overrides:

toString in class java.lang.Object

Returns:

the alias name.

See Also:

getAlias()

equals

public boolean equals(java.lang.Object o)

Checks whether the given Object is a TableSource object with the same alias name.

Overrides:

equals in class java.lang.Object

Parameters:

o - Object to be checked

Returns:

true if the given Object is a TableSource object with the same alias name.

getSql

public java.lang.String getSql()

Returns the SQL command if it was set.

Returns:

the SQL command if it was set.

See Also:

setSql(String)

getSqlWithPromptFieldValues

public java.lang.String getSqlWithPromptFieldValues()
                                             throws ReportException

Returns the SQL command if it was set. Placeholder for PromptFields are replaced by their value.

Returns:

the SQL command if it was set. Placeholder for PromptFields are replaced by their value.

Throws:

ReportException - if sql command contains invalid placeholder.

See Also:

setSql(String)

setSql

public void setSql(java.lang.String newSql)

Sets the SQL statement and changes the type of this TableSource to TYPE_COMMAND.

Note: This method does not check if the sql statement is correct or which columns it will receive.
You also have to add the columns of the statement manually. Add the columns in the same order like column order of the ResultSet.
The columns of the ResultSet of this sql statement have to be added to this TableSource in the same direction.
If you want to use the columns in the report, you also have to register them as DatabaseFields.
The easiest way to add a TableSource from TYPE_COMMAND is to use the Datasource.createTableSourceCommand(String,String) method.

Note: If report contains SortFields and GroupFields, the sort order given by statement may be changed.

Parameters:

newSql - SQL Statement that will be used for this TableSource

See Also:

addColumn(String, int), Datasource.createTableSourceCommand(String, String), ReportProperties.setIgnoreFiltering(boolean), ReportProperties.setIgnoreSorting(boolean)

getDatasource

public Datasource getDatasource()

Returns the Dataosource object, to which this TableSource belongs.

Returns:

the Dataosource object, to which this TableSource belongs.

setInputParameter

public void setInputParameter(java.lang.String[] paramNames,
                              int[] paramTypes)
                       throws ReportException

Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedured or views with parameter. SQL commands also can contain placeholder for promptvalues.

Parameters:

paramNames - The input parameter names.

paramTypes - The types of the input parameter. The type is a constant of class com.inet.report.Field.

Throws:

ReportException - If type of parameter is unknown.

Since:

6.0

See Also:

Field.NUMBER, Field.STRING, Field.TIME, Field.DATETIME, Field.BOOLEAN, Field.BINARY

setInputParameter

public void setInputParameter(java.lang.String[] paramNames,
                              int[] paramTypes,
                              java.lang.Object[] values)
                       throws ReportException

Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedured or views with parameter. Sql commands also can contain placeholder for promptvalues.

This method implicitely creates PromptFields and set the values.
Code sample for a simple stored procedure:

    String[] names = new String(){"costumerId","endDate"};
    int[] types = new int(){Field.STRING,Field.DATE};
    Object values = new Object("ALFKI",new Date(1999,11,11){};
    //mySP has two input parameter and retunes a result set
    TableSource ts = datasource.createTableSource("mySP");
    //creates PromptFields for this procedure and set values
    ts.setInputParameter_TypesOfSQLType(names,types,values);
    //executes the SP to scan returned columns. The parameter for execution were set before.
    ts.refresh(); 
 

Parameters:

paramNames - The input parameter names.

paramTypes - paramTypes The types of the input parameter. The type is a constant of class com.inet.report.Field.

values - values The values for set parameter.

Throws:

ReportException - If type of parameter is unknown.

getInputParameters

public java.util.List getInputParameters()
                                  throws ReportException

Returns the list of PromptFields which are used by this table source as store procedures parameter.

Returns:

the list (not null)

Throws:

ReportException - will be thrown if the engine was invalid or finished

Since:

10.1

setInputParameter_TypesOfSQLType

public void setInputParameter_TypesOfSQLType(java.lang.String[] paramNames,
                                             int[] paramTypes)
                                      throws ReportException

Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedured or views with parameter. Sql commands also can contain placeholder for parameter values.
This method implicitely creates PromptFields and set the values.
Code sample for simple stored procedure:

    String[] names = new String(){"costumerId","endDate"};
    int[] types = new int(){Types.VARCHAR,Types.DATE};
    //mySP has two input parameter and retunes a result set
    TableSource ts = datasource.createTableSource("mySP");
    //creates PromptFields for this procedure.
    ts.setInputParameter_TypesOfSQLType(names,types,values);
    //executes the SP to scan returned columns. The parameter for execution are sample values.
    ts.refresh();
 

Parameters:

paramNames - The input parameter names.

paramTypes - The types of the input parameter. The type is a constant of class java.sql.Types.

Throws:

ReportException - if mapping from SQL type to Field type failed. If paramNames.length is different to paramTypes.length.

setInputParameter_TypesOfSQLType

public void setInputParameter_TypesOfSQLType(java.lang.String[] paramNames,
                                             int[] paramTypes,
                                             java.lang.Object[] values)
                                      throws ReportException

Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedured or views with parameter. Sql commands also can contain placeholder for parameter values.
This method implicitely creates PromptFields and set the values.
Code sample for simple stored procedure:

    String[] names = new String(){"costumerId","endDate"};
    int[] types = new int(){Types.VARCHAR,Types.DATE};
    Object values = new Object("ALFKI",new Date(1999,11,11){};
    //mySP has two input parameter and returns a result set
    TableSource ts = datasource.createTableSource("mySP");
    //creates PromptFields for this procedure and set values
    ts.setInputParameter_TypesOfSQLType(names,types,values);
    //executes the SP to scan returned columns. The parameter for execution were set before.
    ts.refresh();
 

Code sample for a stored procedure with out cursor:

    String[] names = new String(){"outcursor";"costumerId","endDate"};
    int[] types = new int(){-10,Types.VARCHAR,Types.DATE};//-10 is out cursor , it is the standard value returned from oracle JDBC drivers
    Object values = new Object("ALFKI",new Date(1999,11,11){};//no value for out cursor or other out values
    
    
    //mySP2 has two input parameter and retunes a result via out cursor
    TableSource ts = datasource.createTableSource("mySP2");
    //creates PromptFields for this procedure and set values
    ts.setInputParameter_TypesOfSQLType(names,types,values);
    //executes the SP to scan returned columns. The parameter for execution were set before.
    ts.refresh();
 

Parameters:

paramNames - The input parameter names.

paramTypes - The types of the input parameter. The type is a constant of class java.sql.Types. The values -10(type oracle cursor) and 1111(type other) will be ignored.

values - The values for set parameter. Do not set values for out parameter or cursor parameter.

Throws:

ReportException - if mapping from SQL type to Field type failed. If paramNames.length is different to paramTypes.length.

refresh

public void refresh()
             throws ReportException,
                    java.sql.SQLException

Refreshs the columns of the TableSource by requesting meta information from the database.
If the TableSource is of type TYPE_TABLE_OR_VIEW, the following query will send to database to find out the columns of the table:
"select * from "+this.getDatabaseIdentifier()+" where 1=0"
In a similar way the columns of TYPE_COMMAND will requested. An algorithm includes a "1=0" filter clause in the statement.
If the TableSource is of type TYPE_PROCEDURE, the result of the sp will requested.
The parameter of the stored procedure are used from the refering PromptFields.
If the signature of the stored procedure has changed with additional input parameter, this methods adds new PromptFields to the report.
If an input parameter was removed from stored procedure signature, the refering PromptField will removed from report, but if the PromptField is used another where in the report.

Throws:

ReportException - if a DatabaseField is used in report but the refering column does not exists on database.

java.sql.SQLException - if connecting to database failed or if requesting meta information failed.

Since:

6.5

isUsed

public boolean isUsed()
               throws ReportException

Returns the columns of this report are used in the report or this table is used in a join.

Returns:

the flag

Throws:

ReportException - if engine is finished or no report is set.

Since:

11.0