Klasse Database

java.lang.Object
com.inet.report.database.BaseDataFactory
com.inet.report.database.JdbcData
com.inet.report.Database
Alle implementierten Schnittstellen:
DataFactory, com.inet.report.database.sql.SqlSyntax, Serializable
public class Database extends JdbcData implements Serializable, com.inet.report.database.sql.SqlSyntax
This class is a layer between a DBMS or any other data source and i-net Clear Reports. It compensate the differences between the different DBMS. This class is the default implementation and should work with any SQL99 compatible database. The major information are:
  • whether a JDBC driver is used to fetch the data or if you want to set the data directly via engine.setData() or engine.setResultSet()
  • whether or not the data must be fetched for every instance of the sub-report on only once
  • the string that this database uses to represent a boolean

Additionally this class provides a method which is called to fetch the data from the database. This method is called when the report engine is executed and fetches the data for both, the main report and all its sub-reports. You may override this method and use engine.setData() if you want to fetch the report data yourself.

We first describe how data for the main report is fetched:

  • We check which DataSourceConfiguration is used in the report template. You can override the DataSourceConfiguration via the Datasource.setup or via the URL parameter datasource=.
  • The appropriate driver is loaded with the appropriate URL and the appropriate properties.
  • A connection to the database is opened. This only happens when the maximum number of connections (MaxConnections=5 in the i-net Clear Reports configuration) is not exceeded---and if the method database.useJdbcDriver() returns true.
  • The meta-data is examined and the appropriate database class is loaded
  • An SQL statement is created and executed in the database if database.useJdbcDriver() returns true.
  • The report data is fetched by calling the method getReportData(). The data is fetched, unnecessary records are discarded, the data is sorted and grouped and stored into the engine by using the method engine.setData().
  • The connection is closed immediately.

Fetching data for sub-reports is more complicated. Each sub-report is a complete report in its own right and thus consumes one connection to the database. Also a sub-report may be called more than once for each main report. For a given main report a sub-report must be re-executed and its report data must be fetched again if it is called with different set of parameters. This means that the database connection for a sub-report cannot be closed immediately, instead it will be closed after the main report has finished executing.
In the i-net Clear Reports configuration the maximum of connections is set to 5 (MaxConnections=5). If your main report consumes one connection then your sub-reports can only consume MaxConnections-1 connections. So please set MaxConnections to an appropriate value. For example if you have a report which consumes a database connection and 5 sub-reports where each sub-report also consume one database connection set the value MaxConnections=6. However, if your reports use more than 2 or 3 sub-reports, you should better change the layout of your report.
If you override the method getReportData(), you could specify if the method getReportData() should be called whenever the set of parameters changes for the sub-report or only once. You do this by overriding the method getReportDataPerInstance() to return true.
Seit:
1.0
Siehe auch:

  • Konstruktorübersicht

    Konstruktoren
    Konstruktor
    Beschreibung
    Database()
    Create a default Database class instance.

  • Methodenübersicht

    Modifikator und Typ
    Methode
    Beschreibung
    String
    convertIdentifier(Datasource ds, String identifier)
    Checks whether an identifier can be used right away or has to be quoted due to invalid characters or keywords.
    String
    convertModOperation(String dividend, String divisor)
    Returns a valid sql expression to evaluate the remainder of an division.
    String
    convertStringToSQLSyntax(String s, boolean isFunctionParam)
    This function transfoms a java String to a SQL String representation.
    The default implementation surrounds the String with ' char.
    So the String hello will transformed to 'hello'.
    If passed String contains the qote character, it will be quoted.
    So the String Hello 'World' will transformed to 'Hello ''World'''.

    This function can be used to transform the String that multilanguage characters will supported.
    String
    convertToBoolean(boolean value)
    Returns the representation of a boolean value in the query language of the database.
    String
    convertToBoolean(String fieldReference, boolean invert)
    If required, this function modifies a field reference to convert the value of the field to a boolean.
    String
    convertToDate(String date)
    Returns a valid sql expression to convert the given parameter to a Time data type.
    String
    convertToDate(String year, String month, String day)
    Returns a valid sql expression to convert the given parameters to a Date data type.
    String
    convertToDouble(String parameter)
    Returns a valid sql expression to convert the given parameter to a Double data type.
    String
    convertToInt(String parameter)
    Returns a valid sql expression to convert the given parameter to a Integer data type.
    String
    convertToString(String parameter)
    Returns a valid sql expression to convert the given parameter to a String data type.
    String
    convertToTime(String time)
    Returns a valid sql expression to convert the given parameter to a Time data type.
    String
    convertToTime(String hour, String minute, String sec)
    Returns a valid sql expression to convert the given parameters to a Time data type.
    String
    convertToTimeStamp(String year, String month, String day, String hour, String minute, String sec)
    Returns a valid sql expression to convert the given parameters to a Timestamp data type.
    void
    fetchData(Engine engine, FetchTables fetchTables, DataCollector dataCollector)
    Fetches the part of the report data for which this data factory is responsible.
    protected void
    findColumnLabels(TableSource tableSource)
    Tries to find column labels for the columns of the provided TableSource.
    String
    getAddOp()
    Overwrite this method if the used database uses another sign than "+" as add operator.
    String
    getAliasToken()
    Veraltet.
    As of i-net Clear Reports 13.1, replaced by getConfiguration().getAliasToken()
    protected String
    getColumnName(String colName, String alias, int driverVersion, TableSource ts, int colIdx)
    Returns the column name dependent of the database.
    protected int
    getCursorType(int dataType)
    Returns the SQL data type for a cursor if the stored procedure parameter is a cursor.
    String
    getIdentifierQuoteString()
    Returns the string used to quote SQL identifiers.
    int
    getMaxAliasNameLength()
    Returns the maximum length of the alias name.
    protected ResultSet
    getProcedureColumns(Datasource ds, String catalog, String schema, String procedure)
    Is used from #getColumns(TableSource) if the TableSource based on a stored procedure.
    protected ResultSet
    getProcedures(Datasource ds, String catalog)
    Is used from #getTableSourceInfos(Datasource, String) to find a list of stored procedures.
    protected String
    getSourceNameWithChange(TableSource ts, boolean escapeEverything)
    Returns the source name like catalog.schema.table.
    protected String
    getSqlIdentifier(Datasource ds, String identifier, boolean escapeEverything)
    Returns the SQL identifier regarding the settings of getIdentifierQuoteString(), escapeEverything and needQuote(String).
    com.inet.report.database.sql.SqlSyntax
    getSqlSyntax()
    Returns the SQL syntax definition instance for this data factory.
    protected ResultSet
    getTables(Datasource ds, String catalog)
    Is used from #getTableSourceInfos(Datasource, String) to find a list of tables and views.
    protected void
    initTransient()
    Init transient variables after serialize.
    boolean
    isCursor(int sqlType)
    FOR INTERNAL USE ONLY test if the given sql type is a cursor type
    boolean
    isEscapeEverything()
    Returns the value of the flag escapeEverything.
    protected boolean
    isKeyword(String identifier)
    Returns true when the specified identifier is a keyword of this database.
    boolean
    isUseOrderBy()
    Indicates whether an ORDER BY clause is allowed for the sql statement.
    This flag is relevant if tables and one Datasource is used only.
    boolean
    isUseParenthesiseForJoin()
    Indicates whether parentheses are allowed in FROM clause of the sql statement.
    For example the SAPDB does not support parentheses in JOIN syntax.
    This flag is relevant if tables and one Datasource is used only.
    boolean
    isUseQuoteLowerCase()
    Indicates whether table names and column names have to be quoted
    For example, this property can be necessary for Oracle/PostgreSQL databases, if table/column where created with quotes and lower (Oracle) or upper (PostgreSQL) case characters.
    boolean
    isUseSQL92syntax()
    Indicates whether the SQL statement can contain the join condition in SQL92 syntax or not.
    boolean
    isUseWhereClause()
    Indicates whether the SQL statement can contain the WHERE clause or not.
    This flag is relevant if tables and one Datasource is used only.
    protected boolean
    needQuote(String identifier)
    Returns if the supplied SQL identifier needs to be put in quotes.
    protected String[]
    parseSourceName(String sqlSource)
    Splits the provided name of a SQL source (name of a table, view or stored procedure) into three parts.
    protected void
    scanIdentifierQuoteString(Datasource dc)
    This method will detect which String should be used to quote database identifiers for the supplied Datasource.
    protected boolean
    scanSourceType(TableSource ts, com.inet.report.ParameterList dataTypeScale, boolean useMetaDataCache)
    FOR INTERNAL USE ONLY Ermittelt den Typ der Datenquelle true, bei Stored Procedure false(default)
    protected boolean
    scanSourceTypeParameters(Connection con, String srcCatalog, String srcSchema, ResultSet rs, com.inet.report.ParameterList parameters)
    FOR INTERNAL USE ONLY Scans the result set whith a column format as defined by DatabaseMetaData.getProcedureColumns(String, String, String, String) and adds all columns found to a ParameterList.
    protected void
    scanSourceTypeParameterSingle(ResultSet rs, com.inet.report.ParameterList parameters)
    FOR INTERNAL USE ONLY Add a single parameter, does not change the ResultSet position.
    void
    setAliasToken(String aliasToken)
    Veraltet.
    As of i-net Clear Reports 13.1, replaced by getConfiguration().setAliasToken(String)
    void
    setIdentifierQuoteString(String quoteString)
    This method is for adapt the sql statement for a specific database.
    Sets the string used to quote SQL identifiers.
    void
    setUseEscapeEverything(boolean escapeEvreything)
    This method is for adapt the sql statement for a specific database.
    This flag influence the way SQL identifier are written to SQL statement.
    void
    setUseOrderBy(boolean useOrderBy)
    This method is for adapt the sql statement for a specific database.
    Set this flag to false if the database does not support the ORDER BY clause.
    void
    setUseParenthesiseForJoin(boolean useParenthesiseForJoin)
    This method is for adapt the sql statement for a specific database.
    Set here whether the sql statement can have parentheses in FROM clause or not.
    For example the SAPDB does not support parentheses in JOIN syntax.
    This flag is relevant if tables and one Datasource is used only.
    void
    setUseQuoteLowerCase(boolean useQuoteLowerCase)
    This method is for adapt the sql statement for a specific database.
    Set here whether table names and column names have to be quoted.
    For example, this property can be necessary for Oracle/PostgreSQL databases, if table/column where created with quotes and lower (Oracle) or upper (PostgreSQL) case characters.
    void
    setUseSQL92syntax(boolean useSQL92syntax)
    This method is for adapt the sql statement for a specific database.
    Set here whether the SQL statement can contain the join condition in SQL92 syntax or not.
    If set on true, the join is contained in FROM clause, otherwise in WHERE clause.
    Note: full outer join is not possible in WHERE clause.
    void
    setUseWhereClause(boolean useWhereClause)
    This method is for adapt the sql statement for a specific database.
    Set this flag to false if the database does not support the WHERE clause.
    String
    sqlConcat(Object leftOperant, Object rightOperant)
    Returns a valid sql statement concatenating two expressions.
    String
    toSQL92(String function, Field field)
    Creates a SQL92 expression with function and one parameter.

    Von Klasse geerbte Methoden JdbcData

    getColumns, getReportDataPerInstance, getTableSourceData, getTableSourceInfos, patchSQL, supportsCommands

    Von Klasse geerbte Methoden BaseDataFactory

    getColumns, getConfiguration, getTableSourceData, setConfiguration

    Von Klasse geerbte Methoden Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

    Von Schnittstelle geerbte Methoden DataFactory

    getVersion

    Von Schnittstelle geerbte Methoden com.inet.report.database.sql.SqlSyntax

    getValidateMessage

  • Konstruktordetails

    • Database

      public Database()
      Create a default Database class instance.
      Seit:
      1.0

  • Methodendetails

    • initTransient

      protected void initTransient()
      Init transient variables after serialize.
      Seit:
      8.0

    • scanSourceType

      protected boolean scanSourceType(TableSource ts, com.inet.report.ParameterList dataTypeScale, boolean useMetaDataCache) throws SQLException, ReportException
      FOR INTERNAL USE ONLY Ermittelt den Typ der Datenquelle true, bei Stored Procedure false(default)
      Löst aus:
      SQLException
      ReportException

    • scanIdentifierQuoteString

      protected void scanIdentifierQuoteString(Datasource dc) throws SQLException, ReportException
      This method will detect which String should be used to quote database identifiers for the supplied Datasource. For JDBC drivers this will call DatabaseMetaData.getIdentifierQuoteString(). The identifier quote string detected should be applied to this Database using setIdentifierQuoteString(String).
      Overriding classes can use this method to detect the identifier quote string on their own.
      Parameter:
      dc - The Datasource providing the connection information.
      Löst aus:
      SQLException - If a SQL error occurred during request of database meta data.
      ReportException - If an error occurred during establishing a database connection.
      Seit:
      8.0

    • scanSourceTypeParameters

      protected boolean scanSourceTypeParameters(Connection con, String srcCatalog, String srcSchema, ResultSet rs, com.inet.report.ParameterList parameters) throws SQLException
      FOR INTERNAL USE ONLY Scans the result set whith a column format as defined by DatabaseMetaData.getProcedureColumns(String, String, String, String) and adds all columns found to a ParameterList.
      Parameter:
      con - the used connection
      srcCatalog - the catalog from the source of the procedure
      srcSchema - the schema from the source of the procedure
      rs - the result set of the getProcedureColumns method call, must not be null
      parameters - the list to add the parameter specs to, must not be null
      Gibt zurück:
      true, if at least one dataset is in the result set, false if it's empty
      Löst aus:
      SQLException - if a database access error occurs
      Seit:
      23.10

    • scanSourceTypeParameterSingle

      protected void scanSourceTypeParameterSingle(ResultSet rs, com.inet.report.ParameterList parameters) throws SQLException
      FOR INTERNAL USE ONLY Add a single parameter, does not change the ResultSet position. DatabaseMetaData.getProcedureColumns(String, String, String, String) and adds all columns found to a ParameterList.
      Parameter:
      rs - the result set of the getProcedureColumns method call, must not be null
      parameters - the list to add the parameter specs to, must not be null
      Löst aus:
      SQLException - if a database access error occurs
      Seit:
      23.10

    • parseSourceName

      protected String[] parseSourceName(String sqlSource)
      Splits the provided name of a SQL source (name of a table, view or stored procedure) into three parts. The name can consist of the following parts: [[catalog.]schema.]name. The array returned contains these three parts each part set to null if not set.
      Parameter:
      sqlSource - The name of the SQL source which may contain catalog or schema qualifier.
      Gibt zurück:
      A string array of dimension 3 with the following values for the three indexes:
      • array[0] the catalog or null if no catalog is set
      • array[1] the schema or null if no schema is set
      • array[2] the name (never null)
      Seit:
      8.0

    • getColumnName

      protected String getColumnName(String colName, String alias, int driverVersion, TableSource ts, int colIdx)
      Returns the column name dependent of the database.
      Setzt außer Kraft:
      getColumnName in Klasse JdbcData
      Parameter:
      colName - the column name
      alias - the column alias (required for MySQL Connector5.x only)
      driverVersion - the driver version (required for MySQL Connector5.x only)
      ts - the table source
      colIdx - the column index (required for Informix only)
      Gibt zurück:
      the name

    • needQuote

      protected boolean needQuote(String identifier)
      Returns if the supplied SQL identifier needs to be put in quotes. Usually this is the case if the identifier contains special characters or is equal to a SQL keyword.
      Parameter:
      identifier - The SQL identifier to be checked.
      Gibt zurück:
      True if the SQL identifier should be put in quotes false otherwise.
      Seit:
      3.0

    • isKeyword

      protected boolean isKeyword(String identifier)
      Returns true when the specified identifier is a keyword of this database.
      Parameter:
      identifier - the identifier
      Gibt zurück:
      true when the specified identifier is a keyword of this database
      Seit:
      15.0

    • getSqlIdentifier

      protected String getSqlIdentifier(Datasource ds, String identifier, boolean escapeEverything)
      Returns the SQL identifier regarding the settings of getIdentifierQuoteString(), escapeEverything and needQuote(String). If getIdentifierQuoteString() is empty the provided identifier will be returned without modification. If escapeEverything returns true the whole identifier provided should be put in the quotes returned by getIdentifierQuoteString(). Otherwise each part of the SQL identifier will be checked with needQuote(String) and put in quotes if necessary. The identifier can consist of catalog, schema and name like this: [[catalog.]schema.]name.
      Parameter:
      ds - The Datasource object of the report which uses the provided SQL identifier.
      identifier - The identifier which should be prepared for SQL.
      escapeEverything - True if the complete sql identifier should be quoted false otherwise
      Gibt zurück:
      The identifier which can be used in a SQL statement.
      Seit:
      15.0

    • getSourceNameWithChange

      protected String getSourceNameWithChange(TableSource ts, boolean escapeEverything)
      Returns the source name like catalog.schema.table. Overwrites the catalog and schema names if these was changed in the Datasource.
      Angegeben von:
      getSourceNameWithChange in Klasse JdbcData
      Parameter:
      ts - the table source
      escapeEverything - True if the complete sql identifier should be quoted false otherwise
      Gibt zurück:
      the name
      Seit:
      13.0

    • getAddOp

      public String getAddOp()
      Overwrite this method if the used database uses another sign than "+" as add operator.
      Gibt zurück:
      The SQL String representation of the add operator.
      Seit:
      3.0

    • sqlConcat

      public String sqlConcat(Object leftOperant, Object rightOperant)
      Returns a valid sql statement concatenating two expressions. The default implementation is:
      leftOperant + getAddOp() + rightOperant
      Example implemantation of MySQL:
      " concat("+leftOperant+","+rightOperant+") "
      Angegeben von:
      sqlConcat in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      leftOperant - the left operand
      rightOperant - the right operand
      Gibt zurück:
      A SQL String representation
      Seit:
      4.0
      Siehe auch:

    • getTables

      protected ResultSet getTables(Datasource ds, String catalog) throws SQLException, ReportException
      Is used from #getTableSourceInfos(Datasource, String) to find a list of tables and views.
      Angegeben von:
      getTables in Klasse JdbcData
      Parameter:
      ds - The current Datasource containing the Connection.
      catalog - The catalog/database set in login dialog of i-net Designer.
      Gibt zurück:
      A ResultSet with the structure of DatabaseMetaData.getTables()
      Löst aus:
      SQLException - if a database access error occurs
      ReportException - if the creation of a Connection failed.

    • getProcedures

      protected ResultSet getProcedures(Datasource ds, String catalog) throws SQLException, ReportException
      Is used from #getTableSourceInfos(Datasource, String) to find a list of stored procedures. Returns the procedures meta data for a datasource and catalog as defined by DatabaseMetaData.getProcedures(String, String, String)
      Angegeben von:
      getProcedures in Klasse JdbcData
      Parameter:
      ds - the datasource to connect to (if not already connected)
      catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
      Gibt zurück:
      each row is a procedure description
      Löst aus:
      SQLException - if a database access error occurs
      ReportException - if creating a Connection failed.

    • getProcedureColumns

      protected ResultSet getProcedureColumns(Datasource ds, String catalog, String schema, String procedure) throws SQLException, ReportException
      Is used from #getColumns(TableSource) if the TableSource based on a stored procedure.
      Angegeben von:
      getProcedureColumns in Klasse JdbcData
      Parameter:
      ds - The Datasource containing the Connection.
      catalog - The catalog/database set in login dialog of i-net Designer.
      schema - The table owner or null.
      procedure - The procedure name.
      Gibt zurück:
      A ResultSet with the structure of DatabaseMetaData.getProcedureColumns
      Löst aus:
      SQLException - This SQLException will be show in a message box in i-net Designer.
      ReportException - if the creation of a Connection failed.

    • isCursor

      public boolean isCursor(int sqlType)
      FOR INTERNAL USE ONLY test if the given sql type is a cursor type
      Parameter:
      sqlType - The sql type to test if it should be treated as cursor.
      Gibt zurück:
      True if the supplied sql type should be treated as a cursor false otherwise.
      Seit:
      3.3

    • getCursorType

      protected int getCursorType(int dataType)
      Returns the SQL data type for a cursor if the stored procedure parameter is a cursor. If no special SQL data type exists for a cursor then the original data type will be returned.
      The default implementation returns the original data type always.
      Parameter:
      dataType - The original SQL data type for the stored procedure parameter which should be used as cursor.
      Gibt zurück:
      The SQL data type for a cursor.
      Seit:
      4.0

    • getMaxAliasNameLength

      public int getMaxAliasNameLength()
      Returns the maximum length of the alias name. Some databases have restrictions to the length of the alias name. So this method will return 18 as the default value. Databases which allow alias names to have more than 18 characters can overwrite this method and return a higher value.
      Gibt zurück:
      The maximum length of an alias name.
      Seit:
      6.0

    • convertToString

      public String convertToString(String parameter) throws ReportException
      Returns a valid sql expression to convert the given parameter to a String data type. This method is required for transforming the record selection formula and the group to a valid SQL Statement. The base implementation does the following:
      return "{fn CONVERT( "+parameter+", SQL_CHAR ) }";
      The class DatabaseAccess for example overwrites that method and returns:
      return "Cstr("+parameter+")";
      Angegeben von:
      convertToString in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      parameter - set in ToText(parameter)
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToInt

      public String convertToInt(String parameter) throws ReportException
      Returns a valid sql expression to convert the given parameter to a Integer data type. This method is required for transforming the record selection formula and the group to a valid SQL Statement. The base implementation does the following:
      return "{fn CONVERT( "+parameter+", SQL_INTEGER ) }";
      The class DatabaseAccess for example overwrites that method and returns:
      return "Cint("+parameter+")";
      Angegeben von:
      convertToInt in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      parameter - set in Int(parameter),Truncate(parameter)
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToDouble

      public String convertToDouble(String parameter) throws ReportException
      Returns a valid sql expression to convert the given parameter to a Double data type. This method is required for transforming the record selection formula and the group to a valid SQL Statement. The base implementation does the following:
      return "{fn CONVERT( "+parameter+", SQL_DOUBLE ) }";
      The class DatabaseAccess for example overwrites that method and returns:
      return "Cdbl("+parameter+")";
      Angegeben von:
      convertToDouble in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      parameter - set in Cdbl(parameter),ToNumber(parameter)
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToTime

      public String convertToTime(String time) throws ReportException
      Returns a valid sql expression to convert the given parameter to a Time data type. This method is required for transforming the record selection formula and the group to a valid SQL Statement. The base implementation does the following:
      return "{fn CONVERT( "+parameter+", SQL_TIME ) }";
      The class DatabaseOracle for example overwrites that method and returns:
      return "to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')";
      Angegeben von:
      convertToTime in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      time - parameter set in TimeValue(parameter),CTime(parameter)
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToDate

      public String convertToDate(String date) throws ReportException
      Returns a valid sql expression to convert the given parameter to a Time data type. This method is required for transforming the record selection formula and the group to a valid SQL Statement. The base implementation does the following:
      return "{fn CONVERT( "+parameter+", SQL_TIME ) }";
      The class DatabaseOracle for example overwrites that method and returns:
      return "to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')";
      Angegeben von:
      convertToDate in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      date - parameter set in TimeValue(parameter),CTime(parameter)
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToDate

      public String convertToDate(String year, String month, String day) throws ReportException
      Returns a valid sql expression to convert the given parameters to a Date data type. This method must be overwritten if you use the function CDate(YYYY, MM, DD) or Date(YYYY, MM, DD) and at least one of the parameters is a dynamic parameter. The parameter is dynamic if it is a table column for example. The returned sql expression must be database specific, because there exists no escape function with three parameter.
      Example solution for SQL Server:
      "convert(datetime, right( '0000' + convert(varchar,"+year+"),4) + right( '00' + convert(varchar,"+month+"), 2) + right( '00' + convert(varchar,"+day+"), 2))"
      Example solution for Oracle Server:
      "to_date("+year+"||'/'||"+month+"||'/'||"+day+",'YYYY/mm/dd')"
      Angegeben von:
      convertToDate in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      year - The year of the date to convert.
      month - The month of the date to convert.
      day - The day of the date to convert.
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToTime

      public String convertToTime(String hour, String minute, String sec) throws ReportException
      Returns a valid sql expression to convert the given parameters to a Time data type. This method must be overwritten if you use the function CTime(HH, MM, SS) or Time(HH, MM, SS) and at least one of the parameters is a dynamic parameter. The parameter is dynamic if it is a table column for example. The returned sql expression must be database specific, because there exists no escape function with three parameter.
      Example solution for SQL Server:
      "convert(datetime, right( '00' + convert(varchar,"+hour+"),2)+ ':'+ right( '00' + convert(varchar,"+minute+"), 2) +':'+ right( '00' + convert(varchar,"+sec+"),2),8)"
      Example solution for Oracle Server:
      "to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')"
      Angegeben von:
      convertToTime in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      hour - The hour of the time to convert.
      minute - The minute of the time to convert.
      sec - The second of the time to convert.
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertToTimeStamp

      public String convertToTimeStamp(String year, String month, String day, String hour, String minute, String sec) throws ReportException
      Returns a valid sql expression to convert the given parameters to a Timestamp data type. This method must be overwritten if you use the function DateTime(YYYY, MM, DD, HH, MM, SS) or DateTimeValueTime(YYYY, MM, DD, HH, MM, SS) and at least one of the parameters is a dynamic parameter. The parameter is dynamic if it is a table column for example. The returned sql expression must be database specific, because there exists no escape function with three parameter.
      Example solution for SQL Server:
      "convert(datetime, right( '0000' + convert(varchar,"+year+"),4) + '-' + right( '00' + convert(varchar,"+month+"), 2) + '-' +right( '00' + convert(varchar,"+day+"),2)+' '+right( '00' + convert(varchar,"+hour+"),2)+ ':'+ right( '00' + convert(varchar,"+minute+"), 2) +':'+ right( '00' + convert(varchar,"+sec+"),2),20)"
      Angegeben von:
      convertToTimeStamp in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      year - The year of the date to convert.
      month - The month of the date to convert.
      day - The day of the date to convert.
      hour - The hour of the time to convert.
      minute - The minute of the time to convert.
      sec - The second of the time to convert.
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertModOperation

      public String convertModOperation(String dividend, String divisor) throws ReportException
      Returns a valid sql expression to evaluate the remainder of an division. The given parameter are the divisor and the dividend of the division. This method must be overwritten if you use the mod operation and at least one of the parameters is a dynamic parameter. The parameter is dynamic if it is a table column for example. The returned sql expression must be database specific.
      The default implementation is:
      dividend+"%"+divisor Example solution for Oracle Server:
      "mod("+dividend+","+divisor+")"
      Angegeben von:
      convertModOperation in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      dividend - The dividend of the mod operation to convert.
      divisor - The divisor of the mod operation to convert.
      Gibt zurück:
      The SQL String representation.
      Löst aus:
      ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.
      Seit:
      4.0

    • convertStringToSQLSyntax

      public String convertStringToSQLSyntax(String s, boolean isFunctionParam)
      This function transfoms a java String to a SQL String representation.
      The default implementation surrounds the String with ' char.
      So the String hello will transformed to 'hello'.
      If passed String contains the qote character, it will be quoted.
      So the String Hello 'World' will transformed to 'Hello ''World'''.

      This function can be used to transform the String that multilanguage characters will supported. For MS SQL Server for example the class DatabaseSqlServer prefix the N. So the String Hello 'World' will transformed to N'Hello ''World'''.
      Angegeben von:
      convertStringToSQLSyntax in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      s - The String that have to transformed to SQL representation.
      isFunctionParam - Shows that this flag is a constant parameter to be used by a function. This is important for parameters which can not be written as multi-language parameters. For example, the first parameter of the function TIMESTAMPADD cannot be a multi-language parameter for a Microsoft SQL Server database.
      Gibt zurück:
      The SQL String representation.
      Seit:
      7.0

    • isUseOrderBy

      public boolean isUseOrderBy()
      Indicates whether an ORDER BY clause is allowed for the sql statement.
      This flag is relevant if tables and one Datasource is used only.
      Gibt zurück:
      True if the sql statement can contain an ORDER BY clause false otherwise.
      Seit:
      6.1
      Siehe auch:

    • setUseOrderBy

      public void setUseOrderBy(boolean useOrderBy)
      This method is for adapt the sql statement for a specific database.
      Set this flag to false if the database does not support the ORDER BY clause. In this case the record sorting will be done from i-net Clear Reports.
      This flag is relevant if tables and one Datasource is used only.
      Parameter:
      useOrderBy - Whether the sql statement can have an ORDER BY clause or not.
      Seit:
      6.1
      Siehe auch:

    • isUseParenthesiseForJoin

      public boolean isUseParenthesiseForJoin()
      Indicates whether parentheses are allowed in FROM clause of the sql statement.
      For example the SAPDB does not support parentheses in JOIN syntax.
      This flag is relevant if tables and one Datasource is used only.
      Gibt zurück:
      True if parantheses are allowed in the FROM clause false otherwise.
      Seit:
      6.1
      Siehe auch:

    • setUseParenthesiseForJoin

      public void setUseParenthesiseForJoin(boolean useParenthesiseForJoin)
      This method is for adapt the sql statement for a specific database.
      Set here whether the sql statement can have parentheses in FROM clause or not.
      For example the SAPDB does not support parentheses in JOIN syntax.
      This flag is relevant if tables and one Datasource is used only.
      Parameter:
      useParenthesiseForJoin - Whether the sql statement can have parentheses in FROM clause or not.
      Seit:
      5.0
      Siehe auch:

    • isUseQuoteLowerCase

      public boolean isUseQuoteLowerCase()
      Indicates whether table names and column names have to be quoted
      For example, this property can be necessary for Oracle/PostgreSQL databases, if table/column where created with quotes and lower (Oracle) or upper (PostgreSQL) case characters.
      Gibt zurück:
      True if column and table name need to be quoted false otherwise.
      Seit:
      6.1
      Siehe auch:

    • setUseQuoteLowerCase

      public void setUseQuoteLowerCase(boolean useQuoteLowerCase)
      This method is for adapt the sql statement for a specific database.
      Set here whether table names and column names have to be quoted.
      For example, this property can be necessary for Oracle/PostgreSQL databases, if table/column where created with quotes and lower (Oracle) or upper (PostgreSQL) case characters.
      Parameter:
      useQuoteLowerCase - Whether table names and column names have to be quoted.
      Seit:
      6.1
      Siehe auch:

    • isUseSQL92syntax

      public boolean isUseSQL92syntax()
      Indicates whether the SQL statement can contain the join condition in SQL92 syntax or not.
      Gibt zurück:
      True if the SQL statement can contain the join condition in SQL92 syntax false if not.
      Seit:
      6.1
      Siehe auch:

    • setUseSQL92syntax

      public void setUseSQL92syntax(boolean useSQL92syntax)
      This method is for adapt the sql statement for a specific database.
      Set here whether the SQL statement can contain the join condition in SQL92 syntax or not.
      If set on true, the join is contained in FROM clause, otherwise in WHERE clause.
      Note: full outer join is not possible in WHERE clause.
      Parameter:
      useSQL92syntax - Whether the SQL statement can contain the join condition in SQL92 syntax or not.
      Seit:
      6.1
      Siehe auch:

    • isUseWhereClause

      public boolean isUseWhereClause()
      Indicates whether the SQL statement can contain the WHERE clause or not.
      This flag is relevant if tables and one Datasource is used only.
      Gibt zurück:
      True if the database supports WHERE clauses false otherwiese.
      Seit:
      6.1
      Siehe auch:

    • setUseWhereClause

      public void setUseWhereClause(boolean useWhereClause)
      This method is for adapt the sql statement for a specific database.
      Set this flag to false if the database does not support the WHERE clause. In this case the record filtering will be done from i-net Clear Reports.
      This flag is relevant if tables and one Datasource is used only.
      Parameter:
      useWhereClause - True if the database supports WHERE clauses false otherwiese.
      Seit:
      6.1
      Siehe auch:

    • getAliasToken

      @Deprecated public String getAliasToken()
      Veraltet.
      As of i-net Clear Reports 13.1, replaced by getConfiguration().getAliasToken()
      Returns the SQL keyword that is required to define an alias name in a SQL statement.
      For some databases a whitespace have to be set between table name and table alias. Other database expect the keyword "AS".
      Gibt zurück:
      The keyword used to define an alias name.
      Seit:
      6.1
      Siehe auch:

    • setAliasToken

      @Deprecated public void setAliasToken(String aliasToken)
      Veraltet.
      As of i-net Clear Reports 13.1, replaced by getConfiguration().setAliasToken(String)
      This method is for adapt the sql statement for a specific database.
      Sets the SQL keyword that will be put between table name and table alias.
      E.g. the database PostgreSql requires the keyword "AS". Many databases do not need a special keyword to indicate that a alias name is followed.
      Parameter:
      aliasToken - The keyword used to define an alias name.
      Seit:
      6.1
      Siehe auch:

    • getIdentifierQuoteString

      public String getIdentifierQuoteString()
      Returns the string used to quote SQL identifiers.
      Gibt zurück:
      The string used to quote SQL identifiers.
      Seit:
      6.1
      Siehe auch:

    • setIdentifierQuoteString

      public void setIdentifierQuoteString(String quoteString)
      This method is for adapt the sql statement for a specific database.
      Sets the string used to quote SQL identifiers. For most databases it is a " character.
      Parameter:
      quoteString - The string which should be used to quote the SQL identifiers.
      Seit:
      6.1
      Siehe auch:

    • isEscapeEverything

      public boolean isEscapeEverything()
      Returns the value of the flag escapeEverything.
      Gibt zurück:
      True if the complete sql identifier will be quoted false otherwise.
      Seit:
      6.5
      Siehe auch:

    • setUseEscapeEverything

      public void setUseEscapeEverything(boolean escapeEvreything)
      This method is for adapt the sql statement for a specific database.
      This flag influence the way SQL identifier are written to SQL statement. Per default it is false and should be kept false for most of database.
      If the flag is set to true, the complete table identifier will enclosed with quotes.
      If the table identifier contains catalog/schema information, the complete expression will enclosed with quotes.
      Example - escape everything is true:
      the original table identifier is "atable" in catalog "aCatalog".
      The resulting expression for the table in the sql statement will be : select ... from "aCatalog.aTable"
      note: this syntax is not allowed for most of databases. The only known case setting this flag to true is using i-net FOSITEX driver for csv data files.
      Parameter:
      escapeEvreything - True if the complete sql identifier should be quoted false otherwise.
      Seit:
      6.5
      Siehe auch:

    • findColumnLabels

      protected void findColumnLabels(TableSource tableSource)
      Tries to find column labels for the columns of the provided TableSource. Overriding classes can use this method to query meta data information about the columns of the provided TableSource. They can use the connection provided by the report. (TableSource.getDatasource().getConnection()) If any column label is found overriding classes should update the database field with the column lable using DatabaseField.setColumnLabel(String).
      Parameter:
      tableSource - The TableSource to find the column labels for.
      Seit:
      8.0

    • toSQL92

      public String toSQL92(String function, Field field) throws ReportException
      Creates a SQL92 expression with function and one parameter.
      Angegeben von:
      toSQL92 in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      function - the function
      field - the parameter field
      Gibt zurück:
      the result expression
      Löst aus:
      ReportException - the data access failed
      Seit:
      11.0

    • convertToBoolean

      public String convertToBoolean(boolean value)
      Returns the representation of a boolean value in the query language of the database. The result depends of the ability of the database to resolve boolean constants.
      Angegeben von:
      convertToBoolean in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      value - the boolean value to convert
      Gibt zurück:
      the database dependent representation of a boolean
      Seit:
      11.0

    • convertToBoolean

      public String convertToBoolean(String fieldReference, boolean invert)
      If required, this function modifies a field reference to convert the value of the field to a boolean. This is required in case the database has no real boolean representation and uses a bit or number instead.
      Angegeben von:
      convertToBoolean in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      fieldReference - the plain field reference for the column which is supposed to contain a boolean value
      invert - set whether to invert the value of the field
      Gibt zurück:
      the modified field reference, may be the original one in case the database supports boolean columns
      Seit:
      11.0

    • convertIdentifier

      public String convertIdentifier(Datasource ds, String identifier)
      Checks whether an identifier can be used right away or has to be quoted due to invalid characters or keywords. If the identifier requires to be modified, this method will return a modified and valid identifier. Otherwise the original string is returned.
      Angegeben von:
      convertIdentifier in Schnittstelle com.inet.report.database.sql.SqlSyntax
      Parameter:
      ds - the description of all tables used in the current database; required to avoid using table names as identifier. may be null
      identifier - the identifier to convert to a valid SQL identifier, must not be null
      Gibt zurück:
      a valid SQL identifier
      Seit:
      14.0

    • getSqlSyntax

      public com.inet.report.database.sql.SqlSyntax getSqlSyntax()
      Returns the SQL syntax definition instance for this data factory.
      Angegeben von:
      getSqlSyntax in Schnittstelle DataFactory
      Angegeben von:
      getSqlSyntax in Klasse JdbcData
      Gibt zurück:
      the SQL syntax definition instance for this data factory

    • fetchData

      public void fetchData(Engine engine, FetchTables fetchTables, DataCollector dataCollector) throws ReportException
      Fetches the part of the report data for which this data factory is responsible. A description about what tables are required to fetch is given in the specified FetchTables instance. The resulting fetched data is put to the specified DataCollector during execution of this method.

      An example implementation for this method:

      for( TableSource tableSource : fetchDef.getTableSources() ) {
    TableData tableData;
    
     // retrieve data for table source "tableSource"
    tableData = [...]
    
    // deliver the data to the collector:
    collector.addUnjoinedData( tableSource, tableData );
}
      Angegeben von:
      fetchData in Schnittstelle DataFactory
      Setzt außer Kraft:
      fetchData in Klasse BaseDataFactory
      Parameter:
      engine - the engine of the report to render
      fetchTables - a model of joining table describing the data this data factory needs to deliver
      dataCollector - the collector which receives any resulting data
      Löst aus:
      ReportException - on failures during data fetching