Link to home
Start Free TrialLog in
Avatar of Francois Koutchouk
Francois KoutchoukFlag for United States of America

asked on

mySQL query with function in a different database?

- I have a database called REFERENCE.  It stores my functions and stored procs,
e,g,  procDoSomeStuff() with code like SELECT stuff FROM localDatabase;
- I have a multitude of databases containing data.
e.g. DATABASE1

I am in DATABASE1 issuing a long list of queries, some using functions.
e.g. CALL REFERENCE.procDoSomeStuff;

Problem is, REFERENCE.procDoSomeStuff() thinks that all its queries should be executed in the scope of REFERENCE.
It looks for REFERENCE.localDatabase rather than DATABASE1.localdatabase as I was expecting:  in other words, it looks like REFERENCE procedures are not really "loaded" into the DATABASE1, they spin off their commands into REFERENCE.  Weird.

So how do I "parametize" the database name?  How do I modify my SP and functions in REFERENCE to accept a database name I supply?  Or if it is easier, convince that all the code in a function should run in DATABASE1

I have lots of databases, but all use the same functions and stored procs, hence the need to share them in a common database I can call.

Many Thanks.
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image

Probably overkill for your needs, but if you're comfortable with C++ you can write your functions as UDF (user defined functions): http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html

That would have the advantage that your functions would work just like native MySQL functions and will be available for all databases.

HTH,
Dan
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Francois Koutchouk

ASKER

I got it to work by modifying my numerous stored procedures, using the technique:

	SET @myQuery = CONCAT[into a query];
	PREPARE stmt1 FROM @myQuery;
	EXECUTE stmt1;
	DEALLOCATE PREPARE stmt1;

Open in new window


Which leaves the functions.  They now all generate the following error:
Dynamic SQL is not allowed in stored function or trigger

Half way down that page, it confirms that I cannot do dynamic SQL in a function.

Any way around in SQL -- I cannot use an external program (like C++ as Dan suggested, thank you)?
Perhaps turn the functions into procedures, write to a temporary database1.table, then read from that in the continuing code?

And for my own education, why in the world are procedures supporting dynamic SQL and not the functions?
mysql was open-source, and volunteers worked on the features. if nobody asked for this feature, or nobody was assigned the task, it simply wasn't developed.
there may also be technical issues on this, as for example in MS SQL it's not possible neither to have dynamic sql in functions ...