Library: Database access (OMDB) legacy library Include: omdb.xin

Declaration

  define external function dbTableUpdate
         value      dbTable  table
    from read-only  stream   values
    null value      stream   null      optional
   where value      stream   condition

Argument definitions

table

is an open dbTable object.

values

is a stream shelf containing the columns and values to update.

null

is an optional string to represent a NULL value rather than the default unattached stream.

condition

is an optional condition used in the SQL statement to limit the numbers of rows updated.

Use dbTableUpdate to update records in a table using the supplied data and criteria.

Requirements

You must include the following line at the beginning of your OmniMark program:

  include "omdb.xin"

The database connection containing the table must be:

• open (else external exception OMDB104)
• in scope (else external exception OMDB103)

table must:

• exist (else external exception OMDB501)
• be open (else external exception OMDB601)

The record being inserted (values) must:

• contain data (else external exception OMDB606)
• contain valid data for the table (else external exception OMDB501)
• have stream item keys (else external exception OMDB606) that match table column names (else external exception OMDB603)

The condition must be

• valid SQL (else external exception OMDB501) or
• the specially defined constant DB_ALL_RECORDS (all records in the table will be updated).

Usage Notes

To specify a null value for the input you may represent the null data value as either an unattached stream or an optionally defined string. The column name is identified by the key of each values parameter shelf item.

If the record has a date, time, or timestamp field, you must represent the field's value in the OmniMark Date and Time library format. (This format returns the time with a time zone offset from UTC time, which most databases do not provide.)

Example

The following program shows how to:

• retrieve the average for each enrolled student, and
• use dbTableUpdate to update his or her current average.

  process

      ;  local variables
      local dbDatabase this-db
      local dbField student-average variable
      local dbTable student
      local stream SQL-query initial
      {  "select sid, ave(Grade) " ||
          "from StudentCourse " ||
          "group by sid "
      }
      local stream average variable initial { ' ' with key 'Average' }

      set this-db to dbOpenODBC 'dbDemo'
      dbQuery this-db sql SQL-query record student-average
      set student to dbTableOpen this-db table 'Student'

      repeat
          exit unless dbRecordExists student-average

          set average{'Average'} to
                  dbFieldValue student-average[2] null '0'
          dbTableUpdate student from average where
                  "sid = ' " ||  dbFieldValue student-average{'sid'}  ||  " ' "

          ;  advance the cursor
          dbRecordMove student-average
      again

Other Library Functions    dbClose    dbCommit    dbDatabase    dbExecute    dbField    dbFieldPrepare    dbFieldSink    dbFieldValue    dbIsVersionCompatible    dbLibraryVersion    dbLibraryVersionOCI8    dbLibraryVersionOCI8i    dbLibraryVersionODBC    dbOpenOCI8    dbOpenOCI8i    dbOpenODBC    dbProcedure    dbProcedureClose    dbProcedureExecute    dbProcedureOpen    dbQuery    dbRecordExists    dbRecordMove    dbRecordSetMove    dbRollback    dbSetTransactionType    dbStatement    dbStatementCompile    dbStatementDiscard    dbStatementExecute    dbStatementStreamingExecute    dbTable    dbTableClose    dbTableDelete    dbTableInsert    dbTableOpen    dbTableUpdate

[ INDEX ] [ CONCEPTS ] [ TASKS ] [ SYNTAX ] [ LIBRARIES ] [ LEGACYLIBRARIES ] [ ERRORS ]