CODEWITHSUNDEEP
c++sqlsqlite
user963241
user963241

Reputation: 7038

"delete from table" doesn't delete table?

I create "database.db" and everything goes ok but why wouldn't it delete the table at the end? Everytime i run it, i get "table already exist" error message on creating the table.

int main()
{
 sqlite3 *db; //Database Handle
 char *zErr;
 int rc;
 char *sql;

 rc = sqlite3_open("database.db", &db);

 if(rc)
 {
  cout << "Can't open database: " << sqlite3_errmsg(db) << endl;;
  sqlite3_close(db);
  exit(1);
 }

 sql = "create table test(PID int primary key, Name text)"; //sql query

 rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); //execute sql statement

 if(rc != SQLITE_OK)
 {
  if (zErr != NULL)
  {
   cout << "SQL error: " << zErr << endl;
   sqlite3_free(zErr);
  }
 }
 else
 {
  sql = "insert into test values (1,'John')";
  rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);

  sql = "insert into test values (2,'Smith')";
  rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
 }



    //delete the table on exit.

 rc = sqlite3_exec(db, "delete from test", NULL, NULL, &zErr);

 sqlite3_close(db);
 return 0;
}

Also, can the primary keys be auto-generated following the last greater key existing in database?

Upvotes: 4

Views: 2473

Answers (4)

moinudin
moinudin

Reputation: 138347

You need to use drop table. delete deletes rows in the table.

rc = sqlite3_exec(db, "drop table test", NULL, NULL, &zErr);

SQLite will auto-increment an integer field declared as the primary key. See here.

Upvotes: 7

pingw33n
pingw33n

Reputation: 12510

  1. Use DROP TABLE.
  2. From SQLite docs:

The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert.

Upvotes: 2

Brian R. Bondy
Brian R. Bondy

Reputation: 347216

You use a DELETE command to delete rows of a table.

You use a DROP command to drop a whole table or other DB item.

To create the table you can also use CREATE TABLE IF NOT EXISTS if you aren't sure if it exists.

To get your auto generated row ids use: id INTEGER PRIMARY KEY in your CREATE TABLE command.

Upvotes: 2

Kangkan
Kangkan

Reputation: 15571

The DML "delete from test" does delete all the rows from the table test. If you wish to drop the table, try "delete table test" or "drop table" instead.

Upvotes: 0

Related Questions