How to call MySQL Stored Procedures or UDF's

[tvp]

Is there a way of calling MySQL Stored Procedures or User-defined functions thru Squirrel along some input and output parameters? Right now I can only think of creating row-level triggers which call those stored procedures/UDF.

Maybe I am missing something. mysql_query is only used to run queries, right?

[KAKAN]

Do it just like you do with SQL.

Code Select Expand

local sql = @"CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);
 
    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;
 
 RETURN (lvl);
END
"; //The example is at:- http://www.mysqltutorial.org/mysql-stored-function/
mysql_query( yourdb, sql );
local querystring = @"SELECT
    customerName, CustomerLevel(creditLimit)
FROM
    customers
ORDER BY customerName;";
mysql_query( yourdb, querystring  );
It should work, now get your results using mysql_fetch_*
( I think that was your question, right? )

[tvp]

Yes, this works fine for me at this moment. Thanks for clearing it up.

I was looking for something like this:

Code Select Expand

mysql_query( yourdb, "call foo('parm', @a, @b, @c)");
Is it possible for me to pass the "call" command as a parameter within mysql_query, or it always needs to be a DML statement?

[KAKAN]

As long as it doesn't returns anything, you can use it, that's because I think the mysql_fetch only works with DML statements.
You can 'call' any function since the statement is executed in your mysql server, i.e, it depends on your mysql-server version. Anything done using 'mysql_query' is done (mysql)server side.