Problem loading tables from Oracle db into Report Designer

[carlosi]

Hello.

I am having problems loading my database's tables into the Report Designer.

When using an ODBC driver, I can connect to the database, but can't load the tables. I either get an hourglass icon and nothing happens, or I get the following error message.

org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: Select
USERS.USER_ID,
USERS.USERNAME
From
USERS
Where
USERS.USER_ID Between 1321 And 1398
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:254)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:103)
at java.lang.Thread.run(Thread.java:662)
ParentException:
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6986)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3110)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:407)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:250)
at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:103)
at java.lang.Thread.run(Thread.java:662)



When using the native JDBC driver, I can't connect to the database at all, instead getting the following error message:

Error connecting to database [Believe_Oracle_Database] : org.pentaho.di.core.exception.KettleDatabaseException:
Error occured while trying to connect to the database

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Io exception: The Network Adapter could not establish the connection


org.pentaho.di.core.exception.KettleDatabaseException:
Error occured while trying to connect to the database

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Io exception: The Network Adapter could not establish the connection

org.pentaho.di.core.database.Database.normalConnect(Database.java:370)
.
.
.
[100+ lines truncated, all beginning with "java." or "org.pentaho."]
.
.
.
java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Hostname : vpn.giantinternet.com
Port : 1521
Database name : BELIEVE

Anybody has any ideas as to what could be the problem? I have been struggling with this for the past few days, so any help you can give me will be extremely appreciated. Sorry if the problem seems to be an obvious one, but I'm a bit of a newbie at this!

Again, thank you for your time and kind help.

Carlos

[TomS]

Hi Carlos,

regarding your first attempt (using ODBC) I could imagine, that the "USER_ID" column may be not a numeric column... this could cause a kind of exception when using "BETWEEN" clause...

regarding your second attempt (using JDBC) the error message: "Io exception: The Network Adapter could not establish the connection" means that either the host or (more likely) the
database-port (1521) is not reachable.

Since the ODBC-Source seems to have a connection I would suggest to check the following:
* check the ODBC-datasource on your machine (system settings -> administration -> data sources) and look for the TNS-Name (Service-Name) which is used by this ODBC source
then have a look at:
* the tnsnames.ora file of your local oracle client. There you should find the information about which host, port and Oracle-SID is used when accessing a given TNS-Name.

With this information you might want to retry the JDBC Connection...

Cheers,

Tom

[carlosi]

Spot on!

The problem has been fixed and I am now able to load my tables onto the Report Designer.

It was the host information, indeed.

Thanks, Tom, this was really priceless (I had been struggling with this for a while now!).

Kind regards,

C