Updates and Insertions

Updating and inserting new records (rows) into a table is easily accomplished using SQL. Data can be specified in the SQL statement as constants or parameterized by binding variables.

Using SQL constants

If updates and insertions are done infrequently on the database it is convenient to just construct the SQL statement on the fly inserting values as constants and then calling dbExecSQL to perform the action.

Example:

REM val$ can be constructed elsewhere
val$ = "'Mulder','Fox', 11240, 10011232, 3000.00"
hstmt = dbExecSQL(pdb,"INSERT INTO accounts VALUES(" + val$ + ")" )

The INSERT statement can accept an optional column list if not all values are known. Any column names not in the list are filled with NULL values.  There must be a one to one matching of column names and values
 

val$ = "'Mulder','Fox', 11240, 10011232"
hstmt = dbExecSQL(pdb,"INSERT INTO accounts (lname, fname, branch, acctno) VALUES(" + val$ + ")" )

Updates of existing rows is just as simple.  The UPDATE statement allows specifying the columns to update an the a search condition to match. Updating a single row is then accomplished by using a unique key. In our example of a fictional banks database the account number is a uniquely identifying column.

Example:

val$ = "3000.00"
acctno$ = "10011232"
hstmt = dbExecSQL(pdb,"UPDATE accounts SET balance = " + val$ + " WHERE acctno = " + acctno$)

Using parameters

Looking at the examples for using SQL constants you can see that its not a very efficient way of updating and inserting data. A better method for a large number of operations is to used bound parameters.

A parameter is used in a SQL statement when:

For example, an application wants to insert five rows into a table named accounts.

INSERT INTO accounts (lname, fname, branch, acctno) VALUES (?,?,?,?)

In this statement, ? is the parameter marker. By using parameters, the application only needs to prepare this statement once, and then execute the prepared statement five times with different parameter values to insert five rows into the account table.

Using parameters requires a two step execution of the SQL statement, and binding the necessary variables to be used as parameters.

Step 1. Prepare the SQL statement

dbPrepareSQL prepares an SQL statement for later execution, in this manner it can be executed many times using the same statement handle.
 

hsmt = dbPrepareSQL(pdb, "INSERT INTO accounts (lname, fname, branch, acctno) VALUES (?,?,?,?)")

Step 2. Bind parameters

dbBindParameter associates a variable with a parameter in the prepared statement. Parameter numbers are one based
 

DEF last, first as STRING
DEF branch, acctno as INT
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
dbBindParameter(hstmt, 3, branch)
dbBindParameter(hstmt, 4, acctno)

Step 3. fill in the variables and execute the statement one or more times.

dbExecute executes the prepared SQL statement, substituting any bound parameters as needed.
 

last = "Thompson"
first = "Tom"
branch = 11223
acctno = 11014457
dbExecute(hstmt)
 
last = "The Grey"
first = "Gandalf"
branch = 11223
acctno = 110144566
dbExecute(hstmt)

With all statement handles remember to use dbFreeSQL when you are finished. To bind a DBDATE, DBTIME or DBTIMESTAMP UDT parameter use the dbBindDateParam, dbBindTimeParam and dbBindTimeStampParam commands instead of dbPindParameter.