Bug#439241: Retrieving results from stored functions does not seem to work

[Enrico Zini]

Package: libmyodbc
Version: 3.51.15r409-1+b1
Severity: normal

Hello,

thanks for maintaining libmyodbc.

I've been playing with MySQL and stored functions, and I can't seem to
retrieve the function result from ODBC; the attached example program
reproduces the problem for me.

It seems that SQLBindCol is ignored when executing queries like
"SELECT funcname(parms)": the bound result variable is not modified by
the query.

Note that calling the function from inside isql does produce the
expected result.


Ciao,

Enrico


-- System Information:
Debian Release: lenny/sid
  APT prefers testing
  APT policy: (500, 'testing')
Architecture: amd64 (x86_64)

Kernel: Linux 2.6.22-1-amd64 (SMP w/2 CPU cores)
Locale: LANG=it_IT.UTF-8, LC_CTYPE=it_IT.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages libmyodbc depends on:
ii  debconf [debconf-2.0]   1.5.14           Debian configuration management sy
ii  libc6                   2.6.1-1          GNU C Library: Shared libraries
ii  libltdl3                1.5.22-4         A system independent dlopen wrappe
ii  libmysqlclient15off     5.0.45-1         MySQL database client library
ii  odbcinst1debian1        2.2.11-14        Support library and helper program
ii  zlib1g                  1:1.2.3.3.dfsg-5 compression library - runtime

Versions of packages libmyodbc recommends:
ii  libiodbc2                    3.52.5-1+b1 iODBC Driver Manager
ii  unixodbc                     2.2.11-14   ODBC tools libraries

-- debconf information:
  libmyodbc/addtoodbc: false

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

#if __WORDSIZE == 64
#define TEST_SQL_C_SINT_TYPE long
#define TEST_SQL_C_UINT_TYPE unsigned long
#define TEST_SQL_C_SINT SQL_C_SBIGINT
#define TEST_SQL_C_UINT SQL_C_UBIGINT
#else
#define TEST_SQL_C_SINT_TYPE long
#define TEST_SQL_C_UINT_TYPE unsigned long
#define TEST_SQL_C_SINT SQL_C_SLONG
#define TEST_SQL_C_UINT SQL_C_ULONG
#endif


void check(SQLSMALLINT handletype, SQLHSTMT stm, int res)
{
	if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO))
	{
		static const int strsize = 200;
		char stat[10], msg[strsize];
		SQLINTEGER err;
		SQLSMALLINT mlen;
		SQLGetDiagRec(handletype, stm, 1, (unsigned char*)stat, &err, (unsigned char*)msg, strsize, &mlen);
		if (mlen > strsize) mlen = strsize;
		fprintf(stderr, "Error %d %s: %.*s\n", err, stat, mlen, msg);
		exit(1);
	}
}

void checkenv(SQLHENV stm, int res)
{
	check(SQL_HANDLE_ENV, stm, res);
}
void checkdbc(SQLHSTMT stm, int res)
{
	check(SQL_HANDLE_DBC, stm, res);
}
void checkstm(SQLHSTMT stm, int res)
{
	check(SQL_HANDLE_STMT, stm, res);
}

int main()
{
	SQLHENV dba_od_env;
	SQLHDBC od_conn;
	SQLHSTMT stm;
	TEST_SQL_C_SINT_TYPE foo = 42;

	SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &dba_od_env);
	checkenv(dba_od_env, SQLSetEnvAttr(dba_od_env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0));

	SQLAllocHandle(SQL_HANDLE_DBC, dba_od_env, &od_conn);

	printf("Connecting\n");
	checkdbc(od_conn, SQLConnect(od_conn, "test", SQL_NTS, "enrico", SQL_NTS, "", SQL_NTS));

	SQLAllocHandle(SQL_HANDLE_STMT, od_conn, &stm);

	printf("Creating function\n");
	checkstm(stm, SQLExecDirect(stm,
		"CREATE FUNCTION testfunc(val INTEGER) RETURNS INTEGER"
		" BEGIN"
		"  RETURN val + 1;"
		" END", SQL_NTS));

	printf("Querying function\n");
	checkstm(stm, SQLBindCol(stm, 1, TEST_SQL_C_SINT, &foo, sizeof(foo), 0));
	checkstm(stm, SQLExecDirect(stm, "SELECT testfunc(5)", SQL_NTS));

	if (SQLFetch(stm) == SQL_NO_DATA)
		fprintf(stderr, "No results for foo\n");

	printf("Foo is %ld, but it should be 6\n", foo);

	checkstm(stm, SQLCloseCursor(stm));

	printf("Dropping table\n");
	checkstm(stm, SQLExecDirect(stm, "DROP FUNCTION testfunc", SQL_NTS));

	return 0;
}