Top Next

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.
SQLite site: www.sqlite.org
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 System.Data.SQLite.
System.Data.SQLite site: http://sqlite.phxsoftware.com/
The SQL library requires that the .Net Compact Framework 2.0 will be installed on the device.

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 syntax is covered here: http://www.sqlite.org/lang.html

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

Sub Globals

End Sub

Sub App_Start
      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.
End Sub

'Executes the SQL command
Sub mnuExecute_Click
      cmd.CommandText = txtCommand.Text
      'Executes the user query and fills the table with the result.
      cmd.ExecuteTable("table1",500) 'Limits the number of rows to 500 (change to 0 for unlimited)
End Sub

Sub mnuShowScheme_Click
End Sub

Sub mnuRefresh_Click 'Fills the TreeView with the tables and columns data
      for i = 0 to Tree.Count - 1
      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 - 1
            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) & " : " & Reader.GetValue(2))
End Sub

Sub Form2_Show
      if Tree.Count = 0 then mnuRefresh_Click
End Sub

Sub Form1_Close
End Sub