#9 Error processing check - Invalid Object name

[Anonymous]

Hi,
Every time I'm trying to run DB Sanity against my test database, I get report with checks reported as faulty and showing some stack trace exception.
My environment is:
OS - Windows 7 SP1
Java versions: tested on SE 7u3, SE 6u31
JDBC class: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC versions: tested on JDBC 3.0 and JDBC 4.0

Sample check:
<check name="Inspection must have NumberFormatId" table="tcInspection" defectType="data_validation" >
<notNull expression="NumberFormatId" />
</check>

Error listed in report:
java.lang.RuntimeException: Error in query: SELECT InspectionId FROM tcInspection WHERE NumberFormatId IS NULL
at org.databene.jdbacl.QueryIterator.createSource(QueryIterator.java:52)
at org.databene.jdbacl.QueryIterator.(QueryIterator.java:41)
at org.databene.jdbacl.ArrayResultSetIterator.(ArrayResultSetIterator.java:39)
at org.databene.dbsanity.model.query.DefectQueryStrategy.perform(DefectQueryStrategy.java:57)
at org.databene.dbsanity.model.NotNullQueryStrategy.perform(NotNullQueryStrategy.java:60)
at org.databene.dbsanity.model.StrategyBasedCheck.perform(StrategyBasedCheck.java:68)
at org.databene.dbsanity.model.CheckHolderSupport.performCheck(CheckHolderSupport.java:149)
at org.databene.dbsanity.model.CheckHolderSupport.perform(CheckHolderSupport.java:81)
at org.databene.dbsanity.model.SuiteHolderSupport.perform(SuiteHolderSupport.java:94)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:351)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:310)
at org.databene.dbsanity.Main.main(Main.java:50)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tcInspection'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at org.databene.jdbacl.proxy.LoggingStatementHandler.executeQuery(LoggingStatementHandler.java:161)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.databene.commons.BeanUtil.invoke(BeanUtil.java:687)
at org.databene.commons.BeanUtil.invoke(BeanUtil.java:646)
at org.databene.jdbacl.proxy.LoggingStatementHandler.invoke(LoggingStatementHandler.java:104)
at $Proxy1.executeQuery(Unknown Source)
at org.databene.jdbacl.QueryIterator.createSource(QueryIterator.java:49)
... 11 more

I have checked cache file prepared by DB Sanity and can confirm that the table "tcInspection" can be found there. Why is it being reported as "Invalid object name..."?

[Anonymous]

UPDATE::
I have been playing around and testing and managed to get DB Sanity working. It turned out that all problems here were due to environment file configuration. Here are some of my observations:
1. Original configuration was:
db_url=jdbc:sqlserver://<my_remote_server>;
db_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db_user=<my_user>
db_password=<my_password>
db_catalog=<my_database_name>

RESULT: Error as reported originally.

2. Modified configuration by using all parameters in URL only.
db_url=jdbc:sqlserver://<my_remote_server>;databaseName=<my_database_name>;user=<my_user>;password=<my_password>;
db_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

RESULT: Check with <sql></sql> passed ok, but checks like UNIQUE, FOREIGN KEY, NOT NULL fail with error like:
org.databene.commons.ObjectNotFoundException: Table 'tcInspection'
at org.databene.jdbacl.model.DefaultDatabase.getTable(DefaultDatabase.java:200)
at org.databene.jdbacl.model.DefaultDatabase.getTable(DefaultDatabase.java:191)
at org.databene.dbsanity.model.NotNullQueryStrategy.perform(NotNullQueryStrategy.java:55)
at org.databene.dbsanity.model.StrategyBasedCheck.perform(StrategyBasedCheck.java:68)
at org.databene.dbsanity.model.CheckHolderSupport.performCheck(CheckHolderSupport.java:149)
at org.databene.dbsanity.model.CheckHolderSupport.perform(CheckHolderSupport.java:81)
at org.databene.dbsanity.model.SuiteHolderSupport.perform(SuiteHolderSupport.java:94)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:351)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:310)
at org.databene.dbsanity.Main.main(Main.java:50)

3. Modified configuration by using all parameters in URL and in addition db_catalog parameter
db_url=jdbc:sqlserver://<my_remote_server>;databaseName=<my_database_name>;user=<my_user>;password=<my_password>;
db_driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db_catalog=<my_database_name>

RESULT: All checks, UNIQUE, FK, NOT NULL and SQL pass fine.

Conclusion:
Initial error due to incorrectly provided URL (maybe better error output could help identify this issue?). However, only <sql></sql> checks are passed fine with db_url provided. Other checks require the optional db_catalog parameter set as well. If the database name was already provided in db_url, could it be possible to avoid duplicating it in db_catalog for all to work? Or maybe documentation just need to be updated?

Tested on Microsoft's JDBC 4.0 driver for SQL and Java SE 7u3

Hope this helps.
Best regards,
Ryandell