|
   
5.
Retrieving Results
5.1
Performing Queries Using ODBC
5.2
Cursors
5.3
Fetching Large Data
5.4
Manipulating Result Sets
5. Retrieving Results
Performing
queries to retrieve data is one of the most important functions of a
database. In this chapter we examine how to:
retrieve data on a row by row basis by binding columns to a storage
location using the functions SQLBindCol and SQLFetch.
obtain information (such as type and length) about the columns in a
result set by using the functions SQLNumResultCols, SQLDescribeCol
and SQLColAttributes.
use a cursor to do positioned DELETE or positioned UPDATE on a result
set obtained by doing a query. We also examine how to get or set the
cursor name by using the functions SQLGetCursorName and SQLSetCursorName.
retrieve large data objects (LONG VARCHAR or LONG VARBINARY) and file
objects piece by piece by using the function SQLGetData. Retrieving
large objects or file objects piece by piece allows you to use much
smaller buffers than would be required to retrieve the data all at once.
Use rowsets to browse forward or backward through a result set obtained
by doing a query using the functions SQLExtendedFetch and SQLSetPos.
Figure
5-1 on the following page shows the process flow for retrieving
data from a database using the ODBC functions introduced in this chapter.
Figure
5-1: Program flow for retrieving data from a database.
5.1 Performing Queries Using ODBC
When an
application needs to retrieve data from a database, the most common
method is to perform a query by using the SQL SELECT statement. In this
section we will learn how to perform a query and fetch the result data
row by row using ODBC functions.
Binding Storage Locations and Fetching Data from the Result Set
Suppose
we want to get last name, first name and branch information for customers
at branch 11240 from the account table. To do this we might perform
a query like the following:
SELECT lname, fname, branch FROM account WHERE branch = 11240
|
After preparing
and executing this query, we are ready to fetch the data row by row.
If all the information in the result columns in the projection of this
query are known (e.g. column type, precision, scale, etc.), then we
can use SQLBindCol and SQLFetch to fetch the results.
SQLBindCol is used to associate a storage location with a column
of data. The role of SQLBindCol in a SELECT statement is similar
to the SQLBindParameter in an INSERT statement.
SQLFetch is used to fetch a row of data from the result set. The
driver will return data for all bound columns to the storage locations
specified by SQLBindCol.
The prototype
for SQLBindCol is:
RETCODE SQLBindCol(
HSTMT hstmt,
UWORD icol,
SWORD fCType,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR *pcbValue
);
|
An application
needs to pass the following information to SQLBindCol so it can
associate the storage location with the result column.
fCType - the data type to which the data is to be converted.
rgbValue - the address of an output buffer for the data. The application
must allocate this buffer and make sure the buffer is large enough to
accommodate the data retrieved for the specified data type.
cbValueMax - the length of the output buffer. This value is ignored
if the returned data has a fixed width in C, such as an integer value.
pcbValue - the address of a storage buffer which is used to return
the number of bytes of available data. Note that the driver will store
SQL_NULL_DATA in this argument if the fetched data is NULL.
After each
column in the projection is bound, SQLFetch() is called to fetch
a row of data.
The prototype
for SQLFetch is:
RETCODE SQLFetch(HSTMT hstmt);
|
The following
example uses ODBC to perform the query shown earlier. It uses SQLBindCol
and SQLFetch to fetch customer information for all customers
at branch 11240 from the account table.
#define LENGTH 18
UCHAR lname[length], fname[length];
UDWORD branch_no, TRUE = 1;
SDWORD retcode, cblname, cbfname, cbbranch;
retcode = SQLExecDirect(hstmt, "SELECT lname, fname, branch FROM account
WHERE branch = 11240", SQL_NTS);
if (retcode == SQL_SUCESS)
{
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, lname, LENGTH, &cblname);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, fname, LENGTH, &cbfname);
retcode = SQLBindCol(hstmt, 3, SQL_C_LONG, &branch_no, 0, &cbbrach);
}
/* fetch data one row at a time and print out the result data */
/* stop when no more data or error returned from SQLFetch */
while (TRUE)
{
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
if (cblname == SQL_NULL_DATA) /* check null data */
printf("last name: NULL\n");
else
printf("last name: %s\n", lname);
if (cbfname == SQL_NULL_DATA)
printf("first name: NULL\n");
else
printf("first name: %s\n", fname);
if (cbbranch == SQL_NULL_DATA)
printf("branch no: NULL\n");
else
printf("branch no: %d\n", branch_no);
}
else /* if no more data or errors returned */
break;
}
|
Example
5-1: Using SQLFetch and bound parameters
Getting Result Columns Characteristics
As described
in Section 4.4, some applications may not know the data types
to be inserted in advance. Likewise, it's possible that in dynamic SQL
an application doesn't know the result data it will fetch ahead of time.
If this is the case, the SQLNumResultsCols and SQLDescribeCol
functions can help to give more information.
SQLNumResultCols is used to get the number of result columns in
the result set.
SQLDescribeCol is used to desribe characteristics of a result column,
including: name, SQL type, precision, scale and whether or not the column
allows NULL values.
The prototype
for SQLNumResultCols is:
RETCODE SQLNumResultCols(
HSTMT hstmt,
SWORD FAR *pccol
);
|
The prototype
for SQLDescribeCol is:
RETCODE SQLDescribeCol(
HSTMT hstmt,
UWORD icol,
UCHAR FAR *szColName,
SWORD cbColNameMax,
SWORD FAR *pcbColName,
SWORD FAR *pfSqlType,
UDWORD FAR *pcbColDef,
SWORD FAR *pibScale,
SWORD FAR *pfNullable
);
|
The program
flow in this case will look like this:
Figure
5-2: Program flow for obtaining a result set using SQLNumResultCol,
SQLDescribeCol, SQLBindCol and SQLFetch
After preparing
a query, you can call SQLNumResultCols to find out how many result
columns are in the query. Then call SQLDescribeCol to get the
information about each column which is used in turn to call SQLBindCol.
Finally, the result data can be fetched one row at a time by calling
SQLFetch.
The example
shown below uses SQLNumResultCols, SQLDescribeCol, SQLBindCol
and SQLFetch to fetch a result set after performing a query.
#define BUFFER_LEN 256 /* length of the query string buffer */
#define MAX_COLS 128 /* allowed max number of columns */
UCHAR str[BUFFER_LEN];
SDWORD retcode, TRUE = 1;
SWORD i, ncol;
SWORD coltype[MAX_COLS], colscale[MAX_COLS], colnull[MAX_COLS];
SWORD colCtype[MAX_COLS];
UDWORD collen[MAX_COLS], outlen[MAX_COLS];
char *colbuf[MAX_COLS];
BEGIN: /* begin label */
getQueryString(&str); /* get input query string*/
/* prepare the input query */
retcode = SQLPrepare(hstmt,queryStr, SQL_NTS);
err_exit(hstmt, retcode); /* exit if error */
retcode = SQLNumResultCols(hstmt,&ncol); /* get # of result columns */
err_exit(hstmt, retcode); /* exit if error */
if (ncol > 0) /* still columns in query? */
{
printf("There are %d result columns \n",ncol);
for (i = 0; i < ncol; i++) /* describe & bind columns */
{
retcode = SQLDescribeCol(hstmt, i+1, &coltype[i], &collen[i],
&colscale[i], &colnull[i]);
err_exit(hstmt, retcode); /* exit if error */
/* allocate storage location for the column according to its, */
/* type, length and scale, reuse the storage if possible */
allocColumnStorage(coltype[i], collen[i], colscale[i],
&colbuf[i]);
/* get corresponding SQL C type */
getSQLCtype(coltype, &colCtype);
/* bind the column storage */
retcode = SQLBindCol(hstmt, i+1, colCtype[i], coltype[i],
collen[i], colscale[i], colbuf[i],
BUFFER_LEN, &outlen[i]);
err_exit(hstmt, retcode); /* exit if error */
} /* end of for */
} /* end of if */
retcode = SQLExecute(hstmt); /* execute prepared query */
err_exit(hstmt, retcode); /* exit if error */
/* fetch one row at a time until no more data in the result set, if */
/* the if the column data is null, add mark in the column buffer, */
/* then output all the column data (print to file or standard output) */
while(TRUE)
{
retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
for (i = 0; i < ncol; i++)
{
/* if data is NULL, specify NULL in column buffer */
if (outlen[i] == SQL_NULL_DATA)
MarkNullColumn(colbuf[i]);
/* output column data */
outputColumnData(ncol, colCtype[i], coltype[i],
collen[i], colscale[i],
colnull[i], colbuf[i]);
}
}
else
break;
}
retcode = SQLFreeStmt(hstmt, SQL_CLOSE); /* close the cursor used in */
/* the statement hstmt */
err_exit(hstmt, retcode); /* exit if error */
if (user_Quit()) /* user wants to quit */
return;
else
goto BEGIN; /* go to BEGIN */
|
Example
5-2: Fetching a result set after performing a query
Getting More Information About Result Columns
Although
we can get some of the characteristics of columns by calling SQLDescribeCol,
there is still some additional column information applications might
need to know. ODBC provides the function SQLColAttributes for
this purpose.
SQLColAttribute
SQLColAttributes
is used to to return descriptor information for a column, and this information
is for the specified descriptor type.
The prototype
for SQLColAttributes is:
RETCODE SQLColAttributes(
HSTMT hstmt,
UWORD icol,
UWORD fDescType,
PTR rgbDesc,
SWORD cbDescMax,
SWORD FAR *pcbDesc,
SDWORD FAR *pfDesc
);
|
The descriptor
types defined in ODBC include:
SQL_COLUMN_COUNT
SQL_COLUMN_NAME
SQL_COLUMN_TYPE
SQL_COLUMN_LENGTH
SQL_COLUMN_PRECISION
SQL_COLUMN_SCALE
SQL_COLUMN_DISPLAY_SIZE
SQL_COLUMN_NULLABLE
SQL_COLUMN_UNSIGNED
SQL_COLUMN_MONEY
SQL_COLUMN_UPDATABLE
SQL_COLUMN_AUTO_INCREMENT
SQL_COLUMN_CASE_SENSITIVE
SQL_COLUMN_SEARCHABLE
SQL_COLUMN_TYPE_NAME
SQL_COLUMN_TABLE_NAME
SQL_COLUMN_OWNER_NAME
SQL_COLUMN_QUALIFIER_NAME
SQL_COLUMN_LABEL
(For detailed
information about the meaning of each option, see the Microsoft
ODBC Programmer's Reference.) For example, ifthe value of fDescType
is SQL_COLUMN_TYPE, SQLColAttributes will return the SQL
type of the specified column. Although this information can also be
obtained by using SQLDescribeCol, SQLColAttributes can
also provide other information that cannot be obtained by using SQLDescribeCol.
The major
differences between SQLColAttributes and SQLDescribeCol
are:
SQLDescribeCol provides some specific values for one column at one
time, while SQLColAttributes get only the value of one descriptor.
SQLColAttributes provides more specific and detailed column information.
It can also be extended if a driver adds more driver-specific descriptors
or if ODBC defines more new descriptors in future versions.
If an application
needs to know whether one column is case-sensitive (e.g. character data
is case-sensitive while integer data is not), it can use the SQLColAttributes
function with the descriptor option SQL_COLUMN_CASE_SENSITIVE to find
out. The following program is an example of this:
#define TRUE 1
#define FALSE 0
SDWORD CSflag; /* case-sensitive flag */
SDWORD retcode;
retcode = SQLPrepare(hstmt, "SELECT lname, fname, branch FROM account
WHERE branch = 11240", SQL_NTS);
retcode = SQLColAttributes(hstmt, 1, SQL_COLUMN_CASE_SENSITIVE,
NULL, 0, NULL, &CSflag);
if (CSflag == TRUE)
printf("Column 1 is case-sensitive\n");
|
Example
5-3: Using SQLColAttributes to get detailed column information
Clear Bound Columns
After a
storage location is bound to a column by calling SQLBindCol,
it can be reused again and again, like in Example
5-1. In the example, three storage locations are bound to the
three columns in the SELECT statement.
To unbind
all bound columns associated with a statement handle, you can call SQLFreeStmt
with the SQL_UNBIND option.
retcode = SQLFreeStmt(hstmt, SQL_UNBIND);
|
Now all
of the bound storage locations in hstmt are cleared, and an application
can reuse the statement handle and bind different a storage area. If
for some reason the application wants to unbind a single bound column,
an alternative is to call SQLBindCol and pass a NULL pointer
in the rgbValue argument.
If application
doesn't need to reuse the statement handle, then SQLFreeStmt
can be called with the SQL_DROP option. In this case, all the storage
locations are cleared and any existing cursors, pending results and
resources used by the statement handle will be freed as well.
5.2 Cursors
A cursor
is a tool that allows you to step through a result set row-by-row for
row-conditional processing. Applications can perform multiple operations
on each individual row in a given result set. A cursor is opened on
the result set by execution of the query.
When to Use Cursors
A cursor
is used when the program needs to perform update or delete operations
on specific rows in a result set. For example, the program might retrieve
some rows from the query results, display them on the screen for the
user, and then respond to a user's request to update or delete data.
If you
wish to update data using a cursor, the SELECT statements that are used
to generate the result set must explicitly specify FOR UPDATE or FOR
UPDATE OF column_list in the SELECT statement. (e.g. SELECT * FROM account
FOR UPDATE) If the statement has been not declared with FOR UPDATE,
it will default to a read only cursor and you will not be allowed to
do cursor updates or deletes. The syntax for update and delete statements
using cursors is:
UPDATE tablename SET column = value [, SET column = value...]
WHERE CURRENT OF cursorname
|
or
DELETE FROM tablename WHERE CURRENT OF cursorname
|
Getting the Cursor Name
The ODBC
driver will automatically generated a name that begins with the letters
SQL_CUR when you call SQLAllocStmt to allocate a statement handle.
You can use SQLGetCursorName to get the full name of the cursor
associated with a specific statement handle.
The prototype
for SQLGetCursorName is:
RETCODE SQLGetCursorName(
HSTMT hstmt,
UCHAR FAR *szCursor,
SWORD cbCursorMax,
SWORD FAR *pcbCursor
);
|
Note:
The maximum cursor name length is 18 characters.
Using Cursors
The following
example shows how to use SQLGetCursorName in positioned updates,
which involves two different hstmts for the SELECT and UPDATE
statements. In this example John Smith's branch number is updated.
#define NAME_LEN 21
#define CURSOR_LEN 20
HSTMT hstmtSelect;
HSTMT hstmtUpdate;
UCHAR szLname[NAME_LEN],szFname[NAME_LEN],cursorName[CURSOR_LEN];
SWORD cursorLen;
SDWORD sBranch, cbName, cbBranch;
/* Allocate the statement handles */
retcode = SQLAllocStmt(hdbc, &hstmtSelect);
retcode = SQLAllocStmt(hdbc, &hstmtUpdate);
/* SELECT the result set and bind its columns to local storage */
/* NOTE: This is a select FOR UPDATE */
retcode = SQLExecDirect(hstmtSelect, "SELECT lname,fname, branch
FROM account FOR UPDATE",SQL_NTS);
retcode = SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, szLname, NAME_LEN,
&cbLname);
retcode = SQLBindCol(hstmtSelect, 2, SQL_C_CHAR, szFname, NAME_LEN,
&cbFname);
retcode = SQLBindCol(hstmtSelect, 3, SQL_C_INTEGER, &sBranch, 0,
&cbBranch);
/* get the cursor name of the select for use in the update statement */
retcode = SQLGetCursorName(hstmtSelect, cursorName, CURSOR_LEN,
&cursorLen);
/* Read through the result set until the cursor is positioned on the */
/* row for John Smith */
do
retcode = SQLFetch(hstmtSelect);
while ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) &&
(strcmp(szFname, "John") != 0 && strcmp(szLname, "Smith") != 0 &&
sbranch == 2100));
/* Perform a positioned update of John Smith's branch number */
/* NOTE: the cursorName in the CURRENT OF clause */
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
sprintf(updsql, "UPDATE account SET branch = 2101 WHERE
CURRENT OF %s", cursorName);
retcode = SQLExecDirect(hstmtUpdate, updsql, SQL_NTS);
}
|
Example
5-4: Using cursors in positioned updates
Setting the Cursor Name
You can
use SQLSetCursorName to set the cursor name of an active statement
handle. You have to use SQLSetCursorName to change the cursor
name before executing the SELECT statement.
The prototype
for SQLSetCursorName is:
RETCODE SQLSetCursorName(
HSTMT hstmt,
UCHAR FAR *szCursor,
SWORD cbCursor
);
|
5.3 Fetching Large Data
SQLGetData
When you
need to retrieve large data from a LONG VARCHAR or LONG VARBINARY column,
you can use the SQLGetData function to retrieve the data piece
by piece. This way you don't have to prepare a large buffer to retrieve
the whole column in one piece.
The prototype
for SQLGetData is:
RETCODE SQLGetData(
HSTMT hstmt,
UWORD icol,
SWORD fCType,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR * pcbValue
);
|
An application
needs to pass the following information to SQLGetData so it can
associate a result column with a storage location.
fcType - the data type to which the data is to be converted.
rgbValue - the address of an output buffer for the data. The application
must allocate this buffer and make sure the buffer is large enough to
accommodate the data retrieved for the specified data type.
cbValueMax - the length of the output buffer. This value is ignored
if the returned data has a fixed width in C, such as integer data.
pcbValue - the address of a storage buffer which is used to return
the number of bytes of available (remaining) data prior to the current
call to SQLGetData.
The detailed
program flow for using SQLGetData is shown in Figure
5-3.
As we described
before, the normal way to get column data is to use SQLBindCol
to bind a local buffer for the column. During SQLFetch, the column
data is automatically stored in the bound buffer. You can use the bind
method to retrieve large data too if you are sure that your buffer is
big enough. The alternative is to use SQLGetData to get one buffer
full of data each time. To use SQLGetData for a column, you must
NOT bind this column to any buffer to prevent SQLFetch from automatically
sending column data to a buffer.
The detailed
steps for using SQLGetData to retrieve large data objects from
a database are:
Execute the SQL command - Execute the SQL query with SQLExecDirect
or SQLExecute.
Fetch the data - call SQLFetch to get the next row of data. If
SQLFetch returns SQL_NO_DATA_FOUND, then all rows in the result
set of the query have been returned. If the return code is SQL_SUCCESS
or SQL_SUCCESS_WITH_INFO and there is large data you want to fetch,
go to the next step.
Get the large data objects - Call SQLGetData to get one piece
of the data in the unbound column specified by the icol argument
in the current row. Repeat this step until SQLGetData returns
SQL_NO_DATA_FOUND. If you want to fetch data from the next row, go back
to the previous step.
Figure
5-3: Program flow for retrieving large data
In the
following example (Example 5-5), we
will fetch the columns fname, photo and memo from
all rows in the account table and display them. The photo
and memo columns contain large objects. We use the bind method
to get the values in the fname column and SQLGetData to
get the values for the photo and memo columns.
As we explained
before, you can either bind a column to a storage location or use SQLGetData
when retrieving data. This is true for all data, and if you wish you
can use SQLGetData for regular data types such as integers. However,
this is not practical because it involves extra programming effort and
is not necessary.
#define MAX_BINARY_SIZE 1024
#define MAX_CHAR_SIZE 256
#define MAX_NAME_SIZE 21
SDWORD cbFname, cbPhoto, cbMemo, DataLen;
UCHAR FnameBuf[MAX_NAME_SIZE], PhotoBuf[MAX_BINARY_SIZE],
MemoBuf[MAX_CHAR_SIZE];
PTR PhotoDataFile, MemoDataFile;
SDWORD retcode, TRUE=1;
retcode = SQLExecDirect(hstmt, (UCHAR *)"SELECT fname, photo, memo
FROM account", SQL_NTS);
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, FnameBuf, MAX_NAME_SIZE,
&cbFname);
while (TRUE)
{
retcode = SQLFetch(hstmt);
/* After calling SQLFetch, the value of bound column fname is */
/* automatically stored in user buffer FnameBuf */
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WTIH_INFO)
{
/* InitDataFile() opens user's data files for storing */
/* photo and memo column data */
InitDataFile(PhotoDataFile);
InitDataFile(MemoDataFile);
/* The size of remaining data before this SQLGetData is */
/* returned in cbPhoto. This SQLGetData will retrieve */
/* MAX_BINARY_SIZE data of column photo from database, */
/* and put it to binary buffer PhotoBuf. */
while(TRUE)
{
retcode = SQLGetData(hstmt, 2, SQL_C_BINARY, PhotoBuf,
MAX_BINARY_SIZE, &cbPhoto);
if (retcode == SQL_SUCCESS
|| retcode == SQL_SUCCESS_WTIH_INFO)
{
/* GetToFile() moves data from buffer PhotoBuf */
/* to user file PhotoDataFile */
GetToFile(PhotoDataFile, PhotoBuf);
printf("%ld more bytes remains in Photo column \n",
cbPhoto - MAX_BINARY_SIZE);
}
else
break
}
/* Use SQLGetData to get memo data and put it in */
/* MemoDataFile */
while (TRUE)
{
retcode = SQLGetData(hstmt, 3, SQL_C_CAHR, MemoBuf,
MAX_CHAR_SIZE, &cbMemo);
if (retcode == SQL_SUCCESS
|| retcode == SQL_SUCCESS_WTIH_INFO)
{
GetToFile(MemoDataFile, MemoBuf);
}
else
break;
}
/* Display data on screen */
Display(FnameBuf);
DisplayLargeData(PhotoDataFile);
DisplayLargeData(MemoDataFile);
}
else if (retcode == SQL_NO_DATA_FOUND)
{
printf("no data found \n");
break;
}
else
{
printf("error \n");
break;
}
}
|
Example
5-5: Using SQLGetData to retrieve large data objects
For most
applications, all of the data in a large column is usually retrieved
and stored in a temporary file before it is displayed. While this is
still the case for static data that must be displayed all at once, streaming
(audio, video) or page (large text) data can be displayed without the
need for a temporary file. This is done by using a double buffer scheme
to get and show data simultaneously, as shown in Figure
5-4.
Figure
5-4: Using double buffers to get large data
Stopping SQLGetData Operations
If an error
occurs when retrieving data from a database, or if you do not want to
continue retrieving data, you can stop the retrieval process by using
the SQLFreeStmt function with the SQL_CLOSE option. You call
the SQLFreeStmt function with the SQL_CLOSE option to close the
cursor and discard all pending results. You can reopen this cursor to
retrieve data by calling SQLExecute or SQLExecDirect with
the same query again.
Binding Columns to Retrieve Objects into a File
If you
want to retrieve a large data object and have it placed in a client
file, you can use the bind file method to do it. In this method,
you set the fCType argument of SQLBindCol to SQL_C_FILE
and place the file name in the buffer. This instructs DBMaker to create
a file and copy the large object data into it.
The following
example uses this method to retrieve a photograph and place it in a
client file by binding SQL_C_FILE in the fCType argument of SQLBindCol
and preparing the filename in the buffer pPhotoFlName. After
calling SQLFetch the photograph is copied into the file.
UCHAR pPhotoFlName[80];
SDWORD retcode;
retcode = SQLBindCol(hstmt, 1, SQL_C_FILE, pPhotoFlName, 80, &cbPhoto);
strcpy(pPhotoFlName, "/disk1/usr/fo/photo");
retcode = SQLExecDirect(hstmt, "SELECT photograph FROM student
WHERE name = 'mary'", SQL_NTS);
retcode = SQLFetch(hstmt); /* a new file is created and data copied */
|
Example
5-6: Retrieving objects into a file using bound columns
In summary,
there are three ways to retrieve large object data. First, if you have
enough buffer space, you can use the ordinary SQLBindCol method
and retrieve all of the data directly into buffers. Second, if you don't
bind columns, you can use SQLGetData in a loop to retrieve the
data piece by piece. And third, you can use SQLBindCol with
SQL_C_FILE for the fCType argument to instruct DBMaker to
create a client side file for the large object and to copy data into
the file.
Fetching the Filename of File Objects
As described
in chapter four, File Objects (FO) are stored as an external
file on the server. You can use any of the three methods listed above
to retrieve FO data, but the bind client file (SQL_C_FILE) method always
creates a new file on the client side. If you are only interested in
the file name of the FO, you can use built-in function FILENAME() to
get the file name. In this case, you use SQL_C_CHAR to bind the column.
UCHAR pPhotoFlName[80];
SDWORD retcode;
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, pPhotoFlName, 80, &cbPhoto);
retcode = SQLExecDirect(hstmt, "SELECT FILENAME(photograph) FROM student
WHERE name = 'mary'", SQL_NTS);
retcode = SQLFetch(hstmt); /* file name of FO goes to pPhotoFlName */
|
Example
5-7: Fetching the filename of file objects
Currently,
most multimedia tools process multimedia data stored as an operating
system file. If the multimedia data is stored in a LONG VARCHAR or LONG
VARBINARY column, you need to fetch the data from DBMaker and redirect
it to a file which is accepted by the multimedia tool. If you stored
it as a file object, the only thing you need to do is to get the file
name from DBMaker and pass the name to the tool.
5.4 Manipulating Result Sets
Applications
can use SELECT statements to query the underlying database. In addition
to the SQLFetch function from the previous sections, DBMaker also allows
you to use SQLExtendedFetch to easily browse backward or forward
through the result set returned by the SELECT command and SQLSetPos
to further modify the result from SQLExtendedFetch.
Rowsets and Rowset Size
A rowset
behaves like a window on the result set which we can use to browse the
details of the result set. The rowset is always a subset of result set
and has the same tuple order
You can
fetch a rowset using the SQLExtendedFetch function. However,
before calling SQLExtendedFetch you must allocate a buffer and
bind the result columns, and set the number of tuples (rowset size)
that you want to fetch.
You can
call SQLExtendedFetch with different options to move the rowset
window backward or forward to any position within the result set. For
example, if the rowset size is ten, the option SQL_FETCH_FIRST moves
the window to the head of result set and reads the first ten tuples
into the rowset. Applications are responsible for setting the rowset
size by calling the SQLSetStmtOption functionwith the SQL_ROWSET_SIZE
option. The default value for the SQL_ROWSET_SIZE option is one. Applications
are also responsible for allocating enough buffer space for binding
columns using SQLBindCol before calling SQLExtendedFetch.
Program Flow
The program
flow is similar to SQLFetch except for allocating the buffer
for the rowset. You can change rowset size between SQLExtendedFetch
calls, but you must ensure the column output buffer and column status
array is large enough for each column. SQLBindCol must be called
again to rebind any newly allocated column output buffers and column
status arrays. The only exception is that the rgfRowStatus array
argument of SQLExtendedFetch, which is used to record the status
of rows in a rowset and whose size is the same as rowset size, can only
be rebound by recalling SQLExtendedFetch.
Storage Binding
You use
the SQLBindCol function to bind the output buffer (rgbValue)
and column status (pcbValue) for fetched data (rowset) from the
result set. Since the number of tuples fetched can be up to the rowset
size, you must allocate enough space for the output buffer and column
status array with respect to the rowset size. Otherwise, the SQLExtendedFetch
function may fail and place output tuple data into an illegal address
space.
The prototype
of SQLBindCol is:
RETCODE SQLBindCol(
HSTMT hstmt,
UWORD icol,
SWORD fCType,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR *pcbValue
);
|
There are
two ways to bind the output buffer and column status array for a rowset
which might have more than one tuple: column-wise binding and row-wise
binding.
Column-Wise Binding
Use SQLSetStmtOption
to set SQL_BIND_TYPE to BIND_BY_COLUMN to specify column-wise binding.
If you are using column-wise binding, the buffers for the same column
from all tuples of the rowset will be sequential. That is, you allocate
enough buffer at a time for one column. For example, the code fragment
for a table with two columns (INT and CHAR(5)) to bind columns:
(SQLFetch
is a special case of SQLExtendedFetch if column-wise binding)
#define ROWSET_SIZE 6 /* rowset size(6 tuples) */
#define NAME_LEN 30 /* length of NAME column */
#define AGE_LEN 4 /* length of AGE column */
SDWORD retcode;
char *c1_rgbValue, *c2_rgbValue;
SDWORD *c1_pcbValue, *c2_pcbValue;
UWORD *rgfRowStatus;
UDWORD crow;
int irow;
/* set rowset size and exit if there is an error */
retcode = SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, ROWSET_SIZE);
err_exit(hstmt, retcode);
/* set the binding type and exit if there is an error */
retcode = SQLSetStmtOption(hstmt, SQL_BIND_TYPE, SQL_BIND_BY_COLUMN);
err_exit(hstmt, retcode);
/* allocate buffer for the column data (rowset) and column status */
/* arrays */
c1_rgbValue = (char *)malloc(ROWSET_SIZE*NAME_LEN); /*c1 data */
c1_pcbValue = (SDWORD *)malloc(ROWSET_SIZE*sizeof(SDWORD));/*c1 status*/
c2_rgbValue = (char *)malloc(ROWSET_SIZE*AGE_LEN); /*c2 data */
c2_pcbValue = (SDWORD *)malloc(ROWSET_SIZE*sizeof(SDWORD));/*c2 status*/
/* allocate row status array for the rowset */
rgfRowStatus = (UWORD *)malloc(ROWSET_SIZE*sizeof(UWORD));
/* prepare the input query and exit if there is an error */
retcode = SQLPrepare(hstmt, "select NAME, AGE from employee", SQL_NTS);
err_exit(hstmt, retcode);
/* bind the columns and exit if there is an error */
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, c1_rgbValue, NAME_LEN,
c1_pcbValue);
err_exit(hstmt, retcode);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, c2_rgbValue, AGE_LEN,
c2_pcbValue);
err_exit(hstmt, retcode);
/* execute the prepared query and exit if there is an error */
retcode = SQLExecute(hstmt);
err_exit(hstmt, retcode);
/* fetch 6 tuples at once until there is no more data in the result */
/* set */
while(TRUE)
{
retcode = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &crow,
rgfRowStatus);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
printf("**** %d fetched rows in rowset ****\n", crow);
/* print tuples in rowset */
for (irow=0; irow<ROWSET_SIZE; irow++)
{
printf("row %d of rowset - ",irow+1);
switch(rgfRowStatus[irow])
{
case SQL_ROW_SUCCESS:
printf("(NAME: %s), ",c1_rgbValue + irow * NAME_LEN);
printf("(AGE : %s) ",c2_rgbValue + irow * AGE_LEN);
printf("[SUCCESS] \n");
break;
case SQL_ROW_NOROW:
printf(" [NO ROW] \n");
break;
case SQL_ROW_ERROR:
printf("[ROW ERROR] \n");
break;
}
}
}
else
break;
}
/* close cursor associated with hstmt and exit if there is an error */
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
err_exit(hstmt, retcode);
|
Row-Wise Binding
Use SQLSetStmtOption
to set SQL_BIND_TYPE to any value other than BIND_BY_COLUMN to specify
row-wise binding. In this case, the value will be used as the length
of the necessary output buffer for one tuple. That is, the buffer for
one tuple, instead of one column, is allocated at a time which is for
all columns output value and columns status. (If the columns are preknown,
applications often, for easy coding, define a structure which is composed
of column output buffers and status buffers.) For example, the code
fragment for a table with 2 columns (int and char(5)) to bind columns:
#define ROWSET_SIZE 6 /* rowset size(6 tuples) */
#define NAME_LEN 30 /* length of NAME column */
#define AGE_LEN 4 /* length of AGE column */
SDWORD retcode;
char *c1_rgbValue, *c2_rgbValue, *tup_rgbValue, *tup_prn;
SDWORD *c1_pcbValue, *c2_pcbValue;
UWORD *rgfRowStatus;
UDWORD crow;
int irow, tup_len;
/* set the rowset size and exit if there is an error */
retcode = SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, ROWSET_SIZE);
err_exit(hstmt, retcode);
/*calculate the length of one row */
tup_len = NAME_LEN + sizeof(SDWORD) + AGE_LEN + sizeof(SDWORD);
/* set the binding type to row-wise and exit if there is an error */
retcode = SQLSetStmtOption(hstmt, SQL_BIND_TYPE, tup_len);
err_exit(hstmt, retcode);
/* allocate buffer for column data (rowset) and column status arrays */
tup_rgbValue = (char *)malloc(ROWSET_SIZE*tup_len);
/* allocate row status array for rowset */
rgfRowStatus = (UWORD *)malloc(ROWSET_SIZE*sizeof(UWORD));
/* prepare the input query and exit if there is an error */
retcode = SQLPrepare(hstmt, "select NAME, AGE from employee", SQL_NTS);
err_exit(hstmt, retcode);
/* bind the columns and exit if there is an error */
c1_rgbValue = tup_rgbValue;
c1_pcbValue = (SDWORD *)(c1_rgbValue + NAME_LEN);
c2_rgbValue = c1_rgbValue + NAME_LEN + sizeof(SDWORD);
c2_pcbValue = (SDWORD *)(c2_rgbValue + AGE_LEN);
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, c1_rgbValue, NAME_LEN,
c1_pcbValue);
err_exit(hstmt, retcode);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, c2_rgbValue, AGE_LEN,
c2_pcbValue);
err_exit(hstmt, retcode);
/* execute the prepared query and exit if there is an error */
retcode = SQLExecute(hstmt);
err_exit(hstmt, retcode);
/* fetch 6 tuples at once until no more data in the result set */
while(TRUE)
{
retcode = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &crow,
rgfRowStatus);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
tup_prn = tup_rgbValue;
printf("**** %d fetched rows in rowset ****\n", crow);
/* print tuples in rowset */
for (irow=0; irow<ROWSET_SIZE; irow++)
{
printf("row %d of rowset - ",irow+1);
switch(rgfRowStatus[irow])
{
case SQL_ROW_SUCCESS:
printf("(NAME: %s) , ",tup_prn);
printf("(AGE : %s) ",tup_prn + NAME_LEN +
sizeof(SDWORD);
printf("[SUCCESS] \n");
break;
case SQL_ROW_NOROW:
printf(" [NO ROW] \n");
break;
case SQL_ROW_ERROR:
printf("[ROW ERROR] \n");
break;
}
/* print next tuple */
tup_prn = tup_prn + tup_len;
}
}
else
break;
}
/* close cursor associated with hstmt and exit if there is an error */
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
err_exit(hstmt, retcode);
|
Positioning the Cursor
SQLExtendedFetch
positions the cursor on the first row of rowset, if exists, which might
be used by:
Positioned UPDATE and DELETE statements from another statement handle.
You can call SQLExtendedFetch to position the cursor on a row
and use a positioned DELETE statement to delete that row from the result
set of the target table. For example, DELETE ... WHERE CURRENT OF ...
SQLGetData. You can call SQLGetData to get data for those columns which
are not bound. The rowset size should be set to one before calling SQLGetData.
SQLSetPos with the SQL_DELETE, SQL_REFRESH, SQL_UPDATE options (see
below for more information).
Before
the first time you call SQLExtendedFetch, the cursor is positioned
before the start of the result set, which should be seen as undefined.
Using different options might position the cursor before the start of
result set or after the end of result set, instead of on some existing
row.
Arguments of SQLExtendedFetch
The prototype
of SQLExtendedFetch is:
RETCODE SQLExtendedFetch(
HSTMT hstmt,
UWORD fFetchType,
SDWORD irow,
UDWORD FAR *pcrow,
UWORD FAR *rgfRowStatus
);
|
The possible
return values from SQLExtendedFetch are:
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NO_DATA_FOUND
SQL_ERROR
SQL_INVALID_HANDLE
The following
table shows the rowset and return code returned when the ODBC application
requests different rowsets. ODBC applications should check the return
code from SQLExtendedFetch and the row status for each row of
the rowset before it uses the contents of the rowset buffers.
|
Requested
Rowset
|
Return
Code
|
Position
Cursor
|
Returned
Rowset
|
|
Before
start of result set
|
sql_no_data_found
|
Before
start of result set
|
None.
The contents of the rowset buffers are undefined.
|
|
Overlaps
start of result set
|
sql_success
|
First
row of rowset
|
First
rowset in the result set.
|
|
Within
result set
|
sql_success
|
First
row of rowset
|
Requested
rowset.
|
|
Overlaps
end of result set
|
sql_success
|
First
row of rowset
|
For
rows in the rowset that overlap the result set, data is returned.
For rows in the rowset outside the result set, the row status
(rgfRowStatus) is SQL_ROW_NOROW and the contents of that
part of the rowset buffers are undefined.
|
|
After
end of result set
|
sql_no_data_found
|
after
end of rowset
|
None.
The contents of the rowset buffers are undefined.
|
The overlapping
cases (second and fourth) are not symmetrical. For example, suppose
a result set has 100 rows and the rowset size is 5. The following table
shows the rowset and return code returned by SQLExtendedFetch
for different values of irow when the fetch type is SQL_FETCH_RELATIVE
(see below for the definition of this option).
|
Current
Rowset
|
irow
|
Return
Code
|
New
Rowset
|
|
1
to 5
|
-5
|
SQL_NO_DATA_FOUND
|
None
|
|
1
to 5
|
-3
|
SQL_SUCCESS
|
1
to 5
|
|
96
to 100
|
5
|
SQL_NO_DATA_FOUND
|
None.
|
|
96
to 100
|
3
|
SQL_SUCCESS
|
99
and 100. For rows 3, 4, and 5 in the rowset, the corresponding
row statuses are all set to SQL_ROW_NOROW.
|
fFetchType Argument
Type to
decide the position of the window (on the result set) for the rowset.
The valid values for fFetchType are:
SQL_FETCH_FIRST
SQL_FETCH_LAST
SQL_FETCH_NEXT
SQL_FETCH_PRIOR
SQL_FETCH_ABSOLUTE
SQL_FETCH_RELATIVE
Note:
SQL_FETCH_BOOKMARK is not supported.
The irow
argument is applied when the values SQL_FETCH_ABSOLUTE or SQL_FETCH_RELATIVE
are used for the fFetchType argument. The rowset returned for
SQL_FETCH_FIRST, SQL_FETCH_LAST, and SQL_FETCH_ABSOLUTE is not dependant
on the value of fFetchType from the immediately previous SQLExtendedFetch
call since it is not fetched relative to the current rowset. The other
values for fFetchType argument fetch a rowset according to the
previous rowset.
SQL_FETCH_FIRST:
fetches the first rowset in the result set.
SQL_FETCH_LAST:
fetches the last complete rowset in the result set.
SQL_FETCH_ABSOLUTE:
fetches the rowset starting at row irow of the result set.
if irow > 0: fetch the rowset starting at row irow.
irow < 0: fetch the rowset starting at row irow+result
set size+1.
e.g.
if irow = -1 then starting row of returned rowset is the last
row of result set. If irow is less than 0, you can count from back
end of result set to find the first row of the returned rowset.
irow = 0: return SQL_NO_DATA_FOUND and the cursor is positioned before
the start of result set. (to reset)
SQL_FETCH_NEXT:
fetch the next rowset. If the cursor is now positioned before the start
of the result set(eg. the initial condition), this is equivalent to
SQL_FETCH_FIRST.
SQL_FETCH_PRIOR:
fetch prior rowset. If cursor is now positioned after the end of result
set, this is equivalent to SQL_FETCH_LAST.
SQL_FETCH_RELATIVE:
fetch rowset starting at irow row from the start of the current
rowset. If the cursor is positioned before the start of the result set:
irow > 0: fetch the rowset starting at row irow. This
is equivalent to SQL_FETCH_ABSOLUTE value.
irow < 0: return SQL_NO_DATA_FOUND without changing the cursor.
If the
cursor is positioned after the end of the result set:
irow
< 0: fetch the rowset starting at row irow+result set size+1.
This is equivalent to SQL_FETCH_ABSOLUTE value.
irow
> 0: return SQL_NO_DATA_FOUND without changing the cursor.
irow
= 0: refresh (refetch) the current rowset.
irow Argument
The irow
argument specifies the number of the row to fetch. You only need to
use irow if the fFetchType argument is set to either SQL_FETCH_ABSOLUTE
or SQL_FETCH_RELATIVE. Otherwise, you can ignore this value.
pcrow Argument
The pcrow
argument specifies the number of the rows (in the rowset buffer) actually
fetched from an SQLExtendedFetch call. The range of valid values
for pcrow is from 0 to the rowset size.
rgfRowStatus Argument
The rgfRowStatus
argument is an array of status values for all rows in the rowset. This
array is allocated by an ODBC application. The possible status values
set by SQLExtendedFetch are:
SQL_ROW_NOROW: data in this row is undefined.
SQL_ROW_SUCCESS: data in this row was successfully fetched using SQLExtendedFetch.
SQL_ROW_ERROR: an error was found when fetching this row using SQLExtendedFetch.
For example,
if the rowset size is ten and only nine rows are fetched by SQLExtendedFetch
(eg., with fFetchType set to SQL_FETCH_ABSOLUTE and irow
set to -9), then the status value for the last row will be SQL_ROW_NOROW,
while the status values for the other rows will be SQL_ROW_SUCCESS.
Note that
the following values can only be set by SQLSetPos which is used
to manipulate the rows in a rowset fetched by SQLExtendedFetch:
SQL_ROW_UPDATED: this row is updated
SQL_ROW_DELETED: this row is deleted
SQL_ROW_ADDED: this row is added
Returning Values and Processing Errors
SQLExtendedFetch
fetches more than one row at a time. Each row fetched successfully (or
with a warning) is given the value SQL_ROW_SUCCESS as the row status
value. If an error is encountered, the row being fetched will be given
the value SQL_ROW_ERROR and the fetch will stop. Subsequent rows in
the rowset will be marked SQL_ROW_NOROW. The following cases are possible
(suppose the rowset size is 5):
all rows for rowset are fetched
+------+-----------------+
row1 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row2 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row3 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row4 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row5 | data | SQL_ROW_SUCCESS |
+------+-----------------+
all rows for rowset are fetched but it is near the end of result set
+------+-----------------+
row1 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row2 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row3 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row4 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row5 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
error found in fetching 2nd row
+------+-----------------+
row1 | data | SQL_ROW_SUCCESS |
+------+-----------------+
row2 | xxxx | SQL_ROW_ERROR |
+------+-----------------+
row3 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row4 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row5 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
no row fetched at all
+------+-----------------+
row1 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row2 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row3 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row4 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
row5 | xxxx | SQL_ROW_NOROW |
+------+-----------------+
The return
value of SQLExtendedFetch depends on the values of all the rows
in the rowset. You should check the column status array for the status
of the columns in each row. The following return values are possible
for SQLExtendedFetch:
SQL_SUCCESS if no errors or warnings were found and there was at least
one row fetched.
SQL_NO_DATA_FOUND if there were no rows to fetch.
SQL_SUCCESS_WITH_INFO if there were any warnings found but no errors
were found. If SQLError is called for warning information, the
details of the last warning will be returned.
SQL_ERROR if an error was found. All subsequent SQLExtendedFetch
calls will return the same error, and the ODBC application cannot access
that result set any more. (e.g. a lock time-out is seen as an error
even if the lock is later released. SQLExecute is needed to regenerate
the result set.)
Table Modification Using SQLSetPos
If the
result set is generated from single table and each row of the result
set can be uniquely mapped to one row of the target table, then each
row of the rowset (which is a subset of result set) is associated with
one physical row of the target table through an OID (object id).
For example,
rowsets from the following query statements are all updatable:
CREATE TABLE t1 (c1 INT, c2 INT, c3 CHAR(5));
SELECT * FROM t1;
SELECT * FROM t1 WHERE c1 > 10;
SELECT c1 FROM t1 WHERE c2 < 20;
SELECT c2, c1 FROM t1;
|
while rowsets
from the followings query statement are not updatable:
CREATE TABLE t1 (c1 INT, c2 INT, c3 CHAR(5));
SELECT * FROM t1,t2 ;
SELECT c1+c2 FROM t1 ;
SELECT c1*2 FROM t1 ;
|
Note that
SQLSetPos now only supports modification of simple scans on a
single table. Only expressions like c1 can be modified. Expressions
like c1*2, c1+1, and c1+c2 are not modifiable.
If needed,
the column default values are applied for those columns not in the projection,
e.g. row insertion through SQLSetPos. Bound columns are a subset
of the projection which is inversely a subset of the table schema in
a simple scan on single table. You can only use SQLPutData to
manipulate unbound columns.
Arguments of SQLSetPos
The prototype
of SQLSetPos is:
RETCODE SQLSetPos(
HSTMT hstmt,
UWORD irow,
UWORD fOption,
UWORD fLock
);
|
The possible
return values from SQLSetPos are:
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NEED_DATA
SQL_ERROR
SQL_INVALID_HANDLE
Since the
SQLSetPos manipulates the rowset, you must call it after calling
SQLExtendedFetch. The rowset to be operated on is from the immediately
previous SQLExtendedFetch. Note that the rgfRowStatus
argument (the row status array) is set by SQLSetPos (according
to different options) which is implicitly passed from corresponding
SQLExtendedFetch. Again, ODBC applications should check the SQLSetPos
return value and row status array before it accesses the row data. If
the result set is not modifiable, SQLSetPos will just return
an error. The possible values of row status (set by SQLSetPos
with different options) are:
SQL_ROW_SUCCESS
SQL_ROW_ERROR
SQL_ROW_NOROW
SQL_ROW_UPDATED
SQL_ROW_DELETED
SQL_ROW_ADDED
irow Argument
The row
of the rowset the operation specified with fOption will be performed
on. If the value for irow is 0, the operation will be applied
to all rows of the rowset.
fOption Argument
The operation
to apply on the rowset obtained from SQLExtendedFetch. The valid
values are:
SQL_POSITION
SQL_REFRESH
SQL_UPDATE
SQL_DELETE
SQL_ADD
SQL_POSITION:
positions the cursor on the rows of the rowset for those operations
needing a cursor. For example a positioned statement such as UPDATE
WHERE CURRENT OF ... to update the cursor, or SQLPutData to put
data. This option does not change the row status array at all.
if irow = 0: position the cursor on the whole rowset.
If irow = n: position the cursor on row n (the value of n must
be from 1 to the rowset size).
Note that
if this option is used to position cursor on more than one row, then
the positioned statement is only performed on first row of the selected
rows.
The following
code fragment is a sample:
/* hstmtS is for SQLExtendedFetch, SQLSetPos and SQLSetCursorName */
/* hstmtU is for positioned statement */
/* use hstmtS to query */
rc=SQLSetStmtOption(hstmtS, SQL_ROWSET_SIZE, ROWS);
rc=SQLSetCursorName(hstmtS, (UCHAR *)"C1", SQL_NTS);
rc=SQLExecDirect(hstmtS, (UCHAR *)"SELECT NAME, BIRTHDAY FROM EMPLOYEE
FOR UPDATE OF BIRTHDAY", SQL_NTS);
rc=SQLBindCol(hstmtS, 1, SQL_C_CHAR, szName, NAME_LEN, cbName);
rc=SQLBindCol(hstmtS, 2, SQL_C_CHAR, szBirthday, BDAY_LEN, cbBirthday);
/* use hstmtS (through SQLExtendedFetch) to browse all rows */
while (1)
{
rc=SQLExtendedFetch(hstmtS, SQL_FETCH_NEXT, 0, &crow, rgfRowStatus);
if (rc == SQL_ERROR || rc == SQL_NO_DATA_FOUND)
break;
for (irow = 0; irow < crow; irow++)
{
if (rgfRowStatus[irow] != SQL_ROW_DELETED)
printf("%d %10s : %30s\n", irow+1, szName[irow],
szBirthday[irow]);
}
/* read user input for line number and data to update */
/* use hstmtS to position cursor for hstmtU */
/* use hstmtU to execute positioned update statement */
while (TRUE)
{
printf("\nRow number to update? (0 to quit)");
gets((char *)szReply);
irow = atoi((char *)szReply);
if (irow > 0 && irow <= crow)
{
printf("\nNew birthday? ");
gets((char *)szBirthday[irow-1]);
rc=SQLSetPos(hstmtS, irow, SQL_POSITION, SQL_LOCK_NO_CHANGE);
rc=SQLPrepare(hstmtU,(UCHAR *)"UPDATE EMPLOYEE SET BIRTHDAY=?
WHERE CURRENT OF C1", SQL_NTS);
rc=SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, BDAY_LEN, 0,
szBirthday[irow-1], 0, NULL);
rc=SQLExecute(hstmtU);
rc=SQLFreeStmt(hstmtU,SQL_CLOSE);
}
else if (irow == 0)
{
break;
}
}
}
|
SQL_REFRESH:
refresh row data of the rowset. This will refetch the same window into
the rowset buffer. The row status of the newly fetched row is set to
SQL_ROW_SUCCESS and the rows not in result set are set to SQL_ROW_NOROW.
if irow = 0: position the cursor on the whole rowset and refresh
the whole rowset.
If irow = n: DBMaker does not currently support other values
for irow with SQL_REFRESH.
Note that
the row status for refreshed rows will be set to SQL_ROW_SUCCESS if
this option is successful. The window (rowset) might be moved forward
or backward in the result set if the refreshed rowset is full of "holes"
created by the SQL_DELETE option.
SQL_UPDATE:
updates row data. The corresponding rows in the target table are updated
with row data from the rowset buffer. The row status of the successfully
updated rows are set to SQL_ROW_UPDATED. You cannot update a row which
is marked SQL_ROW_DELETED.
if irow = 0: positions the cursor on the whole rowset and updates
the whole rowset.
If irow = n: positions the cursor on row n and updates it.
SQL_DELETE:
deletes the corresponding rows in the target table mapped by the rowset.
You cannot delete a row which is marked SQL_ROW_DELETED. If rows are
deleted (set as SQL_ROW_DELETED), you cannot perform the following operations
on them: positioned UPDATE/DELETE statements, calls to SQLGetData,
or calls to SQLSetPos with any options other than SQL_POSITION
(you can only call SQLSetPos with SQL_SET_POSITION on rows set
to SQL_ROW_DELETED).
if irow = 0: position the cursor on the whole rowset and delete
the the whole rowset.
if irow = n: position the cursor on row n and deletes it.
SQL_ADD:
adds row data. The row status of the added rows is set to SQL_ROW_ADDED.
If this option is applied, the rowset is just used as a user input buffer
for data to be inserted. No corresponding rows in the target table are
mapped by rows in the rowset. This is the only option that allows irow
to be greater than the rowset size. Note that this option does not change
the cursor position(no cursor positioning is done). When inserting columns
not bound to the rowset buffer, default values (if available) are used
or NULL values(if default values not available) are used.
if irow
= 0: add all rows of the rowset.
if irow
= 1 to rowset size: add row irow of the rowset.
if irow
> rowset size: row irow is still found from the start of rowset
buffer with an appropriate offset. For example, if irow = rowset
size + 1, or irow = rowset size + 2, add row irow.
Obviously,
an ODBC application allocates more buffer space than the rowset buffer
space (specified by SQL_ROWSET_SIZE). This simplifies ODBC application
programming. If no extra buffer is allocated and irow is greater
than the rowset size, then an application program error may result from
trying to access illegal memory addresses.
fLock Argument
To lock
or unlock the corresponding operated rows in target table. The valid
values for fLock are:
SQL_LOCK_NO_CHANGE:
do not change the row's lock mode.
Column Indicators
When an
ODBC application wants to insert a NULL value in a column, the only
interface is the column indicator (status). There is no host variable
(like the host variables from INSERT INTO t1 VALUES (?,?)) we can retrieve
information from or specify for inserting NULL values into columns.
With SQLExtendedFetch and SQLSetPos, the column indicators
from SQLBindCol are the only interface for specifying information
for both fetching and modification.
For example,
if an ODBC application wants to update a column with NULL values (or
insert a NULL value to that column), then the corresponding column indicator
should be set to SQL_NULL_DATA before calling SQLSetPos with
the SQL_UPDATE or SQL_INSERT options. The same method is used if the
default value is to be applied, but the column indicator should be set
to SQL_DEFAULT_PARAM.
SQLPutData
SQLGetData,
if used with SQLExtendedFetch, is the only way to fetch unbound
columns (which are mostly BLOB/file object columns). Similarly, you
call SQLSetPos with SQLPutData to modify those unbound
columns (which are mostly BLOB/file object columns). There is no column
indicator to use and you can only use argument cbValue of SQLPutData.
The rowset size must be 1 before executing SQLGetData or SQLPutData.
For non-projection
columns, default values are applied by SQLSetPos with SQL_ADD
options. For example, suppose the table schema is:
CREATE TABLE t1 (c1 INT, c2 INT, c3 CHAR(5) DEFAULT 'col3')
|
and the
query is:
and we
make following calls to modify table t1:
/* bind columns c1, c2 , execute and fetch */
SQLBindCol(hstmt, 1, SQL_C_CHAR, c1_rgbValue, c1_len ,c1_pcbValue);
SQLBindCol(hstmt, 2, SQL_C_CHAR, c2_rgbValue, c2_len ,c2_pcbValue);
SQLExecute(hstmt);
SQLExtendedFetch(hstmt, SQL_FETCH_FIRST, 0, &crow, rgfRowStatus);
/* specify c1, c2 values in first row of rowset */
/* first row of rowset is used as input buffer */
:
SQLSetPos(hstmt, 1, SQL_ADD, SQL_LOCK_NO_CHANGE); /* default for c3 */
/* specify c1,c2 values in first row of rowset. First row of rowset */
/* is used as input buffer. Update the row (in table t1) */
/* corresponding to first row in rowset. */
SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);/* c3 not changed */
|
Note that
column c3 is not in the projection and only columns c1
and c2 can be found in the rowset. In other words, to insert
one extra)tuple, SQLSetPos gets values from the rowset for columns
c1 and c2 and uses default value `col3' for column c3.
Also note
that to update the corresponding row in t1 for the first row
in the rowset, sure column c3 is not changed since c3
is not in the projection.
For bound columns, obviously SQLSetPos gets all the input data
it needs (for the SQL_ADD and SQL_UPDATE options) from the rowset (the
bound buffer).
For each of those unbound columns, if it is neither a BLOB (LONG VARCHAR
or LONG VARBINARY) nor file object type, then the default value is still
applied if needed for SQLSetPos. You cannot use SQLPutData
to put data for this type of column since the default value is used
when SQLSetPos is executed.
For unbound BLOB/file object columns, we must use SQLPutData
to modify them. Before SQLPutData and after SQLSetPos,
we still need to execute SQLParamData to find all the unbound
BLOB/file object columns:
· to input NULL values, call SQLPutData with argument
cbValue set to SQL_NULL_DATA.
· to input default values, call SQLPutData with
argument cbValue set to SQL_DEFAULT_PARAM.
· to input data, call SQLPutData with input data
in argument rgbValue and SQL_NTS or the length of rgbValue
in argument cbValue. To input data the SQL_C_TYPE for the LONG
VARCHAR and LONG VARBINARY data types are SQL_C_LONGVARCHAR
and SQL_C_LONGVARBINARY.
· to input NULL values, call SQLPutData with argument
cbValue set to SQL_NULL_DATA.
· to input default values, call SQLPutData with
argument cbValue set to SQL_DEFAULT_PARAM.
· to input data, make the following call after first executing
SQLSetPos and before executing SQLPutData:
SQLSetStmtOption(hstmt, SQL_SPOS_FO,
SQL_SPOS_FO_DATA|col);
|
This
specifies that the following SQLPutData call will insert data
from argument rgbValue into column col which is a file
object column in the projection. Note that col is the index
of the target file object column in the projection. The option SQL_SPOS_FO_DATA
will force the system to use SQL_C_CHAR or SQL_LONGVARCHAR for binding
the input data. A system file will be automatically created for this
type of data input.
to input user files, make the following call after first executing SQLSetPos
and before execute SQLPutData:
SQLSetStmtOption(hstmt, SQL_SPOS_FO, SQL_SPOS_FO_SFILE|col)
|
This
specifies the following SQLPutData call will insert a user
file whose filename is specified in argument rgbValue into
column col which is a file object column in the projection.
The option SQL_SPOS_FO_SFILE will force the system to use SQL_C_CHAR
or SQL_FILE for binding the input data.
to input system files, make the following call after first executing
SQLSetPos and before executing SQLPutData:
SQLSetStmtOption(hstmt, SQL_SPOS_FO, SQL_SPOS_FO_CFILE|col)
|
This
specifies the following SQLPutData call will insert a system
file whose filename is specified in argument rgbValue into
column col which is a file object column in the projection.
The option SQL_SPOS_FO_CFILE will force the system to use SQL_C_FILE
or SQL_LONGVARCHAR for binding the input data.
The following
example shows how to use SQLSetPos and SQLPutData to input
BLOB and file object data:
Suppose
the table schema is:
create table t1 (c1 int, c2 long varchar, c3 file, c4 int default 10)
|
and the
query is:
select c2, c3, c4 from t1
|
Then you
can use code similar to the following:
/* do not bind any column */
SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, 1);
:
/* execute and fetch */
SQLExecute(hstmt);
SQLExtendedFetch(hstmt, SQL_FETCH_FIRST, 0, &crow, rgfRowStatus);
/* call SQLSetPos to insert one tuple */
/* SQLSetPos returns SQL_NEED_DATA */
SQLSetPos(hstmt, 1, SQL_ADD, SQL_LOCK_NO_CHANGE); /*default(10) for c4*/
/* input null for c2(LONG VARCHAR) */
SQLParamData(hstmt, (void *)¶num);
SQLPutData(hstmt, buf,SQL_NULL_DATA);
/* input user file for c3(file) */
SQLParamData(hstmt, (void *)¶num);
/* specify user file input and place file name in sbuf */
SQLSetStmtOption(hstmt, SQL_SPOS_FO, SQL_SPOS_FO_SFILE|2); /* 2 for c3*/
:
/* input user file for c3(file) */
SQLPutData(hstmt, sbuf, strlen(sbuf));
|
Returning Values and Processing Errors of SQLSetPos
SQLSetPos
modifies more than one row at a time, so the rules for the return value
are similar to SQLExtendedFetch. Each successful (or with warning)
operation on a row is marked according to the option used. The row will
be marked SQL_ROW_ERROR if an error is found and operation is not stopped
unless it is a critical error such as an aborted transaction.
The rules
are stated as following:
Return SQL_SUCCESS if no errors or warnings are found.
Return SQL_NO_DATA_FOUND if there are no rows to fetch (for the SQL_REFRESH
option only).
Return SQL_SUCCESS_WITH_INFO if there are any warnings or errors found
during the operation. We can call SQLError to get the complete
error information. If there are only warnings and no errors, then only
the last warning is recorded.
Return SQL_ERROR if critical error is found in operation.
Note that
the partial result of SQLSetPos done before the tuple where the
error is found is not undone. That is, this function is not an atomic
operation. Each call to SQLSetPos (except with the options SQL_REFRESH
and SQL_POSITION) will commit work after it is successfully executed
if autocommit mode is on.
Limitations of SQLSetPos
Result
sets from a query with a subquery are not modifiable; you cannot call
SQLSetPos for that type of result set.
|
