SQLiteException - no such table

Question

Im getting this error when i try to access my View

I've built my database/View using this

CREATE TABLE Boxer(
BoxerId INTEGER PRIMARY KEY AUTOINCREMENT,
Firstname NVARCHAR(50) NOT NULL,
Lastname NVARCHAR(50) NOT NULL
);

CREATE TABLE Match(
MatchId INTEGER PRIMARY KEY AUTOINCREMENT,
BoxerA INTEGER NOT NULL FOREIGN KEY REFERENCES Boxer(BoxerId),
BoxerB INTEGER NOT NULL FOREIGN KEY REFERENCES Boxer(BoxerId),
MatchDate date NOT NULL DEFAULT GETDATE(),
NumberOfRounds INTEGER NOT NULL DEFAULT  12
);

CREATE TABLE Round(
RoundId INTEGER PRIMARY KEY AUTOINCREMENT,
MatchId INTEGER NOT NULL FOREIGN KEY REFERENCES Match(MatchId),
BoxerA INTEGER NOT NULL DEFAULT 0,
BoxerB INTEGER NOT NULL DEFAULT 0,
Position INTEGER NOT NULL
);

/*
Building a view which dislpays matches with boxers names and total scores
*/
CREATE VIEW MatchDetail AS
SELECT Match.MatchId, A.BoxerId AS IdA, B.BoxerId AS IdB, A.Firstname + ' ' + A.Lastname         AS NameA, B.Firstname + ' ' + B.Lastname AS NameB,
(SELECT SUM(R.BoxerA) AS Score FROM Round AS R WHERE (R.MatchId = Match.MatchId)) AS ScoreA,
(SELECT SUM(R.BoxerB) AS Score FROM Round AS R WHERE (R.MatchId = Match.MatchId)) AS ScoreB, 
Match.MatchDate, Match.NumberOfRounds
FROM Boxer AS A INNER JOIN Match ON A.BoxerId = Match.BoxerA INNER JOIN Boxer AS B ON     Match.BoxerB = B.BoxerId

I've pretty much built my app so far using the notepad example so I then call my DbHelper

Cursor MatchesCursor = mDbHelper.fetchAllMatchDetails();

This then calls the query

public Cursor fetchAllMatchDetails(){
    return mDb.query(VIEW_MATCHDETAIL, new String[] {
            "MatchId"
            }, null, null, null, null, null);
}

VIEW_MATCHDETAIL is defined as a string = "MatchDetail"

and it's here where it crashes saying

no such table MatchDetail: while compiling SELECT MatchId FROM MatchDetail

anyone had this problem before?

Oh so you know when I run the create script on my local sql database it creates the table and view fine! — Steve
– Steve, Commented May 25, 2011 at 20:40
Something that I did find out was that a SimpleCursorAdapter requires a "_id" column so ive also updated my field names for this — Steve
– Steve, Commented May 25, 2011 at 22:13

browep · Accepted Answer · 2011-05-25 21:34:33Z

2

You have some beautiful SQL there. Unfortunately only the first line of sql will be executed in SQLiteDatabase.execSQL. The rest will be ignored silently (convenient eh?). Split up the statements manually like this:

https://github.com/browep/fpt/blob/master/src/com/github/browep/nosql/NoSqlSqliteOpener.java

or if you like to keep your sql in a separate file, try this:

String sqlText = getSqlText();
for(String sqlStmt : sqlText.split(";"))
     myDb.execSQL(slqStmt + ";");

answered May 25, 2011 at 21:34

browep

5,1155 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

ha that would go some what to explain when i explore my sqlite database with Questiod it only has a boxer table.. ill try this and see if i get all 3 databases and if so then move onto my view.

Gyan · Accepted Answer · 2011-05-25 20:45:19Z

0

What stands out to me is the use of datatypes like NVARCHAR(50). SQLite only has a very simple set of datatypes. I'm surprised it doesn't throw an exception when you install the app. Try using simply TEXT instead.

answered May 25, 2011 at 20:45

Gyan

12.4k2 gold badges26 silver badges26 bronze badges

3 Comments

Gyan Over a year ago

Then why do they define the set of data types I linked to?

dan04 Over a year ago

@mikerobi: SQLite 3 does NOT completely ignore the data type. It uses it to determine the column affinity. But there is no difference between NVARCHAR and TEXT.

Gyan Over a year ago

@mikerobi @dan04 Both of your comments were interesting to me none the less. Thanks.

Haphazard · Accepted Answer · 2011-05-25 20:52:15Z

0

If you cannot access a database that you know you have initialized, try passing the Context from the Activity that created the table to the class trying to query the table. Use that Context as part of your connection initialization.

answered May 25, 2011 at 20:52

Haphazard

10.9k7 gold badges46 silver badges55 bronze badges

Collectives™ on Stack Overflow

SQLiteException - no such table

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related