2

GO naturally occurring in a multiline text field can break query parser

description

A rare case to be sure but it happened to us with real data. We're ripping text from pdfs using Apache Tika. So we end up with multiline text fields that can look like this:
  This
is
text
extracted
from
a
pdf
Now imagine what happens when the word GO gets extracted to its own line. Here's a quick demo.
CREATE TABLE [example] ([pdfTikaContents] ntext NOT NULL);
GO

INSERT INTO [example] ([pdfTikaContents]) VALUES (N'PDF contents extracted by Apache Tika...
we
go
in
a
different
direction
...');
GO
I attached a screenshot of running the same query in SQL Server Compact Toolbox 4. It's the same error message from sqlcecmd40 -i command line. I generated the SQL using exportsqlce40.

Probably not an easy fix. I have some ideas for working around it. Thanks for all your work on these great SQL CE tools.

file attachments

Closed Nov 2, 2014 at 12:16 PM by ErikEJ
Fixed in 1.2.0.15

wrote Oct 7, 2014 at 5:01 PM

Thank for reporting this, I basically use the same code for Query parsing both here and in exportsqlce.codeplex.com
I welcome any suggestions to improve in any form and shape!

wrote Oct 22, 2014 at 8:09 AM

wrote Oct 22, 2014 at 9:58 AM

Hi Erik,

I hope the above class solves your issue, I had a fun time writing it to fix a similar problem for DbUp. Sadly a couple of bugs were found with it (now fixed) and those bugs made me realise how meagre my unit tests really were. They told me everything was 'green' :(

On a related note - your name keeps cropping up and its scaring me. Your name is within my VS (I use your extension) - then you leave me a comment on my CrmAdo extension page, then I see you over at DbUp..

If you can stop cropping up everywhere please.. haha!

Darrell

wrote Oct 22, 2014 at 10:32 AM

@darelltunnell Thanks Darell, great contribution!
I will also use this in my Toolbox (actually the API project on whcih the toolbox is based) as I allow multiple queries in my SQL editor, and the current implementation is "brittle"

wrote Oct 23, 2014 at 7:38 PM

I took a look at https://github.com/DbUp/DbUp/blob/master/src/DbUp/Support/SqlServer/SqlCommandReader.cs and this issue https://github.com/DbUp/DbUp/issues/61. Looks promising. My workaround has been regexing bad 'go's out of my sql scripts but that has been brittle.

wrote Oct 24, 2014 at 12:45 PM

I will try to implement this at the weekend, and you can give it a GO (!)..

wrote Oct 24, 2014 at 12:45 PM

wrote Oct 24, 2014 at 4:59 PM

Fixed on changeset 89153

wrote Oct 24, 2014 at 5:00 PM

@twamley Would be grateful if you could check if this works for you!

wrote Oct 28, 2014 at 1:56 AM

@ErikEJ,

I've tested this thoroughly on my end and can confirm it is working now. Thank you! Two things worth noting:

1: I had to add SqlCommandReader.cs to the csproj. Maybe that was a tool problem on my end. But after downloading the changeset zip it didn't compile and I first went down the road of installing the DbUp nuget. That compiled but behaved wrong in regards to -i "C:\path\to\file.sql".

2: Comments at the end of a sql file in single-line ( --comment ) and multiline ( /comment/ ) varieties raise an error now where they did not before. Easy for me to work around although the error message was not much help.
    Error Code: 80040E14
Message   : There was an error parsing the query. [ Token line number = 1,Token
line offset = 11,Token in error =  ]
Minor Err.: 25501
Source    : SQL Server Compact ADO.NET Data Provider
Num. Par. : 1
Num. Par. : 11
Thank you!

wrote Oct 28, 2014 at 6:36 AM

I missed adding the file to the 40 project, yes. I will check if the error with comments at the end of the file can be avoided...

wrote Oct 28, 2014 at 6:41 AM

wrote Oct 28, 2014 at 12:43 PM

It this the error you get on comments at the end of file?

Console.Error.WriteLine("Invalid command text");

wrote Oct 29, 2014 at 8:44 AM

Just checked in a fix for the "comments" issue, pls let me know if that works for you

wrote Oct 30, 2014 at 1:00 PM

Worked great. Thanks again!

wrote Oct 30, 2014 at 1:42 PM

I will do a release update then, thanks for helping fix this!

wrote Nov 2, 2014 at 12:16 PM