RE: [fw-general] Survey: Development environment for PHP/ZFW

[Camilo Usuga]

Hi!

1. Windows / OSX 10.5.6
2. Remote, Running on Ubuntu
3. Eclipse PDT / Netbeans Development
4. SVN
5. Testing is for sissies (PHPUnit when testing)



Camilo Usuga  | Team Leader | Soficol S.A.
Pbx: (574) 444 8555  | Cel: (57 300) 603 6727
Medellín - Colombia

-Original Message-
From: howard chen [mailto:howac...@gmail.com]
Sent: Saturday, May 02, 2009 12:14 PM
To: Zend Framework General
Subject: [fw-general] Survey: Development environment for PHP/ZFW

Please feel free to answer:

1. What OS you are using during development? Windows? Mac? Linux?
2. Do you edit source code on localhost or remote? i.e. is your
testing environment reside on localhost or remote?
3. What tool or IDE you are using? Ultraedit? Apanta? Eclipse PDT?
4. Which versioning system your are using? svn? git?
5. Do you use PHPUnit or other testing tools?

p.s. have a good day.

This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you are not the intended recipient you are notified that disclosing, copying, 
distributing or taking any action in reliance on the contents of this 
information is strictly prohibited.
www.soficol.com

This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you are not the intended recipient you are notified that disclosing, copying, 
distributing or taking any action in reliance on the contents of this 
information is strictly prohibited.
www.soficol.com

RE: [fw-general] RE: About massive inserts

[Camilo Usuga Ortiz]

Hi Bill,

 

I will check the solution you gave me, I think I was commiting the queries
per loop, and about the posgres version, I think that probably I executed
the command in a different server, I will take a look at that too!

 

Thanks a lot for your help!

 

Camilo

 

From: Bill Karwin [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 04, 2007 2:21 PM
To: fw-general@lists.zend.com
Subject: RE: [fw-general] RE: About massive inserts

 

>From what I can see in the PostgreSQL manual, multi-row INSERT syntax
works in 8.2 and is not supported in 8.1, which you said you're running.  

 

Compare docs for INSERT:

http://www.postgresql.org/docs/8.1/static/dml.html#DML-INSERT

http://www.postgresql.org/docs/8.2/static/dml-insert.html

 

Are you certain that when you ran the statement successfully against
Pgsql, it was running on an 8.1 server?

 

Another good practice is to use explicit transactions to insert your rows
as one commit.  Otherwise it implicitly creates a new transaction for each
statement and commits it, which is overhead multiplied by thousands if
you're executing a separate SQL statement per row you insert.  Here's some
code I just ran against my Pgsql server:

 

$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO foo (col1, col2, col3) VALUES (?, ?,
?)');
for ($i = 0; $i < 1000; $i++) {
$stmt->execute(array($i, $i, $i));
}
$db->commit();

 

Another thing to look into if you have many thousands of rows to insert.
There are usually other solutions to do bulk-loading that are much more
efficient than INSERT.  These are not standard SQL, but you may be able to
use them anyway.

 

For example, PostgreSQL has the COPY statement:

http://www.postgresql.org/docs/current/static/sql-copy.html

 

MySQL has the LOAD DATA INFILE statement:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

 

Regards,

Bill Karwin

 

  _  

From: Camilo Usuga Ortiz [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 04, 2007 10:35 AM
To: 'redphantm'; fw-general@lists.zend.com
Subject: RE: [fw-general] RE: About massive inserts

Hi,

Well, the problem is that we already tried the for and an insert per
record, but it works fine with less that 1000 records, after that the
server just died and stopped inserting, I agree with you on that we need
to keep the simplicity of the code, but I also think that it would be a
lot faster to create only 1 insert that several thousands of inserts, what
do you think?

Camilo


-Original Message-
From: redphantm [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 04, 2007 12:32 PM
To: fw-general@lists.zend.com
Subject: Re: [fw-general] RE: About massive inserts


That's odd. Well, if you "have" to make a connection for each then one big
one would be better. I would suggest creating a persistent connection, and
then inserting one by one for simplicities sake. 

Shekar C Reddy wrote:
>
> For individual inserts, what if the dB has to make a connection for each
> insert? Sending all inserts as a single string may need just one
> connection.
>
>
>
>
> On 10/4/07, redphantm <[EMAIL PROTECTED]> wrote:
>>
>>
>> I would just use a for loop. I believe that would keep the code simple,
>> and
>> simplicity I think is the main goal for the Zend Framework. I think the
>> phrase is "simplicity over regularity." I don't think you will even be
>> able
>> to notice the difference from one huge insert query and many single
>> insert
>> queries.
>>
>> Bill Karwin wrote:
>> >
>> > 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().
>> >
>> >
>> >
>> 

RE: [fw-general] RE: About massive inserts

[Camilo Usuga Ortiz]

Hi,

Well, the problem is that we already tried the for and an insert per
record, but it works fine with less that 1000 records, after that the
server just died and stopped inserting, I agree with you on that we need
to keep the simplicity of the code, but I also think that it would be a
lot faster to create only 1 insert that several thousands of inserts, what
do you think?

Camilo


-Original Message-
From: redphantm [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 04, 2007 12:32 PM
To: fw-general@lists.zend.com
Subject: Re: [fw-general] RE: About massive inserts


That's odd. Well, if you "have" to make a connection for each then one big
one would be better. I would suggest creating a persistent connection, and
then inserting one by one for simplicities sake.  

Shekar C Reddy wrote:
> 
> For individual inserts, what if the dB has to make a connection for each
> insert? Sending all inserts as a single string may need just one
> connection.
> 
> 
> 
> 
> On 10/4/07, redphantm <[EMAIL PROTECTED]> wrote:
>>
>>
>> I would just use a for loop. I believe that would keep the code simple,
>> and
>> simplicity I think is the main goal for the Zend Framework. I think the
>> phrase is "simplicity over regularity." I don't think you will even be
>> able
>> to notice the difference from one huge insert query and many single
>> insert
>> queries.
>>
>> Bill Karwin wrote:
>> >
>> > 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: fw-general@lists.zend.com
>> >   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
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>>
http://www.nabble.com/About-massive-inserts-tf4569375s16154.html#a13044752
>> Sent from the Zend Framework mailing list archive at Nabble.com.
>>
>>
> 
> 

-- 
View this message in context:
http://www.nabble.com/About-massive-inserts-tf4569375s16154.html#a13044871
Sent from the Zend Framework mailing list archive at Nabble.com.

RE: [fw-general] About massive inserts

[Camilo Usuga Ortiz]

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: fw-general@lists.zend.com
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: fw-general@lists.zend.com
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

 

 

[fw-general] About massive inserts

[Camilo Usuga Ortiz]

 

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