Source language: Translate to:

MySQL & Stored Procedures

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL & Stored Procedures

Postby David de Argentina » Tue Apr 05, 2011 11:34 am

Hi all,

I created on a MySQL database this procedure:

Code: Select all
CREATE DEFINER=``@`localhost` PROCEDURE `reccount`(out param1 int)
begin
select count(*) into param1 from Alumnos;
end;

If i run this procedure directly on mysql or on an administrator, works fine.

I tried this on NeoBook:

Code: Select all
dbpOpenDatabase "mysql" "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=[#34]Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=servidor;User=ODBC;Option=3[#34]"
dbpClearParameters "mysql" "ClearVariables=Yes"
dbpAddParameter "mysql" "@param1" "Integer" "ReturnValue" "6" "0"
dbpExecProc "mysql" "reccount" ""

When i run this script, i receive this error message:

[MySQL][ODBC 5.1 Driver][mysqld-5.5.11]Incorrect number of arguments for PROCEDURE Servidor.reccount; expected 1, got 2

I don't know how to handle a "ReturnValue" variable, because i need get the "param1" from the procedure.

Any ideas ?

Thanks in advance,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Postby Neosoft Support » Wed Apr 06, 2011 10:06 am

Try using dbpSetParameter instead. The dbpAddParameter action was designed for an older version of MySQL. It may not be needed with the current version.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby David de Argentina » Wed Apr 06, 2011 10:33 am

Hi Dave,
Thanks for your answer.
dbpSetParameter does not work instead dbpAddParameter
using dbpSetParameter i receve something like this:

"object parameter badly defined. the information be incomplete or inconsistent"

And,
If it is work, how to set the type of data (in, out, inout,returnvariable) and what variable receives the value of the stored procedure ?

PS: Does anybody has a sample that works of stored procedure with MySQL that uses parameters ?

Thanks in advance,
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Postby asawyer13 » Wed Apr 06, 2011 12:02 pm

David,
Not sure if this is totally valid, but you have an OUT variable, and I think parameters are really used for IN variables to pass variables in.


I think you'll just get back a recordset with the reccount in it.

Not sure if this helps.

Alan
asawyer13
 
Posts: 123
Joined: Sun Aug 22, 2010 6:13 pm

Postby David de Argentina » Wed Apr 06, 2011 12:43 pm

Thanks Alan,

Problem Solved.

Yes, this is exactly you said in order to get values.

But on MySQL server 5.xx set input values is totally different.

We don't need set parameter values (using dbpSetParameter nor dbAddParameter).

We can do it using

dbpExecProc "ID_Database" "ProcName(param1,param2,etc)" "ResultTable"

The rest is easy.

Thanks again !
David de Argentina
User avatar
David de Argentina
 
Posts: 1562
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina


Return to NeoBookDBPro

Who is online

Users browsing this forum: Bing [Bot] and 1 guest