Execute multiple SQL statements fails (sqlite)

[naruto]

Good day,

The goal of the code below is to execute a SQL script (sqlite) to create my database tables.

I am trying to execute multiple SQL statements at once but failed doing so. No error is shown while executing the SQL statements. After pulling the database from the emulator back to my pc using adb, I noticed that only the first SQL statement is executed.

Note that the variable "SqlStr" contains all of the SQL statements below. The big question remains is how to execute multiple statements from a SQL script?

Any help is much appreciated.

SQL1.BeginTransaction
   
   Try
      SQL1.ExecNonQuery("Pragma foreign_keys=False;")
      SQL1.ExecNonQuery(SqlStr) 
      SQL1.ExecNonQuery("Pragma foreign_keys=True;")
      SQL1.TransactionSuccessful
   Catch
      'the transaction will be cancelled
      Msgbox(LastException.Message, "error")
   End Try
   
   SQL1.EndTransaction

Drop Table If Exists [Category];
CREATE TABLE "Category"(
[category_id] integer UNIQUE NOT NULL
,[categoryname] varchar(255) NOT NULL
,[color] int
, Primary Key(category_id)   
);

Drop Table If Exists [Lesson];
CREATE TABLE "Lesson"(
[lesson_id] INTEGER UNIQUE NOT NULL
,[category_id] INTEGER NOT NULL
,[lessonname] varchar(255) NOT NULL
, Primary Key(lesson_id)   
);

Drop Table If Exists [settings];
CREATE TABLE [settings](
[settings_id] int UNIQUE NOT NULL
,[category] varchar(50)
,[key] varchar(50)
,[value] varchar(300)
   
);

Drop Table If Exists [Word];
CREATE TABLE "Word"(
[word_id] INTEGER UNIQUE NOT NULL
,[lesson_id] integer NOT NULL
,[chinese] VARCHAR(100)
,[pinyin] VARCHAR(100)
,[translation] VARCHAR(100)
,[remarks] TEXT
, Primary Key(word_id)   
);

Drop Table If Exists [WordStats];
CREATE TABLE "WordStats"(
[word_id] integer UNIQUE NOT NULL
,[date_added] DATE
,[date_lastaccessed] DATE
,[date_nextreview] DATE
,[numberoftimesaccesed] int
,[MemorizedLevel] int
,[timescorrect] int
   
);

 

[Mahares]

I have a situation like yours where I create 6 tables in a consecutive manner. I had to do a SQL1.ExecNonQuery for each table creation separately for it to work. You cannot lump them all in one SQL statement. Perhaps, someone like Klaus who is the database guru has a way. I would love to know it.

 

[poseidon]

re

thats the point in 1exec, execute multiple statements

SQL1.BeginTransaction
     SQL1.ExecNonQuery("Pragma foreign_keys=False;Pragma foreign_keys=True;")
SQL1.EndTransaction