SQL

Android devices include an SQL database using the SQLite product. SQLite is a self-contained, transactional SQL database engine. RFO-BASIC! provides an interface to SQLite.

Each database is a file, which can contain tables of information. Databases reside on the base drive where RFO-BASIC! was installed (usually the SD card), in the directory. Within a database, the SQL.NEW_TABLE and SQL.DROP_TABLE statements create and delete tables. The table comprises rows and columns. A single row of a database might describe a single person, song in a music collection, or item in a store's inventory. If the rows of a database described persons, each row's columns might give first name, last name, address, and phone number.

The BASIC program gains access to the database by forming a query describing the criteria on which to select rows from the database. The result is a view into the table, containing some or all of its rows. The SQL.QUERY statement submits a query to SQLite, which returns a cursor into that view of the database. The BASIC program can use this cursor to step through the rows that satisfy its query.

SQLite is the most widely deployed SQL database engine in the world. Full details about SQLite are on the SQLite Home Page, and an excellent online tutorial on SQL is at www.w3schools.com.
 * For more information

SQL.OPEN
Opens a database

SQL.OPEN , 
 * Synopsis

The SQL.OPEN statement opens a database for access.  is the name of the database. If there is no database by that name, SQL.OPEN creates it.
 * Description

 can contain the string value. In this case, SQL.OPEN creates a temporary database in main memory. Temporary databases do not persist after the program ceases to execute.

Like files, which the BASIC program operates on with pointers, the SQL.OPEN statement returns a pointer, in , which the program must use in subsequent commands to refer to the database it opened. If a BASIC program opens several databases at the same time, these pointers will be different and will specify the database to which any subsequent SQL. statement applies.

SQL.CLOSE
Closes a database

SQL.CLOSE 
 * Synopsis

A BASIC program should always execute SQL.CLOSE on all databases it has opened. Failure to do so can reduce the amount of memory available on your Android device.  The SQL.CLOSE statement closes a database previously opened with SQL.OPEN. The variable  specifies which database to close; SQL.CLOSE sets this variable to 0. It no longer refers to any database, but can be reused, such as in another call to SQL.OPEN.
 * Description

SQL.NEW_TABLE
Creates a new table SQL.NEW_TABLE ,  to every new table. This is called a row index column and is unique for each row in that table. For example, the row index column of the fifteenth row created would have the value. The row index counter can be used to connect information in one table to another table. For example, the  in a customer table could appear in a database of orders to uniquely identify a customer, in place of name or phone number or other information from the customer table that might not always be unique.
 * Synopsis

SQL.DROP_TABLE
Drops the specified table from the database

SQL.DROP_TABLE , 
 * Synopsis

Once the BASIC program has opened a database and  is the database pointer, it can use SQL.DROP_TABLE to drop (delete) a table from that database. The table is specified by its name, given in .
 * Description

If the named table exists, it is dropped from the database. Subsequent references to that table name will fail unless the table should be created again.

SQL.INSERT
Insert a row into an open table

SQL.INSERT , , C1$, V1$, C2$, V2$, ..., CN$, VN$
 * Synopsis

The SQL.INSERT statement inserts a new row into a table in a previously opened database. The  is the name of the table that receives the new row. The row is appended to the end of the table.
 * Description

C1$, V1$, C2$, V2$, ..., CN$, VN$: The column name and value pairs for the new row. These parameters must be in pairs. The column names must match the column names used to create the table. Note that the values are all strings. When you need a numeric value for a column, use the BASIC! STR$(n) to convert the number into a string. You can also use the BASIC! FORMAT$(pattern$, N) to create a formatted number for a value. (The Values-as-strings requirement is a BASIC! SQL Interface requirement, not a SQLite requirement. While SQLite, itself, stores all values as strings, it provides transparent conversions to other data types. I have chosen not to complicate the interface with access to these SQLite conversions since BASIC! provides its own conversion capabilities.)
 * Edit me

SQL.DELETE
Delete an existing row from an open table

SQL.DELETE <DB_pointer_nvar>, <table_name_sexp>{,<where_sexp>{,<count_nvar>} }
 * Synopsis

The SQL.DELETE statement deletes any rows from a table in a previously opened database that satisfy a given query. The <table_name_sexp> is the name of the table from which to delete rows, and <where_sexp> is a query, in exactly the same format as for SQL.QUERY, described below.
 * Description

SQL.DELETE sets the parameter <count_nvar> to the number of rows that the statement deleted.

The query can be omitted, in which case <count_nvar> must be omitted too. In this case, all the table's rows are deleted.

Rest of chapter
Sql.query <cursor_nvar>, <DB_pointer_nvar>, <table_name_sexp>, <columns_sexp> {, <where_sexp> {, <order_sexp>} }

Queries a table of a previously-opened database for some specific data. The command returns a Cursor named <Cursor_nvar> to be used in stepping through Query results.

The <columns_sexp> is a string expression with a list of the names of the columns to be returned. The column names must be separated by commas. An example is Columns$ = "First_name, Last_name, Sex, Age". If you want to get the automatically incremented Row Index Column then include the "_id" column name in your column list. Columns may be listed in any order. The column order used in the query will be the order in which the rows are returned.

The optional <where_sexp> is an SQL expression string used to select which rows to return. In general, an SQL expression is of the form <Column Name> <Value>. For example, Where$ = "First_name = 'John' " Note that the Value must be contained in single quotes. Full details about the SQL expressions can be found here. If the Where parameter is omitted, all rows will be returned.

The optional <order_sexp> specifies the order in which the rows are to be returned. It identifies the column upon which the output rows are to be sorted. It also specifies whether the rows are to be sorted in ascending (ASC) or descending (DESC) order. For example, Order$ = "Last_Name ASC" would return the rows sorted by Last_Name from A to Z. If the Order parameter is omitted, the rows are not sorted.

If the Order parameter is present, the Where parameter must be present. If you want to return all rows, just set Where$ = "" Sql.query.length <length_nvar>, <cursor_nvar>

Report the number of records returned by a previous Query command, Given the Cursor returned by a Query, the command writes the number of records into <length_nvar>. This command cannot be used after all of the data has been read. Sql.query.position <position_nvar>, <cursor_nvar>

Report the record number most recently read using the Cursor of a Query command. Given the Cursor returned by a Query, the command writes the position of the Cursor into <Position_nvar>. Before the first Next command, the Position is 0. It is incremented by each Next command. A Next command after the last row is read sets its Done variable to true and resets the Cursor to 0. The Cursor can no longer be used, and this command can no longer be used with that Cursor. Sql.next <done_lvar>, <cursor_nvar>{, <cv_svars>}

Using the Cursor generated by a previous Query command (Sql.query or Sql.raw_query), step to the next row of data returned by the Query and retrieve the data.

<done_lvar> is a Boolean variable that signals when the last row of the Query data has been read.

<cursor_nvar> is a numeric variable that holds the Cursor pointer returned by a Query command. You may have more than one Cursor open at a time.

<cv_svars> is an optional set of column value string variables that return data from the database to your program. The Cursor carries the values from the table columns listed in the Query. Sql.next retrieves one row from the Cursor as string values and writes them into the <cv_svars>. If any of your columns are numeric, you can use the BASIC! VAL(str$) function to convert the strings to numbers.

When this command reads a row of data, it sets the Done flag <done_lvar> to false (0.0). If it finds no data to read, it changes the Done flag to true (1.0) and resets the cursor variable <cursor_nvar> to zero. The Cursor can no longer be used for Sql.next operations. The cursor variable may be used with another Cursor from a different Query.

In its simplest form, <cv_svars> is a comma-separated list of string variable names. Each variable receives the data of one column. If there are more variables than columns, the excess variables are left unchanged. If there are more columns than variables, the excess data are discarded.

SQL.NEXT done, cursor, cv1$, cv2$, cv3$       % get data from up to three columns

The last (or only) variable may be a string array name with no index(es):

SQL.NEXT done, cursor, cv$[]                       % get data from ALL available columns

The data from any column(s) that are not written to string variables are written into the array. If no column data are written to the array, the array has one element, an empty string "". If the variable names an array that already exists, it is overwritten.

If the last (or only) <cv_svars> variable is an array, you may also add (after another comma) a numeric variable <ncol_nvar>. This variable receives the total number of columns in the cursor. Note that this is not necessarily the same as the size of the array.

SQL.NEXT done, cursor, cv$[], nCols               % report number of columns available

The full specification for this command, including the optional array and column count, is as follows:

Sql.next <done_lvar>, <cursor_nvar> {, svar}... {, array$[] {, <ncol_nvar>}} Sql.delete <DB_pointer_nvar>, <table_name_sexp>{,<where_sexp>{,<count_nvar>} }

From the named table of a previously opened database, delete rows selected by the conditions established by the Where string expression. The Count variable reports the number of rows deleted.

The formation of the Where string is exactly the same as described in the Sql.query command. Both Where and Count are optional. If the Where string is omitted all rows are deleted, and the Count variable must be omitted, too. Sql.update <DB_pointer_nvar>, <table_name_sexp>, C1$, V1$, C2$, V2$,...,CN$, VN${: <where_sexp>}

In the named table of a previously opened database, change column values in specific rows selected by the Where$ parameter <where_sexp>. The C$,V$ parameters must be in pairs. The colon character terminates the C$,V$ list and must precede the Where$ in this command. The Where$ parameter and preceding colon are optional.

BASIC! also uses the colon character to separate multiple commands on a single line. The use of a colon in this command conflicts with that feature. Use caution when using both together.

If you put a colon on a line after this command, the preprocessor always assumes the colon is part of the command and not a command separator. If you are not certain of the outcome, the safest action is to put the Sql.update command on a line by itself, or at the end of a multi-command line.

Going around BASIC
Using the raw interface and the ordinary BASIC interface could produce undesired side-effects. For example, if cursors were open using SQL.QUERY and the program submitted a raw "INSERT" command to SQLite, it could create columns that would satisfy the queries but would not be in the result set. A BASIC program could use the raw interface to drop a table into which it had cursors. Using those cursors would produce a run-time error. </DIV> In some cases, it is more convenient not to use the SQL. unit of RFO-BASIC! but to submit commands directly to SQLite. The SQL. unit provides two statements to make this possible from a BASIC program.

SQL.EXEC
Submit a raw database command directly to SQLite

SQL.EXEC <DB_pointer_nvar>, <command_sexp>
 * Synopsis

The SQL.EXEC statement lets a BASIC program execute any non-query SQL command on a previously opened database, specified by <DB_pointer_nvar>. For example, the program can execute "CREATE TABLE", "DELETE", and "INSERT".
 * Description

SQL.RAW_QUERY
Submit a raw query command directly to SQLite

SQL.RAW_QUERY <cursor_nvar>, <DB_pointer_nvar>, <query_sexp>
 * Synopsis

The SQL.RAW_QUERY statement lets a BASIC program execute any query command on a previously opened database, specified by <DB_pointer_nvar>. SQL.RAW_QUERY returns a cursor with which to gain access to the table rows that satisfy the query.
 * Description

Editor's queries

 * SQL.CLOSE sidebar: BASIC does not automatically close databases on exit?  "Failure to do so can reduce the amount of memory available on your Android device."  This sounds like damage will be done to the device.