Bug #45234 for SQL-Beautify: Need SQL Beautify Help

Wed Apr 22 20:48:58 2009 Steve.Reiss [...] paraccel.com - Ticket created

Subject:	Need SQL Beautify Help
Date:	Wed, 22 Apr 2009 19:41:59 -0500
To:	<bug-SQL-Beautify [...] rt.cpan.org>
From:	"Steve Reiss" <Steve.Reiss [...] paraccel.com>

Hi Jonas, I found your EXCELLENT SQL Beautify module on CPAN last week for a project that I'm working on. It works great! I have a problem where a literal in a WHERE clause gets split into 2 tokens and beautified into token1 token2. My example : (problem token is in BOLD) $sql_query = "select day, listtime from sales where field = N'problem' "; %opts = (spaces => 2); my $sql_beauty = new MyBeautify(%opts); $sql_beauty->query($sql_query); my $nice_sql = $sql_beauty->beautify; print $nice_sql; Output = (I added the print *token = to the module at the top of the processing loop) *token = select *token = day *token = , *token = listtime *token = from *token = sales *token = where *token = field *token = = *token = N *token = 'problem' select day, listtime from sales where field = N 'problem' ç incorrectly formatted token The SQL now will fail to run because : field = N<space>'problem' Is invalid SQL - I get a type error on the N when running the query Is there any way to work around this problem that I've run into? Many thanks! Steve Reiss | QA Engineer PAR)ACCEL steve.reiss@PARACCEL.COM <mailto:steve.reiss@PARACCEL.COM> | WWW.PARACCEL.COM <http://www.paraccel.com/> 3 Results Way, Cupertino, CA 95014 PHONE: 408-861-3524

Thu Apr 23 02:20:06 2009 jkramer [...] nex.scrapping.cc - Correspondence added

Subject:	Re: [rt.cpan.org #45234] Need SQL Beautify Help
Date:	Thu, 23 Apr 2009 06:18:44 +0000
To:	bug-SQL-Beautify [...] rt.cpan.org
From:	"Jonas Kramer" <jkramer [...] nex.scrapping.cc>

Hi Steve, I think this is rather a problem with SQL::Tokenizer, because it looks like it's not handling that kind of string as a constant. I'll think of a work around, but in the meantime you should open a ticket for the tokenizer module as well. Regards, Jonas Show quoted text

-----Original Message----- From: "Steve Reiss via RT" <bug-SQL-Beautify@rt.cpan.org> Date: Wed, 22 Apr 2009 20:48:59 To: undisclosed-recipients:;<Invalid address> Subject: [rt.cpan.org #45234] Need SQL Beautify Help Wed Apr 22 20:48:58 2009: Request 45234 was acted upon. Transaction: Ticket created by Steve.Reiss@paraccel.com Queue: SQL-Beautify Subject: Need SQL Beautify Help Broken in: (no value) Severity: (no value) Owner: Nobody Requestors: Steve.Reiss@paraccel.com Status: new Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=45234 > Hi Jonas, I found your EXCELLENT SQL Beautify module on CPAN last week for a project that I'm working on. It works great! I have a problem where a literal in a WHERE clause gets split into 2 tokens and beautified into token1 token2. My example : (problem token is in BOLD) $sql_query = "select day, listtime from sales where field = N'problem' "; %opts = (spaces => 2); my $sql_beauty = new MyBeautify(%opts); $sql_beauty->query($sql_query); my $nice_sql = $sql_beauty->beautify; print $nice_sql; Output = (I added the print *token = to the module at the top of the processing loop) *token = select *token = day *token = , *token = listtime *token = from *token = sales *token = where *token = field *token = = *token = N *token = 'problem' select day, listtime from sales where field = N 'problem' ç incorrectly formatted token The SQL now will fail to run because : field = N<space>'problem' Is invalid SQL - I get a type error on the N when running the query Is there any way to work around this problem that I've run into? Many thanks! Steve Reiss | QA Engineer PAR)ACCEL steve.reiss@PARACCEL.COM <mailto:steve.reiss@PARACCEL.COM> | WWW.PARACCEL.COM <http://www.paraccel.com/> 3 Results Way, Cupertino, CA 95014 PHONE: 408-861-3524

Thu Apr 23 02:20:07 2009 The RT System itself - Status changed from 'new' to 'open'

Thu Apr 23 12:27:54 2009 Steve.Reiss [...] paraccel.com - Correspondence added

Subject:	RE: [rt.cpan.org #45234] Need SQL Beautify Help
Date:	Thu, 23 Apr 2009 11:21:04 -0500
To:	<bug-SQL-Beautify [...] rt.cpan.org>
From:	"Steve Reiss" <Steve.Reiss [...] paraccel.com>

Hi Jonas! Thank you for your reply! I will file the problem with the SQL::Tokenizer package. In the meantime, I've worked up a simple regex to remove the space in the criteria constant that was causing the problem when running the query. Again, thanks for your time! Steve Show quoted text

-----Original Message----- From: jkramer@nex.scrapping.cc via RT [mailto:bug-SQL-Beautify@rt.cpan.org] Sent: Wednesday, April 22, 2009 11:20 PM To: Steve Reiss Subject: Re: [rt.cpan.org #45234] Need SQL Beautify Help <URL: https://rt.cpan.org/Ticket/Display.html?id=45234 > Hi Steve, I think this is rather a problem with SQL::Tokenizer, because it looks like it's not handling that kind of string as a constant. I'll think of a work around, but in the meantime you should open a ticket for the tokenizer module as well. Regards, Jonas

-----Original Message----- From: "Steve Reiss via RT" <bug-SQL-Beautify@rt.cpan.org> Date: Wed, 22 Apr 2009 20:48:59 To: undisclosed-recipients:;<Invalid address> Subject: [rt.cpan.org #45234] Need SQL Beautify Help Wed Apr 22 20:48:58 2009: Request 45234 was acted upon. Transaction: Ticket created by Steve.Reiss@paraccel.com Queue: SQL-Beautify Subject: Need SQL Beautify Help Broken in: (no value) Severity: (no value) Owner: Nobody Requestors: Steve.Reiss@paraccel.com Status: new Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=45234 > Hi Jonas, I found your EXCELLENT SQL Beautify module on CPAN last week for a project that I'm working on. It works great! I have a problem where a literal in a WHERE clause gets split into 2 tokens and beautified into token1 token2. My example : (problem token is in BOLD) $sql_query = "select day, listtime from sales where field = N'problem' "; %opts = (spaces => 2); my $sql_beauty = new MyBeautify(%opts); $sql_beauty->query($sql_query); my $nice_sql = $sql_beauty->beautify; print $nice_sql; Output = (I added the print *token = to the module at the top of the processing loop) *token = select *token = day *token = , *token = listtime *token = from *token = sales *token = where *token = field *token = = *token = N *token = 'problem' select day, listtime from sales where field = N 'problem' ç incorrectly formatted token The SQL now will fail to run because : field = N<space>'problem' Is invalid SQL - I get a type error on the N when running the query Is there any way to work around this problem that I've run into? Many thanks! Steve Reiss | QA Engineer PAR)ACCEL steve.reiss@PARACCEL.COM <mailto:steve.reiss@PARACCEL.COM> | WWW.PARACCEL.COM <http://www.paraccel.com/> 3 Results Way, Cupertino, CA 95014 PHONE: 408-861-3524