Jump to content
Changes to the Jaspersoft community edition download ×
  • This documentation is an older version of JasperReports Server Administration Guide. View the latest documentation.

    When adding a data source to JasperReports Server, several things can cause errors. Start by looking at the following general connectivity issues:

    Check that your database server is available and accepting TCP/IP connections from the host where JasperReports Server is installed.
    Check in your RDBMS that the username and password you're using are correct and have access to the selected database.
    Check for firewalls or network connectivity errors.

    Many databases, including MySQL, also require the user grants to include the specific host from which connections are allowed. Otherwise, when testing the JDBC connection, a connection may not be allowed even though the username and password are correct. For more information, refer to the MySQL documentation for setting up users.

    An easy way to test connectivity from the server to the database with a particular user is to use a tool such as SQuirreL or another DB query tool to connect to the database from the host of your JasperReports Server instance.

    Logging JDBC Operations

    You can enable additional logging to help you find the cause of the error. Set any or all of the following loggers in the server settings interface or in the .../WEB-INF/log4j.properties file:

    log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService
    log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.
    JndiJdbcDataSourceService
    log4j.logger.com.jaspersoft.jasperserver.war.action.ReportDataSourceAction
    log4j.logger.com.jaspersoft.commons.datarator.JdbcDataSet
    log4j.logger.com.jaspersoft.jasperserver.war.common.JasperServerUtil
    log4j.logger.com.jaspersoft.commons.semantic.dsimpl.JdbcDataSetFactory
    log4j.logger.com.jaspersoft.commons.semantic.metaapi.impl.jdbc.BaseJdbcMetaDataFactoryImpl
    log4j.logger.com.jaspersoft.jasperserver.war.validation.ReportDataSourceValidator

    JDBC Drivers

    As of JasperReports Server 5.1, JDBC driver management is automated and simplified. JasperReports Server ships with drivers for some databases, as listed in the dialog for creating data sources. If the JDBC driver for your database is not included, the system administrator can easily upload the driver and use it immediately in a data source.

    For instructions on updating JDBC drivers without restarting the server, see Managing JDBC Drivers. For instructions on adding JDBC drivers when using JBoss, see the next section.

    JDBC Drivers on JBoss

    For modularity reasons, the JBoss application server does not allow web applications such as JasperReports Server to change executable files, such as JDBC drivers, on the fly. In order for JDBC drivers to appear in the list of available drivers and be selectable, you must configure JBoss before you use the drivers to create a JDBC data source.

    To add JDBC drivers on JBoss:

    1. Download or copy the JDBC driver JAR file to the .../WEB-INF/lib/ directory.
    2. Edit the jboss-deployment-structure.xml file as described in the following table:

    Configuring JBoss for JDBC drivers

    Configuration File

    .../META-INF/jboss-deployment-structure.xml

    Property

    Description

    resource-root path="<driver>.jar"

    Locate the resource root element for the JDBC driver you added and uncomment it. The name of the JAR file must match exactly the name of the JDBC driver that you upload. You can add a new resource root element if the JDBC driver of your choice is not given in the commented list.

    3. Restart JBoss.

    Database Permissions

    When creating database users, you must ensure that they have the appropriate privileges to access data, as well as permission to connect from the server that JasperReports Server is running on.

    The database user that you specify in your data source definition should have the appropriate select permissions to query the tables within your database for the reports you want to generate.
    If you accept the defaults during installation of JasperReports Server on Linux from an RPM using apt-get, rpm, or yum, the bundled PostgreSQL allows only the user who owns PostgreSQL to connect. Enter the following commands to connect:
    Many databases, including MySQL, also require that the user permissions name the specific host from which connections are allowed. Otherwise, when testing the JDBC connection, a connection may not be allowed even though the user name and password are correct. For example, see the MySQL documentation for setting up users.

    A fairly easy way to test permissions and connectivity is to use a tool such as SquirrelSQL or another DB query tool to connect to the database from the same host as JasperReports Server and to run typical queries against your database.

    JDBC Database URLs

    When you choose a JDBC driver, the data source creation wizard prompts you for the elements of the URL that are required for your database. In some cases, you may need to add certain arguments to the JDBC URL. Ensure that the database URL you entered when defining your JDBC data source is consistent with what is required for your specific database and database driver. The following table gives the default URLs and port numbers and examples of optional arguments supported by the most common databases:

    Database

    Default JDBC Database URL

    PostgreSQL

    jdbc:postgresql://<host>:5432/<db-name>

    MySQL and MariaDB

    jdbc:mysql://<host>:3306/<db-name>?useUnicode=true&characterEncoding=UTF-8

    Ingres

    jdbc:ingres://<host>:II7/<db-name>;CURSOR=READONLY;auto=multi

    Oracle

    jdbc:oracle:thin:@<host>:1521:orcl

    SQL Server

    jdbc:sqlserver://<host>:1433;databaseName=<db-name>;SelectMethod=cursor

    SQL Server jTDS driver

    jdbc:jtds:sqlserver://<host>:1433/<db-name>

    DB2

    jdbc:db2://<host>:50000/<db-name>:driverType=4;currentSchema=<schema-
    name>;fullyMaterializeLobData=true;fullyMaterializeInputStreams=true;
    progressiveStreaming=2;progresssiveLocators=2

    Vertica

    jdbc:vertica://<host>:5433/<db-name>

    Informix

    jdbc:informix-sqli://<host>:1526/<db-name>:INFORMIXSERVER=<server-name>

    Vertica

    jdbc:sybase:Tds:<host>:5433?ServiceName=<service-name>

    SQL Functions with TIBCO JDBC Drivers

    As of JasperReports Server 5.6.1, TIBCO provides a set of JDBC drivers in the installed server. The TIBCO JDBC drivers are based on the Progress DataDirect Connect drivers and support a slightly different SQL syntax than the vendors' drivers. If you see errors like the one below when running reports or creating domains that use scalar functions, you'll need to modify your queries.

    com.jaspersoft.commons.semantic.metaapi.MetaDataException: Cannot execute JDBC Query. java.sql.SQLDataException: [TIBCO]

    [sqlServer JDBC Driver][sqlServer]Conversion failed when converting the varchar value 'Assets' to data type int.

    For example, the following query does not work with the TIBCO JDBC drivers:

    SELECT account_id+account_description AS account_concat from account

    The correct syntax for the TIBCO JDBC drivers is as follows:

    SELECT {fn CONCAT(account_id,account_description)} AS account_concat from account

    For more information, see the Progress DataDirect page on scalar functions.

    JNDI Services on Apache Tomcat

    If you have trouble with a JNDI connection, you need to look at the JNDI definition for your database on your application server. This section gives common issues with JNDI definitions on Apache Tomcat connecting to MySQL. If you use a different application server or database server, refer to its documentation.

    A JNDI connection on Tomcat is defined in two different files. Make sure both have the following information:

    <tomcat>/webapps/jasperserver[-pro]/META-INF/context.xml
    <Resource name="jdbc/<db-name>" auth="Container" type="javax.sql.DataSource"maxActive="100" maxIdle="30" maxWait="10000"username="<db-user>" password="<db-user-password>"driverClassName="org.postgresql.Driver"validationQuery="SELECT 1" testOnBorrow="true"url="jdbc:mysql://<host>:3306/<database>?autoReconnect=true&autoReconnectForPools=true"/>

    <tomcat>/webapps/jasperserver-pro/WEB-INF/web.xml

    Also check the following points:

    Ensure the driver for your database connection is in the <tomcat>/lib folder.
    If you installed JasperServer from a WAR file, Tomcat may have created a separate copy of context.xml in <tomcat>/conf/Catalina/Localhost/jasperserver-pro.xml. See the corresponding section in the troubleshooting appendix of the JasperReports Server Installation Guide.
    See the Apache Tomcat documentation for JNDI datasources.
    For Oracle databases, you may need to specify additional parameters in the context.xml file. For example, in order to support in Oracle, add the following line

    JNDI Services on JBoss

    After defining JNDI Services on the JBoss application server, JasperReports Server does not automatically detect the new services. To use the new JNDI services as data sources in the server, follow these steps:

    1. Define and deploy a JNDI data source in the JBoss administrator console.
    2. Modify the file <jboss>/webapps/jasperserver-pro/WEB-INF/web.xml to include a data source reference to this new JNDI service.
    3. Modify jboss-web.xml to include a reference to this data source.
    4. Because the deployment configuration files such as web.xml were modified, redeploy the JasperReports Server application.

    Now you can define JNDI data source in the repository, as described in JNDI Data Sources.

    JNDI Services on WebLogic

    Follow these steps to configure JasperReports Server to use JNDI data sources with WebLogic:

    1. Append the following definition to the <reference-descriptor> node of WEB-INF/weblogic.xml:
    2. Append the following definition to WEB-INF/web.xml:
    3. In the WebLogic Admin Console, add a datasource with TestDatabase as the JNDI name.
    4. Restart the jasperserver-pro instance using the WebLogic Admin Console.

    Creating a Data Source on SQL Server Using Windows Authentication

    If your database is Microsoft SQL Server and you use Windows Authentication (also called Integrated Security), use the following procedure to create a data source.

    1. Go to the download page for Microsoft SQL Server JDBC Driver 3.0.

    Do not use version 4.0 of this driver; it will not work.

    2. Download and run the self-extracting executable: 1033sqljdbc_3.0.1301.101_enu.exe
    3. Open the extracted folder sqljdbc_3.0enuauth, and then either the x64 or the x86 subfolder, depending on whether your version of Windows is 64-bit or 32-bit, respectively.
    4. Copy the file sqljdbc_auth.dll to the folder your app server automatically searches for DLLs.

    For Tomcat, this is the <tomcat>bin folder.

    5. Restart your app server.
    6. Log into JasperReports Server as and administrator.
    7. Select Create > Data Source from the main menu.
    8. In the Type field, select JDBC Data Source. The page refreshes to show the fields necessary for a JDBC data source.
    9. Enter a name and optional description for your data source.
    10. From the dropdown field, select com.microsoft.sqlserver.jdbc.SQLServerDriver
    11. Enter the database hostname and database name of your SQL Server instance.
    12. In the URL field, add the following string to the end of the generated URL:

    ;integratedSecurity=true

    13. In the User Name field, enter any non-blank string you want, for example “none.”
    14. In the Password field, enter any non-blank string you want, for example “none.”
    15. Set the Time Zone and Save Location fields if necessary.
    16. Click Test Connection and verify that the connection works.
    17. Click Save to save the data source in the repository.

    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...