This part assumes you have opened a database and connected to it as described it step 2. As shown in the manual-database-manipulation section, after creating the database you must create the tables resides in it. It is your responsibility to check if they already exist: you should only create them the first time you connect to the database.
The first thing to do is to check if the table we wish to create exists already. We do this by executing an SQL "SELECT" statement on a special table, contained in each SQLite database. This is the "SQLite_master" table.
SQLite_master table
Every SQLite database has an SQLITE_MASTER table that defines the schema (that is, the inner structure) for the database.
For tables, the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command
SELECT name FROM sqlite_master WHERE type='table'
Similarly, to get a list of one table or less with the name't_orders' (that is, to check if a table exists), use the following SELECT command:
SELECT name FROM sqlite_master WHERE type='table' AND name='t_orders'
Executing SQL commands
SQL commands are executed from Basic4ppc using the Command object. We use the one previously instantiated and set value to the CommandText property. This is the SQL statement itself. Create a new Sub called CreateTableIfNotExists, and add the following line of code to it:
|
|
Command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='t_orders'" |
|
The Command object now holds our SQL statement. In order to execute it, there exist two Subs in it: ExecuteReader, and ExecuteNonQuery:
-
ExecuteReader is called when you execute a command that is about to return a subset of the rows in a table (including an empty list of rows, and a list containing all rows in the table). This Sub returns an object of type DataReader. There must be a DataReader object in your application ready to be assigned to and instantiated (using the New1 constructor: see Instantiating - Libraries tutorial).
-
ExecuteNonQuery is called when you execute a command that manipulate the data in a way that does not return a list as a result. For example, if you delete a row from the table, you would have set the CommandText to be something like "DELETE t_order WHERE ID=1". No rows are returned as a result. Instead, a number is returned, indicating the number of rows affected by the command.
Since the "SELECT" statement we use is intended to return results, it is essential to create the place to hold them. This place is the DataReader object.
When receiving a list of records from the database, you need a DataReader in order to access them. DataReader objects allow fast, forward only reading of a given list of records.
Start off with adding a DataReader object to your application, the same way you added the Connection and Command objects. Name the new object Reader. It is going to be used to hold the data returned by the command, when executed:
Add the following line below the previous one:
|
|
Reader.Value = Command.ExecuteReader |
|
This will execute the command as described below. A list of data is now held in the Reader object (of type DataReader). Think of it as a big list:
| Pointer | Column 1 | Column 2 | Column 3 | Column 4 | Column 5 |
| ► | [data] | [data] | [data] | [data] | [data] |
| [data] | [data] | [data] | [data] | [data] |
The number of columns differs according to the result of the SELECT statement. A column is generated for each field in the statement. We later access these columns using the GetValue Sub. For now, we are only interested in the number of rows. In order to find out the number of rows we use the pointer.
Fetching the list from the DataReader
DataReaders use a pointer that indicates which is the "current row". Starting with the first row, this pointer points to one of the rows and can be moved forward to the next row using the DataReader's ReadNextRow sub. The row the pointer points to is the one from which data is returned when accessing specific column, but this comes later. For now, only the number of rows we can promote the pointer is of interest. ReadNextRow returns True when succeeds and False if there is no more rows. We will use this feature and try to move the pointer one row forward. If it moves, this means we got at least one table in the list we got back: no need to create a new table. If it does not move, we shall create the table. The code looks like this:
|
|
'instantiate reader Reader.New1 Reader.Value = Command.ExecuteReader
If Reader.ReadNextRow = False Then ' No table with this name in the database. ' Create one. Command.CommandText =
"CREATE TABLE t_orders (ID
INTEGER PRIMARY KEY, Sum REAL, TableNum INTEGER," & _ Reader.Close Command.ExecuteNonQuery End If
|
|
If the ReadNextRow Sub returned false, we set a new SQL command into the CommandText property of the Command object. This SQL command tells the database to create a new table with the list of fields specified.
Note: we used the INTEGER PRIMARY KEY as the data type of the ID field. In SQLite, this causes an auto-increment of this field when no value is entered.
Close the reader before you leave...
You are almost ready to test your code. One last thing is missing - closing the DataReader session. You must close the session before trying to execute another command: this resets the reader. If you did not, an error will occur. Change the End If above to else so that you add the following line of code at the end of the Sub:
|
|
Else Reader.Close End If |
|
And add a call to the CreatTableIfNotExists at the App_Start sub:
Your code should now be looking like this:
|
|
Sub
Globals
'Declare the
global variables here.
End Sub Sub App_StartForm1.Show CreateConnection CreateTableIfNotExists End Sub Sub CreateConnectionConnection.New1 Command.New1( "", Connection.Value)Connection.Open( "Data Source = " & AppPath & "\Rest1.lit")End Sub Sub CreateTableIfNotExists'Find all the tables in this database Command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='t_orders'" 'instantiate reader Reader.New1 'Fill reader Reader.Value = Command.ExecuteReader If Reader.ReadNextRow = False Then ' No table with this name in the database. ' Create one. Command.CommandText =
"CREATE TABLE t_orders (ID
INTEGER PRIMARY KEY, Sum REAL, TableNum INTEGER," & _ Reader.Close Command.ExecuteNonQuery Else 'Close reader anyhow Reader.Close End If End Sub
|
|
Note: the method illustrated here is a bit clumsy. The DataReader part could be skipped, and the SQL statement "IF NOT EXISTS" could have been used. We chose to present the somewhat longer way in order to introduce the reader with both the DataReader object (at this early stage of the reference) and the SQLite unique "sqlite_master" table. This gives a better understanding of manipulating the database scheme.
Next page: Before Step 4 - Parameters