Hi Bill,
Thanks a lot for the Idea, I think the new function will help a lot, and as far as I know, should be simple to develop. The problem is that I'm following the same idea that you gave me, we are formatting the query in the same way, but when we execute the query, we get the following error: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "," LINE 2: ( 317, 44101, '2007-10-04 07:23:52' ), The query is executed when we print it and load it in our dbadmin tool, but not when executed through the $db->query method, Any ideas? Probably the prepare method is messing something? or what do you guys think? Thanks for the help! Camilo From: Bill Karwin [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 12:00 PM To: [email protected] Subject: RE: [fw-general] About massive inserts Right; the Adapter's insert() method accepts only one tuple in its associative array argument. If you are doing an extended insert for many rows, you should format the full SQL statement as a string, and run it with the Adapter's query() method. $sql = "INSERT INTO myTable ( id, field_1, date ) VALUES ( 301, 40971, '2007-10-04 04:00:19' ), ... ( 301, 40990, '2007-10-04 04:00:19' )"; $db->query($sql); Note that you can ignore the Statement object returned by this method in this case. The query() method is just a shortcut for prepare() and execute(). It doesn't have to be a SELECT query, it can be any other SQL statement type (provided the statement supports being prepared). In this case you have no need to execute this statement more than once, so you don't need to save the Statement object returned from query(). Perhaps there is a need for a new method insertMulti() or something like that, which would accept an array of associative arrays, and format the SQL appropriately. Feel free to submit a JIRA issue for this. Regards, Bill Karwin _____ From: Camilo Usuga Ortiz [mailto:[EMAIL PROTECTED] Sent: Thursday, October 04, 2007 7:52 AM To: [email protected] Subject: [fw-general] About massive inserts Hi All, We are currently developing an application where we need to insert a big amount of data (thousands of records), we are connecting to a PostgreSQL (8.1.6) database through PDO (pdo_pgsql ) the query looks pretty much like this: INSERT INTO myTable ( id, field_1, date ) VALUES ( 301, 40971, '2007-10-04 04:00:19' ), ( 301, 40972, '2007-10-04 04:00:19' ), ( 301, 40973, '2007-10-04 04:00:19' ), ( 301, 40974, '2007-10-04 04:00:19' ), .... .... .... .... ( 301, 40990, '2007-10-04 04:00:19' ); It works when we execute the query directly on the database, but when using the _db methods like query, insert or even fetchRow, we always get the same error: Syntax error: 7 ERROR: syntax error at or near "," LINE 1: ...ate ) VALUES ( 303, 41471, '2007-10-04 04:30:45' ), ( 303, 4... So the question is, what would be the best option to insert this information? Seems that the insert method does not support associative arrays to load all of the info there. Thanks a lot for the help! Camilo
