Package com.inet.report

Class TableSource

java.lang.Object
com.inet.report.TableSource
All Implemented Interfaces:
NodeParser, com.inet.report.ReferenceHolder, Serializable
public class TableSource extends Object implements Serializable, com.inet.report.ReferenceHolder, NodeParser
A TableSource is the representation of a table, a view, a stored procedure or a SQL command. The table description 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:

  • Field Details

    • TYPE_TABLE_OR_VIEW

      public static final int TYPE_TABLE_OR_VIEW
      Identifies a TableSource as a table or a view.
      See Also:

    • TYPE_PROCEDURE

      public static final int TYPE_PROCEDURE
      Identifies a TableSource as a stored procedure.
      See Also:

    • TYPE_COMMAND

      public static final int TYPE_COMMAND
      Identifies a TableSource as a sql statement.
      See Also:

    • TYPE_NAME_TABLE

      public static final String TYPE_NAME_TABLE
      The name of table data source returning from meta data.
      See Also:

    • TYPE_NAME_VIEW

      public static final String TYPE_NAME_VIEW
      The name of view data source returning from meta data.
      See Also:

    • TYPE_NAME_SYSTEM_TABLE

      public static final String TYPE_NAME_SYSTEM_TABLE
      The name of system table data source returning from meta data.
      See Also:

  • Method Details

    • getType

      public int getType() throws 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
      Since:
      6.0
      See Also:

    • getResultSet

      @Nonnull public ResultSet getResultSet() throws ReportException, 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 occur a problem on create the Connection
      SQLException - if there occur a problem on execute the Statement.
      Since:
      6.0

    • 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.
      Since:
      6.0

    • addColumn

      public void addColumn(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
      Since:
      6.0
      See Also:

    • addColumn

      public void addColumn(String columnName, int columnType, String columnLabel) throws ReportException
      Adds a column with column description 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
      Since:
      7.0
      See Also:

    • 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:

    • getDatabaseField

      public DatabaseField getDatabaseField(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:

    • 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:

    • getColumnName

      public 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.
      Since:
      6.0
      See Also:

    • getColumnNames

      public String[] getColumnNames()
      Returns an array of all column names.
      Returns:
      An array of all column names.
      Since:
      6.0

    • getColumnTypes

      public int[] getColumnTypes()
      Returns an array of all column types.
      Returns:
      An array of all column types.
      Since:
      6.0

    • getColumnType

      public int getColumnType(int idx) throws IndexOutOfBoundsException
      Returns the column type of the selected column.
      Parameters:
      idx - index of the column
      Returns:
      The column type of the selected column.
      Throws:
      IndexOutOfBoundsException - - if index is out of range (index < 0 || index >= getColumnCount())
      Since:
      6.0
      See Also:

    • removeColumn

      public void removeColumn(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:

    • canRemoveColumn

      public void canRemoveColumn(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(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(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
      Since:
      6.0

    • setDatabaseIdentifierName

      public void setDatabaseIdentifierName(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 identifier name of the TableSource.
      Throws:
      ReportException - If the identifier name is empty.
      Since:
      6.0

    • getColumnCount

      public int getColumnCount()
      Returns the number of columns.
      Returns:
      the number of columns.
      Since:
      6.0

    • getAlias

      public String getAlias()
      Returns the alias name. The name is a unique identifier of this tablesource.
      Returns:
      the alias name.
      Since:
      6.0
      See Also:

    • getDatabaseIdentifier

      public String getDatabaseIdentifier()
      Returns the original name of the table source.
      Returns:
      the original name of the table source.
      Since:
      6.0
      See Also:

    • setLocation

      public void setLocation(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 String toString()
      Returns the alias name.
      Overrides:
      toString in class Object
      Returns:
      the alias name.
      Since:
      6.0
      See Also:

    • equals

      public boolean equals(Object o)
      Checks whether the given Object is a TableSource object with the same alias name.
      Overrides:
      equals in class Object
      Parameters:
      o - Object to be checked
      Returns:
      true if the given Object is a TableSource object with the same alias name.
      Since:
      6.0

    • getSql

      public String getSql()
      Returns the SQL command if it was set.
      Returns:
      the SQL command if it was set.
      Since:
      6.0
      See Also:

    • getSqlWithPromptFieldValues

      public 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.
      Since:
      6.0
      See Also:

    • setSql

      public void setSql(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
      Since:
      6.0
      See Also:

    • setQuoteStringPrompts

      public void setQuoteStringPrompts(boolean value)
      Set if a string prompts in a SQL statement should be quoted or not. The default is true. If you disable the quoting then SQL injection is possible.
      Parameters:
      value - the new value
      Since:
      11.2
      See Also:

    • getQuoteStringPrompts

      public boolean getQuoteStringPrompts()
      Get the value if a string prompts in a SQL statement should be quoted or not.
      Returns:
      current value
      Since:
      11.2

    • getDatasource

      public Datasource getDatasource()
      Returns the Datasource object, to which this TableSource belongs.
      Returns:
      the Datasource object, to which this TableSource belongs.
      Since:
      6.0

    • setInputParameter

      public void setInputParameter(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:

    • setInputParameter

      public void setInputParameter(String[] paramNames, int[] paramTypes, 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 procedures or views with parameter. SQL commands also can contain placeholder for prompt values.

      This method implicitly 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.
      Since:
      6.0

    • getInputParameters

      public List<PromptField> 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

      @Deprecated public void setInputParameter_TypesOfSQLType(String[] paramNames, int[] paramTypes) throws ReportException
      Deprecated.
      As of i-net Clear Reports 15, instead use setInputParameter(String[], int[])
      Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedures or views with parameter. SQL commands also can contain placeholder for parameter values.
      This method implicitly 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.
      Since:
      6.0

    • setInputParameter_TypesOfSQLType

      @Deprecated public void setInputParameter_TypesOfSQLType(String[] paramNames, int[] paramTypes, Object[] values) throws ReportException
      Deprecated.
      As of i-net Clear Reports 15, instead use setInputParameter(String[], int[], Object[])
      Defines the input parameter for this TableSource. It creates PromptFields with a link to this TableSource. Input parameter are necessary for stored procedures or views with parameter. SQL commands also can contain placeholder for parameter values.
      This method implicitly 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 returns 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.
      Since:
      6.0

    • addReferencedObject

      public final void addReferencedObject(com.inet.report.ReferencedObject reference)
      FOR INTERNAL USE ONLY
      Specified by:
      addReferencedObject in interface com.inet.report.ReferenceHolder
      Since:
      6.0

    • getReferencedObjects

      public final com.inet.report.ReferencedObject[] getReferencedObjects()
      FOR INTERNAL USE ONLY
      Specified by:
      getReferencedObjects in interface com.inet.report.ReferenceHolder

    • getRealReferencedObjectCount

      public final int getRealReferencedObjectCount()
      FOR INTERNAL USE ONLY
      Specified by:
      getRealReferencedObjectCount in interface com.inet.report.ReferenceHolder
      Since:
      6.0

    • getReferencedObjectCount

      public final int getReferencedObjectCount()
      FOR INTERNAL USE ONLY
      Specified by:
      getReferencedObjectCount in interface com.inet.report.ReferenceHolder

    • removeReferencedObject

      public final void removeReferencedObject(com.inet.report.ReferencedObject reference)
      FOR INTERNAL USE ONLY
      Specified by:
      removeReferencedObject in interface com.inet.report.ReferenceHolder
      Since:
      6.0

    • setReferences

      public final void setReferences()
      FOR INTERNAL USE ONLY
      Specified by:
      setReferences in interface com.inet.report.ReferenceHolder

    • resetReferences

      public final void resetReferences()
      FOR INTERNAL USE ONLY
      Specified by:
      resetReferences in interface com.inet.report.ReferenceHolder

    • refresh

      public void refresh() throws ReportException, 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.
      SQLException - if connecting to database failed or if requesting meta information failed.
      Since:
      6.5

    • isDOMParser

      public boolean isDOMParser()
      FOR INTERNAL USE ONLY Internal method for reading report XML

      Returns whether this node is to be read via a DOM parser.

      Specified by:
      isDOMParser in interface NodeParser
      Returns:
      true if this node is to be read via a DOM parser, false otherwise.

    • parseDOM

      public void parseDOM(Node node, Map<String,Object> parserMap) throws FatalParserException
      FOR INTERNAL USE ONLY Internal method for reading report XML

      Parses the node.

      Specified by:
      parseDOM in interface NodeParser
      Parameters:
      node - the node
      parserMap - The map of current Parser.
      Throws:
      FatalParserException - if an exception occurs which causes the report to not be able to be read: causes the abortion of the reading of the report.

    • parseElement

      public NodeParser parseElement(com.inet.report.parser.XMLTag group, String tag, Attributes atts, Map<String,Object> parserMap) throws FatalParserException
      FOR INTERNAL USE ONLY Internal method for reading report XML

      Parses an XML node with the given information, and returns either a sub-element which was created as a result, or null if no sub-element was created, i.e. the information was applied to the ReportComponent itself. Note that the parsing is highly tolerant, i.e. exceptions are intercepted and suppressed if at all possible.

      Specified by:
      parseElement in interface NodeParser
      Parameters:
      group - XMLTag of the current node to be parsed, or null if there is no such current group. An XMLTag is a group of nodes bundled together, usually it is a Properties node such as CommonProperties, BorderProperties, etc.
      tag - The XMLTag to be parsed
      atts - The set of attributes in the current XMLTag
      parserMap - The map of current Parser.
      Returns:
      The NodeParser sub-element if one needed to be created, or null if none was created.
      Throws:
      FatalParserException - if an exception occurs which causes the report to not be able to be read: causes the abortion of the reading of the report.

    • parseEndElement

      public void parseEndElement(com.inet.report.parser.XMLTag group, String tag, Map<String,Object> parserMap) throws FatalParserException
      FOR INTERNAL USE ONLY Internal method for reading report XML

      Receive notification of the end of an XML tag.

      Specified by:
      parseEndElement in interface NodeParser
      Parameters:
      group - XMLTag of the current node to be parsed, or null if there is no such current group.
      tag - The XMLTag to be parsed
      parserMap - The map of current Parser.
      Throws:
      FatalParserException - if an exception occurs which causes the report to not be able to be read: causes the abortion of the reading of the report.

    • parseText

      public void parseText(String text, Map<String,Object> parserMap)
      FOR INTERNAL USE ONLY Internal method for reading report XML

      This method is called if text was encountered in the context of this node. (Examples would be a formula's text or a text element's text)

      Specified by:
      parseText in interface NodeParser
      Parameters:
      text - text encountered and to be stored
      parserMap - The map of current Parser.

    • 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