0

I have a problem when creating a view:

In this view I select data from the selected database and join this with data from another database - this part works perfectly.

This is the part that does work:

SELECT tDb1.column1, tDb1.column2, tDb2.columnFromDb2
FROM tableFromDbInUse tDb1
JOIN db2.tableFromDb2 tDb2 ON tDb1.something = tDb2.something

I am looking for a way to exchange the second database without changing the statement in the view. Like "db2" would act as a prameter in a stored procedure @db2. @db2 would be an alias for a database name then that I can set somewhere else. In this way I could use the view in databases with different names but same structure, without amending all statements manually.

Is there any way to do this?

Here's the pseudocode that shall show what I try to achieve. I use @db2 for the "parameter" behaviour I am looking for:

SELECT tDb1.column1, tDb1.column2, tDb2.columnFromDb2
FROM tableFromDbInUse tDb1
JOIN @db2.tableFromDb2 tDb2 ON tDb1.something = tDb2.something
Fuzzzzel
  • 1,733
  • 3
  • 23
  • 37
  • are you referring to the same as this [question](http://stackoverflow.com/q/17951320/3664960) – davejal Jan 07 '16 at 12:11
  • or maybe [this](http://stackoverflow.com/q/10694601/3664960) one? – davejal Jan 07 '16 at 12:19
  • Not really, but thank you for looking it up. First that question refers to MS SQL and second it already works to query different databases within one view. Only I want to change one of the databases from outside the view. – Fuzzzzel Jan 07 '16 at 12:23
  • I'm still not sure what you are asking, but maybe the database experts at [dba](http://dba.stackexchange.com/) could help you. – davejal Jan 07 '16 at 12:30
  • I edited my question, maybe it was not so clear, what I am trying. – Fuzzzzel Jan 07 '16 at 12:33
  • Short answer: Can't be done. (Or at least not as elegantly as you propose.) – Rick James Jan 07 '16 at 18:03
  • Well, it does not habe to be elgant. My problem is that I have two systems with different database names. One for testing and one live. I cannot change the name on either system, because this will break other things. For this reason I am trying to find a way to implement the queries that will work on both systems without refactoring all queries each time I copy them to the other system. – Fuzzzzel Jan 07 '16 at 21:15

1 Answers1

0

You might need to check Prepared Statements

This might be the best solution for your case.

Edited

You will not avoid the part of adding additional code and this might have to be used in all your statements with the additional tweaking.

A stored procedure would do the trick i.e.

DELIMITER //
DROP PROCEDURE IF EXISTS `proc_switch` //
CREATE PROCEDURE `proc_switch`( IN `INquery` LONGTEXT, IN `INdb` VARCHAR(64) )
`bgn_lbl`:BEGIN
    PREPARE `stmt` FROM `INquery`;
    EXECUTE `stmt` USING `INdb`;
    DEALLOCATE PREPARE `stmt`;
END // 

DELIMITER ;

Get the results with a call, i.e.

CALL `proc_switch`( "SELECT tDb1.column1, tDb1.column2, tDb2.columnFromDb2 FROM tableFromDbInUse tDb1 JOIN ?.tableFromDb2 tDb2 ON tDb1.something = tDb2.something", 'db1' );
gmastro
  • 126
  • 3