com.inet.report
Class DatabaseTables

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);


A further way to get a data source is to use query files (generated by CrystalReports). With method readDbFromQuery(java.net.URL) you can let read your pre designed data source from a query file.
DatabaseTables dataSources = eng.getDatabaseTables();
dataSources.readDbFromQuery(new java.net.URL("file:C:/i-net Clear Reports/xyz.qry"));


This class is part of the RDC.

See Also:

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

Field Summary

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

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	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	readDbFromQuery(java.net.URL url) Use this method if you have already designed a query file.
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.

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.

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

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.

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

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();
 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

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

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.

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.

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

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.

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

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

See Also:

Join, addJoin(String, String, String, String, int, int), removeJoin(String, String, String, String)

readDbFromQuery

public void readDbFromQuery(java.net.URL url)
                     throws ReportException

Use this method if you have already designed a query file. This enables you to use the data source (and of course all columns) in the query file with RDC.

Parameters:

url - The URL to the query file. Note that the query file has to be located in the same directory as the report.

Throws:

ReportException - is thrown if query file was not found.

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.

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.

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.

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.

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.

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.

createDatasource

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.

See Also:

DataSourceConfiguration, DataSourceConfigurationManager, Datasource