Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    14

    Unanswered: Create mysql function from a schema to another

    Hi everyone!

    Basically I'm working on a database, and from this database I want to create a stored procedure or find a way to 'send' a function to a reportSchema database that I'm using for reports.

    Is there any way to do this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming the databases are hosted on the same server its easy enough. you can specify the database by prefixing the database name in front of any table/column references. eg:-
    update reports.mytable set reports.mytable.mycolumn = schema.myothertable.column, where reports.mytable.somecolumn = schema.myothertable.thatcolumn

    no reason why you can't insert into using the same specification

    whether it works on a remote database I dunno, I do know it works on a database on the same physical server
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    14
    I can create views in the reportSchema but not function.

    I have this function to create a new reportSchema:

    Code:
    DECLARE reportSchemaName varchar(100) DEFAULT OBTAIN_REPORT_SCHEMA_NAME();
    Code:
     CREATE 
    FUNCTION OBTAIN_REPORT_SCHEMA_NAME()
      RETURNS varchar(100) CHARSET utf8
      READS SQL DATA
    BEGIN
    	DECLARE REPORT_SCHEMA_IDENTIFIER varchar(8);
    	DECLARE currentSchema varchar(100);
    	DECLARE reportSchema varchar(100);
    	
    	
    	SET REPORT_SCHEMA_IDENTIFIER := 'reports_';
    	SET currentSchema := LOWER(DATABASE());
    	
    	IF LEFT(currentSchema, 8) = REPORT_SCHEMA_IDENTIFIER THEN
    		SET reportSchema := currentSchema;
    		
    	ELSE
    		SET reportSchema := concat(REPORT_SCHEMA_IDENTIFIER, currentSchema);
    		
    	END IF;
    	
    	RETURN reportSchema;
    END
    I thought about doing something like this :
    Code:
    set functionCode = concat('create or replace view  ', reportSchemaName, '.function ...'
    but then when I execute this:
    Code:
    CREATE PROCEDURE EXECUTE_QUERY(sqltxt BLOB)
      NO SQL
    BEGIN
    	SET @thesql = sqltxt;
    	
    	IF @thesql IS NOT NULL THEN
    		prepare stm from @thesql;
    		execute stm;
    		deallocate prepare stm;
    	END IF;
    	
    #SET @thesql = null;
    END
    When I do this: execute_query(functionCode), it doesn't work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •