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?
Do it just like you do with SQL.
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? )
Yes, this works fine for me at this moment. Thanks for clearing it up.
I was looking for something like this:
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?
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.