Re: Fix for Declare/Fetch issue - Mailing list pgsql-odbc

My test case failed.

I tested only column wise query.
It returned the correct number of rows, but only the first cursor fetch got
correct data.

The self contained test case is attached:
it creates a test table, inserts there six rows and fetches
those rows as column wise. Please use Fetch=2 with it.
Finally it drops the test table.
You need to change DATABASE, USERID and PASSWORD.

It worked without Declare/fetch as it should:

OK(UseDeclareFetch=0):

After colwise query: data[1] = { 4, 0001 }, data[2] = { 4, 1001 }
After colwise query: data[1] = { 4, 2001 }, data[2] = { 4, 3001 }
After colwise query: data[1] = { 4, 4001 }, data[2] = { 4, 5001 }
ok.
(4= number of characters. "0001" is a character string value.)

FAILED (UseDeclareFetch=1,Fetch=2):

After colwise query: data[1] = { 4, 0001 }, data[2] = { 4, 1001 }
After colwise query: data[1] = { -1, UNSET }, data[2] = { -1, UNSET }
After colwise query: data[1] = { -1, UNSET }, data[2] = { -1, UNSET }
ok.

-1 means possibly a NULL value(?).
UNSET means that the string buffer is uninitialized after SQLFetchScroll.

Unfreed memory was a problem with this failed test case.
You can confirm that by setting the number of rows into 600 000 and
using Fetch=2 with Declare/Fetch.

The program prints
"INITIALIZATION OF 600000 ROWS DONE". Then the process's used memory
grows very fast.

Regards,
Marko Ristola.

Dave Page wrote:

>Great, thanks Anoop.
>
>Can anyone test this fairly quickly? I don't have the appropriate hardware
>to hand for the next few days, and it would be helpful for a number of
>reasons if we could get this committed before November.
>
>Thanks, Dave.
>
>
>



#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

#define NUMBEROF_TEST_ROWS (6)

int CHECK(SQLSMALLINT handle_type,SQLHANDLE handle,SQLRETURN ret)
{
  if (ret != SQL_SUCCESS && ret != SQL_NO_DATA)
    {
      SQLRETURN ret;
      SQLINTEGER i = 0;
      SQLCHAR state[32];
      SQLINTEGER native;
      SQLCHAR text[512];
      SQLSMALLINT text_len;
      int has_err = 0;

      do {
    i++;

    ret = SQLGetDiagRec(handle_type,handle,i,state,&native,text,sizeof(text), &text_len);
    fprintf(stderr,"message=%s\nstate=%s native error %d.\n",text,state,native);
        has_err=1;
      } while(ret == SQL_SUCCESS);

      if (!has_err)
          fprintf(stderr,"An unknonw error occurred\n");
    }

  if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO || ret == SQL_NO_DATA)
    return 1;

  return 0;
}

// PREPARATIONS
int CreateInitialData(SQLHANDLE m_env,SQLHANDLE m_conn)
{
    int row;
    int nextVacuum=1024;

    SQLHANDLE stmt;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLSetConnectOption(m_conn,SQL_AUTOCOMMIT,0)))
    return 1;

    if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&stmt)))
      return 1;

    if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"CREATE TABLE test_testcolwisequery ("
      "  key1 VARCHAR(250),"
      "  key2 VARCHAR(250),"
      "  value1 VARCHAR(250),"
      "  value2 VARCHAR(250),"
      "  primary key(key1,key2))",SQL_NTS)))
      goto fail;

  for (row=0; row < NUMBEROF_TEST_ROWS; row++)
    {
      char key1[200],key2[200],value1[200];
      char sqlcmd[512];

      sprintf(key1,"%d000",row);
      sprintf(key2,"%d001",row);
      sprintf(value1,"%d002",row);

      sprintf(sqlcmd,"INSERT INTO test_testcolwisequery(key1,key2,value1) values( '%s' , '%s' , '%s' )",
          key1,key2,value1);

      if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)sqlcmd,SQL_NTS)))
        goto fail;

      if (row % 10000 == 0) {
        if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
          goto fail;
      }

      if (row == nextVacuum) {
          if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
          goto fail;

        if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"VACUUM ANALYZE test_testcolwisequery",SQL_NTS)))
          goto fail;

          if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
          goto fail;
        nextVacuum *=2;
      }
    }

    if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
      goto fail;

    SQLFreeHandle(SQL_HANDLE_STMT,stmt);

    if (NUMBEROF_TEST_ROWS > 100)
      printf("INITIALIZATION OF %d ROWS DONE.\n",NUMBEROF_TEST_ROWS);

    return 1;

    fail:

    SQLFreeHandle(SQL_HANDLE_STMT,stmt);
    return 0;
}

int DropTestEnv(SQLHANDLE m_env,SQLHANDLE m_conn)
{
    SQLHANDLE stmt;

    if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&stmt)))
      return 1;

    if (!CHECK(SQL_HANDLE_STMT,stmt,SQLExecDirect(stmt,(SQLCHAR*)"DROP TABLE test_testcolwisequery",SQL_NTS)))
      goto fail;

    if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
      goto fail;

    SQLFreeHandle(SQL_HANDLE_STMT,stmt);

    return 1;

    fail:

    SQLFreeHandle(SQL_HANDLE_STMT,stmt);
    return 0;
}

// PREPARATIONS END


#define TEXT_SIZE 300

SQLINTEGER data_len[2];
char data[2][TEXT_SIZE];

void initBufs() {
    data_len[0]=-123;
    data_len[1]=-123;

    strcpy(data[0],"UNSET");
    strcpy(data[1],"UNSET");
}

void PrintData(char *msg,SQLUINTEGER nrows)
{
    unsigned int i;

    printf("%s: ",msg);

    for (i=0; i<nrows; i++) {
        if (i) printf(", ");

        printf("data[%d] = { %d, %s }",i+1,(int)data_len[i],data[i]);
    }

    printf("\n");

    fflush(stdout);
}

/* You need the following definition:
 * CREATE TABLE test_testcolwisequery(value1 VARCHAR(100));
 *
 *
 */
#define USERID "marko"
#define DATABASE "marko"
#define PASSWORD "marko"

int main(int argc, char **argv)
{
  SQLHANDLE m_env;
  SQLHANDLE m_conn;
  SQLHANDLE m_stmt;
  SQLUINTEGER fetchedRows;
  SQLINTEGER statuses[2];
  SQLRETURN rc;

  if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_env))
    {
      fprintf(stderr,"Allocating ENV handle failed.\n");
      return 1;
    }

  if (!CHECK(SQL_HANDLE_ENV,m_env,SQLSetEnvAttr(m_env, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3,0)))
    return 1;

  if (!CHECK(SQL_HANDLE_ENV,m_env,SQLAllocHandle(SQL_HANDLE_DBC,m_env,&m_conn)))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLConnect(m_conn,(SQLCHAR*) DATABASE,
          SQL_NTS,(SQLCHAR*) USERID,SQL_NTS,(SQLCHAR*) PASSWORD,SQL_NTS)))
      return 1;

  if (!CreateInitialData(m_env,m_conn))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLAllocHandle(SQL_HANDLE_STMT,m_conn,&m_stmt)))
    return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN,0)))
       return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_ARRAY_SIZE, (void*)2,0)))
       return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROW_STATUS_PTR, statuses,0)))
       return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLSetStmtAttr(m_stmt, SQL_ATTR_ROWS_FETCHED_PTR, &fetchedRows,0)))
       return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLBindCol(m_stmt,1, // Column number
                               SQL_C_CHAR,           // C data type
                               &data[0],            // parameter value ptr
                               TEXT_SIZE,             // buffer length
                               (SQLINTEGER*)&data_len[0]))) // string length ptr
    return 1;

  if (!CHECK(SQL_HANDLE_STMT,m_stmt,SQLExecDirect(m_stmt,(SQLCHAR*) "SELECT key2 FROM test_testcolwisequery",
SQL_NTS)))
    return 1;

  initBufs();
  while ( (rc = SQLFetchScroll(m_stmt,SQL_FETCH_NEXT, 0)) != SQL_NO_DATA) {
    PrintData("After colwise query",fetchedRows);
    initBufs();
  }

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLFreeStmt(m_stmt,SQL_CLOSE)))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
    return 1;

//  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_ROLLBACK)))
//    return 1;

  if (!DropTestEnv(m_env,m_conn))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLTransact(m_env,m_conn,SQL_COMMIT)))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLFreeStmt(m_stmt,SQL_DROP)))
    return 1;

  if (!CHECK(SQL_HANDLE_DBC,m_conn,SQLDisconnect(m_conn)))
    return 1;

  if (!CHECK(SQL_HANDLE_ENV,m_env,SQLFreeConnect(m_conn)))
    return 1;

  if (SQL_SUCCESS != SQLFreeEnv(m_env))
    {
      fprintf(stderr,"Connect env freeing failed\n");
      return 1;
    }

  fprintf(stderr,"ok.\n");

  return 0;
}