Domain Designer Not Showing Tables

[skkane_vsdl]

Hi,

  New here and currently using the evaluation version of JasperServer 3.1.0.1.    I have successfully created a new DataSource using JDBC pointing to Oracle.  I try and create a new domain and Launch the Domain Designer.  In that I select the new data source and I am able to select the appropriate schema from the list.  I proceed to close the Manage Data Source window and the icon in the Choose tables frame appears to be working but then showing nothing. 

Am I missing something here?  I assume that since I am able to pick the Oracle schema that that JDBC connection is working.  Maybe in the eval version you can't do this.  Can anyone please help.  Thanks.

 

-Sean

[skkane_vsdl]

We have completely reinstalled it and redid the point to Oracle.  We have the same problem but we did find an error in the log.

java.lang.NullPointerException at com.jaspersoft.ji.semantic.action

 

I've seen other posts that had the same issue with tables not showing up during domain creation but those never receiced any response either.  

-Sean

[andrewsok]

Could you attach the stack trace from your log, please?

Do you have Recycle Bin option enabled in your Oracle, and if so, could you make sure you have exclude filter for BIN$** tables in jdbcMetaConfiguration bean of applicationContext-semanticLayer.xml configuration file?

Do you use synonyms? Oralce driver does not show columns by default for synonym tables, this issue will be fixed in next release of JasperServer Pro (coming soon).

[rajukvg]

I am trying the same with some other database. (Vertica). I have the same problem too. Posted some message and waiting for response. I dnt think this is an Oracle specific.

 

Regards,

Raju

[andrewsok]

My experience with Vertica was that their JDBC v.2.1 driver has a bug.

If you get DatabaseMetaData object and call getTables method on it, and pass an array of table types, it returns nothing unless you request just "TABLE" type and nothing else. If you add "VIEW", "ALIAS", etc., it stops returning even tables.

Therefore, to make Domain Designer work , you need to edit jdbcMetaConfiguration bean in applicationContext-semanticLayer.xml file and comment out all table types but <value>TABLE</value>.

[skkane_vsdl]

andrewsok
Wrote:
 

Could you attach the stack trace from your log, please? Do you have Recycle Bin option enabled in your Oracle, and if so, could you make sure you have exclude filter for BIN$** tables in jdbcMetaConfiguration bean of applicationContext-semanticLayer.xml configuration file? Do you use synonyms? Oralce driver does not show columns by default for synonym tables, this issue will be fixed in next release of JasperServer Pro (coming soon).

Hi,

  Thanks so much for the response.  The second attachment is the stack trace associated with the error we are receiving.  Yes, we have the Recycle Bin option in place for Oracle.  The first attachment is a snipet of the config you referenced which shows that the BIN filter is in place.  We use synonyms but not for the schema I selected.  I selected the schema that owns the tables.

[skkane_vsdl]

We also realized that all the defined data sources that come with the eval version are not working either.  So we don't think it is a Oracle specifc issue.  After looking through the stack we saw "   at $Proxy17.resume(Unknown Source)".   It appears that maybe the proxy is messing us up.   A co-worker was able to by-pass the proxy and it appeared to work.  So now we have to investigate this piece of it.  Thanks again for the response.  Hopefully this will help others that may face the same issue.

-Sean

[andrewsok]

Not working sample datasources does not sound right.. Few questions on it:

1) Do you have FOODMART and SUGARCRM schemas created and populated with data?

2) Did you make sure JNDI datasources "foodmart" and "sugarcrm" are set up properly (driver, url. user name and password)?

3) Did you make sure JDBC datasources (repository path /Data Sources and /Analysis Components/Analysis Data Sources) are set up properly? You should be able to enter Edit mode, click Test Connection and see Test successful

[skkane_vsdl]

andrewsok
Wrote:
 

Not working sample datasources does not sound right.. Few questions on it: 1) Do you have FOODMART and SUGARCRM schemas created and populated with data? 2) Did you make sure JNDI datasources "foodmart" and "sugarcrm" are set up properly (driver, url. user name and password)? 3) Did you make sure JDBC datasources (repository path /Data Sources and /Analysis Components/Analysis Data Sources) are set up properly? You should be able to enter Edit mode, click Test Connection and see Test successful

Yes to all of the questions above.  Maybe I wasn't clear in what I stated.  The data sources all worked properly and the domains all worked properly.  However, if I wanted to create a new domain using any data source it will not work when you initially logon and there is a proxy server redirect.  If I hit the server directly with no proxy then everything appears to work fine but once the proxy is there then I get the NullPointerException.  I've talked with other groups where I work and they all just hit the server directly.  It is possible we have a setting wrong somewhere but we are just doing some port forwarding now to get around this issue.

[andrewsok]

Oh, you mean, proxy server, not proxy class from the stack trace.

Yeah, it might be the case where proxy server intrudes into AJAX request parameters and messes them up.

[linvicch]

I receive this error,

At present time tables which doesn't contain any columns are not supported.

The table for the domain is a synonym on oracle 10g.

From the thead above this seem to be a bug. we are using Jasperserver pro 3.5(oem). Is this issue fixed yet?

No event triggered scheduling and if it doesn't even support synonyms, its time to look at other options >.<

 

 

 

 

 

 

[andrewsok]

Because Oracle decided to not show columns on synonym tables by default (JDBC driver performs very slow on this operation), you have to enable it in the configuration before using:

applicationContext-semanticLayer.xml:

...

<property name="includeSynonymsForOracle">

<!-- set 'true' if you use Oracle synonyms in Domains -->

<value>false</value>

</property>

 

Set the property vale to "true" and restart the server.

 

[linvicch]

By slow in performance, what will that affect?

Does it affect the performance of future query or only on initial setup?

 

Thank you :D

karma deserving :p

 

 

 

 

Post Edited by linvicch at 02/17/2010 02:07

[linvicch]

Hi

As per instructed, i have modified

<property name="includeSynonymsForOracle">
            <!-- set 'true' if you use Oracle synonyms in Domains  -->
            <value>true</value>
        </property>
 

my synonyms table works BUT

 NOW my non Synonyms table doesn't work throwing me the error

At present time tables which doesn't contain any columns are not supported.

Following empty tables will not be added

 

 

 

[andrewsok]

linvicch
Wrote:
 

By slow in performance, what will that affect? Does it affect the performance of future query or only on initial setup?

This is about getting metadata (table/column names/types) performance.

It does not affect query execution performance but slows down the how system resonses to user in domain designer where JasperServer has to wait for oracle JDBC driver. For example, user expands table node to see columns, and it may easily take 5 seconds to open it, and all time is spent in the driver method (actually, in DB itself).

[andrewsok]

linvicch
Wrote:

my synonyms table works BUT  NOW my non Synonyms table doesn't work throwing me the error At present time tables which doesn't contain any columns are not supported. Following empty tables will not be added

I cannot reproduce this on my 10.2.0.1.0..

Could you provide as much info as you can, such as

- DB version

- JDBC driver version

- create table and synonym scripts

- possibly related messages in JS log

[linvicch]

Hi Andrew, thank you for taking time in assisting us :)

jasperserver 3.5 pro (OEM)

oracle 10g

ojdbc14-10.2.0

apache-tomcat-5.5.28

the driver is placed on the server level. would that make a difference? -> common/lib

I am 100% sure the synonyms and table are correctly created as they are currently in used by our reports.

There is nothing logged on that error as well.. the last log was

10:32:52,357  WARN LoggerListener,http-8080-Processor19:55 - Authentication event AuthenticationSuccessEvent: jasperadmin; details: com.jaspersoft.jasperserver.multipleTenancy.MTWebAuthenticationDetails@fffc7f0c: RemoteIpAddress: 172.16.5.35; SessionId: D4225850F8A690C60743F279CD24B5F9
10:32:52,359  WARN LoggerListener,http-8080-Processor19:55 - Authentication event InteractiveAuthenticationSuccessEvent: jasperadmin; details: com.jaspersoft.jasperserver.multipleTenancy.MTWebAuthenticationDetails@fffc7f0c: RemoteIpAddress: 172.16.5.35; SessionId: D4225850F8A690C60743F279CD24B5F9
 

 

[andrewsok]

I am now sure what your developer skills are, but let me try...

To separate JS issues from Oracle issues, could you take the attached zip, unpack, comlipe and run the sample app against your oracle with your driver, and see it if shows all synonym tables and fields properly? This way we will make sure JDBC works as it is supposed to.

The line setIncludeSynonyms(true) is what we call when you set the config option into true.

[linvicch]

Hi Andrew,

The script ran fine and I can see all tables irregardless of setIncludeSynonyms(true) or setIncludeSynonyms(false).  When i add my domain, I can see all the tables but the issue only arise when I drag a synonym table from the left to the right. Thats when the error appear

"At present time tables which doesn't contain any columns are not supported.Following empty tables will not be added"

 

. This piece of code seem to be used to display the tables.

Would you be able to send me the code that listens to the event when a user drag a table from the left selection to the right?

[andrewsok]

Actually, the point of exercise was to see fields, not tables. The setIncludeSynonyms(true) affects only fields.

Example of properly working driver :

TABLE1
  FIELD1
  FIELD2 
TABLE2
  FIELD1
  FIELD2   

Example of problematic driver :

TABLE1
TABLE2
 

In later case fields do not show up.

When you ran the mini app, did you see fields?

 

[linvicch]

Hi Andrew,

There is error with displaying the fields and the except thrown is

From reading the code I think it should print out all table names  then all the fields following

 

table1

table2

table1.field1

table1.field2

table2.field1

table2.field2

 

my result are as followed

T_ASSET_DATA_SOURCE
T_ASSET_INSURANCE_FORMULA
T_ASSET_ITEM_TYPE
T_ASSET_RISK
 

So you believe the issue lies with the driver?

 

Exception in thread "main" java.sql.SQLException: ORA-01424: missing or illegal character following the escape character

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:744)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:218)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:969)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1071)
        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:853)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1153)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3369)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3414)
        at oracle.jdbc.driver.OracleDatabaseMetaData.getColumns(OracleDatabaseMetaData.java:273)
        at test.GetMetaData.main(GetMetaData.java:68)

 

 

[andrewsok]

Here is result on my sample tables:

SYN_CLOBTABLE
  ID : 3 : NUMBER
  TEXT : 2005 : CLOB
SYN_MZHOU
  ID : 3 : NUMBER
  COL1 : 1111 : NCHAR
  COL2 : 1111 : NVARCHAR2
  COL3 : 12 : VARCHAR2
  COL4 : 12 : VARCHAR2

It looks like your result does not show fields. By exception you get, it might be the case where you have some special characters in the field names which are not compatble with JDBC drver. Unfortunatelly, JDBC is the only way JS may get the metainfo from the DB, and if driver does not support something, nothing we can do on JS side...

[linvicch]

Hi Andrew,

I have made some changes as well as debug the except. now everything works and I can see all tables and fields, synonyms or non-synonyms. Changed the treeset to a linkedlist and create a filter on the table names where the name != "/". This implies that the driver is working fine?

 

Connected!
AC_ACCOUNT
  ID : 3 : NUMBER
  ID : 3 : NUMBER
  CONTACT_ID : 3 : NUMBER
  CONTACT_ID : 3 : NUMBER
  UPDATE_USER : 3 : NUMBER
  UPDATE_USER : 3 : NUMBER
  UPDATE_DATE : 91 : DATE
  UPDATE_DATE : 91 : DATE
  UPDATE_VERSION : 3 : NUMBER
  UPDATE_VERSION : 3 : NUMBER
  ACCOUNT_TYPE : 3 : NUMBER
  ACCOUNT_TYPE : 3 : NUMBER
  CURRENCY_ID : 3 : NUMBER
  CURRENCY_ID : 3 : NUMBER
  RESERVED_VALUE_FLAG : 3 : NUMBER
  RESERVED_VALUE_FLAG : 3 : NUMBER
  AGENT_NUMBER : 12 : VARCHAR2
  AGENT_NUMBER : 12 : VARCHAR2
  BANK_ACCOUNT_ID : 3 : NUMBER
  BANK_ACCOUNT_ID : 3 : NUMBER
  PAYMENT_PLAN_ID : 3 : NUMBER
  PAYMENT_PLAN_ID : 3 : NUMBER
  PAYMENT_DAY : 3 : NUMBER
  PAYMENT_DAY : 3 : NUMBER
  BRAND_COMPANY_ID : 3 : NUMBER
  BRAND_COMPANY_ID : 3 : NUMBER
  POLICY_HEADER_ID : 3 : NUMBER
  POLICY_HEADER_ID : 3 : NUMBER
  CLAIM_ID : 3 : NUMBER
  CLAIM_ID : 3 : NUMBER
  GL_ACCOUNT_ID : 3 : NUMBER
  GL_ACCOUNT_ID : 3 : NUMBER
  FREEZE_DATE : 91 : DATE
  FREEZE_DATE : 91 : DATE
  FREEZE_START_DATE : 91 : DATE
  FREEZE_START_DATE : 91 : DATE
  MAIN_AGENT_ACCOUNT_ID : 3 : NUMBER
  MAIN_AGENT_ACCOUNT_ID : 3 : NUMBER
  HANDLING_CONTACT_ID : 3 : NUMBER
  HANDLING_CONTACT_ID : 3 : NUMBER
  HANDLING_ACCOUNT_ID : 3 : NUMBER
  HANDLING_ACCOUNT_ID : 3 : NUMBER
  DEFAULT_ENTRY_STATUS_ID : 3 : NUMBER
  DEFAULT_ENTRY_STATUS_ID : 3 : NUMBER
  PAYMENT_DELAY_POINT : 3 : NUMBER
  PAYMENT_DELAY_POINT : 3 : NUMBER
  PAYMENT_DELAY : 3 : NUMBER
  PAYMENT_DELAY : 3 : NUMBER
  PAYMENT_TYPE : 3 : NUMBER
  PAYMENT_TYPE : 3 : NUMBER
  MASTER_POLICY_HEADER_ID : 3 : NUMBER
  MASTER_POLICY_HEADER_ID : 3 : NUMBER
  EXTERNAL_NUMBER : 12 : VARCHAR2
  EXTERNAL_NUMBER : 12 : VARCHAR2
  IS_LOCKED_BY_BILLING_PROC : 3 : NUMBER
  IS_LOCKED_BY_BILLING_PROC : 3 : NUMBER
  WH_TRANSFER_ID : 3 : NUMBER
  WH_TRANSFER_ID : 3 : NUMBER
AC_ACCOUNT_RACI
  ID : 3 : NUMBER
  ID : 3 : NUMBER
  LAST_STATEMENT_RUN_DATE : 91 : DATE
  LAST_STATEMENT_RUN_DATE : 91 : DATE
  WH_TRANSFER_ID : 3 : NUMBER
  WH_TRANSFER_ID : 3 : NUMBER
  ID : 3 : NUMBER
  LAST_STATEMENT_RUN_DATE : 91 : DATE
  WH_TRANSFER_ID : 3 : NUMBER
AC_AGENT_DETAIL
  ID : 3 : NUMBER
  ID : 3 : NUMBER
  ACCOUNT_ID : 3 : NUMBER
  ACCOUNT_ID : 3 : NUMBER
 

 

  public static void main(String[] args) throws Exception {


        if (true) {

            readProperties("H:\\oracle_synonyms\\oracle.properties");
        } else {
            System.out.println("No Property file !");
            System.exit(1);
        }

        Class driverClass = Class.forName(driver);
        Connection conn = DriverManager.getConnection(connStr, user, pass);

        // Oracle specific
        if (true) {
            ((oracle.jdbc.OracleConnection) conn).setIncludeSynonyms(true);
        }

        DatabaseMetaData md = conn.getMetaData();

        System.out.println("Connected!");

        List<String> tables = new LinkedList<String>();
        {
            ResultSet rs = null;
            try {
                rs = md.getTables(null, schema, tablepattern, TABLE_TYPES);
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    //System.out.println(tableName);
                    tables.add(tableName);
                }

            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (Exception ex) {
                    }
                }
            }
        }

        for (String tableName : tables) {
            if (tableName.indexOf("/")==-1) {
                System.out.println(tableName);

                ResultSet rs = null;
                try {

                    rs = md.getColumns(null, schema, tableName, null);

                    while (rs.next()) {

                        String colName = rs.getString("COLUMN_NAME");
                        int typeCode = rs.getInt("DATA_TYPE");
                        String dbVendorType = rs.getString("TYPE_NAME");

                        System.out.println("  " + colName + " : " + typeCode + " : " + dbVendorType);
                    }

                } finally {
                    if (rs != null) {
                        try {
                            rs.close();
                        } catch (Exception ex) {
                        }
                    }
                }
            }
        }
        conn.close();
    }