4.0.0 - Table Input - Copy/Paste SQL, syntax errors

[jkereszturi]

I just downloaded and am playing with 4.0.0 release (running on Windows XP here with java 1.6.0_20). I haven't seen this mentioned and am curious if anyone else has seen this or might have any suggestions:

When creating/modifying SQL in Spoon "Table Input" steps, I generally develop/test the SQL in DBVisualizer and then copy/paste it over to Spoon's SQL editor.

Doing this in 4.0.0, my queries are failing in Spoon with what appear to be syntax errors. The log shows unprintable characters in the SQL (blocks) which appear to be the line-terminators. I've tried pasting over to a straight text editor first (textpad) and then over to Spoon, but I'm getting the same results. I'll also mention that when I paste it into the "Table Input" SQL editor dialog, it does look fine...syntax is colorized properly, comments are green, etc.

-john

[CHamel]

Don't know if that might help but I use to enter comments in my SQL Query in PDI 3.2 like "--This is my comment" and it was working fine. It's not anymore within PDI 4.0 so if you have any of theses, try removing them.

[jkereszturi]

Yes, thanks for that! It was indeed my in-line '--' comments. This is a bug, right?

[MattCasters]

I have actually no idea if it's a bug. It's a bit of a mystery since I don't think we had anything in our code that stripped out single line comments in 3.2.

[jkereszturi]

There shouldn't be any need to strip out comments. I execute SQL with comments (line, block) through jdbc and have never seen any problems there. Perhaps some pre-processing is taking place on the sql source before it gets passed to the DB? I'll grab the source when I'm at work tomorrow and see what I can find.

On a side note, I've seen a similar issue (when I was copying/pasting SQL from an external DB tool into Spoon) before and had posted on it. Specifically it was a fairly hairy query using numerous with-clause definitions. It would run fine in the SQL tool, but Spoon seemed to choke on it, even refusing to define any output fields (when doing a "Show output fields"). Simpler queries using with-clauses worked fine but there's something going on in manipulation or parsing of the query that spoon does, I'd guess.

[MattCasters]

That is incorrect. Certain databases like (certain versions of) DB2 won't even allow a single line feed or carriage return in the SQL statement. Kettle actually strips those out if needed. What could have happened is that the CR's are being stripped out needlessly.

[jkereszturi]

Interesting. The issue sounds similar to the one mentioned here:

http://archives.postgresql.org/pgsql-jdbc/2006-09/msg00125.php

Sounds like the jdbc driver should do it, but doesn't necessarily in all cases.

That's ok. I always hated writing comments, anyway! ;-)

[MattCasters]

You just got a glimpse of all the horrible things that Kettle has to work around on a daily bases.

[jkereszturi]

Matt, just a follow-up on this issue:

Since some drivers are pickier than others, wouldn't it be more appropriate to just pass the SQL, unmodified, so that those folks using databases & jdbc drivers that allow comments can continue to use queries unmodified? Those who are using problematic DB's/JDBC drivers would already know they cannot use those types of comments embedded in their SQL (if they've ever run the queries in another JDBC environment).

The real usability issue is that I can edit/debug a query which runs fine in another tool (e.g., DBVisualizer), but then it fails if I paste that query into the SQL Editor in a 'Table Input' step. Plus, there are situations where embedded comments are highly useful in SQL.

Many thanks and congratulations on the 4.0 release!!

-john