how can i call stored procedure from the function in mariadb?

Question

i want to get count of no.of rows present in table which i pass at runtime to a function.

i have created a procedure and function to execute dynamic queries. function will not allow dynamic query because i am calling procedure from function. that procedure having dynamic query.

///////procedure///////

CREATE PROCEDURE bizopsgolddev.`test1`(tbnm varchar(100))
begin
 declare sql_text varchar(200); 
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
                SELECT CONCAT(sql_text, ' is not valid');
        END;
        set sql_text=concat('select count(*) from ',tbnm);
        SET @SQL := sql_text;
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
end;

//////function//////

DROP FUNCTION IF EXISTS xyz;
CREATE FUNCTION `xyz`(tname varchar(100)) RETURNS int(11)
begin
declare val int;
call test1(tname);
return 1;
end;

if i execute this //select xyz('axpdc')// it should return rows count

can any one tell me how can i get count by passing table name to function(in mariadb only)

Not possible there isn't a workaround for this problem in MariaDB and MySQL .. Safety is the main reason why this isn't allowed — Raymond Nijland, Jan 18 '19 at 09:29
Possible duplicate of [How To have Dynamic SQL in MySQL Stored Procedure](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) — Raymond Nijland, Jan 18 '19 at 09:39
don't think so but you can try because this isn't a issue this is done because of safety issues because MySQL or MariaDB does not provide SQL functions to quote to protect dynamic SQL against SQL injections something like PostgreSQL has something like `('select count(*) from ', quote_identifier(tbnm));` would protect. — Raymond Nijland, Jan 18 '19 at 09:44

score 0 · Answer 1 · answered Aug 20 '19 at 08:42

As I understand the question, the solution would be a function that returns the row count of a table with it's name passed to the function as a parameter.

I think this could be done by querying the information_schema database in MariaDB. A function could look like this:

CREATE DEFINER = 'yourUsername'@'192.168.%'
FUNCTION testDataBase.fn_GetRowCount(tableName VARCHAR(128))
  RETURNS int(11)
BEGIN

  -- This could be a parameter if need it to be.
  DECLARE databaseName varchar(40) DEFAULT 'testDataBase'; 
  DECLARE result int DEFAULT -1;

  SELECT t.TABLE_ROWS INTO result
    FROM information_schema.TABLES t
    WHERE t.TABLE_NAME = tableName
    AND t.TABLE_SCHEMA = databaseName;

  RETURN result;
END

In order for this to work the user mentioned as the definer must have read privilege to the TABLES table in the information_schema database, otherwise you might get an error (tbh, I don't know if this is necessary).

There is a lot of useful information to be grabbed from the information_schema database.

how can i call stored procedure from the function in mariadb?

1 Answers1