Invalid object name 'pm_hive_data'

[Pat B]

Hi,

I finally managed to not get the error "pm cell is down" after installing the Workbench in another computer, Windows 7. I left the SQL Server database in my Windows 8 computer but tested that I can connect to it from the Windows 7 pc using Java code and the sqljdbc4.jar. It works.

I don't know why the i2b2 wiki install guide says to try username "demo" which I was not instructed to create, but even when I use the username "i2b2pm" I get the same error. See JBoss server log below and pm-ds.xml, both from the Windows 7 machine.

The pm-ds.xml file:

<datasource jta="false" jndi-name="java:/PMBootStrapDS"
        pool-name="PMBootStrapDS" enabled="true" use-ccm="false">
        <connection-url>jdbc:sqlserver://WIN8_SQL_SERVER:1433</connection-url>
        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
        <driver>sqljdbc4.jar</driver>
        <security>
            <user-name>i2b2pm</user-name>
            <password>demouser</password>
        </security>
        <validation>
            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
            <validate-on-match>false</validate-on-match>
        </validation>
        <statement>
            <share-prepared-statements>false</share-prepared-statements>
        </statement>
    </datasource>

The JBOSS server.log file:

14:32:00,789 ERROR [edu.harvard.i2b2.pm.dao.PMDbDao] (Thread-74) StatementCallback; bad SQL grammar [select * from pm_hive_data where status_cd <> 'D']; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'pm_hive_data'.
14:32:00,789 ERROR [stderr] (Thread-74) org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select * from pm_hive_data where status_cd <> 'D']; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'pm_hive_data'.

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)

14:32:00,789 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:187)

14:32:00,789 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.dao.PMDbDao.getEnvironment(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.delegate.ServicesHandler.execute(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.ws.ExecutorRunnable.run(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at java.lang.Thread.run(Thread.java:745)

14:32:00,805 ERROR [stderr] (Thread-74) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'pm_hive_data'.

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

14:32:00,805 ERROR [stderr] (Thread-74)     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)

14:32:00,805 ERROR [stderr] (Thread-74)     at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:340)

14:32:00,805 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:443)

14:32:00,805 ERROR [stderr] (Thread-74)     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)

14:32:00,805 ERROR [stderr] (Thread-74)     ... 7 more

14:32:00,805 ERROR [stderr] (Thread-74) edu.harvard.i2b2.common.exception.I2B2DAOException: Database error

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.dao.PMDbDao.getEnvironment(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.delegate.ServicesHandler.execute(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.ws.ExecutorRunnable.run(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at java.lang.Thread.run(Thread.java:745)

14:32:00,805 ERROR [edu.harvard.i2b2.pm.delegate.ServicesHandler] (Thread-74) Database error in getting environment data
14:32:00,805 ERROR [stderr] (Thread-74) java.lang.Exception: Database error in getting environment data

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.delegate.ServicesHandler.execute(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at edu.harvard.i2b2.pm.ws.ExecutorRunnable.run(Unknown Source)

14:32:00,805 ERROR [stderr] (Thread-74)     at java.lang.Thread.run(Thread.java:745)

[Phillips, Lori C.]

Looks like your datasource is missing the scheme / dbname

 

Try this:

<connection-url>jdbc:sqlserver://WIN8_SQL_SERVER:1433;databasename=YOUR_DB_NAME</connection-url>

 

The query should end up as:

[select * from YOUR_DB_NAME.pm_hive_data where status_cd <> 'D'];

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

The information in this e-mail is intended only for the person to whom it is
addressed. If you believe this e-mail was sent to you in error and the e-mail
contains patient information, please contact the Partners Compliance HelpLine at
http://www.partners.org/complianceline . If the e-mail was sent to you in error
but does not contain patient information, please contact the sender and properly
dispose of the e-mail.

[Pat B]

That was it! Thank you Lori!

As my luck goes, I have a "network error" now and after looking in this forum I did not find any topics on it. I also googled the web for the error I see in the JBoss server.log and it seems to be an axis problem. I will post it as a separate problem because it is a separate problem and the "invalid object" error has been fixed thanks to you. Maybe add the fix to the install wiki for Windows SQL Server users.

[Pat B]

Before I post another issue, according to the wiki order of things to install the workbench should load with just the PM service installed. Yet, I get a "network error" empty window message, the workbench crashes and in the Jboss log I see:

12:24:30,627 ERROR [org.apache.axis2.engine.AxisEngine] (http--127.0.0.1-9090-1) The service cannot be found for the endpoint reference (EPR) http://localhost:9090/i2b2/services/QueryToolService/request: org.apache.axis2.AxisFault
etc etc ...
12:24:30,798 ERROR [org.apache.axis2.engine.AxisEngine] (http--127.0.0.1-9090-1) The service cannot be found for the endpoint reference (EPR) http://localhost:9090/i2b2/services/OntologyService/getCategories: org.apache.axis2.AxisFault
etc etc...
12:24:35,444  ERROR [org.apache.axis2.engine.AxisEngine] (http--127.0.0.1-9090-1) The service cannot be found for the endpoint reference (EPR) http://localhost:9090/i2b2/services/WorkplaceService/getFoldersByUserId: org.apache.axis2.AxisFault
etc etc...

I will install the other cells and see if that fixes the problem but if you know already that the errors are not related to the other cells missing let me know.

[Phillips, Lori C.]

These all appear to be related to the individual cells not being installed &/or configured.

Are the URLs shown pointing to jboss at all ?  (the //localhost:9090/i2b2/services/   part of URL) 

--

You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Pat B]

Yes, the URLs shown points to jboss. I am just surprised that I got the errors and the workbench crashed because I respected the order of installation of cells and items. I am installing the ONtology cell now but also at a meeting so it's slow.

[Pat B]

I am very happy to report that I got the workbench to work on my Windows 7 pc! After about a month of trying different things, (never got it to run on my Windows 8 64-bit machine) I can finally use the workbench to read our i2b2 database.

Please take note that it is best to run the workbench after at least the ONT cell has been installed. At least in Windows.

Paty