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

[5 Apr 2005 11:58] Hakan Küçükyılmaz
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
[13 May 2005 21:27] Konstantin Osipov
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.
[13 May 2005 22:15] Bugs System
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
[16 May 2005 14:29] Bugs System
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
[1 Jun 2005 14:35] Michael Widenius
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
[3 Jun 2005 22:57] Paul Dubois
Noted in 5.0.6 changelog.