SclConstraint

Overview

This command permits a constraint to be applied to a query, a delete operation, or an update operation to define a subset of the rows in a table that will be affected.

Synopsis

$QueryHandle SclConstraint $ConstraintEXpression $DeleteHandle SclConstraint $ConstraintEXpression $UpdateHandle SclConstraint $ConstraintEXpression

Description

Apply a constraint to the query, a delete operation, or an update operation to restrict the operation to a subset of the rows in a table.

Note: SclConstraint now allows parentheses (round brackets) in constraint values.

Arguments

QueryHandle, DeleteHandle, UpdateHandle

The handle to a valid query, a delete operation, or an update operation previously obtained using SclQuery, SclQueryUpdate, SclDelete or SclUpdate.

constraintExpression

Passed by value. The constraint expression to define the subset of rows in the table to which the operation is applied. Individual constraint components are defined using a free-form syntax:

field nameoperatorconstraint value

More complex constraints can be used by having a number of individual constraint components that are combined using the and operator.

The constraint engine has some restrictions due to the need to support a simple database type, such as Paradox as the lowest level of database functionality. Only the and operator can be used to combine individual constraint components involving any fields in the table.

Any fields used in a constraint that do not have an associated index may cause the database engine to iterate over all rows in the table to determine which rows satisfy the query constraint. This behaviour is database engine dependent.

The field name must be the name of a field in the database table. The case (UPPER vs lower) of the name is important.

The operator in the constraint component may be one of:

> - The value in the column must be greater than the constraint value to satisfy the condition.
>= - The value in the column must be equal to or greater than the constraint value to satisfy the condition.
== - The value in the column must be exactly equal to the constraint value to satisfy the condition.
< - The value in the column must be less than the constraint value to satisfy the condition.
<= - The value in the column must be equal to or less than the constraint value to satisfy the condition.

Examples

#
# Get the handle to an existing database and query the collar table
# displaying hole ID and max depth for all rows inside a rectangular region
#
if {[sclDatabases SclCountItems] < 1} {
  error "No databases are connected"
} 
sclDatabases SclGetItem DatabaseHandle 0
#
# create a query to iterate over all holes in the collar table
#
$DatabaseHandle SclQuery QueryHandle "collar"
$QueryHandle SclQueryColumn "hole_id" "max_depth" "y" "x" "z"
$QueryHandle SclConstraint "y > 7400 and y < 7500 and x > 1700 and x < 1800"
$QueryHandle SclProcess
$QueryHandle SclGetIterator QueryIterator
while {[$QueryIterator SclIterateNext RowHandle] == $SCL_TRUE} {
  puts "Hole ID=[$RowHandle SclGetValueByName hole_id] 
 max_depth=[$RowHandle SclGetValueByName max_depth] 
 Y=[$RowHandle SclGetValueByName y] 
 X=[$RowHandle SclGetValueByName x] 
 Z=[$RowHandle SclGetValueByName z]"
}
SclDestroy QueryHandle

#
# Get the handle to an existing database and delete all rows
# whose hole_id is >= Z
#
if {[sclDatabases SclCountItems] < 1} {
  error "No databases are connected"
} 
sclDatabases SclGetItem DatabaseHandle 0
#
# create an insert operation for the collar table
#
$DatabaseHandle SclDelete DeleteHandle "collar"
$DeleteHandle SclConstraint "hole_id >= Z"
$DeleteHandle SclProcess
SclDestroy DeleteHandle

#
# Get the handle to an existing database and update all rows
# whose hole_id is >= Z
#
if {[sclDatabases SclCountItems] < 1} {
  error "No databases are connected"
} 
sclDatabases SclGetItem DatabaseHandle 0
#
# create an insert operation for the collar table
#
$DatabaseHandle SclUpdate UpdateHandle "collar"
$UpdateHandle SclSetValueByName max_depth 234.567
$UpdateHandle SclConstraint "hole_id >= Z"
$UpdateHandle SclProcess
SclDestroy UpdateHandle
# now display the modified row
# now check that the row actually made it into the table
$DatabaseHandle SclQuery QueryHandle "collar"
$QueryHandle SclConstraint "hole_id >= Z"
# 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