SQL Batch Query execution

[adamioan]

Hello there,
can somebody tell please, how can I execute many SQL commands seperated by semi-column (; ) in SQLite and B4A.
I see that ExecNonQuery is executing only one command.
The commands I 'd like to use are usually CREATE and INSERT.
Here is a example:

CREATE TABLE "CNM_AREA" ("id" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , "name" VARCHAR); 
CREATE TABLE "CNM_CINEMA" ("id" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , "area_id" INTEGER, "name" VARCHAR, "location" VARCHAR, "descr" VARCHAR, "page" VARCHAR);
CREATE TABLE "CNM_MOVIE" ("id" INTEGER PRIMARY KEY  NOT NULL  UNIQUE , "name" VARCHAR, "name_gr" VARCHAR, "gender" VARCHAR, "descr" VARCHAR, "image" VARCHAR, "stars" INTEGER);
Insert into CNM_AREA (id, name) values (1, 'area1'); 
Insert into CNM_AREA (id, name) values (2, 'area2');

Thank you in advance

 

[kickaha]

the Android SQLiteDatabase execSQL method can only execute one statement. To excecute multiple queries more efficiently you should look at BeginTransaction and EndTransaction

 

[adamioan]

Thank you for your help

 

[gemasoft]

1-If ServiceServer.sql_1.IsInitialized =False Then
2- StartService(ServiceServer)
3-End If
4-cursor_1 =ServiceServer.sql_1.ExecQuery("select id_area from areas")

The problem with these lines is that I find the reason why in line 4 is painted yellow and without triggering any error message.

 

[Erel]

There should be a red message in the local variables list with the error message.
It should also show in the logs.

Note that StartService doesn't start the service immediately. This means that the next lines run before the service has started.

 

[adamioan]

Try this

If ServiceServer.sql_1.IsInitialized =False Then
   StartService(ServiceServer)
End If

do while ServiceServer.sql_1.IsInitialized =False
  DoEvents
loop
cursor_1 =ServiceServer.sql_1.ExecQuery("select id_area from areas")

if the above code is not working try to create a timer just for letting the code to proceed

If ServiceServer.sql_1.IsInitialized =False Then
   StartService(ServiceServer)
   dim tmrWait as Timer
   tmrWait.Initialize("tmrWait", 100)
   tmrWait.enabled=true
End If
...
End Sub

Sub tmrWait_Tick
Dim cursor_1 as Cursor
   tmrWait.enabled=false
   cursor_1 =ServiceServer.sql_1.ExecQuery("select id_area from areas")
... (continue your code)
End Sub

 

[gemasoft]

Erel and adamioan Thanks for the quick answer was useful to the second alternative and the error code was marking me Log in and not in the variables.