So now I have come a bit further in my creating a DbHandler class for my project.
I am encapsulating all of the database operations in a class where I have this code when I instantiate it:
constructor TMonitorDb.Create(dbfile: string);
begin
FConn := TSQLite3Connection.Create(NIL);
FTrans := TSQLTransaction.Create(NIL);
FQuery := TSQLQuery.Create(NIL);
InitDb(dbfile); //How to deal with failure?
end;
function TMonitorDb.InitDb(DbFile: string): boolean;
var
NewDatabase: boolean;
begin
Result := false;
try
FConn.Transaction := FTrans;
FTrans.DataBase := FConn;
FQuery.Transaction := FTRans;
FQuery.DataBase := FConn;
FConn.DatabaseName := DbFile;
FConn.CharSet := 'UTF8';
NewDatabase := not FileExists(DbFile);
FConn.Open;
Result := FConn.Connected;
if Result and NewDatabase then //Database not existing, so create tables
begin
Log.LogStd('Populating new database');
FQuery.SQL.Clear;
//Use queries to build database
FQuery.SQL.Text := SQL_CREATE_TASKS;
FQuery.ExecSQL;
FQuery.SQL.Text := SQL_CREATE_MEASSETTINGS;
FQuery.ExecSQL;
FQuery.SQL.Text := SQL_CREATE_CMDFILES;
FQuery.ExecSQL;
FQuery.SQL.Text := SQL_CREATE_TASKLOG;
FQuery.ExecSQL;
end;
Result := true;
except
FLastError := 'Exception! Could not initialize database ' + DbFile;
end;
end;
In the InitDb method if there is no database it creates it (handled by SqLite itself when it opens a non-existing database file. If that was done I want to set up the tables as shown above, but for some reason it won't work.
Only the two first of the tables are created...
If I run the query strings directly in the db browser they are all created, but I am running them one by one.
Questions:Is there something more to do in between the SQL calls? Delays? (Not fun...)
In MSSQLServer I used to insert a GO command between multiple queries in a stored procedure, is there something like that also in SQLite?
Or is there some query property one can check to verify that the operation is ready?
I am using 4 query strings similar to the one below, one for each table.
At first I loaded them all into the FQuery.SQL stringlist but then only a single table was created.
Then I changed to the code above and now I get two tables....
Typical SQL string:
SQL_CREATE_TASKLOG = 'CREATE TABLE `TASKLOG` ( '+
'`TaskLogID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, '+
'`TaskID` INTEGER NOT NULL DEFAULT -1, '+
'`StartTime` TEXT, '+
'`EndTime` TEXT, '+
'`Readings` INTEGER NOT NULL DEFAULT 0); ';
Can the TSQLTRansaction be used to verify that the ExecSql has actually finished before I run the next?