SQLite::Exception: no such table: login

[Aaron Vienneau]

Hi friends,

I'm creating what is, effectively for the sake of this explanation, a server that receives connections which call out to an SQL database (for authentication purposes, obvi.).

DatabaseConnection::DatabaseConnection(const std::string databaseName)
	: m_connection(databaseName, SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE),
m_createTableQuery(m_connection, CREATE_LOGIN_TABLE_QUERY)
{
   m_createTableQuery.exec();
}

Where, databaseName was first "./test.db" and m_createTableQuery is CREATE TABLE IF NOT EXISTS login(username TEXT PRIMARY KEY, password TEXT);. I then deleted the database (it was getting messy with testing), and was just expecting that upon rebooting the server, the database would be re-created and re-seeded with the appropriate table login, but I promptly received this error:

SQLite::Exception: no such table: login

and the server shut down and the connection closed immediately. The database file WAS created, but when I inspect it with SQL on the command line, the table was never created.

I did some Googling and I couldn't find a definitive answer, but I suspect it's either something as simple as my query being incorrect (tested on raw SQL on command line and works), or some sort of caching issue/file path issue.

Any help would be greatly appreciated,

Best,

Aaron

[jonnin]

no database I know of restores things that you delete without being explicitly told to do so.
you can purge data without dropping the table, for small things a delete from table statement and for bigger ones, db commands or drop and recreate may be better.

not sure what you did but I find it odd it put the DB back. Usually those stay deleted too if you manage to do it.

Can you explain better .... is your program fired off on a reboot such that you think it should re-create the table if its gone? Im having trouble tying what your code says, what you said, and a random reboot together into a picture.

[Aaron Vienneau]

Hi Jonnin,

Thanks for your reply. I don't think I'm being clear enough, sorry.

My program is a server that, when booted, should:

(1) Create "./test.db" file
(2) Create a "login" table within that file

If I then delete the "./test.db" file (rm test.db) and then reboot my server, I'd expect that it should just once again:

(1) Create "./test.db" file
(2) Create a "login" table within that file

(First question: Is that expectation wrong?)

But it doesnt. In fact, even if I change the name of the .db file (Say, "./mynewtest.db) I'm trying to create, it *still* gives me:

SQLite::Exception: no such table: login

but it does create the new file ( "./mynewtest.db).

(Second question: Why is it creating the new file but not creating the new table with m_createTableQuery.exec(); , and why is it throwing an exception as if it's looking for the table I'm actually *trying* to create in the new file).

Let me know if I can make a video for you. Happy to do that if it makes this any clearer.