1 Version 4.0 -- 5/1/89 dbqual
______________________________________________________________________
NAME: dbqual
FUNCTION:
Return a pointer to a WHERE clause suitable for use in updating
the current row in a browsable table.
SYNTAX:
char *dbqual(dbproc, tabnum, tabname)
DBPROCESS *dbproc;
int tabnum;
char *tabname;
dbqual Version 4.0 -- 5/1/89 2
______________________________________________________________________
COMMENTS:
o dbqual() is one of the DB-Library browse mode routines. See
the Introduction for a detailed discussion of browse mode.
o dbqual() provides a WHERE clause that the application can use
to update a single row in a browsable table. Columns from this
row must have previously been retrieved into the application
through a browse-mode SELECT query (i.e., a SELECT that ends
with the key words FOR BROWSE).
The WHERE clause produced by dbqual() begins with the keyword
WHERE and contains references to the row's unique index and
timestamp column. The application simply appends the WHERE
clause to an UPDATE or DELETE statement; it does not need to
examine it or manipulate it in any way.
The timestamp column indicates the time that the particular row
3 Version 4.0 -- 5/1/89 dbqual
______________________________________________________________________
was last updated. An update on a browsable table will fail if
the timestamp column in the dbqual()-generated WHERE clause is
different from the timestamp column in the table. Such a con-
dition, which provokes SQL Server error message 532, indicates
that another user updated the row between the time this appli-
cation selected it for browsing and the time it tried to update
it. The application itself must provide the logic for handling
the update failure. The following program fragment illustrates
one approach:
/* This code fragment illustrates a technique for handling the case where
* a browse-mode update fails because the row has already been updated
* by another user. In this example, we simply retrieve the entire row
* again, allow the user to examine and modify it, and try the update
* again.
*
* Note that "q_dbproc" is the DBPROCESS used to query the database, and
dbqual Version 4.0 -- 5/1/89 4
______________________________________________________________________
* "u_dbproc" is the DBPROCESS used to update the database.
*/
/* First, find out which employee record the user wants to update. */
employee_id = which_employee();
while (1)
{
/* Retrieve that employee record from the database. We'll
* assume that "empid" is a unique index, so this query will
* return only one row.
*/
dbfcmd
(q_dbproc,
"select * from employees where empid = %d for browse",
employee_id);
dbsqlexec(q_dbproc);
5 Version 4.0 -- 5/1/89 dbqual
______________________________________________________________________
dbresults(q_dbproc);
dbnextrow(q_dbproc);
/* Now, let the user examine or edit the employee's
* data, first placing the data into program variables.
*/
extract_employee_data(q_dbproc, employee_struct);
examine_and_edit(employee_struct, &edit_flag);
if (edit_flag == FALSE)
{
/* The user didn't edit this record,
* so we're done.
*/
break;
}
else
dbqual Version 4.0 -- 5/1/89 6
______________________________________________________________________
{
/* The user edited this record, so we'll use the edited
* data to update the corresponding row in the database.
*/
qualptr = dbqual(q_dbproc, -1, "employees");
dbcmd(u_dbproc, "update employees");
dbfcmd
(u_dbproc,
" set address = '%s', salary = %d %s",
employee_struct->address, employee_struct->salary,
qualptr);
dbfreequal(qualptr);
if ((dbsqlexec(u_dbproc) == FAIL)
|| (dbresults(u_dbproc) == FAIL))
{
/* Our update failed. In a real program, it
* would be necessary to examine the messages
7 Version 4.0 -- 5/1/89 dbqual
______________________________________________________________________
* returned from the SQL Server to determine
* why it failed. In this example, we'll
* assume that the update failed because
* someone else has already updated this
* row, thereby changing the timestamp.
*
* To cope with this situation, we'll just
* repeat the loop, retrieving the changed
* row for our user to examine and edit.
* This will give our user the opportunity
* to decide whether to overwrite the change
* made by the other user.
*/
continue;
}
else
{
dbqual Version 4.0 -- 5/1/89 8
______________________________________________________________________
/* The update succeeded, so we're done. */
break;
}
}
}
o dbqual() can only construct WHERE clauses for browsable tables.
You can use dbtabbrowse() to determine whether a table is
browsable.
o dbqual() is usually called after dbnextrow().
o For a complete example that uses dbqual() to perform a browse
mode update, see Example 6 in the DB-Library Reference Supple-
ment.
PARAMETERS:
dbproc - A pointer to the DBPROCESS structure that provides the
9 Version 4.0 -- 5/1/89 dbqual
______________________________________________________________________
connection for a particular front-end/SQL Server process. It
contains all the information that DB-Library uses to manage
communications and data between the front end and SQL Server.
tabnum - The number of the table of interest, as specified in
the SELECT statement's FROM clause. Table numbers start at
1. If tabnum is -1, the tabname parameter will be used to
identify the table.
tabname - A pointer to the null-terminated name of a table
specified in the SELECT statement's FROM clause. If tabname
is NULL, the tabnum parameter will be used to identify the
table.
RETURNS:
A pointer to a null-terminated WHERE clause for the current row
in the specified table. This buffer is dynamically allocated, and
it is the application's responsibility to free it via
dbfreequal().
dbqual Version 4.0 -- 5/1/89 10
______________________________________________________________________
dbqual() will return a NULL pointer if the specified table is not
browsable. For a table to be "browsable," it must have a unique
index and a timestamp column.
dbqual() will also return a NULL pointer if the preceding SELECT
did not include the FOR BROWSE option.
SEE ALSO:
dbcolbrowse, dbcolsource, dbfreequal, dbtabbrowse, dbtabcount,
dbtabname, dbtabsource, dbtsnewlen, dbtsnewval, dbtsput