Sqlite

SQLite
toc This is not an SQLite tutorial. This article explains how to access SQLite from within Run BASIC. To learn more about SQLite, go to the [|SQLite website.]Be sure to read the Sqlite tutorial that is contained in the Run BASIC documentation.

Here is a general SQLite tutorial that is very clear and helpful: http://sqlcourse.com/

Connecting to a Database
The **SQLITECONNECT** statement creates a new database or opens an existing database. It returns a database accessor object and assigns it to the variable #handle specified. The simple act of connecting to a database that doesn't exist causes it to be created.

Syntax: To connect to the database: SQLITECONNECT #handle, environmentExpr$ - Create a SQLITE connection object for environmentExpr$ and assign to #handle

To disconnect from the database:
 * 1) handle DISCONNECT

Important Practices
//In web programming it is important not to leave things like databases open because if the user closes the browser the server cannot know that this has happened. So, when programming using SQLite always open the database, do your work and close the database right away.//

Tables
Data is stored in tables. To create a table called "Members", use the **execute** method:

code sqliteconnect #s, "mydata.db" query$ = "create table Members (name char(50) default 'N/A', country char(24) default 'N/A', rank char(16)

default 'N/A')" code
 * 1) s execute(query$)
 * 2) s disconnect

Tables are created with **create table** followed by a name for the table. This is followed by a list of columns and their data types, inside parentheses. The columns in the example above are "name", "country" and "rank". The data is all of type char(num), where (num) is the length of the string of characters for data in that column. A default value may be included. This value will be used for new entries that don't set a value for a column when they are inserted. The query string looks like this without the default values:

code sqliteconnect #s, "mydata.db" query$ = "create table Members (name char(50), country char(24), rank char(16))" code
 * 1) s execute(query$)
 * 2) s disconnect

Attempting to create a table that already exists will cause an error. You can check if the database already exists, and if it does, you can omit the code to create a table. You can check for file existence with a FILES accessor object.

code 'create file accessor to see if database file exists files #a, "mydata.db"

'check for existence exists = #a hasAnswer

'if database doesn't exist, create it if not(exists) then sqliteconnect #s, "mydata.db" query$ = "create table Members (name char(50) default 'N/A', country char(24) default 'N/A', rank char(16)

default 'N/A')" end if code
 * 1) s execute(query$)
 * 2) s disconnect

SQLite Methods

 * 1) handle EXECUTE(expr$) - Execute the SQL query in expr$
 * 2) handle DISCONNECT - Disconnect from the database
 * 3) handle HASANSWER - Return true (nonzero) if there are result rows to read from the last query
 * 4) handle NEXTROW$(delimiter$) - Return the next row as a string using delimiter$ to separate each item
 * 5) handle ROWCOUNT - Return the number of unread result rows from the last query
 * 6) handle COLUMNNAMES$ - Return a string containing the comma delimited column names from the last query
 * 7) handle DEBUG$ - Return a string containing "Sqlite"
 * 8) handle ISNULL - Return zero (false) because the database accessor is not null.

The following methods are for rendering the result of a query to a web page as a TABLE
 * 1) handle CAPTION(expr$) - Set the caption using expr$
 * 2) handle CSSCLASS(expr$) - Set the CSS class tag to expr$
 * 3) handle TRCLASS(expr$) - Set the CSS class tag for table rows to expr$
 * 4) handle TDCLASS(expr$) - Set the CSS class tag for data items to expr$
 * 5) handle THCLASS(expr$) - Set the CSS class tag for header row items to expr$
 * 6) handle CAPTIONCLASS(expr$) - Set the CSS class tag for the caption to expr$
 * 7) handle ALLCLASS(expr$) - Set the CSS class tag for all of the properties to expr$
 * 8) handle LINK(columnNameExpr$, handler) - Make the items in a column into links using handler when the user clicks

Special Variables

 * RowIndex - The numeric index of the table or database accessor link that generated the last user event
 * NullColumn$
 * NullColumn

When using a QueryResultRow object Run BASIC will translate NULL values into 0 when asked for a numeric value, and into an empty string when asked for a string. You can change the default values to something else if you like by changing the contents of the NullColumn$ and NullColumn variables, which are globally visible.

Executing Queries
In SQLite, data is manipulated by executing queries, which are strings of command information. This is done with the **execute** method. Here is an example that uses **execute** to **insert** a row of data into an existing table.

code sqliteconnect #s, "mydata.db"

'create a query string to add a record to the table called Members query$ = "insert into Members (name, country, rank) values (""John Smith"", ""United States"", ""Novice"")"

'use execute method to execute query
 * 1) s execute(query$)

code
 * 1) s disconnect

Displaying Data
The easiest way to display data is with the **Render** statement, which renders formatted results from the last query. The query command to use is **select**. You must **Render** the table before disconnecting from the database. Here is a small example that selects the data from all columns and displays it in a table:

code 'display info from the database 'connect sqliteconnect #s, "mydata.db"

'create query string query$ = "select * from Members"

'execute query
 * 1) s execute(query$)

'render results render #s


 * 1) s disconnect

code

If you don't want to display the data from all columns, specify which columns are to be displayed:

code 'display info from the database 'connect sqliteconnect #s, "mydata.db"

'create query string to select ONLY TWO columns: 'query$ = "select name, country from Members"

'execute query
 * 1) s execute(query$)

'render results render #s

code
 * 1) s disconnect

Formatting the Appearance
You can use **CSSCLASS** statements to format the appearance of the table that displays the data. General **CSSCLASS** statements will be applied like this:

code 'display info from the database 'connect sqliteconnect #s, "mydata.db"

'create query string query$ = "select * from Members"

'execute query
 * 1) s execute(query$)

'display will use cssclasses for table elements without explicit assignment cssclass "table", "{ width: 400px; border: 6px outset #C0C0C0 ; border-spacing: 2px }" cssclass "tr", "{ background: #CCF; padding: 3px }"

'render results render #s


 * 1) s disconnect

code

You can also use the many CSS display methods of the SQLite object. The following demo overrides the general table-row class with a special SQLite table-row class:

code 'display info from the database 'connect sqliteconnect #s, "mydata.db"

'create query string query$ = "select * from Members"

'execute query
 * 1) s execute(query$)

'display will use cssclasses for table elements without explicit assignment cssclass "table", "{ width: 400px; border: 6px outset #C0C0C0 ; border-spacing: 2px }" cssclass "tr", "{ background: #CCF; padding: 3px }"

'explicit assignment of class overrides general cssclass statement cssclass "tr.diff", "{ background: #FFEEAA }"
 * 1) s TRCLASS("diff")

'render results render #s


 * 1) s disconnect

code

Reading Data
The **Rowcount** method tells you how many rows of data are in a database. The **Hasanswer** method tells you if there is any data to read. The **Nextrow$** method returns the next row of data as a string, using the delimiter of your choice. The following code executes a query to select all columns of data from the Members table in the database. It then checks to see if there is data to read with **Hasanswer**. It then goes through the data in a loop, from 1 to **Rowcount** and reads each row with **Nextrow$**.

code 'retrieve info from the database 'connect sqliteconnect #s, "mydata.db"

'create query string query$ = "select * from Members"

'execute query
 * 1) s execute(query$)

'if there is data, access it if #s hasanswer then 'check in a loop from 1 to number of rows for i = 1 to #s rowcount 'print info obtained with nextrow$ method print #s nextrow$(",") next i end if


 * 1) s disconnect

code

Caption
You may set the caption for the rendered table with the Caption method like this, where #s is the handle of the connected Sqlite object: code code
 * 1) s caption("Member Roster")

Column Links
You can retrieve the names of the columns in the database with the **Columnnames$** method. It returns the names of the columns in a comma-delimited string. You can extract the names with **Word$**. The example below extracts the first column's name: code cnames$ = #s columnnames$ firstname$ = trim$(word$(cnames$,1,",")) code Since you know the name of the first column, you can cause it to be a column of links with the **Link** method: code code The link event handler will receive the text that was clicked in the key$ passed into it if it is a sub, or in EventKey$ if it is a branch label.
 * 1) s LINK(firstname$, "linkHandler")

For more on column links, see Table

Demo Program
This program creates a database if it doesn't exist, and adds some members. It displays the data in a formatted table. It allows the user to add members. code format="vbnet" dbfile$ = "aaamem.db"

'create file accessor to see if database file exists files #a, dbfile$

'check for existence exists = #a hasAnswer

'if database doesn't exist, create it if not(exists) then

sqliteconnect #s, dbfile$ query$ = "create table Members (name char(50) default 'N/A', country char(24) default 'N/A', rank char(16)

default 'N/A')"
 * 1) s execute(query$)

'create a query string to add a record to the database query$ = "insert into Members (name, country, rank) values (""John Smith"", ""United States"", ""Novice"")"

'use execute method to execute query
 * 1) s execute(query$)

'create a query string to add a record to the database query$ = "insert into Members (name, country, rank) values (""Jane Jones"", ""Scotland"", ""Intermediate"")"

'use execute method to execute query
 * 1) s execute(query$)


 * 1) s disconnect

end if

'display info from the database 'connect sqliteconnect #s, dbfile$

'create query string query$ = "select * from Members"

'create query string to select ONLY TWO columns: 'query$ = "select name, country from Members"

'execute query
 * 1) s execute(query$)

'retrieve number of rows cap$ = "Number of rows is "; #s rowcount 'set caption
 * 1) s caption(cap$)

'display will use cssclasses for table elements without explicit assignment cssclass "table", "{ width: 400px; border: 6px outset #C0C0C0 ; border-spacing: 2px }" cssclass "tr", "{ background: #CCF; padding: 3px }"

'explicit assignment of class overrides general cssclass statement cssclass "tr.diff", "{ background: #FFEEAA }"
 * 1) s TRCLASS("diff")

'render results render #s link #lk, "Add member.",[addMember] wait
 * 1) s disconnect

[addMember] print input "Type name, country, rank, separated by commas.";n$,c$,r$ sqliteconnect #s, dbfile$

'add some data: query$ = "insert into Members (Name, Country, Rank) values ('";n$;"','";c$;"','";r$;"')"
 * 1) s execute(query$)

'display info from the database query$ = "select * from Members"
 * 1) s execute(query$)

cssclass "table", "{ width: 400px; border: 6px outset #C0C0C0 ; border-spacing: 2px }" cssclass "tr", "{ background: #CCF; padding: 3px }"
 * 1) s caption("Roster of Members")
 * 2) s TRCLASS("diff")

cls render #s link #lk, "Add member.",[addMember] wait code For more on SQLite usage, see No Frills SQL Demo.