help with a simple ODBC program

Patrick Galbraith <[email protected]> Tue, 01 May 2007 09:57:21 -0400

Newsgroups	gmane.comp.db.mysql.odbc
Message-ID	<[email protected]>

Hi all,

I'm working on a federated odbc storage engine, and was having problems 
fetching results of a 'show table status'. I have one main odbc database 
handle in the storage engine, and then allocate and free statement 
handles for whatever calls I need to make. One of the storage engine 
methods, "::info", does a 'show table status like 'tablename'". I kept 
banging my head with the code segfaulting on fetching result sets, and 
didn't know if it was something to do with the way I allocate memory in 
the storage engine, so I decided to remove the problem into a simpler  
piece of code.

 The code as far as I could tell, should work. What I did next was just 
to write a simple C program that does just one simple show table status 
call. It worked when I had the statement allocation and fetching all in 
"main", but when I tried to see if having statement allocation in a 
different function (one that simply takes an sql query and runs it) it 
segfaults in the part of the program that iterates through the columns 
of the result set. Everything works up to the point where my program 
reports the number of columns, but then segfaults on any subsequent call 
such as SQLColAttribute or SQLGetData. I can't figure it out. It makes 
no sense. The loop that does all this worked perfectly when in the same 
function (main) as the rest of the program setup.

I'm attaching this program that I wrote for anyone who would like to 
help me  with this. I would appreciate any help, in advance, that anyone 
can please give!

Kind regards,

Patrick

-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad


-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]

test_info.c (text/plain, 3.2 KB)

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

SQLHENV *env;
SQLHDBC *dbc;

main()
{
  env= malloc(sizeof(SQLHENV));
  dbc= malloc(sizeof(SQLHDBC));
  SQLRETURN function_result;
  SQLSMALLINT mlen;
  SQLINTEGER odbc_err;
  char msg[200];
  char sql_stat[10];   // Status SQL

  /* Allocate an environment handle */
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, env);
  /* We want ODBC 3 support */
  SQLSetEnvAttr(*env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  /* Allocate a connection handle */
  SQLAllocHandle(SQL_HANDLE_DBC, *env, dbc);
  /* Connect to the DSN mydsn */
  /* You will need to change mydsn to one you have created and tested */
  function_result= SQLDriverConnect(*dbc, (void *)1, "Driver=myodbc3;Server=localhost;Database=federated_odbc;Port=5555;socket=/tmp/mysql-5555.sock;Option=3;UID=root", SQL_NTS,
    NULL, 0, NULL, SQL_DRIVER_COMPLETE);

  if ((function_result != SQL_SUCCESS) &&
        (function_result != SQL_SUCCESS_WITH_INFO))
  {
    printf("Unable to connect, Damnit!\n");
    SQLGetDiagRec(SQL_HANDLE_DBC, *dbc,1,
                  sql_stat, &odbc_err,msg,100,&mlen);
    printf("%s (%d)\n",msg,odbc_err);
    SQLFreeHandle(SQL_HANDLE_ENV, *env);
    return(0);
  }
  run_sql("SHOW TABLE STATUS LIKE 't1'");
  free(dbc);
  free(env);
}

int run_sql(char *query)
{
  SQLHSTMT stmt= malloc(sizeof(SQLHSTMT));
  SQLRETURN function_result;
  SQLSMALLINT mlen,columns;
  SQLINTEGER     odbc_err,row=0;
  char msg[200];
  char sql_stat[10];   // Status SQL
  printf("QUERY %s\n", query);

  /* Allocate a statement handle */
  function_result= SQLAllocHandle(SQL_HANDLE_STMT, *dbc, stmt);
  printf("function_result from alloc of stmt %d\n", (int) function_result);
  /* Retrieve a list of tables */
  //SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
  function_result= SQLExecDirect(*stmt, query, SQL_NTS);
  if (function_result != SQL_ERROR)
    printf("SQLExecDirect function_result %d\n", (int) function_result);
  else
  {
    printf("SQLExecDirect error function_result %d\n", (int) function_result);
    exit(0);
  }


  /* How many columns are there */
  SQLNumResultCols(*stmt, &columns);
  printf("SQLNumResultCols returned %d\n", function_result);
  printf("total columns %d\n", columns);
  /* Loop through the rows in the result-set */
  while (SQL_SUCCEEDED(function_result = SQLFetch(*stmt))) {
    SQLUSMALLINT i;
    printf("Row %d stmt %lx\n", row++, stmt);
    /* Loop through the columns */
    for (i = 1; i <= columns; i++) {
      SQLPOINTER *len;
      SQLINTEGER indicator;
      char buf[1024];

      SQLColAttribute(*stmt, i, SQL_DESC_LENGTH, NULL, (SQLSMALLINT) NULL,NULL, len);
      /* retrieve column data as a string */
      function_result= SQLGetData(*stmt, i, SQL_C_CHAR,
                       buf, sizeof(buf), &indicator);
      if (SQL_SUCCEEDED(function_result)) {
        /* Handle null columns */
        if (indicator == SQL_NULL_DATA)
        {
          strcpy(buf, "NULL");
          *len= 0;
        }
        if (!strlen(buf))
        {
          *len= 0;
        }
        printf("  Column %u : %s length %d\n", i, buf, (int)*len);
      }
    }
  }
  free(stmt);
}