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 specifying how 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. After a single pass through the view, the cursor becomes unusable, though the program can make the same query again.

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 specify a sort order with .  This string contains the name of a column, which may be followed by modifiers such as these:
 * to sort the rows in ascending order (low-to-high), or
 * to sort the rows in descending order (high-to-low).
 * to perform an ascending sort and treat lowercase letters and the corresponding capital letter as the same.   may be   to specify a descending sort.
 * to perform an ascending sort and use a sort order appropriate for the device's locale. For example, this ensures that international characters such as é are treated as e instead of being sorted according to their code order (in which they are beyond the traditional letters).    may be   to specify a descending sort.

The contents of  and <order_sexp> are passed directly to SQLite. The above description is a small fraction of SQLite's capabilities. For example, <order_sexp> can contain one sort specification or a list of sort specifications separated by commas, perhaps to sort by first name those rows where last name is identical. SQLite contains many numeric operators, such as AVG, which can be applied to numeric columns of a database table and avoid conversion problems that would occur if the column data are brought out to BASIC first. Specifying as the sort basis  (a numeric formula) makes SQLite evaluate the column entries as numbers, so that   is not "less than"   based on its string value.

Query the PERSONNEL table for a list of employees living in Hudson, highest salaries first. In the case of several employees with the same salary, sort by last name, then first name.
 * Example

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

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

SQL.QUERY.LENGTH <length_nvar>, <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 SQL.NEXT to step through the entire list of rows that satisfied the query, because the call to SQL.NEXT that reports the end of the list also invalidates the cursor.

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 SQL.NEXT to step through the entire list of rows that satisfied the query, because the call to SQL.NEXT that reports the end of the list also invalidates the cursor.

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. SQL.NEXT creates, or increases the dimension of, the array if necessary to hold all the columns the query calls for. In this 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. Therefore, no additional string variables are allowed after the string 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 because:
 * The count includes the column data returned in any string scalars that preceded the array in the parameter list; and
 * If no columns were left to assign to the string array, the count will include 1 to reflect the empty string written to the first element 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 into the array cv$[]. It sets nCols to the number of columns that cursor obtained, and sets done to 0.

If the program had already retrieved the final row satisfying the query, SQL.NEXT sets done to 1 and sets cursor to 0.

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 text after  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. A BASIC program can also execute SQLite  commands to set the general behavior of the database.

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?