SQL

From RFO-BASIC! Manual
Jump to: navigation, search

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 /rfo-basic/databases/. 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.

A string-only interface

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.

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, 10 is a "smaller" value than 2 because of its smaller first character. Using one of the formatting functions to prepend "leading zeros" (so that 0010 is greater than 0002) requires the programmer to envision a largest-ever value, and negative numbers still will not sort in the intuitive way.

For more information

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.

Database management[edit]

SQL.OPEN[edit]

Opens a database

Synopsis
SQL.OPEN <DB_pointer_nvar>, <DB_name_sexp>
Description

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

<DB_name_sexp> can contain the string value :memory:. 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 <DB_pointer_nvar>, 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[edit]

Closes a database

Synopsis
SQL.CLOSE <DB_pointer_nvar>
Description

The SQL.CLOSE statement closes a database previously opened with SQL.OPEN. The variable <DB_pointer_nvar> 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.

SQL.NEW_TABLE[edit]

Creates a new table

Synopsis
SQL.NEW_TABLE <DB_pointer_nvar>, <table_name_sexp>, C1$, C2$, ...,CN$
Description

Once the BASIC program has opened a database and <DB_pointer_nvar> is the database pointer, it can use SQL.NEW_TABLE to create a new table in that database. The SQL.NEW_TABLE statement creates a new table with the name <table_name_sexp>. The subsequent string variables (in the above synopsis, shown as C1$, C2$, and so on) are the column names for the new table. There is no upper limit on the number of columns, but there must be at least one column named.

RFO-BASIC! always adds a column named _id 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 15. The row index counter can be used to connect information in one table to another table. For example, the _id 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.

SQL.DROP_TABLE[edit]

Drops the specified table from the database

Synopsis
SQL.DROP_TABLE <DB_pointer_nvar>, <table_name_sexp>
Description

Once the BASIC program has opened a database and <DB_pointer_nvar> 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 <table_name_sexp>.

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

Inserting and deleting rows[edit]

SQL.INSERT[edit]

Insert a row into a table

Synopsis
SQL.INSERT <DB_pointer_nvar>, <table_name_sexp>, C1$, V1$, C2$, V2$, ..., CN$, VN$
Description

The SQL.INSERT statement inserts a new row into the table named <table_name_sexp> in the previously opened database whose pointer is <DB_pointer_nvar>. The row is appended to the end of the table.

These two parameters are followed by pairs of parameters, such as C1$ and V1$ in the synopsis above. In each pair, Cn$ is the exact name of one of the table's columns, and Vn$ is the value to which to set it.

SQL.DELETE[edit]

Delete an existing row from a table

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

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, whose format is described below.

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.

Queries[edit]

Format of queries[edit]

Queries are requests for information from a table. Queries are used in the SQL.QUERY command, discussed next; as well as in SQL.DELETE and SQL.UPDATE.

The simplest query is of the form <column name> <operator> <value>. Literals must use single-quotes, to keep BASIC from interpreting them as the start or end of a string literal in BASIC. (To include a single-quote character in a query, type two consecutive single-quotes. Do not use the backslash as an escape character.) Here is an example of a query:

First_name = 'John'

The most precise way to retrieve a single row from a table is to use the unique row index column assigned by the database. For example:

_id = '5'

A program might execute SQL.QUERY on everyone named John, as in the first example, but ask for the return of relevant columns and the row index column, whose name is always _id. 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 _id.

Null query

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

For more information

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:

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

SQL.QUERY[edit]

Selects certain rows from a table

Synopsis
SQL.QUERY <cursor_nvar>, <DB_pointer_nvar>, <table_name_sexp>, <columns_sexp> {, <where_sexp> {, <order_sexp>} }
Description

If a BASIC program has opened a database, whose pointer is in <DB_pointer_nvar>, then it can query a table whose name is <table_name_sexp>. This creates a result set and returns, in <cursor_nvar>, a cursor that the program can use to step through the result set with SQL.NEXT.

Each query can specify the following:

  • Which columns of each qualifying row you want. You specify this, in <columns_sexp>, with a column-separated list of the desired column names, in the exact order they should be provided to the program when it executes SQL.NEXT. The list can include _id for the unique row index column of that row. For example:
First_name, Last_name, _id
  • Which rows satisfy the query. This is in the SQLite query language as described above. If SQL.QUERY omits <where_sexp> 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 <where_sexp>, then it may also provide, in <order_sexp>, the column by which to sort the rows. This string contains the name of a single column, a space, and modifiers such as the following:
    • ASC to sort the rows in ascending order (low-to-high), or
    • DESC to sort the rows in descending order (high-to-low).
    • COLLATE NOCASE ASC to perform an ascending sort but treat lowercase letters and the corresponding capital letter as the same, or
    • COLLATE NOCASE DESC to perform a descending sort but treat lowercase letters and the corresponding capital letter as the same.

The contents of <where_sexp> 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 the conversion issues if the column data are brought out to BASIC first.

Example

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.

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[edit]

Get the number of rows that satisfied a query

Synopsis
SQL.QUERY.LENGTH <length_nvar>, <cursor_nvar>
Description

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.

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[edit]

Get the position of a cursor

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

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.

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[edit]

Step to the next row included in a query

Synopsis
SQL.NEXT <done_lvar>, <cursor_nvar>{, <results>}
Description

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.

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>, <results>
IF <done_lvar> THEN D_U.BREAK   % Exit loop when data is exhausted
% Process the <results>
UNTIL 0
Results

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 <results> in the synopsis above can take several forms.

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, <results> 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.
Examples
SQL.NEXT done, cursor, firstname$, lastname$, cv$[], nCols

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[edit]

Change the contents of one or more rows of a table

Synopsis
SQL.UPDATE <DB_pointer_nvar>, <table_name_sexp>, C1$, V1$, C2$, V2$,...,CN$, VN${: <where_sexp>}
Description

The SQL.UPDATE statement makes changes to one or more rows of the table named <table_name_sexp> in the previously opened database whose pointer is <DB_pointer_nvar>. These two parameters are followed by pairs of parameters, such as C1$ and V1$ in the synopsis above. In each pair, Cn$ is the exact name of one of the table's columns, and Vn$ is the value to which to set it.

Following these name/value pairs, there may be a : 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.

Raw interface[edit]

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[edit]

Submit a raw database command directly to SQLite

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

The SQL.EXEC statement lets a BASIC program execute any non-query SQLite command on a previously opened database, specified by <DB_pointer_nvar>.

Examples

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:

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

SQL.RAW_QUERY[edit]

Submit a raw query command directly to SQLite

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

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.

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

Sources[edit]

Editor's queries[edit]

  • 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?


Manual contents
BASIC features Audio playerBluetoothData structuresGraphicsHTMLInterrupt routinesSocketsSQLUser-defined functions
Programming notes Coexisting with Android