Page 151

Original Author Paul Laughton, 2011

Page 151

De Re BASIC!

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