You should use prepared statements the way they are supposed to be used.
Just prepare a single INSERT statement and execute it against different
sets of values. I'd bet a fair amount of money that that wouldn't hit
your performance as bad as you might think.
Gerard
[resent, I didn't reply to list accidentally]
umpirsky wrote:
Hi zf gurus.
I have large amount of data which I'm inserting into MySQL db. That is array
of urls and I'm iterating and manually hammering bulk insert qury for better
performances.
So, Im running query with
$zdb->query('INSERT INTO `Content` (`FeedId`, `ContentValue`) VALUES (..),
(..)');
This works perfect on my Win XP maxhine, but on Cent OS server I get:
exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY093]:
Invalid parameter number: no parameters were bound' in
/var/www/ct1097024.mywebfilter.de/include/library/Zend/Db/Statement/Pdo.php:238
Stack trace:
#0
/var/www/ct1097024.mywebfilter.de/include/library/Zend/Db/Statement.php(283):
Zend_Db_Statement_Pdo->_execute(Array)
#1
/var/www/ct1097024.mywebfilter.de/include/library/Zend/Db/Adapter/Abstract.php(467):
Zend_Db_Statement->execute(Array)
#2
/var/www/ct1097024.mywebfilter.de/include/library/Zend/Db/Adapter/Pdo/Abstract.php(235):
Zend_Db_Adapter_Abstract->query('INSERT INTO `Co...', Array)
#3
/var/www/ct1097024.mywebfilter.de/include/application/models/Content.php(89):
Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `Co...')
#4 /var/www/ct1097024.mywebfilter.de/include/library/Umpirsky/Feed.php(66):
Default_Model_Content->insertBulkExecute()
#5
/var/www/ct1097024.mywebfilter.de/include/library/Umpirsky/Feed/Phishtank.php(45):
Umpirsky_Feed->import(Array, true)
#6
/var/www/ct1097024.mywebfilter.de/include/library/Umpirsky/Feed/Abstract.php(50):
Umpirsky_Feed_Phishtank->sync(Array)
#7 /var/www/ct1097024.mywebfilter.de/include/crons/feed.php(66):
Umpirsky_Feed_Abstract->update()
#8 {main}
Strange think is also that I have several sorts of feeds and it works for
some other which have only domain names or md5 URLs, so I assume thats some
invalid chars problem. I alse prepare query like this:
$this->insertValues .= '(' . $feedId . ',' .
$this->getAdapter()->quote($contentValue) . '),';
for each link.
Also I tries to dump executed query on failure:
INSERT INTO `Content` (`FeedId`, `ContentValue`) VALUES
(1,'http://211.86.85.166/new/skin/tb/readme.html'),(1,'http://www.octense.de//files/tmp_/tinymce/jscripts/tiny_mce/www.irs.gov/refund/0,,id=181665,00.html'),
... <damn boring long values here> ...
(1,'http://209.50.252.96/camera/beta4.html?forward.to.friends.login:http://login.myspace.com/index.cfm?fuseaction')
Query is so long that Notepad++ cannot completely display it, it contains
8681 URLs.
Any idea?
Regards,
Saša Stamenković.
