Bug#439293: A work-around

[Enrico Zini]

Hello,

a work-around I found is this:

 1. if the procedure has only one out/inout parameter, implement it as a
    function
 2. if the procedure has more than one out/inout parameter, return them
    as a result set using a select statement.

For case 1, see the last code I posted in #439241.

For case 2, see the attached code.


Ciao,

Enrico

-- 
GPG key: 1024D/797EBFAB 2000-12-05 Enrico Zini <[EMAIL PROTECTED]>

#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, foo1 = 0, foo2 = 0;

	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 procedure\n");
	checkstm(stm, SQLExecDirect(stm,
		"CREATE PROCEDURE testfunc(val INTEGER)"
		" BEGIN"
		"  SELECT val+1, val+2;"
		" END", SQL_NTS));
	checkstm(stm, SQLCloseCursor(stm));

	printf("Querying procedure\n");
	checkstm(stm, SQLBindParameter(stm, 1, SQL_PARAM_INPUT, TEST_SQL_C_SINT, SQL_INTEGER, 0, 0, &foo, 0, 0));
	checkstm(stm, SQLBindCol(stm, 1, TEST_SQL_C_SINT, &foo1, sizeof(foo1), 0));
	checkstm(stm, SQLBindCol(stm, 2, TEST_SQL_C_SINT, &foo2, sizeof(foo2), 0));
	checkstm(stm, SQLExecDirect(stm, "{call testfunc(?)}", SQL_NTS));

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

	/* I'm not yet sure why I should call this */
	checkstm(stm, SQLMoreResults(stm));

	printf("Foo is %ld, foo1 is %ld, foo2 is %ld\n", foo, foo1, foo2);

	checkstm(stm, SQLCloseCursor(stm));

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

	return 0;
}

signature.asc
Description: Digital signature