Re: [fw-db] executing .sql file using zend_db

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
--
View this message in context: http://www.nabble.com/executing-.sql-file-using-zend_db-tp18569584p18570671.html
Sent from the Zend DB mailing list archive at Nabble.com.