UserGuide:Database Operations

Once you have connected to a database (refer to the previous sections for the database you are using), the process of doing common database operations is nearly always the same regardless of the database engine you are using. Typically you need to do these actions:

Retrieve data
Add data
Change data
Delete data

The SQL for these operations was briefly described in UserGuide:Database Overview and this topic has more details.

Retrieve Data

RecordSet

The RecordSet class is used to retrieve data from a database. You supply the SQL SELECT command to get data from one or more tables and then use RecordSet to loop through the results.

This code gets the name of all the teams and adds them to an array:

Dim teams() As String
Dim rs As RecordSet
rs = db.SQLSelect("SELECT * FROM Team;")
If rs <> Nil Then
While Not rs.EOF
teams.Append(rs.Field("Name").StringValue)
rs.MoveNext
Wend
rs.Close
Else
// Check if there was an error
If db.Error Then
MessageBox(db.ErrorMessage)
End If
End If

The SQLSelect method returns a RecordSet. You should always check if the RecordSet is Nil before you attempt to use it. A RecordSet could be Nil because of a database error or something as simple as a typo in your SELECT statement. If it is Nil, you should check the database for an error, which is done in the Else clause.

The While loop iterates through the rows in the RecordSet until you reach the end (EOF stands for End Of File).

The Field method is used to get the DatabaseField for the column. This contains a column value for the current row in the RecordSet, in this case the Name column.

It is very important to call rs.MoveNext, which moves the current row of the RecordSet to the next row. If you forget to do this, it would cause an "infinite loop" because the RecordSet would also stay on the same row, adding it over and over to the array ad infinitum (or until you run out of memory).

The Field method is used to get column values based on the column name. You can also use the IdxField method to get column values based on the position of the column in the SELECT statement. Note that IdxField is 1-based, so the first column is column 1.

SQL

Since you use SQLSelect to get RecordSets, your SQL mostly consists of SELECT statements.

The syntax for SELECT statements is generally like this:

SELECT column1, column2 FROM table
WHERE column = value
ORDER BY sortColumn;

There are a lot of types of SELECT statements and the syntax varies depending on the database. Be sure to refer to the documentation for the database you are using to learn about its SELECT statement.

You can just supply the SQL directly as a string (as done in the code above), which works fine for desktop apps. But you do not want to do that with web apps. Because of a hacking technique called UserGuide:SQL Injection, you instead want to make use of Prepared SQL Statements to make your SQL more secure.

Prepared SQL Statements and Database Binding

Each database class has its own PreparedSQLStatement class, but they all work generally the same way. A Prepared SQL Statement passes the SQL and its arguments separately to the database which then binds them together to create an SQL statement that cannot be affected by SQL Injection.

This code shows how you would get the names of all the players on the Seagulls team without using Prepared SQL Statements:

Dim sql As String sql = "SELECT * FROM Player WHERE Team = 'Seagulls';"
Dim rs As RecordSet
rs = db.SQLSelect(sql)

With a Prepared SQL Statement, you instead only supply a placeholder for the 'Seagulls' parameter value (usually a "?", but it varies depending on the database you are using). The actual 'Seagulls' value is supplied later:

Dim sql As String
sql = "SELECT * FROM Player WHERE Team = ?;"

Dim ps As SQLitePreparedStatement
ps = db.Prepare(sql)
// Identify the type of the first parameter value
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
// Bind the first parameter to a value
ps.Bind(0, "Seagulls")

Dim rs As RecordSet
rs = ps.SQLSelect

As you can see, this code is a little longer because there are extra lines to set the type of each parameter value (types are available as constants on the specific prepared statement class) and to bind the actual value to each parameter. But this is worthwhile because it is much safer than just using straight SQL.

You can simplify this somewhat by providing the values as part of the SQLSelect call like this:

Dim sql As String
sql = "SELECT * FROM Player WHERE Team = ?;"

Dim ps As SQLitePreparedStatement
ps = db.Prepare(sql)
// Identify the type of the first parameter
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

Dim rs As RecordSet
rs = ps.SQLSelect("Seagulls")

The database-specific SQLPreparedStatement classes are:

The replacement character (usually a "?" but it varies by database type) can only be used to replace a single value. For usage with an "IN" clause or with other statements that take multiple values, you'll need to provide the appropriate number of replacement characters.

For example, to use with an "IN" clause:

Dim sql As String
sql = "SELECT * FROM Player WHERE Team IN (?, ?);"

Dim ps As SQLitePreparedStatement
ps = db.Prepare(sql)
' Identify the type of the parameters
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)

Dim rs As RecordSet
rs = ps.SQLSelect("Seagulls", "Pigeons")

You can also use Prepared Statements with INSERT and UPDATE commands.

Add Data

You can add new data to your database using two different methods. You can use the DatabaseRecord class to add new rows to the database. Or you can directly use the SQL INSERT statement.

DatabaseRecord

The DatabaseRecord class is used to add new rows to a specific table.

Use the various "Column" methods on the class to assign values to the columns. Then call the InsertRecord method of the Database class to insert the record into the specified table. This code adds a team to the Team table:

Dim row As New DatabaseRecord
row.Column("Name") = "Seagulls"
row.Column("Coach") = "Mike"
row.Column("City") = "Albandy"

db.InsertRecord("Team", row)
If db.Error Then
MessageBox(db.ErrorMessage)
End If

DatabaseRecord works on any database and does not require changes should you change your project to use a different database engine.

INSERT SQL

You can also create the SQL for the INSERT statement manually, but you need to use the correct INSERT syntax for the database you are using. Generally speaking, INSERT syntax looks like this:

INSERT INTO table (column1, column2) VALUES (value1, value2);

You can build up this INSERT command using string concatenation. This code adds a team to the Team table:

Dim tableName = "Team"
Dim teamName = "Seagulls"
Dim coachName = "Mike"
Dim cityName = "Albany"
Dim insertSQL As String
insertSQL = "INSERT INTO " + tableName + "(" + _
"Name, Coach, City) VALUES ('" + _
teamName + "', '" + coachName + "'," + cityName + _
");"

But you should really use a PreparedSQLStatement for better results, more security and simpler code. This is how the above code looks for SQLite using a Prepared Statement:

Dim tableName = "Team"
Dim teamName = "Seagulls"
Dim coachName = "Mike"
Dim cityName = "Albany"
Dim insertSQL As String
insertSQL = "INSERT INTO " + tableName + "(Name, Coach, City) VALUES (?, ?, ?);"
Dim ps As SQLitePreparedStatement
ps = db.Prepare(insertSQL)
ps.SQLExecute(teamName, coachName, cityName)

As you can see, this is simpler to read because you don't have as much string concatenation code to manage. It's also less error-prone as you won't have to worry about making sure you get all the quotes correct.

Change Data

You can change data in two ways. The first way is to directly modify the data in a RecordSet that your retrieved from a SELECT. Or you can directly use the SQL UPDATE command.

Edit and Updating a RecordSet

You can choose to edit the current row of a RecordSet and then save those changes back to the database. You do this by calling the Edit method of the RecordSet. This makes the current row editable so that you can change any of the column values.

After you have changed the values, you can update the database with the changes. This code gets a RecordSet with the values for the Pigeons team and then changes the team name to the Eagles:

Dim rs As RecordSet
rs = db.SQLSelect("SELECT * FROM Team WHERE Name = 'Pigeons';")
If rs <> Nil Then
If Not rs.EOF Then
rs.Edit
If Not db.Error Then
rs.Field("Name").StringValue = "Eagles"
rs.Update
Else
MessageBox(db.ErrorMessage)
End If

End If
rs.Close
Else
// Check if there was an error
If db.Error Then
MessageBox(db.ErrorMessage)
End If
End If

Note that if you use the MovePrevious method to go back in the RecordSet, the value it contains will be what you have changed. The RecordSet will still contain the original values. Another restriction is that MySQL will reset the RecordSet when you call the Update method. For these reasons, most of the time you will only use this Edit capability with RecordSets that have a single row.

UPDATE SQL

Of course, you can also use direct SQL for this. Like with other SQL, you need to use the UPDATE command and match the syntax required by your database. Generally, it looks like this:

UPDATE table  SET column1 = value1  WHERE column2 = value2;

You use SQLExecute to run the UPDATE command. This code changes the team named "Pigeons" to "Eagles":

Dim updateSQL As String
updateSQL = "UPDATE Team SET Name = 'Eagles' WHERE Name = 'Pigeons';"
db.SQLExecute(updateSQL)
If db.Error Then
MessageBox(db.ErrorMessage)
End If

Delete Data

You can delete data from a table in two ways. If you have a RecordSet you can use its DeleteRecord method, or you can directly use the DELETE SQL command.

Delete Using a RecordSet

You can delete the current row in a RecordSet by calling the DeleteRecord method:

rs.DeleteRecord

DELETE SQL

The SQL for deleting data is relatively simple:

DELETE FROM table WHERE column = value;

You use SQLExecute to delete data:

Dim deleteSQL As String
deleteSQL = "DELETE FROM Team WHERE Name = 'Seagulls';"
db.SQLExecute(deleteSQL)
If db.Error Then
MessageBox(db.ErrorMessage)
End If

It is important that you always include the "WHERE" clause when deleting data. If you forget it then you will end up deleting all the rows in the table!

Error Handling

As you may have noticed in many of the code samples, proper error handling is essential when dealing with databases. Without error handling, you will have no way to know if your database commands completed successfully. An error might result in invalid data and possible app crashes.

Always check the Error property after every database command. If the Error property is True, then the ErrorMessage property contains a description of the error, which you should display or log.

If db.Error Then
System.DebugLog(db.ErrorMessage)
MessageBox(db.ErrorMessage)
End If

Database Schema Information

The database schema is the definition of the tables, it columns, indexing and other things related to how the database is implemented. At times it can be useful to get this information directly from the database. The Database class has three methods to return this information: TableSchema, FieldSchema and IndexSchema.

TableSchema

The TableSchema method of the Database class returns a RecordSet with one column containing the names of all the tables in the database.

This code adds the table names to a ListBox:

Dim tables As RecordSet
tables = db.TableSchema
If tables <> Nil Then
While Not tables.EOF
ListBox1.AddRow(tables.IdxField(1).StringValue)
tables.MoveNext
Wend
tables.Close
End If

FieldSchema

Similarly, FieldSchema returns a RecordSet with information for all the columns (fields) on the specified table. The RecordSet results can vary depending on the database, but these columns are typically available: ColumnName, FieldType, IsPrimary, NotNull and Length.

This code displays the information for each column in the Team table in a ListBox:

Dim columns As RecordSet
columns = db.FieldSchema("Team")
If columns <> Nil Then
While Not columns.EOF
ListBox1.AddRow(columns.IdxField(1).StringValue, columns.IdxField(2).StringValue, columns.IdxField(3).StringValue, IdxField(4).StringValue, IdxField(5).StringValue)
columns.MoveNext
Wend
columns.Close
End If

Note: FieldType is an Integer that describes the data type of the column. Refer to the Data Types section below for a table that maps the integer to a data type.

IndexSchema

IndexSchema returns a RecordSet with the name of the indexes on a table. The RecordSet has one column, the name of the index.

Data Types

Each database has its own set of data types that it uses. These types are identified as an Integer in the FieldSchema.FieldType column. These integer values are also used when defining your own data source for use with Reports.

The following table is a complete list of data types and their corresponding values. Note that not all databases use every data type.

Column Data Type	Value
Null	0
Byte	1
SmallInt	2
Integer	3
Char	4
Text / VarChar	5
Float	6
Double	7
Date	8
Time	9
TimeStamp	10
Currency	11
Boolean	12
Decimal	13
Binary	14
Long Text / BLOB	15
Long VarBinary / BLOB	16
String	18
Int64	19
Unknown	255

UserGuide

Database Operations

From Xojo Documentation

Contents