Francois Koutchouk
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.
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(
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it to work by modifying my numerous stored procedures, using the technique:
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?
SET @myQuery = CONCAT[into a query];
PREPARE stmt1 FROM @myQuery;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
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 ...
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 ...
That would have the advantage that your functions would work just like native MySQL functions and will be available for all databases.
HTH,
Dan