SQLite JDBC error: [SQLITE_ERROR] SQL error or missing database (no such table:)

I'm trying to read data from a .db file. This file has a colume of blob data type that cannot be read by function sqlite.

So I switch to Matlab SQLite JDBC database interface with the following steps.

(1) I download the sqlite-jdbc-3.7.2.jar and add it to javaclasspath.txt. (I'm using R2016a)

(2) Set up a JDBC connection in Matlab-Apps-Databse explorer, and type in the flollowing information as required.

    Select "Other"
    Name: SQLite
    username: [nothing]
    password: [nothing]
    driver:org.sqlite.JDBC
    url:jdbc:sqlite:D:\QMDownload\9\uhistory3-20160926.db

I run the test and it shows sucessful.

(3) Then I run the commands:

    datasource = 'SQLite'; 
    username = '';password = '';
    driver = 'org.sqlite.JDBC';
    url = 'jdbc:sqlite:D:\QMDownload\9\uhistory3-20160926.db';
    conn = database(datasource,username,password,driver,url)

 database with properties:

       Instance: 'SQLite'
       UserName: ''
         Driver: 'org.sqlite.JDBC'
            URL: 'jdbc:sqlite:D:\QMDownload\9\uhistory3-20160926.db'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 org.sqlite.Conn]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

(4) Then I read the table:

aCmd = ['SELECT * FROM tb_urlinfo'];
    curs = exec(conn,aCmd);
    data = fetch(curs,rowlimit); 

It always shows an error:

    Error using database/exec (line 54)
        [SQLITE_ERROR] SQL error or missing database (no such table: tb_urlinfo).
        

I have tried many other db files, but it always shows the same error.

I also tried in R2017a, and the error is the same. Can anyone help me? Thanks

[Rik]

Judging by your comment on the FEX, this question is no longer relevant?

[raym]

I would prefer use the native function database in Matlab. I am not sure if I make a wrong step in the setting that cause this error.

[Rik]

I'm hijacking this thread, but the issue is related, so regarding your comment on the FEX

("Hi, I just accidently see your reply here because there is no reminder from the homepage.

I have solve the problem using hex(id) instead of id. The original file is more than 1000MB and hard to upload.")

You can click the follow button to get notified about comments and new versions.

Is your issue now resolved? If it isn't, try creating a small example database that you can upload here. You can either post a new question (please post a link here to make 99% sure I see it), edit this question accordingly, or simply add it here.

[raym]

Hi,

The problem is solved using sqlite3 in this way:

using hex(id) instead of id in SQL command:

hex(id) = 'the hex presentation string'.

But the native matlab command still not solved:

conn = database(datasource,username,password,driver,url);
aCmd = ['SELECT * FROM tb_urlinfo'];
curs = exec(conn,aCmd);
    Error using database/exec (line 54)
        [SQLITE_ERROR] SQL error or missing database (no such table: tb_urlinfo).

[Rik]

I had two reasons for creating the sqlite3 FEX submission:

1. I wanted full compatibilty with GNU Octave and Matlab 6.5 for a specific function I'm expanding
2. I don't have access to the database toolbox

So I can't really help you with the native Matlab command not working as you want it to.