开发者

SQLiteException - no such table

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"
         开发者_StackOverflow   }, 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?


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 + ";");


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜