SclQuery

Overview

This command creates a new query for a database table. Retrieving rows of data from a database table firstly requires a new query to be created. To successfully retrieve data from a table you must define the columns from which data is to be retrieved and an optional constraint to define the subset of rows of interest.

Synopsis

$DatabaseHandle SclQuery QueryHandle $TableName

Description

Create a new query for the specified table and assign the query handle to a variable for later use.

Arguments

DatabaseHandle

The variable must contain a valid database handle.

QueryHandle

Passed by reference. The variable to which the query handle will be assigned.

TableName

Passed by value. The name of the database table that is to be queried.

Returns

Returns $SCL_TRUE on successful execution. Throws a TCL error if there is no database connected or if the specified table does not exist in the database.

Examples

#
# Get the handle to an existing database and query the collar table
# displaying hole ID and max depth for each hole
#
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"
$QueryHandle SclProcess
$QueryHandle SclGetIterator QueryIterator
while {[$QueryIterator SclIterateNext RowHandle] == $SCL_TRUE} {
  set hole_id [$RowHandle SclGetValueByName "hole_id"]
  set max_depth [$RowHandle SclGetValueByName "max_depth"]
  puts "Hole ID=$hole_id, maxdepth=$max_depth"
}
SclDestroy QueryHandle

#
# Get the handle to an existing database and query the collar table
# displaying hole ID and max depth for all rows
#
if {[sclDatabases SclCountItems] < 1} {
  error "No databases are connected"
} 
sclDatabases SclGetItem DatabaseHandle 0
# iterate over tables and get handle to the collar table
set CollarTableFound 0
$DatabaseHandle SclIterateFirst DatabaseIterator
while {[$DatabaseIterator SclIterateNext TableHandle]} {
  if {"[$TableHandle SclGetId]" == "collar"} {
    set CollarTableFound 1
    break
  }
}
if {$CollarTableFound} {
  # create a query to iterate over all holes in the collar table
  $DatabaseHandle SclQuery QueryHandle "collar"
  # register all columns in the table
  $TableHandle SclIterateFirst TableIterator
  while {[$TableIterator SclIterateNext ColumnName]} {
    $QueryHandle SclQueryColumn $ColumnName
    lappend ColumnNames $ColumnName
  }
  puts "Column names = $ColumnNames"
  
  # process the query
  $QueryHandle SclProcessn
  # iterate over all rows returned by the query
  $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
} else {
  puts "No table called collar in this database"
}

#
# Get the handle to an existing database and query the collar table
# displaying hole ID and max depth for all rows that have Y values
# > 7400 and < 7500
#
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"
$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