mORMot Open Source

angusj

Member

Registered: 2016-11-30

Posts: 5

Error Database table is locked (SQLite newbie)

Firstly, apologies for what is probably a very basic question but I'm a complete novice with SQL and to SQLite and to this fantastic Synopse library.
Also, if there's a better forum for this kind of question then please direct me there.

Anyhow, I'm writing a function that drops a column from an SQLite table but I'm encountering the following error:
Error SQLITE_LOCKED (6) - database table is locked

This seems to be the critical code ...

  rows := props.Execute('SELECT sql FROM sqlite_master WHERE type="table" AND name=?', [tableName]);
  if not rows.Step then exit;
  defs := rows.ColumnString(0);
  rows := nil;
  //this is a very crude workaround that avoids the table is locked error ...
  //props.MainConnection.Disconnect;
  //props.MainConnection.Connect;    
  
  //other code that has no bearing on my problem

  //error raised at this line ...
  props.ExecuteNoResult(RawUTF8(format('DROP TABLE %s;', [tableName])), []);

It seems to me that somehow I need to finalize or reset the SELECT statement, but I can't see a good way to do that (apart from the crude workaround above).
Evidently rows := nil; isn't sufficient.
I'd appreciate any help ...

Last edited by angusj (2016-12-03 04:20:06)

angusj

Member

Registered: 2016-11-30

Posts: 5

Re: Error Database table is locked (SQLite newbie)

OK, I found the solution ... I needed to complete stepping the row before nil-ing it.

  rows := props.Execute('SELECT sql FROM sqlite_master WHERE type="table" AND name=?', [tableName]);
  if not rows.Step then exit;
  defs := rows.ColumnString(0);
  while rows.Step do; //this is important
  rows := nil;