Library: Database access (OMDB) Import: omdb.xmd

Declaration

define external function db.update
       value      db.table  table
  from read-only  stream   values
  null value      string   null      optional
 where value      string   condition

Argument definitions

table

is an open db.table 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 db.update to update records in a table using the supplied data and criteria.

Requirements

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-match (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 db.update to update his or her current average.

  import "omdb.xmd" prefixed by db.
  
  process
      local db.database this-db
      local db.field student-average variable
      local db.table 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 db.open-odbc 'dbDemo'
      db.query this-db SQL sql-query into student-average
      set student to db.open-table in this-db named 'Student'
  
      repeat
          exit unless db.record-exists student-average
  
          set average{'Average'} to
                  db.reader of student-average[2] null '0'
          db.update student from average where
                  "SID = ' " ||  db.reader of student-average{'SID'}  ||  " ' "
  
          ;  advance the cursor
          db.move-record student-average
      again

Related Syntax    db.database    db.open-table    db.insert    db.delete    db.table    db.commit    db.rollback

Related Concepts    Using the OMDB library

Other Library Functions    db.advance-recordset    db.close    db.commit    db.compile-statement    db.database    db.delete    db.discard    db.document-writer-sedna    db.execute    db.execute-in    db.field    db.insert    db.is-null    db.move-dynamic-record    db.move-record    db.omdb-version    db.omdb-version-oci10g    db.omdb-version-oci11g    db.omdb-version-oci8i    db.omdb-version-oci9i    db.omdb-version-odbc    db.omdb-version-sedna    db.open-oci10g    db.open-oci11g    db.open-oci8i    db.open-oci9i    db.open-odbc    db.open-procedure    db.open-sedna    db.open-table    db.prepare    db.procedure    db.query    db.reader    db.record-exists    db.rollback    db.set-transaction-type    db.statement    db.streaming-execute    db.table    db.update    db.writer

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