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. In any table row, any column might not contain information. These columns have "null values."

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.

The SQL. unit of RFO-BASIC! is a string-only interface to SQLite. (In cases where this is not sufficient, programs can use the raw interface described at the end of this article.) Null values are returned to the BASIC program as the empty string,. To store numeric values in a database as strings, BASIC provides STR$ to convert numerics to strings, and FORMAT$ and USING$ to permit formatting, including locale-specific formatting. To get the numeric value of a string read from a table, the program can use the VAL function.
 * A string-only interface

Though this presents a simpler interface to the BASIC programmer, it has problems. String representations of numeric values do not automatically sort in numeric order; without work,  is a "smaller" value than   because of its smaller first character. Using one of the formatting functions to prepend "leading zeros" (so that  is greater than  ) requires the programmer to envision a largest-ever value, and negative numbers still will not sort in the intuitive way.

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 go away when the program stops executing.

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 ,. The program analyzes the returned data to identify the one row it is interested in, and executes SQL.UPDATE with a query limited to that value of.
 * Synopsis

A query that is an empty string always makes the request apply to all the table's rows.
 * Null query

The SQLite query language provides many tools to formulate more complex queries. A program can obtain from the table rows that satisfy any of a list of conditions, or only those rows that satisfy all of them. This example searches for two specific persons in the table:
 * For more information

First_name = 'John' AND Last_name = 'Smith' OR First_name = 'Mary' AND Last_name = 'Doe'

SQL.QUERY
Selects certain rows from a table

SQL.QUERY , ,  for the unique row index column of that row. For example: First_name, Last_name, _id
 * Synopsis
 * Which rows satisfy the query. This is in the SQLite query language as described above.  If SQL.QUERY omits  or if it is an empty string, then all the table's rows satisfy the query, and the sort option described below is not available.
 * Which order to sort the rows. If the program provided a query expression in , then it may also provide, in , a single column by which to sort the rows.  This string contains the name of a single column, a space, and one of the following:
 * to sort the rows in ascending order (low-to-high), or
 * to sort the rows in descending order (high-to-low).

Query the PERSONNEL table for a list of employees living in Hudson, sorted by salary.
 * Example

SQL.OPEN DBptr, "ACME_CORP" Query1 = "Hometown = 'Hudson'" DesiredColumns = "First_name, Last_name, Salary, Phone_no" SortOrder = "Salary DESC" SQL.QUERY cur1, DBptr, "PERSONNEL", DesiredColumns, Query1, SortOrder

SQL.QUERY.LENGTH
Get the number of rows that satisfied a query

SQL.QUERY.LENGTH , <cursor_nvar>
 * Synopsis

If the program has successfully made a query and <cursor_nvar> is the cursor that was returned, then SQL.QUERY.LENGTH sets <length_nvar> equal to the number of rows that satisfied the query.
 * Description

A program cannot use SQL.QUERY.LENGTH after it uses to step through the entire list of rows that satisfied the query.

SQL.QUERY.POSITION
Get the position of a cursor

SQL.QUERY.POSITION <position_nvar>, <cursor_nvar>
 * Synopsis

If the program has successfully made a query and <cursor_nvar> is the cursor that was returned, then SQL.QUERY.POSITION sets <position_nvar> to the position of that cursor in the rows that satisfied the query. Immediately after the query is made, SQL.QUERY.POSITION reports 0. After each SQL.NEXT command, the value that SQL.QUERY.POSITION would report is incremented by 1.
 * Description

A program cannot use SQL.QUERY.POSITION after it uses to step through the entire list of rows that satisfied the query.

SQL.NEXT
Step to the next row included in a query

SQL.NEXT <done_lvar>, <cursor_nvar>{, }
 * Synopsis

A query (performed by either SQL.QUERY or SQL.RAW_QUERY) identifies rows from a named table and provides a cursor with which to step through those rows. If the BASIC program has opened a database and done a query on one of its tables, which returned <cursor_nvar>, then it steps through the rows by repeatedly executing the SQL.NEXT statement.
 * Description

The first execution returns data from the first row that satisfied the query. Any execution of SQL.NEXT that returns row data (including from the final row of the query) sets <done_lvar> to FALSE (0). Executing SQL.NEXT after it has returned all the rows of the query sets <done_lvar> to TRUE (1). (It also sets <cursor_nvar> to 0, preventing further use of this cursor, though the variable itself can be reused, such as to be the cursor of a subsequent query.) A template for processing the result set of a query is as follows:

DO SQL.NEXT <done_lvar>, <cursor_nvar>, IF <done_lvar> THEN D_U.BREAK  % Exit loop when data is exhausted % Process the UNTIL 0

Following the first two parameters may be variables into which SQL.NEXT deposits values from the row it retrieved. If there are no variables, then SQL.NEXT does nothing but step the cursor one row through the result set. The parameter shown as in the synopsis above can take several forms.
 * Results

If some columns of the table are designed to represent numeric values, SQL.NEXT must provide string variables to hold their string representations. The program can then use VAL to obtain the numeric values. See the note at the start of this article. </DIV> The <columns_sexp> parameter provided to the prior call to SQL.QUERY specified which columns each SQL.NEXT should return, and in which order. If SQL.NEXT provides a comma-separated list of string variables, each one receives the data of one column. If there are more variables than columns, the excess variables are unchanged. If there are more columns than variables, the excess columns are not returned to the program.

In its simplest form, is a comma-separated list of string variable names. Each variable receives the data of one column. A synopsis of this form of SQL.NEXT is as follows:

SQL.NEXT <done_lvar>, <cursor_nvar>, <columndata1_svar>{, <columndata2_svar> ...}

Following — or instead of — any string variables may be a string array, in which case the synopsis is:

SQL.NEXT <done_lvar>, <cursor_nvar>, {<columndata1_svar>, {<columndata2_svar> ...,}} <others_sarr>{, <ncol_nvar>}

SQL.NEXT writes the values of any column(s) that were not written to the preceding string variables 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.

In the case an array is used, it can be followed by another comma and a numeric variable, shown above as <ncol_nvar>. SQL.NEXT sets this to the total number of columns retrieved. This is not necessarily the same as the size of the array.

SQL.NEXT done, cursor, firstname$, lastname$, cv$[], nCols
 * Examples

Assuming a previous query that selected rows from the table and specified to return first name, last name, and other columns from each row, this SQL.NEXT uses the query's cursor, puts the first name in firstname$, the last name in lastname$, and as many other columns that were specified in the query, and that fit, into the array cv$[]. It sets nCols to the number of columns that cursor obtained, and sets done to indicate whether this retrieval was from the final row in the query.

SQL.UPDATE
Change the contents of one or more rows of a table

SQL.UPDATE <DB_pointer_nvar>,  character, which BASIC also uses to separate statements. Do not use  to append another BASIC statement on the same line, after SQL.UPDATE. The preprocessor assumes that the character is part of the SQL.UPDATE statement. </DIV> The SQL.UPDATE statement makes changes to one or more rows of the table named  character followed by a query in the format given above. If the query is empty or omitted, then the specified changes are made to every row of the table.
 * Synopsis
 * Description

Raw interface
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 RFO-BASIC! statements that present a BASIC-style interface to SQLite, but to submit "raw" commands directly to SQLite. The following two statements enable this in BASIC programs.

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 SQLite command on a previously opened database, specified by <DB_pointer_nvar>.
 * Description

SQLite has many features that are not represented in the RFO-BASIC! interface described in this article. For example, to evade the strings-only limitation of RFO-BASIC! and create a table with numeric columns, a BASIC program could submit a raw command:
 * Examples

CommandText$ = "CREATE TABLE " + tbname$ + " ( " + ~       "_id TEXT, " + ~        colname1$ + " TEXT, " + colname2$ + " TEXT, " + ~        colname3$ + " INTEGER, " + colname4$ + " INTEGER) " SQL.EXEC dbptr, CommandText$

As another example, SQLite has a transaction interface that lets a program specify changes to several tables with the guarantee that either all changes will occur, or none will. Access to this feature requires that the program issue raw commands such as.

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 SQLite 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

The SQL.RAW_QUERY statement does not have the parameters that SQL.QUERY has that specify desired columns and sort order. This is achieved in the raw query in the SELECT clause included in <query_sexp>.

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.
 * Are the column names case-sensitive? Does the use of underscore in our examples mean that column names cannot include spaces?
 * Your table has 20 columns. You make a query for 12 columns, then execute SQL.NEXT done,cursor,A$,B$,C$[],count where C$[] has been DIMmed to 5; a total of 7 string variables.  What's the count?