MySQL Bugs: #9643: CURSOR_TYPE_SCROLLABLE dos not work

Bug #9643	CURSOR_TYPE_SCROLLABLE dos not work
Submitted:	5 Apr 2005 11:58	Modified:	3 Jun 2005 22:57
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4	OS:	Linux (SuSE 9.2)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
CURSOR_TYPE_SCROLLABLE is defined in mysql_com.h but not usable

How to repeat:
include <stdio.h>
#include <stdlib.h>
#include <mysql.h>

MYSQL_STMT *stmt1, *stmt2;
MYSQL *mysql;

void die_if(int rc, int line, const char *msg) {
  if (rc) {
    printf("Error in line %d: %s\n", line, msg);
    exit (-1);
  }
}

MYSQL_STMT *open_cursor(char *query) {

  /* Works
  const unsigned long type= CURSOR_TYPE_READ_ONLY; 
  */

  /* Dos not work */
  const unsigned long type= CURSOR_TYPE_SCROLLABLE;
  
  MYSQL *stmt=  mysql_stmt_init(mysql);
  die_if(mysql_stmt_prepare(stmt, query, strlen(query)), __LINE__, mysql_stmt_error(stmt));
  die_if(mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *)&type), __LINE__, mysql_stmt_error(stmt));
  return stmt;
}

int main (int argc, char **argv) {
  MYSQL_BIND bind[2];
  char a[6], b[6];
  unsigned long a_len, b_len;
  int rc;
  int cnt = 0;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 0);
  
  mysql_query(mysql, "DROP TABLE IF EXISTS t1");
  mysql_query(mysql, "CREATE TABLE t1 (a char(5), b char(5), c char(5), PRIMARY KEY (a, b, c)) ENGINE InnoDB");
  mysql_query(mysql, "INSERT INTO t1 VALUES ('x' , 'y', 'z')");
  mysql_query(mysql, "INSERT INTO t1 VALUES ('a' , 'b', 'c')");
  mysql_query(mysql, "INSERT INTO t1 VALUES ('k' , 'l', 'm')");
  
  stmt1= open_cursor("SELECT DISTINCT a FROM t1");
  stmt2= open_cursor("SELECT b FROM t1");

  die_if(mysql_stmt_execute(stmt1), __LINE__, mysql_stmt_error(stmt1));
  die_if(mysql_stmt_execute(stmt2), __LINE__, mysql_stmt_error(stmt2));
  
  bind[0].buffer_type=    MYSQL_TYPE_VAR_STRING;
  bind[0].buffer=        (char *) a;
  bind[0].buffer_length= sizeof(a);
  bind[0].is_null=       0;
  bind[0].error=         NULL;
  bind[0].length=        &a_len;

  bind[1].buffer_type=    MYSQL_TYPE_VAR_STRING;
  bind[1].buffer=        (char *) b;
  bind[1].buffer_length= sizeof(b);
  bind[1].is_null=       0;
  bind[1].error=         NULL;
  bind[1].length=        &b_len;

  die_if(mysql_stmt_bind_result(stmt1, bind), __LINE__, mysql_stmt_error(stmt1));
  die_if(mysql_stmt_bind_result(stmt2, bind), __LINE__, mysql_stmt_error(stmt2));

  while (!(rc= mysql_stmt_fetch(stmt1))) {
    while (!(rc= mysql_stmt_fetch(stmt2))) {
      cnt++;
    }
    cnt++;
  }

  printf("Fetched %d rows\n", cnt);

  mysql_stmt_close(stmt1);
  mysql_stmt_close(stmt2);
  
  mysql_close(mysql);
}

hakan@x40:~/work/mysql/test> ./cursor_scrollable
Error in line 47: Commands out of sync; you can't run this command now

Scrollable cursors are not implemented. This value of the attribute is ignored by the server.
The fix is to provide an error message in such case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24884

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24949

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Noted in 5.0.6 changelog.