SclQueryUpdate
Overview
The SclQueryUpdate command, which is a variation of SclQuery, enables you to update rows in a database. To successfully retrieve data from a table, you must define the columns from which data is to be retrieved. If you want, you can create a constraint to define the subset of rows in which you are interested. You then use other commands to modify data and write the updated rows to the database.
Using SclQueryUpdate is like writing your own version of FIELD MATHS, to make individual changes to rows.
Note: Conversely, if you need to update many rows of a database, and need to set the specified fields to the same value, use SclUpdate, which is the most efficient command for this situation.
Synopsis
$DatabaseHandle SclQueryUpdate QueryHandle $TableName
Description
Create a new query for the specified table and assign the query handle to a variable for later use with optional updates.
Arguments
- DatabaseHandle
- QueryHandle
- TableName
The variable must contain a valid database handle.
Passed by reference. The variable to which the query handle is assigned.
Passed by value. The name of the database table that is to be queried.
Returns
Returns $SCL_TRUE on successful execution. Returns a TCL error if there is no database connected or if the specified table does not exist in the database.
Example
if {[sclDatabases SclCountItems] < 1} {
error "No databases are connected"
}
#
# Get the handle to an existing database and query the collar table
# for all rows with hole_id between WD004 and WD033 above max_depth of 130
# then offset their x and y coordinates
#
sclDatabases SclGetItem DatabaseHandle 0
$DatabaseHandle SclQueryUpdate QueryHandle "collar"
$QueryHandle SclConstraint "hole_id >= WD004 AND hole_id <= WD033 AND max_depth < 130"
$QueryHandle SclQueryOrder "hole_id" "ascending"
$QueryHandle SclQueryColumn "x" "y"
$QueryHandle SclProcess
$QueryHandle SclGetIterator queryupIter
while {[$queryupIter SclIterateNext rowHandle]==1} {
set x [$rowHandle SclGetValueByName "x"]
set newX [expr $x + 130]
$QueryHandle SclSetValueByName x "$newX"
set y [$rowHandle SclGetValueByName "y"]
set newY [expr $y - 11.02]
$QueryHandle SclSetValueByName y "$newY"
$QueryHandle SclUpdateRow ;# push the changes for this row to the database
# display a message so we can see the change in the Message area
puts "moving $x, $y to $newX, $newY"
}
SclDestroy QueryHandle
|