mysql stored procedure

[nlok888]

Does any body know how to call mysql stored procedure?
And how about passing parameter to/from it?

Example stored procedure in mySql :

drop procedure if exists stp_Test1 ;

create procedure stp_Test1 (IN param1 int ,OUT param2 int )
BEGIN
select count(*) into param2 from table1 where id <= param1 ;
select * from table1 where id<=param1;
END

And calling sp in mysql look like this:
set @x = 0;

call stp_test1(3,@x);

select @x;

[Claire Shuttleworth]

Re: mysql stored procedure

Hi there

Did you ever find out how to do this?
I want to call a microsoft sql server stored procedure sending parameters...

thanks!!

[aburningflame]

Re: mysql stored procedure

dim conn as SQL::Connection
dim rs as SQL::ResultSet

dim cliID as N
cliID=convert_type(Request.Variables.cid,"N")

IF conn.Open("::Name::sqlconnstringname") then
flag=conn.Execute("CALL getClientSummary("+cliID+")")

etc etc etc

[Claire Shuttleworth]

Re: mysql stored procedure

Thanks!! Been really dumb! Is there a link to a more detailed help file here or something?

I want to execute a stored procedure called

InsertPeopleTask
sending a single parameter called scriptid which is in my grid...

[aburningflame]

Re: mysql stored procedure

When do you want to run this stored procedure?
WHen a record is inserted in the grid? deleted? updated?

[Claire Shuttleworth]

Re: mysql stored procedure

after inserted...
I guess I could actually put a trigger on the database as well!
Which would be the more effective way?

thanks!

[aburningflame]

Re: mysql stored procedure

Take a look at the onBeforeSQLCommandExecute and onAfterSQLCommandExecute events of the grid and the AfterInsertRecord.

AfterInsertRecord might be fine, but you may need to use onBeforeSQLCommandExecute and onAfterSQLCommandExecute

You can check e.successFlag in the onAfterSQLCommandExecute and based on that use e.cn.execute to execute your procedure.

You can probably use e.argsXML to check for script id
something like this

dim args as SQL::Argument
args.xml=e.argsXML

args.find("new_scriptid").data

Put a debug(1) in the event and use the bottom window to look at the e.argsXML variable etc etc

Im not quite sure exactly what you wanna do or if there is a better way of doing it - what does your stored procedure do exactly?

[Claire Shuttleworth]

Re: mysql stored procedure

Thanks for feedback!