How do I use Perl and Regular Expressions to convert a SQL document to ColdFusion script?

I need to convert a document of SQL statements to a ColdFusion document. I only have a little experience with Regular Expressions and I am Perl super-newb (I just taught myself the basics of it yesterday so I could accomplish this task).

I am attempting to match and replace a pattern with a script written in Perl, saved as a Filter in BBEdit, that I am running on an open document. The good news is that it mostly works, but the bad news is that it doesn't completely work. I am pretty sure it has to do with my limited understanding of matching across multiple lines and parsing the entire document in paragraphs.

I am given a SQL document that looks something like this (most statements are on their own line (i.e. paragraph) but not all):

---

DELETE FROM example_db.example_tbl;

INSERT INTO example_db.example_tbl (
example_id, example_name
)
(
SELECT 
example_id, example_name
FROM example_2_db.example_tbl ORDER BY example_id
);

INSERT INTO example_db.example_tbl
(SELECT * FROM example_2_db.example_tbl ORDER BY example_id);

UPDATE example_db.example_tbl, example_2_db.example_sub_types_tbl, example_2_db.example_tbl SET example_db.example_tbl.example_sub_type_label=example_2_db.example_sub_types_tbl.example_sub_type_label WHERE example_2_db.example_sub_types_tbl.example_sub_type_id = example_2_db.example_tbl.example_sub_type_id AND example_2_db.example_tbl.example_id=example_db.example_tbl.example_id;

UPDATE example_db.example_tbl, example_2_db.example_tbl SET example_db.example_tbl.example_status_label='Example' WHERE example_2_db.example_tbl.example_status='1' AND example_2_db.example_tbl.example_id=example_db.example_tbl.example_id;

UPDATE example_db.example_tbl, example_2_db.example_tbl SET example_db.example_tbl.example_status_label='Example' WHERE example_2_db.example_tbl.example_status='1' AND example_2_db.example_tbl.example_id=example_db.example_tbl.example_id;
UPDATE example_db.example_tbl, example_2_db.example_tbl SET example_db.example_tbl.example_status_label='Example' WHERE example_2_db.example_tbl.example_status='2' AND example_2_db.example_tbl.example_id=example_db.example_tbl.example_id;
UPDATE example_db.example_tbl, example_2_db.example_tbl SET example_db.example_tbl.example_status_label='Example' WHERE example_2_db.example_tbl.example_status='3' AND example_2_db.example_tbl.example_id=example_db.example_tbl.example_id;

---

I need to wrap each separate sql statement in code to transform the page into a ColdFusion document. I had never used Perl before yesterday but it seemed ideal for this task. For the most part I have it working but I am running into an issue.

This is the Perl script I am running on the document (I have simplified the replacement string just for the sake of this开发者_开发问答 question):

#!/usr/bin/perl -w

use strict;
use warnings;

my $num = 0;
$/ = '';
while (<>) {
  s/(INSERT[\s\S]*?;|DELETE[\s\S]*?;|UPDATE[\s\S]*?;|SELECT[\s\S]*?;)/'<!--- SQL Number: ' . ++$num . ' ' . '<p> ' . $1 . "<\/p> --->\r"/e;
  print;
}

__END__

This works for almost all of the statements in the documents that are separated by an extra line (\r). The ones where there is not an extra line between them are not replaced as expected. Note the three update statements above - it works on the first of the three but not on the remain two.)

I am guessing this is related to my pattern and the use of $/='';, which I think causes the script to read in paragraphs as opposed to lines.

I know there are many ways to approach this task, but this is what has gotten me the closest. I pieced it together from examples on the web and some trial and error. I know this must be simple to a non-newb, but alas I need some pointers.

Can any one help me get this to work completely?

---

You can take advantage of the fact that the individual sql statements in your input file are terminated by a semi-colon character. Set the record input separator to a semi-colon $/ = ';'in your perl script and then it will read one complete sql statement on each read of STDIN, regardless how many actual lines it spans.

#!/usr/bin/perl -w                                                                                                                                                                                                                                                                          

use strict;

$/ = ';';

my $num = 0;
while (my $sql = <>) {
  $sql =~ s/^\s+//;
  printf "<!--- SQL Number: " . ++$num . " <p>$sql</p> --->\n" if $sql;
}

---

From perlvar.html docs:
$/ The input record separator, newline by default. This influences Perl's idea of what a "line" is. Works like awk's RS variable, including treating empty lines as a terminator if set to the null string. (An empty line cannot contain any spaces or tabs.)

If you use $/, it should always be local.
I personally would do it like this:

my $file = join '', <DATA>
$file =~ s/.../.../eg;

But you can do it like below, but you must include the /g modifier.
Look at the >>> chunks Perl is grabbing. When $/ is set to '', it uses a blank line as a record separator.

use strict;
use warnings;

my $num = 0;

{
   local $/ = '';
   while (<DATA>)
   {
      print ">>> '$_'\n\n";
      s/(INSERT[\s\S]*?;|DELETE[\s\S]*?;|UPDATE[\s\S]*?;|SELECT[\s\S]*?;)/'<!--- SQL Number: ' . ++$num . ' ' . '<p> ' . $1 . "<\/p> --->\n"/eg;
      print;
   }
}

__END__

---

Once you have a ; with in the statement you are going to hurt. Use a dedicated tool like SQL::SplitStatement, this works precise.