Database FAQ

With i-net Clear Reports you can use the following datasources:

1. databases to that i-net Clear Reports can create a database connection with JDBC drivers
2. databases to that i-net Clear Reports can create a database connection with ODBC drivers (JDBC-ODBC bridge)
3. databases to that you can create a database connection in your application (You can set the connection for the report with the API)
4. your own datasource (You can set the report data with external ResultSet or object array (see API and samples in ”samples/jdbc”).

You can use the report URL property “catalog” to change the used database in the MS SQL Server or other databases supporting catalogs, e.g.:

http://server:9000/?report=file:c:/MyReport.rpt&catalog=DatabaseName&…

If you use Oracle as database server then you can change the name of the used database service (SID) in the host parameter, e.g.:

http://server:9000/?report=file:c:/MyReport.rpt&host=OracleServerName:port:SID&…

You can find the list of the report URL parameters in the i-net Clear Reports documentation that you can install using the setup or read it online.

If you would like to set the value of these parameters on the server side instead of sending it from client to server then you can use the method checkProperties in the ReportServlet or Report Server (Listener) (if you use it as client-server application).

It is possible to use a connection from the application server's connection pool if you set up a JNDI name for it. The following example shows this for the popular Apache/Tomcat servlet engine:

Assuming you want to connect to the MS SqlServer database (pdssql.dll) and use the Tomcat 4.x servlet engine to obtain the connection. We further assume that you have installed the servlet installation of i-net Clear Reports into the context /reporting and you want to use our JDBC driver i-net OPTA. You can visit the server.xml (located in $TOMCAT_HOME/conf/server.xml) and add the following:

<Context path="/reporting" docBase="reporting" debug="0" reloadable="true" crossContext="true">
	<Logger className="org.apache.catalina.logger.FileLogger"
	prefix="localhost_clearreports_log." suffix=".txt" timestamp="true"/>
 
	<Environment name="maxExemptions" type="java.lang.Integer" value="15"/>
	<Parameter name="context.param.name" value="context.param.value" override="false"/>
 
	<Resource name="jdbc/pdssql.dll" auth="SERVLET"  type="javax.sql.DataSource"/> 
	<ResourceParams name="jdbc/pdssql.dll"> 
		<parameter><name>user</name><value>sa</value></parameter> 
		<parameter><name>password</name><value></value></parameter> 
		<parameter><name>driverClassName</name> 
			<value>com.inet.tds.TdsDriver</value></parameter> 
		<parameter><name>driverName</name> 
			<value>jdbc:inetdae7:YOUR-HOST?database=YOUR-DB</value></parameter> 
	</ResourceParams> 
</Context>

Your JDBC driver should go into the classpath of the Tomcat servlet engine; for example you can extract the classes located in the driver .jar file into the directory $TOMCAT_HOME/classes (if that directory does not exist, just create it). Or you can edit the catalina.bat (or catalina.sh on Unix) startup script to include the driver .jar file into tomcat's global classpath.

If you have another application server, the procedure is equivalent. 

If the connection failed or the name jdbc/pdssql.dll is not bound, then i-net Clear Reports will continue as usual and will use a connection from our i-net Clear Reports connection pool (please see the settings in the used data source configuration for details).

If you receive a “table or view not found” exception because the owner of the table is not included in the sql statement generated by i-net Clear Reports or you want to run the query for a different owner than the one for which the report was designed, please append

&schema=<owner>

at the end of the request URL. For example http://...&schema=SCOTT. Please note the upper-case letters.

Another possibility is to set the owner (and/or other database properties) in the method checkProperties on the servlet/server application side. For more information please refer to the checkProperties API documentation and to the checkProperties samples in the servlet and standalone samples that you can find the in the sample folder of the documentation.

The rendered reports are cached in the i-net Clear Reports server. If the same report with the same report URL parameter values will be requested again within a specific time (EngineCacheTimeout) then i-net Clear Reports use the rendering data from the cache instead of executing the report again.

To get the new executed report you can:

• use the refresh button in the Java viewer,
• change the URL parameter values in the report URL,
• append the parameter cmd=rfsh to the report URL,
  or
• disable the cache with the property “Reload On New Request” using the configuration tool (see cache configuration). If the value is “true” then i-net Clear Reports executes the report each time it is requested from a client.

Before you start, please run the report engine with a real database connection (the tables don't have to exist) and look at the output that the engine has generated:

[ClearReports] sourceType.hasLongNamesInResultSet
[ClearReports] sourceType.mustExecuteInSubReport
[ClearReports] sourceType.dbRowFilter = true
[ClearReports] sourceType.dbSorts = true
[ClearReports] col: 0 name: T1.I type: 6
[ClearReports] col: 1 name: T1.V type: 11
[ClearReports] col: 2 name: T1.N type: 6
[ClearReports] col: 3 name: T2.I type: 6
[ClearReports] col: 4 name: T2.V type: 11
[ClearReports] col: 5 name: T2.N type: 6
[ClearReports] col: 6 name: T3.I type: 6
[ClearReports] col: 7 name: T3.V type: 11
[ClearReports] col: 8 name: T3.N type: 6

The “hasLongNamesInResultSet” specifies that all information about the tables and its columns are hard-coded in the report design and the engine uses this information. This means that the engine can separate T1.I from T2.I. If this flag is not set, then i-net Clear Reports only uses the short column names I and I obtained from the meta-data of the result-set. The other flags and types are irrelevant for this discussion.

The advantage of “hasLongNamesInResultSet” is that the engine can identify all the column names by looking at the design. The disadvantage is that you cannot select the columns in an arbitrary order.

The advantage of “hasLongNamesInResultSet” being switched off is that you can use a SQL statement like “select * from table” and will get correct results. The disadvantage is that you must use column alias statements to separate T1.I from T2.I: select T1.I as “T1.I”, T2.I as “T2.I” which may or may not work with the database you use.

If you look at the engine.setData() and engine.setSql() API, you will see that all setData and setSql() functions require a flag which lets you specify if you want to use the column index from the report design or the column names from the result-set meta-data. We suggest to use the short column names and use column aliases when you have to separate T1.I from T2.I.

You need to add a JDBC driver to the classpath of the JavaVM running i-net Clear Reports and then append the following URL parameters:

&dll=<native>.dll&other-connection-parameter1=...&…

For example: For Oracle you could use our JDBC driver i-net SERO, extract it to the classpath of i-net Clear Reports and append the following parameters to the report URL:

…&dll=pdsora7.dll&host=<host>:1521:<SID>&user=scott&password=tiger

If you don't know what i-net Clear Reports classpath looks like, please enable logging and look for the Java class path property.

No, you do not need to change the report design because with i-net Clear Reports it is possible to use another database and/or another database driver at runtime as at design time.
With the database parameters in the report url (or in the method checkProperties on the server application or servlet) you can change the used dll name (mapped into a JDBC driver), the database host, catalog (if supported by used database), schema, user, password etc.
For more information about the report URL parameters please refer to the i-net Clear Reports documentation that you can install using the setup.

The engine creates a database connection for each report, that means for the main report and each sub report. To increase the performance the engine use a connection pool. The used connections will be added to the connection pool after it has been used. Unused connections will be closed after 10 minutes.

With the property “ConnectionPoolTimeout” in i-net Clear Reports it is possible to reduce the timeout of unused connections so that i-net Clear Reports will close the unused connection earlier. This may degrade the performance because the engine has to open a new connection if no unused connection is in the pool. The Connection Pool Timeout determines how long a connection will be held in the pool before being closed (in minutes). Accessing an open connection is much faster than creating a new one, so we suggest a time of at least 10 minutes. If you are really low on connections you may reduce this to 1 minute, but performance may degrade.

With the property “MaxConnections” you can set the maximum number of connections for one datasource (JDBC url). If the maximum number of connections is exceeded and the login timeout is expired, a SQLException will be thrown. The value of the property “MaxConnections” should be greater than n+1 where n is the number of sub reports in the report.

The length of the table alias names is limited by the database. So sometimes it will be truncated so that the resulting SQL statement will be executed correctly. For each major database vendor default values have been set for the maximum alias name length. These values could differ for some database versions.

The following table will show you the values for the maximum alias name length for different databases.

If you have a database version which supports less than the given alias name length and you have problems that you can not execute reports because of invalid alias names then you should overwrite the particular Database class (e.g. for MS SQL Server com.inet.report.DatabaseSqlServer) and return another value for the method:

int getMaxAliasNameLength()

The problem is that the jdbc-odbc-bridge does not return the result of the stored procedures as required. The value for the Oracle cursor which contains the real ResultSet is not returned. We recommend to use an specific Oracle JDBC driver. We also provide own JDBC driver which can be found at our homepage.

If you use the Engine API instead of the com.inet.report.ReportServlet you need to do the lookup for the datasources by yourself and then use Engine.setConnection to set the connection for the report.
In this case the engine cannot make the lookup because the lookup need to be in the main thread.

This happens if you are using the original Oracle JDBC driver. Some driver versions return wrong meta data information about the column type. The expected types would be java.sql.Types.TIMESTAMP but java.sql.Types.DATE is returned. That's why i-net Clear Reports will work with the date part only. So the time part will be lost.

A workaround start i-net Clear Reports with the JVM parameter ”-Doracle.jdbc.V8Compatible=true” or you set the system property “oracle.jdbc.V8Compatible” to “true” before the Oracle JDBC driver has been registered to the java.sql.DriverManager. Please note that this is a workaround for Oracle JDBC driver 10g only.

You don't have this problem if you're using an Oracle JDBC driver of i-net software such as i-net ORANXO.