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]

In reply to Extract SQL Statements by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.