PDA

View Full Version : Default schema not set in Metadata connection: "table 'xxx' does not exist" errors



caio80
05-27-2008, 10:10 AM
Hi there,

I am poking around with Pentaho and Compiere. One task I was asked to work on is analysis on data located on a postgresql instance used by compiere. Being just a sysadmin I have no clear "big picture" idea on how things work or cowork together here, therefore be nice with me... (even better, if you guys have any good link explaining what does what without BI terminology and so on, I'd appreciate it... It would be really useful to install this suite, for I still don't know whether this "metadata editor" is needed or not... )

Anyways:

I ran the 1.7.0.RC2 metadata editor today on my linux box. First of all some output on command line:


INFO 27-05 14:30:45,281 (VfsLog.java:info:122) -Using "/tmp/vfs_cache" as temporary files store.
ERROR 27-05 14:30:48,677 (LogWriter.java:println:403) -Pentaho Metadata Editor - Unable to load query : java.io.FileNotFoundException: /home/damned/.pentaho-meta/.query (No such file or directory)
Unable to find kettle engine jar file to set build date. (ingored)
Apart from this, everything looks correctly working and the app shows up. Then I edit a new connection to my postgresql DB with the same username and pwd used by compiere itself to access and write tables. Testing the connection gives me an OK.

I can browse the tables on the DB with the bundled browser, but
as soon as I try to open a table or import one, I get:



org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't get field info from [SELECT * FROM a_registrationvalue limit 1]

ERROR: relation "a_registrationvalue" does not exist

at org.pentaho.di.core.database.Database.getQueryFieldsFallback(Database.java:2323)
at org.pentaho.di.core.database.Database.getQueryFields(Database.java:2240)
at org.pentaho.di.core.database.Database.getQueryFields(Database.java:1818)
at org.pentaho.di.core.database.Database.getTableFields(Database.java:1813)
at org.pentaho.pms.editor.MetaEditor.importTableDefinition(MetaEditor.java:3386)
at org.pentaho.pms.editor.MetaEditor.importMultipleTables(MetaEditor.java:3336)
at org.pentaho.pms.editor.MetaEditor$56.handleEvent(MetaEditor.java:1701)
at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
at org.pentaho.pms.editor.MetaEditor.readAndDispatch(MetaEditor.java:810)
at org.pentaho.pms.editor.MetaEditor.main(MetaEditor.java:3587)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "a_registrationvalue" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:239)
at org.pentaho.di.core.database.Database.getQueryFieldsFallback(Database.java:2296)
... 12 more
I then tried to get on postgresql console with psql.

I then recreated the trouble by doing the same sql statement.

And then I got what was going on: it doesn't prefix "compiere." to table names when querying!!

In fact, querying this is ok:

SELECT * FROM compiere.a_registrationvalue:while this doesn't work:

SELECT * FROM a_registrationvalue:
It's weird, because I specified the dbname in the connection options. Even with that filled in, when I browse the DB I start on a upper level, in fact I can see and navigate other schemas such as information_schema or pg_catalog as a first step.

Furthermore, when I go in "Edit Connection -> Features list -> URL" I get jdbc:postgresql://192.100.100.101:5434/compiere/", proving that it should be happy already!!!

Am I missing some steps?


And last but not the least, is this the right road to be followed to make postgresql external data being "handled" by Pentaho? I can't explain better my mind, I'm sorry, I'm too distant from this BI culture... ;-)


Thanks in advance!

caio80
05-27-2008, 10:28 AM
I just found out an option that could solve my trouble: you can find it in the Feature list in edit connection.

It's feature number 48 "use schema name to get table list?", it is set to N.

Also feature # 36 sounds "good": "Schema / Table combination" that is already set to SCHEMA.TABLE, though... Therefore it should already be correct!

Anyways, whatever change I wanna commit, modifications don't seem to be persistent once I close the window... ;--(

caio80
06-04-2008, 04:38 AM
Update:

As I expected, importing a single table from DB explorer works, while the one-click-import-all menu item via right mouse click doesn't as I already explained above. Just FYI...