CODEWITHSUNDEEP
mysqlstored-procedures
Arnold Pistorius
Arnold Pistorius

Reputation: 532

Changing current mysql database in a procedure?

For our system we are using multiple databases with the same structure. For example when we have 1000 customers, there will be 1000 databases. We've chosen to give each customers his own database, so we can delete all his data at once without any hassle.

Now I have to update the database structure several times a year. So I began to write a stored procedure which loops through all schemas. But I got stuck with executing a dynamic USE statement.

My code is as follows:

DECLARE V_SCHEMA VARCHAR(100);
SET V_SCHEMA = 'SomeSchemaName';
SET @QUERYSTRING = CONCAT('USE ', V_SCHEMA);
PREPARE S FROM @QUERYSTRING;
EXECUTE S;
DEALLOCATE PREPARE S;

When I execute this code I get an error which says Error Code: 1295. This command is not supported in the prepared statement protocol yet. So I assume that I cannot change the active database in a procedure.

I have searched the internet, but the only thing I found was creating a string of each alter query and prepare/execute/deallocate it. I hope there is a better solution for this. I could write a shell script that loops through the schemas and executes a SQL file on them, but I prefer a stored procedure that takes care of this.

Does anyone know how to make this work?

Thank you for your help!

EDIT: I use the latest stable version of MySQL 5.6

Upvotes: 2

Views: 4079

Answers (2)

Cari Sayler
Cari Sayler

Reputation: 11

If you put your structure changes into a stored procedure in a temporary schema, you can do this within a Workbench SQL window.

You can build your iteration script using a query against the information_schema, e.g. 

SELECT GROUP_CONCAT(CONCAT('USE ',schema_name,'; CALL tmp.Upgrade')
                    SEPARATOR ';\n') AS BldCode
FROM   information_schema.schemata
WHERE  schema_name NOT IN
       ('information_schema', 'performance_schema', 'mysql', 'sakila', 'world', 'tmp')

Since you cannot execute this as a prepared statement, you can copy the SQL result into a new SQL window, and run that.

Please note that the structure changes stored procedure would need to operate on the current schema, rather than specifying schemas.

Upvotes: 0

Devart
Devart

Reputation: 122032

If there are some known databases, then try to write a CASE.

Otherwise, do not execute USE statement using prepared statements; instead, build other statements (SELECT, INSERT, UPDATE, ...) with full name - <database name> + '.' + <object name>, and execute them using prepared statements.

Upvotes: 2

Related Questions