Extract SQL Statements

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi There
I want to extract just the SQL statements from the DBI trace log, but my script/regex doesn't seem to work so well... any ideas much appreciated>

my $data = qq| T  <- prepare_cached('SELECT trip_id, rate, expires
FROM   trip_exchange_rates
WHERE  trip_id = ?
')= ( DBIx::ContextualFetch::st=HASH(0x9371f4) ) [1 items] at DBI.pm l
+ine 398
 T  <- FETCH('Active')= '' at DBI.pm line 1139
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51
    <- execute('183207')= '0E0' at ContextualFetch.pm line 52
 T  <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53
 T  <- FETCH('NAME_lc')= [ 'trip_id' 'rate' 'expires' ] at DBI.pm line
+ 1140
 T  <- bind_columns(SCALAR(0x40f0128), SCALAR(0x40f01f4), ...)= 1 at D
+BI.pm line 1140
 T  <- fetchrow_arrayref= undef at ContextualFetch.pm line 60
 T  <- FETCH('Active')= 1 at DBI.pm line 326
 T  <- ping= 1 at DBI.pm line 326
 T  <- prepare_cached('INSERT INTO trip_exchange_rates (rate, currency
+, trip_id, expires)
VALUES (?, ?, ?, ?)
')= DBIx::ContextualFetch::st=HASH(0x40ec1c4) at DBI.pm line 398
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51
    <- execute('0.638315', 'USD', ...)= 1 at ContextualFetch.pm line 5
+2
 T  <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53
 T  <- FETCH('Active')= 1 at DBI.pm line 326
 T  <- ping= 1 at DBI.pm line 326
 T  <- prepare_cached('SELECT trip_id, engine
FROM   trip_engine
WHERE  trip_id = ?
')= ( DBIx::ContextualFetch::st=HASH(0x40efc18) ) [1 items] at DBI.pm 
+line 398
 T  <- FETCH('Active')= '' at DBI.pm line 1139
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51
    <- execute('183207')= '0E0' at ContextualFetch.pm line 52
 T  <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53
 T  <- FETCH('NAME_lc')= [ 'trip_id' 'engine' ] at DBI.pm line 1140
 T  <- bind_columns(SCALAR(0x40f602c), SCALAR(0x40f6050))= 1 at DBI.pm
+ line 1140
 T  <- fetchrow_arrayref= undef at ContextualFetch.pm line 60
 T  <- FETCH('Active')= 1 at DBI.pm line 326
 T  <- ping= 1 at DBI.pm line 326
 T  <- prepare_cached('INSERT INTO trip_engine (trip_id, engine)
VALUES (?, ?)
')= DBIx::ContextualFetch::st=HASH(0x40f5d44) at DBI.pm line 398
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 a|;
while($data =~ /prepare_cached\('(.+?)'\)= DBIx/gs) {
    print "(--> $1 <--\n)";
}
[download]

Results

(--> SELECT trip_id, rate, expires
FROM   trip_exchange_rates
WHERE  trip_id = ?
')= ( DBIx::ContextualFetch::st=HASH(0x9371f4) ) [1 items] at DBI.pm l
+ine 398
 T  <- FETCH('Active')= '' at DBI.pm line 1139
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51
    <- execute('183207')= '0E0' at ContextualFetch.pm line 52
 T  <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53
 T  <- FETCH('NAME_lc')= [ 'trip_id' 'rate' 'expires' ] at DBI.pm line
+ 1140
 T  <- bind_columns(SCALAR(0x40f0128), SCALAR(0x40f01f4), ...)= 1 at D
+BI.pm line 1140
 T  <- fetchrow_arrayref= undef at ContextualFetch.pm line 60
 T  <- FETCH('Active')= 1 at DBI.pm line 326
 T  <- ping= 1 at DBI.pm line 326
 T  <- prepare_cached('INSERT INTO trip_exchange_rates (rate, currency
+, trip_id, expires)
VALUES (?, ?, ?, ?)
 <--
)(--> SELECT trip_id, engine
FROM   trip_engine
WHERE  trip_id = ?
')= ( DBIx::ContextualFetch::st=HASH(0x40efc18) ) [1 items] at DBI.pm 
+line 398
 T  <- FETCH('Active')= '' at DBI.pm line 1139
 T  <- FETCH('Taint')= 1 at ContextualFetch.pm line 50
    <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51
    <- execute('183207')= '0E0' at ContextualFetch.pm line 52
 T  <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53
 T  <- FETCH('NAME_lc')= [ 'trip_id' 'engine' ] at DBI.pm line 1140
 T  <- bind_columns(SCALAR(0x40f602c), SCALAR(0x40f6050))= 1 at DBI.pm
+ line 1140
 T  <- fetchrow_arrayref= undef at ContextualFetch.pm line 60
 T  <- FETCH('Active')= 1 at DBI.pm line 326
 T  <- ping= 1 at DBI.pm line 326
 T  <- prepare_cached('INSERT INTO trip_engine (trip_id, engine)
VALUES (?, ?)
 <--
)
[download]

Comment on Extract SQL Statements Select or Download Code

Replies are listed 'Best First'.
Re: Extract SQL Statements by Bloodnok (Vicar) on Oct 08, 2009 at 09:34 UTC
As the age old question goes: what have you tried thus far ? I'd be surprised if there isn't something out there already - but I'm afraid I don't (yet:-) know about it. Having said/asked that, use warnings; use strict; use Data::Dumper; while (<DATA>) { if (/prepare_cached$'/ .. /^'$/) { s/.prepare_cached$'//; s/^'$.//; warn $_; } } __DATA__ my $data = qq\| T <- prepare_cached('SELECT trip_id, rate, expires FROM trip_exchange_rates WHERE trip_id = ? ')= ( DBIx::ContextualFetch::st=HASH(0x9371f4) ) [1 items] at DBI.pm l +ine 398 T <- FETCH('Active')= '' at DBI.pm line 1139 T <- FETCH('Taint')= 1 at ContextualFetch.pm line 50 <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51 <- execute('183207')= '0E0' at ContextualFetch.pm line 52 T <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53 T <- FETCH('NAME_lc')= [ 'trip_id' 'rate' 'expires' ] at DBI.pm line + 1140 T <- bind_columns(SCALAR(0x40f0128), SCALAR(0x40f01f4), ...)= 1 at D +BI.pm line 1140 T <- fetchrow_arrayref= undef at ContextualFetch.pm line 60 T <- FETCH('Active')= 1 at DBI.pm line 326 T <- ping= 1 at DBI.pm line 326 T <- prepare_cached('INSERT INTO trip_exchange_rates (rate, currency +, trip_id, expires) VALUES (?, ?, ?, ?) ')= DBIx::ContextualFetch::st=HASH(0x40ec1c4) at DBI.pm line 398 T <- FETCH('Taint')= 1 at ContextualFetch.pm line 50 <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51 <- execute('0.638315', 'USD', ...)= 1 at ContextualFetch.pm line 5 +2 T <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53 T <- FETCH('Active')= 1 at DBI.pm line 326 T <- ping= 1 at DBI.pm line 326 T <- prepare_cached('SELECT trip_id, engine FROM trip_engine WHERE trip_id = ? ')= ( DBIx::ContextualFetch::st=HASH(0x40efc18) ) [1 items] at DBI.pm +line 398 T <- FETCH('Active')= '' at DBI.pm line 1139 T <- FETCH('Taint')= 1 at ContextualFetch.pm line 50 <- STORE('Taint', 0)= 1 at ContextualFetch.pm line 51 <- execute('183207')= '0E0' at ContextualFetch.pm line 52 T <- STORE('Taint', 1)= 1 at ContextualFetch.pm line 53 T <- FETCH('NAME_lc')= [ 'trip_id' 'engine' ] at DBI.pm line 1140 T <- bind_columns(SCALAR(0x40f602c), SCALAR(0x40f6050))= 1 at DBI.pm + line 1140 T <- fetchrow_arrayref= undef at ContextualFetch.pm line 60 T <- FETCH('Active')= 1 at DBI.pm line 326 T <- ping= 1 at DBI.pm line 326 T <- prepare_cached('INSERT INTO trip_engine (trip_id, engine) VALUES (?, ?) ')= DBIx::ContextualFetch::st=HASH(0x40f5d44) at DBI.pm line 398 T <- FETCH('Taint')= 1 at ContextualFetch.pm line 50 <- STORE('Taint', 0)= 1 a\|; while($data =~ /prepare_cached$'(.+?)'$= DBIx/gs) { print "(--> $1 <--\n)"; } [download] gives `$ perl tst.pl SELECT trip_id, rate, expires FROM trip_exchange_rates WHERE trip_id = ? INSERT INTO trip_exchange_rates (rate, currency, trip_id, expires) VALUES (?, ?, ?, ?) SELECT trip_id, engine FROM trip_engine WHERE trip_id = ? INSERT INTO trip_engine (trip_id, engine) VALUES (?, ?)` [download] How does that compare to your effort(s) ? Update: Added example A user level that continues to overstate my experience :-))	[reply] [d/l] [select]
Re: Extract SQL Statements by mje (Curate) on Oct 08, 2009 at 13:00 UTC
Perhaps http://search.cpan.org/~mjevans/DBIx-Log4perl-0.16/lib/DBIx/Log4perl.pm will do that for you when you elect to only log SQL.	[reply]
Re^2: Extract SQL Statements by mje (Curate) on Oct 08, 2009 at 13:06 UTC
Sorry to followup on myself here but I think there is a callback mechanism in DBI now which you could use to trap calls to prepare and do. I believe these would also catch selectall_xxx etc as well.	[reply]