Page 1 of 2

MySQL Storedprocedures

Posted: 18.09.2006, 13:35
by Kite
Hi
I created a storedprocedure in MySQL 5.0.19 and if I test the procedure with my SQL manager i works fine but not from Delphi it tells me the parameters is not found

I can'd find anywhere to see what is needed in a stedprocedure

This is the code

ZStoredProc.StoredProcName := 'Newdocument';
ZStoredProc.ParamByName('CaseRef').AsString := Edit1.Text;
ZStoredProc.ParamByName('UserID').AsInteger := 12;
ZStoredProc.ExecProc;

can someone please assist me
:?
Thanx
Kite

Posted: 18.09.2006, 13:46
by dhongu
ZStoredProc is not implemented for mysql. If you wish to call stored procedure try to ZSQLProcessor.

[syntax="delphi"]
ZSQLProcessor1.Script.text:= 'Call Newdocument( :CaseRef , :UserID )';
ZSQLProcessor1.ParamCheck:=true;
ZSQLProcessor1.ParamByName('CaseRef').AsString := Edit1.Text;
ZSQLProcessor1.ParamByName('UserID').AsInteger := 12;
ZSQLProcessor1.Execute;
[/syntax]

Posted: 18.09.2006, 13:50
by Kite
Thanx I'll try this

Posted: 18.09.2006, 16:51
by zippo
It can be done just by ZQuery too:

Query1.sql.text := 'Call NewDocument("xx",12)';
Query1.Execute;

Posted: 18.09.2006, 21:19
by Kite
Thanx but how do I get the return value from the procedure?

Posted: 18.09.2006, 21:53
by Kite
Where can I find a lib that support Storedprocedures for MySQL in Delphi?

I needed to use the Storedprocedure of MySQL?

Please help

Posted: 18.09.2006, 23:04
by zippo
Procedures do not return values if I'm not wrong - functions do that.

To return a value from the function follow this example (syntax is aproximative):

Code: Select all

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`USR_MULTIPLY` $$
CREATE FUNCTION `USR_MULTIPLY`(X Integer, Y Integer) RETURNS Integer
    DETERMINISTIC
    COMMENT 'Just a test...'
Begin
  Declare RESULT Integer Default 0;
  Set RESULT = X * Y;
  Return RESULT;
End $$

DELIMITER ;
Now just call it in the Delphi application:

Code: Select all

Query.SQL.Text := 'Select USR_MULTIPLY(5,3)';
Query.Open;
The result set should be 15 and zou read it like this>

Code: Select all

MyResult := Query.Fields[0].AsInteger;

Hope it's clear and it helps you.

Posted: 14.11.2006, 08:53
by que
ZStoredProc is not implemented for mysql
:((((((
My stored procedure returns dataset... Is there any other way i can use it?
Or when sp support will be implemented?

Posted: 14.11.2006, 09:09
by zippo
Use a TZQuery - see above.

Posted: 14.11.2006, 09:21
by que
zippo
Procedures do not return values if I'm not wrong - functions do that.
Functions returns only scalar data types, but procedures returns data through OUT or INOUT parameters or returns datasets like SELECT operator, but i can not use 'call spMySuperSP(param);' in TZQuery, cause mysqlerror "procedure blablabla can't return dataset in given context"

Posted: 14.11.2006, 09:26
by que
example

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`spMySuperSP`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySuperSP`()
    READS SQL DATA
BEGIN
  select * from b;
END$$

DELIMITER ;

Posted: 14.11.2006, 09:35
by zippo
Hmmm.. true, but aren't functions usually called within the Call command, not via Select?

Posted: 14.11.2006, 09:50
by que
functions via select, procedures via call
like i wrote above: call spMySuperSP(param); :)

I'm writing megasecure app with all its logic on server side, so my app will communicate with db hrough sp calls not via select/insert/update an so on.
That's why i'm asking about full sp support, not only one way via ZScriptProcessor

Posted: 14.11.2006, 10:18
by zippo
What about triggers? This way you can post into a table and the server will process the data in a secure way.

Posted: 14.11.2006, 10:29
by que
using triggers is not best solution:
1) loss of flexibility, complicated process flow understanding and unhandy debugging
2) unsuitable for my problem - loading data, not storing :)