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