Re: help with a simple ODBC program

Patrick Galbraith wrote:
> 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
> 
> 
> ------------------------------------------------------------------------

Ok, I found time sooner than I thought.

> 
> #include <stdio.h>
> #include <string.h>
> #include <sql.h>
> #include <stdlib.h>
> #include <sqlext.h>
> 
> SQLHENV *env;
> SQLHDBC *dbc;

I know you were not after a C lesson, but I'd really avoid these 
globals, put them in main and pass them to run_sql.


> 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 */

Never seen anyone do it like that but technically nothing wrong with it.
Why not just do:

SQLHENV env;
SQLHDBC dbc;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

then use env/dbc without derefencing?

>   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))

You can use the SQL_SUCCEEDED macro to avoid testing for both types of 
success.

>   {
>     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);

I think is exactly why allocating memory for an HENV and HDBC did not 
feel right to me. You have not called SQLDisconnect and SQLFreeHandle on 
the dbc and 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);

I think that should be %p for 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);

this is probably your problem. len is SQLPOINTER and it was never 
assigned to anything and SQLColAttribute wants to write a to it.
It should be the address of a SQLUINTEGER or SQLULEN for SQL_DESC_LENGTH.


>       /* 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);
>       }
>     }
>   }

You should call SQLFreeHandle on stmt.

>   free(stmt);
> }

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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