topic Metadata: Retreive Schema from SQLite does not show any db/tables in Talend Studio

Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Mon, 27 Feb 2017 11:30:37 GMT

Hi,

I created a DB connection in metadata for SQLite and gave it the path to the sqlite file. The path does not contain any space etc.
I "check" the connection to see if it is successful. It is.
Then I right click the connection & retrieve schema.
I keep the default checked items on the dialoge box (filter by name; tables,views,synonyms checked, 1 filter with value "%") and I click next
Nothing appears in the schema after clicking next

What am i doing wrong ?
The SQLite file/db has a valid schema with 12-15 tables in it which I can access, query etc using SQLite studio.

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Tue, 28 Feb 2017 06:58:12 GMT

Hi,
Are you using talend data quality product? On which build version you got this issue?

Best regards
Sabrina

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Tue, 28 Feb 2017 11:48:18 GMT

I am using Talend Open Studio 6.3.1

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Wed, 01 Mar 2017 09:44:09 GMT

Hi,
Are you using SQLite database 3.16.2?
Please take a look at document about:TalendHelpCenterupported systems%2C databases and business applications by Talend components.
Did you follow up document about: TalendHelpCenter:Retrieving table schemas to retrieve your schema?
Best regards
Sabrina

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Wed, 01 Mar 2017 10:13:40 GMT

xdshi,
Yes, to both the links.
As stated in my original post, i follwed the exact process described in the link
However, I do not see any database appearing in the next window as you see in the snapshot attached to original post.
Moreover, when i try to use tSQLiteInput to get data from a table I get the same error as the other person in other post. e.g.

connecting to socket on port 3870
connected
Exception in component tSQLiteInput_1
java.sql.SQLException: file is encrypted or is not a database
at org.sqlite.DB.throwex(DB.java:288)
at org.sqlite.NestedDB.prepare(NestedDB.java:115)
at org.sqlite.DB.prepare(DB.java:114)
at org.sqlite.Stmt.executeQuery(Stmt.java:89)
at conan_exiles.test_0_1.test.tSQLiteInput_1Process(test.java:667)
at conan_exiles.test_0_1.test.runJobInTOS(test.java:1161)
at conan_exiles.test_0_1.test.main(test.java:1018)
disconnected
Job test ended at 16:55 28/02/2017.
I read up online and it may be related to Talend jdbc driver for sqlite which is supporting only very old version of sqlite (3.6.7) whereas my sqlite db is 3.16.2. How can I still connect ?

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

vapukov — Wed, 01 Mar 2017 10:48:38 GMT

You have a choice - use JDBC connection with proper driver (I can confirm - shipped with Talend not work)
or edit repository files for change jdbc_driver version
than all start work fine

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Thu, 02 Mar 2017 04:01:56 GMT

While using a Generic JDBC and jdbc_sqlite_3.16.1 i am also able to retrieve schema but When i run any job, i get compile errors.
Execution failed : Job compile errors
At least job "test" has a compile errors, please fix and export again.
Error Line: 661
Detail Message: Invalid escape sequence (valid ones are \b \t \n \f \r \" \' \\ )
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

vapukov — Thu, 02 Mar 2017 05:23:21 GMT

Look like - You are really need to check - what also
often error - missed quotes, or escape character or ...
attached - example in both directions
source file - table - target files similar

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Thu, 02 Mar 2017 13:48:38 GMT

Look like - You are really need to check - what also
often error - missed quotes, or escape character or ...
attached - example in both directions
source file - table - target files similar

I appriciate your effort of creating a test job for me.
I see it works perfectly for you. I did the following:

Created a Generic JDBC connection
Then retreived schema to import tables (this worked with your trick of using generic jdbc with 3.16.1 jar)
Then created a test job but I get compile errors

Do you see any issues in settings ?

Re: Metadata: Retreive Schema from SQLite does not show any db/tables

Anonymous — Thu, 02 Mar 2017 19:55:43 GMT

Vapukov, Credit goes to you
I figured out that the path to JAR file in Generic JDBC had to have double "\\" instead of "\" single ones. Since backslash is an escape character itself. After switching \ with \\ everything works!
Moderator, Please mark this thread as resolved!