Curently the DBI only defines two trace flags:

  ALL - turn on all DBI and driver flags (not recommended)
  SQL - trace SQL statements executed (not yet implemented)

You might look at DBD::Mock. You can probably munge the DBD::Mock::StatementTrack objects into something useful:

For example:

my $dbh = DBI->connect( 'DBI:Mock:', '', '' ) || die "connect: $DBI::e
+rrstr";
# [ call stuff that uses $dbh ]
print join "\n", map { $_->to_string } ( @{ $dbh->{mock_all_history} }
+ );
[download]

Fast and dirty solution:

It probably wouldn't be that hard to programatically extract the SQL bits from a DBI->trace dump, so he doesn't have to find the needle amid the haystack. Below is an excerpt from a DBI->trace(1, $tracefile) dump I did the other day from Class::DBI code:

    <- connect= DBIx::ContextualFetch::db=HASH(0x573ae0)
 T  <- STORE('dbi_connect_closure' CODE(0x573b88))= 1 at DBI.pm line 6
+62
 T  <- prepare_cached('SELECT id, location, membership_level, im_servi
+ce, last_name, password, im_handle, url, registration_date, profile_u
+pdated, priv_email, email, email_bouncing_p, first_names
FROM   dashboard_user
WHERE  id = ?
')= ( DBIx::ContextualFetch::st=HASH(0x5674a4) ) [1 items] at DBI.pm l
+ine 391
 T  <- FETCH('Active')= '' at DBI.pm line 1124
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 49
    <- STORE('Taint' 0)= 1 at ContextualFetch.pm line 50
    <- execute(1)= 1 at ContextualFetch.pm line 51
 T  <- STORE('Taint' 1)= 1 at ContextualFetch.pm line 52
[download]

Depending on the specifics of your configuration, you might be able to get away with looping through the tracefile with a regex like (untested):

/^ T  <- prepare(_cached)?\('(.+?)'\)= \( DBIx::ContextualFetch::st=HASH\(/

... where the SQL would be stored in $2.

(Incidentally, it looks like my tainting is being turned off on every execute! I had never noticed that before ...)

Thanks for all your suggestions. It seems that the consensus is to go with option 1 i.e. hooking DBI (trace). A problem I have found with DBI_TRACE is that it is truncating the SQL, hence is not usable in this case.

jZed metioned the SQL flag bit in the DBI_TRACE level - something I was not aware of. Even if this is not available cross-platform, there is no reason why it could not be implemented for a given DBD - in my case DBD::Sybase.

I know that the SQL_TRACE environment variable does a similar job for Roguewave (object persistence layer). This has worked well for me diagnosing problems with said legacy app, which uses Roguewave dbtools. I will pursue this option with the sybperl list (which is used to support DBD::Sybase).

I haven't worked on implementing the specifics of the DBI_TRACE bits in DBD::Sybase, so that may not work for you.

There are a couple of Sybase-specific alternatives: You could use Ribo to capture incoming SQL requests, you could set up auditing on the server and extract the appropriate SQL requests, and you could also use things like the Sybase Replicator (the java light weight replication system included in ASE 12.5.x) to keep a remote table in sync.

Update Having re-read the original question I see that the "replicator" option is not at all useful here. However, the other two (and in particular Ribo) could be used fairly easily.

Michael

The script uses Class::DBI. Updates to individual columns consist of method calls. The whole idea behind Class::DBI is that it is an object persistence layer, and enables the developer to avoid writing SQL.

If the script were using plain vanilla DBI, it would contain SQL, but probably be at least twice as long.

--
I'm Not Just Another Perl Hacker