Adding, updating and deleting rows

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 Database Basics for beginners and this topic has more details.

Retrieve data

RowSet

The RowSet 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:

Var teams() As String
Var rs As RowSet
Try
 rs = db.SelectSQL("SELECT * FROM Team;")
  For Each Row As DatabaseRow in rs
    teams.AddRow(rs.Column("Name").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
   MessageBox(db.ErrorMessage)
End Try

The SelectSQL method returns a RowSet. You should always use a Try..Catch statement to check for errors when using SelectSQL. A DatabaseException could be because of a database error or something as simple as a typo in your SELECT statement.

The For Each loop iterates through the rows in the RowSet until you reach the end.

The Column method is used to get the DatabaseColumn for the column. This contains a column value for the current row in the RowSet, in this case the Name column.

The Column method is used to get column values based on the column name. You can also use the ColumnAt method to get column values based on the position of the column in the SELECT statement.

SQL

Since you use SelectSQL to get RowSets, 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 Protecting your database from attack, you instead want to make use of Prepared SQL Statements to make your SQL more secure.

Prepared SQL statements and database binding

All of the database class support prepared statements that 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:

Var sql As String sql = "SELECT * FROM Player WHERE Team = 'Seagulls';"
Var rs As RowSet
rs = db.SelectSQL(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 actuae'seagulls' value is supplied later:

Var sql As String
sql = "SELECT * FROM Player WHERE Team = ?;"
Var rs As RowSet
rs = db.SQLSelect(sql, "Seagulls")

As you can see, it takes slightly more thought to create but this is worthwhile because it is much safer than just using straight SQL.

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:

Var sql As String
sql = "SELECT * FROM Player WHERE Team IN (?, ?);"
Var rs As RowSet
rs = db.SelectSQL(sql, "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 DatabaseRow class to add new rows to the database. Or you can directly use the SQL INSERT statement.

DatabaseRow

The DatabaseRow 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 Database.AddRow method of the Database class to insert the record into the specified table. This code adds a team to the Team table:

Var row As New DatabaseRow
row.Column("Name") = "Seagulls"
row.Column("Coach") = "Mike"
row.Column("City") = "Albandy"

Try
  db.AddRow("Team", row)
Catch error As DatabaseException
  MessageBox(error.ErrorMessage)
End Try

DatabaseRow 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:

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

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

Var tableName = "Team"
Var teamName = "Seagulls"
Var coachName = "Mike"
Var cityName = "Albany"
Var insertSQL As String
insertSQL = "INSERT INTO " + tableName + "(Name, Coach, City) VALUES (?, ?, ?);"
Try
  db.ExecuteSQL(insertSQL, teamName, coachName, cityName)
Catch error As DatabaseException
  MessageBox(error.ErrorMessage)
End Try

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 RowSet that your retrieved from a SELECT. Or you can directly use the SQL UPDATE command.

Edit and updating a RowSet

You can choose to edit the current row of a RowSet and then save those changes back to the database. You do this by calling the RowSet.EditRow method. 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 RowSet with the values for the Pigeons team and then changes the team name to the Eagles:

Var rs As RowSet
Try
  rs = db.SelectSQL("SELECT * FROM Team WHERE Name = 'Pigeons';")
  rs.EditRow
  rs.Column("Name").StringValue = "Eagles"
  rs.SaveRow
  rs.Close
Catch error As DatabaseException
  MessageBox(db.ErrorMessage)
End Try

Note that if you use the RowSet.MoveToPreviousRow method to go back in the RowSet, the value it contains will be what you have changed. The RowSet will still contain the original values. Another restriction is that MySQL will reset the RowSet when you call the RowSet.SaveRow method. For these reasons, most of the time you will only use RowSet.EditRow and RowSet.SaveRow with RowSets 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 ExecuteSQL to run the UPDATE command. This code changes the team named "Pigeons" to "Eagles":

Var updateSQL As String
updateSQL = "UPDATE Team SET Name = 'Eagles' WHERE Name = 'Pigeons';"
Try
db.ExecuteSQL(updateSQL)
Catch error As DatabaseException
  MessageBox(db.ErrorMessage)
End Try

Delete data

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

Delete using a RowSet

You can delete the current row in a RowSet by calling the RowSet.RemoveRow method:

rs.RemoveRow

DELETE SQL

The SQL for deleting data is relatively simple:

DELETE FROM table WHERE column = value;

You use ExecuteSQL to delete data:

Var deleteSQL As String
deleteSQL = "DELETE FROM Team WHERE Name = 'Seagulls';"
Try
  db.ExecuteSQL(deleteSQL)
Catch error As DatabaseException
  MessageBox(db.ErrorMessage)
End Try

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 use the Try..Catch statement so you can check a DatabaseException after every set of database commands. If a DatabaseException occurs, then the ErrorMessage property of the DatabaseException contains a description of the error, which you should display or log.

Try
  db.ExecuteSQL("DELETE FROM Team WHERE Name = 'Seagulls';")
Catch error As DatabaseException
  System.DebugLog(db.ErrorMessage)
  MessageBox(db.ErrorMessage)
End Try

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: Tables, TableColumns and TableIndexes.

Tables method

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

This code adds the table names to a ListBox:

Var tables As RowSet
tables = db.Tables
If tables <> Nil Then
  While Not tables.AfterLastRow
    ListBox1.AddRow(tables.ColumnAt(1).StringValue)
    tables.MoveToNextRow
  Wend
  tables.Close
End If

TableColumns

Similarly, TableColumns returns a RowSet with information for all the columns (fields) on the specified table. The RowSet 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:

Var columns As RowSet
columns = db.TableColumns("Team")
If columns <> Nil Then
  While Not columns.AfterLastRow
    ListBox1.AddRow(columns.ColumnAt(1).StringValue, columns.ColumnAt(2).StringValue, columns.ColumnAt(3).StringValue, columns.ColumnAt(4).StringValue, columns.ColumnAt(5).StringValue)
    columns.MoveToNextRow
  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.

TableIndexes

TableIndexes returns a RowSet with the name of the indexes on a table. The RowSet 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 Database.TableColumns 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

See also

Database, DatabaseColumn, DatabaseRow, RowSet classes; Database Basics for beginners topic