Database (i-net Clear Reports API Documentation)

Overview

Package

Class

Use

Deprecated

Help

i-net Clear Reports

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.inet.report
Class Database

java.lang.Object
  com.inet.report.Database

All Implemented Interfaces:: java.io.Serializable

public class Database
extends java.lang.Object
implements java.io.Serializable
extends java.lang.Object
implements java.io.Serializable

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.

See Also:: Serialized Form

Constructor Summary
`Database()` Create a default Database class instance.

Method Summary
`java.lang.String`	`convertModOperation(java.lang.String dividend, java.lang.String divisor)` Returns a valid sql expression to evaluate the remainder of an division.
`java.lang.String`	`convertStringToSQLSyntax(java.lang.String s, boolean isFunctionParam)` This function transfoms a java String to a SQL String representation.
`java.lang.String`	`convertToBoolean(boolean value)` Returns the representation of a boolean value in the query language of the database.
`java.lang.String`	`convertToBoolean(java.lang.String fieldReference, boolean invert)` If required, this function modifies a field reference to convert the value of the field to a boolean.
`java.lang.String`	`convertToDate(java.lang.String date)` Returns a valid sql expression to convert the given parameter to a Time data type.
`java.lang.String`	`convertToDate(java.lang.String year, java.lang.String month, java.lang.String day)` Returns a valid sql expression to convert the given parameters to a Date data type.
`java.lang.String`	`convertToDouble(java.lang.String parameter)` Returns a valid sql expression to convert the given parameter to a Double data type.
`java.lang.String`	`convertToInt(java.lang.String parameter)` Returns a valid sql expression to convert the given parameter to a Integer data type.
`java.lang.String`	`convertToString(java.lang.String parameter)` Returns a valid sql expression to convert the given parameter to a String data type.
`java.lang.String`	`convertToTime(java.lang.String time)` Returns a valid sql expression to convert the given parameter to a Time data type.
`java.lang.String`	`convertToTime(java.lang.String hour, java.lang.String minute, java.lang.String sec)` Returns a valid sql expression to convert the given parameters to a Time data type.
`java.lang.String`	`convertToTimeStamp(java.lang.String year, java.lang.String month, java.lang.String day, java.lang.String hour, java.lang.String minute, java.lang.String sec)` Returns a valid sql expression to convert the given parameters to a Timestamp data type.
`protected void`	`findColumnLabels(TableSource tableSource)` Tries to find column labels for the columns of the provided TableSource.
`java.lang.String`	`getAddOp()` Overwrite this method if the used database uses another sign than "+" as add operator.
`java.lang.String`	`getAliasToken()` Returns the SQL keyword that is required to define an alias name in a SQL statement.
`java.sql.ResultSet`	`getColumns(Datasource ds, java.lang.String catalog, java.lang.String owner, java.lang.String tablename)` Overwrite getTables and getColumns if you want to use your own Database class to design a report with i-net Designer.
`protected int`	`getCursorType(int dataType)` Returns the SQL data type for a cursor if the stored procedure parameter is a cursor.
`java.lang.String`	`getIdentifierQuoteString()` Returns the string used to quote SQL identifiers.
`int`	`getMaxAliasNameLength()` Returns the maximum length of the alias name.
`void`	`getReportData(Engine engine, java.lang.String dataSourceConfigurationName)` Delivers the data of the report.
`boolean`	`getReportDataPerInstance()` Override this method only if you use subreport(s) and you will set the data for the report with the getReportData method (useJdbcDriver = false).
`protected java.lang.String`	`getSqlIdentifier(DatabaseTables dbTables, java.lang.String identifier, boolean escapeEverything)` Returns the SQL identifier regarding the settings of `getIdentifierQuoteString()`, escapeEverything and `needQuote(String)`.
`java.sql.ResultSet`	`getTables(Datasource ds, java.lang.String catalog)` Overwrite getTables and getColumns if you want to use your own Database class to design a report with i-net Designer.
`protected void`	`initTransient()` Init transient variables after serialize.
`boolean`	`isEscapeEverything()` Returns the value of the flag escapeEverything.
`boolean`	`isUseOrderBy()` Indicates whether an ORDER BY clause is allowed for the sql statement.
`boolean`	`isUseParenthesiseForJoin()` Indicates whether parentheses are allowed in FROM clause of the sql statement.
`boolean`	`isUseQuoteLowerCase()` Indicates whether table names and column names have to be quoted This property can be necessary for Oracle databases for example, if table/column where created with quotes and lower 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.
`protected boolean`	`needQuote(java.lang.String identifier)` Returns if the supplied SQL identifier needs to be put in quotes.
`protected java.lang.String[]`	`parseSourceName(java.lang.String sqlSource)` Splits the provided name of a SQL source (name of a table, view or stored procedure) into three parts.
`protected java.sql.PreparedStatement`	`prepareStatement(TableSource tableSource)` Returns a PreparedStatement for execution of the stored procedure represented by the provided TableSource.
`protected void`	`scanIdentifierQuoteString(Datasource dc)` This method will detect which String should be used to quote database identifiers for the supplied Datasource.
`void`	`setAliasToken(java.lang.String aliasToken)` This method is for adapt the sql statement for a specific database.
`void`	`setIdentifierQuoteString(java.lang.String quoteString)` This method is for adapt the sql statement for a specific database.
`void`	`setUseEscapeEverything(boolean escapeEvreything)` This method is for adapt the sql statement for a specific database.
`void`	`setUseOrderBy(boolean useOrderBy)` This method is for adapt the sql statement for a specific database.
`void`	`setUseParenthesiseForJoin(boolean useParenthesiseForJoin)` This method is for adapt the sql statement for a specific database.
`void`	`setUseQuoteLowerCase(boolean useQuoteLowerCase)` This method is for adapt the sql statement for a specific database.
`void`	`setUseSQL92syntax(boolean useSQL92syntax)` This method is for adapt the sql statement for a specific database.
`void`	`setUseWhereClause(boolean useWhereClause)` This method is for adapt the sql statement for a specific database.
`java.lang.String`	`sqlConcat(java.lang.Object leftOperant, java.lang.Object rightOperant)` Returns a valid sql statement concatenating two expressions.
`protected java.lang.String`	`toSQL92(java.lang.String function, Field field)` Creates a SQL92 expression with function and one parameter.
`boolean`	`useJdbcDriver()` Override this method to specify if the engine should use a JDBC driver or if you want to use setData().

Constructor Detail

Database

public Database()

Create a default Database class instance.

Method Detail

initTransient

protected void initTransient()

Init transient variables after serialize.

Since:: 8.0

scanIdentifierQuoteString

protected void scanIdentifierQuoteString(Datasource dc)
                                  throws java.sql.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.

Parameters:: dc - The Datasource providing the connection information.
Throws:: java.sql.SQLException - If a SQL error occurred during request of database meta data.; ReportException - If an error occurred during establishing a database connection.
Since:: 8.0

parseSourceName

protected java.lang.String[] parseSourceName(java.lang.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.

Parameters:

sqlSource - The name of the SQL source which may contain catalog or schema qualifier.

Returns:

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)

Since:

8.0

needQuote

protected boolean needQuote(java.lang.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.

Parameters:: identifier - The SQL identifier to be checked.
Returns:: True if the SQL identifier should be put in quotes false otherwise.

getSqlIdentifier

protected java.lang.String getSqlIdentifier(DatabaseTables dbTables,
                                            java.lang.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.

Parameters:: dbTables - The DatabaseTables 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
Returns:: The identifier which can be used in a SQL statement.

prepareStatement

protected java.sql.PreparedStatement prepareStatement(TableSource tableSource)
                                               throws java.sql.SQLException,
                                                      ReportException

Returns a PreparedStatement for execution of the stored procedure represented by the provided TableSource. Usually this will return an instance of java.sql.PreparedStatement. Overriding classes can change the behaviour e.g. returning an instance of java.sql.CallableStatement instead.

Parameters:: tableSource - The TableSource representing a stored procedure to prepare a statement for.
Returns:: The PreparedStatement which will be used by i-net Clear Reports to call the stored procedure.
Throws:: java.sql.SQLException - If a SQL error occurred during creation of the PreparedStatement.; ReportException - If the connection for the report could not be established.
Since:: 8.0

useJdbcDriver

public boolean useJdbcDriver()

Override this method to specify if the engine should use a JDBC driver or if you want to use setData(). Default value is "true".

Returns:: True if a JDBC driver is used false otherwise.

getReportDataPerInstance

public boolean getReportDataPerInstance()

Override this method only if you use subreport(s) and you will set the data for the report with the getReportData method (useJdbcDriver = false). For more informations see the sample Main_and_Subreport_Data_WithExternalResultSet.java.
With this method you can specify if the engine should call getReportData per subreport instance or only once for each of the subreports in the executed report. The method should return true if you want to set the different report data depending on the state of the report or if you want to set data depending on one or more parameter values. If you want to set data only once per sub-report, this function must return false. For example if you have a sub-report taking one prompt p0, and getReportDataPerInstance() returns true, then your sub-report will be called $n$ times whenever the value for p0 has been changed. So if your report has sub-reports and your sub-report prompts are linked with fields of the main report and you want to set the data whenever the sub-report prompts change, let getReportDataPerInstance() return true.

Returns:: true if the engine should invoke getReportData for every subreport instance or false otherwise.

getReportData

public void getReportData(Engine engine,
                          java.lang.String dataSourceConfigurationName)
                   throws ReportException

Delivers the data of the report. This method is called by the engine to get the data of the report.
Normally this method executes a SQL statement to fetch the data from a database.
This method should be overridden in a class that extends from Database when the data of a report should be fetched
from an external ResultSet or Object array using the method setData (without JDBC connection).
Please see the sample files DataWithoutJDBC.java and Main_and_Subreport_Data_WithExternalResultSet.java. You can find these files in the samples directory.
Exceptions that occurs while fetching data need to be thrown. You can make the report engine load your database class by specifying it in the DatasourceConfiguration.

If you want to examine a sub-report (and change some of its values), you can override this method examine the sub-engine, change some values and then call super.getReportData().

Parameters:: engine - The reference to your engine; dataSourceConfigurationName - A string describing the connection configuration
Throws:: ReportException - If an error occurred during data fetching.

getAddOp

public java.lang.String getAddOp()

Overwrite this method if the used database uses another sign than "+" as add operator.

Returns:: The SQL String representation of the add operator.

sqlConcat

public java.lang.String sqlConcat(java.lang.Object leftOperant,
                                  java.lang.Object rightOperant)

Returns a valid sql statement concatenating two expressions. The default implementation is:
leftOperant + getAddOp() + rightOperant
Example implemantation of MySQL:
" concat("+leftOperant+","+rightOperant+") "

Parameters:: leftOperant - the left operand; rightOperant - the right operand
Returns:: A SQL String representation
See Also:: getAddOp()

getColumns

public java.sql.ResultSet getColumns(Datasource ds,
                                     java.lang.String catalog,
                                     java.lang.String owner,
                                     java.lang.String tablename)
                              throws java.sql.SQLException,
                                     ReportException

Overwrite getTables and getColumns if you want to use your own Database class to design a report with i-net Designer.
For that the i-net Designer use this class, the class must be registered in a DatasourceConfiguration in the datasource manager.

In the designer then you can select your data source and design a report on it.
The i-net Designer first call Database.getTables() and then for each table Database.getColumns().

The easiest way to create the ResultSet is by using class DatabaseMetaDataFactory.

      public ResultSet getColumns(Datasource ds, String cat, String own, String tab) throws SQLException {
         if(tab.equalsIgnoreCase("customers"))
                return DatabaseMetaDataFactory.getColumns(new String[]  {"customerid",   "companyname",  "address"},new int[]        {Types.VARCHAR, Types.VARCHAR,  Types.VARCHAR});
         else
            throw new SQLException("unknown table "+tab);
      }

Parameters:: ds - The Datasource containing the Connection.; catalog - The catalog/database set in login dialog of i-net Designer.; owner - The table owner or null.; tablename - The table name.
Returns:: A ResultSet with the structure of DatabaseMetaData.getColumns
Throws:: java.sql.SQLException - This SQLException will be showen in a message box in i-net Designer.; java.sql.SQLException - if a database access error occurs; ReportException - if the creation of a Connection failed.
Since:: 6.1
See Also:: useJdbcDriver(), getColumns(Datasource, String, String, String), DatabaseMetaDataFactory.getTables(String[], String)

getTables

public java.sql.ResultSet getTables(Datasource ds,
                                    java.lang.String catalog)
                             throws java.sql.SQLException,
                                    ReportException

      public ResultSet getTables(Datasource ds, String catalog)throws SQLException {
          return DatabaseMetaDataFactory.getTables(new String[]{"customers"}); 
       }

Parameters:: ds - The current Datasource containg the Connection.; catalog - The catalog/database set in login dialog of i-net Designer.
Returns:: A ResultSet with the structure of DatabaseMetaData.getTables()
Throws:: java.sql.SQLException - if a database access error occurs; ReportException - if the creation of a Connection failed.
Since:: 6.1
See Also:: useJdbcDriver(), getColumns(Datasource, String, String, String), DatabaseMetaDataFactory.getTables(String[], String)

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.

Parameters:: dataType - The original SQL data type for the stored procedure parameter which should be used as cursor.
Returns:: The SQL data type for a cursor.

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.

Returns:: The maximum length of an alias name.
Since:: 6.0

convertToString

public java.lang.String convertToString(java.lang.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+")";

Parameters:: parameter - set in ToText(parameter)
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToInt

public java.lang.String convertToInt(java.lang.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+")";

Parameters:: parameter - set in Int(parameter),Truncate(parameter)
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToDouble

public java.lang.String convertToDouble(java.lang.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+")";

Parameters:: parameter - set in Cdbl(parameter),ToNumber(parameter)
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToTime

public java.lang.String convertToTime(java.lang.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')";

Parameters:: time - parameter set in TimeValue(parameter),CTime(parameter)
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToDate

public java.lang.String convertToDate(java.lang.String date)
                               throws ReportException

Parameters:: date - parameter set in TimeValue(parameter),CTime(parameter)
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToDate

public java.lang.String convertToDate(java.lang.String year,
                                      java.lang.String month,
                                      java.lang.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')"

Parameters:: year - The year of the date to convert.; month - The month of the date to convert.; day - The day of the date to convert.
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToTime

public java.lang.String convertToTime(java.lang.String hour,
                                      java.lang.String minute,
                                      java.lang.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')"

Parameters:: hour - The hour of the time to convert.; minute - The minute of the time to convert.; sec - The second of the time to convert.
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertToTimeStamp

public java.lang.String convertToTimeStamp(java.lang.String year,
                                           java.lang.String month,
                                           java.lang.String day,
                                           java.lang.String hour,
                                           java.lang.String minute,
                                           java.lang.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)"

Parameters:: 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.
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertModOperation

public java.lang.String convertModOperation(java.lang.String dividend,
                                            java.lang.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+")"

Parameters:: dividend - The dividend of the mod operation to convert.; divisor - The divisor of the mod operation to convert.
Returns:: The SQL String representation.
Throws:: ReportException - if that convert function is not possible in the database. In this case the function will be evaluated by i-net Clear Reports.

convertStringToSQLSyntax

public java.lang.String convertStringToSQLSyntax(java.lang.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'''.

Parameters:: 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.
Returns:: The SQL String representation.
Since:: 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.

Returns:: True if the sql statement can contain an ORDER BY clause false otherwise.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), setUseOrderBy(boolean)

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.

Parameters:: useOrderBy - Whether the sql statement can have an ORDER BY clause or not.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), isUseOrderBy()

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.

Returns:: True if parantheses are allowed in the FROM clause false otherwise.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), setUseParenthesiseForJoin(boolean)

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.

Parameters:: useParenthesiseForJoin - Whether the sql statement can have parentheses in FROM clause or not.
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), isUseParenthesiseForJoin()

isUseQuoteLowerCase

public boolean isUseQuoteLowerCase()

Indicates whether table names and column names have to be quoted
This property can be necessary for Oracle databases for example, if table/column where created with quotes and lower case characters.

Returns:: True if lower case column and table name need to be quoted false otherwise.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), setUseQuoteLowerCase(boolean)

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 databases, if table/column where created with quotes and lower case characters.

Parameters:: useQuoteLowerCase - Whether table names and column names have to be quoted.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), isUseParenthesiseForJoin()

isUseSQL92syntax

public boolean isUseSQL92syntax()

Indicates whether the SQL statement can contain the join condition in SQL92 syntax or not.

Returns:: True if the SQL statement can contain the join condition in SQL92 syntax false if not.
Since:: 6.1
See Also:: setUseSQL92syntax(boolean)

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.

Parameters:: useSQL92syntax - Whether the SQL statement can contain the join condition in SQL92 syntax or not.
Since:: 6.1
See Also:: isUseSQL92syntax()

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.

Returns:: True if the database supports WHERE clauses false otherwiese.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), setUseWhereClause(boolean)

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.

Parameters:: useWhereClause - True if the database supports WHERE clauses false otherwiese.
Since:: 6.1
See Also:: Datasource.getDatabase(), DatabaseTables.getDatasourceCount(), isUseWhereClause()

getAliasToken

public java.lang.String 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".

Returns:: The keyword used to define an alias name.
Since:: 6.1
See Also:: setAliasToken(String)

setAliasToken

public void setAliasToken(java.lang.String aliasToken)

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.

Parameters:: aliasToken - The keyword used to define an alias name.
Since:: 6.1
See Also:: getAliasToken()

getIdentifierQuoteString

public java.lang.String getIdentifierQuoteString()

Returns the string used to quote SQL identifiers.

Returns:: The string used to quote SQL identifiers.
Since:: 6.1
See Also:: setIdentifierQuoteString(String)

setIdentifierQuoteString

public void setIdentifierQuoteString(java.lang.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.

Parameters:: quoteString - The string which should be used to quote the SQL identifiers.
Since:: 6.1
See Also:: getIdentifierQuoteString()

isEscapeEverything

public boolean isEscapeEverything()

Returns the value of the flag escapeEverything.

Returns:: True if the complete sql identifier will be quoted false otherwise.
Since:: 6.5
See Also:: setUseEscapeEverything(boolean)

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.

Parameters:: escapeEvreything - True if the complete sql identifier should be quoted false otherwise.
Since:: 6.5
See Also:: isEscapeEverything()

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

Parameters:: tableSource - The TableSource to find the column labels for.
Since:: 8.0

toSQL92

protected java.lang.String toSQL92(java.lang.String function,
                                   Field field)

Creates a SQL92 expression with function and one parameter.

Parameters:: function - the function; field - the parameter field
Returns:: the result expression
Since:: 11.0

convertToBoolean

public java.lang.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.

Parameters:: value - the boolean value to convert
Returns:: the database dependent representation of a boolean
Since:: 11.0

convertToBoolean

public java.lang.String convertToBoolean(java.lang.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.

Parameters:: 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
Returns:: the modified field reference, may be the original one in case the database supports boolean columns
Since:: 11.0

Overview

Package

Class

Use

Deprecated

Help

i-net Clear Reports

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.inet.report Class Database

Database

initTransient

scanIdentifierQuoteString

parseSourceName

needQuote

getSqlIdentifier

prepareStatement

useJdbcDriver

getReportDataPerInstance

getReportData

getAddOp

sqlConcat

getColumns

getTables

getCursorType

getMaxAliasNameLength

convertToString

convertToInt

convertToDouble

convertToTime

convertToDate

convertToDate

convertToTime

convertToTimeStamp

convertModOperation

convertStringToSQLSyntax

isUseOrderBy

setUseOrderBy

isUseParenthesiseForJoin

setUseParenthesiseForJoin

isUseQuoteLowerCase

setUseQuoteLowerCase

isUseSQL92syntax

setUseSQL92syntax

isUseWhereClause

setUseWhereClause

getAliasToken

setAliasToken

getIdentifierQuoteString

setIdentifierQuoteString

isEscapeEverything

setUseEscapeEverything

findColumnLabels

toSQL92

convertToBoolean

convertToBoolean

com.inet.report
Class Database