MySQL Storedprocedures

Kite · Post by **Kite** » 18.09.2006, 13:35

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

dhongu · Post by **dhongu** » 18.09.2006, 13:46

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]

Kite · Post by **Kite** » 18.09.2006, 13:50

Thanx I'll try this

zippo · Post by **zippo** » 18.09.2006, 16:51

It can be done just by ZQuery too:

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

Kite · Post by **Kite** » 18.09.2006, 21:19

Thanx but how do I get the return value from the procedure?

Kite · Post by **Kite** » 18.09.2006, 21:53

Where can I find a lib that support Storedprocedures for MySQL in Delphi?

I needed to use the Storedprocedure of MySQL?

Please help

zippo · Post by **zippo** » 18.09.2006, 23:04

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.

que · Post by **que** » 14.11.2006, 08:53

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?

zippo · Post by **zippo** » 14.11.2006, 09:09

Use a TZQuery - see above.

que · Post by **que** » 14.11.2006, 09:21

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"

que · Post by **que** » 14.11.2006, 09:26

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 ;

zippo · Post by **zippo** » 14.11.2006, 09:35

Hmmm.. true, but aren't functions usually called within the Call command, not via Select?

que · Post by **que** » 14.11.2006, 09:50

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

zippo · Post by **zippo** » 14.11.2006, 10:18

What about triggers? This way you can post into a table and the server will process the data in a secure way.

que · Post by **que** » 14.11.2006, 10:29

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