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 can contain files, each containing a table of information. Database files reside on the base drive where RFO-BASIC! was installed (usually the SD card), in the directory. The SQL.NEW_TABLE and SQL.DROP_TABLE statements create and delete tables (files). 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

Closes a previously opened database.  will be set to zero. The variable may then be reused in another sql.open command. You should always close an opened database when you are done with it. Not closing a database can reduce the amount of memory available on your Android device.
 * Description

SQL.NEW_TABLE
Creates a new table SQL.NEW_TABLE , , C1$, C2$, ...,CN$
 * Synopsis

A single database may contain many tables. A table is made of rows of data. A row of data consists of columns of values. Each value column has a column name associated with it.
 * Description

This command creates a new table with the name  in the referenced opened database. The column names for that table are defined by the following: C1$, C2$, ..., CN$. At least one column name is required. You may create as many column names as you need.

BASIC! always adds a Row Index Column named "_id" to every table. The value in this Row Index Column is automatically incremented by one for each new row inserted. This gives each row in the table a unique identifier. This identifier can be used to connect information in one table to another table. For example, the _id value for customer information in a customer table can be used to link specific orders to specific customers in an outstanding order database. Sql.drop_table , 

The table named  in the opened database pointed to by  will be dropped from the database if the table exists.

Sql.insert , , C1$, V1$, C2$, V2$, ..., CN$, VN$

Inserts a new row of data columns and values into a table in a previously opened database.

The <table_name_sexp> is the name of the table into which the data is to be inserted. All newly inserted rows are inserted after the last, existing row of the table.

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.) 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. Sql.exec <DB_pointer_nvar>, <command_sexp>

Execute ANY non-query SQL command string ("CREATE TABLE", "DELETE", "INSERT", etc.) using a previously opened database. Sql.raw_query <cursor_nvar>, <DB_pointer_nvar>, <query_sexp>

Execute ANY SQL Query command using a previously opened database and return a Cursor for the results.