Cannot select in SQLite database using Pentaho Data Integration : java.sql.SQLException: ResultSet closed

1
votes

I'm trying to use a SQLite database to make a Table Input step in Pentaho Data Integration. Connection seems fine...

Connection to database [Volp_2] is OK.
Hostname       : C:\Users\Aluno\Apps\VOLP\BD\volp.db
Port           : -1
Database name  : *****

... but previewing first 1000 rows through Table Input step

2014/05/26 11:18:14 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Erro inesperado
2014/05/26 11:18:14 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2014/05/26 11:18:14 - Table input.0 - An error occurred executing SQL: 
2014/05/26 11:18:14 - Table input.0 - select nome from palavras
2014/05/26 11:18:14 - Table input.0 - [SQLITE_ERROR] SQL error or missing database (no such table: palavras)
2014/05/26 11:18:14 - Table input.0 - 
2014/05/26 11:18:14 - Table input.0 -   at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)
2014/05/26 11:18:14 - Table input.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)
2014/05/26 11:18:14 - Table input.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
2014/05/26 11:18:14 - Table input.0 -   at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2014/05/26 11:18:14 - Table input.0 -   at java.lang.Thread.run(Thread.java:724)
2014/05/26 11:18:14 - Table input.0 - Caused by: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: palavras)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.DB.newSQLException(DB.java:383)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.DB.newSQLException(DB.java:387)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.DB.throwex(DB.java:374)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.NativeDB.prepare(Native Method)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.DB.prepare(DB.java:123)
2014/05/26 11:18:14 - Table input.0 -   at org.sqlite.Stmt.executeQuery(Stmt.java:121)
2014/05/26 11:18:14 - Table input.0 -   at org.pentaho.di.core.database.Database.openQuery(Database.java:1630)
2014/05/26 11:18:14 - Table input.0 -   ... 4 more
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Something went wrong while trying to stop the transformation: org.pentaho.di.core.exception.KettleDatabaseException: 
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - Error cancelling statement
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - ResultSet closed
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - Error cancelling statement
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - ResultSet closed
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - 
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.core.database.Database.cancelStatement(Database.java:664)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.core.database.Database.cancelQuery(Database.java:646)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:303)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.trans.Trans.stopAll(Trans.java:1729)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.trans.step.BaseStep.stopAll(BaseStep.java:2605)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.trans.step.RunThread.run(RunThread.java:95)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at java.lang.Thread.run(Thread.java:724)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App - Caused by: java.sql.SQLException: ResultSet closed
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.sqlite.RS.checkOpen(RS.java:63)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.sqlite.Stmt.cancel(Stmt.java:254)
2014/05/26 11:18:14 - C:\Users\Aluno\Apps\App.ktr : App -   at org.pentaho.di.core.database.Database.cancelStatement(Database.java:659)

What am I doing wrong? Can anyone help me?

2
sqlitepentahokettle

2 Answers

1
votes

Well this error looks like the cause: 

select nome from palavras

Since it is complaining that the table does not exist...

Are you sure the table is spelled right or does it have a database name associated? If you connect directly with sqlite3 and run the query, does this query work?

0
votes

This is already covered at https://gobansaor.wordpress.com/2006/12/18/sqlite-jdbc-and-kettle-pentaho-data-integration-etl/

Here are the steps in case that article is removed:

1. Within SPOON, make a new connection using for example a Table Input step.
2. In the General tab, pick “Generic database” from the list of connection types.
3. Pick “Native(JDBC)” from the Method of Access list.
No further information required on this tab, go to the Generic tab.
4. In the URL field, enter “jdbc:sqlite:c:\mydata\mydatabase.db” where c:\mydata\mydatabase.db is the SQLite database you wish to connect to.
5. In the Driver class field, enter “org.sqlite.JDBC”.

Previously the jar file including the JDBC class must be saved into the data-integration\lib folder.