Re: [PHP-DEV] RE: ODBC cursor fix From: Dave Walton (walton <email protected>)
Date: 11/09/98

On 2 Nov 98, at 16:44, Dave Walton wrote:

> On 17 Sep 98, at 9:06, Shane Caraveo wrote:
>
> > I've looked at this stuff further now, and realize there isn't any way to do
> > this other than extra options to the connect function.
> >
> > Email me the stuff, I'll commit it to the cvs tree.
>
> Shane,
>
> Here's the diff (finally). This works for us on NT with MS-SQL,
> though it could potentially cause problems with other configurations
> if sqlext.h doesn't define the necessary constants.

It looks like Shane isn't going to be able to take care of this for a
while. Could someone else please commit this diff?

Here's the background:

We are using PHP3 on NT4 connecting to MS-SQL via ODBC. A
few of the complex stored procedures we use cause odbc_exec()
to fail with an ODBC error reporting "Cannot open a cursor on a
stored procedure that has anything other than a single select
statement in it". Investigation showed that there are two types of
cursors: ODBC cursors and driver cursors. The default is a driver
cursor, but if we use an ODBC cursor instead, the stored
procedures run without error.

The bad news is that the cursor type for a connection can only be
set before the connection is established, which means that support
for selection of cursor type can only be added as an option to
odbc_connect(). The attached diff (which is against v1.87 of
unified_odbc.c) implements an optional fourth parameter to
odbc_connect() that specifies the cursor type. It also defines the
following constants for use in that parameter:

  SQL_CUR_USE_IF_NEEDED
  SQL_CUR_USE_ODBC
  SQL_CUR_USE_DRIVER
  SQL_CUR_DEFAULT

In addition, the option that is used for a connection is added to the
hashed details for that connection, so that a connection ID will only
be reused if the same option is given.

The only problem that may (or may not) exist with this patch is that
the above four constants must be defined in sqlext.h. Since we
don't have access to the include files for all the database servers
that use unified_odbc.c, I can't be certain that those defines exist
in all of them. That should be checked by whomever has the
proper files.

When to use:

Most people will not need to use the new option. However, anyone
encountering an ODBC error similar to the one above can try using
different connect options to correct the problem.

Dave

----------------------------------------------------------------------
Dave Walton
Webmaster, Postmaster Nordic Entertainment Worldwide
walton <email protected> http://www.nordicdms.com
----------------------------------------------------------------------

*** unified_odbc.187.c Mon Nov 2 15:56:23 1998
--- unified_odbc.c Mon Nov 2 16:36:27 1998
***************
*** 336,341 ****
--- 336,349 ----
          REGISTER_LONG_CONSTANT("ODBC_BINMODE_RETURN", 1, CONST_CS | CONST_PERSISTENT);
          REGISTER_LONG_CONSTANT("ODBC_BINMODE_CONVERT", 2, CONST_CS | CONST_PERSISTENT);
  
+ /* Define Constants for different cursor options
+ these Constants are are defined in <sqlext.h>
+ */
+ REGISTER_MAIN_LONG_CONSTANT("SQL_CUR_USE_IF_NEEDED", SQL_CUR_USE_IF_NEEDED, CONST_PERSISTENT | CONST_CS);
+ REGISTER_MAIN_LONG_CONSTANT("SQL_CUR_USE_ODBC", SQL_CUR_USE_ODBC, CONST_PERSISTENT | CONST_CS);
+ REGISTER_MAIN_LONG_CONSTANT("SQL_CUR_USE_DRIVER", SQL_CUR_USE_DRIVER, CONST_PERSISTENT | CONST_CS);
+ REGISTER_MAIN_LONG_CONSTANT("SQL_CUR_DEFAULT", SQL_CUR_DEFAULT, CONST_PERSISTENT | CONST_CS);
+
          return SUCCESS;
  }
  
***************
*** 1641,1647 ****
          char *db = NULL;
          char *uid = NULL;
          char *pwd = NULL;
! pval *arg1, *arg2, *arg3;
          UODBC_CONNECTION *db_conn;
          RETCODE rc;
          list_entry *index_ptr;
--- 1649,1656 ----
          char *db = NULL;
          char *uid = NULL;
          char *pwd = NULL;
! pval *arg1, *arg2, *arg3, *arg4;
! int cur_opt;
          UODBC_CONNECTION *db_conn;
          RETCODE rc;
          list_entry *index_ptr;
***************
*** 1652,1660 ****
--- 1661,1698 ----
          UODBC_GLOBAL(PHP3_UODBC_MODULE).resource_list = list;
          UODBC_GLOBAL(PHP3_UODBC_MODULE).resource_plist = plist;
          
+ /* Now an optional 4th parameter specifying the cursor type
+ * defaulting to the cursors default
+ */
+ switch(ARG_COUNT(ht)) {
+ case 3:
                          if (getParameters(ht, 3, &arg1, &arg2, &arg3) == FAILURE) {
                                  WRONG_PARAM_COUNT;
                          }
+ /* Use Default: Probably a better way to do this */
+ cur_opt = SQL_CUR_DEFAULT;
+ break;
+ case 4:
+ if (getParameters(ht, 4, &arg1, &arg2, &arg3, &arg4) == FAILURE) {
+ WRONG_PARAM_COUNT;
+ }
+ convert_to_long(arg4);
+ cur_opt = arg4->value.lval;
+
+
+ /* Confirm the cur_opt range */
+ if (! (cur_opt == SQL_CUR_USE_IF_NEEDED ||
+ cur_opt == SQL_CUR_USE_ODBC ||
+ cur_opt == SQL_CUR_USE_DRIVER ||
+ cur_opt == SQL_CUR_DEFAULT) ) {
+ php3_error(E_WARNING, "uODBC: Invalid Cursor type (%d)", cur_opt);
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
          
          convert_to_string(arg1);
          convert_to_string(arg2);
***************
*** 1694,1700 ****
                  RETURN_FALSE;
          }
  
! hashed_len = _php3_sprintf(hashed_details, "uodbc_%s_%s_%s", db, uid, pwd);
  
          /* FIXME the idea of checking to see if our connection is already persistent
                  is good, but it adds a lot of overhead to non-persistent connections. We
--- 1732,1738 ----
                  RETURN_FALSE;
          }
  
! hashed_len = _php3_sprintf(hashed_details, "uodbc_%s_%s_%s_%d", db, uid, pwd, cur_opt);
  
          /* FIXME the idea of checking to see if our connection is already persistent
                  is good, but it adds a lot of overhead to non-persistent connections. We
***************
*** 1731,1736 ****
--- 1769,1786 ----
                                                                    SQL_DRIVER_COMPLETE);
                  }
  #else
+
+ rc = SQLSetConnectOption(db_conn->hdbc, SQL_ODBC_CURSORS, cur_opt);
+ if (rc != SQL_SUCCESS ) { /* && rc != SQL_SUCCESS_WITH_INFO ? */
+ UODBC_SQL_ERROR(db_conn->hdbc, SQL_NULL_HSTMT, "SQLSetConnectOption");
+ SQLFreeConnect(db_conn->hdbc);
+ if (persistent)
+ free(db_conn);
+ else
+ efree(db_conn);
+ RETURN_FALSE;
+ }
+
                  rc = SQLConnect(db_conn->hdbc, db, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
  #endif
   

--
PHP Development Mailing List   http://www.php.net/
To unsubscribe send an empty message to php-dev-unsubscribe <email protected>
For help: php-dev-help <email protected>