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.
精彩评论