2

I want to find the exact SQL statements that are generated by a Perl ORM package such as Class::DBI. I am not looking for SQL generated for simple inserts and deletes, but for row modifications that result from slightly complicated object manipulations (inserting rows in a table which is a child of a row in a parent table, for example)

Is there some way to get it?

brian d foy
  • 129,424
  • 31
  • 207
  • 592
Gurunandan Bhat
  • 3,544
  • 3
  • 31
  • 43
  • Why and how would you like to get the SQL? If it's a one time thing, that's not so hard. If you want to do it continually inside the program and do fancy stuff with it, that's a bit harder. – brian d foy Jan 10 '10 at 12:24
  • Here is what I had in mind: I have to keep two databases in sync. Only one of these is an active database. The other just holds a copy of the active one. Since the connection between the two is not always guaranteed to be stable, I want to log all SQL statements generated on one database, collect them in a file, transfer the file over the network and run all statements in a single transaction on the passive database. Unfortunately, for reasons beyond my control, I have no access to replication tools or 2-phase commits which would ideally be the right way to address this problem. – Gurunandan Bhat Jan 10 '10 at 12:47

2 Answers2

6

Class::DBI uses DBI under the hood, so you can enable tracing of all SQL statements via an environment variable:

DBI_TRACE=3=dbi.log your-perl-script

Or inside of Perl, before executing any statements:

use DBI;
DBI->trace(2, 'dbi.log');

See http://metacpan.org/pod/DBI#TRACING

szabgab
  • 6,202
  • 11
  • 50
  • 64
rjh
  • 49,276
  • 4
  • 56
  • 63
2

Since you said "such as"…

You can set the environment variable DBIC_TRACE to 1 if you are using DBIx::Class (which has a Class::DBI compatibility layer).

Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • I think that DBI::Profile would be similarly useful, although I haven' t tried it and my general loathing of ORMs dissuades me from trying :) – brian d foy Jan 10 '10 at 12:26