Re: Declare/Fetch - Mailing list pgsql-odbc

I tested this a bit more now.
I tried to make more small test cases.

Here are the results:

Normal query okay without SQLBindCol (use SQLGetData for getting the data):

marko@amilo:~/workspace/SQLLIB/tests$ ./TestQuery
data = { 4, 0001 }
data = { 4, 1001 }
data = { 4, 2001 }
data = { 4, 3001 }
data = { 4, 4001 }
data = { 4, 5001 }

ok.


SQLBindCol used, but not fetching many rows:

marko@amilo:~/workspace/SQLLIB/tests$ ./TestPreparedQuery
 data[1] = { 4, 0001 }
 data[1] = { 4, 1001 }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }
 data[1] = { -1, UNSET }

SQLBindCol used, with columnwise query:

marko@amilo:~/workspace/SQLLIB/tests$ ./TestColwiseQuery
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.

So the problem seems to be always, if SQLBindCol is used.
In that case the new libpq resultset is lost ???.

So SQLBindCol() loses the data always after the first fetch.


TestPreparedQuery.c contains this simplest test case.
It is not prepared although, but it uses SQLBindCol().

Marko

Dave Page wrote:

>
>
>
>
>>-----Original Message-----
>>From: Johann Zuschlag [mailto:zuschlag2@online.de]
>>Sent: 02 November 2005 13:02
>>To: Dave Page; anoopk@pervasive-postgres.com;
>>pgsql-odbc@postgresql.org
>>Subject: Declare/Fetch
>>
>>Hi Dave, hi Anoop,
>>
>>
>
>Hi Johann,
>
>Unfortunately Anoop et al. are out for a few days, so I'm desperately
>trying to understand and fix this before 8.1 :-(
>
>
>
>>In qresult.c you still find:
>>
>>515    if (fetch_count < fetch_count)
>>
>>Declare/Fetch will not work without changing that, i.e. just
>>fetch one line.
>>
>>515    if (fetch_count < num_backend_rows)
>>(Dave's proposal)
>>
>>seems to be a better choice.
>>
>>
>
>Updated in my local copy (thanks) - unfortunately not a fix to the bug
>reported by Marko.
>
>I have narrowed it down some more though - it's not so much a colwise
>issue, as a bind issue. You can see it with the following:
>
>1) Set cache size to 2 and enable Declare/Fetch
>2) Connect
>3) SQLExecDirect "SELECT relname FROM pg_class"
>4) Bind to column 1
>5) SQLFetch
>6) SQLFetch
>7) SQLFetch *bang* :-)
>
>I'm largely unfamiliar with this part of the code so any help would be
>appreciated. FWIW, the bug seems to be libpq version specific - it's
>certainly not in 07.xx.
>
>Regards, dave
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>


#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_testpreparedquery ("
      "  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_testpreparedquery(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_testpreparedquery",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_testpreparedquery",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[1];
char data[1][TEXT_SIZE];

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

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

static 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_testpreparedquery(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;
  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,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_testpreparedquery",
SQL_NTS)))
    return 1;

  initBufs();
  while ( (rc = SQLFetch(m_stmt)) != SQL_NO_DATA) {

    if (!CHECK(SQL_HANDLE_STMT,m_stmt,rc))
      return 1;

    PrintData("After colwise query",1);
    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;
}