Package: libmyodbc
Version: 3.51.15r409-1+b1
Severity: normal
Hello,
Here is another report about stored procedures.
The attached code should run a simple stored procedure with an input and
an output parameter, but instead it fails with this error message:
Connecting
Creating procedure
Error 1304 HY000: [MySQL][ODBC 3.51
Driver][mysqld-5.0.45-Debian_1-log]PROCEDURE testfunc already exists
I found the documentation on calling stored procedures from ODBC in
here: http://technet.microsoft.com/en-us/library/ms403294.aspx
Calling the same procedure from inside MySQL works fine:
mysql> set @a=0;
Query OK, 0 rows affected (0.00 sec)
mysql> call testfunc(5, @a);
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 5 |
+------+
1 row in set (0.01 sec)
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, foo1 = 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, OUT res INTEGER)"
" BEGIN"
" SET res=val;"
" 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, SQLBindParameter(stm, 2, SQL_PARAM_OUTPUT, TEST_SQL_C_SINT, SQL_INTEGER, 0, 0, &foo1, 0, 0));
checkstm(stm, SQLExecDirect(stm, "{call testfunc(?, ?)}", SQL_NTS));
/*
if (SQLFetch(stm) == SQL_NO_DATA)
fprintf(stderr, "No results for foo\n");
*/
printf("Foo is %ld, foo1 should be %ld but is %ld\n", foo, foo, foo1);
checkstm(stm, SQLCloseCursor(stm));
printf("Dropping table\n");
checkstm(stm, SQLExecDirect(stm, "DROP PROCEDURE testfunc", SQL_NTS));
return 0;
}
