help with a simple ODBC program [MyODBC]

Prev: May Newsletter
Next: חודשים ניסיון בחינם-כדאי!

From: Patrick Galbraith on 1 May 2007 10:00

--------------000108090705050908070804
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000108090705050908070804
Content-Type: text/plain; x-mac-type="0"; x-mac-creator="0";
name="test_info.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="test_info.c"

#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);
}

--------------000108090705050908070804
Content-Type: text/plain; charset=us-ascii

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw
--------------000108090705050908070804--

From: Martin Evans on 1 May 2007 10:13

From: Jess Balint on 1 May 2007 10:27

On Tue, May 01, 2007 at 09:57:21AM -0400, Patrick Galbraith wrote:
> 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!

Looks like you wanted a pointer here:
SQLHSTMT stmt= malloc(sizeof(SQLHSTMT));

Jess

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw

From: Martin Evans on 1 May 2007 10:37

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/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw

From: Patrick Galbraith on 1 May 2007 10:50

Jess Balint wrote:

>On Tue, May 01, 2007 at 09:57:21AM -0400, Patrick Galbraith wrote:
>
>
>>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!
>>
>>
>
>Looks like you wanted a pointer here:
> SQLHSTMT stmt= malloc(sizeof(SQLHSTMT));
>
>Jess
>
>
>
Jess,

ack - I had switched to using "SQLHSTMT stmt" back to using a SQLSTMT
pointer just prior to sending, and forgot to change it back to:

"SQLHSTMT *stmt= malloc(sizeof(SQLHSTMT))";

thanks!

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/myodbc?unsub=myodbc(a)freebsd.csie.nctu.edu.tw

| Next | Last
Pages: 1 2
Prev: May Newsletter
Next: חודשים ניסיון בחינם-כדאי!