stored procedures with out parameters

stored procedures with out parameters

am 27.05.2006 08:33:59 von Nagesh Sharvari-A21337

------_=_NextPart_001_01C68157.8B49393E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello,
I am using myodbc with unixODBC. I have downloaded myodbc for
solaris.
now, I am facing problems executing stored procedures with out
parameters.=20
I am using the example shown in stored procedure section of MySQL
reference manual :=20
=20
CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM t;

END;

my C code to access stored procedure:

strcpy(stmt, "call simpleproc(?)") ;
rc =3D SQLPrepare(hstmt,stmt,SQL_NTS); =20

=20
and binding an integer parameter, id to the statment :
=20
=20
SQLINTEGER id;
rc=3D SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &id, 0, NULL);
=20
rc=3DSQLExecute(hstmt);
if ((rc !=3D SQL_SUCCESS) && (rc !=3D SQL_SUCCESS_WITH_INFO))
{
printf("Error in call to stored procedure %d\n",rc);
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt,1, stat,&err,msg,100,&mlen);
printf("%s (%d)\n State : %s \n",msg,err, stat);
}
=20
=20
=20
When I compile and run this program, I get the following error.
=20
Connected !
Error in call to stored procedure -1
[MySQL][ODBC 3.51 Driver][mysqld-5.0.19-standard]OUT or INOUT argument 1
for routine test.simplepro (1414)
State : HYT00

=20
Initially I thought State: HYT00, is something related to timeout. so
changed the statement attribute and changed the timeout period. but that
also doesn't help
I even tried the "?=3Dcall simpleproc()" but this gives syntax error.
=20
I spoke to unixODBC people also, they are saying, the problem is in
myodbc.
so please help me.
=20
thanks and regards
sharari

=20


------_=_NextPart_001_01C68157.8B49393E--

RE: stored procedures with out parameters

am 29.05.2006 11:36:00 von Nagesh Sharvari-A21337

------_=_NextPart_001_01C68303.4CF3340F
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hey,=20
I was searching in the net for a solution for this problem and found
this link :
http://archives.neohapsis.com/archives/mysql/2004-q3/1719.ht ml
It says, my-ODBC 3.51 does not support stored procedures yet. Is this
the reason, my code is failing??=20
If so, is there a solution for this? or should I wait for 3.53 to be
released?
=20
When is myODBC 3.53 getting released? It seems, this discussion happened
in 2004 July. and yet I couldn't find MyODBC 3.53.=20
regards

________________________________

From: Nagesh Sharvari-A21337=20
Sent: Saturday, May 27, 2006 12:04 PM
To: 'myodbc@lists.mysql.com'
Subject: stored procedures with out parameters


Hello,
I am using myodbc with unixODBC. I have downloaded myodbc for
solaris.
now, I am facing problems executing stored procedures with out
parameters.=20
I am using the example shown in stored procedure section of MySQL
reference manual :=20
=20
CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM t;

END;

my C code to access stored procedure:

strcpy(stmt, "call simpleproc(?)") ;
rc =3D SQLPrepare(hstmt,stmt,SQL_NTS); =20

=20
and binding an integer parameter, id to the statment :
=20
=20
SQLINTEGER id;
rc=3D SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &id, 0, NULL);
=20
rc=3DSQLExecute(hstmt);
if ((rc !=3D SQL_SUCCESS) && (rc !=3D SQL_SUCCESS_WITH_INFO))
{
printf("Error in call to stored procedure %d\n",rc);
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt,1, stat,&err,msg,100,&mlen);
printf("%s (%d)\n State : %s \n",msg,err, stat);
}
=20
=20
=20
When I compile and run this program, I get the following error.
=20
Connected !
Error in call to stored procedure -1
[MySQL][ODBC 3.51 Driver][mysqld-5.0.19-standard]OUT or INOUT argument 1
for routine test.simplepro (1414)
State : HYT00

=20
Initially I thought State: HYT00, is something related to timeout. so
changed the statement attribute and changed the timeout period. but that
also doesn't help
I even tried the "?=3Dcall simpleproc()" but this gives syntax error.
=20
I spoke to unixODBC people also, they are saying, the problem is in
myodbc.
so please help me.
=20
thanks and regards
sharari

=20


------_=_NextPart_001_01C68303.4CF3340F--

Re: stored procedures with out parameters

am 30.05.2006 01:12:40 von Daniel Kasak

Nagesh Sharvari-A21337 wrote:

> Hey,
> I was searching in the net for a solution for this problem and found
> this link :
> http://archives.neohapsis.com/archives/mysql/2004-q3/1719.ht ml
> It says, my-ODBC 3.51 does not support stored procedures yet. Is this
> the reason, my code is failing??
>

The very latest version of MyODBC-3.51.x which was posted here a while
ago ( direct link:
ftp://ftp.mysql.com/pub/mysql/hidden/connectors/odbc/mysql-c onnector-odbc-3.51.13-win32.msi
) does actually allow you to activate a stored procedure from MyODBC. Be
warned, however, that it was remarkably unstable on my system, and
what's worse, it was remarkably difficult to uninstall and replace with
a previous version. I was getting MS Access crashes every 30 seconds or
so, no matter what I did. It might be more stable with other
applications. For those wondering, yes I reported all issues I had,
submitted crash logs, SQL logs, etc. I've been a good beta tester, at
least when I can find something beta to test.

Reading back over your original post, it seems you're using MyODBC with
unixODBC. I don't think there are Linux / Unix binaries for 3.51.13 yet.
If you can track down the source to MyODBC-3.51.13, you might be able to
get somewhere further.

> If so, is there a solution for this? or should I wait for 3.53 to be
> released?
>

Yes. The consensus is that we wait. We wait until hell freezes over, we
wait until the cows come home, we wait until George Dubya finds WOMD in
Iraq, and we wait until a meadow of four-leaf clovers covers Antarctica.

> When is myODBC 3.53 getting released? It seems, this discussion happened
> in 2004 July. and yet I couldn't find MyODBC 3.53.
> regards
>

That's right. There has been talk of a release for almost half a decade
now. In the meantime, I'm busy porting stuff to Postgres, which has
client libraries that are being actively developed. Seriously, they
release drivers! Lord knows how, but they do it.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org