*SQL file opened should
always close at the end.
SQLite of Android can be used.
It can use from both Program sauce and Direct command.
SQL mode change is possible.
The variable of Basic can be used as an original function.
The query result is storable not only in text output but array variable.
[Features] The SQLite command is executed.
[Format] SQL sqlcommand
For include a variable of Basic in an SQL sentence; like @var
it attach @ before a variable name.
(Please the blank of one character puts in the end.)
In the case of a character variable, a result of the value enclosed like 'str' is brought.
The end of the command, doesn't have to be with [;].
['] of the SQL sentence cann't be interpreted as rem sentence.
[e.g.] SQL sample.(Use variable at line 50)
Please delete 30 lines of create sentences after the second.
10 dd=2: ss$="Lemon"
20 sql open test1.db
30 sql create table table1(no int, name text)
40 sql insert into table1 values (1,'Grapes')
50 sql insert into table1 values (@dd , @ss$ )
60 sql insert into table1 values (3,'Banana')
70 sql select * from table1
80 sql close
[Features] Sets the Output destination of the query result, and SQL mode.
[Format] SQL EX_SQL [Two-dimensional character array name] [,on|off]
The character array variable which outputs a query result is specified by a variable name.
It is necessary to be the beforehand defined two-dimensional array.
It is stored in sequence of Array-(Item,Record)
Columns name(Item) is stored in the zeroth record.
It becomes text output when it omits.(Default)
SQL mode is specified with the second parameter.
It isn't necessary to attach 'SQL' to beginning of sentence in SQL mode.
By specified 'on', all are interpreted as a SQL command.(Edit & Execution command can be used)
How to escape from the SQLmode-(1.'ex_sql ,off' is performed. 2.To switch mode from the menu.)
And also, on the case of converting '@variable' to string,
it can set whether to enclose string with ''.
EX_SQL ,VON 'set enclosing'(default)
EX_SQL ,VOFF 'set not enclosing'
(*SQL strings data are usually enclosed in '' like 'sample')
ver4.16, It is now possible to 'The setting which makes a SQL file close automatically when the program ends'.
EX_SQL ,ATON automatically close at end.
EX_SQL ,ATOFF not automatically close at end.(default)
Regardless of this setting, as much as possible, please make them close with 'SQL CLOSE' statement in the program.
By c-mode & 'EX_SQL ,ON' configuration, the next case is careful.
The prefix 'SQL ' is not added in 'GOSUB' & 'FUNC' jump area.
However, it is possible to set 'EX_SQL ,ON' in the local jump area.
Also there is a way to describe 'SQL' each time.
[e.g.] The sample which sets it in SQL mode and dedicates result to Array.
10 dim sd$(8,64) :'(Item,Record)
20 ex_sql sd$,on :'SQL mode
30 open test2.db
40 create table table1(no int, name text)
50 insert into table1 values (1,'Grapes')
60 insert into table1 values (2,'Lemon')
70 insert into table1 values (3,'Banana')
80 select * from table1
100 ex_sql ,off :'mode cancel
110 print sd$(0,0);" ";sd$(1,0) :'Item
120 print sd$(0,1);sd$(1,1)
130 print sd$(0,2);sd$(1,2)
140 print sd$(0,3);sd$(1,3)
--- Shows the basic usage of SQLite command. ---
For details, please refer to the specialized document of SQLite.
[Features] To Open the database.
[Format] SQL OPEN Filename
With SQL in This Basic, when opening the existing DB and opening new DB,
both use 'SQL OPEN'.
The last closes DB by 'SQL CLOSE'.
sql open test1.db
Database access folder is 'data/data/and.bas/databases/'.
files"sql:" -It can be viewing the files in this SQL folder.
When there is no folder specification, the file in 'data/data/and.bas/databases/' is treated.
When use DB file of SD card, specify the full path of SD card.
In this case, the original file, the backup file is generated attached '.bak'.
sql open mnt/sdcard/and.bas/test2.db
Deletion of a database, delete the database file in the folder with the 'kill' command.
'curdir$(2) indicate SQL folder.
[Features] To Close the database.
[Format] SQL CLOSE
Please close opened DB at the end.
|SQL CREATE TABLE|
[Features] To Create a table.
[Format] SQL CREATE TABLE TableName(QueryName DataType, QueryName DataType,...)
sql create table table1(no int, name text)
[Features] To Insert of data.
[Format] SQL INSERT INTO TableName VALUES (Data, Data...)
sql insert into table1 values (1,'Grapes')
[Features] To Select of data, the output of query result.
[Format] SQL SELECT QueryName, QueryName... FROM TableName [WHERE Conditions]
When all items are specified in the [*].
sql select no,name from table1
sql select * from table1 where no<10
[Features] To Update of data.
[Format] SQL UPDATE TableName SET QueryName=Data WHERE Conditions
sql update table1 set name='Apple' where no=3
[Features] To Delete of data.
[Format] SQL DELETE FROM TableName WHERE Conditions
sql delete from table1 where no=1
- Some control commands can be used in SQL mode.
Commands available in SQL mode. (all functions can be used)
'(rem) let dim print input goto gosub return func endfunc select case ex_sql
if-then-else for-next while-wend repeat-until continue break data read restore
run end new list edit renum console load save files kill copy font
The abbreviation of 'let' isn't possible by
the substitution of the variable and Arrays.
e.g. let var=10
Label can't be used in SQL mode.