SclInsertUpdate

Overview

This command permits you to update an existing row in a table, provided a row with the same key (the hole_id in a collar table for example) already exists. If a row with the same key doesn't already exist in the table the operation reverts to an insert and creates a new row in the table.

Synopsis

$DatabaseHandle SclInsertUpdate $Tablename

Description

Tries to update an existing row if one with a matching key already exists and if not it inserts a new row into the table.

Note that the values in the indexed fields cannot be changed using and Insert/Update operation as it is these fields that are used to match to an existing row.

If you need to change the values of the key fields then you must first delete the existing row using SclDelete and then insert a new row by using SclInsert or SclInsertUpdate.

Arguments

DatabaseHandle

The variable must contain a valid database handle.

InsertUpdateHandle

Passed by reference. The variable to which the insert/update handle will be assigned.

TableName

Passed by value. The name of the database table to which the insert/update operation will be applied.

Examples

#
# Get the handle to an existing database and a update an existing  row
# in the collar table 
#
# By using insert/update it will revert to an insert of a row with
# the matching hole_id does not already exist
#
if {[sclDatabases SclCountItems] < 1} {
  error "No databases are connected"
} 
sclDatabases SclGetItem DatabaseHandle 0
#
# create an insert operation for the collar table
#
$DatabaseHandle SclInsertUpdate InsertUpdateHandle "collar"
$InsertUpdateHandle SclSetValueByName date_drilled "2004-11-11"
$InsertUpdateHandle SclSetValueByName drilling_co "Company A"
$InsertUpdateHandle SclSetValueByName geologist "Tony"
$InsertUpdateHandle SclSetValueByName hole_id "ZZZ"
$InsertUpdateHandle SclSetValueByName hole_path "linear"
$InsertUpdateHandle SclSetValueByName hole_type "RC"
$InsertUpdateHandle SclSetValueByName max_depth "1000.0"
$InsertUpdateHandle SclSetValueByName prospect "Tony's Folly"
$InsertUpdateHandle SclSetValueByName x "1000.123"
$InsertUpdateHandle SclSetValueByName y "2000.456"
$InsertUpdateHandle SclSetValueByName z "100.5"
$InsertUpdateHandle SclProcess
SclDestroy InsertUpdateHandle
# now check that the row actually made it into the table
$DatabaseHandle SclQuery QueryHandle "collar"
$QueryHandle SclConstraint "hole_id == ZZZ"
# get handle to the collar table
$DatabaseHandle SclIterateFirst DatabaseIterator
while {[$DatabaseIterator SclIterateNext TableHandle]} {
  if {"[$TableHandle SclGetId]" == "collar"} {
    break
  }
}
# register all columns in the collar table
$TableHandle SclIterateFirst TableIterator
while {[$TableIterator SclIterateNext ColumnName]} {
  $QueryHandle SclQueryColumn $ColumnName
  lappend ColumnNames $ColumnName
}
puts "Column names = $ColumnNames"
$QueryHandle SclProcess
$QueryHandle SclGetIterator QueryIterator
while {[$QueryIterator SclIterateNext RowHandle] == $SCL_TRUE} {
  # iterate over all values in the row and append them for display purposes
  $RowHandle SclIterateFirst RowIterator
  while {[$RowIterator SclIterateNext ColumnValue]} {
      lappend values $ColumnValue
  }
  
  # now display the values for this row
  puts "Row values = $values"
  unset values
}
SclDestroy QueryHandle