OAUPDATEWHERE
oaupdatewhere updates a set of rows based on criteria supplied through
the criteria parameter. By default, oaupdatewhere waits until the physical
file (table) that corresponds to the table parameter is accessible, then
locks it before updating the row. oaupdatewhere then rereads the row to
be sure that it wasn’t updated since it was fetched, before updating
the row and the Omnidex indexes to reflect new data in indexed columns.
oaupdatewhere combines the functions of oaselect, oafetch and oaupdate
into one call. To select rows for updating in a specified table:
- Supply a set of criteria that indicates which rows you want to update.
- Qualify a set of rows using Omnidex searches, and pass the ODXID option
to oaupdatewhere to update the set of qualified rows.
- Use both methods described above.
By default, when you update a row using oaupdatewhere, any data that
you change for that row is reflected in the Omnidex indexes. This ensures
that the OmniAccess search routines “find” rows after they
are modified and maintains Omnidex index integrity as rows are updated.
When options references the ODXID option, criteria can be blank to return
all the rows that correspond to the Omnidex internal ID list. If criteria
does contain an SQL search expression when used with the ODXID option,
that expression is used to further select (or “filter”) rows
from those that correspond to the Omnidex internal ID list.
You can update several columns at once as long as buffer reflects the
data for each column as the correct type and length for that column. When
the CHAR= n option is used, integer columns should be passed to buffer
as character values of length n.
The criteria parameter contains selection criteria equivalent to the
WHERE clause of an SQL SELECT statement. With the exception of not supporting
the $VALUES token, the rules for providing selection criteria are the
same as for oaselect.
When options references the ODXID option, criteria can be blank to return
all the rows that correspond to the Omnidex internal ID list. If criteria
does contain an SQL search expression when used with the ODXID option,
that expression is used to further select (or “filter”) rows
from those that correspond to the Omnidex internal ID list.
Using oaupdatewhere Only
A typical application for oaupdatewhere is reflecting the name change
for several CUSTOMERS rows. For example, if a customer’s name changed
from “Continental Graphics” to “CG Press”, and
you wanted to change the name in all CUSTOMERS rows for that company,
you could pass the following call to oaupdatewhere:
oaupdatewhere ( cursor,;,status,CUSTOMERS,COMPANY, COMPANY="Continental
Graphics","CG Press")
The buffer parameter contains the replacement string “CG Press”
while the criteria parameter contains the existing string (“Continental
Graphics”).
Using oaqualify to Qualify Rows to Update
If you found all the rows you want to update by searching Omnidex keys,
you can use the ODXID option to update those rows. For example, if you
searched the COMPANY key to find all CUSTOMERS rows with the COMPANY name
“Continental Graphics”, you could call oaupdatewhere with
the ODXID option to change their COMPANY column to “CG Press”:
oaqualify(cursor, ";", status, "CUSTOMERS",
"COMPANY", "CONTINENTAL AND GRAPHICS")
oaupdatewhere ( cursor,ODXID,status,CUSTOMERS,COMPANY,;,CG
Press)
Filter Qualified Rows to Update
You can apply additional selection to an Omnidex search by passing ODXID
in the options parameter, and passing the additional selection criteria
in the criteria parameter. For example, if only the Michigan branches
of Continental Graphics changed their name to CG Press, and there was
only a keyword index installed on the COMPANY column, you could find all
rows for Continental Graphics through an Omnidex search, and apply additional
selection criteria to change only those rows that reflect a STATE of MI:
oaqualify(cursor, ";", status, "CUSTOMERS",
"COMPANY", "CONTINENTAL AND GRAPHICS")
oaupdatewhere ( cursor,ODXID,status,CUSTOMERS,COMPANY, STATE='MI',CGPress)
Syntax
oaupdatewhere (cursor, options, status, tables, columns, criteria,
values)
cursor -- Is a 32-bit signed integer
returned by OmniAccess. Cursor is passed by value and was first returned
by oaopencursor.
options -- Is a 256-byte character string,
passed by reference, and terminated by a semicolon or a null character.
It indicates the action(s) for oaupdatewhere to take. The valid options
are:
status -- Indicates the success or failure
of the oaupdatewhere routine. A zero status.error means a successful call
to oaupdatewhere. The status structure is passed by reference and contains
fourteen 32-bit signed integers, followed by a 36-character buffer.
tables -- Is a character value passed
by reference, not longer than 33 bytes including a semicolon or null terminator.
Table contains the name of the table where the updated row resides.
columns -- Is a character array passed
by reference, not longer than 4096 bytes. Separate multiple columns with
commas and terminate the array with a semicolon or null character. Columns
specifies those columns from table that will contain the data passed in
buffer. Columns should contain one or more column names separated by commas.
Columns may contain an asterisk ( * ) to retrieve all columns if all columns
are represented in buffer.
criteria -- The search criteria for selecting
rows for updating. Criteria is a character value, up to 8192 bytes long,
passed by reference, and terminated with a null character or a semicolon.
values -- Is an array passed by reference
that contains the data to overwrite the columns referenced in columns.
Buffer must equal the combined length of the columns referenced in columns.
The data in buffer must be in native format. If columns references two
columns, one 30-byte character column and one four-byte binary integer
column, then buffer must reference a buffer that contains 30 bytes of
character data followed by four bytes of binary integer data. Binary data
may require conversion when rows are transferred from the server to a
client. If you pass the CHAR= n option in the options parameter, buffer
should have a character column of length n for each numeric column.
Options
CHAR=n -- converts character numbers
to binary format, where n is the byte length of the input number. The
default is 32 bytes. The alphanumeric representation of the values is
left justified and space-filled to length n. For more information, see
“The CHAR=n option”.
ODXID -- causes oaupdatewhere to update
rows that were qualified by previous successive calls to oaqualify or
oajoin. This is the equivalent of passing a $ODXID expression in the criteria
parameter.
Passing a semicolon or a null character causes default behavior.
Example
Top
|
