Re: help with a simple ODBC program

Martin,

Thanks! I should have made a disclaimer that my sample code was ugly and 
not up to spec ;)

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? Is a 
SQLHSTMT where the stored result is?


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

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

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