executing .sql file using zend_db

[tony stamp]

Hello, is it possible to load and execute .sql files using zend_db? The following is what i am trying to achieve:

$db->beginTransaction();
                                        try {
                                                $db->query('DROP TABLE ? LIMIT 1', $tableName);
                                                $db->query('LOAD DATA INFILE ?', $fullPathname);
                                                $db->commit();
                                        }catch (Exception $e){
                                                $db->rollBack();
                                                echo $e->getMessage();
                                        }
Is that possible? If not, how would i go about executing .sql files?
Thanks.

[Bill Karwin]

tony stamp wrote:

Hello, is it possible to load and execute .sql files using zend_db?

No, there is no support for running SQL scripts in Zend_Db.  I proposed it a long time ago, but it's a lot of work because every brand of database has their own syntax in scripts.  It must also be done with caution because once you enable execution of multi-queries, you increase the risk of SQL injection.
The feature request is currently here: http://framework.zend.com/issues/browse/ZF-1694

Currently the only way to execute a SQL script is to with the PHP system() function.  Run the mysql command-line tool with appropriate arguments.

  system("mysql --username=tony --password=xxx --database=dbname < script.sql");

tony stamp wrote:

$db->beginTransaction();
                                        try {
                                                $db->query('DROP TABLE ? LIMIT 1', $tableName);
                                                $db->query('LOAD DATA INFILE ?', $fullPathname);
                                                $db->commit();
                                        }catch (Exception $e){
                                                $db->rollBack();
                                                echo $e->getMessage();
                                        }
Is that possible? If not, how would i go about executing .sql files?

DROP TABLE cannot be rolled back.  It implicitly commits the current transaction.

LOAD DATA INFILE also had a bug for a while that it could not be rolled back.  This was reported against 5.0.6 and fixed in MySQL 5.0.26 and 5.1.12, except when using clustering.   In MySQL 4.1 you could roll back LOAD DATA INFILE.

You can't use a parameter for the table name in a DROP TABLE statement.   You can use a parameter only in place of a SQL value like 123 or 'string'.  To make a table name dynamic, you have to interpolate the table name into the query before running it.

MySQL does not support LOAD DATA INFILE as a prepared statement, but all queries run by Zend_Db are run as prepared statements.  You can run a non-prepared statement by calling $db->getConnection() and using the underlying connection resource object (e.g. PDO object or Mysqli object) for which $db is the adapter, but then you effectively aren't using Zend_Db anymore.

The API of the underlying connection varies.  For instance, if you used the PDO_MySQL adapter, the function you need is exec():

  $db->getConnection()->exec("LOAD DATA INFILE '$fullPathname' INTO TABLE $tableName");

Regards,
Bill Karwin

[Wojciech Naruniec]

Wiadomość napisana w dniu 2008-07-21, o godz. 17:08:41, przez Bill  
Karwin:

> Currently the only way to execute a SQL script is to with the PHP  
> system()
> function.  Run the mysql command-line tool with appropriate arguments.

There is another way to achieve this:

// $schema is content of *.sql file
$schema = explode(";\n", $schema);
$schema = array_map('trim',$schema);
$schema = array_filter($schema, 'strlen');

try {
     foreach ($schema as $sql) {
         $db->query($sql);
     }
} catch (Exception $e) {
     // [...]
}

Greetings,
Wojciech Naruniec

http://wojciech.naruniec.info/

[Bill Karwin]

Wojciech Naruniec wrote:

$schema = explode(";\n", $schema);
. . .
$db->query($sql);
. . .

It's a bit more complicated than that to support SQL scripts.
Try the following SQL script using your solution:

-- comment lines cannot be prepared as statements
-- mysql client tool builtin command cannot be prepared or executed by server
USE testdb;

-- multi-line statement
CREATE TABLE foo (
  string VARCHAR(100)
);

-- statement that is not supported as a prepared statement
LOAD DATA INFILE 'datafile.txt' INTO TABLE foo;

-- statement that is not terminated with a semicolon
DELIMITER //

-- statement contains semicolon without it being the statement terminator
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM foo;
END
//

Regards,
Bill Karwin

[tony stamp]

Thank you bill for the detailed response. Not only did you answer the question, you provided a lot of information to stop me exploring other pointless avenues.

This limits me to two options: either the shell or shell_exec functions, or db::exec. Is db::exec basically a wrapper for shell or shell_exec? I'm just wondering if i will be able to use either approaches if the host has disabled shell access.

[tony stamp ()]

I'm having some problems with the load data infile running through exec().

I have the following table structure:

CREATE TABLE `users` (
  `id` int(4) unsigned NOT NULL auto_increment,
  `name` varchar(200) collate latin1_general_ci default NULL,
  `surname` varchar(100) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

And i'm trying to load the data for the table using the approach mentioned: db->getConnection->exec('load data infile...');

The file i am trying to load contains this (some records removed):

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'James','Brown'),(2,'Percy','Sledge'),(3,'Al/Pete','Monsoon'),(4,'Harry','wish_man'),(5,'Steven','Bing'),(6,'Phil','Wern'),(7,'Pony','Man'),(8,'duck','duck'),(9,'Too Old For This','too_old_for_this');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

I am calling this as follows:

try{
        $result = $db->getConnection()->exec("LOAD DATA INFILE '$script' INTO TABLE users");
        print_r($result);
}catch (Exception $e){
        echo $e->getMessage();
}

(where $script is the full path to the above sql file containing the inserts). Here is my config:

$config = new Zend_Config(
            array(
                'database' => array(
                    'adapter' => 'PDO_MYSQL',
                    'params' => array(
                        'dbname' => 'MyShop',
                        'username' => 'root',
                        'password' => '',
                        'host' => 'localhost'
                    )
                )
);

...but what is happening is that instead of entering the 21 records that are contained in the file, only 9 records are entered, all like this:

id | name | surname
--------------------
0 |null|null  
0 |null|null
0 |null|null
etc
what's going wrong here? I've entered the inserts into phpmyadmin both as a copy/paste and also under the import tab to load the file, and both insert the data fine, yet when i run it through zend_db, it doesn't seem to insert the data at all.

[Bill Karwin]

tony stamp wrote:

I'm having some problems with the load data infile running through exec().
The file i am trying to load contains this:
[excerpt of SQL script shown]

LOAD DATA INFILE is for loading raw data in a textual format, for instance comma-separated values.  This command cannot execute SQL statements from a script.  Only the mysql command-line tool can do that.

Because you were talking about SQL scripts, I assumed you had a script that contained the LOAD DATA INFILE statement, and this statement in turn named another file in the appropriate textual format.

I admit even the MySQL documentation website does not clearly describe the format of the file that can be read by LOAD DATA INFILE.  It just says "loads data from a text file," so it's not surprising that you misunderstood what can be in that text file.

tony stamp wrote:

This limits me to two options: either the shell or shell_exec functions, or db::exec. Is db::exec basically a wrapper for shell or shell_exec? I'm just wondering if i will be able to use either approaches if the host has disabled shell access.

Right - since you have a SQL script, you must use the 'mysql' command-line tool.  If you don't have permission to run external commands from PHP using shell_exec() or system(), then you are out of luck.  You will have to run your script with another interface, such as phpmyadmin.

The db::exec() function I mentioned has no relationship to shell_exec().  db::exec runs a single SQL statement, without preparing it.  I thought you wanted to run LOAD DATA INFILE, and this statement is incompatible with being prepared, so it needs to be run another way.  But this is irrelevant anyway, because LOAD DATA INFILE is not what you need.

Regards,
Bill Karwin