public class Database extends JdbcData implements java.io.Serializable, com.inet.report.database.sql.SqlSyntax
We first describe how data for the main report is fetched:
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.Constructor and Description |
---|
Database()
Create a default Database class instance.
|
Modifier and Type | Method and Description |
---|---|
java.lang.String |
convertIdentifier(Datasource ds,
java.lang.String identifier)
Checks whether an identifier can be used right away or has to be quoted due to invalid characters or keywords.
|
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.
|
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.
|
java.lang.String |
getAddOp()
Overwrite this method if the used database uses another sign than "+" as add operator.
|
java.lang.String |
getAliasToken()
Deprecated.
As of i-net Clear Reports 13.1, replaced by getConfiguration().getAliasToken()
|
protected java.lang.String |
getColumnName(java.lang.String colName,
java.lang.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.
|
java.lang.String |
getIdentifierQuoteString()
Returns the string used to quote SQL identifiers.
|
int |
getMaxAliasNameLength()
Returns the maximum length of the alias name.
|
protected java.sql.ResultSet |
getProcedureColumns(Datasource ds,
java.lang.String catalog,
java.lang.String schema,
java.lang.String procedure)
Is used from
#getColumns(TableSource) if the TableSource based on a stored procedure. |
protected java.sql.ResultSet |
getProcedures(Datasource ds,
java.lang.String catalog)
Is used from
#getTableSourceInfos(Datasource, String) to find a list of stored procedures. |
void |
getReportData(Engine engine,
java.lang.String dataSourceConfigurationName)
Deprecated.
As of i-net Clear Reports 15.0, instead implement the method
fetchData(Engine, FetchTables, DataCollector) |
protected java.lang.String |
getSourceNameWithChange(TableSource ts,
boolean escapeEverything)
Returns the source name like catalog.schema.table.
|
protected java.lang.String |
getSqlIdentifier(Datasource ds,
java.lang.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 java.sql.ResultSet |
getTables(Datasource ds,
java.lang.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 |
isEscapeEverything()
Returns the value of the flag escapeEverything.
|
protected boolean |
isKeyword(java.lang.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.
|
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
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.
|
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 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)
Deprecated.
As of i-net Clear Reports 13.1, replaced by getConfiguration().setAliasToken(String)
|
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.
|
java.lang.String |
toSQL92(java.lang.String function,
Field field)
Creates a SQL92 expression with function and one parameter.
|
boolean |
useJdbcDriver()
Deprecated.
As of i-net Clear Reports 13.0, extends from the interface
DataFactory . Use DatabaseUtils.useJdbcDriver(DataFactory) instead. |
getColumns, getReportDataPerInstance, getTableSourceData, getTableSourceInfos
getColumns, getConfiguration, getTableSourceData, getTableSourceData, setConfiguration
protected void initTransient()
protected void scanIdentifierQuoteString(Datasource dc) throws java.sql.SQLException, ReportException
DatabaseMetaData.getIdentifierQuoteString()
. The identifier
quote string detected should be applied to this Database using
setIdentifierQuoteString(String)
.dc
- The Datasource providing the connection information.java.sql.SQLException
- If a SQL error occurred during request of database
meta data.ReportException
- If an error occurred during establishing a
database connection.protected java.lang.String[] parseSourceName(java.lang.String sqlSource)
[[catalog.]schema.]name
.
The array returned contains these three parts each part set to
null
if not set.sqlSource
- The name of the SQL source which may contain catalog
or schema qualifier.null
if no catalog is setnull
if no schema is setnull
)protected java.lang.String getColumnName(java.lang.String colName, java.lang.String alias, int driverVersion, TableSource ts, int colIdx)
getColumnName
in class JdbcData
colName
- the column namealias
- the column alias (required for MySQL Connector5.x only)driverVersion
- the driver version (required for MySQL Connector5.x only)ts
- the table sourcecolIdx
- the column index (required for Informix only)protected boolean needQuote(java.lang.String identifier)
identifier
- The SQL identifier to be checked.protected boolean isKeyword(java.lang.String identifier)
identifier
- the identifierprotected java.lang.String getSqlIdentifier(Datasource ds, java.lang.String identifier, boolean escapeEverything)
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
.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 otherwiseprotected java.lang.String getSourceNameWithChange(TableSource ts, boolean escapeEverything)
getSourceNameWithChange
in class JdbcData
ts
- the table sourceescapeEverything
- True if the complete sql identifier should be quoted false otherwise@Deprecated public boolean useJdbcDriver()
DataFactory
. Use DatabaseUtils.useJdbcDriver(DataFactory)
instead.@Deprecated public void getReportData(Engine engine, java.lang.String dataSourceConfigurationName) throws ReportException
fetchData(Engine, FetchTables, DataCollector)
It is strongly recommended to use fetchData(Engine, FetchTables, DataCollector)
instead. In particular, it is not supported to use the optimization with JdbcData.getReportDataPerInstance()
=false
when data fetching is implemented by this method!
engine
- The reference to your enginedataSourceConfigurationName
- A string describing the connection configuration of the first datasourceReportException
- If an error occurred during data fetching.public java.lang.String getAddOp()
public java.lang.String sqlConcat(java.lang.Object leftOperant, java.lang.Object rightOperant)
leftOperant + getAddOp() + rightOperant
" concat("+leftOperant+","+rightOperant+") "
leftOperant
- the left operandrightOperant
- the right operandgetAddOp()
protected java.sql.ResultSet getTables(Datasource ds, java.lang.String catalog) throws java.sql.SQLException, ReportException
#getTableSourceInfos(Datasource, String)
to find a list of tables and views.getTables
in class JdbcData
ds
- The current Datasource containing the Connection.catalog
- The catalog/database set in login dialog of i-net Designer.java.sql.SQLException
- if a database access error occursReportException
- if the creation of a Connection failed.protected java.sql.ResultSet getProcedures(Datasource ds, java.lang.String catalog) throws java.sql.SQLException, ReportException
#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)
getProcedures
in class JdbcData
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 searchjava.sql.SQLException
- if a database access error occursReportException
- if creating a Connection failed.protected java.sql.ResultSet getProcedureColumns(Datasource ds, java.lang.String catalog, java.lang.String schema, java.lang.String procedure) throws java.sql.SQLException, ReportException
#getColumns(TableSource)
if the TableSource based on a stored procedure.getProcedureColumns
in class JdbcData
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.java.sql.SQLException
- This SQLException will be show in a message box in i-net Designer.ReportException
- if the creation of a Connection failed.protected int getCursorType(int dataType)
dataType
- The original SQL data type for the stored procedure
parameter which should be used as cursor.public int getMaxAliasNameLength()
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.public java.lang.String convertToString(java.lang.String parameter) throws ReportException
return "{fn CONVERT( "+parameter+", SQL_CHAR ) }";
return "Cstr("+parameter+")";
parameter
- set in ToText(parameter)ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToInt(java.lang.String parameter) throws ReportException
return "{fn CONVERT( "+parameter+", SQL_INTEGER ) }";
return "Cint("+parameter+")";
parameter
- set in Int(parameter),Truncate(parameter)ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToDouble(java.lang.String parameter) throws ReportException
return "{fn CONVERT( "+parameter+", SQL_DOUBLE ) }";
return "Cdbl("+parameter+")";
parameter
- set in Cdbl(parameter),ToNumber(parameter)ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToTime(java.lang.String time) throws ReportException
return "{fn CONVERT( "+parameter+", SQL_TIME ) }";
return "to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')";
time
- parameter set in TimeValue(parameter),CTime(parameter)ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToDate(java.lang.String date) throws ReportException
return "{fn CONVERT( "+parameter+", SQL_TIME ) }";
return "to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')";
date
- parameter set in TimeValue(parameter),CTime(parameter)ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToDate(java.lang.String year, java.lang.String month, java.lang.String day) throws ReportException
"convert(datetime, right( '0000' + convert(varchar,"+year+"),4) + right( '00' + convert(varchar,"+month+"), 2) + right( '00' + convert(varchar,"+day+"), 2))"
"to_date("+year+"||'/'||"+month+"||'/'||"+day+",'YYYY/mm/dd')"
year
- The year of the date to convert.month
- The month of the date to convert.day
- The day of the date to convert.ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertToTime(java.lang.String hour, java.lang.String minute, java.lang.String sec) throws ReportException
"convert(datetime, right( '00' + convert(varchar,"+hour+"),2)+ ':'+ right( '00' + convert(varchar,"+minute+"), 2) +':'+ right( '00' + convert(varchar,"+sec+"),2),8)"
"to_date('1970-01-01 '||"+hour+"||'/'||"+minute+"||'/'||"+sec+",'yyyy-mm-dd HH24/MI/SS')"
hour
- The hour of the time to convert.minute
- The minute of the time to convert.sec
- The second of the time to convert.ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.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
"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)"
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.ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertModOperation(java.lang.String dividend, java.lang.String divisor) throws ReportException
dividend+"%"+divisor
Example solution for Oracle Server:"mod("+dividend+","+divisor+")"
dividend
- The dividend of the mod operation to convert.divisor
- The divisor of the mod operation to convert.ReportException
- if that convert function is not possible in the database.
In this case the function will be evaluated by i-net Clear Reports.public java.lang.String convertStringToSQLSyntax(java.lang.String s, boolean isFunctionParam)
hello
will transformed to 'hello'
.Hello 'World'
will transformed to 'Hello ''World'''
.Hello 'World'
will transformed to N'Hello ''World'''
.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.public boolean isUseOrderBy()
Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
setUseOrderBy(boolean)
public void setUseOrderBy(boolean useOrderBy)
useOrderBy
- Whether the sql statement can have an ORDER BY clause or not.Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
isUseOrderBy()
public boolean isUseParenthesiseForJoin()
Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
setUseParenthesiseForJoin(boolean)
public void setUseParenthesiseForJoin(boolean useParenthesiseForJoin)
useParenthesiseForJoin
- Whether the sql statement can have parentheses in FROM clause or not.Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
isUseParenthesiseForJoin()
public boolean isUseQuoteLowerCase()
Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
setUseQuoteLowerCase(boolean)
public void setUseQuoteLowerCase(boolean useQuoteLowerCase)
useQuoteLowerCase
- Whether table names and column names have to be quoted.Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
isUseParenthesiseForJoin()
public boolean isUseSQL92syntax()
setUseSQL92syntax(boolean)
public void setUseSQL92syntax(boolean useSQL92syntax)
useSQL92syntax
- Whether the SQL statement can contain the join condition in SQL92 syntax or not.isUseSQL92syntax()
public boolean isUseWhereClause()
Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
setUseWhereClause(boolean)
public void setUseWhereClause(boolean useWhereClause)
useWhereClause
- True if the database supports WHERE clauses
false otherwiese.Datasource.getDataFactory()
,
DatabaseTables.getDatasourceCount()
,
isUseWhereClause()
@Deprecated public java.lang.String getAliasToken()
setAliasToken(String)
@Deprecated public void setAliasToken(java.lang.String aliasToken)
aliasToken
- The keyword used to define an alias name.getAliasToken()
public java.lang.String getIdentifierQuoteString()
setIdentifierQuoteString(String)
public void setIdentifierQuoteString(java.lang.String quoteString)
"
character.quoteString
- The string which should be used to quote the SQL
identifiers.getIdentifierQuoteString()
public boolean isEscapeEverything()
setUseEscapeEverything(boolean)
public void setUseEscapeEverything(boolean escapeEvreything)
"atable"
in catalog "aCatalog"
.select ... from "aCatalog.aTable"
escapeEvreything
- True if the complete sql identifier should
be quoted false otherwise.isEscapeEverything()
protected void findColumnLabels(TableSource tableSource)
TableSource.getDatasource().getConnection()
)
If any column label is found overriding classes should update the
database field
with the
column lable using DatabaseField.setColumnLabel(String)
.tableSource
- The TableSource to find the column labels for.public java.lang.String toSQL92(java.lang.String function, Field field) throws ReportException
function
- the functionfield
- the parameter fieldReportException
- the data access failedpublic java.lang.String convertToBoolean(boolean value)
value
- the boolean value to convertpublic java.lang.String convertToBoolean(java.lang.String fieldReference, boolean invert)
fieldReference
- the plain field reference for the column which is supposed to contain a boolean valueinvert
- set whether to invert the value of the fieldpublic java.lang.String convertIdentifier(Datasource ds, java.lang.String identifier)
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
public com.inet.report.database.sql.SqlSyntax getSqlSyntax()
getSqlSyntax
in class JdbcData
public void fetchData(Engine engine, FetchTables fetchTables, DataCollector dataCollector) throws ReportException
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 );
}
fetchData
in interface DataFactory
fetchData
in class BaseDataFactory
engine
- the engine of the report to renderfetchTables
- a model of joining table describing the data this data factory needs to deliverdataCollector
- the collector which receives any resulting dataReportException
- on failures during data fetchingCopyright © 1999-2020 by i-net software GmbH