DatabaseTables (i-net Clear Reports)

java.lang.Object
- com.inet.report.DatabaseTables

All Implemented Interfaces:

java.io.Serializable
```
public class DatabaseTables
extends java.lang.Object
implements java.io.Serializable
```
The class DatabaseTables is the starting point if you want to configure everything has to do with databases, tables, connections, table joins and so on. The DatabaseTables contains a list of Datasource objects and a list of Join objects. A Datasource handles the connection to the database and it contains the table, stored procedures and sql commands that are used in the report. A Join is for linking tables, stored procedures and sql commands with each other.
The following example shows how to define a Datasource for a database. To create the Datasource a Data Source Configuration is used. You can define such a Data Source Configurations with the Data Source Manager in i-net Designer. Also, you can define it via API DataSourceConfigurationManager.createDataSourceConfiguration(String, int).
(eng is your reference to a new Engine instance.)

DatabaseTables dbTables = eng.getDatabaseTables(); Datasource dsToNortwind = dbTables.createDatasource("myNorthwindDataSourceConfiguration");
Now you can define a table that is required for the report:
TableSource tsOrders = dsNortwind.createTableSource("Orders"); tsOrders.addColumn("OrderID",Field.NUMBER); tsOrders.addColumn("CustomerID",Field.STRING); tsOrders.addColumn("EmployeeID",Field.NUMBER); tsOrders.addColumn("OrderDate",Field.DATETIME); tsOrders.addColumn("Freight",Field.CURRENCY); //and so on for each column (or at least for each required column)
To create a link between two tables you can call addJoin(String, String, String, String, int, int) or addJoin(TableSource, DatabaseField, TableSource, DatabaseField, int, int).
For example:
TableSource tsOrders = dsNortwind.createTableSource("Orders"); tsOrders.addColumn("OrderID",Field.NUMBER); tsOrders.addColumn("CustomerID",Field.STRING); //... TableSource tsCustomers = dsNortwind.createTableSource("Customers"); tsOrders.addColumn("CustomerID",Field.STRING); //... Join aJoin = dbTables.addJoin(tsOrders,tsOrders.getDatabaseField("CustomerID"),tsCustomers,tsCustomers.getDatabaseField("CustomerID"),DatabaseTables.JOINTYPE_INNER,DatabaseTables.JOINLINK_EQUALS);
To insert columns into an empty report you should do something like that:

Fields fields = eng.getFields(); DatabaseField dbField = null; FieldElement fElem = null; Area dArea = eng.getArea("D"); Section dSec = dArea.getSection(0); dbField = tsOrders.getDatabaseField("OrderID"); fElem = dSec.addFieldElement(dbField, 100, 100, 2000, 500); dbField = tsOrders.getDatabaseField("CustomerID"); fElem = dSec.addFieldElement(dbField, 3000, 100, 2000, 500); dbField = tsOrders.getDatabaseField("OrderDate"); fElem = dSec.addFieldElement(dbField, 100, 100, 2000, 500); dbField = tsOrders.getDatabaseField("Freight"); fElem = dSec.addFieldElement(dbField, 6000, 100, 2000, 500);
This class is part of the RDC.

Since:

2.0

See Also:

Datasource, TableSource, Join, DataSourceConfigurationManager, DataSourceConfiguration, Serialized Form

Field Summary

Fields
Modifier and Type	Field and Description
`static int`	`JOINLINK_EQUALS` Use this value to set an "=" link between two table columns.
`static int`	`JOINLINK_GREATER_EQUALS_THAN` Use this value to set an ">=" link between two table columns.
`static int`	`JOINLINK_GREATER_THAN` Use this value to set an ">" link between two table columns.
`static int`	`JOINLINK_LESS_EQUALS_THAN` Use this value to set an "<=" link between two table columns.
`static int`	`JOINLINK_LESS_THAN` Use this value to set an "<" link between two table columns.
`static int`	`JOINLINK_NOT_EQUALS` Use this value to set an "<>" link between two table columns.
`static int`	`JOINTYPE_FULL_OUTER` Use this value for creating an full outer join.
`static int`	`JOINTYPE_INNER` Use this value for creating an inner join.
`static int`	`JOINTYPE_LEFT_OUTER` Use this value for creating an left outer join.
`static int`	`JOINTYPE_RIGHT_OUTER` Use this value for creating an right outer join.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`Join`	`addJoin(java.lang.String fromTableAlias, java.lang.String fromColumn, java.lang.String toTableAlias, java.lang.String toColumn, int joinType, int linkType)` Adds a join into report.
`Join`	`addJoin(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn, int joinType, int linkType)` Adds a join into report.
`static java.lang.String`	`changeWhereToNoRows(java.lang.String sqlStatement)` Returns the sql statement with WHERE condition 1=0 to avoid the db returning any data.
`Datasource`	`createDatasource(java.lang.String dataSourceConfigurationName)` Creates a Datasource that uses a DataSourceConfiguration to create a database connection.
`java.lang.String[]`	`getAllAvailableColumns()` Returns all columns that are available for this report.
`java.lang.String[]`	`getColumnNames(java.lang.String alias)` Returns a String[] with the column names.
`int[]`	`getColumnTypes(java.lang.String alias)` Returns an int[] with the types of columns.
`Datasource`	`getDatasource(int idx)` Returns the data source at position idx.
`Datasource`	`getDatasource(java.lang.String name)` Returns the datasource by name.
`int`	`getDatasourceCount()` Returns how many data sources are known.
`java.util.Vector`	`getJoinsEntries()` Returns a Vector with an entry for each join.
`java.util.Vector<java.lang.String>`	`getJoinsView()` Returns a Vector with an entry for each join, i.e.
`int`	`getSqlAliasCount()` Returns the count of aliases.
`java.lang.String[]`	`getSqlAliasNames()` Returns a String[] with the names of the sql aliases used in this report.
`java.lang.String[]`	`getSqlSourcesView()` Returns a String[] with the names of all TableSources used in this report.
`TableSource`	`getTablesource(java.lang.String alias)` Returns the TableSource with that alias name.
`boolean`	`isSavePassword()` Returns if the password should be saved.
`static int`	`mapSqlTypeToCCType(int sqlType)` Conversion between the java.sql.Types data types of a database table column and the internal representation of data types in RDC.
`void`	`removeDatasource(Datasource ds)` Removes a datasource connection from the known connections.
`void`	`removeJoin(java.lang.String fromTableAlias, java.lang.String fromColumn, java.lang.String toTableAlias, java.lang.String toColumn)` Removes a join from report if exists.
`void`	`removeJoin(TableSource fromTableSource, DatabaseField fromColumn, TableSource toTableSource, DatabaseField toColumn)` Removes a link of a join from the report if it exists.
`void`	`removeSqlSource(java.lang.String alias)` Removes a sql source from report (a table or a stored procedure), if exists.
`void`	`setSavePassword(boolean save)` Sets if the password for this report should be saved in the xml file (report template).

- Field Detail
  - JOINTYPE_INNER
```
public static final int JOINTYPE_INNER
```
    Use this value for creating an inner join.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINTYPE_RIGHT_OUTER
```
public static final int JOINTYPE_RIGHT_OUTER
```
    Use this value for creating an right outer join.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINTYPE_LEFT_OUTER
```
public static final int JOINTYPE_LEFT_OUTER
```
    Use this value for creating an left outer join.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINTYPE_FULL_OUTER
```
public static final int JOINTYPE_FULL_OUTER
```
    Use this value for creating an full outer join.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_EQUALS
```
public static final int JOINLINK_EQUALS
```
    Use this value to set an "=" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_GREATER_THAN
```
public static final int JOINLINK_GREATER_THAN
```
    Use this value to set an ">" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_GREATER_EQUALS_THAN
```
public static final int JOINLINK_GREATER_EQUALS_THAN
```
    Use this value to set an ">=" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_LESS_THAN
```
public static final int JOINLINK_LESS_THAN
```
    Use this value to set an "<" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_LESS_EQUALS_THAN
```
public static final int JOINLINK_LESS_EQUALS_THAN
```
    Use this value to set an "<=" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
  - JOINLINK_NOT_EQUALS
```
public static final int JOINLINK_NOT_EQUALS
```
    Use this value to set an "<>" link between two table columns.
    
    See Also:
    
    addJoin(String, String, String, String, int, int), Constant Field Values
- Method Detail
  - setSavePassword
```
public void setSavePassword(boolean save)
                     throws ReportException
```
    Sets if the password for this report should be saved in the xml file (report template).
    
    Parameters:
    
    save - The password will be saved only if this is true.
    
    Throws:
    
    ReportException - will thrown if Engine is invalid or finished.
    
    Since:
    
    3.0
    
    See Also:
    
    isSavePassword(), Datasource.getUsername(), Datasource.getPassword(), Datasource.setPassword(String)
  - isSavePassword
```
public boolean isSavePassword()
```
    Returns if the password should be saved.
    
    Returns:
    
    True if the password should be saved false otherwise.
    
    Since:
    
    3.0
    
    See Also:
    
    setSavePassword(boolean), Datasource.getUsername(), Datasource.getPassword(), Datasource.setPassword(String)
  - changeWhereToNoRows
```
public static java.lang.String changeWhereToNoRows(java.lang.String sqlStatement)
```
    Returns the sql statement with WHERE condition 1=0 to avoid the db returning any data. Use this to check the errors in the statement.
    
    Parameters:
    
    sqlStatement - the original statement
    
    Returns:
    
    the changed statement with condition
    
    Since:
    
    4.0
  - removeSqlSource
```
public void removeSqlSource(java.lang.String alias)
                     throws ReportException
```
    Removes a sql source from report (a table or a stored procedure), if exists.
    
    Parameters:
    
    alias - The alias name of the sql source, i.e. "Customer" or "Credit_Limit".
    
    Throws:
    
    ReportException - If there is no TableSource (that means table, view or stored procedure) in the report with the given alias or a stored procedure should be removed which uses prompt fields for their parameter(s) and at least one parameter could not be removed as it is still used in the report.
    
    Since:
    
    2.0
  - getSqlSourcesView
```
public java.lang.String[] getSqlSourcesView()
```
    Returns a String[] with the names of all TableSources used in this report.
    
    Returns:
    
    an array of the names of all TableSources in this report
    
    Since:
    
    2.0
    
    See Also:
    
    Datasource.createTableSource(String), Datasource.createTableSource(String, String), removeSqlSource(String)
  - getSqlAliasNames
```
public java.lang.String[] getSqlAliasNames()
```
    Returns a String[] with the names of the sql aliases used in this report.
    Example:
```
 Engine e = new Engine( Engine.NO_EXPORT );
 e.setReportFile("file:c:/MyReport.rpt");
 DatabaseTables dt = e.getDatabaseTables();
 String[] aliasNames = dt.getSqlAliasNames();
 for (int i=0;i<aliasNames.length;i++) {
     System.out.println(aliasNames[i]);
 }
```
    Returns:
    
    all sql alias names of the TableSources used in this report
    
    Since:
    
    3.0
    
    See Also:
    
    Datasource.createTableSource(String), Datasource.createTableSource(String, String), removeSqlSource(String)
  - getSqlAliasCount
```
public int getSqlAliasCount()
```
    Returns the count of aliases.
    
    Returns:
    
    the number of TableSources defined in this report
    
    Since:
    
    3.0
  - getColumnNames
```
public java.lang.String[] getColumnNames(java.lang.String alias)
```
    Returns a String[] with the column names.
    
    Parameters:
    
    alias - the alias name of the TableSource to return the column names of
    
    Returns:
    
    column names or null if alias does not exist.
    
    Since:
    
    3.0
    
    See Also:
    
    getSqlAliasNames()
  - getColumnTypes
```
public int[] getColumnTypes(java.lang.String alias)
```
    Returns an int[] with the types of columns.
    
    Parameters:
    
    alias - The sql source alias.
    
    Returns:
    
    The types of columns or null if alias does not exist.
    
    Since:
    
    3.0
    
    See Also:
    
    Field.NUMBER, Field.BOOLEAN, Field.DATE, Field.DATETIME, Field.TIME, Field.STRING
  - addJoin
```
public Join addJoin(java.lang.String fromTableAlias,
                    java.lang.String fromColumn,
                    java.lang.String toTableAlias,
                    java.lang.String toColumn,
                    int joinType,
                    int linkType)
             throws ReportException
```
    Adds a join into report. Joins are required to connect the tables of the report. A join consists of two tables (fromTable and toTable) and the link between their columns. To create a join between a table with a primary key and a table with foreign key, use joinType JOINTYPE_INNER and linkType JOINLINK_EQUALS. That makes the statement "fromTable.fromColumn = toTable.toColumn" valid. If the joinLink is JOINLINK_GREATER_THAN only records will be printed, which makes the statement "fromTable.fromColumn > toTable.toColumn" valid. For create a join which is defined by multiple links between the table, simply call that method for each link again, with the same parameter for joinType, fromTable and toTable. If given joinType for one table pair is another than the joinType in existing joins, then the join type of the table pair will be changed.
    Parameters:
    
    fromTableAlias - The name of the table the join starts, i.e. Customer
    
    toTableAlias - The name of the table the join ends, i.e. Employee_Address
    
    fromColumn - The name of the column the join starts, i.e. City
    
    toColumn - The name of the column the join starts, i.e. City
    joinType - The join type. Possible values:
    
    JOINTYPE_FULL_OUTER
    
    JOINTYPE_INNER
    
    JOINTYPE_LEFT_OUTER
    
    JOINTYPE_RIGHT_OUTER
    linkType - The link type for the column link. Possible values:
    
    JOINLINK_EQUALS
    
    JOINLINK_GREATER_EQUALS_THAN
    
    JOINLINK_GREATER_THAN
    
    JOINLINK_LESS_EQUALS_THAN
    
    JOINLINK_LESS_THAN
    
    JOINLINK_NOT_EQUALS
    Returns:
    
    the created Join object or the join object to which further columns were added.
    
    Throws:
    
    ReportException - will be thrown if join is not valid.
    
    ReportException - if the same join already exists
    
    Since:
    
    4.0
  - addJoin
```
public Join addJoin(TableSource fromTableSource,
                    DatabaseField fromColumn,
                    TableSource toTableSource,
                    DatabaseField toColumn,
                    int joinType,
                    int linkType)
             throws ReportException
```
    Adds a join into report. Joins are required to connect the tables of the report. A Join consists of two tables (fromTable and toTable) and the link between their columns. To create a join between a table with a primary key and a table with foreign key, use joinType JOINTYPE_INNER and linkType JOINLINK_EQUALS. That makes the statement "fromTable.fromColumn = toTable.toColumn" valid. If the joinLink is JOINLINK_GREATER_THAN only records will be printed, which makes the statement "fromTable.fromColumn > toTable.toColumn" valid. For create a join which is defined by multiple links between the table, simply call that method for each link again, with the same parameter for joinType, fromTable and toTable. If given joinType for one table pair is another than the joinType in existing joins, then the join type of the table pair will be changed.
    Parameters:
    
    fromTableSource - The TableSource from which the join starts.
    
    toTableSource - The TableSource the join ends.
    
    fromColumn - The column of the fromTableSource that is linked with a column of the toTableSource.
    
    toColumn - The column of the toTableSource that is linked with a column of the fromTableSource.
    joinType - The join type. Possible values:
    
    JOINTYPE_FULL_OUTER
    
    JOINTYPE_INNER
    
    JOINTYPE_LEFT_OUTER
    
    JOINTYPE_RIGHT_OUTER
    linkType - The link type for the column link. Possible values:
    
    JOINLINK_EQUALS
    
    JOINLINK_GREATER_EQUALS_THAN
    
    JOINLINK_GREATER_THAN
    
    JOINLINK_LESS_EQUALS_THAN
    
    JOINLINK_LESS_THAN
    
    JOINLINK_NOT_EQUALS
    Returns:
    
    the created Join object or the join object to which further columns were added.
    
    Throws:
    
    ReportException - will be thrown if join is not valid or if the same join already exists
    
    java.lang.IllegalArgumentException - If the passed joinType or linkType is invalid.
    
    Since:
    
    6.1
    
    See Also:
    
    getTablesource(String), Datasource.createTableSource(String,String), TableSource.getDatabaseField(String)
  - removeJoin
```
public void removeJoin(java.lang.String fromTableAlias,
                       java.lang.String fromColumn,
                       java.lang.String toTableAlias,
                       java.lang.String toColumn)
                throws ReportException
```
    Removes a join from report if exists. If not nothing happens.
    
    Parameters:
    
    fromTableAlias - The name of the table the join starts, i.e. "Customer".
    
    fromColumn - The name of the column the join starts, i.e. "City".
    
    toTableAlias - The name of the table the join ends, i.e. "Employee_Addresses".
    
    toColumn - Tha name of the column the join ends, i.e. "City".
    
    Throws:
    
    ReportException - will thrown if parameters are invalid.
    
    Since:
    
    3.0
  - removeJoin
```
public void removeJoin(TableSource fromTableSource,
                       DatabaseField fromColumn,
                       TableSource toTableSource,
                       DatabaseField toColumn)
```
    Removes a link of a join from the report if it exists.
    
    Parameters:
    
    fromTableSource - The from TableSource of the join.
    
    fromColumn - The column of the from TableSource of the join.
    
    toTableSource - The to TableSource of the join.
    
    toColumn - The column of the to TableSource of the join.
    
    Throws:
    
    java.lang.IllegalArgumentException - If one of the parameters is null or if the columns can not be found in the given TableSources.
    
    Since:
    
    6.1
  - getJoinsView
```
public java.util.Vector<java.lang.String> getJoinsView()
```
    Returns a Vector with an entry for each join, i.e. "table1.id = table2.id".
    
    Returns:
    
    a Vector containing the join conditions as strings
    
    Since:
    
    2.0
    
    See Also:
    
    addJoin(String, String, String, String, int, int), removeJoin(String, String, String, String)
  - getJoinsEntries
```
public java.util.Vector getJoinsEntries()
```
    Returns a Vector with an entry for each join. Every entry is a Join object
    
    Returns:
    
    a Vector containing instances of Join
    
    Since:
    
    3.0
    
    See Also:
    
    Join, addJoin(String, String, String, String, int, int), removeJoin(String, String, String, String)
  - mapSqlTypeToCCType
```
public static int mapSqlTypeToCCType(int sqlType)
```
    Conversion between the java.sql.Types data types of a database table column and the internal representation of data types in RDC.
    
    Parameters:
    
    sqlType - One of the types defined in java.sql.Types.
    
    Returns:
    
    One of the following types NUMBER, BOOLEAN, STRING, DATE, TIME, DATETIME or -1 if unsupported in RDC.Field.
    
    Since:
    
    3.0
    
    See Also:
    
    Field.NUMBER, Field.BOOLEAN, Field.DATE, Field.DATETIME, Field.TIME, Field.STRING
  - getAllAvailableColumns
```
public java.lang.String[] getAllAvailableColumns()
```
    Returns all columns that are available for this report. These are not the columns that are used in the report but all columns that could be used because of the set database connection and the tables / views / stored procedure that is/are available.
    The returned Strings are the full column names which will include the alias of the table / view / stored procedure and will look like this:
    
    table-alias.column-name
    
    Returns:
    
    The array containing all columns that can be used in this report.
    
    Since:
    
    4.0
    
    See Also:
    
    getSqlAliasNames(), getColumnNames(String)
  - getTablesource
```
public TableSource getTablesource(java.lang.String alias)
                           throws ReportException
```
    Returns the TableSource with that alias name.
    
    Parameters:
    
    alias - Alias of data source to be retrieved.
    
    Returns:
    
    The TableSource with that alias name.
    
    Throws:
    
    ReportException - If no TableSource exists with the alias name.The alias name is case insensitive per default. It is case sensitive if the database property "useQuoteLowerCase" is set on true.
    
    Since:
    
    4.0
  - removeDatasource
```
public void removeDatasource(Datasource ds)
                      throws ReportException
```
    Removes a datasource connection from the known connections.
    
    Parameters:
    
    ds - Datasource to remove.
    
    Throws:
    
    ReportException - If trying to remove a connection which is used by the report.
    
    Since:
    
    4.0
  - getDatasource
```
public Datasource getDatasource(int idx)
```
    Returns the data source at position idx. A special behaviour for index 0: if getDatasource(0) returns null, a new Datasource object will be created automatically. So getDatasource(0) is always possible. For all indexes>0 you have to make sure that set index matches with Datasource count.
    
    Parameters:
    
    idx - Position of data source to be returned. The first Datasource has index 0.
    
    Returns:
    
    Data source at position idx.
    
    Since:
    
    4.0
  - getDatasource
```
public Datasource getDatasource(java.lang.String name)
```
    Returns the datasource by name. Returns no default empty datasource like getDatasource(int) with 0.
    
    Parameters:
    
    name - the name
    
    Returns:
    
    the datasource or null if this not found
    
    Since:
    
    9.2
  - getDatasourceCount
```
public int getDatasourceCount()
```
    Returns how many data sources are known.
    
    Returns:
    
    How many data sources are known.
    
    Since:
    
    4.0
  - createDatasource
```
@Nonnull
public Datasource createDatasource(java.lang.String dataSourceConfigurationName)
                                      throws ReportException
```
    Creates a Datasource that uses a DataSourceConfiguration to create a database connection.
    A DataSourceConfiguration is defined server wide, so the report doesn't store information about host, user, password,.. .
    The advantage of using datasource configurations is that if you change the used database later you don't need to change the reports. You only need to change the global data source configuration.
    Multiple data sources can be used in the same report. Therefore it is possible to use tables from different database servers in the same report.
    The method implicitly adds the created Datasource to the list of Datasources in the report.
    
    Parameters:
    
    dataSourceConfigurationName - the name of the global datasource configuration.
    
    Returns:
    
    a new datasource for the report
    
    Throws:
    
    ReportException - if there is no known DataSourceConfiguration with passed name.
    
    Since:
    
    6.0
    
    See Also:
    
    DataSourceConfiguration, DataSourceConfigurationManager, Datasource

Class DatabaseTables

Field Summary

Method Summary

Field Detail

JOINTYPE_INNER

JOINTYPE_RIGHT_OUTER

JOINTYPE_LEFT_OUTER

JOINTYPE_FULL_OUTER

JOINLINK_EQUALS

JOINLINK_GREATER_THAN

JOINLINK_GREATER_EQUALS_THAN

JOINLINK_LESS_THAN

JOINLINK_LESS_EQUALS_THAN

JOINLINK_NOT_EQUALS

Method Detail

setSavePassword

isSavePassword

changeWhereToNoRows

removeSqlSource

getSqlSourcesView

getSqlAliasNames

getSqlAliasCount

getColumnNames

getColumnTypes

addJoin

addJoin

removeJoin

removeJoin

getJoinsView

getJoinsEntries

mapSqlTypeToCCType

getAllAvailableColumns

getTablesource

removeDatasource

getDatasource

getDatasource

getDatasourceCount

createDatasource