Back to Concepts and HOW TOs

Contents 1 Description 2 Prerequisites 3 Procedure 3.1 Create (Insert) 3.2 Update 3.3 Delete 3.4 Caller Example 4 Error Handling 5 Examples 6   7 Next Steps 8 Tags

[edit] Description

This document describes how to create functions within a class to perform the basic database operations: create, update, retrieve, and delete.

[edit] Prerequisites

Connecting to an SQL Database
Creating a Class to Contain Row Data from an SQL Table
Logging in to an SQL Database

[edit] Procedure

In the Prerequisite documents, we described how to create a Task class to contain row data from a task table in your database and a collection of Task objects to contain multiple rows from a task table. To implement the basic database operations, create, update, retrieve, and delete, you can create functions within the Task class. We showed an example of retrieve in Logging in to an SQL Database here we will show the other three basic operations.

The following examples use SQL escaping which is described in detail in Using SQL Query Escaping.

Notice that these functions, which operate on a row in the task table, are members of the Task class. It is a best practice to create these functions in the class which you created to contain row data from your table. The functions are operating on an instance of Task corresponding to a specific row in the task table and, the functions have access to all of the fields they needs to perform these operations on the database.

Note In the following examples and screen shots we are referring to SQLConnection with is the superclass for both the MySQL and Postgres Runtime Types. As long as the object that you instantiate for your database connection is either a MySQL or Postgres Runtime Type, you can assign it to an SQLConnection Runtime Type. It can be advantagous to pass an SQLConnection Runtime Type to functions if there is a chance you could switch between MySQL and Postgres at some point.

[edit] Create (Insert)

In the following screen shot, we have a default query string with arguments ('?') for inserting a task into the task table.

1. buildArgCollection is called to create a collection of strings containing the arguments for the default query string.
2. SQLConnection's escapeQuery is called to escape each argument and return a final query.
3. SQLConnection's queryNoResults is called to execute the final query on the task table.
4. Since this is creating a new row in the database, SQLConnection's lastInsertID is called to get the id of the inserted row. This sets the id field on this instance of the Task class.
   

[edit] Update

In the following screen shot, we have a default query string with arguments ('?') for updating a task in the task table.

1. buildArgCollection is called to create a collection of strings containing the arguments for the default query string.
2. SQLConnection's escapeQuery is called to escape each argument and return a final query.
3. SQLConnection's queryNoResults is called to execute the final query on the task table.
4. isDirty is set back to false since this task has been updated.
   

[edit] Delete

In the following screen shot, we are not using a default query string with arguments ('?'), since there is no input from an end user.

1. In the var statement, we build the delete sql query by appending the id of this instance of Task.
2. SQLConnection's queryNoResults is called to execute the query on the task table.
    

[edit] Caller Example

The following screen shot shows how the TaskManager's updateTask function calls the Task's update function.

1. selectedItem is of type Task.
2. We pass the SQLConnection db to update so that update has access to the database connection.

[edit] Error Handling

 

[edit] Examples

[edit]  

[edit] Next Steps

Using SQL Query Escaping
Debugging SQL

[edit] Tags