Android Question SQL No such table error

Jimmy-1974

Member
Licensed User
Whilst experimenting with SQLite databases and trying to incorporate them into a simple program, I keep getting the following error:

android.database.SQLiteException: No Such Table..(code1) while compiling SELECT * FROM..

I have seen various experiences of this problem by google search. I have tried deleting cache files and uninstalling app but with no luck.

I have tried using both the Mozilla Firefox SQLite Manager and the SQLite Browser but both produce the same result.

Does anyone have a solution to this problem? I am a complete novice at this so apologies in advance if the solution is very simple!

Many thanks
 

Mahares

Well Known Member
Licensed User
Your code looks good except that you need spaces in this SQL statement like this:SELECT * FROM capitals. The problem is the test.db is a bad file that I could not open.
 

sorex

Expert
Licensed User
the problem is that it doesn't copy the file, it creates an empty one because you use ,TRUE in the sqlite init.
 

sorex

Expert
Licensed User
never use sqlite myself so I don't know how it is supposed to behave but what I see is that the copy works fine but the sql1.init trashes the file again even when using FALSE for autocreate.
 

Mahares

Well Known Member
Licensed User
the problem is that it doesn't copy the file, it creates an empty one because you use ,TRUE in the sqlite init.
I am not sure I agree with your explanation, because the following statement in his code should copy the db from assets to defaultexternal. Try to open the db via any db utility, you will find out it does not open
B4X:
If File.Exists(File.DirDefaultExternal,"test.db")=False Then
    File.Copy(File.DirAssets,"test.db", File.DirDefaultExternal,"test.db")
End If
 

parijs

Active Member
Licensed User
I can open the db in the root
Not the db in files
Missing test.sql
Your code woks with me
 

sorex

Expert
Licensed User
that what I said, it creates a 0 bytes test.db once you use sql1.initialize(...,...,true)

also what Parijs says is true.

the file in the project folder is a valid sqlite file.

the one in the files/assets folder is just an sql query

B4X:
BEGIN TRANSACTION;
CREATE TABLE `capitals` (
    `Country`    TEXT,
    `City`    TEXT,
    `quesid`    INTEGER,
    PRIMARY KEY(quesid)
);
INSERT INTO `capitals` VALUES ('Spain','Madrid','2');
INSERT INTO `capitals` VALUES ('France','Paris','3');
COMMIT;

copy the file from the projects folder to files\ and it works fine.
 

klaus

Expert
Licensed User
How did you generate the database ?
I tested your project and I get the same error.
Then I tryed to open the database with SQLiteoPro2009 and I get the message that the database is encripted !?
So without knowing what exactly you have done it's difficult to give you a concrete advice.
 
Last edited:

sorex

Expert
Licensed User
see above, Klaus. One is valid the other is just a text file containing that multiple query.
 

Jimmy-1974

Member
Licensed User
Hoe did you generate the database ?
I tested your project and I get the same error.
Then I tryed to open the database with SQLiteoPro2009 ad I get the message that the database is encripted !?
So without knowing what exactly you have done it's difficult to give you a concrete advice.
I created it from the SQLite Manager Add-on with Mozilla. Selected 'new database' tab and built it from the tabs within that (add column, add row etc). I then selected the 'Export Database' option from the database tab into the 'app' file folder as a .db file.

Thanks for all the replies, I appreciate the time.
 
Top