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.
| SQL sqlcommand |
[Features] The SQLite command is
executed.
[Format] SQL sqlcommand
[Explanation]
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.
[smp_sql.bas]
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
[Result]
no
name
-- -------
1 Grapes
2 Lemon
3 Banana
OK
| SQL EX_SQL |
[Features] Sets the Output destination
of the query result, and SQL mode.
[Format] SQL
EX_SQL [Two-dimensional character array name] [,on|off]
[Explanation]
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.
*Attention
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.
[smp_sql_ary.bas]
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
90 close
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)
[Result]
no
name
1Grapes
2Lemon
3Banana
--- Shows the basic usage of
SQLite command. ---
For
details, please refer to the specialized document of SQLite.
| SQL OPEN |
[Features] To Open the database.
[Format] SQL
OPEN Filename
[Explanation]
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'.
[e.g.]
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'.
[e.g.]
sql open mnt/sdcard/and.bas/test2.db
Deletion of a database, delete the database file
in the folder with the 'kill' command.
[e.g.]
kill
curdir$(2)+"test1.db"
'curdir$(2) indicate
SQL folder.
| SQL CLOSE |
[Features] To Close the
database.
[Format] SQL CLOSE
[Explanation]
Please close opened DB at the end.
[e.g.]
sql close
| SQL CREATE TABLE |
[Features] To Create a table.
[Format] SQL CREATE TABLE
TableName(QueryName DataType, QueryName DataType,...)
[Explanation]
[e.g.]
sql
create table table1(no int, name text)
| SQL INSERT |
[Features] To Insert of data.
[Format] SQL INSERT INTO TableName
VALUES (Data, Data...)
[Explanation]
[e.g.]
sql
insert into table1 values (1,'Grapes')
| SQL SELECT |
[Features] To Select of data, the
output of query result.
[Format] SQL SELECT QueryName,
QueryName... FROM TableName [WHERE Conditions]
[Explanation]
When all items are specified in the
[*].
[e.g.]
sql select no,name from table1
sql select * from table1 where no<10
| SQL UPDATE |
[Features] To Update of data.
[Format] SQL UPDATE TableName SET
QueryName=Data WHERE Conditions
[Explanation]
[e.g.]
sql
update table1 set name='Apple' where no=3
| SQL DELETE |
[Features] To Delete of data.
[Format] SQL DELETE FROM TableName
WHERE Conditions
[Explanation]
[e.g.]
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.