Creating databases using Zend_Db_Adapter

Question

I'm struggling with a part of my application where I am using Zend_Db_Adapter to create a database schema in mysql per client signup.

I have a database script that contains all the DDL to create the tables, I then read the contents of the file and then execute it using the Zend_Db_Adapter. Following the creation of the database I want to populate it with some data using the same adapter. However this always fails with an error such as:

2011-08-10T14:15:16+01:00 DEBUG (7): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'client_3.users' doesn't exist

Can someone see where in my code it's going slightly wrong? I've had the MySQL logging up to see any errors and there don't appear to be any, and the DDL script works fine when ran by hand.

$databaseName = 'client_' . $clientId;
$createDbStr = "DROP DATABASE IF EXISTS ".$databaseName."; CREATE DATABASE " . $databaseName.";";

            $dbAdapter->getConnection()->exec($createDbStr);

            //we now create the database adapter for the new database on the server
            //as we now populate the user data in the databaes.
            $newDbAdapter = Zend_Db::factory('Pdo_Mysql', array(
                'host' => 'localhost',
                'username' => 'appdbuser',
                'password' => 'appdbpassword',
                'dbname' => $databaseName
            ));


            //we now load the SQL Script that is used to create the database schema.
            $sqlScriptPath = APPLICATION_PATH . DIRECTORY_SEPARATOR . 'datamodel' . DIRECTORY_SEPARATOR . 'client_schema.sql';
            $fileUtils = new App_FileSystem_FileUtility();
            $sqlScript = "USE ".$databaseName.";";
            $sqlScript .= $fileUtils->getFileContents($sqlScriptPath);

            //debug sql script;
            App_Application_Log::getInstance()->log($sqlScript, Zend_Log::DEBUG);

            //we now create the schema
            $newDbAdapter->getConnection()->exec($sqlScript);


            $usersTable = new Application_Model_UsersTable($newDbAdapter);
            $newUserTableData = array(
                'username' => 'Administrator',
                'fullname' => $contactName',
                'email' => $email,
                'uuid' => App_Security_Uuid::getInstance()->getUuid(),
                'create_date' => new Zend_Db_Expr('now()'),                
            );

            $userId = $usersTable->insert($newUserTableData); //errors here

Is there a step that needs to be done from the running of the DDL script before I can start populating it with data?

Thanks,

Grant

I would try adding a CREATE TABLE command to your sqlScript, eg: $sqlScript = "USE ".$databaseName."; CREATE TABLE test (`id` int);"; to try to narrow down the problem. And I presume the spare ' at the end of 'fullname' => $contactName', has somehow got into your post, but isn't in your code? Presumably it wouldn't even run if it was there, but just checking. — ChrisA
– ChrisA, Commented Aug 10, 2011 at 13:50
I've done what you've suggested and same problem no tables created :(. The ' at the end of the 'fullname' => $contactName' is a post typo. Code works fine, well it executes ;) — Grant Collins
– Grant Collins, Commented Aug 10, 2011 at 14:18
Ok, lets just try : $newDbAdapter->getConnection()->exec("CREATE TABLE ".$databaseName.".test (`id` int);"; after you exec the create database. — ChrisA
– ChrisA, Commented Aug 10, 2011 at 14:53
That worked... I've got an idea on what I need to do now. Thanks — Grant Collins
– Grant Collins, Commented Aug 10, 2011 at 15:03

H Hatfield · Accepted Answer · 2011-08-10 14:17:39Z

1

I don't think $dbAdapter->getConnection()->exec($createDbStr); will run two SQL statements like you have. Split each into its own statement (the drop table if exists and the create table)

answered Aug 10, 2011 at 14:17

H Hatfield

8465 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grant Collins Over a year ago

the create database is fine, the main issue is the creation of database tables, which seems a bit strange.

Grant Collins · Accepted Answer · 2011-08-12 21:12:56Z

I have a solution to this problem now which I hope will help others.

What I did was ensure that the create_schema.sql script had the database names in the DDL

e.g.

CREATE TABLE `dbname`.`users`(
 userid INT,
 username VARCHAR(255)
)

I then, in my PHP code did a find replace of the using the str_replace function of the database name with with name of the clients database.

$sqlScriptPath = APPLICATION_PATH . DIRECTORY_SEPARATOR . 'datamodel' . DIRECTORY_SEPARATOR . 'client_schema.sql';
            $fileUtils = new App_FileSystem_FileUtility();            
            $sqlScript = str_replace('dbname', $databaseName, $fileUtils->getFileContents($sqlScriptPath));

            //debug sql script;
            App_Application_Log::getInstance()->log($sqlScript, Zend_Log::DEBUG);

            //we now create the schema
            $newDbAdapter->getConnection()->exec($sqlScript);

This then created all the tables and indexes as described in the SQL script.

Hope this helps.

Collectives™ on Stack Overflow

Creating databases using Zend_Db_Adapter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related