One Star

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

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.
9 REPLIES
Moderator

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

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

Best regards
Sabrina
 
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

I am using Talend Open Studio 6.3.1
Moderator

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

Hi,
 Are you using SQLite database 3.16.2?
Please take a look at document about:TalendHelpCenterSmiley Frustratedupported 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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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 ? 
Twelve Stars

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

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   
  
-----------
One Star

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

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.
Twelve Stars

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

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
-----------
One Star

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

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 ?
One Star

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

Vapukov, Credit goes to you Smiley Happy
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!