SQL

From RFO-BASIC! Manual
Manual contents (Statement index)
Language features The basics • Arrays • Data structures • Interrupt routines • User-defined functions
Interfaces Audio player • Bluetooth • Files • Graphics • HTML • Keyboard • Sockets • SQL • Telecom
Programming notes Coexisting with Android

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 in a subdirectory of the RFO-BASIC! installation directory, rfo-basic/databases/. Within a database, the SQL.NEW_TABLE and SQL.DROP_TABLE statements create and delete tables.

A 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 view may also contain data derived from information in the table. Rows of the view may be grouped or sorted as the programmer specifies.

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.

Regular SQL. statements versus the raw interface

Most of the SQL. statements of RFO-BASIC take the values of their parameters and assemble a command to SQLite. The descriptions of some statements, such as SQL.NEW_TABLE, show the exact form of the resulting command to SQLite. The SQL. unit is designed to be simple, but it gives you access to a small number of the features of SQLite.

It is always an option to compose your own command to SQLite and submit it through the raw interface discussed below. For example, to give a table column a constraint (such as to never be null, or to always have its value validated), or to add or remove columns from a table, you would use the raw interface.

A string-only interface

Most of the SQL. statements provide a string-only interface to SQLite. Any columns you specify when creating a table use the SQLite data type TEXT. SQLite returns any null values to the BASIC program as the empty string, "". To convert numeric values to strings to store in a database, BASIC provides STR$(). In addition, FORMAT$() and USING$() perform formatting, including locale-specific formatting. To get the numeric value of a string read from a table, BASIC provides VAL().

SQLite has the tools to apply powerful numeric expressions. For example, there are SQLite functions to obtain the maximum value of a column in any row of the table, or the average of a column across all rows. You can use the raw interface to create numeric columns, then to use these expressions to reduce your view of a table to the rows that are exactly relevant. Performing computations on each row of a table inside SQLite is usually more efficient than having a BASIC program loop through all rows of the table because there is no repeated interpretation of BASIC statements.

Use the raw interface to create numeric columns especially when their values will be compared. String representations of numeric values do not automatically sort as you expect (but see the programming note below for a work-around).

For more information

SQLite is the most widely deployed SQL database engine in the world. Resources include the following:

Database management[edit]

An RFO-BASIC! program must use SQL.OPEN to open a database before it can use any tables in that database. It refers to tables by name (a string expression), and can use SQL.CREATE_TABLE to create new tables, and use SQL.DROP_TABLE to destroy tables. It should use SQL.CLOSE to close any database it has opened, after completing its operations on the tables in that database.

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>

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. See also Memory leaks below.

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

The program does not need to use SQL.NEW_TABLE on existing tables. Statements discussed below insert and delete rows, update values, and perform queries, using string expressions to specify the names of existing tables.

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. Each column has the SQLite data type TEXT. There is no upper limit on the number of columns, but there must be at least one column named.

If <table_name_sexp> gives the name of a table that already exists in the database, then SQL.NEW_TABLE fails and issues an error.

  • To create a table but not produce an error if the table already exists, you can bypass SQL.NEW_TABLE and instead use SQL.EXEC to send the command CREATE TABLE IF NOT EXISTS directly to SQLite.
  • To determine if a table already exists, see the programming note below.

SQLite also creates a unique column in each new table, named .rowid. Usually, it is the tree's primary key. RFO-BASIC! programs can use it, if desired.

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.

Translation

A syntactically valid SQL.NEW_TABLE statement sends the following command to SQLite (using the statement's parameters to formulate the items shown in gray): 

CREATE TABLE table_name (
 _id INTEGER PRIMARY KEY AUTOINCREMENT,
 c1 TEXT,
 c2 TEXT,
  ...
 cn TEXT
 )

Row index columns, over time, can become very large; they do not just go up to the number of rows in the table, but up to the number of rows that have ever been in the table.

This shows that all the columns the BASIC program specified are created as SQL data type TEXT, and the row index column is created as INTEGER. PRIMARY KEY means the row index column contains a value that uniquely identifies the row. AUTOINCREMENT means that, every time you insert a new row, its row index column gets a value one higher than the highest row in the table.

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. If the named table does not exist, SQL.DROP_TABLE has no effect and does not produce an error.

Translation

A syntactically valid SQL.DROP_TABLE statement sends the following command to SQLite: 

DROP TABLE IF EXISTS table_name

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.

Translation

A syntactically valid SQL.INSERT statement sends the following command to SQLite: 

INSERT INTO table_name (c1, c2, ... cn) VALUES (v1, v2, ... vn)

To provide additional information for a table but not create a new row if a suitable row already exists, SQLite supports INSERT OR REPLACE INTO, which can be submitted using SQL.EXEC.

SQL.DELETE[edit]

Delete an existing row from a table

Synopsis
SQL.DELETE <DB_pointer_nvar>, <table_name_sexp>{,<query_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 <query_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_sexp> can be omitted, in which case <count_nvar> must be omitted too. In this case, all the table's rows are deleted.

Translation

A syntactically valid SQL.DELETE statement sends the following command to SQLite: 

DELETE FROM table_name WHERE query

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 value of a string expression used as a query can be any string that is a valid query to SQLite—any syntax SQLite accepts after the keyword WHERE. The string value must not start with the word WHERE; RFO-BASIC! generates it. The simplest syntax is of the form <column name> <operator> <value>. Text 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 text literal, 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 _id automatically added by RFO-BASIC!, or the comparable .rowid column assigned by SQLite. 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.

Presence or absence of data

A query such as the following matches only rows where the specified column has no data: 

Graduate_degrees IS NULL

Similarly, IS NOT NULL matches only rows where the specified column has data.

For more information

SQLite 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> {, <query_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 comma-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 the hidden columns _id and .rowid. For example: First_name, Last_name, _id
    If <columns_sexp> contains *, then each row of the result set contains an entire row of the table, except for the hidden columns.
  • Which rows satisfy the query. The <query_sexp> parameter is any valid SQLite query, as described above. If SQL.QUERY omits <query_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 <query_sexp>, then it may also specify a sort order with <order_sexp>. This string contains the name of a column, which may be followed by modifiers such as these:
    • 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 and treat lowercase letters and the corresponding capital letter as the same. In place of ASC, you can use DESC to specify a descending sort.
    • COLLATE LOCALIZED ASC 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). In place of ASC, you can use DESC to specify a descending sort.

The contents of <query_sexp> and <order_sexp> are passed directly to SQLite. This description is a small fraction of SQLite's capabilities and omits many of the things you can put in these parameters. 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.

Translation

A syntactically valid SQL.QUERY statement sends the following command to SQLite: 

SELECT columns FROM table_name WHERE query ORDER BY order
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.

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.

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${: <query_sexp>}

SQL.UPDATE uses the : 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.

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.

Translation

A syntactically valid SQL.UPDATE statement sends the following command to SQLite: 

UPDATE table_name SET c1=v1, c2=v2, ... cn=vn WHERE query

Raw interface[edit]

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.

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 INTEGER PRIMARY KEY AUTOINCREMENT, " + ~   % Optional, but imitates RFO-BASIC! behavior
 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>.

Programming notes[edit]

Existence of tables[edit]

An RFO-BASIC! program may want to determine whether a table exists. A pertinent example is if the program uses a database table to store persistent settings (information it wants to survive from one running of the program to the next).

The first time the program is ever run, the table does not exist. The program uses SQL.NEW_TABLE to create it, then writes to it the default values for those settings. On subsequent runs, the table does exist. The program must not create it again, but must read the current values for each setting from the table. It is also possible that the user deletes the database, such as with the Android File Manager. The user's intention might be exactly to return the BASIC program to its default settings. In this case, the program must use SQL.NEW_TABLE again.

In summary, the program should follow two courses of action depending on whether a table exists. There are two ways to detect this:

Query the database metadata

Your SQLite database contains not just the tables you created, but a table called sqlite_master, whose rows describe each object in the database, including your tables. Therefore, you can query sqlite_master to see if a specified table exists. 

SQL.OPEN MyDatabase, "ABC-DATABASE"
QueryText$ = "SELECT * FROM sqlite_master WHERE type='table' AND tbl_name='SETTINGS';"
SQL.RAW_QUERY MyCursor, MyDatabase, QueryText$

The program doesn't even need to use SQL.NEXT to walk through the result set. QueryText$ was so specific that the result set either contains 0 or 1 row, the number of tables named SETTINGS that exist. We can obtain the number of rows as follows: 

SQL.QUERY.LENGTH TableAlreadyExists, MyCursor
IF TableAlreadyExists THEN   % 1 is "TRUE" and the following statements are executed
 ...
ENDIF
Let SQL.NEW_TABLE fail

The program can try to create a table and write default settings to it. SQL.NEW_TABLE fails if the table already exists. This programming error can be trapped using the ONERROR: handler. The initialization code might take this form: 

SQL.OPEN MyDatabase, "ABC-DATABASE"
AmTestingForTable = 1    % Prepare for the next statement to fail!
SQL.NEW_TABLE MyDatabase, "SETTINGS", "SettingName", "SettingValue"
AmTestingForTable = 0
! At this point, we create rows in
! "SETTINGS" for each of the desired
! persistent settings

TableAlreadyExists:
! Here we have either created the table
! or found that it already exists.  Read
! the settings from the table into
! program variables.

The error handler may have many additional jobs, but we use the variable AmTestingForTable to indicate that the error occurred trying to create a table. The error handler cannot return to the previous spot, as interrupt handlers can, but it can execute a suitable GOTO: 

ONERROR:
 IF AmTestingForTable THEN GOTO TableAlreadyExists
 ...

Strings representing numbers[edit]

For RFO-BASIC! programs that do not use the raw interface, every column specified for an SQL table is of type TEXT. The text may have a numeric value, such as "2".

Comparing two such columns does not always give the result you expect. For example, "10" is a smaller value than "2" because of its smaller first character. One work-around is to use one of RFO-BASIC!'s formatting functions to prepend leading zeros (so that "0010" is greater than "0002"). However, this forces you to decide what the largest-ever value will be, and numbers with minus signs still do not sort in the intuitive way.

SQLite lets you use these columns in numeric expressions, and it converts them to their numeric value before doing further processing. The simplest way to do this is to add zero to the column data. For example, as <order_sexp> in an SQL.QUERY statement, you might specify columnthree+0. This would make SQLite sort the table not by the value of the TEXT columns but by the value of the numbers they represent.

Conserving row index columns[edit]

Row index columns, such as the _id column that RFO-BASIC! adds when a program executes SQL.NEW_TABLE, or the .rowid column that SQLite adds to every new table, can get large. A new row index is assigned whenever a new row is inserted, but no row index changes when a row is deleted. For example, the magnitude of a row index for a customer table would reflect not the number of current customers but the number of customers there have ever been.

To make such tables more manageable, without techniques like a batch program to copy information to a new customer table with "packed" row indexes, a user-defined function can use SQL to find the best row index to assign to a new row: 

FN.DEF useNum(dbPtr, ffile$, ffield$)
! ------------------------------------------------------------------------------------
! Find the smallest unused row index number
! dbPtr   = Pointer to your database
! ffile$  = your table file
! ffield$ = the field serving as the row index
! ------------------------------------------------------------------------------------
!Formulate a query of places where successive row indexes do not differ by 1
query$ = "SELECT a."+ffield$+" AS aa, c."+ffield$+" AS cc,a."+ffield$+" - 1 AS useNum "~
"FROM "+ffile$+" AS a "~
"LEFT JOIN "+ffile$+" as c "~
"ON    c."+ffield$+" = a."+ffield$+" - 1 "~
"WHERE c."+ffield$+" is null "~
"AND   a."+ffield$+" > 1 "~
"ORDER BY a."+ffield$+" LIMIT 1"

SQL.RAW_QUERY cur, dbPtr, query$
SQL.QUERY.LENGTH rows, cur

!If the above exercise produced rows, return the first "hole"
IF rows > 0 THEN
 SQL.NEXT xdone, cur, aNum$, cNum$, useNum$       
 FN.RTN val(useNum$)

!Otherwise, query the largest value anywhere and return one greater
ELSE
 query$ = "SELECT max("+ffield$+") as useNum FROM "+ffile$
 SQL.RAW_QUERY cur, dbPtr, query$
 SQL.QUERY.LENGTH rows, cur
 IF rows > 0 THEN
  SQL.NEXT xdone, cur,useNum$       
  FN.RTN val(useNum$) + 1
 ENDIF
!If there are no values at all, it's safe to use 1
ENDIF
FN.RTN 1
FN.END

Memory leaks[edit]

Certain valid uses of the SQL interface can nevertheless tie up memory on the Android device. A program that runs continuously and does not observe good programming practices will eventually run out of memory and fail.

The description of SQL.CLOSE mentioned the need to close any database the program opens. Leaving databases open ties up memory on the Android device.

Private version. An emerging version of OLI-BASIC includes an SQL.CLC (CLear Cursors) statement that solves this problem.

Cursors are another aspect that can tie up memory.

RFO-BASIC! closes a cursor for an SQL statement at the end of the execution of that statement. It closes a cursor for a query when the program executes SQL.NEXT that reports the end of the result set.

However, creating a cursor consumes memory of the Android device, and closing it does not release the memory. A program that creates cursors continually will eventually run out of memory and fail.

Sources[edit]

Editor's query[edit]

  • Are the column names case-sensitive? Does the use of underscore in our examples mean that column names cannot include spaces?
Retrieved from "https://rfobasic.miraheze.org/w/index.php?title=SQL&oldid=585"