A comprehensive tutorial about SQL in Basic4ppc is available here (external link):
The SQL library is based on the popular open source SQL engine, SQLite 3.
With SQLite the database is stored in a single file.
In the above link you can find many explanations about the SQLite engine and syntax.
This implementation is based on the open source .Net library named
The SQL library requires that the .Net Compact Framework 2.0 will be installed on the
Using this library you can use larger databases and with much more options than using
only the Table control.
There are built-in methods for showing the queried results in a Table control and for
saving the data in a Table control inside an SQLite database.
This help manual do not cover the SQLite syntax.
The SQL library includes three types of objects:
- Connection - Used to establish a connection to a database file. Also includes the
CreateSQLTable which copies the data from a Table control into the database.
- Command - Used to execute SQL commands.
- DataReader - Allows forward only access to the result of an executed command. The
reading is done row after row.
Change: Note that when you distribute an application that uses this library you should
copy the distribute the following files as well:
These files are located under Libraries\NativeSQL folder.
A simple example of using this library along with Microsoft's Northwind database sample
(converted to SQLite) can be downloaded here: www.basic4ppc.com/Downloads
Tree.New1("Form2", 5, 5, Form2.Width - 10, Form2.Height - 10)
Con.Open("Data Source = " & AppPath & "\Northwind.sl3") 'Opens a
connection with the database.
AddEvent("btnExecute",Click,"mnuExecute_Click") 'The button and
the menu use the same sub.
'Executes the SQL command
cmd.CommandText = txtCommand.Text
'Executes the user query and fills the table
with the result.
the number of rows to 500
(change to 0 for unlimited)
Sub mnuRefresh_Click 'Fills the TreeView with the tables and columns
for i = 0 to Tree.Count
Con.BeginTransaction 'Starts a block of I/O
with the database.
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type =
'table'" 'Finds all the tables in this database
Reader.Value = cmd.ExecuteReader
Do while reader.ReadNextRow = True
for i = 0 to Tree.Count
Node.Value = Tree.GetNode(i)
cmd.CommandText = "PRAGMA table_info ('" & Node.Text & "')" 'Special SQLite command to find the table's metadata.
reader.Value = cmd.ExecuteReader
Do while Reader.ReadNextRow = True
Node.AddNewNode(Reader.GetValue(1) & " : " &
if Tree.Count = 0 then mnuRefresh_Click