Re: help with a simple ODBC program

Patrick Galbraith wrote:
> Martin,
> 
> Thanks! I should have made a disclaimer that my sample code was ugly and 
> not up to spec ;)

I suspected from what you said it was just code knocked up to reproduce 
the problem.

> Martin Evans wrote:
> 
>> -- 
>>
>> 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.
> 
> 
> I'm always up for tips and learning ;)
> The reason I did this is that the current version of Federated has a 
> class variable 'MYSQL *mysql'
> 
> class ha_federated: public handler
> {
>  THR_LOCK_DATA lock;      /* MySQL lock */
>  FEDERATED_SHARE *share;    /* Shared lock info */
>  MYSQL *mysql; /* MySQL connection */
>  MYSQL_RES *stored_result;
> 
> ...
> 
> So, what I've done is to try to do the same, but:
> 
> class ha_federated_odbc: public handler
> {
>  THR_LOCK_DATA lock;      /* MySQL lock */
>  FEDERATED_ODBC_SHARE *share;    /* Shared lock info */
>  SQLHENV      *odbc_env_h;      // Handle ODBC environment
>  SQLHDBC      *odbc_dbh;     // Handle connection
>    SQLHSTMT     *odbc_stmt; // works like "stored_result" (?????)
> 
> So, the idea is I have a class stmt variable for situations where I need 
> a result set I iterate through such as within rnd_next/index_next, and 
> then for simple no-result calls (write queries, or things like 'set 
> autocommit....', I define a local stmt. Does this make sense?

Still can't see why you cannot just use SQLHENV (which is technically 
already a pointer) but the choice is yours - no real right or wrong here.

 > Is a
> SQLHSTMT where the stored result is?

yes, assuming SQLNumResultCols is > 0 else it has not been used or it 
was used for an insert/delete/update.

> 
>>> 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?
> 
> 
> Just trying to stick with how it's done with mysql client api in 
> federated... I suppose the other way I could do.
> 
>>
>>>   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.
> 
> 
> Yes.
> 
>>
>>>   {
>>>     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.
>>
> Yes, you are right! I tried what you suggested, and now it works.
> 
> Question: why did it work ok when this code was in "main" ? As soon as I 
> put it in it's own function, it bombed.

Move the code you move where the variable gets put on the stack and what 
it's initial value is (could have been anything in your case as 
automatic variables are not initialised).

valgrind should have spotted this for you - useful tool.


>>> }
>>>
>>> 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.
> 
> 
> I do this in the storage engine code - also, I disconnect in 
> ha_federated_odbc::close ;)
> 
>>
>>>   free(stmt);
>>> }
>>
>>
>> Martin
> 
> 
> Thank you very very very much!
> 
> Patrick
> 

No problem.

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]