For the ASE ODBC Driver

The following describes scrollable cursors for the ASE ODBC Driver.

Support for the Static Insensitive scrollable cursor

The ASE ODBC Driver supports the Static Insensitive scrollable cursor. It implements the ODBC SQLFetchScroll method to scroll and fetch rows. The SQLFetchScroll method is a standard ODBC method defined in Microsoft Open Database Connectivity Software Development Kit Programmer’s Reference, Volume 2, which is part of the MSDN library. Go to the Microsoft Web site for more information.

The ODBC driver supports the following scrolling types:

• SQL_FETCH_NEXT – return the next rowset.

• SQL_FETCH_PRIOR – return the prior rowset.

• SQL_FETCH_RELATIVE – return the rowset n from the start of the current rowset.

• SQL_FETCH_FIRST – return the first rowset in the result set.

• SQL_FETCH_LAST – return the last complete rowset in the result set.

• SQL_FETCH_ABSOLUTE – return the rowset starting at row n.

Setting scrollable cursor attributes

You must set the following attributes to use scrollable cursors:

• SQL_ATTR_CURSOR_SCROLLABLE – the type of scrollable cursor you are using. It should be set to the value of SQL_SCROLLABLE. Possible values are static, semi-sensitive, insensitive.

• SQL_ATTR_CURSOR_SENSITIVITY – the sensitivity value for this scrollable cursor.

  The only supported value for this is SQL_INSENSITIVE.

The following are optional attributes when using scrollable cursors.

• SQL_ATTR_ROW_ARRAY_SIZE – the number of rows that you want returned from each call to the SQLFetchScroll() method.

  If you do not set this value, the default value of one row is used.

• SQL_ATTR_CURSOR_TYPE – The type of scrollable cursor you are using.

  The only supported values for this are SQL_CURSOR_FORWARD_ONLY or SQL_CURSOR_STATIC.

• SQL_ATTR_ROWS_FETCHED_PTR – the address where the number of rows fetched are stored. The SQL_ATTR_ROWS_FETCHED_PTR points to a variable of data type SQLUINTEGER.

• SQL_ATTR_ROW_STATUS_PTR – the address where the row status is stored. The SQL_ATTR_ROW_STATUS_PTR points to a variable of data type SQLUSMALLINT.

Executing scrollable cursors

To set up a program to execute a scrollable cursor

1. Set the scrollable cursor attributes for your environment.

   See “Setting scrollable cursor attributes” for more information.

2. Bind the results. For example, add the following to your program:

   res=SQLBindCol(m_StatementHandle, 2, SQL_C_DOUBLE, price, 0, NULL);
   

   res=SQLBindCol(m_StatementHandle, 3, SQL_C_LONG, quantity, 0, NULL);
   

3. Scroll and fetch by using SQLFetchScroll(). For example, add the following to your program:

   res = SQLSetStmtAttr(m_StatementHandle, SQL_ATTR_CURSOR_SCROLLABLE,
   (SQLPOINTER)SQL_SCROLLABLE,SQL_IS_INTEGER);
   
   res = SQLSetStmtAttr(m_StatementHandle, SQL_ATTR_CURSOR_SENSITIVITY,
   SQLPOINTER)SQL_INSENSITIVE, SQL_IS_INTEGER);
   
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_NEXT,0);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_PRIOR,0);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_FIRST,0);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_LAST,0);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_ABSOLUTE,2);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_ABSOLUTE,-2);
   res = SQLFetchScroll(m_StatementHandle, SQL_FETCH_RELATIVE,1);
   

4. Execute the Select statement. For example, add the following to your program:

   res = SQLExecDirect(m_StatementHandle, (SQLCHAR "select price, quantity
   from book" SQL_NTS);
   

5. Close the result set and the cursor. For example, add the following to your program:

   res = SQLFreeStmt(m_StatementHandle,SQL_CLOSE);
   

Looking at results

After you execute a scrollable cursor, you will see these results, assuming a total of N rows and a rowset m where N > m:

The following results are expected if the current cursor points to row k and k-a > 0, k + m + a < N, a>=0:

Implicit setting of scrolling cursor attributes

Certain attributes are set implicitly when your application sets specific attributes. The supported ODBC scrollable cursor attributes set implicitly are as follows:

---

Copyright © 2008. Sybase Inc. All rights reserved.

View this document as PDF